MS SQL Kompression

nur als kurze Notiz: ähnlich wie in Oracle kann man auch im SQL Server das physikalische Speichervolumen einer Tabelle reduzieren: zum einen durch Verwendung der kleinsten geeigneten Datentypen (row compression) und darüber hinaus durch Ersetzung wiederkehrender Inhalte durch Platzhalter und die Verwendung einer Hash Map zur Auflösung dieser Ersetzungen (page compression). Die Komprimierung kann über die SSMS-GUI erfolgen oder auch über folgendes Kommando:

USE [Name_der_Datenbank]
ALTER TABLE [dbo].[Name_der_Tabelle] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) 

SQL*Net Compression

In Jonathan Lewis' Blog findet sich ein Beitrag zum Thema der SQL*Net Compression, die eine Reduzierung der Daten, die vom Server zum Client transportiert werden, ermöglicht. Grundsätzlich funktioniert das offenbar so, dass für jeden Satz entschieden wird, ob die Daten einer Spalte identisch mit den Inhalten der entsprechenden Spalte im letzten transferierten Satz sind - und auf diese Weise spielt die Sortierung der Daten eine wichtige Rolle für die Effizienz dieser Komprimierung (die eigentlich eine "de-duplication" ist). Interessanterweise kann ich die Ergebnisse nachvollziehen, wenn ich mit einer String-Spalte teste, wie sie Jonathan Lewis verwendet, also:

create table test
as
select rownum rn
     , lpad(dbms_random.string('U',2),40,'X')  v1
  from dual
connect by level < 10000;

-- Ergebnis:
-- 464825  bytes sent via SQL*Net to client - unsortiert
-- 118679  bytes sent via SQL*Net to client - sortiert nach v1

Wenn ich aber eine harmlose nummerische Spalte mit Wiederholungen verwende, sehe ich zwar auch Unterschiede zwischen der sortierten und der unsortierten Ausführung, aber sie gehen seltsamerweise in die andere Richtung:

create table test
as
select rownum rn
     , mod(rownum, 3)  v1
  from dual
connect by level < 10000;


-- Ergebnis:
-- 81653  bytes sent via SQL*Net to client - 
unsortiert
-- 81653  bytes sent via SQL*Net to client - sortiert nach rn


-- 90971  bytes sent via SQL*Net to client - sortiert nach v1

Mag sein, dass sich NUMBER-Werte bezüglich der Komprimierung tatsächlich anders verhalten. Oder mir entgeht noch irgendetwas Entscheidendes.

Nachtrag: nachdem mir auf Anhieb keine plausible Erklärung eingefallen war, habe ich Jonathan Lewis im Blog danach gefragt und folgende Antwort bekommen:

I think this may be a result of a fixed overhead for compression (or de-duplication, which might be a slightly better term). I think a row probably has to say “this column is the same as last time”, so I would look for a fixed size token to represent “same as last row”.
Test: create a table of 1,000,000 rows with a constant (e.g. 1000000001) and a variable (e.g. 1000000001 + rownum) column, set a large arraysize (5,000 is the max) then select each column in turn with autotrace traceonly statistics. See if the difference in volume is significant. It is, but there is a minimum volume of data per row which means you don’t see much of a saving with a single column of small numbers.

Meiner Erfahrung nach bekommt man auf thematisch passende Fragen in den Blogs meiner Sidebar immer gute Antworten (so war's jedenfalls auch bei Richard Foote und Charles Hooper).

Unschärfen mit dem Datentyp NUMBER

Dass ich es mit numerischen Datentypen nicht besonders genau nehme, gehört zu den schlechten Angewohnheiten, die ich bei der Arbeit mit Oracle im Lauf der Zeit angenommen habe. Da für Oracle ohnehin alles NUMBER ist, neige ich dazu, die Definitionen von Precision und Scale nur als Constraints zu betrachten, mit denen der Bereich erlaubter Werte eingegrenzt werden kann. Das ist aber offenbar nur ein Teil der Geschichte, wie der folgende Test zeigt:

Im praktischen Fall wollte ich mir aus einer Kennzahl eine Stammdateninformation extrahieren, die darin enthalten war. Die folgende Query zeigt die Struktur der Daten: neben einer Artikelnummer liegt eine Mengenangabe (QUANTITY) vor und eine zusätzliche Angabe, die ursprünglich als Menge * Packungsgröße (QUANTITY_BASEQUANTITY * QUANTITY) gebildet worden war:

select ITEMID
     , QUANTITY_BASEQUANTITY
     , QUANTITY
  from fact_sales
 where ITEMID = 334150
   and rownum < 10;

ITEMID QUANTITY_BASEQUANTITY   QUANTITY
------- --------------------- ----------
 334150                   4,8          1
 334150                   4,8          1
 334150                   9,6          2
 334150                   9,6          2
 334150                   9,6          2
 334150                   4,8          1
 334150                   9,6          2
 334150                   4,8          1
 334150                   4,8          1

Meine Vermutung war, dass die Packungsgrößen durch eine Division QUANTITY_BASEQUANTITY/QUANTITY eindeutig pro Artikel zu ermitteln wäre:

create table item_salesquantity
as
select distinct ITEMID
     , QUANTITY_BASEQUANTITY/QUANTITY Salesquantity
  from fact_sales;

select distinct salesquantity
  from item_salesquantity 
 where ITEMID = 334150;

SALESQUANTITY
-------------
          4,8
          4,8
          4,8
          4,8
          4,8
          4,8
          4,8
          4,8

8 Zeilen ausgewählt.

Das kam etwas überraschend: auf den ersten Blick sehen alle Werte identisch aus, aber da DISTINCT sie unterscheidet, sind sie's offenbar nicht. Mit VSIZE lässt sich die physikalische Größe der Werte bestimmen:

select distinct salesquantity, vsize(SALESQUANTITY)
  from item_salesquantity 
 where ITEMID = 334150
 order by vsize(SALESQUANTITY);

SALESQUANTITY VSIZE(SALESQUANTITY)
------------- --------------------
          4,8                    3
          4,8                   10
          4,8                   10
          4,8                   11
          4,8                   11
          4,8                   21
          4,8                   21
          4,8                   21

8 Zeilen ausgewählt.

Offenbar gibt es also tatsächlich Unterschiede und diese beruhen anscheinend darauf, dass schon die zugrunde liegenden Werte bezüglich der VSIZE unterschiedlich sind:

create table item_salesquantity_noagg
as
select ITEMID
     , QUANTITY_BASEQUANTITY
     , QUANTITY
     , QUANTITY_BASEQUANTITY/QUANTITY Salesquantity
  from fact_sales;
  
select distinct QUANTITY_BASEQUANTITY
     , vsize(QUANTITY_BASEQUANTITY)
     , salesquantity
     , vsize(SALESQUANTITY)
  from item_salesquantity_noagg
 where ITEMID = 334150
 order by QUANTITY_BASEQUANTITY;
 
QUANTITY_BASEQUANTITY VSIZE(QUANTITY_BASEQUANTITY) SALESQUANTITY VSIZE(SALESQUANTITY)
--------------------- ---------------------------- ------------- --------------------
                  4,8                           10           4,8                   10
                  9,6                           10           4,8                   10
                 14,4                           10           4,8                   21
                 19,2                           10           4,8                   11
                   24                            2           4,8                    3
                 28,8                           10           4,8                   10
                 33,6                           10           4,8                   21
                 38,4                           10           4,8                   11
                 43,2                           10           4,8                   21
                   48                            2           4,8                    3

10 Zeilen ausgewählt.

Die Moral von der Geschichte ist wohl, dass ich NUMBER-Werte weniger indifferent behandeln sollte. Als kurzfristiger Fix diente eine Rundung, die das gewünschte Resultat brachte:

create table item_salesquantity_round
as
select distinct ITEMID
     , round(QUANTITY_BASEQUANTITY/QUANTITY, 4) Salesquantity
  from fact_sales; 

select distinct salesquantity, vsize(SALESQUANTITY)
  from item_salesquantity_round

where ITEMID = 334150
 order by vsize(SALESQUANTITY);

SALESQUANTITY VSIZE(SALESQUANTITY)
------------- --------------------
          4,8                    3  

Viewer für Trace Event 10053

In Jonathan Lewis' Blog findet sich ein Link auf einen Viewer, den die Herren Hans-Peter Sloot und Robert van der Ende geschrieben haben, und mit dessen Hilfe sich die cbo Informationen des 10053er Events übersichtlicher darstellen lassen.

Beim Testen bin ich auch noch auf eine instruktive Liste von Trace Events auf der PSOUG-Seite gestossen.

Und noch eine Notiz: Voraussetzung für die Erzeugung eines cbo-Traces ist ein hard parse für das fragliche Statement (was durchaus einleuchtet).

sqlplus spool

Als Notizzettel ein paar Einstellungen zur Erzeugung von csv-Dateien aus sqlplus über spool:

-- kein Seitenwechsel
set pages 0
-- keine Überschriften
set heading off
-- keine Bildschirmausgabe bei Aufruf aus Script
set termout off
-- keine trailing blanks im Ausgabefile
set trimspool on
-- fetch size von 15 auf 100 erhöht (Verringerung von roundtrips)
set arraysize 100

spool 

select column_1 || ';' || column_2 || ';' || column_3
  from my_table;
  
spool off  

Block Dump Interpretation

Ein paar gute Ideen dazu, wie man die Spaltenwerte in einem Index Block Dump in lesbare Inhalte verwandelt, findet man in Dion Chos Blog. Der Hinweis auf diesen Beitrag fand sich übrigens mal wieder bei Richard Foote.

Letztlich läuft es dabei darauf hinaus, dass man abhängig vom Datentyp der jeweiligen Spalte entweder utl_raw.cast_to_number oder utl_raw.cast_to_varchar2 aufruft.

USER_TAB_COLS

Eine Randnotiz: beim Versuch, herauszubekommen, ob eine Tabellenspalte als virtual definiert ist, habe ich hoffnungsvoll in USER_TAB_COLUMNS nachgeschaut und dort zu meiner Überraschung nichts gefunden. Bei Oracle im Netz habe ich dann den Hinweis entdeckt, dass die Information nur in USER_TAB_COLS vorliegt, einer View, die mit USER_TAB_COLUMNS nahezu identisch ist, aber ein paar zusätzliche Spalten enthält (und zukünftig meine Quelle für Spalteninformationen werden sollte).

Änderung des cbo-Verhaltens für frequency histograms in 10.2.0.4

In Randolf Geists Blog findet sich ein interessanter Hinweis auf eine Verhaltensänderung des cbo beim Einsatz von frequency histograms, die im Rahmen des Patches 10.2.0.4 eingeführt wurde und recht unschöne Effekte für Spalten mit sehr ungleichmäßiger Verteilung und einem extrem häufigen Wert haben kann.