Home » RDBMS Server » Performance Tuning » Why showing BITMAP in Plan while no bitmap exists for the table (Oracle9i)
Why showing BITMAP in Plan while no bitmap exists for the table [message #345944] Fri, 05 September 2008 06:37 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I have analysed the table statistics using
analyze table <table_name> compute statistics for table for all indexes for all indexed columns;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'<table owner name>',tabname=>'<table name>',cascade=>TRUE);




I tried with both the above statement
on the tables that I have created new index.


When I am trying to view the plan, in the plan I am seeing BITMAP CONVERSION TO ROWIDS,BITMAP AND, BITMAP OR
whilethere are no bitmap index created on the table
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
| Id  | Operation                          |  Name                       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |     1 |    23 |    48 |
|   1 |  SORT GROUP BY                     |                             |     1 |    23 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID      | TRANSFER_DATA_TBL	         |     3 |    69 |    48 |
|   3 |    BITMAP CONVERSION TO ROWIDS     |                             |       |       |       |
|   4 |     BITMAP AND                     |                             |       |       |       |
|   5 |      BITMAP CONVERSION FROM ROWIDS |                             |       |       |       |
|   6 |       SORT ORDER BY                |                             |       |       |       |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   7 |        INDEX RANGE SCAN            | I2_TRANSFER_DATA_TBL        |   562 |       |     4 |
|   8 |      BITMAP OR                     |                             |       |       |       |
|   9 |       BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |
|  10 |        SORT ORDER BY               |                             |       |       |       |
|  11 |         INDEX RANGE SCAN           | I1_TRANSFER_DATA_TBL        |   562 |       |     4 |
|  12 |       BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |
|  13 |        SORT ORDER BY               |                             |       |       |       |
|  14 |         INDEX RANGE SCAN           | I1_TRANSFER_DATA_TBL        |   562 |       |    14 |
--------------------------------------------------------------------------------------------------



There is no bitmap index created for the table
Want to know why its a different plan now.


Regards,
Oli

[Updated on: Fri, 05 September 2008 07:08]

Report message to a moderator

Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #345961 is a reply to message #345944] Fri, 05 September 2008 07:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please search before posting.
http://www.orafaq.com/forum/m/171621/42800/?srch=BITMAP+CONVERSION+TO+ROWID#msg_171621
Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #345973 is a reply to message #345961] Fri, 05 September 2008 07:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the update. I have ran the below query
alter session set "_b_tree_bitmap_plans"=FALSE;


When trying to generate the plan

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
| Id  | Operation               |  Name                       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                             |     1 |    24 |  4540 |
|   1 |  SORT GROUP BY          |                             |     1 |    24 |       |
|   2 |   VIEW                  | index$_join$_001            |   884 | 21216 |  4540 |
|   3 |    HASH JOIN            |                             |   884 | 21216 |       |
|   4 |     INDEX RANGE SCAN    | I2_TRANSFER_DATA_TBL        |   884 | 21216 |   443 |
|   5 |     INDEX FAST FULL SCAN| I2_TRANSFER_DATA_TBL        |   884 | 21216 |   443 |
---------------------------------------------------------------------------------------



Can see index$_join$_001 .Its a different plan.

Was there anything wrong that had happened while executing the below query
SQL>analyze table <table_name> compute statistics for table for all indexes for all indexed columns;
SQL>exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'<table owner name>',tabname=>'<table name>',cascade=>TRUE);



Regards,
Oli




Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #345981 is a reply to message #345973] Fri, 05 September 2008 08:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nothing is "wrong", IMHO.
Just a behaviour with certain settings and your Sql query.
Investigate / google/ search about the hidden parameter
_index_join_enabled

Right now, I do not have access to database, else i could try to reproduce the case.
Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #345983 is a reply to message #345973] Fri, 05 September 2008 08:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Also,
Did you try Michael Bialik's suggestion in the other thread?
Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #345987 is a reply to message #345981] Fri, 05 September 2008 08:22 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
That means it may happen that someone who have the access has enabled the parameter _index_join_enabled?


using
 alter session set star_transformation_enabled = FALSE 
I am not being able to bypass it but


alter session set "_b_tree_bitmap_plans"=FALSE;


worked. But I can notice some change in the plan now for the same query which I ran yesterday.Already mentioned above.

Regards,
Oli

[Updated on: Fri, 05 September 2008 09:13]

Report message to a moderator

Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #346000 is a reply to message #345987] Fri, 05 September 2008 09:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>That means it may happen that someone who have the access has enabled the parameter _index_join_enabled?

Seems it is set to TRUE by default in 9i.

[Updated on: Fri, 05 September 2008 09:16]

Report message to a moderator

Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #346237 is a reply to message #346000] Sun, 07 September 2008 21:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What am I missing? Is there something WRONG with Oracle using a bitmap access path to combine b-tree indexes?

The alternative is single index scan or full table scan. Is that somehow better?

Ross Leishman
Re: Why showing BITMAP in Plan while no bitmap exists for the table [message #346489 is a reply to message #346237] Mon, 08 September 2008 13:13 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I as well, in Oracle 9.2.0.3, have seen some good performance in
the implicit bit map conversion (i have an explain plan from
a larger sql saved somewhere i must dig up to show)

I jump ahead with this post as I have wanted to post a few questions on this topic but want to note what I found
(or think I found).

The alternative it seemed was to use concatanation - for an 'OR' condition, concatonate each criteria using a UNION ALL.

Some where I read Oracle in a 9.2 sub-release can be too
"liberal" in its application of the bitmap conversion; however, trial and compare resolves for the best way.

Ok, seems like I should get my SQL and plan because interesting was that the bitmap conversion to join indices in this manner
skipped a sort step when merging results into an inline view.

Regards
Harry
Previous Topic: lobg running query
Next Topic: Use of Index
Goto Forum:
  


Current Time: Tue Dec 06 04:24:24 CST 2016

Total time taken to generate the page: 0.29151 seconds