Top-N Queries & Truncate Table Enhancement in Oracle 12c

Top-N Queries & Truncate Table Enhancement in Oracle 12c

In fact there is already various method exist to fetch TOP N query results in the previous release. In the oracle 12c retrieving TOP N query simplified and become easier with the new Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses, together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set so you can easily retrieve the result set.

The row limiting clause is simply added at the end of any SQL statement to fetch a specific set of records:

§         OFFSET provides a way to skip the N first rows in a result set before starting to return any rows

§         The FETCH clause limits the number of rows returned in the result set

§         For the result offset clause, the value of the integer literal must be equal to 0 (default if the clause is not given), or positive.

§         If it is larger than the number of rows in the underlying result set, no rows are returned.

§         For the fetch first clause, the value of the literal must be 1 or higher.

§         The literal can be omitted, in which case it defaults to 1. If the clause is omitted entirely, all rows (or those rows remaining if a result offset clause is also given) will be returned.

Suppose for Example: if you have employee table with huge set of records and you want to retrieve the first five records then you only need to FETCH FIRST N ROWS to the SQL query.

SQL> select owner, emp#, emp_name

from employee

order by owner, emp#

FETCH FIRST 5 ROWS ONLY;

Thus the row limiting clause is making it easier to do something easier than before. The above example returns the first five records. The use of ONLY clause limits the number of records exactly requested.

SQL> select owner, emp#, emp_name

from employee

order by owner, emp#

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

We can achieve the same result with above query. The use of OFFSET clause, will skip the first five rows and get the next five rows from a result set.

Important point while using OFFSET clause:

• If the offset is not specified it is assumed to be 0 and Negative values for the offset, row-count or percent are treated as 0.
• Null values for offset, row-count or percent result in no rows being returned.
• Fractional portions of offset, row-count or percent are truncated.
• If the offset is greater than or equal to the total number of rows in the set, no rows are returned.
• If the row-count or percent are greater than the total number of rows after the offset, all rows are returned.
• The row limiting clause can not be used with the FOR UPDATE clause, CURRVAL and NEXTVAL sequence pseudo-columns or in an fast refresh materialized view.

Truncate Table Cascade

In the previous release, there was not a direct option available to truncate a master table while child table exist and having records.

TRUNCATE <table-name> CASCADE;

Now the truncate table with cascade option in 12c truncates the records in master as well as all referenced child table with an enabled ON DELETE constraint. This enhancement override the previous truncate table option where truncating all child records before truncating the master records.

Create master/detail table:

CREATE TABLE example_master

(

EMP# NUMBER(4) PRIMARY KEY,

DEPT# NUMBER(10)

)

CREATE TABLE example_child

(

EMP# NUMBER(4) PRIMARY KEY,

DEPT# NUMBER(10)

);

Add reference constraint:

ALTER TABLE example_child

ADD CONSTRAINT exp_child_fk FOREIGN KEY (dept#)

REFERENCE example_master ON DELETE CASCADE;

Add some Records:

Insert into example_master values (101, 10001);

Insert into example_master values (102, 10002);

……

Insert into example_detail values (101, 10001);

Insert into example_detail values (102, 10002);

……

Select * from example_master;

       EMP#       DEPT#

-------------     ------------------

                  101                                    1001

                  102                                    1002

Select * from example_detail;

       EMP#       DEPT#

-------------     ------------------

                  103                                    1001

                  104                                    1002

SQL> TRUNCATE TABLE example_master CASCADE;

Now you will not find any records in master as well as detail table. This option works also for partitioned table but only on table level. If you want to use this option on partition or sub-partition level it works only for reference partitioning.