RE: Need help in SQL tunning

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 21 Oct 2022 18:45:22 -0400
Message-ID: <097301d8e59e$ce049570$6a0dc050$_at_rsiz.com>

PS: I did all this early this morning, so I didn’t see the comments of the others, which are more about your existing query.  

I still think my re-write is iso-functional and what you should do, but maybe someone can point out a flaw.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Friday, October 21, 2022 6:20 PM
To: chrishna0007_at_gmail.com; 'Oracle L' Subject: RE: Need help in SQL tunning  

Not answering your question, but puzzled…  

select a.status, b.status from a left join b

on b.name = a.name  

would be the outer join.  

but then, you only want the rows returned when  

a.c_id in (select id from c where b.name =: <a value>….)  

now any no-match between a and b on name is going to return NULL for the value of b.name, that being the nature of outer joins.  

That makes me wonder why you don’t prune a first, before the join, and then equijoin the pruned a with b where a.name = b.name.  

further, any other value than 1 for sys_b_2 won’t get any rows, so it seems strange to use a variable there when using 1 would give the CBO more information. (If the application is going to submit for any other rownum than 1, simply don’t run the query.  

Trivial example (which I think is version invariant):  

SQL> r

  1 select a.status astat, b.status bstat, a.name aname, b.name bname

  2 from a left join b

  3 on b.name = a.name

  4 where a.c_id in (

  5 select id from c

  6 where b.name = 'one'

  7 )

  8* and rownum = 2  

no rows selected  

SQL> c/2/1

  8* and rownum = 1

SQL> r

  1 select a.status astat, b.status bstat, a.name aname, b.name bname

  2 from a left join b

  3 on b.name = a.name

  4 where a.c_id in (

  5 select id from c

  6 where b.name = 'one'

  7 )

  8* and rownum = 1  

     ASTAT      BSTAT ANAME                                    BNAME



---------- ---------- ---------------------------------------- ----------------------------------------



         1          4 one                                      one

 

should be friendlier to the CBO as

SQL> r

  1 with ax as (select a.status, a.name from a

  2 where a.name = 'one'

  3 and a.c_id in (select id from c)

  4 )

  5 select ax.status astat, b.status bstat, ax.name, b.name

  6 from ax, b

  7* where ax.name = b.name  

     ASTAT BSTAT NAME NAME

• ---------- ---------- ----------

         1 4 one one

         1 2 one one  

SQL> i

  8 and rownum = 1;  

     ASTAT BSTAT NAME NAME

• ---------- ---------- ----------

         1 4 one one  

SQL> 8   8* and rownum = 1

SQL> c/1/2

  8* and rownum = 2

SQL> r

  1 with ax as (select a.status, a.name from a

  2 where a.name = 'one'

  3 and a.c_id in (select id from c)

  4 )

  5 select ax.status astat, b.status bstat, ax.name, b.name

  6 from ax, b

  7 where ax.name = b.name

  8* and rownum = 2  

no rows selected  

Now if b smaller than a, you can do the similar with statement on b. Since the original is using a b value as a coordinated equality predicate, only the inner join results can be delivered, so if either pruning a or b is expensive compared to joining it with the pruned other, just prune the one cheapest to prune before the join (remembering to restrict the results of to a.id in c if that pruning was skipped as the with clause).  

Now if you really wanted 1,2 instead of 1,4 as the status results, then you need to order the data in the with clause anyway.        

using the data:  

SQL> select * from a;  

        ID NAME STATUS C_ID

• ---------- ---------- ----------

         1 one                 1         11

         2 two                 1         12

         3 three               1         13

 

SQL> select * from b;  

        ID NAME STATUS

• ---------- ----------

         1 one                 2

         3 three               2

         1 one                 4

 

SQL> select * from c;  

        ID TYPE

• ----------------------------------------

        11 type11

        11 type11

        12 type11

        12 type11  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Krishnaprasad Yadav Sent: Thursday, October 20, 2022 6:55 AM To: Oracle L
Subject: Need help in SQL tunning  

Hi Experts ,  

I have situation , where below was i need to tune , db version :12.1  

SQL Text

---

select a.status ,
b.status
 from ng_vid_d_verify a left join ng_vid_status b on b.wi_name = a.wi_name
 where a.c_id in (select id

                          from ng_structure_txn 
                           where b.wi_name = :"SYS_B_0"
                           and APPLICANT_TYPE = :"SYS_B_1") 

and rownum =:"SYS_B_2"

<snip>

--

http://www.freelists.org/webpage/oracle-l Received on Sat Oct 22 2022 - 00:45:22 CEST