In Oracle 12c new word CASCADE can be specified for TRUNCATE command.
TRUNCATE <table_name> CASCADE
If you specify CASCADE, then Oracle Database truncates all child tables that reference TABLE with an enabled ON DELETE referential constraint. This is a recursive operation that will truncate all child tables, grandchild tables, and so on, using the specified options.
This article presents how to execute DML operations on complex views using INSTEAD OF triggers that otherwise could not be done.
Update/Insert/Delete via view is possible in Oracle.
create table test_tbl ( id number, id1 number, id2 number ); create view test_vw as select * from test_tbl;
INSERT, UPDATE, DELETE
insert into test_vw values(1,1,1); insert into test_vw values(2,2,2); insert into test_vw values(3,3,3); commit; update test_vw set id2=4 where id1=3; commit; delete from test_vw where id=2; commit; select * from test_vw; ID ID1 ID2 ---------- ---------- ---------- 1 1 1 3 3 4
but with many exceptions
The view must not contain any of the following constructs:
and many many others – more you can read in Oracle documentation. If view violates one of above rule then it’s called complex view.
Autonomous transaction – is an independent transaction called from another transaction which is the main transaction.
Features of autonomous transaction
Special pragma AUTONOMOUS_TRANSACTION is introduced in PL/SQL. The pragma instructs the database that executed code is to be executed as new autonomous transaction independent from main transaction.
Partial indexing enables to create local and global indexes on a subset of partitions, subpartitions of a table. When a table is created or altered, a default indexing property can be specified for the table or its partitions.
New attribute INDEXING ON|OFF can be specified on table(it becomes default attribute for its partitions and subpartitions), partition(it becomes default attribute for its subpartitions) or subpartition level. The attribute is important only for partial indexing.
New attribute INDEXING PARTIAL|FULL can be specified during index creation
This article presents enhancements for maintenance operations on multiple partitions in Oracle 12C.
Following multiple operations using just one single command are now possible in Oracle 12C:
This feature is beautiful extension I really love it 🙂
Sometimes creation of an index can take a lot of time. It’s especially true for warehouses. In Oracle there is nice old trick to create definition of index in dictionary without allocating physical space. It can help you to quickly determine what columns should be indexed.
Virtual index – it’s dictionary definition of index which is not allocating physical space .
It’s fantastic new feature in 12C. In this release, a data file can now be moved online while it is open and being accessed.
alter database move datafile '<file_to_move_old_location>' to '<file_to_move_new_location';
Example of potential use of it:
In Oracle 12C you can create multiple indexes on the same set of columns as long as some characteristic is different like:
It’s very helpful feature if you want to quickly migrate to different type of index or use them during different period of time.
NOTE – to create such indexes you need to use INVISIBLE clause.
Article presents quick installation of Oracle Enterprise RAC 12.1.0.1 on Windows 2008 using Oracle Virtual Box.
Architecture
Software used:
UNION or UNION ALL consists of many queries(branches) that in pre 12C releases were executed one by one.
<BRANCH1> UNION <BRANCH2> <BRANCH1> UNION ALL <BRANCH2>
So in pre 12C first is processed BRANCH1 then BRANCH2. Of course each individual query(branch) can be processed in serial or in parallel but only one branch at time.
Oracle 12C allows to run branches(statement) of UNION or UNION ALL concurrently. It means that BRANCH2 can be processed together with BRANCH1 :). It can even return data faster than BRANCH1.
This feature is turned on automatically and entire UNION or UNION ALL is processed in parallel if: