index strategy on SQL tuning - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 27

Thread: index strategy on SQL tuning

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by fossil
    Hi,

    2. For joining large tables (million's of rows) you don't
    need an index on any column in the where clause.

    3. Multi table query will join the tables first and then
    filter the rows according to the where clause. You should
    not be having an index lookup for the filter.
    quite wrong indeed!

    I suggest you try dbms_xplan.display in 9i and you will see how predicates are applied before join to reduce the result set! (of course this is not always the case but when the index is selective this will be)

  2. #12
    Join Date
    Dec 2002
    Posts
    36
    Jmodic,

    You should have given your points to prove me wrong instead of furioulsy discarding what I said.


    Thanks,
    fossil

  3. #13
    Join Date
    Dec 2002
    Posts
    36
    Pando,


    Point(according to original poster) is what would it take to run the query faster if you are having
    multi million rows tables.

    Having indexes on the columns of the where clause would apply the predicate first and it will reduce the resultset, however, its going to make query run much slower than not having indexes on it. 15% for a multi million rows table is quite a big result set.

    Not having indexes on the columns in the where clause would have CBO do Full table scan and join the tables first and then apply filter(see the explain plan).


    Indexes are preferred when selecting a very very small number of rows may be upto 5%.

    Moreover, must try query both ways to and compare the timing.



    Thanks,
    fossil

  4. #14
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by fossil
    Pando,


    Point(according to original poster) is what would it take to run the query faster if you are having
    multi million rows tables.

    Having indexes on the columns of the where clause would apply the predicate first and it will reduce the resultset, however, its going to make query run much slower than not having indexes on it. 15% for a multi million rows table is quite a big result set.

    Not having indexes on the columns in the where clause would have CBO do Full table scan and join the tables first and then apply filter(see the explain plan).


    Indexes are preferred when selecting a very very small number of rows may be upto 5%.

    Moreover, must try query both ways to and compare the timing.



    Thanks,
    No, no no. If you put an index on the large table the CBO does not have to use it. It will look at the predicate, the table and index statistics, and make a choice based on minimum cost.

    This stuff about "Indexes are preferred when selecting a very very small number of rows may be upto 5%." is all wrong .. index usage is based on block selectivity not row selectivity. Read the link I posted previously.

    What you need to do is go and read the section in the Concepts Guide on how the optimizer works.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    2. For joining large tables (million's of rows) you don't
    need an index on any column in the where clause.

    Not true if low number of rows is gonna be returned, the poster in no where states that this is going to return many rows and your statement simply doesnt fit in general


    3. Multi table query will join the tables first and then
    filter the rows according to the where clause. You should
    not be having an index lookup for the filter.

    Here in no where you mentions about index, too general, does not apply to all cases, plus the poster says his indexes are being used by his predicates which means there are index and not as you said in your last post "Not having indexes on the columns in the where clause would have CBO do Full table scan". In his case his predicates are applied first for t1 then joined


    As for original poster, you dont compare costs, that is used internally, not by you! And you should post the query plan for this kind of questions

  6. #16
    Join Date
    Dec 2002
    Posts
    36
    Slim Dave,

    "No, no no. If you put an index on the large table the CBO does not have to use it. It will look at the predicate, the table and index statistics, and make a choice based on minimum cost."

    Yes, CBO would choose the plan based on mininum cost, however, having minimum cost doesn't make the query run faster.

    Check the cost when you have indexes on all the columns in the where clause, the cost would be less when compared to not having indexes
    on the columns of the where clause.

    Now compare the timing by running the querty w/wo indexes.
    fossil

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Fossil,

    You are pulling our legs, aren't you? Of course, it's April Fools! Didn't get it untill now....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Dec 2002
    Posts
    36
    Pando,


    "Not true if low number of rows is gonna be returned, the poster in no where states that this is going to return many rows and your statement simply doesnt fit in general"

    "Here in no where you mentions about index, too general, does not apply to all cases, plus the poster says his indexes are being used by his predicates which means there are index and not as you said in your last post "Not having indexes on the columns in the where clause would have CBO do Full table scan". In his case his predicates are applied first for t1 then joined"


    Can your statement fits in general ?.

    My statement fits in general, here is how ?.

    If you have small tables having small number of rows, then
    you don't need indexes as tables are small, FTS will be faster
    then index lookup.

    If you very large tables multi million rows and joinig them then
    also FTS will be fastest. Will do the join first, sorts them (if you
    have order by) and starts returning rows while filtering.

    Not having indexes indeed applies to almost all the cases
    just on the cost of query returning few rows.



    "As for original poster, you dont compare costs, that is used internally, not by you! And you should post the query plan for this kind of questions"


    Cost can be misleading ?. Run the queries both ways and check
    the timing.
    fossil

  9. #19
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by fossil

    If you very large tables multi million rows and joinig them then
    also FTS will be fastest. Will do the join first, sorts them (if you
    have order by) and starts returning rows while filtering.
    This is so wrong it must be an april fool's day thing I guess.

    You are saying that if I have a 1 billion row table joined to a fifty row table, and I have a predicate on the billion row table that filters out all but one row, then Oracle will ...
    • Join the fifty row table to the 1 billion row table
    • Order the result set
    • Filter the ordered set to remove all but those based on the single row of the billion row table?

    There are probably more dumb ways to execute such a query, but this would be way up there on the dumbness scale.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #20
    Join Date
    Dec 2002
    Posts
    36
    SlimDave,

    Didn't you read what I am saying ?. You did not.

    Looks like you don't have anything left to prove your point
    "for having indexes on the columns of the where clause" and
    lost sense of the problem of the original poster.
    fossil

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