Solved

Tuning the SQL Query

Posted on 2012-03-15
8
261 Views
Last Modified: 2012-04-23
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
Comment
Question by:irsbenz
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 37726687
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
 
LVL 26

Expert Comment

by:tigin44
ID: 37726762
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
 
LVL 26

Expert Comment

by:tigin44
ID: 37726782
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
 
LVL 6

Expert Comment

by:ianmills2002
ID: 37727306
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:irsbenz
ID: 37729362
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
 
LVL 11

Expert Comment

by:yuching
ID: 37736374
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
 

Author Comment

by:irsbenz
ID: 37740524
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
 
LVL 11

Accepted Solution

by:
yuching earned 500 total points
ID: 37742510
Hi, the d is a derived table result from query a1 and b1
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now