FBDA -- 3 : Support for TRUNCATEs

One of the points in the previous post was how ADD and DROP Column commands are supported in FBDA.

Let's look at support for TRUNCATEs.

(Just to prove that I am not using the Undo tablespace for the AS OF query, I drop the Undo tablespace) :

SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS1
SQL> show parameter spfile

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
spfile         string  +DATA/NONCDB/PARAMETERFILE/spf
       ile.267.896483727
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS1
SQL> create undo tablespace undotbs2;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS2';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2
SQL> 

I now TRUNCATE my test table and then run an AS OF query against it.

SQL> connect hemant/hemant
Connected.
SQL> select count(*) from test_fbda;

  COUNT(*)
----------
      1000

SQL> truncate table test_fbda;

Table truncated.

SQL> select count(*) from test_fbda;

  COUNT(*)
----------
  0

SQL> select count(*) from test_fbda as of timestamp (sysdate-1);

  COUNT(*)
----------
      1000

SQL> 

The AS OF query was satisfied by the FBDA History table  (and not from Undo).

However, because of yesterday's test of ADD and DROP columns (see yesterday's post), the table structure doesn't match AS OF yesterday.  So, I work around the difference by naming the columns.

SQL> insert into test_fbda select * from test_fbda as of timestamp (sysdate-1);
insert into test_fbda select * from test_fbda as of timestamp (sysdate-1)
            *
ERROR at line 1:
ORA-00913: too many values


SQL> 
SQL> desc test_fbda
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE

SQL> insert into test_fbda
  2  select id_column, data_column, date_inserted
  3  from test_fbda as of timestamp (sysdate-1);

1000 rows created.

SQL> commit;

Commit complete.

SQL>          

Thus, when my query matches the table structure, I can use the data from the previous version of the query.

Note how I did NOT use the SYS_FBA_HIST_93250 table as I'd shown in my previous posts.  The History table is NOT to be accessed directly but I demonstrated queries against it it to show the underlying architecture in FBDA.
.
.
.