Redo-Generierung für Indizes

Eigentlich hatte ich angenommen, die Regeln der nologging-clause einigermaßen genau zu kennen. Das war aber wohl ein Irrtum. Zu meiner Überraschung ist es nämlich offenbar nicht möglich, die redo-Generierung für Indizes bei direct-path-Inserts zu vermeiden. Hier noch mal die Details zum Mitschreiben:

• im NOARCHIVELOG-Modus erzeugt ein INSERT /*+ append */ nur minimale redo Informationen
• allerdings werden redo-Informationen für zugehörige Indizes erzeugt
• dies geschieht auch dann, wenn der Index in den nologging Modus gesetzt wurde
• nologging spielt für Indizes nur bei rebuild-Operationen eine Rolle

Damit diese Aussagen nicht reine Behauptungen bleiben, hier die zugehörigen Tests:

create table test as select * from dba_objects where 1 = 0;

Tabelle wurde erstellt.

insert into test select * from dba_objects;

54496 Zeilen wurden erstellt.

--> 5982892  redo size

-- nach rollback:
insert /*+ append */ into test select * from dba_objects;

54496 Zeilen wurden erstellt.

--> 2328  redo size

--> dabei spielt das logging-Attribut der Tabelle im vorliegenden NOARCHIVELOG-Modus keine Rolle

-- nach rollback:
create index test_idx1 on test(object_name);

Index wurde erstellt.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 3799568  redo size

alter index test_idx1 nologging;

Index wurde geändert.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 3799568  redo size

--> der nologging-Modus des Index spielt im Fall des INSERTs keine Rolle
--> Änderungen am Index werden immer geloggt
--> für größere Änderungsoperationen ist demnach ein kompletter Neuaufbau des Index vorzuziehen

-- nach rollback:
alter index test_idx1 unusable;

Index wurde geändert.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 1888  redo size

alter index test_idx1 rebuild;

Index wurde geändert.

--> 60180 redo size

--> minimale redo Generierung für das rebuild eines Index im nologging-Modus

Advanced Query Rewrite

während alle ernsthaften Blogger sich mit Features aus Version 11 beschäftigen, entdecke ich immer noch hübsche Dinge in 10g. So z.B. das Package DBMS_ADVANCED_REWRITE, das es ermöglicht, eine abgesetzte Query intern durch ein anderes Statement zu ersetzen. Einerseits kann man damit natürlich ungeheuren Blödsinn anstellen, andererseits eröffnet dieses Package ziemlich umfangreiche Möglichkeiten für die Definition einer intelligenten Rewrite-Logik im DataWarehouse-Bereich. Hier ein ganz simples Syntax-Beispiel:

-- als sys:
SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO kettle;

Benutzerzugriff (Grant) wurde erteilt.

-- als kettle:
SQL> create table test_rewrite(a varchar2(64));

Tabelle wurde erstellt.

SQL> insert into test_rewrite(a)
2 values ('aus der Tabelle test_rewrite');

1 Zeile wurde erstellt.

SQL> commit;

SQL> begin
2    SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence
3        (name             => 'test_rewrite',
4         source_stmt      => 'SELECT * FROM test_rewrite',
5         destination_stmt => 'SELECT ''aus dual'' from dual',
6         validate         => FALSE);
7    end;
8    /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT * FROM test_rewrite;

A
------------------------------------
aus der Tabelle test_rewrite

-- das System muss erst davon überzeugt werden, dass unser Rewrite vertrauenswürdig ist
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session wurde geändert.

SQL> SELECT * FROM test_rewrite;

A
--------
aus dual

SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence 
2         (name => 'test_rewrite');

PL/SQL-Prozedur erfolgreich abgeschlossen.

Execution Plan

es wird behauptet, manchmal sei es besser nichts zu schreiben als nicht zu schreiben, daher hier ein paar Hinweise, die man an ca. 10000 anderen Orten finden kann:

In 10g lassen sich Zugriffspläne sehr komfortabel über das dbms_xplan-Package darstellen, also z.B.:

SQL> r
1  SELECT plan_table_output
2*   FROM TABLE( dbms_xplan.display_cursor ( 'a5ks9fhw2v9s1', 0))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 Zeilen ausgewählt.

Dabei ist der erste Parameter die sql_id und der zweite cursor_child_no (beide findet man in v$sql). Wenn ich jetzt nichts durcheinander werfe, ist der Formatierungsmechanismus der gleiche der bei autotrace verwendet wird.