Bitmap index column goes for full table scan

890271 · Apr 4, 2013 2:01AM

Hi all,

Database : 10g R2
OS : Windows xp

my select query is :

SELECT tran_id, city_id, valid_records
FROM transaction_details
WHERE type_id=101;

And the Explain Plan is :

Plan
SELECT STATEMENT ALL_ROWSCost: 29 Bytes: 8,876 Cardinality: 634
1 TABLE ACCESS FULL TABLE TRANSACTION_DETAILS** Cost: 29 Bytes: 8,876 Cardinality: 634

total number of rows in the table = 1800 ;
distinct value of type_ids are 101,102,103

so i created a bit map index on it.

CREATE BITMAP INDEX btmp_typeid ON transaction_details
(type_id)
LOGGING
NOPARALLEL;

after creating the index, the explain plan shows the same. why it goes for full table scan?.

Kindly share ur idea on this.

Edited by: 887268 on Apr 3, 2013 11:01 PM

Edited by: 887268 on Apr 3, 2013 11:02 PM

jeneesh · Apr 4, 2013 2:05AM

So the total records is 1800. The output count is 600..

Full Table Scan is not a bad thing here..

You can force index usage by INDEX hint and check what is the plan , cost and details.. (Just see the details)

You will probably understand the difference then..

Full scans are NOT EVIL

BEDE · Apr 4, 2013 2:11AM

Try using the hint:

SELECT /*+ index(t  btmp_typeid) */ tran_id, city_id, valid_records 
 FROM transaction_details t
 WHERE type_id=101

Still, if there are lots of transactions on that table, it is not good to have bitmap indexes. Bitmap indexes are to be used on tables where you have few transactions, generally in database warehouses.
Moreover, are you sure you have Oracle Enterprise database? If it is not Enterprise, then it does not create bitmap indexes, but only tree indexes will be created even when you specify bitmap.

BEDE · Apr 4, 2013 2:16AM

Moreover, bitmap indexes are generally used in database warehouses for partitioned tables and are also local indexes, so that a local index may be rebuilt on a given partition, not on the whole table which will be time consuming.
And, if you do not use hints, the optimizer will take the latest DB statistics available to make an execution plan for your query. If you just created the index and have not run statistics, then the index may not have been analyzed.
And, if you have few rows in a table, then a fullscan may run faster than an index range scan. Even having the statistics up to date, the optimizer may decide a fullscan will run faster.

Manik · Apr 4, 2013 2:20AM

BEDE wrote:
Moreover, bitmap indexes are generally used in database warehouses for partitioned tables and are also local indexes,

I guess this is incorrect statment..BITMAP indexes are created and are most appropriate for columns having low distinct values.
I would not recommend index hint here because optimizer knows the best way to generate explain plan..

May be OP has to gather stats on his table after creating index. But yes if the table is small then the optimizer thinks better to do FTS sometimes.

Cheers,
Manik.

jeneesh · Apr 4, 2013 2:40AM

Manik wrote:

BEDE wrote:
Moreover, bitmap indexes are generally used in database warehouses for partitioned tables and are also local indexes,

I guess this is incorrect statment..BITMAP indexes are created and are most appropriate for columns having low distinct values.

Normally BITMAP indexes are not much advisable for OLTP systems.. It will cause a lot of locking issues - For example, multiple rows may get locked during a single row UPDATE.

BITMAP are mainly used in DW systems where the DMLs will be in batch and wont be much frequent..

890271 · Apr 4, 2013 2:55AM

Thanks BEDE,

using the hint query i got this plan,

Plan
SELECT STATEMENT ALL_ROWS*Cost: 149* Bytes: 8,876 Cardinality: 634
3 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 149 Bytes: 8,876 Cardinality: 634
2 BITMAP CONVERSION TO ROWIDS
1 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) BTMAP

Now the cost is increased from 29 to 149. why... it is supposed to be lesser than 29.. am i correct or wrong?

890271 · Apr 4, 2013 2:58AM

thanks jeneesh,

posted the plan above for the query with index hint. Kindly explain me why its greater now.

kindly help me.

Edited by: 887268 on Apr 3, 2013 11:58 PM

Paul Horth · Apr 4, 2013 3:10AM

887268 wrote:
Thanks BEDE,

using the hint query i got this plan,

Plan
SELECT STATEMENT ALL_ROWS*Cost: 149* Bytes: 8,876 Cardinality: 634
3 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 149 Bytes: 8,876 Cardinality: 634
2 BITMAP CONVERSION TO ROWIDS
1 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) BTMAP

Now the cost is increased from 29 to 149. why... it is supposed to be lesser than 29.. am i correct or wrong?

The cost is greater because index access is worse than a full table scan in this instance. Do not use the hint. Let Oracle decide.

Please read what other have written. Full table scans are not evil. Often index access can be worse.

jeneesh · Apr 4, 2013 3:13AM

887268 wrote:

Now the cost is increased from 29 to 149. why... it is supposed to be lesser than 29.. am i correct or wrong?

You are not correct ..

Think of a Simple example:

I have a small table - 100 rows . In the database all these rows are residing in a single data block (Assume)

And there is an index on COL1. Suppose the index also is in another single block.

Now, if you have query

select *
from table
where  col1 = something;

Suppose this will return 20 rows.

Now if ORACLE is going for a Full Table scan => How many blocks of data it has to read ? Answer is 1 coz, the whole data is residing in a single block.

If oracle is going for an INDEX SCAN, it has to read 2 blocks of data - Firstly the INDEX Block to find out the ROWIDs, and then the TABLE BLOCK to find the rows...

So, which is better? Which is of less cost?

bencol · Apr 4, 2013 5:17AM

..BITMAP indexes are created and are most appropriate for columns having low distinct values.

Agree with you, but I would go futher and say that bitmap indexes are most appropriate where they can be combined. If there are low distinct values, then a single one is not going to provide much advantage over a full table scan. I have found them useful in data warehouses as you can control the writes, minimising the contention, and there can be many more distinct queries, so deciding a good selection of btree indexes can be difficult. They are also useful for aggregations, if you are grouping on a bitmap indexed column.

Ben

Forum Stats

Quick Links

Categories

Bitmap index column goes for full table scan

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Bitmap index column goes for full table scan

Answers