Home » SQL & PL/SQL » SQL & PL/SQL » Tuning the query
Tuning the query [message #393654] Tue, 24 March 2009 00:25 Go to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
I need to tune a query...

SELECT col1, col2
  FROM tbl1
 WHERE EXISTS
 (SELECT 1
          FROM tbl2, tbl3
         WHERE tbl2.col1 LIKE
               SUBSTR(tbl1.col1,
                      1,
                      DECODE(tbl1.col2, 1, 2, 3, 4, 5, 6) || '%')) AND
       tbl1.col2 <> 'X' AND
       tbl1.col3 = (SELECT MAX(col3) FROM tbl3 WHERE tbl1.col1 = tbl2.col2)

This query is taking a long time...pls help on this...already the col1,col2,col3 forms an unique composite index...
especially the substr and decode conditions are taking long time...

[Formatted by RL]

[Updated on: Tue, 24 March 2009 01:50] by Moderator

Report message to a moderator

Re: Tuning the query [message #393658 is a reply to message #393654] Tue, 24 March 2009 00:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Tuning the query [message #393666 is a reply to message #393654] Tue, 24 March 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Tuning the query [message #393698 is a reply to message #393666] Tue, 24 March 2009 01:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT col1, col2
FROM   tbl1
WHERE  EXISTS (
    SELECT 1
    FROM   tbl2, tbl3
    WHERE  tbl2.col1 LIKE
           SUBSTR(tbl1.col1, 1, 
                  DECODE(tbl1.col2, 1, 2, 3, 4, 5, 6) || '%'
                 )
)
AND   tbl1.col2 <> 'X' 
AND   tbl1.col3 = (
    SELECT MAX(col3) 
    FROM   tbl3 
    WHERE  tbl1.col1 = tbl2.col2
)

Doesn't formatting help?

In the EXISTS sub-query, you have joined TBL3 in the FROM clause, but there it is not joined to TBL2 or correllated to TBL1. That makes it a CARTESIAN JOIN. That can't be good.

Ross Leishman
Previous Topic: Rowise as column wise sum
Next Topic: how to increment number in left pad if number already exists
Goto Forum:
  


Current Time: Sun Dec 04 02:17:10 CST 2016

Total time taken to generate the page: 0.04733 seconds