Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Strange execution plan picking random index to do bitmap conv on.

RE: Strange execution plan picking random index to do bitmap conv on.

From: Turner, Adrian A SITI-ITPSIE <Adrian.Turner_at_shell.com>
Date: Fri, 07 Nov 2003 07:44:26 -0800
Message-ID: <F001.005D5FA1.20031107074426@fatcity.com>


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: Adrian.Turner_at_shell.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Turner, Adrian A SITI-ITPSIE INET: Adrian.Turner_at_shell.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).
Received on Fri Nov 07 2003 - 09:44:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US