Sorry, its late on Friday and need beer... The bitmap index is partitioned in INV5.
-----Original Message----- Sent: 07 November 2003 15:30 To: Multiple recipients of list ORACLE-L on. [Sorry, also posted with the wrong subject title - RE: (un)intelligent agent] Hi all, Does anyone have an idea why the query is picking a random local bitmap index whose single column is not used in the query to do a conversion on? Regards, Adrian ----------------------- SELECT 1 FROM small_t bo_ WHERE exists ( select 1 from big_t where bo_.be_id = inv5) ------------------------------------------------------------------------------------------------------ | Operation | Name Rows | Bytes| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | SELECT STATEMENT | 352 | 17K| 29 | | | | FILTER | | | | | | | TABLE ACCESS FULL |SMALL_T 352 | 17K| 29 | | | | PARTITION RANGE SINGLE | | | | KEY | KEY | | TABLE ACCESS BY LOCAL INDEX ROWID |BIG_T 250K| 2M| 26561 | KEY | KEY | | BITMAP CONVERSION TO ROWIDS | | | | | | | BITMAP INDEX FULL SCAN |TRANSACTIONT_BIX9 | | | KEY | KEY | ------------------------------------------------------------------------------------------------------ Version 8.1.7.4.1, WinNT TABLE TYPE INDEX_NAME NUM_ROWS BLVL DSTNCT_KYS CF COLUMN_NAME ------------------------------------------------------------------------------------------------------ BIG_T BITMAP TRANSACTIONT_BIX9 24041302 1 2 1953 INV9 BIG_T is partitioned on INV5. There is no index on INV5. NAME TYPE VALUE ------------------------------------ ------- ------------------------------ object_cache_optimal_size integer 102400 optimizer_features_enable string 8.1.7 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_max_permutations integer 80000 optimizer_mode string CHOOSE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Turner, Adrian A SITI-ITPSIE 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Turner, Adrian A SITI-ITPSIE 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).