oracle中的reuse,VIEW中的Oracle SQL Reuse子查询

我正在尝试设置一个可能包含1000个记录的视图.某些字段需要子查询,这些子查询返回完全相同的数据.我想知道我是否可以在视图中查询一次,而不是每次都运行它.

下面是一些示例表/数据：

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;

select语句的结果如下：

PARENT_PK OUTPUT_VAL_1 OUTPUT_VAL_2

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

1 1 2

2

因此,在上面的查询中,我想对视图中的每一行只运行一次以下查询：

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

有没有办法在视图中优化内部子查询？