RE: bitmap conversion on a index that is not bitmapped ???

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-----
Sent: Wednesday, October 15, 2003 6:39 PM To: Multiple recipients of list ORACLE-L

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-----
Sent: Wednesday, October 15, 2003 12:50 PM To: Multiple recipients of list ORACLE-L

Platform is 8.1.7.4, HP-UX 11.00  

I 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.   John

John Fedock
"K" Line America, Inc.
www.kline.com
* john.fedock_at_us.kline.com  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Fedock, John (KAM.RHQ)
  INET: John.Fedock_at_us.kline.com

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: ListGuru_at_fatcity.com (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).