Results 1 to 3 of 3

Thread: Doubt in Index

  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    41

    Unanswered: Doubt in Index

    Hi EverB,


    I have created a table say T1 with 5 columns (col1......col5).
    I have created an index on col4 and col5.

    iam executing a select query say.

    select col1,col2,col3 from t1 where col1 =0;

    In the above query i hav not used the columns for which i hav created the index.My ques is-

    Does the index really works when u execute the above query?

    Any answer please.........
    It wuld be better if u culd suggest some sites for this.

    Thanks and Regds,
    Arun

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In your example index would not be used.
    Code:
    SQL> create table brisime
      2  (col1 number,
      3   col2 number
      4  );
    
    Table created.
    
    SQL> 
    create index i1br on brisime (col1) tablespace baza_index;
    
    Index created.
    
    SQL> insert into brisime values (1, 1);
    
    1 row created.
    
    SQL> 
    insert into brisime values (2, 2);
    
    1 row created.
    
    SQL> 
    insert into brisime values (3, 3);
    
    1 row created.
    
    SQL> alter session set sql_trace = true;
    
    Session altered.
    
    SQL> select * from brisime where col2 > 1;
    
          COL1       COL2
    ---------- ----------
             2          2
             3          3
    
    SQL> exit
    And here's execution plan for this statement:
    Code:
    Misses in library cache during parse: 1
    Optimizer hint: CHOOSE
    Parsing user id: 26  (SCOTT)
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
          3    TABLE ACCESS (FULL) OF 'BRISIME'
    As you can see, full table scan was performed.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Just curious Arun, but how did you imagine that an index on (col4, col5) could be useful in a query that accesses neither of those columns? That would be rather like trying to use a dictionary to look up your friend's phone number, wouldn't it?

Posting Permissions

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