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