Use cases für die model clause

Tony Hasler spricht in seinem Blog zwei Fälle an, in denen die model clause etwas leistet, das nicht genauso gut mit PL/SQL umzusetzen wäre:

• Implementierung von Analytics, die nicht direkt durch SQL-Funktionen zur Verfügung gestellt werden
• Parallelisierte Ausführung

Die Beispiele für Fall 1 sind die Definition eines MEDIAN über einen sich bewegenden Zeitraum (die als model wirklich sehr kompakt wird; in solchen Fällen habe ich in der Vergangenheit üblicherweise einen self join verwendet, bei dem ich die aktuelle Zeile mit dem entsprechenden range verknüpfte) und die Bestimmung eines zscores (= Abstand eines Werts vom Mittelwert in Anzahl Standardabweichungen).

Split Partition und Indizes

Nur eine kurze Notiz: sofern man ins Kommando ALTER TABLE ... SPLIT PARTITION ...; nicht explizit die Option UPDATE INDEXES aufnimmt, führt das das Splitting zur Invalidierung der Indizes in der Quell- und in der Zielpartition, sofern anschließend in beiden Partitionen Werte enthalten sind:

-- 11.1.0.7
drop table test_mpr;

create table test_mpr (
    part_col number
  , col1 number
)
partition by list (part_col) 
(
    partition part_default values (default)
)
;
 
create index test_mpr_ix on test_mpr (
     col1
) local;
 
insert into test_mpr (part_col, col1) values (1, 1);
insert into test_mpr (part_col, col1) values (2, 2);
 
commit;

select partition_name
     , status 
  from user_ind_partitions
 where index_name = 'TEST_MPR_IX';

PARTITION_NAME                 STATUS
------------------------------ --------
PART_DEFAULT                   USABLE

alter table test_mpr 
split partition part_default values(1)
into (partition p1, partition part_default); 

select partition_name
     , status 
  from user_ind_partitions
 where index_name = 'TEST_MPR_IX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             UNUSABLE
PART_DEFAULT                   UNUSABLE

Nach dem Split enthalten beide Tabellen-Partitionen je einen Satz und beide Index-Partitionen sind UNUSABLE. Anders verhält es sich, wenn die Default-Partition nach dem Split leer ist: dann behandelt Oracle die Operation offenbar als reine Umbenennung und die Indizes bleiben USABLE. Um die Indizes nach dem Split in jedem Fall funktionsfähig zu erhalten, dient die UPDATE INDEXES-Klausel:

alter table test_mpr
split partition part_default values(1)
into (partition p1, partition part_default)
update indexes;

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             USABLE
PART_DEFAULT                   USABLE

Ob die Maintainance der Indizes im Fall der Verwendung von UPDATE INDEXES aufwändiger ist als bei einem kompletten Rebuild wäre gelegentlich auch noch interessant.

Nachtrag 31.10.2012: beim Vergleich der v$sesstat-Inhalte für den Fall der Verwendung von UPDATE INDEXES und den des expliziten REBUILD der Index-Partitionen nach einem Split ohne UPDATE INDEXES kann ich keine signifikanten Verarbeitungsunterschiede erkennen: anscheinend erfolgt in beiden Fällen ein kompletter Neuaufbau der Indizes. Das gilt auch für einen 99:1-Split, bei dem man argumentieren könnte, dass eine behutsame Aktualisierung des Index effizienter wäre. Allerdings würde das Verfahren dadurch natürlich auch komplizierter und fehleranfälliger.

Nachtrag 25.11.2012: für die Statistiken gelten ähnliche Gesetzmäßigkeiten: ein Split ohne Migration von Daten im Segment (also ein Split, der als Metadatenänderung nur einer Umbennenung entspricht), führt nicht zur Invalidierung der Statistiken. Wenn der Split aber Daten verschiebt, dann werden die Partitionsstatistiken gelöscht - ein Verhalten, das ich für völlig konsistent und schlüssig halte.

Oracles Big Data Lösungen

Keine eigenen Gedanken, nur ein Link (bzw. mehrere) ...

Mark Rittman hat in seinem Blog eine interessante Serie zu Oracles aktuellen Lösungen im Umfeld Big Data Analysis veröffentlicht:

• Oracle Exalytics, Oracle R Enterprise and Endeca Part 1 : Oracle’s Analytics, Engineered Systems, and Big Data Strategy
• Oracle Exalytics, Oracle R Enterprise and Endeca Part 2 : Oracle Endeca, the Advanced Analytics Option and Oracle Exalytics
• Oracle Exalytics, Oracle R Enterprise and Endeca Part 3 : Flight Delays Analysis using OBIEE, Endeca and Oracle R Enterprise on Exalytics

Sperrige Titel (die bei mir irgendwie die Assoziation an Tlön, Uqbar, Orbis Tertius aufrufen), aber sehr interessante Ausführungen, die das Zusammenspiel der Oracle-BI-Tools verdeutlichen. Ich spare mir das Exzerpieren, da ich aktuell mit keinem der angesprochenen Produkte arbeite (obwohl das sicher spannend wäre ...).

Materialized Views und ORA-32036

Vor längerer Zeit hatte ich erwähnt, dass die Verwendung von Queries mit subquery factoring (CTEs, WITH-Klauseln) als Datengrundlage für das SSAS-Prozessing oder auch für SSRS-Berichte den (nicht unbedingt selbsterklärenden) Fehler "ORA-32036: Nicht unterstützte Schreibweise für Inlining von Abfrage-Name in WITH-Klausel" hervorrufen kann. Dabei konnte man die Queries über sqlplus oder SQL Developer problemlos ausführen, aber beim Zugriff der Microsoft-Systeme ergaben sich die Probleme, die sich nur durch Refakturierung des Codes oder größere Umwege (z.B. die Definition entsprechender table functions) umgehen ließen.

Gestern ist mir ORA-32036 wieder begegnet: diesmal bei der Anlage einer Materialized View. Und in diesem Fall lässt sich das Verhalten sehr leicht reproduzieren:

-- Anlage von zwei - sehr simplen - Basistabellen
create table test_base_mpr
as
select rownum id
     , mod(rownum, 2) col1
  from dual
connect by level <= 100;

create table test_add_mpr
as
select rownum col1
  from dual
connect by level <= 10;

create materialized view test_mpr
as
with
basedata as (
select test_base_mpr.id
     , test_base_mpr.col1
  from test_base_mpr
  left outer join 
       test_add_mpr
    on (test_base_mpr.col1 = test_add_mpr.col1)
)
,
basedata_filtered as (
select * 
  from basedata
 where id <= 50
)
,
basedata_grouped as (
select col1
     , count(*) count_id
  from basedata_filtered
 group by col1
)
select basedata_filtered.col1
     , basedata_grouped.count_id
  from basedata_filtered
  left outer join
       basedata_grouped
    on basedata_filtered.col1 = basedata_grouped.col1
 order by basedata_filtered.col1;
 
FEHLER in Zeile 1:
ORA-32036: Nicht unterstützte Schreibweise für Inlining von Abfrage-Name in WITH-Klausel

-- wenn man die CTE basedata in eine inline-View umwandelt, gibt es keine Probleme:
create materialized view test_mpr
as
with
basedata_filtered as (
select *
  from (select test_base_mpr.id
             , test_base_mpr.col1
          from test_base_mpr
          left outer join
               test_add_mpr
            on (test_base_mpr.col1 = test_add_mpr.col1)
        ) basedata
 where id <= 50
)
,
basedata_grouped as (
select col1
     , count(*) count_id
  from basedata_filtered
 group by col1
)
select basedata_filtered.col1
     , basedata_grouped.count_id
  from basedata_filtered
  left outer join
       basedata_grouped
    on basedata_filtered.col1 = basedata_grouped.col1
 order by basedata_filtered.col1;

Materialized View wurde erstellt.

Auch in diesem Fall lässt sich die ursprüngliche Query via sqlplus problemlos ausführen (über die Semantik der Query und den Inhalt der Ergebnismenge - je 25 mal die Tupel (0, 25) und (1, 25) - muss man nicht weiter nachdenken). Auch kann man auf der Basis der Definition eine (nicht materialisierte) View anlegen. Aber die MV stellt offenbar ein Problem dar. Beim Vergleich der Pläne für die SELECTs (inklusive "Column Projection Information") sehe ich - abgesehen von generierten Query-Block- und temp-table-transformation-Namen - keine Unterschiede. Dabei sollte das SQL der Query dem des MV-Aufbaus entsprechen: zumindest im Fall des erfolgreichen Aufbaus trifft dies zu, hier scheint nur das übliche

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MPR"

vor der Definitions-Query.

Mein Versuch, die exakten Voraussetzungen des Problems zu bestimmen, bleibt erst einmal unvollständig. Ursprünglich nahm ich an, dass die Verwendung einer CTE an mehreren Stellen der Query genügen würde, um den Fehler hervorzurufen, aber auch die funktionierende Version verwendet die CTE basedata_filtered als Basis für basedata_grouped und als Element des Outer Joins im Haupt-SELECT. Möglicherweise spielt tatsächlich nur die CTE-Verschachtelungstiefe eine Rolle. In jedem Fall ist das Problem ziemlich unerfreulich, da es dazu zwingt, den durch CTEs verständlich gegliederten SQL-Code wieder in eine Form zu bringen, die der CBO offenbar besser versteht - die aber für den Entwickler in vielen Fällen schwerer zu durchschauen sein dürfte. Falls mir (oder jemandem, der hier vorbei kommt) jenseits der Phänomenologie eine plausible Erklärung dazu einfällt, liefere ich sie nach.

Aus Gründen der Vollständigkeit hier auch noch mal der Link auf Dom Brooks Überlegungen zum Thema - interessant sind dort auch die Kommentare.

COUNT und bitmap Indizes

Zu den alten Wahrheiten, mit denen ich seit Jahren meine unvorsichtigen Zuhörer langweile, gehört, dass es keinen Unterschied macht, ob man in einer Query count(*) oder count(1) zum Zählen der Sätze verwendet. Falls jemand Details zum Thema wünscht, verweise ich auf Tom Kyte, der das Thema bereits 2001 abschließend beantwortet hat. Jetzt liest man bei Jonathan Lewis, dass es im Fall vorliegender bitmap Indizes allerdings einen Unterschied macht, ob man count(1) oder count(-1) verwendet: für den count(1)-Fall (und ebenso für count(*)) kann Oracle die Abkürzung des BITMAP CONVERSION COUNT verwenden, für count(-1) muss hingegen eine BITMAP CONVERSION TO ROWIDS durchgeführt werden. Immer wieder erstaunlich, welche Überraschungen bei solch harmlosen Tests auftauchen.

Skip Scan Costing

Jonathan Lewis führt in seinem Blog ein ziemlich einfaches Beispiel vor, in dem aufgrund eines deutlich besseren ClusteringFactors ein SKIP SCAN auf die zweite Spalte eines zusammengesetzten Index erfolgt statt des (für den menschlichen Benutzer plausibleren) Zugriffs über einen Index, der nur diese Spalte beinhaltet. Er weist dabei auch darauf hin, dass das Kostenmodell für SKIP SCANs insgesamt nicht viel taugt, was sich mit meinen Beobachtungen deckt - ich betrachte den SKIP SCAN fast schon als Warnsignal; umgekehrt tritt er dann aber nicht unbedingt dort auf, wo er hilfreich wäre.

Bitmap Indizes und Partitions-Compression

Im Fall des folgenden Problems wundere ich mich, dass mir das erst jetzt aufgefallen ist - aber vielleicht habe ich frühere Begegnungen auch einfach vergessen. Es handelt sich um eine jener kleinen Asymmetrien, bei denen ich mir die Frage stelle, ob es dafür eine inhaltliche Begründung gibt: das nachträgliche Komprimieren einzelner Partitionen von Tabellen mit lokalen B*Tree Indizes ist eine ganz harmlose Operation. Kommen aber bitmap Indizes ins Spiel, wird der Vorgang komplizierter. Dazu ein Test.

Fall 1: nachträgliche Komprimierung einer Partition mit B*Tree Index

-- 11.1.0.7
-- Anlage einer List-partitionierten Tabelle mit zwei Spalten
drop table list_part;
create table list_part (
    start_date date
  , col1 number
)
partition by list (start_date)
(
    partition P201201 values (to_date('01.01.2012', 'dd.mm.yyyy'))
  , partition P201202 values (to_date('01.02.2012', 'dd.mm.yyyy'))
  , partition P201203 values (to_date('01.03.2012', 'dd.mm.yyyy'))
  , partition P201204 values (to_date('01.04.2012', 'dd.mm.yyyy'))
  , partition P201205 values (to_date('01.05.2012', 'dd.mm.yyyy'))
  , partition P201206 values (to_date('01.06.2012', 'dd.mm.yyyy'))
  , partition P201207 values (to_date('01.07.2012', 'dd.mm.yyyy'))
  , partition P201208 values (to_date('01.08.2012', 'dd.mm.yyyy'))
  , partition P201209 values (to_date('01.09.2012', 'dd.mm.yyyy'))
  , partition P201210 values (to_date('01.10.2012', 'dd.mm.yyyy'))
);

insert into list_part(start_date, col1) 
values (to_date('01.01.2012', 'dd.mm.yyyy'), 1);

-- Anlage eines lokalen B*Tree Index
create index list_part_ix_local on list_part (col1) local;

Im Lauf der Zeit wird mir nun klar, dass ich die älteren Partitionen dieser Tabelle gerne komprimieren würde:

alter table list_part modify partition P201201 compress;
--> Tabelle wurde geändert.
alter table list_part move partition P201201;
--> Tabelle wurde geändert.

select partition_name
     , status
  from user_ind_partitions
 where index_name = 'LIST_PART_IX_LOCAL'
/

PARTITION_NAME                 STATUS
------------------------------ --------
P201201                        UNUSABLE
P201202                        USABLE
P201203                        USABLE
P201204                        USABLE
P201205                        USABLE
P201206                        USABLE
P201207                        USABLE
P201208                        USABLE
P201209                        USABLE
P201210                        USABLE

Die nachträgliche Komprimierung der Einzelpartition funktioniert also ohne Probleme. Der lokale Index wird durch die physikalische Reorganisation der Tabelle aber natürlich unbenutzbar und muss neu aufgebaut werden.

Fall 2: nachträgliche Komprimierung einer Partition mit bitmap Index

Nun zum bitmap Index:

-- 11.1.0.7
-- Anlage einer List-partitionierten Tabelle mit zwei Spalten
drop table list_part;
create table list_part (
    start_date date
  , col1 number
)
partition by list (start_date)
(
    partition P201201 values (to_date('01.01.2012', 'dd.mm.yyyy'))
  , partition P201202 values (to_date('01.02.2012', 'dd.mm.yyyy'))
  , partition P201203 values (to_date('01.03.2012', 'dd.mm.yyyy'))
  , partition P201204 values (to_date('01.04.2012', 'dd.mm.yyyy'))
  , partition P201205 values (to_date('01.05.2012', 'dd.mm.yyyy'))
  , partition P201206 values (to_date('01.06.2012', 'dd.mm.yyyy'))
  , partition P201207 values (to_date('01.07.2012', 'dd.mm.yyyy'))
  , partition P201208 values (to_date('01.08.2012', 'dd.mm.yyyy'))
  , partition P201209 values (to_date('01.09.2012', 'dd.mm.yyyy'))
  , partition P201210 values (to_date('01.10.2012', 'dd.mm.yyyy'))
);

insert into list_part(start_date, col1) 
values (to_date('01.01.2012', 'dd.mm.yyyy'), 1);

-- Anlage eines lokalen bitmap Index
-- wobei der Versuch, auf einer partitionierten Tabelle einen globalen bitmap Index 
-- zu erzeugen, mit der wunderbar klaren Fehlermeldung "ORA-25122
-- Bei partitionierten Tabellen sind nur LOCAL-Bitmap-Indizes zulässig"
-- beantwortet wird.
create bitmap index list_part_bix_local on list_part (col1) local;

Und auch für diesen Fall will ich die erste Partition nachträglich komprimieren:

alter table list_part modify partition P201201 compress;
--> Tabelle wurde geändert.
alter table list_part move partition P201201;

FEHLER in Zeile 1:
ORA-14646: Der angegebene Vorgang zur Änderungen einer Tabelle
der eine Komprimierung umfasst, kann nicht ausgeführt werden,
wenn verwendbare Bitmap-Indizes vorhanden sind.

Na gut, dann mache ich die Index-Partition eben UNUSABLE:

alter index list_part_bix_local modify partition P201201 unusable;
--> Index wurde geändert.
alter table list_part move partition P201201;

FEHLER in Zeile 1:
ORA-14646: Der angegebene Vorgang zur Änderungen einer Tabelle
der eine Komprimierung umfasst, kann nicht ausgeführt werden,
wenn verwendbare Bitmap-Indizes vorhanden sind.

Das hilft also nicht. Tatsächlich muss in diesem Fall nicht nur die lokale Partition, sondern der gesamte Index UNUSABLE gesetzt und später wieder per REBUILD neu aufgebaut werden:

alter index list_part_bix_local unusable;
--> Index wurde geändert.
alter table list_part move partition P201201;
--> Tabelle wurde geändert.

alter index list_part_bix_local rebuild;
FEHLER in Zeile 1:
ORA-14086: Ein partitionierter Index kann nicht als ganzes neu erstellt werden

Immerhin kann man alle Indizes einer Partition mit dem Kommando

alter table ... modify partition ... rebuild unusable local indexes;

neu erzeugen, was ich gelegentlich schon mal erwähnt hatte.

Fehlt noch eine Antwort auf die Frage, ob es einen guten Grund für das unterschiedliche Verhalten gibt. Dazu ist zunächst zu sagen, dass die Oracle-Dokumentation den hier dargestellten Fall explizit erläutert. Dort heißt es:

This rebuilding of the bitmap index structures is necessary to accommodate the potentially higher number of rows stored for each data block with table compression enabled. Enabling table compression must be done only for the first time. All subsequent operations, whether they affect compressed or uncompressed partitions, or change the compression attribute, behave identically for uncompressed, partially compressed, or fully compressed partitioned tables.

To avoid the recreation of any bitmap index structure, Oracle recommends creating every partitioned table with at least one compressed partition whenever you plan to partially or fully compress the partitioned table in the future. This compressed partition can stay empty or even can be dropped after the partition table creation.

Having a partitioned table with compressed partitions can lead to slightly larger bitmap index structures for the uncompressed partitions. The bitmap index structures for the compressed partitions, however, are usually smaller than the appropriate bitmap index structure before table compression. This highly depends on the achieved compression rates.

Es scheint also doch einen guten Grund für das Verhalten zu geben. Ein Blick in Julian Dykes klassische Präsentation Bitmap Internals erklärt das Verhalten: dort wird auf Folie 37 erklärt, dass für die Komprimierung der bitmaps der Hakan Factor der Tabelle relevant ist. Den Hakan Factor findet man in TAB$:

select OBJ#
     , spare1 
  from sys.tab$
 where OBJ# = 105354;

-- vor dem MOVE der Partition
      OBJ#     SPARE1
---------- ----------
    105354        736

-- nach dem MOVE der Partition
      OBJ#     SPARE1
---------- ----------
    105354     163831

Der Hakan Factor ist demnach ein Attribut der kompletten Tabelle (die OBJECT_IDs der Partitionen findet man nicht in TAB$) und ändert sich erst nach dem Neuaufbau einer Partition - also nicht bereits mit dem MODIFY PARTITION ... COMPRESS, sondern erst mit dem MOVE, also der physikalischen Reorganisation. Das Verhalten ist somit begründet - und mehr wollte ich gar nicht wissen.

TNS Protocol Internals

Gwen Shapira hat im Pythian Blog Ian Redferns klassische Untersuchung Oracle Protocol reposted, die in den Tiefen des Netzes unterzugehen drohte. Darin findet man allerlei Grundlegendes zu den internen Mechanismen des TNS Protokolls. Interessant für den Fall, dass ich mal von meiner Linie abgehe, Netzwerkfragen als Problem anderer Leute zu klassifizieren.