| In Class with sub query [message #671953] |
Tue, 25 September 2018 15:16  |
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
|
Senior Member |
|
|
Hello
I have quick question on usage/performance.
I have requirement to transform attribute values based on value availability in subquery/view. So i wrote two different queries as below
One:
select
a.key
,CASE WHEN a.key1 IN (SELECT column1 FROM view) THEN 1 ELSE 0 AS value_1 //view is created based on multiple tables; this is same as below inline query
,CASE WHEN a.key1 NOT IN (SELECT column1 FROM view) THEN 2 ELSE Z AS value_2
,CASE WHEN a.key1 IN (SELECT column1 FROM view) THEN 3 ELSE 0 AS value_3
FROM
tab1 a
Two:
select
a.key
,CASE WHEN b.key1 IS NOT NULL THEN 1 ELSE 0 AS value_1
,CASE WHEN b.key1 IS NULL THEN 2 ELSE 0 AS value_2
,CASE WHEN b.key1 IS NOT NULL THEN 1 ELSE 0 AS value_3
FROM
tab1 a
LEFT OUTER JOIN
(SELECT column1 FROM tab1 a INNER JOIN tab2 b ON(a.key=b.key) //This query is complex and involves multiple tables; this is same as view in above query
ON(a.key=b.key)
could someone advise which one gives better performance or approach?
Thanks
|
|
|
|
|
|
| Re: In Class with sub query [message #671959 is a reply to message #671953] |
Wed, 26 September 2018 02:58  |
John Watson
Messages: 8836
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your first formulation, where you are projecting a column based on a subquery, is the worst kind of correlated subquery. In principle, you are asking Oracle to run the subquery three times (once for each column) for every row in tab1. The optimizer will always attempt to rewrite the query to a join (as in your second formulation) but if the subquery is too complex it cannot do this. If the rewrite cannot be done, a possible optimization is to cache the results of each subquery run so that it doesn't have to be re-run every time, but it is still much worse than the join.
See here, where the subquery is simple enough to be rewritten:pdby1>
pdby1> set autot on exp
pdby1> select dname,(select sum(sal) from emp where emp.deptno=dept.deptno) from dept;
DNAME (SELECTSUM(SAL)FROMEMPWHEREEMP.DEPTNO=DEPT.DEPTNO)
-------------- --------------------------------------------------
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
OPERATIONS
Execution Plan
----------------------------------------------------------
Plan hash value: 2834279049
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 156 | 7 (29)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 4 | 156 | 7 (29)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3 | 78 | 5 (40)| 00:00:01 |
| 5 | VIEW | VW_SSQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 6 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ITEM_1"(+)="DEPT"."DEPTNO")
filter("ITEM_1"(+)="DEPT"."DEPTNO")
pdby1>pdby1> select /*+ no_query_transformation */ dname,(select sum(sal) from emp where emp.deptno=dept.deptno) from dept;
DNAME (SELECTSUM(SAL)FROMEMPWHEREEMP.DEPTNO=DEPT.DEPTNO)
-------------- --------------------------------------------------
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
OPERATIONS
Execution Plan
----------------------------------------------------------
Plan hash value: 2018188441
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
pdby1> |
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
