Archives of the TeradataForum

Message Posted: Sun, 01 Nov 2015 @ 14:47:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

To answer your specific questions:

I believe the sub-table will store -

a) the hash-value of the combination of (fname + lname) - DW: Yes

b) the actual values of fname and lname- DW: Yes

c) and finally the row-id(s) of the base table record(s). - DW: Yes

I think since the hash-value is generated based on the combination of fname + lname columns, using only fname in the query will result in a + different hash-value and hence can't be looked up in the sub-table. Hence the SI will not be utilized in this case. Please correct me if my rationale is correct. DW: Your rationale is correct.

An example of a covering index.

Firstly, a covering index is one where all of the columns from a particular table that are named in the query are in a particular index (not a PI, but an SI, JI etc).

Secondly there is no syntax to create a covering index, the index gets this name because it 'covers' access to the table for that query. The index is 'just an index'. So there is no difference in the sub-table content between a 'covering index' and any other index of the same type.

To use your example table and query from below.

     create table employee
     (
         empid integer,
         fname varchar(50),
         lname  varchar(50)
     )
     unique primary index (empid);
     create index (fname, lname) on employee;
     -------------------------------------------------
     select * from employee where fname = 'John';
     -------------------------------------------------

This index ** may ** be used as a covering index for the query. The bigger the table in relation to the index structure the more likely that is to happen. As has been mentioned by other people, the only real way to tell is to EXPLAIN the query and find out what is happening.

If you want to try and ensure that the index does get used for this query, then changing the index definition to the following:

     create index (fname, lname) ORDER BY HASH (fname) on employee;

This allows the optimiser to do a hash-lookup on the index as opposed to an index scan with the previous definition, BUT the index is not a covering index for this query because the query requires the empid column which is not in the index.

Now let's change the query to:

     select lname from employee where fname = 'John';

Using the second index definition ("create index (fname, lname) ORDER BY HASH (fname) on employee;") this index will probably 'cover' the query because all of the columns for this table that are referenced in the query are contained within the index. In addition, because the index entries are ordered by the hash of the fname column which is used for selection this becomes a lookup rather than a scan.

Note that for the index to cover the query, all columns from the table that are referenced in the query are included in the index. Those columns may be in the SELECT list, in the WHERE clause or both.

You might want to look at the 'Database Design' manual, I don't know what release of TD you are using, but for TD 14.10 look in chapter 10 ("secondary indices"), the section on "NUSIs and Query Covering".

I hope that helps.

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>