Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Using an index.

Re: Using an index.

From: Frank <fvanbortel_at_netscape.net>
Date: Sat, 26 Apr 2003 20:27:36 +0200
Message-ID: <3EAACF98.2060901@netscape.net>


Jonathan Lewis wrote:
> 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
>
>
>
>

Old? From the 8i Performance tuning Guide: 3. The optimizer compares the costs of the plans and chooses the one with the smallest cost.

A bit further:
The CBO chooses an access path based on the following factors: * The available access paths for the statement. * The estimated cost of executing the statement using each access

   path or combination of paths.

Then:
To choose among available access paths, the optimizer considers the following factors:

Selectivity: The selectivity is the percentage of rows in the table that the query selects. A query that selects a small percentage of a table's rows has good selectivity, while a query that selects a large percentage of rows has poor selectivity.

The optimizer is more likely to choose an index scan over a full table scan for a query with good selectivity than for one with poor selectivity. Index scans are usually more efficient than full table scans for queries that access only a small percentage of a table's rows, while full table scans are usually faster for queries that access a large percentage.

OK - so define small percentage, and large. And then, there's a whole lot of parameters that affect CBO.

And yes, I've read the story (and quoted it), as it came from a source I respect - but I'll darned if I can remember that source. Have been looking around, but cannot find it - will quote it, when found, though. I used to believe 10<x<30 (Oracle Consulting), but the other source claimed x=5.

-- 
Regards, Frank van Bortel
Received on Sat Apr 26 2003 - 13:27:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US