Results 1 to 4 of 4

Thread: Cluster Index

  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Cluster Index

    Hi All,

    In a table there is column C1,C2 and C3.

    There is a Cluster Index on C1 and Non-Cluster index on C3
    and in the WHERE clause of the query I have mentioned

    Where C1>2000
    and C2 = 'A';

    Now will Oracle use both the indexes or only one ?

    If I change the WHERE clause like :

    Where C1>2000
    or C2 = 'A';

    What will happen in this case ?


    Thanks

    JD

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Why would Oracle use an index on C3 if there is no condition on C3 in the WHERE clause ?

    Concerning the use of indexes, it really depends on the "quality" of the indexes you created with respect to the query you want to optimize, and on the stats you have collected (think about histograms if your data is skewed) : the CBO will use the execution plan that seems the less costly to it according to the stats you have collected. You have to test and benchmark to see which indexes to create so as to reduce response time / resource consumption. There are some general rules of thumbs, but in the end just benchmark in YOUR environment to see what works best.

    Finally, don't forget that Full Table Scans are NOT EVIL , in general a "good index" scan is better, but sometimes a FTS may be better (don't forget FTS allow Multi-Block IO whereas Index Scans only allow Single-Block IO, with the exception of Fast Full Index Scan, that works more or less like a FTS on the index).

    HTH & Regards,

    RBARAER

  3. #3
    Join Date
    Jan 2004
    Posts
    153
    Hi RBARAER,

    Thanks for your speedy response.
    I would like to rectify my question.

    In both the where clause instead of C2 it will be C3.

    Where C1>2000
    and C3 = 'A';

    Now will Oracle use both the indexes or only one ?

    If I change the WHERE clause like :

    Where C1>2000
    or C3 = 'A';

    Looking for your answer.


    Thanks

    JD

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    OK for C3, but did you actually read what I wrote after my first line ?

    To be even clearer : one cannot say, only the optimizer knows, after it has considered several execution plans and evaluated their costs, which will be the less costly. We can only "guess" that, for example, "an index on these two columns might help" (I speak in general, not in your example), but only tests and benchmarks can prove it. You have to try and see how the optimizer reacts. Always have your stats up-to-date, that is : each time you add a lot of data, or you add/drop an index or so, gather your stats again, so that the optimizer has "good knowledge" of the data and the available access paths. Learn how to use EXPLAIN PLAN, and understand each type of access path, so as to detect where the optimizer is doing something obviously "wrong", and try ideas to help it choose a better plan... Learn how it works, test and benchmark in YOUR environment (what works like a charm in your environment might be awful in another one). To learn about exec plan understanding and how to test/benchmark efficiently, I suggest you read the doc, go to asktom, or buy one of the excellent Tom Kyte's books.

    BTW, what do you mean by "Cluster index", is your table in a cluster ? If so, B-Tree or Hash-cluster ?

    Also be aware that OR queries are very often (not to say almost always) slower than AND queries because OR is transformed in a UNION of two queries before being optimized, and will have to process more data.

    HTH & Regards,

    RBARAER

Posting Permissions

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