Tipp Index

Oracle Tipp Text

SQL Tuning mit SAMPLE und ROWNUM

Bereich:SQL, Version: ab RDBMS 12.x:RDBMS 19.3:RDBMS 21.1, Letzte Überarbeitung: 04.07.2018

Keywords:SQL, Tuning, ROWNUM, SAMPLE

Mit der SAMPLE-Klausel kann die SQL-Ergebnismenge von 0,000001% bis 100% der ursprünglichen reduziert werden. Oracle greift einfach zufällig in die Ergebnismenge und holt (wählt) einen gewissen Prozentsatz der Daten heraus.In der Schule nannte man das Experiment: Ziehen mit zurücklegen :-)

Schauen wir uns ein paar Beispiele an:

SELECT count(*) FROM big
SAMPLE (0.001);

	erster Versuch	zweiter Versuch	dritter Versuch
Rückgabe	11	10	6

Man sieht also, die Rückgabemenge kann durch statistische Abweichungen mal mehr, mal weniger Zeilen zurückliefern.

Mit dem SEED-Parameter kann die Ergebnismenge auf einer Speicherplatznummer gespeichert werden (hier: Position 1)

SELECT name FROM big SAMPLE (0.0001) SEED (1);

Ergebnis:

ORD_DICOM
WWV_FLOW_PREFERENCES
DBMS_SQLTUNE

SELECT name FROM big SAMPLE (0.0001) SEED (2);

Ergebnis:

DBMS_STATS

Wenn Sie die erste Ergebnismenge nochmal benötigen, setzen Sie einfach das Statement mit SEED(1) nochmal ab:

SELECT name FROM big
SAMPLE (0.0001) SEED (1);

Ergebnis:

ORD_DICOM
WWV_FLOW_PREFERENCES
DBMS_SQLTUNE

Wir konnten bei unseren Tests bis Speicheradresse SEED(4294967295) gehen. Nach dieser Speicheradresse wurden die Ergebnismengen nicht mehr geändert. Also egal welche SEED-Adresse darüber hinaus Sie verwenden, Sie bekommen immer die Daten der Nummer SEED(4294967295) zu sehen.

Sie können in das Statement auch eine WHERE-Klausel einbauen:

SELECT name FROM big SAMPLE(0.01) WHERE name LIKE 'A%';

Auch ein Alias Name für die Tabelle ist möglich (wenn auch an eigenartiger Stelle):

SELECT name FROM big SAMPLE(0.01) b WHERE name LIKE 'A%';

Auch bei Joins steht die Sample-Klausel direkt hinter dem Tabellennamen:

SELECT * FROM emp SAMPLE(10), dept
WHERE emp.deptno=dept.deptno;

Hier endet auch leider der Nutzen der SAMPLE-Klausel, denn sobald Sie komplexere Statements haben, funktioniert sie nicht mehr!

Gut, dann verwenden wir wieder die altbewährte ROWNUM-Klausel. Wenn Sie verschiedene Varianten eines SQL-Statements austesten möchten und dabei feststellen, jeder Versuch dauert mehr als 3 Stunden, werden Sie diese Möglichkeit zu schätzen wissen.

Wenn das Statement also lautet:

SELECT empno, ename, sal, loc FROM emp, dept
WHERE emp.deptno=dept.deptno;

Machen wir daraus:

SELECT * FROM ( SELECT empno, ename, sal, loc
FROM emp, dept WHERE emp.deptno=dept.deptno )
WHERE rownum<10;

Und Statements mit WITH-Klausel?

WITH t as (select deptno, loc FROM dept)
SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
WHERE e.deptno=t.deptno;

Ab Oracle 12.2:

SELECT * FROM (
                WITH t as ( SELECT deptno,loc FROM dept )
                SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
                 WHERE t.deptno=e.deptno )
WHERE rownum<10;

Oder alternativ:

WITH t as ( SELECT deptno, loc FROM dept )
SELECT * FROM (   -- ### Hier wurde die Zusatzklausel eingefügt
                SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
                 WHERE t.deptno=e.deptno )
WHERE rownum<10; -- ### Hier endet die Zusatzklausel

Wenn die Ergebnismenge sehr groß ist, kann man auch nur einen COUNT(*) verwenden.

Dieser muss auch die ganze Ergebnismenge durchgehen, sich aber nicht mit der (lästigen und langwierigen) Zeilenausgabe beschäftigen:

SELECT count(*) FROM (
                       WITH t as (SELECT deptno, loc FROM dept)
                       SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
                        WHERE t.deptno=e.deptno )
WHERE rownum<10;

Weitere Interessente Artikel zum Thema:

Empfohlene Schulungen zum Thema:

1 - 1

Search Results

SQL Tuning mit SAMPLE und ROWNUM

Bereich:SQL, Version: ab RDBMS 12.x:RDBMS 19.3:RDBMS 21.1, Letzte Überarbeitung: 04.07.2018

Keywords:SQL, Tuning, ROWNUM, SAMPLE