index scan is costly

From: Steve Robin <ocmaman_at_gmail.com>
Date: 5 Mar 2007 18:56:09 -0800
Message-ID: <1173149768.947681.132620@s48g2000cws.googlegroups.com>

In this case index scan is costly.
Even there is no repeat value in index column.

SQL> SELECT TCPART_GLOBAL.tril_gid FROM MCC_CATALOG.TCPART_GLOBAL WHERE ( TCPART_GLOBAL.ProductId ) = 'G62580000000' ORDER BY TCPART_GLOBAL.tril_gid;

TRIL_GID

---

BYN0FHR1WGGZJSGJ53ZJATYQ0Y20ZZZZ Elapsed: 00:00:06.06

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TCPART_GLOBAL'    2 1 INDEX (FULL SCAN) OF 'KEY_TCPART_GLOBAL' (UNIQUE) Statistics

---

          0  recursive calls
          0  db block gets
      11066  consistent gets
          0  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        349  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ FULL(TCPART_GLOBAL) */ TCPART_GLOBAL.tril_gid FROM MCC_CATALOG.TCPART_GLOBAL WHERE ( TCPART_GLOBAL.ProductId ) = 'G62580000000' ORDER BY TCPART_GLOBAL.tril_gid;

TRIL_GID

---

BYN0FHR1WGGZJSGJ53ZJATYQ0Y20ZZZZ Elapsed: 00:00:05.96

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=180 Card=395 Bytes=1
          2245)

   1    0   SORT (ORDER BY) (Cost=180 Card=395 Bytes=12245)
   2    1     TABLE ACCESS (FULL) OF 'TCPART_GLOBAL' (Cost=149 Card=39
          5 Bytes=12245)

Statistics

---

          0  recursive calls
          0  db block gets
        972  consistent gets
          1  physical reads
          0  redo size
        242  bytes sent via SQL*Net to client
        349  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(1) from MCC_CATALOG.TCPART_GLOBAL;

  COUNT(1)

---

     25147
SQL> select * from dba_indexes where index_name='KEY_TCPART_GLOBAL';

OWNER                          INDEX_NAME
INDEX_TYPE                  TABLE_OWNER
TABLE_NAME                     TABLE_TYPE  UNIQUENES COMPRESS
PREFIX_LENGTH TABLESPACE_NAME                 INI_TRANS  MAX_TRANS

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE

LOG     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS     NUM_ROWS
SAMPLE_SIZE LAST_ANAL DEGREE
INSTANCES                         PAR T G S BUFFER_ USE DURATION

PCT_DIRECT_ACCESS ITYP_OWNER
------------------------------ ------------------------------

• ------------------------------
  ------------------------------ ----------- --------- --------
  
  • ------------------------------ ---------- ----------
    -------------- ----------- ----------- ----------- ------------
    
  • -------------- ---------- --------------- ---------- ---
    ---------- ----------- ------------- -----------------------
    
    • ----------------- -------- ----------
      ----------- --------- ----------------------------------------
      
      • --- - - - ------- ---
        --------------- ----------------- ---
        PARAMETERS

        ---

        GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI --- ------------ ------ -------- --- MCC_CATALOG KEY_TCPART_GLOBAL NORMAL MCC_CATALOG TCPART_GLOBAL TABLE UNIQUE DISABLED MCC_CATALOG_INDEX 2 255 4194304 1048576 1 2147483645 0 1 1 10 YES VALID 1 1 NO N N N DEFAULT NO

NO                               NO

SQL> select count(distinct ProductId) FROM MCC_CATALOG.TCPART_GLOBAL;

COUNT(DISTINCTPRODUCTID)

---

                   25147

What could be reason Index scaning is costly then table scan. Received on Mon Mar 05 2007 - 20:56:09 CST