RE: Optimal SQL

From: Pratap Singh -X (prsingh1 - SRS CONSULTING INC at Cisco) <prsingh1_at_cisco.com>
Date: Thu, 23 May 2013 22:28:16 +0000
Message-ID: <BD1AACDC7F859F40BAB592DB89EEE1332251DC3E_at_xmb-aln-x02.cisco.com>

You can use outer join with over partition by first , second column and order by NULL last. You will need check for outer joined first column to be check not null to ensure at least first column is joined or put that a where instead on join. Thanks
PB Singh
Tech Project Manager
Ph P: 408 424 2777
Email: prsingh1_at_cisco.com

 Consider the environment. Please don't print this e-mail unless you really need to.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rajiv Iyer Sent: Thursday, May 23, 2013 8:35 AM
To: oracle-l_at_freelists.org
Subject: Optimal SQL

Hello.
I am trying to find out the most optimal way of writing sql for a particular problem.

I have two tables TEST_MST & TEST_TRN

CREATE TABLE TEST_MST(
ID NUMBER,
SHIP NUMBER,
MERCHANT NUMBER,
BILL NUMBER
);
CREATE TABLE TEST_TRN(
SALE_ID NUMBER ,
SALE_DATE DATE ,
SHIP NUMBER,
MERCHANT NUMBER
);
I want to get the id from the test_mst by joining with test_trn on ship and merchant columns. If no data is retrieved I would want to join only with the ship column and get the id.

Test Data:

insert into TEST_MST values (11423,1000,2000,111); insert into TEST_MST values (11424,1000,2000,112); insert into TEST_MST values (11425,1000,null,null); insert into TEST_MST values (11426,1001,2001,188); insert into TEST_MST values (11427,1001,2001,189); insert into TEST_TRN values (101,sysdate-3,1000,1124); insert into TEST_TRN values (102,sysdate-2,1001,2001); commit;

I get the required data by writing the following sql:

SELECT
NVL(
(SELECT B.ID FROM

TEST_MST B
WHERE B.SHIP = A.SHIP
and B.MERCHANT = a.MERCHANT
and rownum=1
),
(SELECT B.ID FROM

TEST_MST B
WHERE B.SHIP = A.SHIP
and rownum=1
)
) mst_id
FROM TEST_TRN A;
*Output:*

MST_ID

---

11423
11426

But the query slows down when there are lot of rows present in TEST_TRN. Is there any better way to write this sql?

Thanks,
Rajiv Iyer

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 24 2013 - 00:28:16 CEST