Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Unanswered: Views integration

    I had to make a view of a view in order to complete what I was looking for but as you might guess is really slow and a bad practice in all departments. For some reason I haven't been able to make it work in one view so I came to you guys. Hope you can help me.

    The pre-view:
    Code:
    CREATE OR REPLACE  VIEW SPCTrendTemp AS ( SELECT  
    (SELECT F_NAME FROM SHFT_DAT A WHERE SGRP_INF.F_SPLT = A.F_SHFT) AS Shift, 
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP 
    AND B.F_NAME= 'NCR NUMBER' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS NCR,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'PRR NUMBER' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS PRR,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'RISK LEVEL' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS RISK,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'LINE/MACHINE NUMBER' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS LINE_MACHINE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'REASON' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS REASON,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'DATE GENERATED' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING'),'MM/DD/YYYY') AS GENERATED,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND
     B.F_NAME= 'INCIDENT DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING'),'MM/DD/YYYY') AS INCIDENT_DATE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'RESPONSIBLE PERSON' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS RESPONSIBLE_PERSON,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'DUE DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING'),'MM/DD/YYYY') AS DUE_DATE,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND
     B.F_NAME= 'APPROVAL DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING'),'MM/DD/YYYY') AS APPROVAL_DATE,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'COMPLETION DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING'),'MM/DD/YYYY') AS COMPLETION_DATE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'COMPLETED BY' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS COMPLETED_BY,
    TO_DATE((SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'EXTENSION NEW DUE DATE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING'),'MM/DD/YYYY') AS EXTENSION_NEW_DUE_DATE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'EXTENSIONS' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS EXTENSIONS,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'ROOT CAUSE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS ROOT_CAUSE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'STATUS' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS STATUS,
    (select case when due < ext then due else ext   end  from (select dd.due as due,ee.ext as ext  ,dd.idn as idn  from (SELECT c.F_sgrp idn, 
    round(sysdate-to_date(A.F_NAME,'MM/DD/YYYY')) due FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE 
    A.F_DSGP = B.F_DSGP AND B.F_NAME= 'DUE DATE' AND C.F_SGRP = D.F_SGRP AND A.F_DESC = D.F_DESC AND C.F_FLAG = 0 ) dd,
    (SELECT c.F_sgrp idn, round(sysdate-to_date(A.F_NAME,'MM/DD/YYYY')) ext FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E
    WHERE A.F_DSGP = B.F_DSGP AND B.F_NAME= 'EXTENSION NEW DUE DATE' AND C.F_SGRP = D.F_SGRP AND A.F_DESC = D.F_DESC AND
     C.F_FLAG = 0 AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') ee,(select F_SGRP idn from SGRP_TST) tt WHERE tt.idn=dd.idn AND tt.idn=ee.idn) where idn=SGRP_INF.F_SGRP) as DAYS_OVERDUE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'TURN AROUND TIME' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS TURN_AROUND_TIME,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'OVERDUE' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP) AS OVERDUE,
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND 
    B.F_NAME= 'COMMENT' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS COMMENTS,
    (SELECT A.F_NAME  FROM PART_DAT A WHERE A.F_PART =SGRP_INF.F_PART) AS PART, 
    (SELECT A.F_NAME FROM PRCS_DAT A WHERE A.F_PRCS =SGRP_INF.F_PRCS)AS PROCESS, 
    (SELECT A.F_NAME FROM TEST_DAT A WHERE A.F_TEST =SGRP_TST.F_TEST) AS TEST, 
    (SELECT A.F_NAME FROM  PART_LOT A WHERE A.F_LOT = SGRP_INF.F_LOT) AS LOT,
    (SELECT A.F_NAME FROM EMPL_INF A WHERE A.F_EMPL =SGRP_INF.F_EMPL) AS EMPLOYEE 
     FROM SGRP_INF, SGRP_TST WHERE SGRP_INF.F_SGRP=SGRP_TST.F_SGRP  AND SGRP_INF.F_FLAG =0 AND (SELECT A.F_NAME FROM PRCS_DAT A WHERE A.F_PRCS =SGRP_INF.F_PRCS) = 'TRENDING')
    And now the view of the view:
    Code:
    CREATE OR REPLACE  VIEW SPCTrend AS ( select a.shift, a.ncr,a.prr,a.risk,a.line_machine,a.reason,a.generated,a.incident_date,a.responsible_person,a.due_date,a.approval_date,a.completion_date,a.completed_by,a.extension_new_due_date,a.extensions,a.root_cause,a.status,case when a.extension_new_due_date is null then (select round(sysdate - c.due_date)  from dual b, spctrendtemp c where a.ncr = c.ncr) else  (select round(sysdate - c.extension_new_due_date)  from dual b, spctrendtemp c where a.ncr = c.ncr) end days_overdue , a.turn_around_time, case when a.days_overdue>0 then 'OVERDUE' else 'ON SCHEDULE' end OVERDUE, a.comments,a.part,a.process,a.test,a.lot,a.employee from spctrendtemp a, dual b  where a.test = 'TREND')
    Any help is appreciated. Thanks in advance.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1. in the SPCTrend view why the joins to dual, they dont seem to do anything.

    2. in the SPCTrendTemp view it is simply too large to understand. However it looks like your repeating the same basic query as a subquery in the select part of the query. Remove the subqueries and move them to from..where part of the main sql so you are not executing the same subquery N times, just do it once. The fact that you select a different value of b.f_name can be handled the following way

    select
    max(case when b.f_name='NCR NUMBER' then A.F_NAME end) x,
    max(case when b.f_name='PRR NUMBER' then A.F_NAME end) x2
    ....
    from
    ....
    and b.f_name in (''NCR NUMBER' ,'PRR NUMBER' ...)
    group by <all the columns you need except for b.f_name)

    I may have misunderstood your view but I think the above solution will make your view smaller and much more efficient.

    3. Use a WITH clause as this may help elimanate or simply the multiple subqueries if the above solution doesnt work

    Alan

  3. #3
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    I tried the above suggestion but no luck. What it brought was a null row or a row full of nulls if you prefer. I know that the query is really big. The first view is proccessed prtty fast but when I run the second one it takes for ever.

    I'll try to reduce query to the real problem. There are a couple of coluns that are "calculated" based on contents of the same table. for example:

    From first view:
    .
    .
    .
    TO_DATE((SELECT A.F_NAME FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP AND
    B.F_NAME= 'EXT NEW DUE DATE' AND C.F_SGRP = D.F_SGRP AND A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS =
    E.F_PRCS AND E.F_NAME = 'TRENDING'),'MM/DD/YYYY') AS EXTENSION_NEW_DUE_DATE
    .
    .
    .

    And in second view:
    .
    .
    .
    case when a.extension_new_due_date is null then (select round(sysdate - c.due_date) from
    dual b, spctrendtemp c where a.ncr = c.ncr) else (select round(sysdate - c.extension_new_due_date) from dual b,
    spctrendtemp c where a.ncr = c.ncr) end days_overdue
    .
    .
    .

    Any ideas?

    Below is a database in Access with the tables and data to build the views and a .txt with the actualized queries for the views.
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I am not even going to try to figure out what your view does, but I am going to give you a technique to try to use. If you have multiple rows of information from a table, then use the following type of select

    select a.des,b.des,c.des,d.code
    from table1 a, table1 b, table1 c, table2 d
    where d.code = a.code
    and d.code = b.code
    and d.code = c.code
    and a.type = 'X'
    and b.type = 'Y'
    and c.type = 'Z';

    Using the same table multiple times in the same select with different where criteria is a very fast way of returning multiple bits of information from the same table in a single select.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    In subqueries like this:

    case ... else (select round(sysdate - c.extension_new_due_date) from dual b,
    spctrendtemp c where a.ncr = c.ncr) end

    DUAL isn't doing anything and you should remove it, as AlanP mentioned above.

  6. #6
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Quote Originally Posted by beilstwh
    I am not even going to try to figure out what your view does, but I am going to give you a technique to try to use. If you have multiple rows of information from a table, then use the following type of select

    select a.des,b.des,c.des,d.code
    from table1 a, table1 b, table1 c, table2 d
    where d.code = a.code
    and d.code = b.code
    and d.code = c.code
    and a.type = 'X'
    and b.type = 'Y'
    and c.type = 'Z';

    Using the same table multiple times in the same select with different where criteria is a very fast way of returning multiple bits of information from the same table in a single select.
    I know to do what you specify. The problem comes if the table/view you need you need data from in order to decide, with a case statement, the value of certain column is another column value in the same row of the same table/view. Hope I made myself clear now.

    Thanks in advance.

  7. #7
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Quote Originally Posted by WilliamR
    In subqueries like this:

    case ... else (select round(sysdate - c.extension_new_due_date) from dual b,
    spctrendtemp c where a.ncr = c.ncr) end

    DUAL isn't doing anything and you should remove it, as AlanP mentioned above.
    The part of the query you quote is from my original post or from the .txt attached? I think I fixed that in the .txt but correct me if wrong.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    No the refs to dual are in your view SPCTrend. Try this (didnt check it) as it should be much simpler.
    Code:
    CREATE OR REPLACE  VIEW SPCTrend AS 
    ( 
    	select a.shift, 	
    	a.ncr,a.prr,a.risk,a.line_machine,a.reason,a.generated,a.incident_date,a.responsible_person,a.due_date,
    	a.approval_date,a.completion_date,a.completed_by,a.extension_new_due_date,a.extensions,a.root_cause,a.status,
    	case when a.extension_new_due_date is null then round(sysdate - a.due_date) else  round(sysdate - a.extension_new_due_date end days_overdue , 
    	case when a.completion_date is null then round(sysdate - a.generated else round(a.completion_date - a.generated) end turn_around_time, 
    	case when a.days_overdue>0 then 'OVERDUE' else 'ON SCHEDULE' end OVERDUE, 
    	a.comments,a.part,a.process,a.test,a.lot 
    	from spctrendtemp a where a.test = 'TREND'
    )
    As for you larger view SPCTrendTemp you are basically repeating the same query over and over again except for b.f_name. It would be much better to create a result set (as a subquery) for all the values of b.f_name and then select out the specific values you are interested in.

    Alan

  9. #9
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    I removed the dual part. About your last comment an example should be great. How I'll use then the results of the subquery in the big one since the subquery will return more than one row?

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I gave the example earlier, unfortunately your query is so large it would take a long time for anyone to rewrite it. How bout you create a simpler version of the temp view which doesnt have so many subqueries, then we might be able to help better.

    Alan

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Good gosh, did you even see the plan of this query with the sample data you posted on here ? (that is, take this data, import it into oracle, create the view and run an explain plan for it -- it is *probably* not the actual plan, but it's the coolest effect I've ever seen), scroll it upwards and downwards and see it!
    Code:
    SQL> set autotrace on explain
    SQL> select * from SPCTrendTemp;
    
    no rows selected
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   TABLE ACCESS (FULL) OF 'SHFT_DAT'
       2    0   MERGE JOIN
       3    2     SORT (JOIN)
       4    3       MERGE JOIN
       5    4         SORT (JOIN)
       6    5           MERGE JOIN
       7    6             SORT (JOIN)
       8    7               MERGE JOIN
       9    8                 SORT (JOIN)
      10    9                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
      11    8                 SORT (JOIN)
      12   11                   TABLE ACCESS (FULL) OF 'SGRP_INF'
      13    6             SORT (JOIN)
      14   13               TABLE ACCESS (FULL) OF 'SGRP_DSC'
      15    4         SORT (JOIN)
      16   15           TABLE ACCESS (FULL) OF 'DESC_DAT'
      17    2     SORT (JOIN)
      18   17       TABLE ACCESS (FULL) OF 'DESC_GRP'
      19    0   MERGE JOIN
      20   19     SORT (JOIN)
      21   20       MERGE JOIN
      22   21         SORT (JOIN)
      23   22           MERGE JOIN
      24   23             SORT (JOIN)
      25   24               MERGE JOIN
      26   25                 SORT (JOIN)
      27   26                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
      28   25                 SORT (JOIN)
      29   28                   TABLE ACCESS (FULL) OF 'SGRP_INF'
      30   23             SORT (JOIN)
      31   30               TABLE ACCESS (FULL) OF 'SGRP_DSC'
      32   21         SORT (JOIN)
      33   32           TABLE ACCESS (FULL) OF 'DESC_DAT'
      34   19     SORT (JOIN)
      35   34       TABLE ACCESS (FULL) OF 'DESC_GRP'
      36    0   MERGE JOIN
      37   36     SORT (JOIN)
      38   37       MERGE JOIN
      39   38         SORT (JOIN)
      40   39           MERGE JOIN
      41   40             SORT (JOIN)
      42   41               MERGE JOIN
      43   42                 SORT (JOIN)
      44   43                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
      45   42                 SORT (JOIN)
      46   45                   TABLE ACCESS (FULL) OF 'SGRP_INF'
      47   40             SORT (JOIN)
      48   47               TABLE ACCESS (FULL) OF 'SGRP_DSC'
      49   38         SORT (JOIN)
      50   49           TABLE ACCESS (FULL) OF 'DESC_DAT'
      51   36     SORT (JOIN)
      52   51       TABLE ACCESS (FULL) OF 'DESC_GRP'
      53    0   MERGE JOIN
      54   53     SORT (JOIN)
      55   54       MERGE JOIN
      56   55         SORT (JOIN)
      57   56           MERGE JOIN
      58   57             SORT (JOIN)
      59   58               MERGE JOIN
      60   59                 SORT (JOIN)
      61   60                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
      62   59                 SORT (JOIN)
      63   62                   TABLE ACCESS (FULL) OF 'SGRP_INF'
      64   57             SORT (JOIN)
      65   64               TABLE ACCESS (FULL) OF 'SGRP_DSC'
      66   55         SORT (JOIN)
      67   66           TABLE ACCESS (FULL) OF 'DESC_DAT'
      68   53     SORT (JOIN)
      69   68       TABLE ACCESS (FULL) OF 'DESC_GRP'
      70    0   MERGE JOIN
      71   70     SORT (JOIN)
      72   71       MERGE JOIN
      73   72         SORT (JOIN)
      74   73           MERGE JOIN
      75   74             SORT (JOIN)
      76   75               MERGE JOIN
      77   76                 SORT (JOIN)
      78   77                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
      79   76                 SORT (JOIN)
      80   79                   TABLE ACCESS (FULL) OF 'SGRP_INF'
      81   74             SORT (JOIN)
      82   81               TABLE ACCESS (FULL) OF 'SGRP_DSC'
      83   72         SORT (JOIN)
      84   83           TABLE ACCESS (FULL) OF 'DESC_DAT'
      85   70     SORT (JOIN)
      86   85       TABLE ACCESS (FULL) OF 'DESC_GRP'
      87    0   MERGE JOIN
      88   87     SORT (JOIN)
      89   88       MERGE JOIN
      90   89         SORT (JOIN)
      91   90           MERGE JOIN
      92   91             SORT (JOIN)
      93   92               MERGE JOIN
      94   93                 SORT (JOIN)
      95   94                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
      96   93                 SORT (JOIN)
      97   96                   TABLE ACCESS (FULL) OF 'SGRP_INF'
      98   91             SORT (JOIN)
      99   98               TABLE ACCESS (FULL) OF 'SGRP_DSC'
     100   89         SORT (JOIN)
     101  100           TABLE ACCESS (FULL) OF 'DESC_DAT'
     102   87     SORT (JOIN)
     103  102       TABLE ACCESS (FULL) OF 'DESC_GRP'
     104    0   MERGE JOIN
     105  104     SORT (JOIN)
     106  105       MERGE JOIN
     107  106         SORT (JOIN)
     108  107           MERGE JOIN
     109  108             SORT (JOIN)
     110  109               MERGE JOIN
     111  110                 SORT (JOIN)
     112  111                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
     113  110                 SORT (JOIN)
     114  113                   TABLE ACCESS (FULL) OF 'SGRP_INF'
     115  108             SORT (JOIN)
     116  115               TABLE ACCESS (FULL) OF 'SGRP_DSC'
     117  106         SORT (JOIN)
     118  117           TABLE ACCESS (FULL) OF 'DESC_DAT'
     119  104     SORT (JOIN)
     120  119       TABLE ACCESS (FULL) OF 'DESC_GRP'
     .............................
     .............................
     295    0   MERGE JOIN
     296  295     SORT (JOIN)
     297  296       MERGE JOIN
     298  297         SORT (JOIN)
     299  298           MERGE JOIN
     300  299             SORT (JOIN)
     301  300               MERGE JOIN
     302  301                 SORT (JOIN)
     303  302                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
     304  301                 SORT (JOIN)
     305  304                   TABLE ACCESS (FULL) OF 'SGRP_INF'
     306  299             SORT (JOIN)
     307  306               TABLE ACCESS (FULL) OF 'SGRP_DSC'
     308  297         SORT (JOIN)
     309  308           TABLE ACCESS (FULL) OF 'DESC_DAT'
     310  295     SORT (JOIN)
     311  310       TABLE ACCESS (FULL) OF 'DESC_GRP'
     312    0   MERGE JOIN
     313  312     SORT (JOIN)
     314  313       MERGE JOIN
     315  314         SORT (JOIN)
     316  315           MERGE JOIN
     317  316             SORT (JOIN)
     318  317               NESTED LOOPS
     319  318                 TABLE ACCESS (FULL) OF 'PRCS_DAT'
     320  318                 TABLE ACCESS (FULL) OF 'SGRP_DSC'
     321  316             SORT (JOIN)
     322  321               TABLE ACCESS (FULL) OF 'SGRP_INF'
     323  314         SORT (JOIN)
     324  323           TABLE ACCESS (FULL) OF 'DESC_DAT'
     325  312     SORT (JOIN)
     326  325       TABLE ACCESS (FULL) OF 'DESC_GRP'
     327    0   MERGE JOIN
     328  327     SORT (JOIN)
     329  328       MERGE JOIN
     330  329         SORT (JOIN)
     331  330           MERGE JOIN
     332  331             SORT (JOIN)
     333  332               MERGE JOIN
     334  333                 SORT (JOIN)
     335  334                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
     336  333                 SORT (JOIN)
     337  336                   TABLE ACCESS (FULL) OF 'SGRP_INF'
     338  331             SORT (JOIN)
     339  338               TABLE ACCESS (FULL) OF 'SGRP_DSC'
     340  329         SORT (JOIN)
     341  340           TABLE ACCESS (FULL) OF 'DESC_DAT'
     342  327     SORT (JOIN)
     343  342       TABLE ACCESS (FULL) OF 'DESC_GRP'
     344    0   TABLE ACCESS (FULL) OF 'PART_DAT'
     345    0   TABLE ACCESS (FULL) OF 'PRCS_DAT'
     346    0   TABLE ACCESS (FULL) OF 'TEST_DAT'
     347    0   MERGE JOIN
     348  347     SORT (JOIN)
     349  348       MERGE JOIN
     350  349         SORT (JOIN)
     351  350           MERGE JOIN
     352  351             SORT (JOIN)
     353  352               MERGE JOIN
     354  353                 SORT (JOIN)
     355  354                   TABLE ACCESS (FULL) OF 'PRCS_DAT'
     356  353                 SORT (JOIN)
     357  356                   TABLE ACCESS (FULL) OF 'SGRP_INF'
     358  351             SORT (JOIN)
     359  358               TABLE ACCESS (FULL) OF 'SGRP_DSC'
     360  349         SORT (JOIN)
     361  360           TABLE ACCESS (FULL) OF 'DESC_DAT'
     362  347     SORT (JOIN)
     363  362       TABLE ACCESS (FULL) OF 'DESC_GRP'
     364    0   TABLE ACCESS (FULL) OF 'EMPL_INF'
     365    0   FILTER
     366  365     MERGE JOIN
     367  366       SORT (JOIN)
     368  367         TABLE ACCESS (FULL) OF 'SGRP_TST'
     369  366       SORT (JOIN)
     370  369         TABLE ACCESS (FULL) OF 'SGRP_INF'
     371  365     TABLE ACCESS (FULL) OF 'PRCS_DAT'
    
    SQL>
    I had to cut more than half of it because of the forum's limitations. Anyway, back to the solution: I see that in almost all the scalar subquerys, you're always joining the tables DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E all together back to its driving query. Why dont just make a join (or a right join if you want to have all the rows from your driving query and those that match from these tables) to these tables and use basic CASE statements to apply your criteria on column F_NAME of table DESC_GRP. That is, something like
    Code:
    select case when b_f_name = 'COMMENT'
                then a_f_name
            end comments,
           case when b_f_name = 'OVERDUE'
                then a_f_name
            end overdue
           ........
      from (
           SELECT A.F_NAME A_F_NAME, B.F_NAME B_F_NAME
             FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E, SGRP_TST T
            WHERE A.F_DSGP = B.F_DSGP
              AND C.F_SGRP = D.F_SGRP
              AND A.F_DESC = D.F_DESC
              AND C.F_SGRP = SGRP_INF.F_SGRP
              AND C.F_PRCS = E.F_PRCS
              AND E.F_NAME = 'TRENDING'
              AND C.F_FLAG = 0
              AND C.F_SGRP = T.F_SGRP
           )
    This will probably cut the time by half (if not much). Try tweaking it a litle bit like this (this is left as an excersize for you), and see how far can you get it.

  12. #12
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Quote Originally Posted by AlanP
    I gave the example earlier, unfortunately your query is so large it would take a long time for anyone to rewrite it. How bout you create a simpler version of the temp view which doesnt have so many subqueries, then we might be able to help better.

    Alan
    Here's a working view with only 2 subqueries. Those are the 2 types of subqueries. NCR is an example of the repetitive subquery where only B.F_NAME changes.

    Code:
    CREATE OR REPLACE  VIEW SPCTrendTemp AS ( SELECT  
    (SELECT F_NAME FROM SHFT_DAT A WHERE SGRP_INF.F_SPLT = A.F_SHFT) AS Shift, 
    (SELECT A.F_NAME  FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E WHERE A.F_DSGP = B.F_DSGP 
    AND B.F_NAME= 'NCR NUMBER' AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC AND C.F_SGRP = SGRP_INF.F_SGRP AND C.F_PRCS = E.F_PRCS AND E.F_NAME = 'TRENDING') AS NCR
    FROM SGRP_INF, SGRP_TST WHERE SGRP_INF.F_SGRP=SGRP_TST.F_SGRP  AND SGRP_INF.F_FLAG =0 AND (SELECT A.F_NAME FROM PRCS_DAT A WHERE A.F_PRCS =SGRP_INF.F_PRCS) = 'TRENDING')
    I know the view give a headache trying to read it but I deeply appreciate your help and effort.

  13. #13
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Here's my attempt with your suggestion Martinez:

    Code:
    select c,case when b_f_name = 'NCR NUMBER'
                then a_f_name
            end NCR,
           case when b_f_name = 'PRR NUMBER'
                then a_f_name
            end PRR,
            case when b_f_name = 'RISK LEVEL'
                then a_f_name
            end RISK,
            case when b_f_name = 'LINE/MACHINE'
                then a_f_name
            end LINE_MACHINE,
            case when b_f_name = 'REASON'
                then a_f_name
            end REASON,
            case when b_f_name = 'DATE GENERATED'
                then TO_DATE(a_f_name,'MM/DD/YYYY')
            end GENERATED,
            case when b_f_name = 'INCIDENT DATE'
                then TO_DATE(a_f_name,'MM/DD/YYYY')
            end INCIDENT,
            case when b_f_name = 'RESPONSIBLE PERSON'
                then a_f_name
            end RESPONSIBLE_PERSON,
            case when b_f_name = 'DUE DATE'
                then TO_DATE(a_f_name,'MM/DD/YYYY')
            end DUE_DATE,
            case when b_f_name = 'INVESTIGATION  APPROVAL DATE'
                then TO_DATE(a_f_name,'MM/DD/YYYY')
            end DUE_DATE,
            case when b_f_name = 'COMPLETION DATE'
                then TO_DATE(a_f_name,'MM/DD/YYYY')
            end COMPLETION_DATE,
            case when b_f_name = 'EXT NEW DUE DATE'
                then TO_DATE(a_f_name,'MM/DD/YYYY')
            end EXTENSION_NEW_DUE_DATE,
            case when b_f_name = 'EXTENSIONS'
                then a_f_name
            end EXTENSIONS,
            case when b_f_name = 'ROOT CAUSE'
                then a_f_name
            end ROOT_CAUSE,
            case when b_f_name = 'STATUS'
                then a_f_name
            end STATUS,
            case when b_f_name = 'TURN AROUND TIME'
                then a_f_name
            end TURN_AROUND_TIME,
            case when b_f_name = 'OVERDUE'
                then a_f_name
            end OVERDUE,
            case when b_f_name = 'COMMENT'
                then a_f_name
            end COMMENTS
      from (
           SELECT A.F_NAME A_F_NAME, B.F_NAME B_F_NAME, C.F_SGRP c
             FROM DESC_DAT A, DESC_GRP B, SGRP_INF C, SGRP_DSC D, PRCS_DAT E, SGRP_TST T, SGRP_INF
            WHERE A.F_DSGP = B.F_DSGP
              AND C.F_SGRP = D.F_SGRP
              AND A.F_DESC = D.F_DESC
              AND C.F_SGRP = SGRP_INF.F_SGRP
              AND C.F_PRCS = E.F_PRCS
              AND E.F_NAME = 'TRENDING'
              AND C.F_FLAG = 0
              AND C.F_SGRP = T.F_SGRP
           ) order by c
    It does give me the answer but not like I wanted. Here's a sample of the data:
    Code:
    1132151137	(null)	(null)	(null)	N/A	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	PC-2005-225	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	Moderate	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	2004-12-09 00:00:00.0	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	2005-02-01 00:00:00.0	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	2005-02-11 00:00:00.0	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	Closed	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	2005-02-11 00:00:00.0	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	Dimensions out of tolerance	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	Roberto Lopez	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	0	(null)	(null)	(null)	(null)	(null)
    1132151137	PRR-PCD-012005-002	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	2004-12-09 00:00:00.0	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	2005-02-28 00:00:00.0	(null)	(null)	(null)	(null)	(null)	(null)
    1132151137	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	EQUIPMENT	(null)	(null)	(null)	(null)
    As you might see it gives me the correct answers but each in a different row with the rest of the answers null. Is there a way to join them in one row? I know the key is c (SGRP_INF.F_SGRP) but I don't know how to express it.

    I'm getting way closer with your help and the query qorked like 100 times faster! Thanx!

  14. #14
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    *HINT: Use MIN/MAX on the other columns and group by the key (c).

  15. #15
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Quote Originally Posted by JMartinez
    *HINT: Use MIN/MAX on the other columns and group by the key (c).
    Maybe this is the solution but it takes 200 times more to execute (and still counting, waiting for the result).

    Actually I left my office for about 30 mins. and still waiting. Any other idea?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •