Infrastructure Software

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

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.

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.

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.

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

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?

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

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.

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?

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

I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.

Regards,

>
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?.
>
Why would Oracle use that index to retrieve 1700 rows of an 1800 row table? That doesn't make any sense.

As you already showed Oracle will use the plan with the lowest cost and that is the full table scan.

>
I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.
>
See my full reply in this thread
2399392
>
ETL is affected because DML operations (INSERT/UPDATE/DELETE) on tables with bitmapped indexes can have serious performance issues due to the serialization involved. Updating a single bit-mapped column value (e.g. from 'M' to 'F' for gender) requires both bitmapped index blocks to be locked until the update is complete. A bitmap index stored ROWID ranges (min rowid - max rowid) than can span many, many records. The entire 'range' of rowids is locked in order to change just one value.

To change from 'M' the 'M' rowid range for that one row is locked and the ROWID must be removed from the range byt clearing the bit. To change to 'F' the 'F' rowid id range needs to be found, locked and the bit set that corresponds to that rowid. No other rows with rowids in the range can be changed since this is a serial operation. If the range includes 1000 rows and they all need changed it takes 1000 serial operations.

>
BITMAP indexes are created and are most appropriate for columns having low distinct values.
>
That MYTH has been busted for many years now by many people.

See Richard Foote's series of articles about this myth and many others regarding indexes.
http://richardfoote.wordpress.com/2010/02/18/myth-bitmap-indexes-with-high-distinct-columns-blow-out/
>
One of the great myths in Oracle is that bitmap indexes are only suitable and should only be used with columns that have so-called low cardinality (few distinct) values
. . .

did you try a normal index? the bitmap is usefull for safe space when you have few values (is not your problem becuase the table is small), and because you can use more than one (with bitmap you can acces by the table by more than one index, with normal optimezer have to choose one, this is correct for you because you have one condition)

did you collect statistics on the table?

USED in DW is fine.. I am not arguing that..

But learnt something from that link.. Thanks RP. This completely changed my view about BITMAP indexes.
I like the last sentance there :

The next time you unfortunately read that bitmap indexes should only be used with very “few” distinct values and would be “huge” otherwise, well hopefully you’ll appreciate that’s simply not correct.

;)

I should have read thoroughly the documentation where it says :

http://docs.oracle.com/cd/B28359_01/server.111/b28313/indexes.htm

A gender column, which has only two distinct values (male and female), is optimal for a bitmap index. However, data warehouse administrators also build bitmap indexes on columns with higher cardinalities.

Thanks for sharing!!!

Cheers,
Manik.

Sorry for the late response. rp0428's post is clear enough for you to understand the issue.

You could also go through this discussion at asktom : Bitmap indexes and locking

Thank you all for sharing your ideas. im little bit confused with this concepts . i will so through with the links shared.

Thanks again for all.

Edited by: 887268 on Apr 6, 2013 1:39 AM