Mittwoch, August 25, 2010

Scalar Subqueries

Eine sehr interessante Darstellung wichtiger Vorteile scalarer Subqueries findet man hier. Allerdings wird nicht ausgeführt, wann ein outer join die geeignetere Lösung für eine entsprechende Fragestellung ist, was auch ganz interessant wäre. Bei einer sehr großen Zahl von Lookups, die nicht gecacht werden können, würde ich die scalaren Subqueries inzwischen eher vermeiden (also wenn die Satzanzahl im Ergebnis, zu der die Lookups durchgeführt werden sollen, sehr groß ist); wenn mir noch eine plausiblere Bestimmung einfällt, liefere ich sie nach.

Donnerstag, August 19, 2010

Datentyp Long

Adrian Billington hat in seinem Blog vermutlich alles Wissenswerte über den Datentyp Long aufgeschrieben und vor allem die Möglichkeiten erläutert, mit denen man Long-Werte auslesen kann.

Freitag, August 13, 2010

Interval Partitions

Partitionen sind eines der wichtigsten Hilfsmittel, um große Datenmengen sinnvoll verwalten und performant abfragen zu können. In Oracle 11 gibt es nun (endlich) die Möglichkeit, Partitionen bei Bedarf automatisch vom System anlegen zu lassen. Dazu ein kleines Beispiel: Ich lege eine partitionierte Tabelle mit genau einer Spalte, einer Partition und einer Intervall-Angabe an:

SQL> r
  1  create table test_interval_partition
  2  (col1 number)
  3  partition by range (col1)
  4  interval (1000)
  5  (partition test_p1 values less than (1000)
  6* )

Tabelle wurde erstellt.

"Interval" gibt dabei an, welchen Bereich ein automatisch angelegtes Intervall umfassen soll. Bei der Füllung der Tabelle werden nun nach Bedarf neue Intervalle angelegt. Zunächst ein Wert, der in die initial angelegte Partition fällt:

SQL> insert into TEST_INTERVAL_PARTITION values (999);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1

Jetzt folgt ein Wert, der außerhalb der ersten Partition liegt:

SQL> insert into TEST_INTERVAL_PARTITION values (1000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2

Um den Wert unterbringen zu können, legt das System eine neue Partition SYS_P25 an, die erwartungsgemäß an Position 2 erscheint. Nun ein deutlich höherer Wert:

SQL> insert into TEST_INTERVAL_PARTITION values (10000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2
TEST_INTERVAL_PARTITION        SYS_P26         11000                       3

Das System legt eine weitere Partition SYS_P26 an Position 3 an. Jetzt ein Wert für den Raum zwischen den beiden automatisch erzeugten Partitionen:

SQL> insert into TEST_INTERVAL_PARTITION values (5000);

1 Zeile wurde erstellt.

SQL> select table_name
  2       , partition_name
  3       , HIGH_VALUE
  4       , PARTITION_POSITION
  5    from user_tab_partitions t
  6   where table_name = 'TEST_INTERVAL_PARTITION';

TABLE_NAME                     PARTITION_NAME  HIGH_VALUE PARTITION_POSITION
------------------------------ --------------- ---------- ------------------
TEST_INTERVAL_PARTITION        TEST_P1         1000                        1
TEST_INTERVAL_PARTITION        SYS_P25         2000                        2
TEST_INTERVAL_PARTITION        SYS_P27         6000                        3
TEST_INTERVAL_PARTITION        SYS_P26         11000                       4

Die neue Partition SYS_P27 rückt an Position 3 und SYS_P26 auf Position 4.

Funktioniert also alles ziemlich genau so, wie man es erwarten würde. Interval Partitioning funktioniert übrigens nur für NUMBER und DATE Partitionsschlüssel, aber andere würde ich ohnehin nicht definieren wollen.

In Harald van Breederodes Blog finden sich übrigens noch ein paar interessante Hinweise zum Löschen von interval partitions:
Und im Rittman Blog schreibt Peter Scott über den Neuaufbau von lokalen Indizes für interval partitions:

    Mittwoch, August 11, 2010

    Fragmentation

    Jonathan Lewis hat in den letzten Wochen wieder allerlei interessante Blog-Einträge veröffentlicht, darunter auch eine weit gefasste Begriffserklärung für den Terminus Fragmentation. In dem Teil, der den Begriff im Kontext von Indizes erläutert, findet sich eine sehr erinnerungswürdige Kurzzusammenfassung zum Thema der Wiederverwendung von Speicherplatz in Tabellen und Indizes nach Satzlöschungen:
    we have to remember that there is a difference between index deletions and table deletions that makes the subsequent behaviour different. When a transaction deletes a row from a table it can reduce the row to a stub of just a few bytes before the commit takes place and immediately make use of the space this reduction frees up in the table block; when a transaction deletes a row from an index it has to leave the entire index entry in place and flag it as deleted – it cannot immediately reuse the space, it has to wait until after the commit.
    Eine Operation, die in einer Transaktion Sätze löscht und einfügt, sollte einen Index demnach deutlich wachsen lassen; dazu ein kleiner Test, in dem ich eine Tabelle mit Index anlege, alle Sätze der Tabelle lösche und anschließend identische Sätze einfüge:

    --  Test1: ohne commit nach Satzlöschung
    SQL> create table test
      2  as
      3  select rownum rn
      4    from dual
      5  connect by level < 1000000;
    
    Tabelle wurde erstellt.
    
    SQL> create index test_idx on test(rn);
    
    Index wurde erstellt.
    
    SQL> select INDEX_NAME
      2       , LEAF_BLOCKS
      3    from user_indexes
      4   where INDEX_NAME = 'TEST_IDX';
    
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    TEST_IDX                              1099
    
    SQL> delete from test;
    
    999999 Zeilen wurden gelöscht.
    
    SQL> insert into test
      2  select rownum rn
      3    from dual
      4  connect by level < 1000000;
    
    999999 Zeilen wurden erstellt.
    
    SQL>  exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                        estimate_percent=>dbms_stats.auto_sample_size)
    
    SQL> select INDEX_NAME
      2       , LEAF_BLOCKS
      3    from user_indexes
      4   where INDEX_NAME = 'TEST_IDX';
    
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    TEST_IDX                              2197
    

    Ohne Commit wächst der Index also tatsächlich auf die doppelte Größe.

    --  Test2: mit commit nach Satzlöschung
     SQL> drop table test;
    
    Tabelle wurde gelöscht.
    
    SQL> create table test
      2  as
      3  select rownum rn
      4    from dual
      5  connect by level < 1000000;
    
    Tabelle wurde erstellt.
    
    SQL> create index test_idx on test(rn);
    
    Index wurde erstellt.
    
     SQL> select INDEX_NAME
      2       , LEAF_BLOCKS
      3    from user_indexes
      4   where INDEX_NAME = 'TEST_IDX';
    
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    TEST_IDX                              1099
    
    SQL> delete from test;
    
    999999 Zeilen wurden gelöscht.
    
    SQL> commit;
    
    Transaktion mit COMMIT abgeschlossen.
    
    SQL> insert into test
      2  select rownum rn
      3    from dual
      4  connect by level < 1000000;
    
    999999 Zeilen wurden erstellt.
    
    SQL> exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST',
                       estimate_percent=>dbms_stats.auto_sample_size)
    
    PL/SQL-Prozedur erfolgreich abgeschlossen.
    
    SQL> select INDEX_NAME
      2       , LEAF_BLOCKS
      3    from user_indexes
      4   where INDEX_NAME = 'TEST_IDX';
    
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    TEST_IDX                              1099 
    

    In diesem Fall wächst der Index nicht, da der Platz nach dem Commit freigegeben wurde. Der Herr Lewis hat also recht, was mich jetzt aber auch nicht besonders überrascht...

    Für einen unique index würde das hier verwendete Beispiel übrigens eine Wiederverwendung des Speicherplatzes zeigen, da dies eine Besonderheit dieses Index-Typs ist. Bei Richard Foote findet man eine umfangreichere Erläuterung dieses Verhaltens.