这是使用相同子查询的三个不同选择.如何使用子查询结果而不是再次执行子查询.
SELECT *
FROM Address
WHERE address_key IN
(
SELECT address_key
FROM person_address
WHERE peson_key IN (person_list)
); -- person_list := '1,2,3,4'
SELECT *
FROM Phone
WHERE phone_key IN
(
SELECT address_key
FROM person_address
WHERE peson_key IN (person_list)
);
SELECT *
FROM Email
WHERE address_key IN
(
SELECT address_key
FROM person_address
WHERE peson_key IN (person_list)
);
解决方法
您可以为此查询创建物化视图：
CREATE MATERIALIZED VIEW v_address REFRESH FORCE ON COMMIT AS SELECT address_key FROM person_address WHERE person_key IN (person_list)
,或创建一个临时表并填充它：
CREATE GLOBAL TEMPORARY TABLE tt_address (VARCHAR2(50)); INSERT INTO tt_address SELECT address_key FROM person_address WHERE person_key IN (person_list)
但是,实际上,如果您为person_key编制索引,则可以重用子查询.
由于您有3个单独的查询,因此您需要以这样或那样的方式显示您的值.
这意味着您需要将这些值存储在某处,无论是内存,临时表空间还是永久表空间.
但是你需要的值已经存储在person_address中了,你只需要获取它们.
使用子查询3次将涉及12次索引扫描以从person_key上的索引获取ROWID,并使用12次表ROWID查找从表中获取address_key.然后很可能会在它们上构建一个HASH TABLE.
这是微秒的问题.
