Home » RDBMS Server » Performance Tuning » BITMAP CONVERSION FROM/TO ROWIDS (10g Enterprise Edition Release 10.2.0.1.0)
BITMAP CONVERSION FROM/TO ROWIDS [message #391162] Wed, 11 March 2009 05:17 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi Friends,

I have an SQL statement as given below:

select	SUM(DECODE(a.year_s, 2003, a.MONTH_01, 0)) AmntPct 
from	table_1 a 
where	a.FID=100 
and	a.OID=5000 
and	a.CID=300 
and	a.TID=0 
and	a.year_s IN (2003) 
and	a.curr = 'USD'


When i see the plan o/p for the above query it looks like
1	Plan hash value: 1222490892
2	 
3	---------------------------------------------------------------------------------------------------
4	| Id  | Operation                         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
5	---------------------------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT                  |               |     1 |    19 |    40   (3)| 00:00:01 |
7	|   1 |  SORT AGGREGATE                   |               |     1 |    19 |            |          |
8	|*  2 |   TABLE ACCESS BY INDEX ROWID     | table_1   	  |    11 |   209 |    40   (3)| 00:00:01 |
9	|   3 |    BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
10	|   4 |     BITMAP AND                    |               |       |       |            |          |
11	|   5 |      BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
12	|*  6 |       INDEX RANGE SCAN            | table_1_6     |       |       |     8   (0)| 00:00:01 |
13	|   7 |      BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
14	|*  8 |       INDEX RANGE SCAN            | table_1_4     |       |       |    12   (0)| 00:00:01 |
15	|   9 |      BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
16	|* 10 |       INDEX RANGE SCAN            | table_1_3     |       |       |    14   (0)| 00:00:01 |
17	---------------------------------------------------------------------------------------------------
18	 
19	Predicate Information (identified by operation id):
20	---------------------------------------------------
21	 
22	   2 - filter("A"."TID"=0 AND "A"."YEAR_S"=2003 AND "A"."CURR"='USD')
23	   6 - access("A"."FID"=100)
24	   8 - access("A"."OID"=5000)
25	  10 - access("A"."CID"=300)


Since BITMAP CONVERSION FROM/TO ROWIDS were involved, based on some suggestions given in the net i set the flag "_b_tree_bitmap_plans" to false. Then my plan output was different as show below:

1	Plan hash value: 1421338446
2	 
3	----------------------------------------------------------------------------------
4	| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
5	----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT   |             |     1 |    19 |   153   (5)| 00:00:02 |
7	|   1 |  SORT AGGREGATE    |             |     1 |    19 |            |          |
8	|*  2 |   TABLE ACCESS FULL| table_1	 |    11 |   209 |   153   (5)| 00:00:02 |
9	----------------------------------------------------------------------------------
10	 
11	Predicate Information (identified by operation id):
12	---------------------------------------------------
13	 
14	   2 - filter("A"."FID"=100 AND "A"."OID"=5000 AND 
15	              "A"."CID"=300 AND "A"."TID"=0 AND "A"."YEAR_S"=2003 
16	              AND "A"."CURR"='USD')

Please suggest me what i really be doing here for good performance of the query.

Thanks so much for reading the big post!

[Updated on: Wed, 11 March 2009 05:48]

Report message to a moderator

Re: BITMAP CONVERSION FROM/TO ROWIDS [message #391196 is a reply to message #391162] Wed, 11 March 2009 07:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Well,
the difference is .01 second.
Bitmap conversion from rowid is just a behavior. Not a problem, in most cases.
Re: BITMAP CONVERSION FROM/TO ROWIDS [message #391346 is a reply to message #391196] Wed, 11 March 2009 21:05 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
All those columns in a single index would probably give you the best performance.

Ross Leishman
Previous Topic: Effective SQL
Next Topic: Issue of Space as Data is growing Rapidly.
Goto Forum:
  


Current Time: Mon Dec 05 04:45:03 CST 2016

Total time taken to generate the page: 0.25760 seconds