Home » RDBMS Server » Performance Tuning » Need suggestion for a better query.
Need suggestion for a better query. [message #335522] Tue, 22 July 2008 07:39 Go to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Database :Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
--- Create Table -----
create table GRP_TEMP
(
  GROUP_ID  NUMBER,
  DEAL_NAME VARCHAR2(30),
  FAC_ID    NUMBER,
  FAC_NAME  VARCHAR2(30)
);

--- Insert -----
Insert into GRP_TEMP   (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values    (1, 'AAAAA', 1, 'DUEDKBNFYO');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (1, 'AAAAA', 2, 'ZIXXOHYCYT');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (1, 'AAAAA', 3, 'PIZBPWLOSW');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (1, 'AAAAA', 4, 'KXKBYLRNOF');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (2, 'CCCCC', 5, 'KVVLKKGQIH');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (2, 'CCCCC', 6, 'IUDJQTRPRD');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (2, 'CCCCC', 7, 'YYCJQYUOLD');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (2, 'CCCCC', 8, 'NJXTFASGSY');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (3, 'DDDDD', 9, 'DTJWFBQCRQ');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (3, 'DDDDD', 10, 'OPLRPAUPAV');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (3, 'DDDDD', 11, 'NTAGAOVWPI');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (3, 'DDDDD', 12, 'RVIBZDZTMH');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (4, 'EEEEE', 13, 'OJEWUCKSJG');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (4, 'EEEEE', 14, 'WGASEEHESV');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (4, 'EEEEE', 15, 'JIXZROTKLB');
Insert into GRP_TEMP    (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME)  Values   (4, 'EEEEE', 16, 'IJYFSIWKYK');
COMMIT;

--- Query -----
SELECT DENSE_RANK() OVER(ORDER BY PRE_SORT_COL ASC, A.GROUP_ID) RECORD_NUM,
       A.RECORD_TOTAL,
       A.GROUP_ID,
       A.DEAL_NAME,
       A.FAC_ID,
       A.FAC_NAME
  FROM (SELECT COUNT(DISTINCT V1.GROUP_ID) OVER() RECORD_TOTAL,
               V1.*,
               MIN(V1.FAC_NAME) OVER(PARTITION BY V1.GROUP_ID) PRE_SORT_COL
          FROM GRP_TEMP V1) A
 ORDER BY PRE_SORT_COL ASC, GROUP_ID, FAC_NAME ASC;

Output of above query: 
RECORD_NUM RECORD_TOTAL   GROUP_ID DEAL_NAME                          FAC_ID FAC_NAME
---------- ------------ ---------- ------------------------------ ---------- -----------
         1            4          3 DDDDD                                   9 DTJWFBQCRQ
         1            4          3 DDDDD                                  11 NTAGAOVWPI
         1            4          3 DDDDD                                  10 OPLRPAUPAV
         1            4          3 DDDDD                                  12 RVIBZDZTMH
         2            4          4 EEEEE                                  14 DTJWFBQCRQ
         2            4          4 EEEEE                                  16 IJYFSIWKYK
         2            4          4 EEEEE                                  15 JIXZROTKLB
         2            4          4 EEEEE                                  13 OJEWUCKSJG
         3            4          1 AAAAA                                   1 DUEDKBNFYO
         3            4          1 AAAAA                                   4 KXKBYLRNOF
         3            4          1 AAAAA                                   3 PIZBPWLOSW
         3            4          1 AAAAA                                   2 ZIXXOHYCYT
         4            4          2 CCCCC                                   6 IUDJQTRPRD
         4            4          2 CCCCC                                   5 KVVLKKGQIH
         4            4          2 CCCCC                                   8 NJXTFASGSY
         4            4          2 CCCCC                                   7 YYCJQYUOLD

16 rows selected.

SQL> 

The above query produces the same result exactly what i needed.
In the above query i am sorting(ASC) on FAC_NAME column.
A GROUP_ID can have multiple FAC_ID.
For query perormance point of view i want to know wether i can get the same result by any other efficient query.
Please put your suggestion.

Thanks
Pravin.
Re: Need suggestion for a better query. [message #335658 is a reply to message #335522] Wed, 23 July 2008 03:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Analytic functions aren't always the most efficient solution. The seem like they do a single pass of the data, but you can end up doing multiple passes for sorting in memory/TEMP.

Try:
WITH GRPS AS (
    SELECT GROUP_ID, ROWNUM AS RECORD_NUM
    FROM (
        SELECT GROUP_ID, MIN(V1.FAC_NAME) AS MIN_FAC_NAME
        FROM   GRP_TEMP 
        GROUP BY GROUP_ID
        ORDER BY 2, 1
    )
)
SELECT  A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM    GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GROUPS) B
JOIN GRP_TEMP C ON C.GROUP_ID = A.GROUP_ID


Ross Leishman

Re: Need suggestion for a better query. [message #336648 is a reply to message #335658] Mon, 28 July 2008 09:01 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi Ross,
thanks for the query that you have given.
and i am very sorry for not able to reply you immded.
I have done a small changes in that query to get a same output
as it was giving by my query. Also found your query work 2 times faster than my query.

Your Query(Modified):
WITH GRPS AS (
    SELECT GROUP_ID, ROWNUM AS RECORD_NUM
    FROM (
        SELECT GROUP_ID, MIN( FAC_NAME) AS MIN_FAC_NAME
        FROM   GRP_TEMP 
        GROUP BY GROUP_ID
        ORDER BY 2, 1 
        )
) 
SELECT  A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM    GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN  GRP_TEMP C ON C.GROUP_ID = A.GROUP_ID
ORDER BY 1 , FAC_NAME ASC ; 
.

Instead of table GRP_TEMP i am using view which is based on no. of tables.i have attached its definition.
when i run the same (above)query against that view its take too much time approx 20 sec to shows 50 groups.
Below i have mention few details. Please suggest, what is wrong in the query/view.

1. Query
WITH GRPS AS(
  SELECT GROUP_ID, ROWNUM AS RECORD_NUM
    FROM (SELECT GROUP_ID, MIN( 
    case when v.TRADE_TYPE_IND='NORM' THEN V.DEAL_NAME 
         when v.TRADE_TYPE_IND IN ('SPLT', 'TRPL') AND  V.IS_PARENT_FLAG='Y' THEN V.DEAL_NAME 
         ELSE NULL  END  ) AS MIN_FAC_NAME
            FROM TEMP_VW  V
           GROUP BY GROUP_ID
           ORDER BY 2 ASC ,  1
           ))
  SELECT 
 A.RECORD_NUM, B.RECORD_TOTAL, C.*
    FROM GRPS A
   CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
    JOIN TEMP_VW C ON C.GROUP_ID = A.GROUP_ID
   WHERE RECORD_NUM BETWEEN 1 AND 50
   ORDER BY 1, DEAL_NAME  ASC ;

2.Tables Rows
SLT_SLIQ_TRADE =25000
SLT_SLIQ_TRADE_FACILITY=50000
SLT_SLIQ_PORTFOLIO =25000
SLT_E_SETTL_TRADE=25000
SLT_E_SETTL_FACILITY=50000
3. Explain plan.
Elapsed: 00:00:19.70

Execution Plan
----------------------------------------------------------
Plan hash value: 152901756

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |  5837 |    17M|       |  6782   (2)| 00:01:22 |
|   1 |  TEMP TABLE TRANSFORMATION        |                             |       |       |       |            |          |
|   2 |   LOAD AS SELECT                  | TEMP_VW                     |       |       |       |            |          |
|   3 |    COUNT                          |                             |       |       |       |            |          |
|   4 |     VIEW                          |                             |   764 |  9932 |       |   761   (5)| 00:00:10 |
|   5 |      SORT ORDER BY                |                             |   764 | 31324 |       |   761   (5)| 00:00:10 |
|   6 |       SORT GROUP BY               |                             |   764 | 31324 |       |   761   (5)| 00:00:10 |
|   7 |        VIEW                       | TEMP_VW                     |   764 | 31324 |       |   759   (5)| 00:00:10 |
|   8 |         UNION-ALL                 |                             |       |       |       |            |          |
|   9 |          NESTED LOOPS OUTER       |                             |   249 | 19422 |       |   286   (9)| 00:00:04 |
|* 10 |           HASH JOIN               |                             |   249 | 18426 |       |   285   (9)| 00:00:04 |
|* 11 |            TABLE ACCESS FULL      | SLT_SLIQ_TRADE              |   250 | 10500 |       |   247   (6)| 00:00:03 |
|  12 |            NESTED LOOPS           |                             | 25000 |   781K|       |    37  (25)| 00:00:01 |
|  13 |             INDEX FAST FULL SCAN  | SLT_SLIQ_PORTFOLIO_PK       | 25000 |   390K|       |    29   (4)| 00:00:01 |
|* 14 |             INDEX RANGE SCAN      | SLT_SLIQ_TRADE_FACILITY_PK  |     1 |    16 |       |     0   (0)| 00:00:01 |
|* 15 |           INDEX RANGE SCAN        | SLT_E_SETTL_TRADE_PK        |     1 |     4 |       |     0   (0)| 00:00:01 |
|* 16 |          FILTER                   |                             |       |       |       |            |          |
|* 17 |           HASH JOIN               |                             |   515 | 14420 |       |   216   (7)| 00:00:03 |
|* 18 |            TABLE ACCESS FULL      | SLT_E_SETTL_TRADE           |   263 |  6312 |       |   175   (6)| 00:00:03 |
|  19 |            INDEX FAST FULL SCAN   | SLT_E_SETTL_FACILITY_PK     | 50000 |   195K|       |    39   (3)| 00:00:01 |
|* 20 |           INDEX RANGE SCAN        | SLT_SLIQ_TRADE_IE1          |     1 |     4 |       |     1   (0)| 00:00:01 |
|  21 |   SORT ORDER BY                   |                             |  5837 |    17M|    45M|  6021   (2)| 00:01:13 |
|* 22 |    HASH JOIN                      |                             |  5837 |    17M|       |  2234   (3)| 00:00:27 |
|  23 |     NESTED LOOPS                  |                             |   764 | 29796 |       |     4   (0)| 00:00:01 |
|  24 |      VIEW                         |                             |     1 |    13 |       |     2   (0)| 00:00:01 |
|* 25 |       FILTER                      |                             |       |       |       |            |          |
|  26 |        SORT AGGREGATE             |                             |     1 |       |       |            |          |
|  27 |         VIEW                      |                             |   764 |       |       |     2   (0)| 00:00:01 |
|  28 |          TABLE ACCESS FULL        | SYS_TEMP_0FD9D680A_2E882A84 |   764 |  9932 |       |     2   (0)| 00:00:01 |
|* 29 |      VIEW                         |                             |   764 | 19864 |       |     2   (0)| 00:00:01 |
|  30 |       TABLE ACCESS FULL           | SYS_TEMP_0FD9D680A_2E882A84 |   764 |  9932 |       |     2   (0)| 00:00:01 |
|  31 |     VIEW                          | TEMP_VW                     |   764 |  2286K|       |  2230   (3)| 00:00:27 |
|  32 |      UNION-ALL                    |                             |       |       |       |            |          |
|* 33 |       HASH JOIN OUTER             |                             |   249 |   130K|       |  1061   (3)| 00:00:13 |
|* 34 |        HASH JOIN                  |                             |   249 |   127K|       |   893   (3)| 00:00:11 |
|* 35 |         HASH JOIN                 |                             |   498 |   204K|       |   739   (4)| 00:00:09 |
|* 36 |          TABLE ACCESS FULL        | SLT_SLIQ_TRADE              |   250 | 50000 |       |   247   (6)| 00:00:03 |
|  37 |          TABLE ACCESS FULL        | SLT_SLIQ_TRADE_FACILITY     | 50000 |    10M|       |   490   (2)| 00:00:06 |
|  38 |         TABLE ACCESS FULL         | SLT_SLIQ_PORTFOLIO          | 25000 |  2539K|       |   153   (2)| 00:00:02 |
|  39 |        TABLE ACCESS FULL          | SLT_E_SETTL_TRADE           | 26256 |   333K|       |   167   (2)| 00:00:03 |
|* 40 |       FILTER                      |                             |       |       |       |            |          |
|* 41 |        HASH JOIN                  |                             |   515 |   158K|       |   653   (3)| 00:00:08 |
|* 42 |         TABLE ACCESS FULL         | SLT_E_SETTL_TRADE           |   263 | 26563 |       |   175   (6)| 00:00:03 |
|  43 |         TABLE ACCESS FULL         | SLT_E_SETTL_FACILITY        | 50000 |    10M|       |   476   (2)| 00:00:06 |
|  44 |        TABLE ACCESS BY INDEX ROWID| SLT_SLIQ_TRADE              |     1 |    12 |       |     2   (0)| 00:00:01 |
|* 45 |         INDEX RANGE SCAN          | SLT_SLIQ_TRADE_IE1          |     1 |       |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------

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

  10 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
  11 - filter(CASE  WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y')
              THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN
              "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
  14 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
              "SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
  15 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
  16 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
              "SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
  17 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
  18 - filter(CASE  WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1
              END =1)
  20 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)
  22 - access("C"."GROUP_ID"="A"."GROUP_ID")
  25 - filter(1<=50)
  29 - filter("A"."RECORD_NUM">=1 AND "A"."RECORD_NUM"<=50)
  33 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
  34 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
              "SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
  35 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
  36 - filter(CASE  WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y')
              THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN
              "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
  40 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
              "SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
  41 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
  42 - filter(CASE  WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1
              END =1)
  45 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)


Statistics
----------------------------------------------------------
        114  recursive calls
         66  db block gets
     296430  consistent gets
         57  physical reads
       1468  redo size
      34320  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         53  rows processed

SQL>
  • Attachment: TEMP_VW.txt
    (Size: 13.73KB, Downloaded 215 times)
Re: Need suggestion for a better query. [message #336758 is a reply to message #336648] Mon, 28 July 2008 22:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Let's just isolate what performance issues are due to the view, and those due to our SQL. How long does the following take?
SELECT *
FROM (
    SELECT GROUP_ID, min(TRADE_TYPE_IND), min(DEAL_NAME)
    FROM TEMP_VW  V
    GROUP BY GROUP_ID
    ORDER BY 2 ASC ,  1
)
WHERE ROWNUM > 1



Also see if this helps. It should avoid having to resolve the view twice:
WITH TMP AS ( 
    SELECT GROUP_ID, TRADE_TYPE_IND, DEAL_NAME,
           {.. any other required columns, NOT useless cols ..}
)
, GRPS AS (
    SELECT GROUP_ID, ROWNUM AS RECORD_NUM
    FROM (
        SELECT GROUP_ID, MIN(...) AS MIN_FAC_NAME
        FROM   TMP 
        GROUP BY GROUP_ID
        ORDER BY 2, 1 
        )
) 
SELECT  A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM    GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN  TMP C ON C.GROUP_ID = A.GROUP_ID
ORDER BY 1 , FAC_NAME ASC ;
Re: Need suggestion for a better query. [message #336912 is a reply to message #336648] Tue, 29 July 2008 05:53 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Thanks again for your help Ross.Definitely the problem seems to be with view and not with query.
But still I am not able to trace the problem in that view.
That view has 112 columns out of which 80 are shown on the screen.
Here are the statistics.

Query 1:
SELECT *
FROM (
    SELECT GROUP_ID, min(TRADE_TYPE_IND), min(DEAL_NAME)
    FROM TEMP_VW  V
    GROUP BY GROUP_ID
    ORDER BY 2 ASC ,  1
) WHERE ROWNUM > 1 ; 


Elapsed: 00:00:03.33

Execution Plan
----------------------------------------------------------
Plan hash value: 1454345946

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |   311 | 10885 |   508   (8)| 00:00:07 |
|   1 |  COUNT                         |                            |       |       |            |          |
|*  2 |   FILTER                       |                            |       |       |            |          |
|   3 |    VIEW                        |                            |   311 | 10885 |   508   (8)| 00:00:07 |
|   4 |     SORT ORDER BY              |                            |   311 | 10885 |   508   (8)| 00:00:07 |
|   5 |      HASH GROUP BY             |                            |   311 | 10885 |   508   (8)| 00:00:07 |
|   6 |       VIEW                     | TEMP_VW                    |   311 | 10885 |   506   (7)| 00:00:07 |
|   7 |        UNION-ALL               |                            |       |       |            |          |
|   8 |         NESTED LOOPS OUTER     |                            |   249 | 18426 |   286   (9)| 00:00:04 |
|*  9 |          HASH JOIN             |                            |   249 | 17430 |   285   (9)| 00:00:04 |
|* 10 |           TABLE ACCESS FULL    | SLT_SLIQ_TRADE             |   250 |  9500 |   247   (6)| 00:00:03 |
|  11 |           NESTED LOOPS         |                            | 25000 |   781K|    37  (25)| 00:00:01 |
|  12 |            INDEX FAST FULL SCAN| SLT_SLIQ_PORTFOLIO_PK      | 25000 |   390K|    29   (4)| 00:00:01 |
|* 13 |            INDEX RANGE SCAN    | SLT_SLIQ_TRADE_FACILITY_PK |     1 |    16 |     0   (0)| 00:00:01 |
|* 14 |          INDEX RANGE SCAN      | SLT_E_SETTL_TRADE_PK       |     1 |     4 |     0   (0)| 00:00:01 |
|  15 |         NESTED LOOPS           |                            |    62 |  1736 |   220   (5)| 00:00:03 |
|* 16 |          HASH JOIN ANTI        |                            |    32 |   768 |   188   (6)| 00:00:03 |
|* 17 |           TABLE ACCESS FULL    | SLT_E_SETTL_TRADE          |   263 |  5260 |   175   (6)| 00:00:03 |
|* 18 |           INDEX FAST FULL SCAN | SLT_SLIQ_TRADE_IE1         | 22137 | 88548 |    13   (8)| 00:00:01 |
|* 19 |          INDEX RANGE SCAN      | SLT_E_SETTL_FACILITY_PK    |     2 |     8 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM>1)
   9 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
  10 - filter(CASE  WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
              ELSE 1 END =1)
  13 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
              "SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
  14 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
  16 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"="SLT_E_SETTL_TRADE"."SLT_TRADE_ID")
  17 - filter(CASE  WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM'
              AND "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN'
              THEN 2 ELSE 1 END =1)
  18 - filter("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID" IS NOT NULL)
  19 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
     102155  consistent gets
          0  physical reads
          0  redo size
        397  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>


Query 2: Selected only 16 columns
WITH TMP AS (
SELECT group_id,
       slt_trade_id,
       slt_parent_trade_id,
       is_parent_flag,
       trade_type_ind,
       trade_rid,
       trade_id,
       parent_trade_id,
       cls_type_ind,
       e_settl_trade_id,
       trade_ref_id,
       trade_lock_ind,
       deal_name,
       deal_cusip,
       excp_type_code,
       facility_name
  from TEMP_VW V 
  ), 
  GRPS AS (
    SELECT GROUP_ID, ROWNUM AS RECORD_NUM
    FROM ( 
    SELECT GROUP_ID, MIN( facility_name ) AS MIN_FAC_NAME
        FROM   TMP 
        GROUP BY GROUP_ID
        ORDER BY 2, 1 
        )) 
SELECT  A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM    GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN  TMP C ON C.GROUP_ID = A.GROUP_ID
WHERE RECORD_NUM BETWEEN 1 AND 50 
ORDER BY 1 , facility_name  ASC ;

Elapsed: 00:00:06.57

Execution Plan
----------------------------------------------------------
Plan hash value: 3325484232

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |   205 |  1867   (3)| 00:00:23 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|   3 |    VIEW                    | TEMP_VW                     |   764 |   123K|  1847   (3)| 00:00:23 |
|   4 |     UNION-ALL              |                             |       |       |            |          |
|*  5 |      HASH JOIN OUTER       |                             |   249 | 29382 |   936   (3)| 00:00:12 |
|*  6 |       HASH JOIN            |                             |   249 | 26145 |   768   (4)| 00:00:10 |
|*  7 |        HASH JOIN           |                             |   498 | 44322 |   739   (4)| 00:00:09 |
|*  8 |         TABLE ACCESS FULL  | SLT_SLIQ_TRADE              |   250 | 14500 |   247   (6)| 00:00:03 |
|   9 |         TABLE ACCESS FULL  | SLT_SLIQ_TRADE_FACILITY     | 50000 |  1513K|   490   (2)| 00:00:06 |
|  10 |        INDEX FAST FULL SCAN| SLT_SLIQ_PORTFOLIO_PK       | 25000 |   390K|    29   (4)| 00:00:01 |
|  11 |       TABLE ACCESS FULL    | SLT_E_SETTL_TRADE           | 26256 |   333K|   167   (2)| 00:00:03 |
|* 12 |      FILTER                |                             |       |       |            |          |
|* 13 |       HASH JOIN            |                             |   515 | 36050 |   653   (3)| 00:00:08 |
|* 14 |        TABLE ACCESS FULL   | SLT_E_SETTL_TRADE           |   263 | 12098 |   175   (6)| 00:00:03 |
|  15 |        TABLE ACCESS FULL   | SLT_E_SETTL_FACILITY        | 50000 |  1171K|   476   (2)| 00:00:06 |
|* 16 |       INDEX RANGE SCAN     | SLT_SLIQ_TRADE_IE1          |     1 |     4 |     1   (0)| 00:00:01 |
|  17 |   LOAD AS SELECT           |                             |       |       |            |          |
|  18 |    COUNT                   |                             |       |       |            |          |
|  19 |     VIEW                   |                             |     1 |    13 |     8  (25)| 00:00:01 |
|  20 |      SORT ORDER BY         |                             |     1 |    30 |     8  (25)| 00:00:01 |
|  21 |       SORT GROUP BY        |                             |     1 |    30 |     8  (25)| 00:00:01 |
|  22 |        VIEW                |                             |   764 | 22920 |     6   (0)| 00:00:01 |
|  23 |         TABLE ACCESS FULL  | SYS_TEMP_0FD9D688C_2E882A84 |   764 |   123K|     6   (0)| 00:00:01 |
|  24 |   SORT ORDER BY            |                             |     1 |   205 |    12  (17)| 00:00:01 |
|* 25 |    HASH JOIN               |                             |     1 |   205 |    11  (10)| 00:00:01 |
|  26 |     NESTED LOOPS           |                             |   764 | 29796 |     4   (0)| 00:00:01 |
|  27 |      VIEW                  |                             |     1 |    13 |     2   (0)| 00:00:01 |
|* 28 |       FILTER               |                             |       |       |            |          |
|  29 |        SORT AGGREGATE      |                             |     1 |       |            |          |
|  30 |         VIEW               |                             |   764 |       |     2   (0)| 00:00:01 |
|  31 |          TABLE ACCESS FULL | SYS_TEMP_0FD9D688D_2E882A84 |   764 |  9932 |     2   (0)| 00:00:01 |
|* 32 |      VIEW                  |                             |   764 | 19864 |     2   (0)| 00:00:01 |
|  33 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D688D_2E882A84 |   764 |  9932 |     2   (0)| 00:00:01 |
|  34 |     VIEW                   |                             |   764 |   123K|     6   (0)| 00:00:01 |
|  35 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D688C_2E882A84 |   764 |   123K|     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   5 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
   6 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
              "SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
   7 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
   8 - filter(CASE  WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
              ELSE 1 END =1)
  12 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
              "SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
  13 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
  14 - filter(CASE  WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN
              ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN
              2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
  16 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)
  25 - access("C"."GROUP_ID"="A"."GROUP_ID")
  28 - filter(1<=50)
  32 - filter("A"."RECORD_NUM">=1 AND "A"."RECORD_NUM"<=50)


Statistics
----------------------------------------------------------
        479  recursive calls
        436  db block gets
     106882  consistent gets
        413  physical reads
       3060  redo size
       6361  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         58  rows processed

SQL>

Query 3: Select 112 columns
WITH TMP AS (
SELECT * from TEMP_VW V 
  ), 
  GRPS AS (
    SELECT GROUP_ID, ROWNUM AS RECORD_NUM
    FROM ( 
    SELECT GROUP_ID, MIN( facility_name ) AS MIN_FAC_NAME
        FROM   TMP 
        GROUP BY GROUP_ID
        ORDER BY 2, 1 
        )) 
SELECT  A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM    GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN  TMP C ON C.GROUP_ID = A.GROUP_ID
WHERE RECORD_NUM BETWEEN 1 AND 50 
ORDER BY 1 , facility_name  ASC ;

Elapsed: 00:00:15.60

Execution Plan
----------------------------------------------------------
Plan hash value: 2296946159

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |  3104 |  2123   (3)| 00:00:26 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|   3 |    VIEW                    | TEMP_VW                     |   764 |  2286K|  1972   (3)| 00:00:24 |
|   4 |     UNION-ALL              |                             |       |       |            |          |
|*  5 |      HASH JOIN OUTER       |                             |   249 |   130K|  1061   (3)| 00:00:13 |
|*  6 |       HASH JOIN            |                             |   249 |   127K|   893   (3)| 00:00:11 |
|*  7 |        HASH JOIN           |                             |   498 |   204K|   739   (4)| 00:00:09 |
|*  8 |         TABLE ACCESS FULL  | SLT_SLIQ_TRADE              |   250 | 50000 |   247   (6)| 00:00:03 |
|   9 |         TABLE ACCESS FULL  | SLT_SLIQ_TRADE_FACILITY     | 50000 |    10M|   490   (2)| 00:00:06 |
|  10 |        TABLE ACCESS FULL   | SLT_SLIQ_PORTFOLIO          | 25000 |  2539K|   153   (2)| 00:00:02 |
|  11 |       TABLE ACCESS FULL    | SLT_E_SETTL_TRADE           | 26256 |   333K|   167   (2)| 00:00:03 |
|* 12 |      FILTER                |                             |       |       |            |          |
|* 13 |       HASH JOIN            |                             |   515 |   158K|   653   (3)| 00:00:08 |
|* 14 |        TABLE ACCESS FULL   | SLT_E_SETTL_TRADE           |   263 | 26563 |   175   (6)| 00:00:03 |
|  15 |        TABLE ACCESS FULL   | SLT_E_SETTL_FACILITY        | 50000 |    10M|   476   (2)| 00:00:06 |
|* 16 |       INDEX RANGE SCAN     | SLT_SLIQ_TRADE_IE1          |     1 |     4 |     1   (0)| 00:00:01 |
|  17 |   LOAD AS SELECT           |                             |       |       |            |          |
|  18 |    COUNT                   |                             |       |       |            |          |
|  19 |     VIEW                   |                             |     1 |    13 |    74   (5)| 00:00:01 |
|  20 |      SORT ORDER BY         |                             |     1 |    30 |    74   (5)| 00:00:01 |
|  21 |       SORT GROUP BY        |                             |     1 |    30 |    74   (5)| 00:00:01 |
|  22 |        VIEW                |                             |   764 | 22920 |    72   (2)| 00:00:01 |
|  23 |         TABLE ACCESS FULL  | SYS_TEMP_0FD9D6892_2E882A84 |   764 |  2286K|    72   (2)| 00:00:01 |
|  24 |   SORT ORDER BY            |                             |     1 |  3104 |    77   (3)| 00:00:01 |
|* 25 |    HASH JOIN               |                             |     1 |  3104 |    76   (2)| 00:00:01 |
|  26 |     NESTED LOOPS           |                             |   764 | 29796 |     4   (0)| 00:00:01 |
|  27 |      VIEW                  |                             |     1 |    13 |     2   (0)| 00:00:01 |
|* 28 |       FILTER               |                             |       |       |            |          |
|  29 |        SORT AGGREGATE      |                             |     1 |       |            |          |
|  30 |         VIEW               |                             |   764 |       |     2   (0)| 00:00:01 |
|  31 |          TABLE ACCESS FULL | SYS_TEMP_0FD9D6893_2E882A84 |   764 |  9932 |     2   (0)| 00:00:01 |
|* 32 |      VIEW                  |                             |   764 | 19864 |     2   (0)| 00:00:01 |
|  33 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6893_2E882A84 |   764 |  9932 |     2   (0)| 00:00:01 |
|  34 |     VIEW                   |                             |   764 |  2286K|    72   (2)| 00:00:01 |
|  35 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6892_2E882A84 |   764 |  2286K|    72   (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   5 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
   6 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
              "SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
   7 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
   8 - filter(CASE  WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
              ELSE 1 END =1)
  12 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
              "SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
  13 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
  14 - filter(CASE  WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN
              ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN
              2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
  16 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)
  25 - access("C"."GROUP_ID"="A"."GROUP_ID")
  28 - filter(1<=50)
  32 - filter("A"."RECORD_NUM">=1 AND "A"."RECORD_NUM"<=50)


Statistics
----------------------------------------------------------
       2416  recursive calls
       2550  db block gets
     111801  consistent gets
       2494  physical reads
       4684  redo size
      35149  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         58  rows processed

SQL>

Query 4: Select to TEMP_VW
SELECT *  FROM TEMP_VW  ORDER BY 1 ; 

Elapsed: 00:03:55.39

Execution Plan
----------------------------------------------------------
Plan hash value: 3305677553

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |   311 |   930K|       |  1546   (3)| 00:00:19 |
|   1 |  SORT ORDER BY                 |                         |   311 |   930K|  2504K|  1546   (3)| 00:00:19 |
|   2 |   VIEW                         | TEMP_VW                 |   311 |   930K|       |  1344   (4)| 00:00:17 |
|   3 |    UNION-ALL                   |                         |       |       |       |            |          |
|*  4 |     HASH JOIN OUTER            |                         |   249 |   130K|       |  1061   (3)| 00:00:13 |
|*  5 |      HASH JOIN                 |                         |   249 |   127K|       |   893   (3)| 00:00:11 |
|*  6 |       HASH JOIN                |                         |   498 |   204K|       |   739   (4)| 00:00:09 |
|*  7 |        TABLE ACCESS FULL       | SLT_SLIQ_TRADE          |   250 | 50000 |       |   247   (6)| 00:00:03 |
|   8 |        TABLE ACCESS FULL       | SLT_SLIQ_TRADE_FACILITY | 50000 |    10M|       |   490   (2)| 00:00:06 |
|   9 |       TABLE ACCESS FULL        | SLT_SLIQ_PORTFOLIO      | 25000 |  2539K|       |   153   (2)| 00:00:02 |
|  10 |      TABLE ACCESS FULL         | SLT_E_SETTL_TRADE       | 26256 |   333K|       |   167   (2)| 00:00:03 |
|  11 |     TABLE ACCESS BY INDEX ROWID| SLT_E_SETTL_FACILITY    |     2 |   430 |       |     3   (0)| 00:00:01 |
|  12 |      NESTED LOOPS              |                         |    62 | 19840 |       |   283   (5)| 00:00:04 |
|* 13 |       HASH JOIN ANTI           |                         |    32 |  3360 |       |   188   (6)| 00:00:03 |
|* 14 |        TABLE ACCESS FULL       | SLT_E_SETTL_TRADE       |   263 | 26563 |       |   175   (6)| 00:00:03 |
|* 15 |        INDEX FAST FULL SCAN    | SLT_SLIQ_TRADE_IE1      | 22137 | 88548 |       |    13   (8)| 00:00:01 |
|* 16 |       INDEX RANGE SCAN         | SLT_E_SETTL_FACILITY_PK |     2 |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   4 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
   5 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
              "SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
   6 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
   7 - filter(CASE  WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1
              END =1)
  13 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"="SLT_E_SETTL_TRADE"."SLT_TRADE_ID")
  14 - filter(CASE  WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
              "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
              ELSE 1 END =1)
  15 - filter("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID" IS NOT NULL)
  16 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      17506  consistent gets
          0  physical reads
          0  redo size
   14136794  bytes sent via SQL*Net to client
      23195  bytes received via SQL*Net from client
       2068  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      31000  rows processed

SQL>

Re: Need suggestion for a better query. [message #337098 is a reply to message #336912] Tue, 29 July 2008 21:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The final query is using a different plan for the view - strange - but that is not the one you are trying to tune (right?) so I'll ignore it.

I assume you are trying to tune the 3rd query.

I noticed that the view contains a bunch of scalar sub-queries in the SELECT clause. You need to get rid of them. See this article for more.

Ross Leishman
Re: Need suggestion for a better query. [message #338275 is a reply to message #337098] Mon, 04 August 2008 07:27 Go to previous message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Thanks Ross,
The query you was provided,
i am sure it can not BE further tunned.
Though my query is taking time because of the view and i
am thinking of using flat table which will be populated based on that view. hope i am going in the right direction.
i have also done test, created single table based on that view and when fire the query against it, its just takes approx 2 sec.
CREATE TABLE FLAT_TAB AS SELECT * FROM TEMP_VW.
I have choose this approch bec
1. Cant reduce no. of tables in the view
2. Can avoide sub-queries and CASE and other functions.in the
view
3. Union ALL.

I am sorry to myself and You for getting again late to reply.

Regards
Pravin.




Previous Topic: SQL Tuning Methods & Books
Next Topic: Merging large amounts of data into large tables
Goto Forum:
  


Current Time: Sun Dec 04 16:40:04 CST 2016

Total time taken to generate the page: 0.13214 seconds