Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Tuning the SQL Query

I am runing the query in the script but it take more than 3 hours in real time. The query and explain plan is given below.

SELECT *
FROM c1 a 
where cab1 IN (select DISTINCT (A.cab2)cab
                                   from a1 A
                                   where not exists (select g.fab1 
                                                     from B1 g
                                                     where A.cab2=g.fab1)
    and not exists (select g.FAB2
			from B1 g
			where A.cab3=g.fab2)
    and A.cdate<= TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-2),'yyyy')

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------
| Id  | Operation                       |  Name                        | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |   219K|    10M|       | 34806 |
|   1 |  MERGE JOIN SEMI                |                              |   219K|    10M|       | 34806 |
|   2 |   SORT JOIN                     |                              |   219K|  7506K|    23M|  1535 |
|   3 |    TABLE ACCESS FULL            | C1			       |   219K|  7506K|       |   122 |
|*  4 |   SORT UNIQUE                   |                              |  5987 | 77831 |   248K| 33272 |
|   5 |    VIEW                         | VW_NSO_1                     |  5987 | 77831 |       | 45225 |
|*  6 |     FILTER                      |                              |       |       |       |       |
|   7 |      TABLE ACCESS BY INDEX ROWID| A1                           |  5987 | 89805 |       | 33251 |
|*  8 |       INDEX RANGE SCAN          | B1_TDATE		       |   431K|       |       |  1670 |
|*  9 |      INDEX RANGE SCAN           | B1_PSR             	       |     4 |    16 |       |     1 |
|* 10 |      INDEX RANGE SCAN           | B1_RPK  		       |     2 |    10 |       |     1 |
--------------------------------------------------------------------------------------------------------
                                                                                                        
Predicate Information (identified by operation id):                                                     
---------------------------------------------------                                                     
                                                                                                        
   4 - access("A"."CAB1"="VW_NSO_1"."CAB")                                                              
       filter("A"."CAB1"="VW_NSO_1"."CAB")                                                              
   6 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "B1" "F" WHERE "F"."FAB1"=:B1)                          
              AND  NOT EXISTS (SELECT /*+ */ 0 FROM "B1" "F" WHERE "F"."FAB2"=:B2))                     
   8 - access("SYS_ALIAS_2"."CDATE"<=TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE@!,'yyyy'))-2),'yyyy')    
              )                                                                                         
   9 - access("G"."FAB2"=:B1)   
  10 - access("G"."FAB2"=:B1)   
                                
Note: cpu costing is off   

Open in new window

   
30 rows selected.
0
irsbenz
Asked:
irsbenz
  • 2
  • 2
  • 2
  • +2
1 Solution
 
johnsoneSenior Oracle DBACommented:
What indexes exist on the C1 table?

I don't see anything that jumps out at me that would help.

I do want to point out you are doing a lot of data type conversions on your date.  This would accomplish the same thing:

add_months(trunc(sysdate,'mm'), -24)
0
 
tigin44Commented:
try this

SELECT A.*
FROM C1 A
      INNER JOIN A1 B ON A.cab1 = B.cab2
      LEFT JOIN B1 G ON A.cab2 = G.fab1
      LEFT JOIN B1 H ON A.cab3 = H.fab2
WHERE G.fab1 IS NULL
  AND H.fab2 IS NULL
  AND A.cdate<= TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-2),'yyyy')
0
 
tigin44Commented:
missed an allias in the above post.. try this..

SELECT A.*
FROM C1 A
      INNER JOIN A1 B ON A.cab1 = B.cab2
      LEFT JOIN B1 G ON B.cab2 = G.fab1
      LEFT JOIN B1 H ON A.cab3 = H.fab2
WHERE G.fab1 IS NULL
  AND H.fab2 IS NULL
  AND A.cdate<= TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-2),'yyyy')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ianmills2002Commented:
Try something like this. I have removed the IN and DISTINCT. the DISTINCT can be very slow with large amounts of data.

SELECT *
FROM c1 a 
where cab1 exists (select *
                               from a1
                               where a1.cab2 = a.cab1)
  and cab1 not exists (select *
                                   from B1 g
                                    where a.cab1=g.fab1)
    and not exists (select g.FAB2
			     from B1 g
			     where A.cab3=g.fab2)
    and A.cdate<= TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-2),'yyyy')

Open in new window

0
 
irsbenzAuthor Commented:
Hi All,
There is a typo in my last query please see the updated query. SELECT *
FROM c1 c
where c.cab1 IN (select DISTINCT (A.cab2)cab2
                                   from a1 A
                                   where not exists (select g.fab1
                                                     from b1 g
                                                     where A.cab2=g.fab1)
                               and not exists (select g.FAB2
                                       from B1 g
                                       where A.cab3=g.fab2)
                            and A.cdate<= TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-2),'yyyy')
0
 
yuchingCommented:
Try this

SELECT *
FROM c1 c
INNER JOIN (
    SELECT a.cab2
    FROM a1 A
    WHERE A.cdate<= TO_DATE(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-2),'yyyy')      
    AND NOT EXISTS (
        SELECT 1 from b1 g WHERE a.cab2 = g.fab1 OR a.cab3 = g.fab2
   )
) d on d.cab2 = c.cab1
0
 
irsbenzAuthor Commented:
Hi Yuching,
Thanks for reply. the modified query which you have send mention table d but in my query I don't have any d table.
I have on 3 table C1,a1,b1.

Waiting for your reply.
Thanks in advance.
0
 
yuchingCommented:
Hi, the d is a derived table result from query a1 and b1
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now