This post is part 3 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive the series, please see part 1.
All the Series Parts
Published
Part 1 – write less configuration with SQL*Loader Express Mode
Part 2 – write less application code with Identity Columns
Part 3 – write less application code with In-Database Archiving
Part 4 – write less application code with Temporal Validity
Part 5 – write less code in SQL with Row Limiting
Part 6 – write less “inappropriately located” code with SELECT FROM Package-Level Collection Types
Part 7 – write less code in SQL with Lateral Inline Views
Part 8 – write less “inappropriately located” code with PL/SQL in the WITH Clause
Task #3
We don’t want to “really” delete obsolete PROJECTS records, as we may have PROJECT_ASSIGNMENTS records that reference them (via a foreign key constraint). We just want to “hide” them, or, as it is often called, “logically delete” them.
A Pre-12c Solution
We can add a column to the PROJECTS table – IS_DELETED – which contains either 0 (representing an active project) or 1 (an obsolete project):
ALTER TABLE projects ADD is_deleted NUMBER(1) DEFAULT 0 NOT NULL CHECK (is_deleted IN (0,1));
Now, since usually we want to hide the obsolete projects, we can rename the table to, say, ALL_PROJECTS, and create a view that exposes only the active records:
RENAME projects TO all_projects; CREATE VIEW projects AS SELECT * FROM all_projects WHERE is_deleted=0;
By naming the view PROJECTS, just as the table’s original name, we make all the existing references to PROJECTS throughout the application (or applications) see only active projects. Now we only need to handle the rare cases where obsolete projects should be seen, by using ALL_PROJECTS in the relevant SQL statements.
A 12c Solution
In-Database Archiving
In Oracle 12c tables can be defined as ROW ARCHIVAL. As a result, a hidden column – ORA_ARCHIVE_STATE – is implicitly added to the table, holding an archiving (“logical deletion”) state. The default value is ‘0’, representing the “Non-Archived” state, and any other value means “Archived”. Based on a session-level setting, “Archived” rows are either visible or not.
If you want to learn about many more new features of Oracle 12c, you may be interested in one of my in-house training sessions, particularly the Oracle 12c New Features for Developers full-day seminar.
In our case we can recreate the table like this:
drop table projects cascade constraints;
create table projects (
project_id integer not null constraint projects_pk primary key,
project_name varchar2(100) not null,
status number(1) not null,
last_days_to_show_in_reports integer not null
)
ROW ARCHIVAL;
When we DESCRIBE the table, we see only the columns we explicitly defined:
> desc projects Name Null? Type -------------------------------------------- -------- ------------------------------ PROJECT_ID NOT NULL NUMBER(38) PROJECT_NAME NOT NULL VARCHAR2(100) STATUS NOT NULL NUMBER(1) LAST_DAYS_TO_SHOW_IN_REPORTS NOT NULL NUMBER(38)
But if we select from USER_TAB_COLS we discover one more column – ORA_ARCHIVE_STATE:
> select column_name, data_type, column_id,
hidden_column, char_length, data_default
from user_tab_cols
where table_name = 'PROJECTS';
COLUMN_NAME DATA_TYPE COLUMN_ID HIDDEN_CO CHAR_LENGTH DATA_DEFAULT
---------------------------- ---------- ---------- --------- ----------- ------------
PROJECT_ID NUMBER 1 NO 0
PROJECT_NAME VARCHAR2 2 NO 100
STATUS NUMBER 3 NO 0
LAST_DAYS_TO_SHOW_IN_REPORTS NUMBER 4 NO 0
ORA_ARCHIVE_STATE VARCHAR2 YES 4000 0
Note: there is a major difference between the data dictionary views USER_TAB_COLUMNS and USER_TAB_COLS: while the former exposes only the explicitly defined columns, the latter includes also system-generated columns.
Let’s fill the table with some records, without specifying values to the hidden ORA_ARCHIVE_STATE column:
insert into projects values (1,'Project A',1,2); insert into projects values (2,'Project B',2,3); insert into projects values (3,'Project C',1,4); insert into projects values (4,'Project D',2,3); commit;
Hidden columns are quite shy. By default, they are not shown:
> select * from projects;
LAST_DAYS_TO
PROJECT_ID PROJECT_NAME STATUS SHOW_IN_REPORTS
---------- ------------ ---------- ---------------
1 Project A 1 2
2 Project B 2 3
3 Project C 1 4
4 Project D 2 3
But if we explicitly select ORA_ARCHIVE_STATE we can see its value, which is, at this point, the default – ‘0’:
> select p.*,ORA_ARCHIVE_STATE from projects p; LAST_DAYS_TO PROJECT_ID PROJECT_NAME STATUS SHOW_IN_REPORTS ORA_ARCHIVE_STATE ---------- ------------ ---------- --------------- ----------------- 1 Project A 1 2 0 2 Project B 2 3 0 3 Project C 1 4 0 4 Project D 2 3 0
Now, let’s “delete” two records. Not a “real” deletion, only a logical one. We do it by updating ORA_ARCHIVE_STATE to ‘1’ (or any other non-zero value):
> update projects set ORA_ARCHIVE_STATE='1' where project_id in (1,3);
2 rows updated.
And now, if we select again from the table, with no (explicit) condition, we’ll see only the “remaining” two records. Remember that we didn’t actually DELETE any records, and still:
> select * from projects;
LAST_DAYS_TO
PROJECT_ID PROJECT_NAME STATUS SHOW_IN_REPORTS
---------- ------------ ---------- ---------------
2 Project B 2 3
4 Project D 2 3
How did it happen? Let’s check the execution plan:
> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ SQL_ID dcthaywgmzra7, child number 1 ------------------------------------- select * from projects Plan hash value: 2188942312 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| PROJECTS | 4 | 8372 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("PROJECTS"."ORA_ARCHIVE_STATE"='0')
And here it is. We can see that Oracle added a hidden condition: ORA_ARCHIVE_STATE=’0′.
ROW ARCHIVAL VISIBILITY
We saw the default behavior. If we want to see all the records, including the archived ones, we should change the ROW ARCHIVAL VISIBILITY setting of the session to ALL:
alter session set ROW ARCHIVAL VISIBILITY = ALL;
If we repeat the query now, we’ll see all the records of the table. Oracle will not add the behind-the-scenes condition on ORA_ARCHIVE_STATE this time:
> select p.*,
case ora_archive_state when '0' then 'Active' else 'Deleted' end is_active
from projects p;
LAST_DAYS_TO
PROJECT_ID PROJECT_NAME STATUS SHOW_IN_REPORTS IS_ACTIVE
---------- ------------ ---------- --------------- ---------------------
1 Project A 1 2 Deleted
2 Project B 2 3 Active
3 Project C 1 4 Deleted
4 Project D 2 3 Active
> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID bwkf66qbwkaqt, child number 0
-------------------------------------
select p.*, case ora_archive_state when '0' then 'Active' else
'Deleted' end is_active from projects p
Plan hash value: 2188942312
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| PROJECTS | 4 | 8372 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
To get back to the default behavior we should change the ROW ARCHIVAL VISIBILITY setting of the session to its default value – ACTIVE:
alter session set ROW ARCHIVAL VISIBILITY = ACTIVE; > select * from projects; LAST_DAYS_TO PROJECT_ID PROJECT_NAME STATUS SHOW_IN_REPORTS ---------- ------------ ---------- --------------- 2 Project B 2 3 4 Project D 2 3
The Bad News
It seems that In-Database Archiving is not supported in PL/SQL at the moment (12.1.0.2).
> var rc refcursor
> begin
open :rc for select p.*,ora_archive_state from projects p;
end;
/
PL/SQL procedure successfully completed.
> print rc
LAST_DAYS_TO
PROJECT_ID PROJECT_NAME STATUS SHOW_IN_REPORTS ORA_ARCHIVE_STATE
---------- ------------ ---------- --------------- -----------------
1 Project A 1 2 1
2 Project B 2 3 0
3 Project C 1 4 1
4 Project D 2 3 0
Update (20-Jun-2017): this bug has been fixed in the one-off patch 23080557, in patchset 12.1.0.2.170418 for Windows, and is marked as fixed in the future (as of now) version
12.2.0.218.1
There are two sides to every coin
We’ve just seen that In-Database Archiving adds hidden columns to tables and hidden predicates to SQL statements. This is cool, but also dangerous. When things are hidden, it is easy to forget them, but we shouldn’t.
For example, suppose that:
- there are many records in the PROJECTS table
- most of them are obsolete (i.e., their ORA_ARCHIVE_STATE != ‘0’)
- ROW ARCHIVAL VISIBILITY = ACTIVE (the default setting)
Since Oracle adds the condition ORA_ARCHIVE_STATE = ‘0’ to every query, and this filters most of the records out, we may want to create an index on ORA_ARCHIVE_STATE, or add ORA_ARCHIVE_STATE as an additional column to other indexes.
Why VARCHAR2(4000)?
As we saw, the column ORA_ARCHIVE_STATE is created as VARCHAR2(4000), which seems a bit extreme for storing Boolean values – representing that the row is either Active or Deleted. Since the condition that Oracle adds to filter “deleted” rows out is ORA_ARCHIVE_STATE = ‘0’, we can use any string other than ‘0’ to represent deleted rows. One benefit it gives is that we can specify some information regarding the deletion of each record (like the “deletion reason”). Still, I would prefer a NUMBER(1) column rather than VARCHAR2(4000).
This can also limit our ability to add ORA_ARCHIVE_STATE to indexes, as the maximum key length of indexes is limited. For example, suppose that the PROJECT_NAME column is defined as VARCHAR2(4000), and that we want to create a composite index on PROJECT_NAME and ORA_ARCHIVE_STATE. Using a standard block size of 8KB, the index creation will fail:
> alter table projects modify (project_name varchar2(4000));
Table altered.
> create index projects_i1 on projects (project_name,ora_archive_state);
create index projects_i1 on projects (project_name,ora_archive_state)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
Conclusion
We saw in this part of the Write Less with More series that In-Database Archiving allows us to write less application code.
The next post will be about another new Oracle 12c feature – Temporal Validity.