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.