Thanks
for your comments. You are correct that AIF_OUTBOUND_IX01 is more
selective than AIF_OUTBOUND_IX03.
From
what I am seeing, degree is affecting the plan. If I delete stats, alter
index parallel 10, analyze table estimate stats - the bitmap conversion plan is
used.
If I
delete stats, alter index parallel 10 and renalyze, it goes back to the
index range scan.
At
this point, I can accept it - although I am curious as to why it has
happening. And can I use degree on other indexes where a
index range scan is being done to help performance? So many questions, so
little time.
-----Original Message-----
From: Khedr, Waleed [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 15, 2003 6:39 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: bitmap conversion on a index that is not bitmapped ???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]
