Query Optimierung mit ORDER BY?

Franck Pachot liefert in seinem Blog ein interessantes Beispiel, in dem eine simple Query ohne jede Einschränkung, die eine kleine Tabelle via Full Table Scan einliest, eine deutliche Reduzierung der Consistent Gets und auch der Laufzeit erfährt, wenn man ein ORDER BY ergänzt:

• select lpad(x,2000,x) from DEMO;
  
• 683 consistent gets
• 20163693 bytes sent via SQL*Net to client
• 668 SQL*Net roundtrips to/from client
• select lpad(x,2000,x) from DEMO order by x;
• 35 consistent gets
• 118324 bytes sent via SQL*Net to client
• 668 SQL*Net roundtrips to/from client

Das klingt erst mal überraschend, hat aber zwei einleuchtende Ursachen:

• die sqlnet compression, die Jonathan Lewis gelegentlich erläutert, hat, was ich an anderer Stelle nacherzählt habe, und die sich dergestalt auswirkt, dass wiederholte Elemente dedupliziert werden können - und das funktioniert mit den sortierten Werten im Beispiel deutlich besser, weil die Spalte x in der Demo-Tabelle nur die Werte 0 und 1 in abwechselnder Folge enthält.
• die Wirkung der ARRAYSIZE, die auf den sqlplus-Standard 15 gesetzt ist (was an 10000/668 abzusehen ist). Jeder Roundtrip führt dabei zu einem neuerlichen Blockzugriff (also 35 + 668 * 1 = 683), wobei der Block natürlich permanent im Speicher gehalten wird. Im Fall der Query mit Sortierung werden die Daten initial in die PGA gelesen und dort sortiert. Der Zugriff auf das Ergebnis erfordert dann keine lesekonsistenten Zugriffe mehr.

Obwohl mir der Zusammenhang grundsätzlich klar war, muss ich gestehen, dass ich zunächst eine Weile nachdenken (und ausprobieren musste), ehe ich die Details an die richtige Stelle einordnen konnte.