Nested Loops cost

From: <sweidanz_at_yahoo.com>
Date: Wed, 1 May 2002 10:58:08 +1000
Message-ID: <66Hz8.13$rT2.1303@nsw.nnrp.telstra.net>

Can anyone explain the difference in consistent gets on the following two queries.
1.

SELECT  car.cd_company_system,
        car.no_combine_024,
        car.no_account,
        le.nm_preferred
FROM   CAR_table car,
        LE_TABLE le

WHERE car.tp_cust_acct_role = 'P'
AND (car.dt_end IS NULL OR car.dt_end > SYSDATE)

AND    car.cd_company_system = 'RPT1'
AND    car.cd_company_system = le.cd_company_system
AND    car.no_legal_entity = le.no_legal_entity

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=103 Card=2 Bytes=80)    1 0 NESTED LOOPS (Cost=103 Card=2 Bytes=80)    2 1 TABLE ACCESS (FULL) OF 'CAR_TABLE' (Cost=101 Card=2 Bytes=38)

   3 1 TABLE ACCESS (BY INDEX ROWID) OF 'LE_TABLE' (Cost=1 Card=307 Bytes=6447)

   4 3 INDEX (UNIQUE SCAN) OF 'PVP036' (UNIQUE) Statistics

---

          0  recursive calls
          8  db block gets
       1051  consistent gets
        649  physical reads
          0  redo size
       6358  bytes sent via SQL*Net to client
        836  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        122  rows processed

2.

SELECT /*+ INDEX (car xvp0242) */

       car.cd_company_system,
       car.no_combine_024,
       car.no_account,
      le.nm_preferred
FROM   CAR_TABLE car,
       LE_TABLE le

WHERE car.tp_cust_acct_role = 'P'
AND (car.dt_end IS NULL OR car.dt_end > SYSDATE)

AND    car.cd_company_system = 'RPT1'
AND    car.cd_company_system = le.cd_company_system
AND    car.no_legal_entity = le.no_legal_entity

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=354 Card=2 Bytes=80)    1 0 NESTED LOOPS (Cost=354 Card=2 Bytes=80)    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CAR_TABLE' (Cost=352 Card=2 Bytes=38)

   3 2 INDEX (FULL SCAN) OF 'XVP0242' (NON-UNIQUE) (Cost=347 Card=2)

   4 1 TABLE ACCESS (BY INDEX ROWID) OF 'LE_TABLE' (Cost=1 Card=307 Bytes=6447)

   5 4 INDEX (UNIQUE SCAN) OF 'PVP036' (UNIQUE) Statistics

---

          0  recursive calls
          0  db block gets
        846  consistent gets
          0  physical reads
          0  redo size
       6358  bytes sent via SQL*Net to client
        836  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        122  rows processed

xvp0242 index is a nonunique composite index on (no_legal_entity, cd_company_system)
pvp036 index is a unique composite index on (no_legal_entity, cd_company_system)

I can not understand why by forcing to use an index xvp0242, it does less consistent gets than with doing full scan on the table. My undestanding is INDEX full scan, will read each index, and as the index is not enough to check for the rest of the conditions, it needs to get the data block for that index, then check that the record meets the conditions.If it does, then join to the second table to get the match. Which eventually means reading all the index blocks, reading all data blocks for the first table. While in doing a full scan on the first table, it does read the data blocks only, check that record meets the conditions, then join to the second table to get the match record. By doing so, it means full scan should read less data blocks (as it did not have to read the index blocks). But it does not seem that Oracle is doing what I initially understand.
Can anyone helps in clarifying this behaviour.

Thanks,
ZS Received on Tue Apr 30 2002 - 19:58:08 CDT