Logo Oracle Deutschland   DBA Community  -   Juli 2014
Was verbirgt sich hinter einer View? - Nutzung von EXPAND_SQL_TEXT in 12c
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Wem ist das nicht auch schon mal passiert? Man möchte eine relativ einfache Abfrage tunen und hat es plötzlich mit einem sehr langen Ausführungsplan zu tun. Bei genauer Analyse stellt man fest, dass mehrere Views in der Abfrage genutzt werden; schliesslich gehört es auch zu einer der Best Practices, umfangreiche Abfragen über Views zu verbergen, um die Komplexität der Abfragen zu verringern.

Ein anderer Fall ist die Nutzung von Virtual Private Database (VPD) und das Tunen der zugrunde liegenden "Policy". Noch einmal zur Erinnerung: VPD implementiert die Kontrolle für den Zugriff auf einzelne Zeilen auf der Ebene der Tabelle. Mit einem speziellen Package werden alle Befehle wie INSERT, UPDATE, DELETE und SELECT, die auf eine mit VPD geschützte Tabelle zugreifen, um eine zusätzliche WHERE-Bedingung erweitert. Jeder sieht nur die für ihn durch die WHERE-Bedingung gefilterten Datensätze. Möchte man solch eine Abfrage tunen, muss man herausfinden, wie die Filterbedingung aussieht.

Wie erhält man nun die View Abfrage? Eine Möglichkeit, diese Fragestellungen zu beantworten, besteht darin, das Data Dictionary zu befragen. View Definitionen finden sich in DBA_VIEWS, Policy Definitionen in DBA_POLCIES. Wie zu Beginn schon erwähnt, werden auch im Ausführungsplan die verwendeten Tabellen bzw. Filterkriterien angezeigt. Ab Oracle Database 12c gibt es nun eine dritte Möglichkeit, diese Problematik anzugehen. Die neue Prozedur EXPAND_SQL_TEXT des Package DBMS_UTILITY nimmt eine Abfrage als Eingabe Parameter und ersetzt die View Referenzen durch die zugehörigen Abfragen auf die Tabellen.

Am besten illustrieren wir die neue Funktion an zwei Beispielen. Im ersten Beispiel legen wir die View MY_VIEW an. 

SQL>CREATE VIEW my_view AS
    SELECT prod_id, cust_id, time_id, channel_id, promo_id, 
           quantity_sold, amount_sold
    FROM sales WHERE amount_sold<1300
View created.
Nun verwenden wir diese View in der folgenden Abfrage. 
SELECT prod_name, sum(amount_sold) 
FROM sh.my_view INNER JOIN sh.products USING (prod_id) 
GROUP BY prod_name
Wie sieht nun das Ergebnis nach Nutzung der Prozedur EXPAND_SQL_TEXT aus? 
-- Anwendung von EXPAND_SQL_TEXT auf die Abfrage in input_1
SQL>
set serveroutput on
declare
   input_1 clob;
   output_1 clob;
begin
 
input_1:='SELECT prod_name, sum(amount_sold) FROM sh.my_view INNER JOIN products using(prod_id) 
          GROUP BY prod_name';

DBMS_UTILITY.EXPAND_SQL_TEXT (input_1,output_1);

dbms_output.put_line('Die Abfrage lautet:');
dbms_output.put_line(output_1);
end;
/
Die Abfrage lautet:
SELECT "A1"."PROD_NAME_1" "PROD_NAME",SUM("A1"."AMOUNT_SOLD_0")
"SUM(AMOUNT_SOLD)" FROM  (SELECT "A3"."AMOUNT_SOLD"
"AMOUNT_SOLD_0","A2"."PROD_NAME" "PROD_NAME_1" FROM  (SELECT "A4"."PROD_ID"
"PROD_ID","A4"."AMOUNT_SOLD" "AMOUNT_SOLD" FROM SH."SALES" "A4" WHERE
"A4"."AMOUNT_SOLD"<1300) "A3","SH"."PRODUCTS" "A2" WHERE
"A3"."PROD_ID"="A2"."PROD_ID") "A1" GROUP BY "A1"."PROD_NAME_1"

PL/SQL procedure successfully completed.
Wie man erkennen kann, ist die Verwendung ganz einfach. Das Ganze funktioniert auch für existierende (migrierte) Views oder auch Data Dictionary Views und natürlich mit komplexeren verschachtelten Abfragen.

Um die Funktionsweise in einem weiteren Beispiel zu veranschaulichen, nehmen wir eine Abfrage unter Verwendung von Virtual Private Database. Zur Veranschaulichung dient das Beispiel aus unserem Tipp Oracle Virtual Private Database . Die Idee dahinter war, dass Auftragsdaten aus der Tabelle OE.ORDERS nur von den Verkäufern, die den Auftrag abgeschlossen haben, lesbar sind. Ausserdem sollen Kunden ihre eigenen Auftragsdaten lesen dürfen. Die Policy enthält eine spezielle Zeichenkette, die als WHERE-Bedingung durch VPD genutzt wird. Folgendes Beispiel illustriert die Verwendung. 
-- connect als User colsen  

SQL> 
set serveroutput on
declare
   input_1 clob;
   output_1 clob;
begin

input_1:='select customer_id from oe.orders';

DBMS_UTILITY.EXPAND_SQL_TEXT (input_1,output_1);
dbms_output.put_line('Die Abfrage lautet:');
dbms_output.put_line(output_1);
end;
/
SQL>  
Die Abfrage lautet:
SELECT "A1"."CUSTOMER_ID" "CUSTOMER_ID" FROM  (SELECT "A2"."ORDER_ID"
"ORDER_ID","A2"."ORDER_DATE" "ORDER_DATE","A2"."ORDER_MODE"
"ORDER_MODE","A2"."CUSTOMER_ID" "CUSTOMER_ID","A2"."ORDER_STATUS"
"ORDER_STATUS","A2"."ORDER_TOTAL" "ORDER_TOTAL","A2"."SALES_REP_ID"
"SALES_REP_ID","A2"."PROMOTION_ID" "PROMOTION_ID" FROM "OE"."ORDERS" "A2" WHERE
"A2"."SALES_REP_ID"=153 OR "A2"."CUSTOMER_ID"=153) "A1"

PL/SQL procedure successfully completed.
-- Die Policy generiert Informationen zu den Spalten SALES_REP_ID und CUSTOMER_ID

-- nun connect als User SYSTEM ohne Einschränkung
-- das Ergebnis von DBMS_UTILITY.EXPAND_SQL_TEXT sieht dann folgendermassen aus... 
Die Abfrage lautet:
SELECT "A1"."CUSTOMER_ID" "CUSTOMER_ID" FROM  (SELECT "A2"."ORDER_ID"
"ORDER_ID","A2"."ORDER_DATE" "ORDER_DATE","A2"."ORDER_MODE"
"ORDER_MODE","A2"."CUSTOMER_ID" "CUSTOMER_ID","A2"."ORDER_STATUS"
"ORDER_STATUS","A2"."ORDER_TOTAL" "ORDER_TOTAL","A2"."SALES_REP_ID"
"SALES_REP_ID","A2"."PROMOTION_ID" "PROMOTION_ID" FROM "OE"."ORDERS" "A2" WHERE
1=1) "A1"

PL/SQL procedure successfully completed.
Weitere Hinweise dazu finden sich im Handbuch Oracle Database PL/SQL Packages and Types Reference 12c Release 1 (12.1).

Zurück zur Community-Seite