1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Index in oracle

Discussion in 'SQL PL/SQL' started by praveenkumarglory, Sep 26, 2011.

  1. praveenkumarglory

    praveenkumarglory Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    pls tell me the types of oracle index in detail.

    consider this situation, if a select query without any where clause takes much time to get data,then we will create index for that table.

    now again run the same select query. this time, whether the query takes much time again or will it work fast?
    whether the index is used for a select query execution or not?

    how we can confirm that whether the oracle using the index normally?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you don't use a WHERE clause it's highly likely the index you create won't be used; the one exception to that would be if your select list exactly matches the columns in the index and at least one of those columns is declared as NOT NULL:

    Code (SQL):
    SQL> CREATE TABLE test1(
      2      c_1 NUMBER NOT NULL,
      3      c_2 varchar2(255)
      4  );
    TABLE created.
    SQL>
    SQL> BEGIN
      2      FOR i IN 1..10 loop
      3       INSERT INTO test1
      4       VALUES (i, 'Test '||i);
      5      END loop;
      6  
      7      commit;
      8  END;
      9  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> CREATE INDEX test1_c_1
      2  ON test1(c_1);
    INDEX created.
    SQL>
    SQL> CREATE INDEX test1_c_2
      2  ON test1(c_2);
    INDEX created.
    SQL>
    SQL> EXEC dbms_stats.gather_table_stats(ownname=>NULL, tabname=>'TEST1', method_opt=>'for all columns size 20', cascade=>TRUE, estimate_percent=>NULL)
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> SELECT c_1 FROM test1;
           C_1                                                                      
    ----------                                                                      
             1                                                                      
             2                                                                      
             3                                                                      
             4                                                                      
             5                                                                      
             6                                                                      
             7                                                                      
             8                                                                      
             9                                                                      
            10                                                                      
    10 ROWS selected.

    Execution Plan
    ----------------------------------------------------------                      
    Plan hash VALUE: 2345822858                                                    
                                                                                   
    ------------------------------------------------------------------------------  
    | Id  | Operation        | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |  
    ------------------------------------------------------------------------------  
    |   0 | SELECT STATEMENT |           |    10 |    30 |     1   (0)| 00:00:01 |  
    |   1 |  INDEX FULL SCAN | TEST1_C_1 |    10 |    30 |     1   (0)| 00:00:01 |  
    ------------------------------------------------------------------------------  

    Statistics
    ----------------------------------------------------------                      
              1  recursive calls                                                    
              0  db block gets                                                      
              2  consistent gets                                                    
              0  physical reads                                                    
              0  redo SIZE                                                          
            509  bytes sent via SQL*Net TO client                                  
            385  bytes received via SQL*Net FROM client                            
              2  SQL*Net roundtrips TO/FROM client                                  
              0  sorts (memory)                                                    
              0  sorts (disk)                                                      
             10  ROWS processed                                                    
    SQL>
     
    If your select list doesn't exactly match column definitions in your index or all of the columns are nullable the index won't be used:

    Code (SQL):
    SQL> SELECT c_2 FROM test1;
    C_2                                                                            
    -----------------------------------                                            
    Test 1                                                                          
    Test 2                                                                          
    Test 3                                                                          
    Test 4                                                                          
    Test 5                                                                          
    Test 6                                                                          
    Test 7                                                                          
    Test 8                                                                          
    Test 9                                                                          
    Test 10                                                                        
    10 ROWS selected.

    Execution Plan
    ----------------------------------------------------------                      
    Plan hash VALUE: 4122059633                                                    
                                                                                   
    ---------------------------------------------------------------------------    
    | Id  | Operation         | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |    
    ---------------------------------------------------------------------------    
    |   0 | SELECT STATEMENT  |       |    10 |    80 |     3   (0)| 00:00:01 |    
    |   1 |  TABLE ACCESS FULL| TEST1 |    10 |    80 |     3   (0)| 00:00:01 |    
    ---------------------------------------------------------------------------    

    Statistics
    ----------------------------------------------------------                      
              1  recursive calls                                                    
              0  db block gets                                                      
              8  consistent gets                                                    
              0  physical reads                                                    
              0  redo SIZE                                                          
            550  bytes sent via SQL*Net TO client                                  
            385  bytes received via SQL*Net FROM client                            
              2  SQL*Net roundtrips TO/FROM client                                  
              0  sorts (memory)                                                    
              0  sorts (disk)                                                      
             10  ROWS processed                                                    
    SQL>
    You need to read these posts:

    http://oratips-ddf.blogspot.com/2008/04/tale-of-two-indexes.html
    http://oratips-ddf.blogspot.com/2008/04/to-b-or-not-to-b.html
    http://oratips-ddf.blogspot.com/2008/11/magical-indexes.html

    Before you attempt to improve performance with indexes you need to truly understand what they can and cannot do.
     
    fahad08 likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Nice links David..
     
  4. malli_18arjun

    malli_18arjun Active Member

    Messages:
    36
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Hyderabad
    Nice links and thanks David