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!

indexing help

Discussion in 'General' started by dsub42, May 10, 2010.

  1. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Thankyou, that makes perfect sense to me now..

    Sorry for being a pain, Im trying to teach my self, and i feel unconfortable moving on until I properly understand something.

    In theory, if i placed individual indexes on each of the columns, would the same be true?

    i.e if each column had an individual index then it would still use a full table scan?....
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,566
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, as only the index on sex would be used. Yoiu can certainly index every column in a table individually however with B-TREE indexes Oracle will choose to use the best ONE index for the query. Only bitmap indexes can be joined without using a hint so that more than one can be used on a single table. This is why concatenated indexes are available (indexes built on more than one column). And, if the table had one index per column you would only get an index fast full scan when selecting one column from the table.
     
  3. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    So, no matter how many b-tree indexes are placed on a table, oracle will only ever use one of them per query, thats if it uses any atall?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,566
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Generally speaking, yes. You can use an index_join hint but use of that hint is rare in my experience. Without any 'fiddling' Oracle will use the best index (according to its methods) per table.
     
    dsub42 likes this.