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 )
..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 )
..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
*
[EMAIL PROTECTED]
