1 2 Previous Next 20 Replies Latest reply: Feb 27, 2013 5:30 PM by khallas301 Go to original post RSS
      • 15. Re: subquery tuning...
        Another_user
        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
          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
            OK - if this is all the information you can provide, then yes, back to the FAQ for you.
            • 18. Re: subquery tuning...
              khallas301
              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
                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
                  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