| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Why isn't Oracle using my index ? <humour>
Hi Jonathan,
The whole concept of the percentage of *rows* being the driver in the CBO choosing an access path has always been one of my favourite bedtime fairy tales (that and the "Three Little Pigs").
*ONE* method to achieve what you suggest is as follows using a table with 50 million rows and a PK index:
SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name
= '
TEST' and owner = 'BOWIE';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
50000000 116960 139039
SQL> select count(*) from test;
COUNT(*)
SQL> set autotrace on
SQL> select * from test where x = 100;
X NAME
---------- ----------
100 Bowie
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=301 Card=1 Bytes=10) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=301 Card=1 B
ytes=10)
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C003233' (UNIQUE) (Cost=2 Ca
rd=50000000)
Statistics
174 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter table test parallel 100;
Table altered.
SQL> select * from test where x = 100;
X NAME
---------- ----------
100 Bowie
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=32 Card=1 Bytes=10) 1 0 TABLE ACCESS* (FULL) OF 'TEST' (Cost=32 Card=1 Bytes=10) :Q10000
1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."X",A1.
"NAME" FROM "TEST" PX_GRANULE(0, BLO
Statistics
194 recursive calls
3 db block gets
117058 consistent gets
116960 physical reads
796 redo size
430 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
the CBO chooses the FTS.
As it should, it's the cheapest cost, right ......
But then, if I return a certain parameter back to it's default value which I changed previously:
SQL> alter session set *** mystery parameter *** !!! = 100;
Session altered.
SQL> select * from test where x = 100;
X NAME
---------- ----------
100 Bowie
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=10) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Byt
es=10)
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C003233' (UNIQUE) (Cost=2 Ca
rd=50000000)
Statistics
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
we're back to the index again.
All good fun ....
Cheers ;)
Richard
> > > I'm sure that most of you have seen various claims > along the lines of: > Oracle will use an for a query that > access less than X% of the rows. > > X is usually given as something in the > region of 2% to 5% > > Having a spare 40 minutes in an airport this > evening, I asked myself - > "How low can you go ?" > > In my first test case, I managed to get > Oracle 9.2 to choose (i.e. no hints involved) > to scan 1.5 million row, rather than use a > primary key index to fetch one row. > i.e. 0.000067% > and it still did a tablescan. > > The result of the test also suggested that > I could safely bet on getting a tablescan > to select one row in 150,000,000. > > Unfortunately I don't have enough spare > disk space to check the theory. > > > So next time someone says: > Oracle will use an index for > accessing less than .... > you know what to say. > > > 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 > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > INET: jonathan_at_jlcomp.demon.co.uk > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > >
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: richard.foote_at_bigpond.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Sun Apr 27 2003 - 00:11:37 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |