Solved

Tuning the SQL Query

Posted on 2012-03-15
8
281 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
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.

 
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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

789 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