Index Seek

[Gayathri_Jayaraman]

When is WILD used and when is KEYED used?

[bforeman]

KEYED and WILD are used with multi-component keys, and is used to indicate to the compiler which of the leading index fields are used as filters (KEYED) or wild carded (WILD) so that the compiler can warn you if you’ve gotten it wrong. Trailing fields not used in the filter are ignored (always treated as wildcards).

So for example if I have a multi-component key with an ID, LastName, and Firstname fields, and I want to build a query by Lastname, I would WILD the ID, use KEYED on LastName, and nothing is needed for FirstName since it is a trailing field not used in the filter.

So with the use of KEYED and WILD, you are telling the compiler that this is the intention of your query, and you simply did not forget to include the ID as a search field.

Hope this helps!

Bob

[Gayathri_Jayaraman]

In a multi-component key, do all leading fields that aren't used in the filter need to be mandatorily wild carded?

Thanks,
Gayathri

[bforeman]

In a multi-component key, do all leading fields that aren't used in the filter need to be mandatorily wild carded?

Yes, or you will receive a compiler warning.


Bob

[Gayathri_Jayaraman]

Please let me know if my understanding here is correct:

KEYED, when used in JOIN, indicates indexed access into record set - accepts index name alone - all fields part of index may be used for record access.

KEYED, when used in FETCH, accepts an expression using field name(s??) and mainly filters the index, performs what is called as the 'bookmark lookup' in database world, to retrieve all fields for records satisfying the index key.

Regards,
Gayathri

[bforeman]

Hi Gayathri,

Yes, spot on, and essentially they are doing the same thing, but KEYED as you mention is used in different contexts (JOIN vs. FETCH). Usually the Half-Keyed JOIN is used with a payload index, optimizing the read, and FETCH is used when the INDEX is a non-payload type, and additional I/O is needed into the base dataset.

Hope this helps!

Bob

[rtaylor]

Gayathri,

KEYED as an option on JOIN and KEYED as used in an INDEX filter are two different things.

The KEYED option on JOIN nominates an INDEX into the right dataset for the JOIN so that that join condition is first applied against the INDEX as a filter, then the selected right dataset records are fetched to pass on to the TRANSFORM. This is called a "full-keyed" JOIN.

When KEYED is used in an INDEX filter (along with WILD) you are simply specifying filtering by a trailing element of the key and wildcarding the leading element(s). That means the binary tree of the INDEX will be scanned (instead of walked) and the INDEX read will still be fast. Without KEYED/WILD in the filter, filtering by trailing elements causes the INDEX to be treated as a DATASET, and all the leaf nodes of the INDEX (the index "records") will be read to satisfy the filter (AKA a "full table scan").

A JOIN condition is implicitly a filter on the records in the left and right datasets. Therefore, if the right dataset is actually an INDEX (usually a payload index -- making the JOIN "half-keyed"), then you can also use KEYED in the JOIN condition to ensure the INDEX is treated as an index and not a dataset.

HTH,

Richard

[bforeman]

Thanks for the clarification Richard, when I said the "same" thing, I was referring to KEYED used in the JOIN condition, not as the JOIN flag, but it is important that you point that out. KEYED as the JOIN flag is used in a FULL KEYED JOIN, where the KEYED option in the JOIN condition is used as you described it.

Regards,

Bob

[Gayathri_Jayaraman]

Thanks Bob and Richard!!

What is the difference between a DICTIONARY and an INDEX?

[rtaylor]

Gayathri,

What is the difference between a DICTIONARY and an INDEX?

Take a look at this thread that discusses that very point: http://hpccsystems.com/bb/viewtopic.php?f=10&t=1062&sid=464a43fde197e917f465bff5eb3015a9

HTH,

Richard