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!

Do you know how Oracle uses indexes? The explain plan you ran for each query shows what?

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)

>> 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.

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




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.

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:



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?

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.

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.

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!

Logon to the server directly and run this. Or, run it in SQL*Plus on a client. Do not use Toad.

- 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?



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.

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.

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.

Well querying each column individually seems to work just fine, the performance issue only crops up when both columns are included in the WHERE clause.

Hi Andy,

>> Bitmap indexes work better with only two or three discrete values in this type of scenario.

Good catch, I missed that. Usually, any bitmap with over 500 distinct values is problematic.

****************************************************
To the OP: (what's your name, anyway?)

- Is this a production database?
- The IOT is read-only, right?
- Why did you chose an IOT with bitmap indexing?
- How often is the IOT refreshed, and the bitmaps rebuilt?

As to the performance issue, try the one with two bitmaps, using the index_combine hint:

(Note the alias for the table "d"):



The docs note:



***********************************************************
>> And thanks Don -- I really appreciate you taking the time to look into this matter.

No problem, this is interesting . . . .

This is a development database. It's a data warehouse database, and the tables get updated on an ad-hoc basis due to the fact that it's a development environment. These very same tables get reloaded and analyzed daily on the test and prod environment -- and the test server has the same performance issue with this customer table as the development box. The production is a much bigger server so it's hard to compare the performance between that and the dev and test environments.

Although the customer table is reloaded daily (on test & prod. and about once a week on dev) using partition exchange, so no updates ever occur on the table itself. Bitmap indexing has been used since it appears to give the best performance even though the cardinality is higher than optimum (I've tried the conventional BTree indexes for comparision).

Using the index_combine hint, I get the same explain plan that the CBO picks on it's own:

SELECT STATEMENT
SORT(AGGREGATE)
PARTITION RANGE(SINGLE)
INDEX(UNIQUE SCAN) WHDATA.CUSTOMER_DIM_PK
BITMAP CONVERSION(TO ROWIDS)
BITMAP AND
BITMAP INDEX(SINGLE VALUE)
BITMAP INDEX(SINGLE VALUE)

Cost: 2

The run time hasn't changed.

Hi,

>> The run time hasn't changed.

Thanks for the update.

We may never know the whole story because Oracle carefully protects the optimizer internal machinations (and the source code), and the best that we can do is run tests and speculate on the CBO's behavior!

Sorry, that we sould not come-up with a more definitive answer for you . . . .