1 2 Previous Next 21 Replies Latest reply: May 3, 2012 5:28 AM by wassim haddad RSS

    SQL performance

    wassim haddad
      Dears
      it's a simple
      query 1 execute fast
      query 2 take long time

      Both are the same with one deference (in bold)

      Q1 :
      SELECT REPORT2.ORIGT_BRA_CODE,
      REPORT2.BRA_CODE,
      REPORT2.CUR_CODE,
      REPORT2.LED_CODE,
      REPORT2.DEB_CRE_IND,
      REPORT2.TRA_AMT,
      COUNT (DISTINCT REPORT2.TRA_SEQ1)
      FROM absmis.REPORT2, PIOGL.PIO_ACCOUNTS, last_date
      WHERE *(REPORT2.TRA_DATE = '02052012')*
      AND ( REPORT2.BRA_CODE = PIOGL.PIO_ACCOUNTS.BRA_CODE
      AND REPORT2.CUS_NUM = PIOGL.PIO_ACCOUNTS.CUS_NUM
      AND REPORT2.CUR_CODE = PIOGL.PIO_ACCOUNTS.CUR_CODE
      AND REPORT2.LED_CODE = PIOGL.PIO_ACCOUNTS.LED_CODE
      AND REPORT2.SUB_ACCT_CODE = PIOGL.PIO_ACCOUNTS.SUB_ACCT_CODE)
      GROUP BY REPORT2.ORIGT_BRA_CODE,
      REPORT2.BRA_CODE,
      REPORT2.CUR_CODE,
      REPORT2.LED_CODE,
      REPORT2.DEB_CRE_IND,
      REPORT2.TRA_AMT

      Q2 :
      SELECT REPORT2.ORIGT_BRA_CODE,
      REPORT2.BRA_CODE,
      REPORT2.CUR_CODE,
      REPORT2.LED_CODE,
      REPORT2.DEB_CRE_IND,
      REPORT2.TRA_AMT,
      COUNT (DISTINCT REPORT2.TRA_SEQ1)
      FROM absmis.REPORT2, PIOGL.PIO_ACCOUNTS, last_date
      WHERE *(REPORT2.TRA_DATE = last_date)*
      AND ( REPORT2.BRA_CODE = PIOGL.PIO_ACCOUNTS.BRA_CODE
      AND REPORT2.CUS_NUM = PIOGL.PIO_ACCOUNTS.CUS_NUM
      AND REPORT2.CUR_CODE = PIOGL.PIO_ACCOUNTS.CUR_CODE
      AND REPORT2.LED_CODE = PIOGL.PIO_ACCOUNTS.LED_CODE
      AND REPORT2.SUB_ACCT_CODE = PIOGL.PIO_ACCOUNTS.SUB_ACCT_CODE)
      GROUP BY REPORT2.ORIGT_BRA_CODE,
      REPORT2.BRA_CODE,
      REPORT2.CUR_CODE,
      REPORT2.LED_CODE,
      REPORT2.DEB_CRE_IND,
      REPORT2.TRA_AMT
        • 1. Re: SQL performance
          wassim haddad
          execution plan for Q1 (fast on ) is :


          Total Cost:

               578

          Execution Steps:

          Step #     Step Name
          19          SELECT STATEMENT
          18          SORT [GROUP BY]
          17          HASH JOIN
          15          ABSMIS.REPORT2 VIEW
          14          SORT [UNIQUE]
          13          UNION-ALL
          7          NESTED LOOPS [ANTI]
          3          PARTITION RANGE [SINGLE]
          2          PIOBANKS.TRANSACT TABLE ACCESS [BY LOCAL INDEX ROWID]
          1          PIOBANKS.TRANSACT_PK INDEX [RANGE SCAN]
          6          PARTITION RANGE [SINGLE]
          5          PIOGL.PIO_INBR_CLE TABLE ACCESS [BY LOCAL INDEX ROWID]
          4          PIOGL.INBR_CLE_PK INDEX [RANGE SCAN]
          12          NESTED LOOPS [OUTER]
          10          PARTITION RANGE [SINGLE]
          9          PIOGL.PIO_INBR_CLE TABLE ACCESS [BY LOCAL INDEX ROWID]
          8          PIOGL.INBR_CLE_PK INDEX [RANGE SCAN]
          11          PIOGL.LEDGERS_PK INDEX [UNIQUE SCAN]
          16          PIOGL.PIO_ACCOUNTS TABLE ACCESS [FULL]

          Step #     Description     Est. Cost     Est. Rows Returned     Est. KBytes Returned
          1      This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index TRANSACT_PK.     2     1     --
          2      This plan step retrieves rows from table TRANSACT through ROWID(s) returned by a local partitioned index.     3     1     0.129
          3      This plan step accesses a single partition of a range-partitioned table.               
          4      This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index INBR_CLE_PK.     1     1     --
          5      This plan step retrieves rows from table PIO_INBR_CLE through ROWID(s) returned by a local partitioned index.     2     1     0.02
          6      This plan step accesses a single partition of a range-partitioned table.               
          7      This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.     5     1     0.148
          8      This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index INBR_CLE_PK.     5     203     --
          9      This plan step retrieves rows from table PIO_INBR_CLE through ROWID(s) returned by a local partitioned index.     36     779     104.982
          10      This plan step accesses a single partition of a range-partitioned table.               
          11      This plan step retrieves a single ROWID from the B*-tree index LEDGERS_PK.     0     1     0.004
          12      This plan step joins two sets of rows by iterating over the outer, or driving, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause; in addition to the rows satisfying that condition, Oracle also returns all rows from the row set not containing the outer join operator (+) which failed to satisfy that condition.     36     779     108.025
          13      This plan step accepts multiple sets of rows, and combines them into one set including all duplicates.               
          14      This plan step accepts a row set (its only child) and sorts it in order to identify and eliminate duplicates.     43     780     108.174
          15      This plan step represents the execution plan for the subquery defined by the view REPORT2.     43     780     89.121
          16      This plan step retrieves all rows from table PIO_ACCOUNTS.     532     185,100     4,157.52
          17      This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.     577     964     131.797
          18      This plan step accepts a set of rows from its child node, and sorts them into groups based on the columns specified in the query's GROUP BY clause.     578     964     131.797
          19      This plan step designates this statement as a SELECT statement.     578     964     131.797
          • 2. Re: SQL performance
            wassim haddad
            execution plan for Q2 (take long time) is


            Total Cost:

                 62,048

            Execution Steps:

            Step #     Step Name
            20          SELECT STATEMENT
            19          SORT [GROUP BY]
            18          HASH JOIN
            4          NESTED LOOPS
            2          PIOGL.PIO_BRANCHES TABLE ACCESS [BY INDEX ROWID]
            1          PIOGL.BRANCHES_PK INDEX [UNIQUE SCAN]
            3          PIOGL.PIO_ACCOUNTS TABLE ACCESS [FULL]
            17          ABSMIS.REPORT2 VIEW
            16          SORT [UNIQUE]
            15          UNION-ALL
            8          PARTITION RANGE [ALL]
            7          HASH JOIN [RIGHT ANTI]
            5          PIOGL.PIO_INBR_CLE TABLE ACCESS [FULL]
            6          PIOBANKS.TRANSACT TABLE ACCESS [FULL]
            14          HASH JOIN [RIGHT OUTER]
            9          PIOGL.LEDGERS_PK INDEX [FAST FULL SCAN]
            13          PARTITION RANGE [ALL]
            12          INLIST ITERATOR
            11          PIOGL.PIO_INBR_CLE TABLE ACCESS [BY LOCAL INDEX ROWID]
            10          PIOGL.INBR_CLE_SEC1 INDEX [RANGE SCAN]

            Step #     Description     Est. Cost     Est. Rows Returned     Est. KBytes Returned
            1      This plan step retrieves a single ROWID from the B*-tree index BRANCHES_PK.     0     1     --
            2      This plan step retrieves rows from table PIO_BRANCHES through ROWID(s) returned by an index.     1     1     0.012
            3      This plan step retrieves all rows from table PIO_ACCOUNTS.     532     185,100     4,157.52
            4      This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.     533     185,100     6,326.66
            5      This plan step retrieves all rows from table PIO_INBR_CLE.     8,804     30,075     792.993
            6      This plan step retrieves all rows from table TRANSACT.     31,736     654,426     84,359.602
            7      This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.     40,548     654,425     101,614.819
            8      This plan step iterates over all of the partitions of a range-partitioned table.               
            9      This plan step retrieves all of the ROWIDs of B*-tree index LEDGERS_PK by sequentially scanning the leaf nodes.     3     2,482     9.695
            10      This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index INBR_CLE_SEC1.     523     688,815     --
            11      This plan step retrieves rows from table PIO_INBR_CLE through ROWID(s) returned by a local partitioned index.     7,268     60,360     9,961.758
            12      This plan step loops through the query's IN list predicate, executing its child step for each value found.               
            13      This plan step iterates over all of the partitions of a range-partitioned table.               
            14      This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.     7,272     60,360     10,197.539
            15      This plan step accepts multiple sets of rows, and combines them into one set including all duplicates.               
            16      This plan step accepts a row set (its only child) and sorts it in order to identify and eliminate duplicates.     57,806     714,785     111,812.358
            17      This plan step represents the execution plan for the subquery defined by the view REPORT2.     57,806     714,785     87,952.061
            18      This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.     62,019     883,284     138,875.707
            19      This plan step accepts a set of rows from its child node, and sorts them into groups based on the columns specified in the query's GROUP BY clause.     62,048     883,284     138,875.707
            20      This plan step designates this statement as a SELECT statement.     62,048     883,284     138,875.707
            • 3. Re: SQL performance
              wassim haddad
              i need the second fast

              any help please
              • 4. Re: SQL performance
                HuaMin Chen
                Show the types of the 2 columns pls

                Edited by: HuaMin on May 3, 2012 4:07 PM
                • 5. Re: SQL performance
                  wassim haddad
                  date
                  • 6. Re: SQL performance
                    HuaMin Chen
                    If both are dates then try
                    WHERE (REPORT2.TRA_DATE = to_char(last_date,'ddmmyyyy'))
                    • 7. Re: SQL performance
                      wassim haddad
                      both are date

                      when i put last_date table the execution plan change
                      the problem not the type of columns

                      i replace fixed date with sysdate and works fast (but i need it as variable not fixed , variable like in Q2)

                      i need to enforce Q2 to use plan of Q1
                      • 8. Re: SQL performance
                        HuaMin Chen
                        Do you mean to use sysdate? How about using this?
                        WHERE (REPORT2.TRA_DATE = to_char(sysdate,'ddmmyyyy'))
                        • 9. Re: SQL performance
                          Paul  Horth
                          What does last_date table contain?

                          Is it a single row table with a column of last_date?

                          Can you post definitions of reports view and last_date table please?
                          • 10. Re: SQL performance
                            Dom Brooks
                            See comments others have made about making sure you use the correct datatype.

                            For advice on what you should post in a tuning request, see:
                            HOW TO: Post a SQL statement tuning request - template posting


                            What is needed initially is not a running commentary of the different steps of the plan but just nice, clearly formatted execution plans (use the code tags) including the very important predicates section.
                            • 11. Re: SQL performance
                              wassim haddad
                              Dear the problem is not in data type

                              last_date is a view with one record

                              SELECT pre_bank_date Last_Date
                              FROM pio_branches
                              WHERE bra_code = 999;

                              reports 2 is a view

                              SELECT transact.tra_date,
                              transact.tra_seq1,
                              transact.tra_seq2,
                              transact.bra_code,
                              transact.cus_num,
                              transact.cur_code,
                              transact.led_code,
                              transact.sub_acct_code,
                              transact.tell_id,
                              transact.ext_int_flag,
                              transact.dep_code,
                              transact.dis_code,
                              transact.tra_amt,
                              transact.deb_cre_ind,
                              transact.man_app,
                              transact.man_rep,
                              transact.expl_code,
                              transact.val_date,
                              transact.int_date,
                              transact.can_rea_code,
                              transact.doc_alp,
                              transact.doc_num,
                              transact.cur_pri,
                              transact.equ_tra_amt,
                              transact.origt_bra_code,
                              transact.origt_tra_date,
                              transact.origt_tra_seq1,
                              transact.origt_tra_seq2,
                              remarks
                              FROM transact
                              WHERE ( (transact.tra_date, transact.tra_seq1, transact.tra_seq2) NOT IN
                              (SELECT piogl.pio_inbr_cle.tra_date,
                              piogl.pio_inbr_cle.tra_seq1,
                              piogl.pio_inbr_cle.tra_seq2
                              FROM piogl.pio_inbr_cle
                              WHERE piogl.pio_inbr_cle.tra_date = transact.tra_date
                              AND piogl.pio_inbr_cle.tra_seq1 = transact.tra_seq1
                              AND piogl.pio_inbr_cle.tra_seq2 = transact.tra_seq2
                              AND piogl.pio_inbr_cle.ORIGT_BRA_CODE =
                              pio_inbr_cle.BRA_CODE
                              AND piogl.pio_inbr_cle.ORIGT_BRA_CODE =
                              transact.BRA_CODE
                              AND piogl.pio_inbr_cle.pro_code = 2))
                              UNION
                              SELECT piogl.pio_inbr_cle.tra_date,
                              piogl.pio_inbr_cle.tra_seq1,
                              piogl.pio_inbr_cle.tra_seq2,
                              piogl.pio_inbr_cle.bra_code,
                              piogl.pio_inbr_cle.cus_num,
                              piogl.pio_inbr_cle.cur_code,
                              piogl.pio_inbr_cle.led_code,
                              piogl.pio_inbr_cle.sub_acct_code,
                              piogl.pio_inbr_cle.tell_id,
                              piogl.pio_inbr_cle.ext_int_flag,
                              piogl.pio_inbr_cle.dep_code,
                              piogl.pio_inbr_cle.dis_code,
                              DECODE (PRO_CODE,
                              2, DECODE (CAN_REA_CODE, 0, piogl.pio_inbr_cle.tra_amt, 0),
                              0),
                              piogl.pio_inbr_cle.deb_cre_ind,
                              piogl.pio_inbr_cle.man_app,
                              piogl.pio_inbr_cle.man_rep,
                              piogl.pio_inbr_cle.expl_code,
                              piogl.pio_inbr_cle.val_date,
                              piogl.pio_inbr_cle.int_date,
                              piogl.pio_inbr_cle.can_rea_code,
                              piogl.pio_inbr_cle.doc_alp,
                              piogl.pio_inbr_cle.doc_num,
                              piogl.pio_inbr_cle.cur_pri,
                              DECODE (
                              PRO_CODE,
                              2, DECODE (CAN_REA_CODE, 0, piogl.pio_inbr_cle.equ_tra_amt, 0),
                              0),
                              piogl.pio_inbr_cle.origt_bra_code,
                              piogl.pio_inbr_cle.origt_tra_date,
                              piogl.pio_inbr_cle.origt_tra_seq1,
                              piogl.pio_inbr_cle.origt_tra_seq2,
                              remarks
                              FROM piogl.pio_inbr_cle, piogl.pio_ledgers
                              WHERE ( (piogl.pio_inbr_cle.pro_code != 2)
                              OR (piogl.pio_inbr_cle.pro_code = 2
                              AND piogl.pio_inbr_cle.origt_bra_code =
                              piogl.pio_inbr_cle.bra_code))
                              AND DECODE (
                              origt_bra_code,
                              piogl.pio_inbr_cle.bra_code, TO_NUMBER (
                              SUBSTR (cus_acct, 15, 4)),
                              piogl.pio_inbr_cle.led_code) = piogl.pio_ledgers.led_code(+);
                              • 12. Re: SQL performance
                                wassim haddad
                                Q1


                                Plan hash value: 4214617715

                                -------------------------------------------------------------------------------------------------------------------------
                                | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
                                -------------------------------------------------------------------------------------------------------------------------
                                | 0 | SELECT STATEMENT | | 1318 | 176K| 44 (7)| 00:00:01 | | |
                                | 1 | SORT GROUP BY | | 1318 | 176K| 44 (7)| 00:00:01 | | |
                                | 2 | NESTED LOOPS | | 1318 | 176K| 43 (5)| 00:00:01 | | |
                                | 3 | VIEW | REPORT2 | 1322 | 151K| 43 (5)| 00:00:01 | | |
                                | 4 | SORT UNIQUE | | 1322 | 183K| 43 (89)| 00:00:01 | | |
                                | 5 | UNION-ALL | | | | | | | |
                                | 6 | NESTED LOOPS ANTI | | 1 | 152 | 5 (0)| 00:00:01 | | |
                                | 7 | PARTITION RANGE SINGLE | | 1 | 132 | 3 (0)| 00:00:01 | KEY | KEY |
                                | 8 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACT | 1 | 132 | 3 (0)| 00:00:01 | KEY | KEY |
                                |* 9 | INDEX RANGE SCAN | TRANSACT_PK | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
                                | 10 | PARTITION RANGE SINGLE | | 1 | 20 | 2 (0)| 00:00:01 | KEY | KEY |
                                |* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| PIO_INBR_CLE | 1 | 20 | 2 (0)| 00:00:01 | KEY | KEY |
                                |* 12 | INDEX RANGE SCAN | INBR_CLE_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
                                | 13 | NESTED LOOPS OUTER | | 1321 | 183K| 36 (0)| 00:00:01 | | |
                                | 14 | PARTITION RANGE SINGLE | | 1321 | 178K| 36 (0)| 00:00:01 | KEY | KEY |
                                |* 15 | TABLE ACCESS BY LOCAL INDEX ROWID| PIO_INBR_CLE | 1321 | 178K| 36 (0)| 00:00:01 | KEY | KEY |
                                |* 16 | INDEX RANGE SCAN | INBR_CLE_PK | 201 | | 5 (0)| 00:00:01 | KEY | KEY |
                                |* 17 | INDEX UNIQUE SCAN | LEDGERS_PK | 1 | 4 | 0 (0)| 00:00:01 | | |
                                |* 18 | INDEX UNIQUE SCAN | ACCOUNTS_PK | 1 | 20 | 0 (0)| 00:00:01 | | |
                                -------------------------------------------------------------------------------------------------------------------------

                                Predicate Information (identified by operation id):
                                ---------------------------------------------------

                                9 - access("TRANSACT"."TRA_DATE"=SYSDATE@!)
                                11 - filter("PIO_INBR_CLE"."ORIGT_BRA_CODE"="PIO_INBR_CLE"."BRA_CODE" AND "PIO_INBR_CLE"."PRO_CODE"=2 AND
                                "PIO_INBR_CLE"."ORIGT_BRA_CODE"="TRANSACT"."BRA_CODE")
                                12 - access("PIO_INBR_CLE"."TRA_DATE"=SYSDATE@! AND "PIO_INBR_CLE"."TRA_SEQ1"="TRANSACT"."TRA_SEQ1" AND
                                "PIO_INBR_CLE"."TRA_SEQ2"="TRANSACT"."TRA_SEQ2")
                                15 - filter("PIO_INBR_CLE"."PRO_CODE"<>2 OR "PIO_INBR_CLE"."ORIGT_BRA_CODE"="PIO_INBR_CLE"."BRA_CODE" AND
                                "PIO_INBR_CLE"."PRO_CODE"=2)
                                16 - access("PIO_INBR_CLE"."TRA_DATE"=SYSDATE@!)
                                17 - access("PIO_LEDGERS"."LED_CODE"(+)=DECODE("ORIGT_BRA_CODE","PIO_INBR_CLE"."BRA_CODE",TO_NUMBER(SUBSTR("CUS
                                _ACCT",15,4)),"PIO_INBR_CLE"."LED_CODE"))
                                18 - access("REPORT2"."BRA_CODE"="PIO_ACCOUNTS"."BRA_CODE" AND "REPORT2"."CUS_NUM"="PIO_ACCOUNTS"."CUS_NUM"
                                AND "REPORT2"."CUR_CODE"="PIO_ACCOUNTS"."CUR_CODE" AND "REPORT2"."LED_CODE"="PIO_ACCOUNTS"."LED_CODE" AND
                                "REPORT2"."SUB_ACCT_CODE"="PIO_ACCOUNTS"."SUB_ACCT_CODE")
                                • 13. Re: SQL performance
                                  wassim haddad
                                  Q2

                                  Plan hash value: 4009522734

                                  -----------------------------------------------------------------------------------------------------------------------
                                  | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
                                  -----------------------------------------------------------------------------------------------------------------------
                                  | 0 | SELECT STATEMENT | | 15M| 2310M| | 267K (2)| 01:20:07 | | |
                                  | 1 | SORT GROUP BY | | 15M| 2310M| | 267K (2)| 01:20:07 | | |
                                  | 2 | NESTED LOOPS | | 15M| 2310M| | 266K (1)| 01:19:56 | | |
                                  | 3 | NESTED LOOPS | | 15M| 2024M| | 265K (1)| 01:19:38 | | |
                                  | 4 | TABLE ACCESS BY INDEX ROWID| PIO_BRANCHES | 1 | 12 | | 1 (0)| 00:00:01 | | |
                                  |* 5 | INDEX UNIQUE SCAN | BRANCHES_PK | 1 | | | 0 (0)| 00:00:01 | | |
                                  |* 6 | VIEW | REPORT2 | 15M| 1848M| | 265K (1)| 01:19:38 | | |
                                  | 7 | SORT UNIQUE | | 15M| 2361M| 5189M| 265K (17)| 01:19:38 | | |
                                  | 8 | UNION-ALL | | | | | | | | |
                                  | 9 | PARTITION RANGE ALL | | 13M| 1997M| | 40637 (2)| 00:12:12 | 1 | 11 |
                                  |* 10 | HASH JOIN RIGHT ANTI | | 13M| 1997M| | 40637 (2)| 00:12:12 | | |
                                  |* 11 | TABLE ACCESS FULL | PIO_INBR_CLE | 30075 | 792K| | 8804 (1)| 00:02:39 | 1 | 11 |
                                  | 12 | TABLE ACCESS FULL | TRANSACT | 13M| 1658M| | 31781 (2)| 00:09:33 | 1 | 11 |
                                  |* 13 | HASH JOIN RIGHT OUTER | | 2205K| 363M| | 8817 (2)| 00:02:39 | | |
                                  | 14 | INDEX FAST FULL SCAN | LEDGERS_PK | 2482 | 9928 | | 3 (0)| 00:00:01 | | |
                                  | 15 | PARTITION RANGE ALL | | 2205K| 355M| | 8806 (2)| 00:02:39 | 1 | 11 |
                                  |* 16 | TABLE ACCESS FULL | PIO_INBR_CLE | 2205K| 355M| | 8806 (2)| 00:02:39 | 1 | 11 |
                                  |* 17 | INDEX UNIQUE SCAN | ACCOUNTS_PK | 1 | 20 | | 0 (0)| 00:00:01 | | |
                                  -----------------------------------------------------------------------------------------------------------------------

                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------

                                  5 - access("BRA_CODE"=999)
                                  6 - filter("REPORT2"."TRA_DATE"="PRE_BANK_DATE")
                                  10 - access("PIO_INBR_CLE"."TRA_DATE"="TRANSACT"."TRA_DATE" AND
                                  "PIO_INBR_CLE"."TRA_SEQ1"="TRANSACT"."TRA_SEQ1" AND "PIO_INBR_CLE"."TRA_SEQ2"="TRANSACT"."TRA_SEQ2" AND
                                  "PIO_INBR_CLE"."ORIGT_BRA_CODE"="TRANSACT"."BRA_CODE")
                                  11 - filter("PIO_INBR_CLE"."ORIGT_BRA_CODE"="PIO_INBR_CLE"."BRA_CODE" AND "PIO_INBR_CLE"."PRO_CODE"=2)
                                  13 - access("PIO_LEDGERS"."LED_CODE"(+)=DECODE("ORIGT_BRA_CODE","PIO_INBR_CLE"."BRA_CODE",TO_NUMBER(SUBSTR("C
                                  US_ACCT",15,4)),"PIO_INBR_CLE"."LED_CODE"))
                                  16 - filter("PIO_INBR_CLE"."PRO_CODE"<>2 OR "PIO_INBR_CLE"."ORIGT_BRA_CODE"="PIO_INBR_CLE"."BRA_CODE" AND
                                  "PIO_INBR_CLE"."PRO_CODE"=2)
                                  17 - access("REPORT2"."BRA_CODE"="PIO_ACCOUNTS"."BRA_CODE" AND "REPORT2"."CUS_NUM"="PIO_ACCOUNTS"."CUS_NUM"
                                  AND "REPORT2"."CUR_CODE"="PIO_ACCOUNTS"."CUR_CODE" AND "REPORT2"."LED_CODE"="PIO_ACCOUNTS"."LED_CODE" AND
                                  "REPORT2"."SUB_ACCT_CODE"="PIO_ACCOUNTS"."SUB_ACCT_CODE")
                                  • 14. Re: SQL performance
                                    HuaMin Chen
                                    last_date is a view with one record
                                    tune this view is the answer
                                    1 2 Previous Next