natural join

Eine unerwartete Bedingung in der where Klausel

Es macht mich stolz zu erfahren, dass Carlos Sierra meinen Blog verfolgt. Carlos ist mir aus meiner Zeit bei Oracle schon lange bekannt, auch wenn wir uns erst kürzlich das erste Mal getroffen haben. Ich schätze Carlos als einen Mann der Tat. Wenn er einen Missstand sieht, beklagt er sich nicht, sondern tut etwas dagegen.
Mit meinem nächsten Beispiel will ich zeigen, dass er auch ein scharfsinniger Analytiker ist.
Vor Kurzem sah ich in einem Plan eine Bedingung, die so nicht im Sql statement stand. Ich wollte wissen, wie die Bedingung in den Plan gekommen war.
Ich gebe dazu ein einfaches Beispiel: Gegeben sei eine Tabelle x die wie folgt aussieht


SQL> desc x
Name Null? Typ
----------------------------------------- -------- -----------------
Y NUMBER
Z NOT NULL NUMBER

Ich lasse die folgende Abfrage laufen:


select count(*) from x where z=1;

Der Exection Plan sieht aus wie folgt:


Plan hash value: 989401810

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   420 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| X    |  1144K|    14M|   420   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NVL("Z",1)=1)

Wieso weisst der Plan diese merkwürdige Bedingung auf? Die Antwort finded Ihr in Carlos Sierra’s Blog. Noch ein Hinweis: Es hat etwas mit Default Werten zu tun.

 

Advertisements

Warum ist die neue Hardware langsamer?

Man kauft neue Hardware um schneller zu werden. Das ist eine ganz normale Erwartung. Was aber wenn die neue Hardware langsamer ist als die alte? Die Spekulationen über die Ursache gingen wild hin und her. Da ich auf diesen Einsatz urlaubsbedingt lange warten musste, war die Spannung gross als die Untersuchung endlich beginnen konnte.

Eine schneller Überprüfung zeigte, dass die neue Hardware nicht langsamer war als die alte. Den entscheidenden Hinweis lieferte ein raw trace. Ich zeige hier nur ein entscheidenden Ausschnitt.

Auf der alten Hardware sah der trace so aus:


FETCH #25:c=1154407,e=1152124,p=0,cr=102603,cu=0,mis=0,r=101,dep=0,og=1,tim=650755949521

Cursor #25 ist ein grosses Select, das langsam läuft. Auf der neuen Hardware hingegen sah der trace wie folgt aus:


FETCH #601010888:c=31200,e=22483,p=0,cr=3706,cu=50,mis=0,r=1,dep=0,og=1,plh=3621104505,tim=39783214696
WAIT #601010888: nam='SQL*Net message from client' ela= 171 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217398
LOBGETLEN: c=0,e=3,p=0,cr=0,cu=0,tim=39783217416
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217423
WAIT #0: nam='SQL*Net message from client' ela= 117 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217546
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217560
LOBREAD: c=0,e=12,p=0,cr=1,cu=0,tim=39783217567

Cursor #601010888: entspricht Cursor #25 auf der alten Hardware. Die Datenbank auf der neuen Hardware ist Version 11, die Datenbank auf der alten Hardware ist Version 10.
Offensichtlich besteht ausser bei der Version mindestens ein weiterer Unterschied zwischen beiden Datenbanken. Was ist es? Wie wirkt sich dieser Unterschied aus?
Beide Datenbanken werden über das exakt selbe Programm angesprochen, welche mit MS Visual Studio realisiert ist.

 

Lösung: Warum ist kein Alias nötig?

Nun, beantworten wir zunächst die Zusatzfrage. Es scheint, dass in sql die Typen nicht direkt genutzt werden können.
Wie Martin Berx im Kommentar zum Englischen Blog richtig bemerkt, muss man zuerst auf den Typ casten.
In diesem Fall:

TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'

Dies it ein nützlicher kleiner Trick, den man immer wieder einmal brauchen kann.
Jetzt kann man das Statement ausführen. Mein Vermutung war, dass der natural Join dafür sorgt, dass man keinen Alias genötigt.
In der Tat resultiert

SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders o USING (customer_id)
JOIN order_items oi ON (oi.order_id=o.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY order_id
)
GROUP BY order_id

in:

FEHLER in Zeile 10:
ORA-00918: Spalte nicht eindeutig definiert

Erst wenn man einen Prefix für Order id im group by definiert geht es:


SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders USING (customer_id)
JOIN order_items ON (order_items.order_id=orders.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY o.order_id
)
GROUP BY order_id

Meiner Meinung nach sollte es eigentlich immer so funktionieren wie im Fall des natural join. Da hat man schon definiert, dass die Felder gleich sind, soll sich der Optimizer doch eines aussuchen.

 

Warum ist kein Alias nötig?

Hier ist eine Frage von OTN. Ich bin gespannt, was Sie zu sagen haben. (Bitte nicht die Antwort auf OTN nachsehen.)


select order_id, sum(quantity*unit_price)
from order_items
having sum(quantity*unit_price) > ALL
(select sum(quantity*unit_price)
from customers
join orders using (customer_id)
join order_items using (order_id )
where STATE_PROVINCE = 'FL'
group by order_id)
group by order_id;

Die Frage lautet: Warum brauchen wir kleinen Alias für order_id in der „Group order_id“ Klausel der Unterabfrage, obwohl die Spalte in beiden Tabellen angezeigt wird?
So, um es ein bisschen schwieriger zu machen, habe ich eine kleine zusätzliche Hürde errichtet. STATE_PROVINCE ist in der OE-Schema für Version 12 (für andere Versionen habe ich nicht getestet) Bestandteil einer Typvariable. Die Abfrage läuft nicht wie hier gezeigt. Wie sollen wir die Abfrage so ändern, dass es funktionieren wird?