it is quite simple with regards to indexes, follow these two rules:

o have as few indexes as possible
o but as many as you need

or you could use 10g and let the access advisor do the work for you....

but sure.

the cardinality has nothing to do with it.

how you USE the index, what QUESTIONS you ask - that is what it is all about.

Think of a copy of ALL_OBJECTS. Suppose these are the questions you ask:

"What objects does SCOTT own"
"What tables does SCOTT own"
"Show me all of the details for the TABLE named EMP owned by SCOTT"


Well, we'd like to have an index on OWNER for question 1.

On OBJECT_TYPE, OWNER for question 2.

On OBJECT_NAME, OBJECT_TYPE, OWNER for question 3.

Arguably - object_name is the most "selective" and either owner or object_type the least selective.

However, the index that makes the most sense?

(owner,object_type,object_name) - it can be used for ALL THREE.


Now, say I ask the question - "show me all object_id's greater than :X for tables"

where object_id > :x and object_type = 'TABLE'

object_id is certainly "unique", very selective, object_type is not selective at all.

But I'd want an index on (OBJECT_TYPE,OBJECT_ID) - not the other way around. I want to goto TABLES in the index, find the first OBJECT_ID for a table > :x, and range scan from there (everything - EVERYTHING I hit in the range scan is "data I want").

If we indexed (object_id,object_type) - we'll, we would range scan every OBJECT_ID > :x and then have to ask each index entry "are you a table???" we'd process ton of index entries we didn't care about.




As for the clustering factor - if the cluster factor is near the number of blocks in the table - that indicates the "data in the table is sorted by the index key". If the cluster factor is near the number of ROWS in the table - that indicates "the data in the table is not stored sorted by the index key"


Remember - data can be sorted by how many keys? Pretty much the answer is "1" - I would be surprised if the data in the table was sorted by

A,B

AND

B,A

In the case of a primary key - say your primary key is (bad key, just for ILLUSTRATION, lets not discuss the pros/cons of using timestamp as part of a key :)

CUSTOMER_ID, TRANSACTION_TIMESTAMP


Now, the data tends to get inserted into this table by transaction_timestamp. customer_ids however arrive randomly. If you did a "select *" from this table, you would likely see the data coming out mostly sorted by timestamp (not entirely, but mostly)

Hence, an index on (transaction_timestamp,customer_id) would have a "very good clustering factor" - becaue the index key and the table data are very much "in the same order"

An index on (customer_id,transaction_timestamp) on the other hand would have a "very bad clustering factor" since the data is not stored sorted by customer_id in the table.

But I used AIR QUOTES on purpose. The clustering factor is just a number - if your most important query was:

select *
from t
where customer_id = :x
and transaction_timestamp >= sysdate-7


I would sure want the index to be on (customer_id,transaction_timestamp)!!!! There is no way I'd want it the other way around.

Now, we could get into a discussion on "how to keep the data organized by customer_id" and so on - but I'll point you to a book for that.

This discussion and most about physical structures is part of my old book "Expert one on one Oracle" (the index discussion, clustering of data and so on) as well as the updated version for 9i/10g in Expert Oracle: Database Architecture.

it would be highly unusual to use an index for those operations - you would typically not index them for that particular operation.

think about it - how would you foresee an index being useful in those cases?

It is not entirely clear what you are saying, but I think you are saying "if we have a where clause with six AND'ed predicates, and 4 of them use things we 'conventionally think - we should index' and two of them use these NOT IN or whatever constructs, the index should only contain the four fields".


Maybe, maybe not. "IT DEPENDS" (one of two answers to any technical question, the other being "why?")


select a, b, c, d, x, y, z
from t
where a = 5 and b = 10 and c = 15 and d = 20 and x not in (1,2) and y <> 42;


would it make sense to index (a,b,c,d,x,y) - sure, it could, it might even make sense to index (a,b,c,d,x,y,z)


The reason - to avoid hitting the table when not necessary (a,b,c,d,x,y) can do that. If there are 1,000,000 records out of 100,000,000 where "a = 5 and b = 10 and c = 15 and d = 20" is true, but out of that 1,000,000 only 2 are such that "x not in (1,2) and y <> 42" is true - we might want to discover that before hitting the table 1,000,000 times. We can answer the where clause against the INDEX instead of banging on the table 1,000,000 times to see what x and y are.

And if you add Z, we can avoid the table entirely (wouldn't make sense in my example necessarily because we only visit it twice, but if out of the 1,000,000 times - we found 500,000 hits for the x/y predicate - we might consider adding Z to the index as well)

See the progression of plans in the following:

 autotrace traceonly explain
ops$tkyte%ORA10GR2> create index t_idx on t(a,b,c,d);

Index created.

ops$tkyte%ORA10GR2> select a,b,c,d,x,y,z from t where a = 5 and b = 10 and c = 15 and d = 20 and x not in (1,2) and y <> 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    91 |     1   (0
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    91 |     1   (0
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("X"<>1 AND "X"<>2 AND "Y"<>42)
   2 - access("A"=5 AND "B"=10 AND "C"=15 AND "D"=20)

<b>Hit index to evaluate a,b,c,d - go to table to evaluate x,y and pick up z</b>

ops$tkyte%ORA10GR2> drop index t_idx;

Index dropped.

ops$tkyte%ORA10GR2> create index t_idx on t(a,b,c,d,x,y);

Index created.

ops$tkyte%ORA10GR2> select a,b,c,d,x,y,z from t where a = 5 and b = 10 and c = 15 and d = 20 and x not in (1,2) and y <> 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    91 |     0   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    91 |     0   (0
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     0   (0
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"=5 AND "B"=10 AND "C"=15 AND "D"=20)
       filter("X"<>1 AND "X"<>2 AND "Y"<>42)

<b>hit index to evaluate a,b,c,d and then filter on x,y and THEN hit table to pick up Z</b>

ops$tkyte%ORA10GR2> drop index t_idx;

Index dropped.

ops$tkyte%ORA10GR2> create index t_idx on t(a,b,c,d,x,y,z);

Index created.

ops$tkyte%ORA10GR2> select a,b,c,d,x,y,z from t where a = 5 and b = 10 and c = 15 and d = 20 and x not in (1,2) and y <> 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 2946670127

------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    91 |     0   (0)| 00:00:01
|*  1 |  INDEX RANGE SCAN| T_IDX |     1 |    91 |     0   (0)| 00:00:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"=5 AND "B"=10 AND "C"=15 AND "D"=20)
       filter("X"<>1 AND "X"<>2 AND "Y"<>42)

<b>just hit the index, no table</b>

ops$tkyte%ORA10GR2> set autotrace off