Index Usage ?!

From: Prem Khanna J <jprem_at_kssnet.co.jp>
Date: Thu, 24 Jul 2003 17:58:15 +0900
Message-Id: <25998.339309@fatcity.com>

Guys,

SQL> SELECT NAME,AGE,MEMNO,BLOODTYPE,HIGHT,LOOKSLIKE,CONTENT,PICID FROM PROFILE

       WHERE PREF = :PREF AND SEX = :OP_SEX AND FLAG = :FLAG AND ENTPC = :ENTPC
       AND NAME IS NOT NULL AND FACCESS > SYSDATE - 14 ORDER BY ENTDAY DESC;

NO ROWS SELECTED ELAPSED: 00:00:00.07 EXECUTION PLAN

---

   0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=7 CARD=187 BYTES=13838)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PROFILE' (COST=7 CARD=187 BYTES=13838)    2 1 INDEX (RANGE SCAN DESCENDING) OF 'IDX_PROFILE_SHINKI' (NON-UNIQUE) (COST=2 CARD=3759) STATISTICS

---

          0  RECURSIVE CALLS
          0  DB BLOCK GETS
          0  CONSISTENT GETS
          0  PHYSICAL READS
          0  REDO SIZE
        599  BYTES SENT VIA SQL*NET TO CLIENT
        372  BYTES RECEIVED VIA SQL*NET FROM CLIENT
          1  SQL*NET ROUNDTRIPS TO/FROM CLIENT
          0  SORTS (MEMORY)
          0  SORTS (DISK)
          0  ROWS PROCESSED

after analyzing the index "idx_profile_shinki" , the exection plan is as below and the elapsed time is 31 secs.but before analyzing the elapsed time was 0.07 secs ( as above ).

ELAPSED: 00:00:31.04 EXECUTION PLAN

---

   0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=1680 CARD=187 BYTES=13838)    1 0 SORT (ORDER BY) (COST=1680 CARD=187 BYTES=13838)    2 1 TABLE ACCESS (FULL) OF 'PROFILE' (COST=1676 CARD=187 BYTES=13838) STATISTICS

---

          0  RECURSIVE CALLS
          0  DB BLOCK GETS
      17448  CONSISTENT GETS
       5876  PHYSICAL READS
          0  REDO SIZE
        599  BYTES SENT VIA SQL*NET TO CLIENT
        372  BYTES RECEIVED VIA SQL*NET FROM CLIENT
          1  SQL*NET ROUNDTRIPS TO/FROM CLIENT
          1  SORTS (MEMORY)
          0  SORTS (DISK)
          0  ROWS PROCESSED

the env. is 9.2.0.3/win2k-sp3.

it is built on "profile" table .
the index has the columns "entpc,sex,flag,pref,entday" in it. the order of the columns in the index is also the same as i have mentioned.

1.so ,does it mean that "idx_profile_shinki" is a bad index.
2.if it is bad , why does cbo select this index ?
3.if it is good , why does elapsed time increase after analyzing this index ?
4.the leading column (entpc) of the index is not there in the beginning of "where" clause.

  then how is the index used ? it was not said so in a perf tuning book by "richard j. niemiec"    Received on Thu Jul 24 2003 - 03:58:15 CDT