Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
2 Pages V   1 2 >  
Reply to this topicStart new topic
> Single-column indexe performance vs. Composite Index Performance
xl5
post Jul 31 2008, 02:12 PM
Post #1


Newbie
*

Group: Members
Posts: 9
Joined: 31-July 08
Member No.: 30,196



I have an IOT table of about 6 million rows when queried return about 3 million rows total. I have bitmap indexes on each of these two columns, and the query explain plan shows that those indexes are being used. In this test however I am only getting back a tiny number of rows since the query's being run interactively in Toad.

Query: SELECT * from MY_TABLE where ColA = TO_DATE(value) AND ColB = 'Y'

Scenario A: One bitmap index on ColA and another bitmap index on ColB. Total of 2 indexes.
Cost: 2
Time to query (in Toad): 20 min

Scenario B: I added a composite bitmap index comprising of (ColA,ColB) to the table.
Cost: 1
Time to query (in Toad) <1 second.

My question is this: Why does it take SO much longer (20 min vs. less than 1 second) for Oracle to run in the scenario A above compared to B? Common sense would suggest a negligible difference between the two approaches in terms of run time.

Thanks in advance for your help!
Go to the top of the page
 
+Quote Post
SteveC
post Jul 31 2008, 02:40 PM
Post #2


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



Do you know how Oracle uses indexes? The explain plan you ran for each query shows what?
Go to the top of the page
 
+Quote Post
xl5
post Jul 31 2008, 03:32 PM
Post #3


Newbie
*

Group: Members
Posts: 9
Joined: 31-July 08
Member No.: 30,196



WITH COLUMN INDEXES ONLY (NO COMPOSITE INDEX)

Oracle uses the following plan for when I query just one of the two columns:

SELECT STATEMENT
SORT(AGGREGATE)
PARTITION RANGE(SINGLE)
BITMAP CONVERSION(COUNT)
BITMAP INDEX(SINGLE VALUE)

When I query both columns, I get the following:

SORT(AGGREGATE)
PARTITION RANGE(SINGLE)
INDEX(UNIQUE SCAN) WHDATA.CUSTOMER_DIM_PK
BITMAP CONVERSION(TO ROWIDS)
BITMAP AND
BITMAP INDEX(SINGLE VALUE) [for col1]
BITMAP INDEX(SINGLE VALUE) [for col2]

WITH COMPOSITE INDEX

SELECT STATEMENT
SORT(AGGREGATE)
PARTITION RANGE(SINGLE)
BITMAP CONVERSION(COUNT)
BITMAP INDEX(SINGLE VALUE)

QUOTE (SteveC @ Jul 31 2008, 03:41 PM) *
Do you know how Oracle uses indexes? The explain plan you ran for each query shows what?
Go to the top of the page
 
+Quote Post
burleson
post Jul 31 2008, 07:53 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,734
Joined: 26-January 04
Member No.: 13



>> Why does it take SO much longer

Obviously, becaus it is reading every block in the IOT.

Can you post the exact SQL and run them again in SQL*Plus with "set autotrace on"?

That will give more details on I/O.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
xl5
post Aug 1 2008, 09:10 AM
Post #5


Newbie
*

Group: Members
Posts: 9
Joined: 31-July 08
Member No.: 30,196



With composite index

SQL> select count(1) from whdata.customer_dim where current_record_flg = 'Y' and effective_start_dt = to_date('19000101','YYYYMMDD');

COUNT(1)
----------
1712628

Elapsed: 00:00:01.57

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (SINGLE) (Cost=1 Card=7183 Bytes=57464)
3 2 BITMAP CONVERSION (COUNT) (Cost=1 Card=7183 Bytes=5746
4)

4 3 BITMAP INDEX (SINGLE VALUE) OF 'COMPOSITE_IDX_TEMP_B' (INDEX (BIT
MAP))





Statistics
----------------------------------------------------------
7903 recursive calls
0 db block gets
2449 consistent gets
249 physical reads
0 redo size
218 bytes sent via SQL*Net to client
280 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
197 sorts (memory)
0 sorts (disk)
1 rows processed


Without composite index

SQL> select count(1) from whdata.customer_dim where current_record_flg = 'Y' and effective_start_dt = to_date('19000101','YYYYMMDD');

COUNT(1)
----------
1712628

Elapsed: 00:18:56.62

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (SINGLE) (Cost=2 Card=7183 Bytes=57464)
3 2 INDEX (UNIQUE SCAN) OF 'CUSTOMER_DIM_PK' (INDEX (UNIQU
E)) (Cost=2 Card=7183 Bytes=57464)

4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP AND
6 5 BITMAP INDEX (SINGLE VALUE) OF 'CUSTOMER_DIM_B5_
R' (INDEX (BITMAP))

7 5 BITMAP INDEX (SINGLE VALUE) OF 'CUSTOMER_DIM_B1_
R' (INDEX (BITMAP))





Statistics
----------------------------------------------------------
395 recursive calls
0 db block gets
5142876 consistent gets
87890 physical reads
0 redo size
234 bytes sent via SQL*Net to client
280 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> quit

QUOTE (burleson @ Jul 31 2008, 08:54 PM) *
>> Why does it take SO much longer

Obviously, becaus it is reading every block in the IOT.

Can you post the exact SQL and run them again in SQL*Plus with "set autotrace on"?

That will give more details on I/O.



It is also interesting that it is doing more than 5 million gets when using 2 bitmap indexes. One would think that since the SQL contains an "AND" condition, Oracle would do an index scan on the first condition and use that resultset to run the next condition, thereby querying a much smaller subset of records, and not scan through almost the entire table.
Go to the top of the page
 
+Quote Post
burleson
post Aug 1 2008, 11:33 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,734
Joined: 26-January 04
Member No.: 13



Hi,

This is an interesting problem! I've asked a SQL guru to pop-in and advise you.

It's no suprise that SQL is faster then using a composite index, especially with bitmaps.

Check this out:

http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm

********************************************************************************
**
>> It is also interesting that it is doing more than 5 million gets when using 2 bitmap indexes.

Yes! But even more, note the disk reads! Remember, disk reads are the events that take the most time:

CODE
249 physical reads
87890 physical reads


Usually, the "fastest" SQL is the one that fetches the right results with the least buffer touches (consistent gets).

Back to your original questions:

>> Why does it take SO much longer (20 min vs. less than 1 second) for Oracle to run in the scenario A above compared to B?

Because the composite bitmap has already done the matching for you, right?

>> Common sense would suggest a negligible difference between the two approaches in terms of run time.

How so? What is your ressoning on this?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
xl5
post Aug 1 2008, 12:07 PM
Post #7


Newbie
*

Group: Members
Posts: 9
Joined: 31-July 08
Member No.: 30,196



QUOTE (burleson @ Aug 1 2008, 12:34 PM) *
>> Common sense would suggest a negligible difference between the two approaches in terms of run time.

How so? What is your ressoning on this?


The reasoning is that if I query just one of the two columns, it takes less than a second to come back with the result. We also know that if I query both the columns, Oracle can create a result set for the first column, then using the other bitmap index, query the result set of the first query in order to apply the condition of the 2nd column. Of course, it doesn't seem to be doing that in practice.

And thanks Don -- I really appreciate you taking the time to look into this matter.
Go to the top of the page
 
+Quote Post
burleson
post Aug 1 2008, 12:10 PM
Post #8


Advanced Member
***

Group: Members
Posts: 11,734
Joined: 26-January 04
Member No.: 13



Hi again,

- What exact relase are you on?

- Have you checked MetaLink for bugs?

We must also recall that Oracle changed the requirements for the STAR transformation from using single column bitmaps to a single composite bitmap (with all fact table columns included), so perhaps this is related to the huge performance differences that you have observed.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
SteveC
post Aug 1 2008, 12:15 PM
Post #9


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



When you say without composite index, does that mean you dropped it? The IOT table is based on which column? What are the comparisons when run on the server AND not in Toad?
Go to the top of the page
 
+Quote Post
xl5
post Aug 1 2008, 12:25 PM
Post #10


Newbie
*

Group: Members
Posts: 9
Joined: 31-July 08
Member No.: 30,196



QUOTE (SteveC @ Aug 1 2008, 01:16 PM) *
When you say without composite index, does that mean you dropped it? The IOT table is based on which column? What are the comparisons when run on the server AND not in Toad?


That is correct -- I dropped the composite index prior to running the SQL query again.

The IOT is based on CUSTOMER_DIM_ID, the surrogate key of each row in the table.

I am not sure what you mean by run on the server and not in Toad... can you please clarify?

Thanks!
Go to the top of the page
 
+Quote Post
SteveC
post Aug 1 2008, 12:29 PM
Post #11


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



Logon to the server directly and run this. Or, run it in SQL*Plus on a client. Do not use Toad.
Go to the top of the page
 
+Quote Post
xl5
post Aug 1 2008, 12:32 PM
Post #12


Newbie
*

Group: Members
Posts: 9
Joined: 31-July 08
Member No.: 30,196



QUOTE (burleson @ Aug 1 2008, 01:11 PM) *
Hi again,

- What exact relase are you on?

- Have you checked MetaLink for bugs?

We must also recall that Oracle changed the requirements for the STAR transformation from using single column bitmaps to a single composite bitmap (with all fact table columns included), so perhaps this is related to the huge performance differences that you have observed.


- Version 10.2.0.3.0 Enterprise Edition 64-bit, running on a (relatively) small HP-UX box. 16GB memory allocated to this instance.

- MetaLink has not yielded anything useful.

- I believe the STAR transformation issue wouldn't apply here since I am querying the DIM table by itself, not joining it to any fact table. Is this a correct assumption?

QUOTE (SteveC @ Aug 1 2008, 01:30 PM) *
Logon to the server directly and run this. Or, run it in SQL*Plus on a client. Do not use Toad.


My earlier post at 10:11AM was actually run using SQL*Plus, and I have cut-and-pasted the spooled output here on the forum.
Go to the top of the page
 
+Quote Post
andrew kerber
post Aug 1 2008, 12:53 PM
Post #13


Advanced Member
***

Group: Banned
Posts: 489
Joined: 16-November 07
Member No.: 14,089



How many discrete values are there on the date index?
Go to the top of the page
 
+Quote Post
xl5
post Aug 1 2008, 01:00 PM
Post #14


Newbie
*

Group: Members
Posts: 9
Joined: 31-July 08
Member No.: 30,196



QUOTE (andrew kerber @ Aug 1 2008, 01:54 PM) *
How many discrete values are there on the date index?


467 total number of distinct date values.

However, out of 6.7 million total rows, 3.35 million rows have the date equalling 1900-01-01.
Go to the top of the page
 
+Quote Post
andrew kerber
post Aug 1 2008, 01:28 PM
Post #15


Advanced Member
***

Group: Banned
Posts: 489
Joined: 16-November 07
Member No.: 14,089



The number of discrete values may be part of the problem. Bitmap indexes work better with only two or three discrete values in this type of scenario.
Go to the top of the page
 
+Quote Post

2 Pages V   1 2 >
Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 28th November 2014 - 06:53 PM