RE: Need help in SQL tunning
Date: Fri, 21 Oct 2022 18:20:29 -0400
Message-ID: <088601d8e59b$56e5ec30$04b1c490$_at_rsiz.com>
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:20:29 CEST