Re: Optimal SQL

From: Rajiv Iyer <raju.rgi_at_gmail.com>
Date: Fri, 24 May 2013 15:15:35 +0530
Message-ID: <CADxvSwOkAZ1hAj1bOf6ABENuddzSU-Roki3MDSSfsa2rQ0Mo6Q_at_mail.gmail.com>



Hi Pratap.
I do get the data by using partition clause and inner join. Thanks for the clue.
I have got 2 more options now to get me the required information.:

select id from (
select rank() over (partition by a.ship,a.merchant order by b.id) rk, b.id,a.ship,a.merchant from test_trn a, test_mst b where
(a.ship=b.ship and a.merchant=b.merchant) or (a.ship=b.ship)
) where rk=1;
select
(
select id from test_mst b where ((b.ship=a.ship and b.merchant=a.merchant) or (b.ship=a.ship))
and rownum=1
) mst_id
from test_trn a;
I will test and see which is the most efficient of these.

Thanks,
Rajiv

On Fri, May 24, 2013 at 3:58 AM, Pratap Singh -X (prsingh1 - SRS CONSULTING INC at Cisco) <prsingh1_at_cisco.com> wrote:

> 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 - 11:45:35 CEST

Original text of this message