Oracle SQL Reuse sub query inside VIEW

Question

I am trying to set up a view which could potentially contain 1000's of records. Some of the fields requires subqueries which return the exact same data. I was wondering if I can query this once, within the view, instead of running it each time.

Below is some sample tables / data:

DROP VIEW MYVIEW;
DROP TABLE MYTABLE;
DROP TABLE MYTABLE_PARENT;

-- TABLES FOR VIEW
CREATE TABLE MYTABLE_PARENT ( PRIMARY_KEY NUMBER PRIMARY KEY );
CREATE TABLE MYTABLE ( PRIMARY_KEY NUMBER, MYVAL VARCHAR(255), PARENT_PRIMARY_KEY NUMBER);
INSERT INTO MYTABLE_PARENT VALUES (1);
INSERT INTO MYTABLE_PARENT VALUES (2);
INSERT INTO MYTABLE VALUES (1, 'MYVAL1-1', 1);
INSERT INTO MYTABLE VALUES (2, 'MYVAL1-2', 1);
INSERT INTO MYTABLE VALUES (3, 'MYVAL2-1', 2);

-- VIEW
CREATE OR REPLACE FORCE VIEW MYVIEW AS 
  SELECT CS.PRIMARY_KEY AS PARENT_PK,

  -- THE BELOW STATEMENTS REUSE THE SAME INNER SUBQUERY, IF THE 
  -- VIEW CONTAINS 1000 ROWS, THE INNER SUBQUERY WILL BE EXECUTED 
  -- 1000 TIMES, RETURNING THE SAME DATA EACH TIME.
  (SELECT PRIMARY_KEY FROM MYTABLE WHERE MYVAL = 'MYVAL1-1' AND 
  PARENT_PRIMARY_KEY = (SELECT PRIMARY_KEY FROM MYTABLE_PARENT 
  WHERE PRIMARY_KEY = CS.PRIMARY_KEY)) AS OUTPUT_VAL_1,

  (SELECT PRIMARY_KEY FROM MYTABLE WHERE MYVAL = 'MYVAL1-2' AND 
  PARENT_PRIMARY_KEY = (SELECT PRIMARY_KEY FROM MYTABLE_PARENT 
  WHERE PRIMARY_KEY = CS.PRIMARY_KEY)) AS OUTPUT_VAL_2
-- DEFINE CS
  FROM MYTABLE_PARENT CS;

SELECT * FROM MYVIEW;

The result from the select statement is the following:

PARENT_PK              OUTPUT_VAL_1           OUTPUT_VAL_2           
---------------------- ---------------------- ---------------------- 
1                      1                      2                      
2

So, in the above query, i want to run the following query only once for each row in the view:

(SELECT PRIMARY_KEY FROM MYTABLE_PARENT WHERE PRIMARY_KEY = CS.PRIMARY_KEY)

Is there any way of optimizing the inner subqueries within the view?

Why do you need the `SELECT PRIMARY_KEY FROM MYTABLE_PARENT WHERE PRIMARY_KEY = CS.PRIMARY_KEY` query at all? Why not just use CS.PRIMARY_KEY? — , Dec 30 '11 at 14:34

score 3 · Accepted Answer · answered Dec 30 '11 at 14:43

For a single access each of mytable and mytable_parent, try:

CREATE OR REPLACE FORCE VIEW MYVIEW AS 
  SELECT CS.PRIMARY_KEY AS PARENT_PK,
         MAX(DECODE(MT.MYVAL, 'MYVAL1-1',MT.PRIMARY_KEY, TO_NUMBER(NULL))
                        AS OUTPUT_VAL_1,
         MAX(DECODE(MT.MYVAL, 'MYVAL1-2',MT.PRIMARY_KEY, TO_NUMBER(NULL))
                        AS OUTPUT_VAL_2
  FROM MYTABLE_PARENT CS
  LEFT JOIN MYTABLE MT ON MT.PARENT_PRIMARY_KEY = CS.PRIMARY_KEY AND
                          MT.MYVAL IN ('MYVAL1-1', 'MYVAL1-2')
  GROUP BY CS.PRIMARY_KEY

Aaron · Answer 2 · 2011-12-30T14:51:44.560

2

I'm not able to test this right now, but try this for the SELECT portion of your view. I'm thinking it'll at least cut-out your nested subqueries.

SELECT CS.PRIMARY_KEY AS PARENT_PK, mv1.PRIMARY_KEY AS OUTPUT_VAL_1, mv2.PRIMARY_KEY AS OUTPUT_VAL_2
FROM MYTABLE_PARENT CS
LEFT JOIN MYTABLE mv1 ON mv1.PARENT_PRIMARY_KEY = CS.PRIMARY_KEY
LEFT JOIN MYTABLE mv2 ON mv2.PARENT_PRIMARY_KEY = CS.PRIMARY_KEY
WHERE mv1.MYVAL = 'MYVAL1-1'
AND mv2.MYVAL = 'MYVAL1-2';

edited Dec 30 '11 at 14:51

answered Dec 30 '11 at 14:33

Aaron

55,518
11
116
132

Inner joins assume there will always be a matching mytable record - not necessarily true of original view. – Dec 30 '11 at 14:44

score 0 · Answer 3 · answered Dec 30 '11 at 14:29

The subquery reads:

(SELECT PRIMARY_KEY FROM MYTABLE_PARENT WHERE PRIMARY_KEY = CS.PRIMARY_KEY)

But the alias CS also points at MYTABLE_PARENT:

MYTABLE_PARENT CS;

So the entire subquery is superfluous, and you could replace it with CS.PRIMARY_KEY ?

The entire query could then be rewritten like:

select    cs.primary_key as parent_pk
,         mt1.primary_key
,         mt2.primary_key
from      mytable_parent cs
join      mytable mt1
on        mt.parent_primary_key = cs.primary_key
          and mt1.myval = 'myval1-1'
join      mytable mt2
on        mt2.parent_primary_key = cs.primary_key
          and mt2.myval = 'myval1-2'

Inner joins assume there will always be a matching mytable record - not necessarily true of original view. — , Dec 30 '11 at 14:44

Oracle SQL Reuse sub query inside VIEW

3 Answers3