Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using an index.
Good starting point - and I hadn't thought of the parallel option, But there are a few more crunch points to go.
-- 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 "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:zyIqa.23149$1s1.355305_at_newsfeeds.bigpond.com...Received on Sun Apr 27 2003 - 01:30:22 CDT
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:b8bpc7$b4q$1$830fa17d_at_news.demon.co.uk...
> >
> > 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.
> >
> >
>
> 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
>
> *** Yes it has been analyzed with current stats***
>
> SQL> select count(*) from test;
>
> COUNT(*)
> ----------
> 50000000
>
> *** Yes, it still does have 50 million rows ****
>
> 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
>
> **** Yes, it does have a unique index ****
>
> **** Now if we just do the following .... ****
>
> SQL> alter table test parallel 100;
>
> Table altered.
>
> **** and run the same query, which we know only returns 1 row in 50
Million
> ... ***
>
> 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
> >
![]() |
![]() |