✔ ORACLE 9.2 / Problem SUB-Select bzw. PL/SQL Fkt.

[OlafKoeln]

Hallo,

folgendes Problem:

in einer where-Bedingung steht folgender Teil:

.... and loc_id in (select xy from table where ....) and ...

Liefert der SUB-Select select kein Ergebnis, soll aber als Ergebnis ein fester definierter Wert zurückgegeben werden.

Leider funktioniert

.... and loc_id in nvl((select xy from table where ....), x) and ...

nicht, da NVL keine Liste von zurückgegebenen Werten verarbeiten kann.

Ich habe auch probiert, eine Funktion zu schreiben in der Form:

.... and loc_id in get_function(...) and ...

Die Function liefert Werte als CHAR in der Form "(123, 556,345)" zurück.

Aber ORCALE interpretiert es als CHAR-String, nicht als Teil des Selectes selbst.

Der wert loc_id ist number.

.... and to_char(loc_id) in get_function(...) and ...

funktioniert auch nicht - führt zu keinem Ergebnis, nicht einmal eine Fehlermeldung.

Wer hat da noch eine Idee?

Danke !

Olaf

 

[Exceptionfault]

Also, da ich ein Freund von Prozeduren in der Datenbank bin ;-) hier meine kurze Lösung in PL/SQL.

-- zuerst einen Typ anlegen, den wir als Liste nutzen können
CREATE OR REPLACE TYPE head_lst IS TABLE OF NUMBER
/

-- nun die Prozedur, die uns die Liste zurückliefert
REATE OR REPLACE FUNCTION GET_LIST RETURN head_lst
IS
 lst  head_lst;
BEGIN

    SELECT  HEADID
    BULK COLLECT INTO lst
    FROM    HEAD
    WHERE   HEADID = 18;

    IF SQL%ROWCOUNT = 0 THEN
        lst  := head_lst( 17 );
    END IF;
            
    RETURN lst;

END;
/

/* Ich selektiere alle Sätze aus der Tabelle HEAD bei denen die HEADID = 18 ist. 
Wenn ich keinen finde, liefere ich den Satz mit der ID 17 zurück. *7

SQL> select headid from head;

         HEADID
---------------
             17

1 Zeile wurde ausgewõhlt.

/* wie wir sehen ist nur ein Satz drin... */

SQL> select headid from head where headid in ( select * from table(get_list()) );

         HEADID
---------------
             17

1 Zeile wurde ausgewõhlt.

/* Da wir keinen Satz mit HEADID 18 finden, bekomme ich  den mit 17.
Der Trick ist, das Ergebnis der Prozedur als Tabelle zu casten und in einem 
Subselect des IN Statements zu nutzen. */

 

[OlafKoeln]

Super - herzlichen Dank !

Das sieht wirklich gut aus.

Olaf

 

[OlafKoeln]

... Augen auf im Straßenverkehr ... habe das mit dem casten übersehen ...

habe es jetzt so realisiert:

p.loc_id in (select * from table (cast (get_p_loc_id(xy) as id_lst)))

... der subselect funktioniert prima, ich bekomme eine tolle ID-Liste.

Aber im Verbund mit dem Hauptselect passiert nichts - d.h., es kommt kein Ergebnis, der
Select sucht sich tot. Trage ich die Ergbnisse per hand in eine Liste ein (p.loc_id in (x,y,z)
alles relativ schnell - das verstehe ich nun nicht mehr :-(

Olaf

 

[Exceptionfault]

Nun, das Problem wird sein, dass er für jeden Satz aus deinem Hauptstatement einmal die IN Liste und somit die Funktion auswerten will. Das ist Performance - technisch natürlich nicht so toll. Du hast nun 2 Möglichkeiten:

Entweder du versuchst das IN ( ) in einen INNER Join umzubauen. Sollte nicht so schwer sein, wenn du wieder deine Liste als Tabelle castest.
Die andere Idee läuft auf das selbe hinaus, nur dass du vorher deine Liste in eine Temporäre Tabelle selektierst und die in einen JOIN mit einbaust.

Der Vorteil von JOINS ist, dass der Optimizer da noch ein wenig Spielraum hat. Bei einer IN Liste ist er immer fix auf einen Ausführungsweg gezwungen. Daher sollte man eigentlich immer einen JOIN vorziehen. I.d.R. ist es auch meistens möglich das Statement in einen JOIN zu wandeln, nur sind die Entwickler zu faul weil die IN Liste schöner aussieht ;-)

 

[OlafKoeln]

Hallo Exceptionfault,

danke wieder für den Tip - hat zumindest etwas gebracht. Aber das ist ja jetzt alles bei uns datenbankinterne Performance Probleme. Das eigentliche Problem ist ja gelöst - dank Dir


Olaf