It
seems that you have tow predicates in the where clause that can take advantage
of two different indexes.
Also
you have "AND" condition that Oracle tries to take advantage of using "BITMAP
AND" operation.
Since
you like the performance of the plan that uses the BITMAP conversion, I can say
that index "'AIF.AIF_OUTBOUND_IX01"
is more selective than
"'AIF.AIF_OUTBOUND_IX03"
And in the second plan that one that does not use the BITMAP, if
you force it to use index IX01, it would be
faster.
Degree has nothing to do to the index performance except when
doing Index FFS.
Regards,
Waleed
-----Original Message-----
From: Fedock, John (KAM.RHQ) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 15, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L
Subject: bitmap conversion on a index that is not bitmapped ???Platform is 8.1.7.4, HP-UX 11.00I have a complex view. After rebuilding all indexes this weekend, the view got much faster. I did a trace on it. Now, a few days later, the view is getting much slower. Yes, an analyze has run since then, doing an 'estimate statistics', but an estimate stats was done after the rebuilds as well. The difference in the explain is below - please note that bitmapped conversion info (in bold):Good plan:..67 .....FIRST ROW
..66 ......VIEW OF 'GAPP_USER. (CARD=1 BYTES=13 )
..65 .......SORT (AGGREGATE) (CARD=1 BYTES=121 )
..64 ........NESTED LOOPS (COST=53 CARD=1 BYTES=121 )
..61 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.AIF_OUTBOUND' (COST=51 CARD=1 BYTES=86 )
..60 ..........BITMAP CONVERSION (TO ROWIDS)
..59 ...........BITMAP AND
..55 ............BITMAP CONVERSION (FROM ROWIDS)
..54 .............INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03' (NON-UNIQUE) (COST=4 )
..58 ............BITMAP CONVERSION (FROM ROWIDS)
..57 .............SORT (ORDER BY)
..56 ..............INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX01' (NON-UNIQUE) (COST=11 )
..63 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.EDI_ROUTE' (COST=1 CARD=65 BYTES=2275 )
..62 ..........INDEX (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE) (CARD=65 )Bad plan:..61 .....FIRST ROW
..60 ......VIEW OF 'GAPP_USER. (CARD=1 BYTES=13 )
..59 .......SORT (AGGREGATE) (CARD=1 BYTES=121 )
..58 ........NESTED LOOPS (COST=52 CARD=1 BYTES=121 )
..55 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.AIF_OUTBOUND' (COST=51 CARD=1 BYTES=86 )
..54 ..........INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03' (NON-UNIQUE) (COST=3 CARD=1 )
..57 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.EDI_ROUTE' (COST=1 CARD=65 BYTES=2275 )
..56 ..........INDEX (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE) (CARD=65 )The interesting thing is, the table in question does not have any bit mapped indexes on it. Some notes on MetaLink show other people questioning the same issue, but no concrete info is given. Any ideas out there?TIA.JohnJohn Fedock
"K" Line America, Inc.
www.kline.com
* [EMAIL PROTECTED]