more than one index for the same combination of key fields 
Author Message
 more than one index for the same combination of key fields
hi,

I'm wondering, if it does make sense to create two indices of the following
kind:

the primary key of a table is combined with the fields a and b.

therefor  a key exists with the fields a,b ascending order.
but a key with the fields a,b with descending order is also created.

background: the table entries should be read in both directions.

in my opinion only one key is required.

what do you think about that?

greetings
 Michael



Mon, 18 Jul 2005 17:02:40 GMT
 more than one index for the same combination of key fields

If these fields will EVER be used in descending sequence eg for a screen
paging backwards or to show a panel in descending sequence
then a descending index is sometimes a must ! It depends on how many rows
are in the table and how often this "function" is used/needed.
What we do here is create about 4 Descending indices on the most heavily
"backwards" used tables and all is OK. As usual you could create
the index and then see if its used by either using EXPLAIN or, if on z/OS,
doing a select from the SYSPACKDEP for the index name in question
with BTYPE = 'I'.


Mon, 18 Jul 2005 17:28:49 GMT
 more than one index for the same combination of key fields

Quote:
> If these fields will EVER be used in descending sequence eg for a screen
> paging backwards or to show a panel in descending sequence
> then a descending index is sometimes a must ! It depends on how many rows
> are in the table and how often this "function" is used/needed.
> What we do here is create about 4 Descending indices on the most heavily
> "backwards" used tables and all is OK. As usual you could create
> the index and then see if its used by either using EXPLAIN or, if on z/OS,
> doing a select from the SYSPACKDEP for the index name in question
> with BTYPE = 'I'.

You could also use:

CREATE INDEX ... ON ... ALLOW REVERSE SCANS

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Mon, 18 Jul 2005 18:40:35 GMT
 more than one index for the same combination of key fields
But that option doesn't exist under DB2 V6 in z/OS.....


Mon, 18 Jul 2005 21:24:34 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Two index keys on one field????

2. One-field INDEX performance VS multiple fields index

3. ***** Combination index on two numeric fields *****

4. Numeric Primary Key vs. Combination Primary Key

5. Primary key is a combination of two keys

6. Creating Unique composite Index on a table which has datetime as one of its keys

7. insert data from one field in one table to another field in another table

8. more than one key field in paradox

9. LOCATE; More than one key-field

10. Moving primary key from one field to another

11. composite foreign key one field constant?

12. Multiple foreign keys in one field


 
Powered by phpBB® Forum Software