performance of Indexes in Composite key
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: performance of Indexes in Composite key

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Location
    india
    Posts
    10

    performance of Indexes in Composite key

    hi
    i have table having composite primary key on four fields.
    According to my knowledge oracle has indexed in the sequence of fields i have given like " Primary key(f1,f2,f3,f4) "

    Now
    first is:
    if i give my where condition like: "where f1='some value' and
    f2='some value' and f3='some value' and f4='some value'"
    Note : in above where condition i have taken the same sequence for fields as it is in compsite primary key.

    Second is:
    if i give my where condition like: "where f3='some value' and
    f2='some value' and f1='some value' and f4='some value'"

    Note : in above where condition it have not taken the same sequence for fields as it is in composite primary key.

    Now my First question is which query will give me the better performance weather index created by composite primary key will work?


    Second Question is if i take single any field in where condition like :
    "where f3='some value' "
    then the index created by composite primary key will work or not

    thanx
    Avi

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: performance of Indexes in Composite key

    Originally posted by avi kumar
    Now my First question is which query will give me the better performance weather index created by composite primary key will work?
    Both will result in the same execution plan, hence the same performance in both cases. Composit index will (or will not) be used in botrh cases if optimizer chooses so. Nothing is preventing the usage of composite index in both cases, though.
    Second Question is if i take single any field in where condition like :
    "where f3='some value' "
    then the index created by composite primary key will work or not
    Generaly, in this case the composite index can not be used unless f3 is the leading column of the index.

    However in 9i cost based optimizer might choose to use your composite index even if f3 is not the leading column of the index.
    Last edited by jmodic; 01-23-2003 at 09:53 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    1. It will/will not use a composite index for two first queries though the columns mentioned in the WHERE clause aren't as specified in the index defination (sequence of column specification). BUT It will definately improve the speed of retrival of data if you specify the sequence of the columns in the WHERE condition as you specified in the index defination. As it is always recommended

    Look at the follwing example.

    Code:
    SQL> CREATE TABLE test AS SELECT object_id, object_name, object_type FROM all_objects;
    
    Table created.
    
    SQL> CREATE INDEX ix_test ON test (object_id, object_type);
    
    Index created.
    
    SQL> SELECT object_name FROM test WHERE object_id = 1199  AND object_type = 'VIEW';
    
    
    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 object_name FROM test
      2    WHERE object_type = 'VIEW' AND  object_id = 1199;
    
    
    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)
    
    
    -- only second column has been specified in the WHERE clause
    so it doesn't use the index.
    
    SQL> SELECT object_name FROM test
      2  WHERE object_type = 'VIEW' ;
    
    1155 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   TABLE ACCESS (FULL) OF 'TEST'
    
    
    SQL> SELECT object_name FROM test WHERE object_id = 1199;
    
    
    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)
    
    
    -- Here you force optimizer to user index by suppling hint
    
    SQL> SELECT /*+ INDEX (test, ix_test) */ object_name FROM test  WHERE object_type = 'VIEW' ;
    
    1155 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=10 Bytes=280
              )
    
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=34 Card=10 B
              ytes=280)
    
       2    1     INDEX (FULL SCAN) OF 'IX_TEST' (NON-UNIQUE) (Cost=26 Car
              d=10)
    Last edited by Sameer; 01-23-2003 at 10:01 AM.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Sameer
    BUT It will definately improve the speed of retrival of data if you specify the sequence of the columns in the WHERE condition as you specified in the index defination. As it is always recommended
    Hmm, hmm, .... What makes you belive this? It will definately NOT HAVE ANY INFLUENCE WHATSOEVER in the speed of retrival of data if you specify the sequence of the columns in the WHERE condition as you specified in the index defination. The order of conditions in WHERE close is irrelevant.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by jmodic

    It will definately NOT HAVE ANY INFLUENCE WHATSOEVER in the speed of retrival of data
    Sorry.. I agree, I take the sentence back.. my mistake!!

  6. #6
    Join Date
    Jan 2003
    Location
    india
    Posts
    10
    Thanx to all

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