10 Replies Latest reply: Apr 4, 2013 11:27 AM by user648773 RSS

    Oracle 10.1 + query optimization

    DavyOra
      Hi all,

      I would have to optimize this query :

      SELECT CO_ID , CH_SEQNO , CH_STATUS , CH_REASON , CH_VALIDFROM , ENTDATE , USERLASTMOD , REC_VERSION , REQUEST
      FROM CONTRACT_HISTORY x
      WHERE x.CH_SEQNO = ( SELECT max( z.CH_SEQNO )
      FROM CONTRACT_HISTORY z
      WHERE (z.CH_PENDING IS NULL OR z.CH_PENDING != 'X')
      AND z.CO_ID = x.CO_ID );

      The explain plan show 2 Table Access Full on contract_history table.

      Do you have an idea to optimize this query ?

      Thanks.
        • 1. Re: Oracle 10.1 + query optimization
          sb92075
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: Oracle 10.1 + query optimization
            bencol
            This may give the same results you want with 1 full table scan:
            select co_id 
                  ,ch_seqno 
                  ,ch_status
                  ,ch_reason
                  ,ch_validfrom
                  ,entdate
                  ,userlastmod
                  ,rec_version
                  ,request
            from  (select co_id 
                         ,ch_seqno 
                         ,ch_status
                         ,ch_reason
                         ,ch_validfrom
                         ,entdate
                         ,userlastmod
                         ,rec_version
                         ,request
                         ,row_number() over (partition by co_id
                                             order by ch_seqno desc
                                            ) rn
                          from   contract_history
                          where  z.ch_pending is null or z.ch_pending != 'X'
                  )
            where  rn = 1;
            if the filter z.ch_pending is null or z.ch_pending != 'X' is selective enough to benefit from an index lookup then you will need a function based index and change your query to use that.
            • 3. Re: Oracle 10.1 + query optimization
              DavyOra
              Thanks a lot. I will try this query tomorrow.
              I will not see which function based index can i use with IS NULL or != 'X' in the where clause.

              Could you explain me ?

              Thanks again.
              • 4. Re: Oracle 10.1 + query optimization
                Nikolay Savvinov
                Hi,

                it looks like are facing a typical problem of making "slicing" queries for timeseries data, i.e. when you need your query to return the state of things as of a certain moment in time (an important special case -- as of now). If you are making many such queries, and if your DML activities aren't too intensive, then you can consider a solution involving some redundant columns to quickly identify latest entries in each class, e.g. a flag IS_LATEST which would have to be updated every time a new latest value is saved. This would require creating some logic for DML operations (which can be implemented via triggers) and will increase their cost, so you need to weigh costs and benefits very carefully. Once you have the flag in place, you can put latest records on one partition to further simplify manipulating them.

                Best regards,
                Nikolay
                • 5. Re: Oracle 10.1 + query optimization
                  bencol
                  As you filter is effectively on:
                  DECODE(ch_pending,'X',0,1) = 1
                  you can create an index
                  CREATE INDEX contact_history_fx01 ON contact_history (DECODE(ch_pending,'X',0,1));
                  Then uss the filter as I have put it. The effectiveness of this will depend on how selective this index will be.

                  As Nikolay says, it might be beneficial to maintain a latest_version column that is maintain by triggers (you will have to be careful to enforce serailisation correctly), or a fast refresh mv (or separate table) that only holds the latest version for each co_id.

                  Ben
                  • 6. Re: Oracle 10.1 + query optimization
                    chris227
                    Is this query the same?
                    It reduces the result set to the rows matching the predicate "z.ch_pending is null or z.ch_pending != 'X'", which the origin query doesnt.
                    There all rows with ch_seqno in max(ch_seqno) are returned.
                    • 7. Re: Oracle 10.1 + query optimization
                      chris227
                      Depends on which part is the bottleneck (the filter on CH_SEQNO or the filter on z.CH_PENDING IS NULL OR z.CH_PENDING != 'X'),
                      but did you try an index on CH_SEQNO ?
                      • 8. Re: Oracle 10.1 + query optimization
                        bencol
                        Possibly not, I went though it quickly last night before going home. No sample data to test on. I should probably move the ch_pending filters to the order by. I was auuming that ch_seq was the primary key.
                        • 9. Re: Oracle 10.1 + query optimization
                          DavyOra
                          Thanks for your help !
                          • 10. Re: Oracle 10.1 + query optimization
                            user648773
                            Are you gettin a hash join between the two tables?
                            Here is another alternative, but possible with the same explain plan. Also, it has two full scan.

                            SELECT CO_ID , CH_SEQNO , CH_STATUS , CH_REASON , CH_VALIDFROM , ENTDATE , USERLASTMOD , REC_VERSION , REQUEST
                            FROM CONTRACT_HISTORY x,
                                 (SELECT z.CO_ID, max( z.CH_SEQNO ) CH_SEQNO
                                 FROM CONTRACT_HISTORY z
                                 WHERE (z.CH_PENDING IS NULL OR z.CH_PENDING != 'X'
                                 group by z.CO_ID) aux
                            WHERE x.CH_SEQNO = aux.CH_SEQNO;