Using an index.

I came across that old fairy-tale about
Oracle and indexes a few days ago -
the one that goes:

    Oracle will use an index if the query     is going to access less than X%
    of the data.

Where X is usually given a value somewhere between 2 and 10.

Well, just as an idle way to pass the time, I decided to construct a data set and 'tune' an instance to see how low I could make X and still get a tablescan instead of fetching a single row through a primary key index.

Statistics generated by

    analyze table t1 compute statistics

The query:

    select v1 from t1 where n1 = 999;

The index generated by

    alter table t1 add constraint t1_pk
    primary key (n1);

The result:

I configured the system so that Oracle
CHOSE (i.e. without hints) to do a tablescan of 1,500,000 rows instead of using the
primary key index.

Given enough space, I KNOW that I could
push with up to tablescan for 1 row in 300M, and I think I could manage 1 row in 740M.

If you're going to IOUG-A I'll try to find time to explain what I did in my presentation on CBO on Monday afternoon.

Otherwise, I'll write it up some time in the next few weeks.

--
Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html



____UK_______April 22nd

____USA_(FL)_May 2nd

____Denmark__May 21-23rd

____Sweden___June

____Finland__September

____Norway___September


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html


____UK_(Manchester)_May x 2

____Estonia___June (provisional)

____Australia_June (provisional)

____USA_(CA, TX)_August


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