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"
It's eating my brains.Kindly explain me Gurus.
Regards,
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
