This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Feb 27, 2013 3:30 PM by khallas301 Go to original post RSS
  • 15. Re: subquery tuning...
    Another_user Explorer
    Currently Being Moderated
    Show us all of the indexes that exist on all of your tables used here.

    And how selective is your subquery?
  • 16. Re: subquery tuning...
    khallas301 Newbie
    Currently Being Moderated
    subquery output changes as per bind variable

    all the indexes are there and also pk entry exists and used in explain plan...

    trying to understand at which point nested loops enters in query and increases total time?
  • 17. Re: subquery tuning...
    Another_user Explorer
    Currently Being Moderated
    OK - if this is all the information you can provide, then yes, back to the FAQ for you.
  • 18. Re: subquery tuning...
    khallas301 Newbie
    Currently Being Moderated
    there are like 50 indexes combined for all 3 join tables.. it will be hard to list them..

    thanks for your input..
  • 19. Re: subquery tuning...
    Another_user Explorer
    Currently Being Moderated
    Of course your variables change - they are variables. Look at the selectivity:

    SELECT COUNT (*), COUNT (DISTINCT otherid), COUNT (DISTINCT id) FROM def;

    SELECT COUNT (*), COUNT (DISTINCT id) FROM abc;

    SELECT COUNT (*), COUNT (DISTINCT id) FROM xyz;


    Is there an index on def where OTHERID is the leading column?

    Is there an index on def where ID is the leading column?

    Is there an index on abc where ID is the leading column?

    Is there an index on xyz where ID is the leading column?
  • 20. Re: subquery tuning...
    khallas301 Newbie
    Currently Being Moderated
    SELECT COUNT (*), COUNT (DISTINCT otherid), COUNT (DISTINCT id) FROM def;
    -- 9390138, 305986

    SELECT COUNT (*), COUNT (DISTINCT id) FROM abc;
    -- 2370985, 2790985

    SELECT COUNT (*), COUNT (DISTINCT id) FROM xyz;
    409500, 409500

    Is there an index on def where OTHERID is the leading column?
    -- yes

    Is there an index on def where ID is the leading column?
    -- yes

    Is there an index on abc where ID is the leading column?
    -- yes

    Is there an index on xyz where ID is the leading column?
    -- yes
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points