Introduction :- Oracle 12c has lots of characteristics In-Memory is one of the most important features. It enables tables, partitions, materialized views be stored in memory using column format, which delivers fast SQL processing for the Analytical purpose. Oracle Database In-Memory proffers an idiosyncratic dual-format architecture that legalizes tables to be concurrently represented in memory using standard row […]
Category: ORACLE 12C
Truncate table with cascade feature1 in oracle 12c
In oracle 12c TRUNCATE TABLE CASCADE will truncate its child tables, if ON DELETE CASCADE relationship is present on child table. Create one parent and child table with some data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
SQL> CREATE TABLE DBATEST ( EMPNO number, NAME varchar2(10) , empid number); Table created. SQL> alter table dbatest add constraint emp_pk primary key ( empid); Table altered. NOT NULL SQL> CREATE TABLE DBACHILD ( EMPID number, dept number); Table created. --- add the delete cascade keyword while adding foreign key constraint SQL> alter table dbachild add constraint child_fk foreign key ( EMPID ) REFERENCES dbatest(EMPID) on delete cascade; Table altered. SQL> insert into dbatest values ( 1,'RAJ',1); 1 row created. SQL> insert into dbatest values ( 1,'RAM',2); 1 row created. SQL> insert into dbatest values ( 1,'RAM',3); 1 row created. SQL> commit; Commit complete. SQL> insert into dbachild values ( 1,2); 1 row created. SQL> insert into dbachild values ( 2,2); 1 row created. SQL> commit; Commit complete. SQL> select count(*) from dbatest; COUNT(*) ---------- 3 SQL> select count(*) from dbachild; COUNT(*) ---------- 2 |
You can check the child table details using : Get child table details
|
1 2 3 |
OWNER CHILD_TABL STATUS FK_COLUMN CONSTRAINT_NAME ---------- ---------- -------- --------------- --------------------------- TEST DBACHILD ENABLED EMPID CHILD_FK |
Truncate the parent table with cascade option:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> truncate table dbatest cascade; Table truncated. SQL> select count(*) from dbatest; COUNT(*) ---------- 0 SQL> select count(*) from dbachild; COUNT(*) ---------- 0 |
We can […]
Row limiting clause in oracle 12c
Row limiting clause clause allows sql queries to limit the number of rows returned and to specify a starting row for the return set. 1. Fetch first N rows:
|
1 2 3 4 5 6 7 8 9 |
SQL> select * from test2 fetch first 5 rows only; OWNER OBJECT_NAME STATUS -------- --------------------- ------- SYS I_CCOL1 VALID SYS I_PROXY_ROLE_DATA$_1 VALID SYS C_OBJ# VALID SYS CON$ VALID SYS I_USER1 VALID |
2. Fetch 5 rows after offset of 4 rows
|
1 2 3 4 5 6 7 8 9 |
SQL> select * from test2 a OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY; 2 OWNER OBJECT_NAME STATUS -------- --------------------- ------- SYS I_USER1 VALID SYS COL$ VALID SYS PROXY_DATA$ VALID SYS I_CON2 VALID SYS I_UNDO1 VALID |
3. Fetch percentage of rows from a query:
|
1 2 |
SELECT * from test2 FETCH FIRST 1 PERCENT ROWS ONLY; |
Oracle12c Multitenant Architecture