Re: Using an index.

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sun, 27 Apr 2003 14:11:57 +1000
Message-ID: <zyIqa.23149$1s1.355305@newsfeeds.bigpond.com>

"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 Received on Sat Apr 26 2003 - 23:11:57 CDT