Home » RDBMS Server » Performance Tuning » Covering Index - Execution plan
Covering Index - Execution plan [message #64929] Tue, 09 March 2004 06:21 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I thought that covered indexes were much faster than accessing a table by rowid based on one index?! I would like to know based on my query and the query plans, why is the index on one column much more efficient than my composite index containing the columns of the select and the where list in my query?

I have a small table with about 16000 rows (gen_data01) and a large "big" table with about 3 millions rows (gen_datalog01).

Here is my query:


SELECT
data_id
FROM gen_data01 gd
WHERE gd.tjeneste = 'vits'
AND gd.kategori like 'generelt'
AND NOT EXISTS ( SELECT gdlog.data_id
FROM gen_datalog01 gdlog
WHERE gdlog.data_id = gd.data_id
AND gdlog.msn = '97181504' );

----------------------------------------------------------------------------------

EXECUTION PLAN 1: Using 'IDX_GEN_DATA01'  - covering index on (data_id, tjeneste, kategori)

----------------------------------------------------------------------------------

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=19)
   1    0   FILTER
   2    1     INDEX (FAST FULL SCAN) OF 'IDX_GEN_DATA01' (UNIQUE) (Cos
          t=5 Card=1 Bytes=19)

   3    1     INDEX (RANGE SCAN) OF 'GEN_DATALOG01_IDX' (NON-UNIQUE) (
          Cost=3 Card=1 Bytes=13)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
        375  consistent gets
         75  physical reads
          0  redo size
        186  bytes sent via SQL*Net to client
        429  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

----------------------------------------------------------------------------------

EXECUTION PLAN 2: Using 'IDX_GEN_DATA01_TJENESTE' index on (tjeneste)

----------------------------------------------------------------------------------
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=19)
   1    0   FILTER
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'GEN_DATA01' (Cost=5 Ca
          rd=1 Bytes=19)

   3    2       INDEX (RANGE SCAN) OF 'IDX_GEN_DATA01_TJENESTE' (NON-U
          NIQUE) (Cost=1 Card=1)

   4    1     INDEX (RANGE SCAN) OF 'GEN_DATALOG01_IDX' (NON-UNIQUE) (
          Cost=3 Card=1 Bytes=13)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        304  consistent gets
          3  physical reads
          0  redo size
        186  bytes sent via SQL*Net to client
        429  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

I can't understand that a my covering index is more expansive in term of time, consistent gets and physical reads!? I thought that with that kind of covered index, no access on the table was necessary and my query could get the result directly from the index data structure which are clustered!??

Thank you for your help.

Regards,

Patrick Tahiri.
Re: Covering Index - Execution plan [message #64930 is a reply to message #64929] Tue, 09 March 2004 09:35 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
Elapsed: 00:00:00.03
Elapsed: 00:00:00.02

are not that much different,dont you think? Besides,the timing reported by SQLPlus doesnt actually reflect the actual query execution timings.You will need to sql trace your session and look at the cpu/elapsed timings in the trace file to get the actual timings.

The 2nd time you executed the query, it may have read the cached index/table blocks(ie from memory) and hence reduced physical reads. You will need to focus more on the logical reads .

Now to the consistent gets,in the first case, its doing a fast full index scan(reading every leaf block),possibly using multiblock reads and hence the number of block reads could be more than the 2nd case,where its a table lookup after getting a range of rowids.It could be either way..there's no hard and fast rule about this. Index range scans is not always better than fast full index scans.

Also remember, eventually its the timing that matters.You could try increasing db_file_multiblock_read_count and see if the 1st one performs better. It 'looks' better.

-Thiru
Re: Covering Index - Execution plan [message #64932 is a reply to message #64930] Tue, 09 March 2004 22:01 Go to previous message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Thank you very much!

Regards,

Patrick Tahiri.
Previous Topic: 9i Performance on NT4
Next Topic: LOG_BUFFER - Selects?
Goto Forum:
  


Current Time: Thu Mar 28 12:14:03 CDT 2024