Composite index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Composite index

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    I am confuse about composite index !! What is that said use the leading column and is it true for example if in the join condition column was second in order of the composite index it does not utilize ?? Can you clarify me may be with a real example
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Which version?

    In 8i, your leading column of a composite index must be utilized in order for your index to be used.

    In 9i, this is no longer true.

    For example, if you have a table:
    Code:
    CREATE TABLE xyz (
       x number(10),
       y number(10), 
       z number(10))
    And you have an index:
    Code:
    CREATE INDEX xyz_x_y ON xyz(x,y)
    In Oracle 8i, the following query will NOT use your index:
    Code:
    SELECT x, y, z 
    FROM xyz
    WHERE y = 123;
    Whereas in 9i, it would.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Posts
    416
    Jeff we have Oracle 8.0.5 , it's not 8i . What's going on in 8 version? Can you explain?
    An ounce of prevention is worth a pound of cure

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    i oracle 8.0.5 also you need to use the leading column first in the join condition.

    cheese
    anandkl
    anandkl

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