5 Replies Latest reply on Jan 28, 2020 4:13 PM by BluShadow

    Sql Query Optimiztion

    sandeepgupta18
    sandeepgupta18 Sep 29, 2019 5:49 PM

      Hi All,

       

      Can anyone help  me query Optimization

      I used two tables A & B

      when querying from A its perfect but when I join it with B it took 5 min and return on 60k records.

      I tried to find out reason and found   there is no index on that table(only Non-unique indexes) and table contains 20 millions of records.

       

       

      Now the problem is index can not be created on that table and neither it can be  drop or recreate  ..

      All stats are gathered already

       

      can anyone suggest something on this..

       

      Thanks in Advance

        • 1. Re: Sql Query Optimiztion
          Tejas Kulkarni
          Tejas Kulkarni Jan 27, 2020 9:59 AM (in response to sandeepgupta18)

          It's like you are searching names on telephone directory without having alphabets to directory pages. Whenever we have a table join, Oracle optimizer uses certain access path to retrieve the data. If indexes are not there then it will have to go for full table scan. Your table is having 20 millions of records and for 5 minutes 60k records are already returned. This means join of tables is returning large amount of  data. If Oracle goes for hash joins, take your DBA's help to increase PGA size to occupy hash table effectively in memory.

          If your join condition contains inequality condition then it will follow sort merge join method which will also take PGA space for sorting the both table's data. Increasing PGA size is going to help lot. If oracle goes for nested loop joins It, will happen if you are retrieving few thousands or millions of data only. This will lead to unnecessary disk I/O's. So Nested loops will be of no use if youo do not have index.

          Another option is you can specify hint /*+ parallel (number of cores) */.

           

          Cheers,

          Tejas

          • 2. Re: Sql Query Optimiztion
            BluShadow
            BluShadow Jan 27, 2020 10:11 AM (in response to sandeepgupta18)

            *** Moderator Note: I've moved your question over to the SQL and PL/SQL space.  Whilst the "Getting Started" space is a great place to introduce yourself and learn about using the community platform, it's not the place to ask product related questions.  Please ensure you search for the correct product related space for your product questions in future.

            • 3. Re: Sql Query Optimiztion
              gsalem-Oracle
              gsalem-Oracle Jan 27, 2020 9:34 PM (in response to sandeepgupta18)

              Well,

              Can you at least show us the query you are using, and the execution plan you have?

               

              This would make for easier replies, otherwise it's a guess work.

              • 4. Re: Sql Query Optimiztion
                Sven W.
                Sven W. Jan 28, 2020 1:54 PM (in response to sandeepgupta18)

                Is there a relationship between the two tables? Like a foreign key (which should be indexed)?

                 

                You also said that "only" 60k records are returned. Did you expect more that that and why?

                • 5. Re: Sql Query Optimiztion
                  BluShadow
                  BluShadow Jan 28, 2020 4:13 PM (in response to sandeepgupta18)

                  *** Just noticed this was asked 4 months ago - it was the initial reply 4 months later that brought it to my attention to move it... perhaps too late now.