Using index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Using index

  1. #1
    Join Date
    May 2000
    Location
    India
    Posts
    18

    Using index

    Hi All,

    I have a query, performs full scan. I created a BITMAP index on the joined columns of the table as cardinality is very low. The query is still using full scan. Later on I dropped and recreated the index Normal B*Tree index. Now the query is using Index. What could be the reason for not using Index Scan for BITMAP Index?

    Thank you very much in advance.

    Regards,
    Bhaskara

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Hi

    How many distinct values do you have on the column on which you have created the BITMAP index

    Regards
    Amar
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Are you using version 9?

    Is it a star schema design?
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Which optimizer mode are you using?... Try passing hint to the query. and hope you are not using less than or greater than comparison operators. Bitmapped indexes are only useful for AND, OR, NOT, or equality queries.

    Code:
    SQL> CREATE TABLE tab1 (id number, gender varchar2(1));
    Table created.
    SQL>  CREATE TABLE tab2  (id number, gender varchar2(1));
    Table created.
    SQL> INSERT INTO tab1 VALUES (1,'F');
    1 row created.
    SQL> INSERT INTO tab1 VALUES (1,'M');
    1 row created.
    SQL> INSERT INTO tab1 VALUES (1,'-');
    1 row created.
    :
    :
    SQL> INSERT INTO tab2 SELECT * FROM tab1;
    
    Then I inserted further junk records..
    
    SQL> CREATE BITMAP INDEX ixtab1 ON tab1 (gender);
    
    Index created.
    
    SQL> CREATE BITMAP INDEX ixtab2 ON tab2 (gender);
    
    Index created.
    
    SQL> SELECT a.id, b.gender FROM tab1 a, tab2 b 
       2 WHERE a.gender = b.gender 
       3 AND a.gender = 'M';
    
    1048576 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   MERGE JOIN
       2    1     SORT (JOIN)
       3    2       TABLE ACCESS (FULL) OF 'TAB2'
       4    1     SORT (JOIN)
       5    4       TABLE ACCESS (FULL) OF 'TAB1'
    
    
    SQL> SELECT /*+ INDEX (a ixtab1) */ a.id, b.gender 
       2 FROM tab1 a, tab2 b 
       3 WHERE a.gender = b.gender ;
    
    1048576 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=1673 Bytes=2
              8441)
    
       1    0   HASH JOIN (Cost=38 Card=1673 Bytes=28441)
       2    1     TABLE ACCESS (FULL) OF 'TAB2' (Cost=1 Card=409 Bytes=818
              )
    
       3    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=35 Card=40
              9 Bytes=6135)
    
       4    3       BITMAP CONVERSION (TO ROWIDS)
       5    4         BITMAP INDEX (FULL SCAN) OF 'IXTAB1'
    Sameer

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    sameer if you analyze your table you probably dont have to use index hint

  6. #6
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by pando
    sameer if you analyze your table you probably dont have to use index hint
    Salut :-)

    Code:
    SQL> SELECT a.id, b.gender FROM tab1 a, tab2 b 
      2  WHERE a.gender = b.gender ;
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   MERGE JOIN
       2    1     SORT (JOIN)
       3    2       TABLE ACCESS (FULL) OF 'TAB2'
       4    1     SORT (JOIN)
       5    4       TABLE ACCESS (FULL) OF 'TAB1'
    
    
    
    SQL> SELECT /*+ INDEX (a ixtab1) */ a.id, b.gender 
      2  FROM tab1 a, tab2 b 
      3  WHERE a.gender = b.gender ;
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=67 Bytes=113
              9)
    
       1    0   HASH JOIN (Cost=37 Card=67 Bytes=1139)
       2    1     TABLE ACCESS (FULL) OF 'TAB2' (Cost=1 Card=82 Bytes=164)
       3    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=34 Card=82
               Bytes=1230)
    
       4    3       BITMAP CONVERSION (TO ROWIDS)
       5    4         BITMAP INDEX (FULL SCAN) OF 'IXTAB1'
    
    
    
    SQL> ANALYZE TABLE tab1 ESTIMATE STATISTICS;
    
    Table analyzed.
    
    SQL> SELECT a.id, b.gender FROM tab1 a, tab2 b 
      2  WHERE a.gender = b.gender ;
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=31 Bytes=155)
       1    0   NESTED LOOPS (Cost=2 Card=31 Bytes=155)
       2    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=2 Card=3072 Bytes=92
              16)
    
       3    1     BITMAP CONVERSION (TO ROWIDS)
       4    3       BITMAP INDEX (SINGLE VALUE) OF 'IXTAB2'
    Thanks

    Sameer

  7. #7
    Join Date
    May 2000
    Location
    India
    Posts
    18
    I am using Oracle8i with version 8.1.7 with Cost based optimizer.

    I have a table FLT_FLEET_ALARM, contains 88409 records. The column ALARM_TYPE contains on 4 distinct values. I have already analyzed the table.

    Here is the query I am using.

    SELECT MAX(MESSAGE_SEQ_ID) FROM FLT_FLEET_ALARM WHERE ALARM_TYPE IN ('A/C FAULT','ENGINE FAULT','EXCEEDANCE')

    I created B*Tree index I1 on ALARM_TYPE and below is the execution plan.

    Query Plan
    ----------------------------------------------------------------------------------------------------
    SELECT STATEMENT Cost = 233
    SORT AGGREGATE
    INLIST ITERATOR
    TABLE ACCESS BY INDEX ROWID FLT_FLEET_ALARM
    INDEX RANGE SCAN I1

    Then, I recreated Index I1 as BITMAP Index and below is the execution plan.

    Query Plan
    ----------------------------------------------------------------------------------------------------
    SELECT STATEMENT Cost = 424
    SORT AGGREGATE
    TABLE ACCESS FULL FLT_FLEET_ALARM

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    after create index, analyze it

  9. #9
    Join Date
    May 2000
    Location
    India
    Posts
    18
    Yes, I analyzed the index... Query is still using FULL SCAN.

  10. #10
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    I guess because of MAX function it is doing FTS though we Analyze the table.. Try suppling hint to query

    Code:
    SQL> DROP INDEX ixtab1;
    
    Index dropped.
    
    SQL> CREATE BITMAP INDEX ixtab1 ON tab1 (gender);
    
    Index created.
    
    SQL> ANALYZE INDEX ixtab1 ESTIMATE STATISTICS;
    
    Index analyzed.
    
    SQL> SELECT MAX(id) FROM tab1 WHERE gender IN ('M','F','-');
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=47 Card=1 Bytes=5)
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=47 Card=172032 Bytes
              =860160)
    
    
    SQL> SELECT /*+INDEX (tab1 ixtab) */  MAX(id) FROM tab1 WHERE gender IN ('M','F','-');
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=466 Card=1 Bytes=5)
       1    0   SORT (AGGREGATE)
       2    1     INLIST ITERATOR
       3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=466 Card
              =172032 Bytes=860160)
    
       4    3         BITMAP CONVERSION (TO ROWIDS)
       5    4           BITMAP INDEX (SINGLE VALUE) OF 'IXTAB1'
    Last edited by Sameer; 12-30-2002 at 07:33 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width