Wie vermeidet man dieselbe Unterabfrage bei Mehrfachauswahl in Oracle?

Question

Dies sind drei verschiedene Selects mit derselben Subquery. Wie kann ich subquery Ergebnis verwenden, anstatt zu tun sub Abfrage erneut.

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

Verwenden Sie die With-Klausel. Ich habe Ihr Beispielproblem nicht genau nachgestellt, aber eine beliebige Anzahl von wiederholten Unterabfragen kann in die WITH-Klausel eingefügt und dann in der Abfrage referenziert werden.

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 2

Sie können eine materialisierte Ansicht für diese Abfrage erstellen:

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

oder erstellen Sie eine temporäre Tabelle und füllen Sie diese auf:

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

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

aber wenn Sie wirklich Ihre person_key ist es in Ordnung, die Unterabfrage wiederzuverwenden.

Da Sie 3 separaten Abfragen, müssen Ihre Werte auf die eine oder andere Weise für sie sichtbar sein.

Das bedeutet, dass Sie diese Werte irgendwo speichern müssen, sei es im Speicher, in einem temporären Tablespace oder in einem permanenten Tablespace.

Aber die Werte, die Sie benötigen, sind bereits in der person_address müssen Sie sie nur noch abholen.

Verwendung der Unterabfrage 3 Zeiten werden beinhalten 12 Index-Scans zum Abrufen der ROWID aus dem Index auf person_key y 12 Tabelle ROWID Nachschlagewerke zum Abrufen address_key vom Tisch. Dann wird höchstwahrscheinlich ein HASH TABLE über ihnen gebaut werden.

Dies ist eine Frage von Mikrosekunden.

Natürlich wäre die temporäre Tabelle oder eine materialisierte Ansicht ein wenig effizienter, aber das Ändern der Unterabfragezeit von 100 Mikrosekunden bis 50 lohnt sich kaum, vorausgesetzt, die wichtigsten Abfragen können Minuten dauern.

Answer 3

Zunächst denke ich, dass diese Optimierung in den meisten Fällen keine signifikanten Verbesserungen bringt (nach der ersten Abfrage würden die Datenblöcke von PERSON_ADDRESS größtenteils im Puffercache zwischengespeichert und daher nicht von der Festplatte gelesen).

Allerdings als Fallstudie oder aus welchen Gründen auch immer: Sie müssen die sich wiederholenden Abfrageergebnisse zwischenspeichern und sie später in 3 Selects wiederverwenden. Dies kann durch eine (temporäre) Tabelle oder MV oder eine plsql-Struktur varray erreicht werden.

Die ersten beiden Optionen deckt Quassnoi in seiner Antwort ab, daher werde ich sie nicht erwähnen. Die dritte Option hat den Nachteil, dass man die maximale Anzahl der Zeilen im Voraus angeben muss (und ich weiß nicht genau, was passiert, wenn man ein Varray mit einer Obergrenze von 1M oder 1G Elementen deklariert, auch wenn man nur 1k benötigt).

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