Home » SQL & PL/SQL » SQL & PL/SQL » In Class with sub query
In Class with sub query [message #671953] Tue, 25 September 2018 15:16 Go to next message
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 #671954 is a reply to message #671953] Tue, 25 September 2018 17:26 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
nagaraju.ch wrote on Tue, 25 September 2018 13:16
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
We don't have your tables.
We don't have your data.

So we can't run or test posted code.
Since you have the tables & the data why did not not simply actually run both SQL & measure the elapsed time directly yourself?

Post EXPLAIN PLAN for both SQL.
Re: In Class with sub query [message #671959 is a reply to message #671953] Wed, 26 September 2018 02:58 Go to previous messageGo to next message
John Watson
Messages: 8344
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>
and if the rewrite doesn't happen, the cost goes up:
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>
Re: In Class with sub query [message #671969 is a reply to message #671959] Wed, 26 September 2018 09:09 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Versions <12 can't unnest scalars either Smile

[Updated on: Wed, 26 September 2018 09:09]

Report message to a moderator

Previous Topic: How to Find the Parent of Index Organized Table
Next Topic: Create collection of nested table
Goto Forum:
  


Current Time: Wed Sep 23 01:35:28 CDT 2020