Optimal SQL

From: Rajiv Iyer <raju.rgi_at_gmail.com>
Date: Thu, 23 May 2013 21:05:23 +0530
Message-ID: <CADxvSwOfWDEgh9OALhGk0V_AKVi5QxDeOMVkEgDH7f75hcDDJg_at_mail.gmail.com>

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

Received on Thu May 23 2013 - 17:35:23 CEST