This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 6, 2013 1:39 AM by 890271 RSS

Bitmap index column goes for full table scan

890271 Newbie
Currently Being Moderated
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
  • 1. Re: Bitmap index column goes for full table scan
    jeneesh Guru
    Currently Being Moderated
    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
  • 2. Re: Bitmap index column goes for full table scan
    BEDE Explorer
    Currently Being Moderated
    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.
  • 3. Re: Bitmap index column goes for full table scan
    BEDE Explorer
    Currently Being Moderated
    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.
  • 4. Re: Bitmap index column goes for full table scan
    Manik Expert
    Currently Being Moderated
    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.
  • 5. Re: Bitmap index column goes for full table scan
    jeneesh Guru
    Currently Being Moderated
    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..
  • 6. Re: Bitmap index column goes for full table scan
    890271 Newbie
    Currently Being Moderated
    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?
  • 7. Re: Bitmap index column goes for full table scan
    890271 Newbie
    Currently Being Moderated
    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
  • 8. Re: Bitmap index column goes for full table scan
    Paul Horth Expert
    Currently Being Moderated
    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.
  • 9. Re: Bitmap index column goes for full table scan
    jeneesh Guru
    Currently Being Moderated
    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?
  • 10. Re: Bitmap index column goes for full table scan
    bencol Pro
    Currently Being Moderated
    ..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
  • 11. Re: Bitmap index column goes for full table scan
    34MCA2K2 Journeyer
    Currently Being Moderated
    jeneesh wrote:

    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.
    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,
  • 12. Re: Bitmap index column goes for full table scan
    rp0428 Guru
    Currently Being Moderated
    >
    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.
  • 13. Re: Bitmap index column goes for full table scan
    rp0428 Guru
    Currently Being Moderated
    >
    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
    Bitmap index for FKs on Fact tables
    >
    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.
  • 14. Re: Bitmap index column goes for full table scan
    rp0428 Guru
    Currently Being Moderated
    >
    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
    . . .
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points