Index Seek
When is WILD used and when is KEYED used?
- Gayathri_Jayaraman
- Posts: 75
- Joined: Wed May 08, 2013 5:03 am
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
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
- bforeman
- Community Advisory Board Member
- Posts: 1006
- Joined: Wed Jun 29, 2011 7:13 pm
In a multi-component key, do all leading fields that aren't used in the filter need to be mandatorily wild carded?
Thanks,
Gayathri
Thanks,
Gayathri
- Gayathri_Jayaraman
- Posts: 75
- Joined: Wed May 08, 2013 5:03 am
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
- bforeman
- Community Advisory Board Member
- Posts: 1006
- Joined: Wed Jun 29, 2011 7:13 pm
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
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
- Gayathri_Jayaraman
- Posts: 75
- Joined: Wed May 08, 2013 5:03 am
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
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
- bforeman
- Community Advisory Board Member
- Posts: 1006
- Joined: Wed Jun 29, 2011 7:13 pm
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
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
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
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
Regards,
Bob
- bforeman
- Community Advisory Board Member
- Posts: 1006
- Joined: Wed Jun 29, 2011 7:13 pm
Thanks Bob and Richard!!
What is the difference between a DICTIONARY and an INDEX?
What is the difference between a DICTIONARY and an INDEX?
- Gayathri_Jayaraman
- Posts: 75
- Joined: Wed May 08, 2013 5:03 am
Gayathri,
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
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
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
12 posts
• Page 1 of 2 • 1, 2
Who is online
Users browsing this forum: No registered users and 1 guest