| Why showing BITMAP in Plan while no bitmap exists for the table [message #345944] |
Fri, 05 September 2008 06:37  |
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 #345973 is a reply to message #345961] |
Fri, 05 September 2008 07:40  |
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 #346489 is a reply to message #346237] |
Mon, 08 September 2008 13:13 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
