How to avoid the same subquery with multiple selection in oracle?

Question

These are three different options using the same subquery. How can I use the result of a subquery instead of retrying a sub request.

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

Answer 1

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 specified your person_key , then it will be okay to reuse the subquery.

Since you have 3 separate queries, you need your values ​​to be visible to them anyway.

This means that you need to store these values ​​somewhere, be it memory, a temporary table space, or a permanent table space.

But the values ​​you need are already stored in person_address , all you need is their choice.

Using a 3 times subquery will include 12 index scans to extract the ROWID from the index in person_key tags and 12 table ROWID to retrieve the address_key from the table. Then, most likely, a HASH TABLE will be built over them.

This is a matter of microseconds.

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

Answer 2

Use the with clause. I did not recreate your exact problem, but any number of repeated subqueries can be placed in the WITH clause and then indicated in the request.

 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 

Answer 3

At first, I think that in most cases this optimization does not bring significant improvements (after the first request, the PERSON_ADDRESS data blocks will mainly be cached in the buffer cache and, therefore, will not be read from the HDD).

However, as an example, or for any reason: You need to cache duplicate query results, and then reuse them in three ways. This can be achieved using table (temp) or MV or plsql structure.

The first two options include Kvasnoy in his answer, so I will not mention them. The third has the disadvantage that you need to specify the maximum number of lines in advance (and I don’t know, I know what happens when you declare a varray with an upper bound of 1M or 1G elements, even if you only need 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; /