Umgang mit virtuellen Spalten in Oracle 11g
von Frank Schneede, ORACLE Deutschland GmbH

Die Verwendung von Ausdrücken wird durch die Oracle Datenbank schon seit langem unterstützt. Ein Beispiel hierfür ist die Verwendung von Ausdrücken in Views oder function-based Indices. Üblicherweise werden Views dafür genutzt, Berechnungen transparent für den Anwender abzubilden. Das geschieht, indem das Ergebnis einer Berechnung aus Spalten der zugrundeliegenden Tabelle als neue Spalte in einer View bereitgestellt wird. Seit Oracle 8i besteht die Möglichkeit, Ausdrücke zu indizieren, welches über die Verwendung von function-based Indices geschieht. In Oracle 11g können nun Ausdrücke als virtuelle Spalten direkt in den Basistabellen gespeichert werden.

Dieser Artikel zeigt auf, wie flexibel der Einsatz von virtuellen Spalten im Vergleich zu den geschilderten Alternativen ist. Hierbei geht es um die Grundlagen der Verwendung von virtuellen Spalten und die verschiedenen Aspekte, die beim Umgang mit virtuellen Spalten zu beachten sind:

  1. Anlegen einer virtuellen Spalte
  2. Indices und Constraints
  3. Hinzufügen virtueller Spalten
  4. Virtuelle Spalten und PL/SQL Funktionen
  5. Virtuelle Spalten im Data-Dictionary
  6. Virtuelle Spalten, Speicherplatz und der Cost Based Optimizer (CBO)
  7. Virtuelle Spalten und Partitionierung
  8. Einschränkungen bei der Nutzung von Virtuellen Spalten


Anlegen einer virtuellen Spalte

Eine Tabelle, die eine virtuelle Spalte enthält, wird über ein einfaches syntakisches Konstrukt angelegt: 
SQL> CREATE TABLE t_test
  2  ( c_col1 INTEGER
  3  , c_col2 INTEGER
  4  , c_col3 INTEGER GENERATED ALWAYS AS (c_col1 + c_col2) VIRTUAL
  5  );

Table created.

SQL>

Wie man erkennen kann, wird die virtuelle Spalte über einen einfachen Ausdruck auf Basis der anderen Spalten der Tabelle erzeugt. Das Schlüsselwort VIRTUAL ist hierbei optional, jedoch erleichtert seine Verwendung die Lesbarkeit des Statements. Werte in virtuellen Spalten werden nicht abgespeichert, sondern lediglich zur Laufzeit berechnet. Dazu wird der Ausdruck - im obigen Beispiel also c_col1 + c_col2 - benutzt. Diese Vorgehensweise spart auf der einen Seite natürlich Platz, auf der anderen Seite sind jedoch ein paar Dinge zu beachten, die das Einfügen von Daten betreffen. 
SQL> INSERT INTO t_test VALUES (10, 20, 30);
INSERT INTO t_test VALUES (10, 20, 30)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns


SQL>

Wie man sieht, ist das explizite Füllen einer virtuellen Spalte nicht möglich. Aus diesem Grunde versuchen wir nun, lediglich die physisch vorhandenen Spalten anzusprechen. 
SQL> INSERT INTO t_test VALUES (10, 20);
INSERT INTO t_test VALUES (10, 20)
            *
ERROR at line 1:
ORA-00947: not enough values


SQL>

Auch dieser Versuch schlägt fehl. Es ist also nicht möglich, virtuelle Spalten in INSERT oder UPDATE Statements direkt anzusprechen. Trotzdem gehören auch virtuelle Spalten natürlich zur Beschreibung der Tabelle. Das bedeutet, dass die Spalten in dem oben gezeigten INSERT Statement explizit referenziert werden müssen. 
SQL> INSERT INTO t_test (c_col1, c_col2) VALUES (10, 20);

1 row created.

SQL>

Da vollständig qualifizierte SQL Statements ohnehin für jeden DBA eine "best practise" darstellen sollte, ist diese Einschränkung de facto irrelevant. Nun ist unsere Beispieltabelle also mit Daten gefüllt und kann abgefagt werden. 
SQL> SELECT * FROM t_test;

    C_COL1     C_COL2     C_COL3
---------- ---------- ----------
        10         20         30

SQL>

Indices und Constraints

Virtuelle Spalten können für Indices und Constraints verwendet werden. Hierbei wird der Wert der virtuellen Spalte im Index gespeichert. In dem folgenden Beispiel wird ein Primärschlüssel Constraint auf der virtuellen Spalte c_col3 angelegt. 
SQL> CREATE UNIQUE INDEX t_test_pk ON t_test(c_col3);

Index created.

SQL> ALTER TABLE t_test ADD
  2  	CONSTRAINT t_test_pk
  3  	PRIMARY KEY (c_col3)
  4  	USING INDEX;

Table altered.

SQL>

Beim Versuch des Einfügens eines neuen Datensatzes mit Werten, die einen schon vorhandenen Wert in der virtuellen Spalte ergeben würden, erfolgt eine Verletzung des Primärschlüssel Constraints. 
SQL> INSERT INTO t_test (c_col1, c_col2) VALUES (10, 20);
INSERT INTO t_test (c_col1, c_col2) VALUES (10, 20)
*
ERROR at line 1:
ORA-00001: unique constraint (VTEST.T_TEST_PK) violated


SQL>

Wie erwartet, wird eine ORA-00001 Fehlermeldung ausgegeben.

Wie man sieht, stellt das Anlegen eines Primärschlüssels keine besondere Herausforderung dar. Im folgenden Beispiel wird nun eine Detail-Tabelle angelegt, deren Fremdschlüssel Constraint auf die virtuelle Spalte c_col3 der Testtabelle verweist. 
SQL> CREATE TABLE t_test_detail
  2  ( c_col3 INTEGER
  3  , CONSTRAINT t_test_detail_fk
  4  	  FOREIGN KEY  (c_col3)
  5  	  REFERENCES  t_test(c_col3)
  6  );

Table created.

SQL>

Beim Einfügen gültiger und ungültiger Datensätze verhält sich das System wie erwartet. 
SQL> INSERT INTO t_test_detail VALUES (30);

1 row created.

SQL> INSERT INTO t_test_detail VALUES (40);
INSERT INTO t_test_detail VALUES (40)
*
ERROR at line 1:
ORA-02291: integrity constraint (VTEST.T_TEST_DETAIL_FK) violated - parent key not found


SQL>

Hinzufügen virtueller Spalten

Selbstverständlich können virtuelle Spalten auch zu einer bereits bestehenden Tabelle mit einem ALTER TABLE Befehl hinzugefügt werden. Im folgenden Beispiel wird eine weitere virtuelle Spalte ergänzt und mit einem zusätzlichen Check-Constraint versehen. 
SQL> ALTER TABLE t_test ADD
  2  	c_col4 GENERATED ALWAYS AS (c_col1 * c_col2)
  3  	CHECK (c_col4 >= 10);

Table altered.

SQL>

Wie bereits oben ausgeführt, wird im Index der Wert der virtuellen Spalte als Ergebnis des zugrundeliegenden Ausdrucks abgespeichert. Ein Check-Constraint wertet zum Zeitpunkt der INSERT- oder UPDATE-Operation den Ausdruck auf Basis der enthaltenen Daten aus. Dieses erscheint offensichtlich, denn ein Check-Constraint besitzt ja keine Datenstruktur, also keinen Index, in dem Werte abgelegt sein könnten.

Auf unserer neuen virtuellen Spalte c_col4 stellt der Check-Constraint sicher, dass das Produkt der Spalten c_col1 und c_col2 größer als 10 ist. Ein kleiner Test zeigt, dass der Check-Constraint funktioniert. 
SQL> INSERT INTO t_test (c_col1, c_col2) VALUES (1, 2);
INSERT INTO t_test (c_col1, c_col2) VALUES (1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (VTEST.SYS_C0011537) violated


SQL>

Virtuelle Spalten und PL/SQL Funktionen

Es ist möglich, virtuelle Spalten über PL/SQL Funktionen zu versorgen, das heißt als Ganzes oder als Teil des gesamten Ausdruckes. Die einzige Bedingung, die an die verwendete PL/SQL Funktion gestellt wird, ist die, dass die Funktion deterministisch sein muss. Die Funktion kann in einem Package enthalten sein. Im folgenden Beispiel wird eine deterministische Funktion erstellt, die die Summe zweier Eingabe-Parameter zurückliefert. 
SQL> CREATE FUNCTION f_summiere(
  2  		     p1 IN INTEGER,
  3  		     p2 IN INTEGER ) RETURN INTEGER DETERMINISTIC AS
  4  BEGIN
  5  	RETURN p1 + p2;
  6  END f_summiere;
  7  /

Function created.

SQL>

Das Schlüsselwort DETERMINISTIC muss hier angegeben werden um sicherzustellen, dass diese Funktion in einer virtuellen Spalte verwendet werden darf. Eine weitere virtuelle Spalte c_col5 wird zu unserer Testtabelle hinzugefügt. Diese Spalte enthält die Summe der Spalten c_col1 und c_col2, die hier jedoch mittels der Funktion f_summiere gebildet wird. Anschließend wird die neue virtuelle Spalte abgefragt. 
SQL> ALTER TABLE t_test ADD
  2  	c_col5 GENERATED ALWAYS AS (f_summiere(c_col1,c_col2));

Table altered.

SQL> SELECT c_col1, c_col2, c_col5 FROM t_test;

    C_COL1     C_COL2     C_COL5
---------- ---------- ----------
        10         20         30

SQL>

Man sieht, dass die neue Spalte c_col5 den gleichen Wert hat, wie die virtuelle Spalte c_col3. Es ist für das Verständnis des Datenmodells hilfreich, Ausdrücke über Programmkonstrukte (Funktionen, Packages) zu definieren, jedoch ist dieses Vorgehen mit Auswirkungen auf die Performance verbunden. Dieses zeigt sich an einem kleinen Beispiel, in dem 1.000.000 Zeilen mit den Spalten c_col3 und c_col5 abgefragt werden. Zuerst müssen diese Testdaten jedoch erzeugt werden. 
SQL> INSERT INTO t_test (c_col1, c_col2)
  2  SELECT ROWNUM*10, TRUNC(ROWNUM/20)+1
  3  FROM   dual
  4  CONNECT BY ROWNUM < 1000000;

999999 rows created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL>

Jetzt befinden sich also 1 Million Testdatensätze in unserer Tabelle. Diese werden über die Spalten c_col3 und c_col5 in zwei unterschiedlichen Select-Statements abgefragt. Um die Auswirkungen auf die Performance sehen zu können, wird Autotrace aktiviert und Timing gesetzt. 
SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> SELECT c_col3 FROM t_test;

1000000 rows selected.

Elapsed: 00:00:05.43

Statistics
----------------------------------------------------------
         28  recursive calls
          3  db block gets
      69746  consistent gets
          0  physical reads
      70956  redo size
   15556478  bytes sent via SQL*Net to client
     733745  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> SELECT c_col5 FROM t_test;

1000000 rows selected.

Elapsed: 00:00:09.93

Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
      69698  consistent gets
          0  physical reads
      64312  redo size
   15556478  bytes sent via SQL*Net to client
     733745  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

In unserem Vergleich benötigt die Abfrage der virtuellen Spalte c_col5, die auf einer PL/SQL Funktion basiert, ungefähr doppelt so lange wie die Abfrage auf Basis des Ausdruckes (c_col3). Der I/O-Aufwand für beide Abfragen ist annähernd gleich. Bei einfachen Ausdrücken wie in unserem Beispiel macht es also durchaus Sinn, die Berechnungslogik in die virtuellen Spalten zu integrieren und den Nachteil in der Performance zu vermeiden. Virtuelle Spalten auf Basis von PL/SQL Funktionen mit einer geringen Kardinalität können von dem in Oracle 11g eingeführten Feature des Result Caches profitieren. Ein Performancenachteil gegenüber den "inline" definierten Ausdrücken wird sich dennoch zeigen.

Virtuelle Spalten im Data-Dictionary

Beim Design von Datenodellen ist es wichtig, diese so zu gestalten, dass mit der Abfrage des Data-Dictionary das System möglichst selbsterklärend beschrieben werden kann. Hierzu sind Kommentare sehr hilfreich, die für physisch vorhandene und virtuelle Spalten angelegt werden sollten, wie im folgenden Beispiel gezeigt. 
SQL> COMMENT ON COLUMN t_test.c_col3 IS 'Virtual column [c_col1 + c_col2]';

Comment created.

SQL> COMMENT ON COLUMN t_test.c_col4 IS 'Virtual column [c_col1 * c_col2]';

Comment created.

SQL> COMMENT ON COLUMN t_test.c_col5 IS 'Virtual column [f_summiere(c_col1,c_col2)]';

Comment created.

SQL> SELECT column_name
  2  ,	    comments
  3  FROM   user_col_comments
  4  WHERE  table_name = 'T_TEST';

COLUMN_NAME                    COMMENTS
------------------------------ ---------------------------------------------
C_COL1
C_COL2
C_COL3                         Virtual column [c_col1 + c_col2]
C_COL4                         Virtual column [c_col1 * c_col2]
C_COL5                         Virtual column [f_summiere(c_col1,c_col2)]

SQL>

Informationen über virtuelle Spalten können an unterschiedlichen Stellen im Data-Dictionary gefunden werden. In den folgenden Abfragen werden einige USER-Dictionary Views gezeigt, die korrespondierenden ALL- und DBA-Views beinhalten die gleichen Informationen. Das erste Beispiel zeigt die Spalten der Testtabelle mit dem Wert DATA_DEFAULT. 
SQL> SELECT column_name
  2  ,	    data_type
  3  ,	    data_default
  4  FROM   user_tab_columns
  5  WHERE  table_name = 'T_TEST'
  6  ORDER  BY
  7  	    column_id;

COLUMN_NAME DATA_TYPE DATA_DEFAULT
----------- --------- ---------------------------------------
C_COL1      NUMBER
C_COL2      NUMBER
C_COL3      NUMBER    "C_COL1"+"C_COL2"
C_COL4      NUMBER    "C_COL1"*"C_COL2"
C_COL5      NUMBER    "VTEST"."F_SUMMIERE"("C_COL1","C_COL2")

SQL>

Die Spalte DATA_DEFAULT zeigt hier also die Definition der Ausdrücke, die die virtuellen Spalten beschreiben. Anhand der Data-Dictionary View USER_TAB_COLUMNS kann man zwischen physisch vorhandenen und virtuellen Spalten unterscheiden. 
SQL> SELECT column_name
  2  ,	    virtual_column
  3  ,	    segment_column_id
  4  ,	    internal_column_id
  5  FROM   user_tab_cols
  6  WHERE  table_name = 'T_TEST'
  7  ORDER  BY
  8  	    column_id;

COLUMN_NAME VIRTUAL_COLUMN SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----------- -------------- ----------------- ------------------
C_COL1      NO                             1                  1
C_COL2      NO                             2                  2
C_COL3      YES                                               3
C_COL4      YES                                               4
C_COL5      YES                                               5

SQL>

Der oben angelegte Index kann durch Abfrage der View USER_INDEXES abgefragt werden. Der INDEX_TYPE zeigt, dass es sich - wie oben gesagt - um einen function-based Index auf der virtuellen Spalte handelt. Weder die virtuellen Spalten, noch die Ausdrücke des function-based Index werden physisch an einem anderen Ort abgelegt als im Index selbst. Die Data-Dictionary View USER_IND_EXPRESSIONS zeigt Informationen über den Primärschlüsselindex auf der virtuellen Spalte c_col3. 
SQL> SELECT index_name
  2  ,	    index_type
  3  ,	    funcidx_status
  4  FROM   user_indexes
  5  WHERE  table_name = 'T_TEST';

INDEX_NAME INDEX_TYPE                  FUNCIDX_STATUS
---------- --------------------------- --------------
T_TEST_PK  FUNCTION-BASED NORMAL       ENABLED

SQL> SELECT *
  2  FROM   user_ind_expressions
  3  WHERE  index_name = 'T_TEST_PK';

INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
---------- ---------- ----------------- ---------------
T_TEST_PK  T_TEST     "C_COL1"+"C_COL2"               1

SQL>

Diese Informationen im Data-Dictionary sind für jeden DBA wichtig. Es sei an dieser Stelle bemerkt, dass andere Data-Dictionary Views (z. B. USER_CONS_COLUMNS, USER_CONSTRAINT, USER_COL_COMMENTS, ...) nicht zwischen physisch vorhandenen und virtuellen Spalten unterscheiden.

Virtuelle Spalten, Speicherplatz und der Cost Based Optimizer (CBO)

Wie bereits oben ausgeführt, belegen virtuelle Spalten selbst keinen Speicherplatz. Funktionen wie DUMP oder VSIZE liefern hingegen trotzdem die "normalen" Werte zurück, denn die Funktionen arbeiten auf dem Ergebnis des Ausdrucks, der der virtuellen Spalte zugrunde liegt. Dieses Verhalten sieht man im folgenden Beispiel. 
SQL> SELECT DUMP(c_col3)  AS dump
  2  ,	    VSIZE(c_col3) AS vsize
  3  FROM   t_test
  4  WHERE  ROWNUM = 1;

DUMP                                  VSIZE
----------------------------------- -------
Typ=2 Len=2: 193,12                       2

SQL>

Die Tatsache, dass auch mehrere virtuelle Spalten keinen zusätzlichen Platz verbrauchen, läßt sich an dem folgenden Beispiel leicht beweisen. Es werden zwei Tabellen angelegt und mit jeweils 10.000 Datensätzen gefüllt. 
SQL> CREATE TABLE t_storage_test
  2  ( c_col1 VARCHAR2(4000)
  3  , c_col2 VARCHAR2(4000)
  4  , c_col3 VARCHAR2(4000)
  5  )
  6  PCTFREE 0;

Table created.

SQL> CREATE TABLE t_storage_test_vc
  2  ( c_col1  VARCHAR2(4000)
  3  , c_col2  VARCHAR2(4000)
  4  , c_col3  VARCHAR2(4000)
  5  , c_col4 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c_col1)) VIRTUAL
  6  , c_col5 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c_col2)) VIRTUAL
  7  , c_col6 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c_col3)) VIRTUAL
  8  )
  9  PCTFREE 0;

Table created.

SQL> INSERT INTO t_storage_test
  2  SELECT RPAD('x',4000)
  3  ,	    RPAD('x',4000)
  4  ,	    RPAD('x',4000)
  5  FROM   dual
  6  CONNECT BY ROWNUM <= 10000;

10000 rows created.

SQL> INSERT INTO t_storage_test_vc (c_col1, c_col2, c_col3)
  2  SELECT RPAD('x',4000)
  3  ,	    RPAD('x',4000)
  4  ,	    RPAD('x',4000)
  5  FROM   dual
  6  CONNECT BY ROWNUM <= 10000;

10000 rows created.

SQL>

Jetzt lassen sich beide Tabellen über eine einfache Data-Dictionary Abfrage vergleichen. 
SQL> SELECT segment_name
  2  ,	    bytes
  3  ,	    blocks
  4  ,	    extents
  5  FROM   user_segments
  6  WHERE  segment_name LIKE 'T_STORAGE_TEST%';

SEGMENT_NAME                   BYTES     BLOCKS    EXTENTS
------------------------- ---------- ---------- ----------
T_STORAGE_TEST             125829120      15360         86
T_STORAGE_TEST_VC          125829120      15360         86

SQL>

Wie erwartet, ist der Bedarf an Speicherplatz für beide Tabellen identisch.

Der Cost Based Optimizer (CBO) behandelt physisch vorhandene und virtuelle Spalten gleich, d. h. es wird auf Basis identischer Annahmen kalkuliert, wenn entsprechende Statistiken fehlen. Durch Aktivierung des AUTOTRACE Feature kann man die Ausführungspläne der Abfragen auf der soeben angelegten Tabelle t_storage_test_vc sehen. Um den Ausführungsplan lesbarer zu gestalten, wird als erstes ein Update durchgeführt. Anschließend erfolgt die erste Beispielabfrage. 
SQL> UPDATE t_storage_test_vc SET c_col1 = 'X';

10000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> set autotrace traceonly explain
SQL> SELECT * FROM t_storage_test_vc WHERE c_col1 = 'X';

Execution Plan
----------------------------------------------------------
Plan hash value: 1169272982

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   | 11337 |   129M|  5443  (25)| 00:01:06 |
|*  1 |  TABLE ACCESS FULL| T_STORAGE_TEST_VC | 11337 |   129M|  5443  (25)| 00:01:06 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_COL1"='X')

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>

Da keine Statistiken vorhanden sind, wird dynamic sampling verwendet. Der CBO nimmt an, dass nahezu alle Sätze der Tabelle die Abfragekriterien erfüllen. Die zu c_col1 korrespondierende Spalte ist c_col4 und wird über den Ausdruck UPPER(c_col1) erzeugt. Die Abfrage ergibt folgendes Bild. 
SQL> SELECT * FROM t_storage_test_vc WHERE c_col4 = 'X';

Execution Plan
----------------------------------------------------------
Plan hash value: 1169272982

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |   113 |  1325K|  5452  (25)| 00:01:06 |
|*  1 |  TABLE ACCESS FULL| T_STORAGE_TEST_VC |   113 |  1325K|  5452  (25)| 00:01:06 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_COL4"='X')

Note
-----
   - dynamic sampling used for this statement (level=2)

Der CBO ermittelt eine andere Kardinalität für die virtuelle Spalte, ungeachtet der Tatsache, dass c_col4 sich direkt aus c_col1 ergibt. Offenbar legt der CBO die Standard-Selektivität von 1% für einen Ausdruck der Form "function(column)=literal" an, um zu diesem Ergebnis zu kommen. Interessant ist, dass auch hier dynamic sampling zugrunde gelegt wird. Das bedeutet, dass auch auf virtuellen Spalten Statistiken berechnet werden können. Dieses wiederum hat zur Folge, dass der CBO mit Statistiken auf Spaltenebene und natürlich mit Histogrammen eine wesentlich genauere Abschätzung der Kosten des Statements vornehmen kann. Also werden nun Statistiken berechnet und die Abfrage auf die virtuelle Spalte c_col4 wird erneut ausgeführt. 
SQL> set autotrace off
SQL> 
SQL> BEGIN
  2  	DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_STORAGE_TEST_VC');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> SELECT * FROM t_storage_test_vc WHERE c_col4 = 'X';

Execution Plan
----------------------------------------------------------
Plan hash value: 1169272982

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   | 10000 |   152M|  5452  (25)| 00:01:06 |
|*  1 |  TABLE ACCESS FULL| T_STORAGE_TEST_VC | 10000 |   152M|  5452  (25)| 00:01:06 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_COL4"='X')

SQL>

Man sieht auf den ersten Blick, dass nun die korrekte Kardinalität zugrunde gelegt wird. Dieses läßt sich anhand weiterer Abfragen auf Tabellen- und Spaltenebene verifizieren. 
SQL> SELECT num_rows
  2  ,	    sample_size
  3  FROM   user_tab_statistics
  4  WHERE  table_name = 'T_STORAGE_TEST_VC';

  NUM_ROWS SAMPLE_SIZE
---------- -----------
     10000       10000

SQL> SELECT column_name
  2  ,	    num_distinct
  3  ,	    density
  4  ,	    num_nulls
  5  ,	    num_buckets
  6  ,	    histogram
  7  FROM   user_tab_col_statistics
  8  WHERE  table_name = 'T_STORAGE_TEST_VC'
  9  ORDER  BY
 10  	    column_name;

COLUMN_NAME NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
----------- ------------ ---------- ---------- ----------- ---------------
C_COL1                 1     .00005          0           1 FREQUENCY
C_COL2                 1          1          0           1 NONE
C_COL3                 1          1          0           1 NONE
C_COL4                 1     .00005          0           1 FREQUENCY
C_COL5                 1          1          0           1 NONE
C_COL6                 1          1          0           1 NONE

6 rows selected.

SQL>

Die Statistiken scheinen wesentlich besser zu sein. Die Spalte DENSITY besitzt für die Spalten c_col1 und c_col4 andere Werte, da Histogramme angelegt worden sind. Die Histogramme wurden automatisch erzeugt, da diese Spalten in den Abfragen vor Berechnung der Statistiken verwendet worden sind. Die Verwendung aller in Prädikaten verwendeten Spalten wird in den neueren Versionen von Oracle durchgeführt, um das Berechnen der Statistiken noch präziser automatisiert ausführen zu können.

Virtuelle Spalten und Partitionierung

Oben wurde ausgeführt, dass virtuelle Spalten in Primärschlüsseln verwendet werden können. Ebenso können sie seit Oracle 11g als Partitionsschlüssel in partitionierten Tabellen verwendet werden. Dieses funktioniert auch für sub-partitionierte Tabellen, in denen die virtuelle Spalte sowohl in der Partition als auch in der Subpartition als Schlüssel verwendet wird. Die Funktionsweise soll an einem einfachen Beispiel gezeigt werden, das auf den Daten der Data-Dictionary View ALL_OBJECTS beruht. Die Spalte c_col_p1 ist hier eine virtuelle Spalte, deren Wert auf Basis der Spalten c_col_c1 und c_col_c2 berechnet wird. 
SQL> CREATE TABLE t_part_test
  2  ( c_col_n1 INTEGER
  3  , c_col_c1 VARCHAR2(30)
  4  , c_col_c2 VARCHAR2(30)
  5  , c_col_d1 DATE NOT NULL
  6  , c_col_p1 VARCHAR2(61)
  7  	     GENERATED ALWAYS
  8  	     AS (c_col_c1 || '_' || CASE
  9  				 WHEN c_col_c2 LIKE 'TABLE%'
 10  				 THEN 'TABLE'
 11  				 WHEN c_col_c2 LIKE 'INDEX%'
 12  				 THEN 'INDEX'
 13  				 WHEN c_col_c2 LIKE 'PACKAGE%'
 14  				 OR   c_col_c2 LIKE 'TYPE%'
 15  				 OR   c_col_c2 IN ('TRIGGER','PROCEDURE','FUNCTION')
 16  				 THEN 'PLSQL'
 17  				 ELSE 'OTHER'
 18  			      END)
 19  )
 20  PARTITION BY LIST (c_col_p1)
 21  ( PARTITION p_scott_table VALUES ('SCOTT_TABLE')
 22  , PARTITION p_scott_index VALUES ('SCOTT_INDEX')
 23  , PARTITION p_scott_plsql VALUES ('SCOTT_PLSQL')
 24  , PARTITION p_scott_other VALUES ('SCOTT_OTHER')
 25  , PARTITION p_sh_table VALUES ('SH_TABLE')
 26  , PARTITION p_sh_index VALUES ('SH_INDEX')
 27  , PARTITION p_sh_plsql VALUES ('SH_PLSQL')
 28  , PARTITION p_sh_other VALUES ('SH_OTHER')
 29  );

Table created.

SQL>

Nachdem die partitinierte Tabelle mit 8 Partitionen erstellt worden ist, wird diese nun mit Daten versorgt. Eine kleine Abfrage zeigt die Datenverteilung. Zum Abschluß der Vorbereitungen werden die Statistiken auf der partitionierten Tabelle berechnet. 
SQL> INSERT INTO t_part_test (c_col_n1, c_col_c1, c_col_c2, c_col_d1)
  2  SELECT object_id
  3  ,	    owner
  4  ,	    object_type
  5  ,	    created
  6  FROM   dba_objects
  7  WHERE  owner IN ('SCOTT','SH');

312 rows created.

SQL> SELECT c_col_p1, COUNT(*)
  2  FROM   t_part_test
  3  WHERE  c_col_c1 = 'SH'
  4  GROUP  BY
  5  	    c_col_p1;

C_COL_P1                                                        COUNT(*)
------------------------------------------------------------- ----------
SH_TABLE                                                              73
SH_INDEX                                                             223
SH_OTHER                                                              10

SQL> BEGIN
  2  	DBMS_STATS.GATHER_TABLE_STATS(user,'T_PART_TEST');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

Die folgenden Abfragen sollen nun zeigen, wie auch bei der Partitionierung mit virtuellen Spalten eine Partition Elimination funktioniert. Über das AUTOTRACE Feature wird der Ausführungsplan erzeugt. 
SQL> set autotrace traceonly explain
SQL> SELECT * FROM t_part_test WHERE c_col_p1 = 'SH_INDEX';

Execution Plan
----------------------------------------------------------
Plan hash value: 2593459040

-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   223 |  8920 |     7  (58)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|             |   223 |  8920 |     7  (58)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS FULL   | T_PART_TEST |   223 |  8920 |     7  (58)| 00:00:01 |     6 |     6 |
-----------------------------------------------------------------------------------------------------

SQL>

Man sieht, dass durch den Equi-Join auf dem Partitionsschlüssel wie erwartet alle Partitionen elimiert werden, außer der Partition, die die gewünschten Daten enthält. Das folgende Beispiel zeigt einen LIKE-Ausdruck auf dem Partitionierungsschlüssel: 
SQL> SELECT * FROM t_part_test WHERE c_col_p1 LIKE 'SH%';

Execution Plan
----------------------------------------------------------
Plan hash value: 509857162

-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |    62 |  2418 |    11  (46)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ITERATOR|             |    62 |  2418 |    11  (46)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL     | T_PART_TEST |    62 |  2418 |    11  (46)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C_COL_P1" LIKE 'SH%')

SQL>

Man sieht, dass in diesem Beispiel ein PARTITION LIST ITERATOR für den LIKE-Ausdruck gewählt wird. Die zu lesende Partition wird also erst zur Laufzeit ermittelt. Dieses funktiniert natürlich nur für LIKE-Ausdrücke, die KEINE führenden Wildcards besitzen.

Einschränkungen bei der Nutzung von Virtuellen Spalten

Wie man aus den obigen Ausführungen und Beispielen entnehmen konnte, sind virtuelle Spalten ein sehr mächtiges Werkzeug. Es gibt jedoch noch einge Einschränkungen, die bei der Benutzung virtueller Spalten beachtet werden müssen:
  • Virtuelle Spalten können nur in sogenannten Heap-organisierten Tabellen verwendet werden. Virtuelle Spalten in index-organized, externen, Objekt-, Cluster- oder temporären Tabellen sind nicht unterstützt.
  • Der Ausdruck in der AS-Klausel der Defintion der virtuellen Spalte hat folgende Einschränkungen:
    • Der Ausdruck darf sich nicht auf eine andere virtuelle Spalte beziehen.
    • Jede Spalte, auf die sich die virtuelle Spalte bezieht, muss sich in der gleichen Tabelle befinden.
    • Eine deterministische benutzerdefinierte Funktion kann verwendet werden. In diesem Fall kann die virtuelle Spalte jedoch NICHT als Partitionierungsschlüssel genutzt werden.
    • Das Ergebnis des Ausdruckes muss ein skalarer Wert sein.
  • Eine virtuelle Spalte darf kein Oracle-supplied Datentyp (z. B. LCR-Typ) sein, ebenso kein benutzer-definierter Typ, LOB oder LONG RAW.


Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite