Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Indexes and tuning

    Hi All,

    I have a question on Tuning with indexes. I have a SQL query which is getting data from 4 tables. Something like this-
    select * from A,B,C,D
    where A.a=B.a and
    A.b=C.b and
    A.d=D.d ...

    -If I have a composite index on table A on columns(b,c,d) or
    -If column 'a' in table A is not indexed or if it has a normal index
    will the index still be used?

    (I can do an explain plan to see if the index is used or not, though sometimes it shows its used, there is no change in performance)

    Can someone please give me some idea on this.
    Thanks in advance!

    Nandini

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It depends.
    please reading the Tuning Guide found at http://tahiti.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by nandinir
    -If I have a composite index on table A on columns(b,c,d)
    As you are only using columns b and d in the query, the index will most probably not be used. Or if it is, then only to search on column b.

    -If column 'a' in table A is not indexed or if it has a normal index
    will the index still be used?
    Which index do you mean? The (b,c,d) index?

    The execution plan can easily answer those questions!

    (I can do an explain plan to see if the index is used or not, though sometimes it shows its used, there is no change in performance)
    An index usage is not necessarily better than e.g. a Full Table Scan.

    Check out SQL*Plus autotrace feature which will also show you the I/O needed to retrieve the data. That might give you a hint, why with using the hint it is still not faster.

    Get rid of the SELECT * unless you really, really need all columns.

    As you request all columns of all tables, it is very likely that Oracle decides a FTS is quicker (in terms of IO) compared to an index usage. Reduce the number of columns to those that you really need and re-check your plan.

    And do follow anacedent's advice and read the manual. There is a special tuning guide available:
    http://download-uk.oracle.com/docs/c...b14211/toc.htm

  4. #4
    Join Date
    Jul 2005
    Posts
    276
    Thank you very much for your reply. I will look into the guide.

    -Nandini

Posting Permissions

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