Re: Don't rely on 9i Skip Scan Indexing

Greg,

As others have said 66 rows (even big rows as you seem to have) is pretty small when testing indexes.

Rule one when 'feature does not work' is to hint it in to find out what the cost of making it work is going to be.

Try /*+ index_ss_asc */ with the query
and you may find that a skip scan on
your data set has a cost which is higher than the FTS..

(You say the primary key has 6 values on a table of 66 rows - did you mean the leading column ? If so, the skip scan will probably have a cost of about 13)

I have actually built data sets where the first two columns of an index are skipped automatically for a query that uses only the third.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





grjohnson wrote in message
<32b8a689.0210202017.1400c57e_at_posting.google.com>...


>

>I thought I'd do a bit of testing regarding this.

>

>I took a table and created a composite index. The table had only 66

>rows. Small enough to test on, and the primary key index had 6

>distinct keys. I figure this is lowish cardiantly for this table.

>Results are as follows.

>

>1). First I analyzed the table.

>

>SQL> ANALYZE TABLE CR_FILE COMPUTE STATISTICS;