Flashback-Query

Ein ganz simples Beispiel dieser praktischen Funktionalität, mit deren Hilfe man sich ansehen kann, wie eine Tabelle zu einem bestimmten Zeitpunkt in der Vergangenheit gefüllt war:

-- Anlage der Tabelle test1, die mit fünf Sätze gefüllt wird:
create table test1 (a number);

insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);
insert into test1 values (4);
insert into test1 values (5);

commit;

-- ein Blick auf die Uhr:
select to_char(sysdate, 'hh24:mi:ss') from dual;

TO_CHAR(
--------
10:30:29

-- und auf den Inhalt der Tabelle:
select * from test1;

A
----------
1
2
3
4
5

5 Zeilen ausgewählt.

-- jetzt löschen wir drei Zeilen:
delete from test1 where a > 2;

3 Zeilen wurden gelöscht.

commit;

-- 5 - 3 = 2
select * from test1;

A
----------
1
2

2 Zeilen ausgewählt.

-- mit Hilfe der flashback query können wir sehen, was vor der Löschung in der Tabelle enthalten war
SELECT *
  FROM Test1
  AS OF TIMESTAMP ( TO_TIMESTAMP( '30.05.07 10:30:30', 'DD.MM.YY HH24:MI:SS') )

A
----------
1
2
3
4
5

5 Zeilen ausgewählt.

Quizfrage

Bei Dominic Delmolino (http://www.oraclemusings.com/?p=57) gab's kürzlich eine interessante SQL-Aufgabe. Hier meine (nicht besonders hübsche) Lösung dazu:

SQL> r
select case when lag_col is null then min_col
when substr(min_col, 1, 1) <> substr(lag_col, 1, 1)
then substr(min_col, 1, 1)
when substr(min_col, 1, 1) = substr(lag_col, 1, 1)
and substr(min_col, 1, 2) <> substr(lag_col, 1, 2) then substr(min_col, 1, 2)
when substr(min_col, 1, 2) = substr(lag_col, 1, 2)
and substr(min_col, 1, 3) <> substr(lag_col, 1, 3) then substr(min_col, 1, 3)
when substr(min_col, 1, 3) = substr(lag_col, 1, 3)
and substr(min_col, 1, 4) <> substr(lag_col, 1, 4) then substr(min_col, 1, 4)
when substr(min_col, 1, 4) = substr(lag_col, 1, 4)
and substr(min_col, 1, 5) <> substr(lag_col, 1, 5) then substr(min_col, 1, 5)
when substr(min_col, 1, 5) = substr(lag_col, 1, 5)
and substr(min_col, 1, 6) <> substr(lag_col, 1, 6) then substr(min_col, 1, 6)
when substr(min_col, 1, 6) = substr(lag_col, 1, 6)
and substr(min_col, 1, 7) <> substr(lag_col, 1, 7) then substr(min_col, 1, 7)
when substr(min_col, 1, 7) = substr(lag_col, 1, 7)
and substr(min_col, 1, 8) <> substr(lag_col, 1, 8) then substr(min_col, 1, 8)
when substr(min_col, 1, 8) = substr(lag_col, 1, 8)
and substr(min_col, 1, 9) <> substr(lag_col, 1, 9) then substr(min_col, 1, 9)
when substr(min_col, 1, 9) = substr(lag_col, 1, 9)
and substr(min_col, 1, 10) <> substr(lag_col, 1, 10) then substr(min_col, 1, 10)
when substr(min_col, 1, 10) = substr(lag_col, 1, 10)
and substr(min_col, 1, 11) <> substr(lag_col, 1, 11) then substr(min_col, 1, 11)
when substr(min_col, 1, 11) = substr(lag_col, 1, 11)
and substr(min_col, 1, 12) <> substr(lag_col, 1, 12) then substr(min_col, 1, 12)
else null end
|| ' thru ' ||
case when lead_col is null then max_col
when substr(max_col, 1, 1) <> substr(lead_col, 1, 1)
then substr(max_col, 1, 1)
when substr(max_col, 1, 1) = substr(lead_col, 1, 1)
and substr(max_col, 1, 2) <> substr(lead_col, 1, 2) then substr(max_col, 1, 2)
when substr(max_col, 1, 2) = substr(lead_col, 1, 2)
and substr(max_col, 1, 3) <> substr(lead_col, 1, 3) then substr(max_col, 1, 3)
when substr(max_col, 1, 3) = substr(lead_col, 1, 3)
and substr(max_col, 1, 4) <> substr(lead_col, 1, 4) then substr(max_col, 1, 4)
when substr(max_col, 1, 4) = substr(lead_col, 1, 4)
and substr(max_col, 1, 5) <> substr(lead_col, 1, 5) then substr(max_col, 1, 5)
when substr(max_col, 1, 5) = substr(lead_col, 1, 5)
and substr(max_col, 1, 6) <> substr(lead_col, 1, 6) then substr(max_col, 1, 6)
when substr(max_col, 1, 6) = substr(lead_col, 1, 6)
and substr(max_col, 1, 7) <> substr(lead_col, 1, 7) then substr(max_col, 1, 7)
when substr(max_col, 1, 7) = substr(lead_col, 1, 7)
and substr(max_col, 1, 8) <> substr(lead_col, 1, 8) then substr(max_col, 1, 8)
when substr(max_col, 1, 8) = substr(lead_col, 1, 8)
and substr(max_col, 1, 9) <> substr(lead_col, 1, 9) then substr(max_col, 1, 9)
when substr(max_col, 1, 9) = substr(lead_col, 1, 9)
and substr(max_col, 1, 10) <> substr(lead_col, 1, 10) then substr(max_col, 1, 10)
when substr(max_col, 1, 10) = substr(lead_col, 1, 10)
and substr(max_col, 1, 11) <> substr(lead_col, 1, 11) then substr(max_col, 1, 11)
when substr(max_col, 1, 11) = substr(lead_col, 1, 11)
and substr(max_col, 1, 12) <> substr(lead_col, 1, 12) then substr(max_col, 1, 12)
else null end spine, min_col, max_col
from (select ntile_range,
min_col,
max_col,
lead( min_col) over (order by ntile_range) lead_col,
lag( max_col) over (order by ntile_range) lag_col
from (select ntile_range,
min(column_name) min_col,
max(column_name) max_col
from (select column_name,
ntile(15) over(order by column_name) ntile_range
from ac1
)
group by ntile_range order by ntile_range
)
) t

SPINE MIN_COL MAX_COL
------------------------- ------------------------------ ------------------------------
A thru BITMAP A BITMAP
BITMAPP thru C_ BITMAPPED C_OBJ#
C1 thru De C1 Default
D1 thru ERR_ D1 ERR_NUM
ERRO thru GENL ERRORS GENLINKS
GENO thru I_ GENOPTION I_AGREE
IN thru LOB_ INTRO LOB_COL_NAME
LOBI thru MV_QUA LOBINDEX MV_QUANTITY_SUM
MV_QUE thru Op MV_QUERY_GEN_MISMATCH Option
OS thru P_ OSHST P_REF_TIME
P1 thru R_ P1 R_CONSTRAINT_NAME
R1 thru SHORT_WAITS R1 SHORT_WAITS
SHORT_WAIT_ thru Si SHORT_WAIT_TIME_MAX Size
SU thru T_ SUMGROSSTURNOVER T_PER_EXEC
T1 thru ZERO_RESULTS T1OBJID ZERO_RESULTS

15 Zeilen ausgewählt.

case insensitive Suche unter Oracle

Bei Tom Kyte (wo sonst?) findet man folgende hübsche Möglichtkeit, in 10g case eine insensitive Konditionsprüfung zu ermöglichen. Relevant wäre diese Option beispielsweise dann, wenn man die Statements einer Applikation nicht ändern - und die Prüfung z.B. nicht mit einem UPPER beeinflussen - kann.

create table t ( data varchar2(20) );

insert into t values ( 'Hello' );
insert into t values ( 'HeLlO' );
insert into t values ( 'HELLO' );

-- wie zu erwarten liefert die folgende Query
-- zunächst kein Ergebnis
select * 
  from t
 where data = 'hello'

 Es wurden keine Zeilen ausgewählt

alter session set nls_comp=ansi;

Session wurde geändert.

alter session set nls_sort=binary_ci;

Session wurde geändert.

-- Nach Anpassung der beiden NLS-Parameter 
-- wird die Bedingung case insensitive behandelt
select * 
  from t
 where data = 'hello'

DATA
--------------------
Hello
HeLlO
HELLO

3 Zeilen ausgewählt.

set autot trace
select * 
  from t
 where data = 'hello'

3 Zeilen ausgewählt.

Ausführungsplan
--------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 |
--------------------------------------------------

-- durch die Anlage eines function based index kann man den
-- Zugriff dann auch noch optimieren.
create index t_idx on t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );

select * 
  from t
 where data = 'hello';

3 Zeilen ausgewählt.

Ausführungsplan
-----------------------------------------------------
Plan hash value: 470836197
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 |
-----------------------------------------------------

Zu Prüfen wäre in einem solchen Fall nur, welche weiteren Auswirkungen die Änderung der NLS-Parameter auf eine Applikation haben könnte. Zusätzliche Details zum Thema findet man im AskTom-Thread.