oracle 如何在oracle中的多选中避免相同的子查询？

How to avoid same subquery in multiple select in oracle?

提问by

This is three different selects using same subquery. How can I use subquery result instead of doing sub query again.

这是使用相同子查询的三个不同选择。如何使用子查询结果而不是再次执行子查询。

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)
        );

回答by Quassnoi

You can create a materialized view for this query:

您可以为此查询创建物化视图：

CREATE MATERIALIZED VIEW v_address
REFRESH FORCE ON COMMIT
AS
SELECT  address_key
FROM    person_address
WHERE   person_key IN (person_list)

, or create a temporary table and populate it:

，或创建一个临时表并填充它：

CREATE GLOBAL TEMPORARY TABLE tt_address (VARCHAR2(50));

INSERT
INTO   tt_address
SELECT  address_key
FROM    person_address
WHERE   person_key IN (person_list)

, but, really, if you index your person_key, it's OK to reuse the subquery.

，但是，实际上，如果您索引您的person_key，则可以重用子查询。

Since you have 3separate queries, you need your values to be visible to them one way or another.

由于您有3单独的查询，因此您需要以一种或另一种方式对它们可见。

That means you need to store these values somewhere, be it memory, temporary tablespace or a permanent tablespace.

这意味着您需要将这些值存储在某处，无论是内存、临时表空间还是永久表空间。

But the values you need are already stored in the person_address, all you need is to fetch them.

但是您需要的值已经存储在 中person_address，您需要的只是获取它们。

Using the subquery 3times will involve 12index scans to fetch the ROWID's from the index on person_keyand 12table ROWIDlookups to fetch address_keyfrom the table. Then most probably a HASH TABLEwill be built over them.

使用子查询3时间将涉及12索引扫描来获取ROWID从对指数的person_key并12表ROWID查找获取address_key从表中。那么很可能HASH TABLE会在它们之上建立一个意志。

This is a matter of microseconds.

这是几微秒的问题。

Of course, the temporary table or a materialized view would be a little more efficient, but changing the subquery time from 100microseconds to 50is hardly worth it, provided that the main queries can take minutes.

当然，临时表或物化视图效率会更高一些，但是将子查询时间从100微秒更改50为几乎不值得，前提是主查询可能需要几分钟。

回答by Brian

Use the with clause. I didn't re-create your exact example issue, but any number of repeated sub-queries can be put in the WITH clause and then referenced in the query.

使用 with 子句。我没有重新创建您的确切示例问题，但是可以将任意数量的重复子查询放入 WITH 子句中，然后在查询中引用。

WITH  address_keys as (
        SELECT  address_key
        FROM    person_address
        WHERE   peson_key IN (person_list)
        )
Select * from table1, table2, address_keys
where table1.address_key = address_keys.address_key
and table2.address_key = address_keys.address_key

回答by Michal Pravda

First I think that in most cases this optimization does not bring significant improvements(after the first query the data blocks of PERSON_ADDRESS would be mostly cached in the buffer cache and therefore not read from HDD).

首先，我认为在大多数情况下，这种优化不会带来显着的改进（在第一次查询之后，PERSON_ADDRESS 的数据块将大部分缓存在缓冲区缓存中，因此不会从 HDD 中读取）。

However as a case study or for whatever reason: You need to cache the repeating query results and later reuse them in 3 selects. This can be achieved by a (temp) table or MV or a plsql structure varray.

但是，作为案例研究或出于任何原因：您需要缓存重复的查询结果，然后在 3 次选择中重用它们。这可以通过（临时）表或 MV 或 plsql 结构变量来实现。

First two options covers Quassnoi in his answer so I won't mention them. The third one has disadvantage in having to state the maximum count of rows in advance (and I don't know right know what happens when you declare a varray with upper bound of 1M or 1G items even if you need only 1k).

前两个选项在他的回答中涵盖了 Quassnoi，所以我不会提及它们。第三个的缺点是必须提前说明最大行数（我不知道当你声明一个上限为 1M 或 1G 项的 varray 时会发生什么，即使你只需要 1k）。

--creating db object to hold the data - maximum of 1000 items allowed. 
--I assume that key is number(10,0).
create type t_address_keys is varray (1000) of number (10,0); 

declare 
  la_address_keys t_address_keys; --declare cache variable
begin

--cache keys
SELECT  address_key 
bulk collect into la_address_keys
        FROM    person_address
        WHERE   peson_key IN (person_list);

SELECT  *
into ...
FROM    Address
WHERE   address_key IN table(la_address_keys);

SELECT  *
into ...
FROM    Phone
WHERE   address_key IN table(la_address_keys);

SELECT  *
into ...
FROM    email
WHERE   address_key IN table(la_address_keys);

end;
/