need help

[satishqlik]

Hi Experts,

I need a clarification on this.

temp1:

LOAD

     key,

     nam,

     doj,

     AutoNumber(name&'|'&doj) as samplenum,

     age,

     sales

FROM

<path>;

Yes,It showing unique numbers for each unique combination of field values

But what is the use of this and where to use particularly?

Any suggestions??

[kkkumar82]

Hi Satish,

The combination you have used is technically known as a composite key, even there is no Auto number function on top it it does work as name&'|'&doj as samplenum.

It is generally used to avoid synthetic keys , for eg another table also contains the same combination if you don't use a composite key with or with out auto Number it will result  a synthetic key.

[Anonymous]

Assuming you name and doj fields are long text fields then this will turn them into a small numeric field which consume less memory.  Plus if it is used to join 2 tables together this small numeric key field will be far more efficient.

[swuehl]

A benefit from using the Autonumber() is that it does not need to store the composite key values in the symbol table, hence it saves you some memory.

[kkkumar82]

One more point is , auto number is used to speed up the association because autonumber results a unique number

[swuehl]

I don't think that the autonumber will speed up associations or be more efficient in the table records (where the bit stuffed pointers should be the same, regardless if you are using Autonumber() or not).

The only benefit should result from less memory occupied in the symbol tables (no space required for an autonumber value).

[Peter_Cammaert]

Well, the symbol table with all composite key strings (or, as Rob thinks, hashes) will be there, but it will only last as long as the script runs.

[swuehl]

True, there need to be bookkeeping of autonumber values during script run, as far as I read the previous comments, we were discussing performance increase w.r.t. joins and linking of tables.

I don't think that using Autonumber() will do anything different / better than just using the composite keys.

[Peter_Cammaert]

"...and where to use particularly?"

Never use autonumber() to generate values that travel outside of the current data model (e.g. for storing in a QVD). Because there is no symbol table that survives the current load script run, an autonumber() call on the same key fields in another load script may generate a similar set of values that are linked to entirely different composite key strings.

Peter

[swuehl]

Using the second argument to the Autonumber() function, you can also create indices from distinct autonumber "buckets", e.g.

LOAD

     Customer,

     OrderID,

     OrderDate,

     Autonumber(OrderID, Customer) as OrderIndexCustomer,

     ...

RESIDENT INPUT

ORDER BY OrderDate asc;

will create an index on orders for each customer separately (order by was used to order the index same way as the order dates). You can create the index also using a table sorted by Date and Customer, and using Rangesum() and Peek(), this is just another use case I make use of the Autonumber() from time to time.