composite Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: composite Index

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    301

    composite Index

    Hi all,

    if i h've composite index. How is it evaluate on a query?
    when i use only one column from this index or two columns in the WHERE
    clause.

    Thank's
    Bensmail

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hope following examples helps you...

    Code:
    CREATE TABLE test (id number, id1 number, name varchar2(10))
    
    CREATE INDEX ix_test ON test (id, id1)
    
    I have created composite index on id and id1;
    
    INSERT INTO test VALUES (1,1,'one');
    INSERT INTO test VALUES (2,2,'two');
    INSERT INTO test VALUES (1,3,'three');
    INSERT INTO test VALUES (2,4,'four');
    INSERT INTO test VALUES (1,5,'five');
    INSERT INTO test VALUES (2,6,'six');
    INSERT INTO test VALUES (1,7,'seven');
    INSERT INTO test VALUES (2,9,'eight');
    INSERT INTO test VALUES (3,3,'nine');
    INSERT INTO test VALUES (3,4,'ten');
    
    COMMIT;
    
    See following SELECT statements how optimizer uses composite index
    
    SQL> SELECT * FROM test WHERE id = 1;                     
                                                              
            ID        ID1 NAME                                
    ---------- ---------- ----------                          
             1          1 one                                 
             1          3 three                               
             1          5 five                                
             1          7 seven                               
                                                              
                                                              
    Execution Plan                                            
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE               
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'       
       2    1     INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)
                                                              
                                                              
                                                              
    SQL> SELECT * FROM test WHERE id1 = 1;                    
                                                              
            ID        ID1 NAME                                
    ---------- ---------- ----------                          
             1          1 one                                 
                                                              
                                                              
    Execution Plan                                            
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE               
       1    0   TABLE ACCESS (FULL) OF 'TEST'                 
                                                              
    
    SQL> SELECT /*+ INDEX (test, ix_test) */ * FROM test WHERE id1 = 1;
    
            ID        ID1 NAME
    ---------- ---------- ----------
             1          1 one
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=33)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=34 Card=1 By
              tes=33)
    
       2    1     INDEX (FULL SCAN) OF 'IX_TEST' (NON-UNIQUE) (Cost=26 Car
              d=1)
                                                              
                                                              
    SQL> SELECT * FROM test WHERE id = 1 and id1 = 5;         
                                                              
            ID        ID1 NAME                                
    ---------- ---------- ----------                          
             1          5 five                                
                                                              
                                                              
    Execution Plan                                            
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE               
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'       
       2    1     INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)
                                                              
                                                              
                                                              
    SQL> SELECT * FROM test WHERE id1 = 1 and id = 1;         
                                                              
            ID        ID1 NAME                                
    ---------- ---------- ----------                          
             1          1 one                                 
                                                              
                                                              
    Execution Plan                                            
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE               
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'       
       2    1     INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)
    Whenever leading composite index column is in a WHERE condition, it uses index. Else you can force query to use index by supplying a hint. The order of the columns used in the definition is important. Generally, the most commonly accessed or most selective columns go first.


    Sameer
    Last edited by Sameer; 12-09-2002 at 10:39 AM.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you forgot to analyze the table, in 9i it may or may not skip the use of index but in order to use that you have to analyze the table, in your example RULE is used

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