Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Tuning the SQL Query

Posted on 2012-03-15
8
Medium Priority
?
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 35

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 total points
ID: 37742510
Hi, the d is a derived table result from query a1 and b1
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

722 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