Wed Oct 28, 2020 9:13 pm
Login Register Lost Password? Contact Us


Index Seek

Questions and comments regarding the Online Lessons should be posted here.

Mon Sep 30, 2013 11:20 am Change Time Zone

When is WILD used and when is KEYED used?
Gayathri_Jayaraman
 
Posts: 75
Joined: Wed May 08, 2013 5:03 am

Mon Sep 30, 2013 1:43 pm Change Time Zone

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
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1002
Joined: Wed Jun 29, 2011 7:13 pm

Tue Oct 01, 2013 5:00 am Change Time Zone

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

Thanks,
Gayathri
Gayathri_Jayaraman
 
Posts: 75
Joined: Wed May 08, 2013 5:03 am

Tue Oct 01, 2013 11:54 am Change Time Zone

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
Community Advisory Board Member
 
Posts: 1002
Joined: Wed Jun 29, 2011 7:13 pm

Fri Oct 04, 2013 9:53 am Change Time Zone

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
Gayathri_Jayaraman
 
Posts: 75
Joined: Wed May 08, 2013 5:03 am

Fri Oct 04, 2013 12:32 pm Change Time Zone

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
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1002
Joined: Wed Jun 29, 2011 7:13 pm

Fri Oct 04, 2013 1:29 pm Change Time Zone

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
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1566
Joined: Wed Oct 26, 2011 7:40 pm

Fri Oct 04, 2013 1:38 pm Change Time Zone

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
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1002
Joined: Wed Jun 29, 2011 7:13 pm

Mon Oct 07, 2013 4:30 am Change Time Zone

Thanks Bob and Richard!!

What is the difference between a DICTIONARY and an INDEX?
Gayathri_Jayaraman
 
Posts: 75
Joined: Wed May 08, 2013 5:03 am

Mon Oct 07, 2013 12:58 pm Change Time Zone

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
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1566
Joined: Wed Oct 26, 2011 7:40 pm

Next

Return to Online Classroom

Who is online

Users browsing this forum: No registered users and 1 guest