Forum Stats

  • 3,759,174 Users
  • 2,251,510 Discussions
  • 7,870,525 Comments

Discussions

Bitmap index column goes for full table scan

890271
890271 Member Posts: 132
edited Apr 6, 2013 4:39AM in SQL & PL/SQL
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
Tagged:
«1

Answers

  • jeneesh
    jeneesh Member Posts: 7,168
    edited Apr 4, 2013 2:25AM
    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
    BEDE Oracle Developer Member Posts: 2,283 Gold Trophy
    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
    BEDE Oracle Developer Member Posts: 2,283 Gold Trophy
    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
    Manik Member Posts: 2,906 Gold Trophy
    edited Apr 4, 2013 2:21AM
    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
    jeneesh Member Posts: 7,168
    edited Apr 4, 2013 2:42AM
    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
    890271 Member Posts: 132
    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
    890271 Member Posts: 132
    edited 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
    Paul Horth Member Posts: 3,402 Gold Trophy
    edited Apr 4, 2013 3:11AM
    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
    jeneesh Member Posts: 7,168
    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
    bencol Member Posts: 894 Bronze Badge
    ..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
This discussion has been closed.