Friday, January 2, 2015

Oracle 12C #7 : Oracle 12c In-Memory Database - Part 1

Once I heard the term " In-Memory Database" , i thought its new appliance like exadata...
Its not true..

What is In-Memory database?

All the data can be read or manipulated from memory only. This is true for all oracle database.
In most of the well tuned oracle systems  >95% request, data can be retrieved from memory and few request going to disk. In this case how In-memory database going to benefit for business?

In data world, majority of the system classified as OLTP and another one is decision support system. Customer always necessary to maintain both type of system for their business requirements.

Oracle In Memory database accelerates both DSS and mixed workload OLTP databases.
No more required 2 separate systems.
Easily deployed under any existing application which is running on oracle  and no application changes are required.


Benefits:

  • Supports both OLTP and DSS in single system (reduce the cost for maintenance/support)
  • Achieve the extreme performance of database any size( Data can be in column format )
  • Real time answers for OLTP and smarter decision for analytics.
  • Not required any changes on application end.
  • Supports the existing hardware (Additional Memory required on database server)
  • Compatible with 12c (12.1.0.2)
  • In-Memory column store is a separately licensed option of Oracle Database Enterprise Edition.

Lets discuss more architecture details in an another post soon



Thursday, January 1, 2015

Oracle 12C #6 - DBMS_UTILITY.EXPAND_SQL_TEXT in Oracle 12C


DBMS_UTILITY.EXPAND_SQL_TEXT is oracle 12C feature.

This utility is helps to expand the SQL text with the corresponding view sub query.
Also it is also very useful to see how oracle internally rewrites SQL queries or translate views into query for even simple query...


SQL> create or replace view test_view as (select * from user_objects where object_type='TABLE');

View created.


SQL>

SET SERVEROUTPUT ON

DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'SELECT * FROM test_view',
    output_sql_text => l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/

==

SELECT "A1"."OBJECT_NAME" "OBJECT_NAME","A1"."SUBOBJECT_NAME"
"SUBOBJECT_NAME","A1"."OBJECT_ID" "OBJECT_ID","A1"."DATA_OBJECT_ID"
"DATA_OBJECT_ID","A1"."OBJECT_TYPE" "OBJECT_TYPE","A1"."CREATED"
"CREATED","A1"."LAST_DDL_TIME" "LAST_DDL_TIME","A1"."TIMESTAMP"
"TIMESTAMP","A1"."STATUS" "STATUS","A1"."TEMPORARY" "TEMPORARY","A1"."GENERATED"
"GENERATED","A1"."SECONDARY" "SECONDARY","A1"."NAMESPACE"
"NAMESPACE","A1"."EDITION_NAME" "EDITION_NAME","A1"."SHARING"
"SHARING","A1"."EDITIONABLE" "EDITIONABLE","A1"."ORACLE_MAINTAINED"
"ORACLE_MAINTAINED" FROM  (SELECT "A2"."OBJECT_NAME"
"OBJECT_NAME","A2"."SUBOBJECT_NAME" "SUBOBJECT_NAME","A2"."OBJECT_ID"
"OBJECT_ID","A2"."DATA_OBJECT_ID" "DATA_OBJECT_ID","A2"."OBJECT_TYPE"
"OBJECT_TYPE","A2"."CREATED" "CREATED","A2"."LAST_DDL_TIME"
"LAST_DDL_TIME","A2"."TIMESTAMP" "TIMESTAMP","A2"."STATUS"
"STATUS","A2"."TEMPORARY" "TEMPORARY","A2"."GENERATED"
"GENERATED","A2"."SECONDARY" "SECONDARY","A2"."NAMESPACE"
"NAMESPACE","A2"."EDITION_NAME" "EDITION_NAME","A2"."SHARING"
"SHARING","A2"."EDITIONABLE" "EDITIONABLE","A2"."ORACLE_MAINTAINED"
"ORACLE_MAINTAINED" FROM  ( (SELECT "A5"."NAME" "OBJECT_NAME","A5"."SUBNAME"
"SUBOBJECT_NAME","A5"."OBJ#" "OBJECT_ID","A5"."DATAOBJ#"
"DATA_OBJECT_ID",DECODE("A5"."TYPE#",0,'NEXT
OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PRO
CEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
SOURCE',29,'JAVA CLASS',30,'JAVA
RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX
SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE
EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "A7" WHERE
"A7"."OBJ#"="A5"."OBJ#" AND
BITAND("A7"."XPFLAGS",8388608)=8388608),'MATERIALIZED
VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION',114,'SQL
TRANSLATION PROFILE',115,'UNIFIED AUDIT POLICY','UNDEFINED')
"OBJECT_TYPE","A5"."CTIME" "CREATED","A5"."MTIME"
"LAST_DDL_TIME",TO_CHAR("A5"."STIME",'YYYY-MM-DD:HH24:MI:SS')
"TIMESTAMP",DECODE("A5"."STATUS",0,'N/A',1,'VALID','INVALID')
"STATUS",DECODE(BITAND("A5"."FLAGS",2),0,'N',2,'Y','N')
"TEMPORARY",DECODE(BITAND("A5"."FLAGS",4),0,'N',4,'Y','N')
"GENERATED",DECODE(BITAND("A5"."FLAGS",16),0,'N',16,'Y','N')
"SECONDARY","A5"."NAMESPACE" "NAMESPACE","A5"."DEFINING_EDITION"
"EDITION_NAME",DECODE(BITAND("A5"."FLAGS",196608),65536,'METADATA
LINK',131072,'OBJECT LINK','NONE') "SHARING",CASE  WHEN ("A5"."TYPE#"=4 OR
"A5"."TYPE#"=5 OR "A5"."TYPE#"=7 OR "A5"."TYPE#"=8 OR "A5"."TYPE#"=9 OR
"A5"."TYPE#"=11 OR "A5"."TYPE#"=12 OR "A5"."TYPE#"=13 OR "A5"."TYPE#"=14 OR
"A5"."TYPE#"=22 OR "A5"."TYPE#"=87 OR "A5"."TYPE#"=114) THEN
DECODE(BITAND("A5"."FLAGS",1048576),0,'Y',1048576,'N','Y') ELSE NULL END
"EDITIONABLE",DECODE(BITAND("A5"."FLAGS",4194304),4194304,'Y','N')
"ORACLE_MAINTAINED" FROM  (SELECT "A9"."OBJ#" "OBJ#","A9"."DATAOBJ#"
"DATAOBJ#","A9"."NAME" "NAME","A9"."NAMESPACE" "NAMESPACE","A9"."SUBNAME"
"SUBNAME","A9"."TYPE#" "TYPE#","A9"."CTIME" "CTIME","A9"."MTIME"
"MTIME","A9"."STIME" "STIME","A9"."STATUS" "STATUS","A9"."LINKNAME"
"LINKNAME","A9"."FLAGS" "FLAGS","A9"."SPARE3" "OWNER#",CASE  WHEN ( NOT EXISTS
(SELECT 0 FROM SYS."USER_EDITIONING$" "A15" WHERE "A9"."TYPE#"="A15"."TYPE#" AND
"A15"."USER#"="A9"."SPARE3") OR BITAND("A9"."FLAGS",1048576)=1048576 OR
BITAND("A8"."SPARE1",16)=0) THEN NULL WHEN "A8"."TYPE#"=2 THEN  (SELECT
"A14"."NAME" "NAME" FROM SYS."OBJ$" "A14" WHERE "A14"."OBJ#"="A8"."SPARE2") ELSE
'ORA$BASE' END  "DEFINING_EDITION" FROM SYS."OBJ$" "A9",SYS."USER$" "A8" WHERE
"A9"."OWNER#"="A8"."USER#" AND ( NOT EXISTS (SELECT 0 FROM
SYS."USER_EDITIONING$" "A13" WHERE "A9"."TYPE#"="A13"."TYPE#" AND
"A13"."USER#"="A9"."SPARE3") AND "A9"."TYPE#"<>88 OR
BITAND("A9"."FLAGS",1048576)=1048576 OR BITAND("A8"."SPARE1",16)=0 OR  EXISTS
(SELECT 0 FROM SYS."USER_EDITIONING$" "A12" WHERE "A9"."TYPE#"="A12"."TYPE#" AND
"A12"."USER#"="A9"."SPARE3") AND ("A8"."TYPE#"<>2 AND
SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "A8"."TYPE#"=2 AND
"A8"."SPARE2"=SYS_CONTEXT('userenv','current_edition_id') OR  EXISTS (SELECT 0
FROM SYS."OBJ$" "A11",SYS."USER$" "A10" WHERE "A11"."TYPE#"=88 AND
"A11"."DATAOBJ#"="A9"."OBJ#" AND "A11"."OWNER#"="A10"."USER#" AND
"A10"."TYPE#"=2 AND
"A10"."SPARE2"=SYS_CONTEXT('userenv','current_edition_id'))))) "A5" WHERE
"A5"."OWNER#"=USERENV('SCHEMAID') AND "A5"."LINKNAME" IS NULL AND
("A5"."TYPE#"<>1 AND "A5"."TYPE#"<>10 OR "A5"."TYPE#"=1 AND 1= (SELECT 1 "1"
FROM "SYS"."IND$" "A6" WHERE "A6"."OBJ#"="A5"."OBJ#" AND ("A6"."TYPE#"=1 OR
"A6"."TYPE#"=2 OR "A6"."TYPE#"=3 OR "A6"."TYPE#"=4 OR "A6"."TYPE#"=6 OR
"A6"."TYPE#"=7 OR "A6"."TYPE#"=8 OR "A6"."TYPE#"=9))) AND
"A5"."NAME"<>'_NEXT_OBJECT' AND "A5"."NAME"<>'_default_auditing_options_' AND
BITAND("A5"."FLAGS",128)=0) UNION ALL  (SELECT "A4"."NAME" "OBJECT_NAME",NULL
"SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL)
"DATA_OBJECT_ID",'DATABASE LINK' "OBJECT_TYPE","A4"."CTIME"
"CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",'VALID' "STATUS",'N'
"TEMPORARY",'N' "GENERATED",'N' "SECONDARY",NULL "NAMESPACE",NULL
"EDITION_NAME",'NONE' "SHARING",NULL "EDITIONABLE",'N' "ORACLE_MAINTAINED" FROM
"SYS"."LINK$" "A4" WHERE "A4"."OWNER#"=USERENV('SCHEMAID'))) "A2" WHERE
"A2"."OBJECT_TYPE"='TABLE') "A1"

PL/SQL procedure successfully completed.

Oracle 12C #5 - Invisible columns in Oracle 12C



Oracle 12c provides to make invisible the column in a table..
When a columns is defined as invisible, the column data won't appear in your SQL Query unless until if you mention explicitly on your SQL.

SQL> conn rb/RB

Connected.
SQL> create table RB_TEST
  2  (t1 number,
  3  t2 number,
  4  t3 number Invisible,
  5  t4  number);

Table created.

SQL> desc RB_TEST
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
T1                                                 NUMBER
T2                                                 NUMBER
T4                                                 NUMBER

SQL*Plus can optionally show hidden columns by setting the COLINVISIBLE option
SQL> SET COLINVISIBLE ON

SQL> desc RB_TEST
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
T1                                                 NUMBER
T2                                                 NUMBER
T4                                                 NUMBER
T3 (INVISIBLE)                                     NUMBER

I just trying to insert values for all columns including Invisible column and its throwing error because t3 column already invisible.

SQL> insert into RB_TEST values (1,2,3,4);
insert into RB_TEST values (1,2,3,4)
            *
ERROR at line 1:
ORA-00913: too many values

SQL> insert into RB_TEST values (1,2,3);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from RB_TEST; - It won't display the invisible column data.
        T1         T2         T4
---------- ---------- ----------
         1          2          3

SQL> select T1,T2,T3,T4 from RB_TEST; -- If you mention explicitly the columns and you can see the invisible column data..But its have NULL value.
        T1         T2         T3         T4
---------- ---------- ---------- ----------
         1          2                     3

SQL> insert into RB_TEST (t1,t2,t3,t4) values (1,2,3,4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from RB_TEST;
        T1         T2         T4
---------- ---------- ----------
         1          2          3
         1          2          4

SQL> select T1,T2,T3,T4 from RB_TEST;
        T1         T2         T3         T4
---------- ---------- ---------- ----------
         1          2                     3
         1          2                  4

Lets insert few more records..

SQL> insert into RB_TEST (t1,t2,t3,t4) values (5,6,7,8);

1 row created.

SQL> insert into RB_TEST (t1,t2,t3,t4) values (9,10,11,12);

1 row created.

SQL> insert into RB_TEST (t1,t2,t3,t4) values (13,14,15,16);

1 row created.

SQL> commit;

Commit complete.

SQL> select T1,T2,T3,T4 from RB_TEST;

        T1         T2         T3         T4
---------- ---------- ---------- ----------
         1          2                     3
         1          2          3          4
         5          6          7          8
         9         10         11         12
        13         14         15         16

Lets check How index is working for Invisible column?

SQL> Select * from RB_TEST where T3=7;
        T1         T2         T4
---------- ---------- ----------
         5          6          8

SQL> Explain plan for Select * from RB_TEST where T3=7;

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1142072750
---------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    52 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| RB_TEST |     1 |    52 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
   1 - filter("T3"=7)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

SQL> create index RB_TEST_T3_IDX on RB_TEST(T3);

Index created.

SQL> Explain plan for Select * from RB_TEST where T3=7;

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1187002515
---------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |    52 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| RB_TEST        |     1 |    52 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | RB_TEST_T3_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T3"=7)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18 rows selected.


T3 Column explicitly referenced in WHERE condition and but still CBO plan is using invisible column index in their plan.

SQL>  select column_name, hidden_column from user_tab_cols where table_name='RB_TEST';

COLUMN_NAME          HID
-------------------- ---
T4                   NO
T3                   YES
T2                   NO
T1                   NO

SQL> alter table RB_TEST modify T3 VISIBLE;

Table altered.

SQL>  select column_name, hidden_column from user_tab_cols where table_name='RB_TEST';

COLUMN_NAME          HID
-------------------- ---
T4                   NO
T3                   NO
T2                   NO
T1                   NO

SQL> select * from RB_TEST;
        T1         T2         T4         T3
---------- ---------- ---------- ----------
         1          2          3
         1          2          4          3
         5          6          8          7
         9         10         12         11
        13         14         16         15

Few Things needs to be consider before using the INVISIBLE COLUMN

1. Not Null/Primary Key/Unique Key column won't support the INVISIBLE feature...At the same time NOT NULL DEFAULT value support the INVISIBLE feature.

Test case with NOT NULL COLUMN

SQL> create table RB_TEST
  2     (t1 number,
  3      t2 number,
  4      t3 number NOT NULL,
  5      t4  number);

Table created.

SQL> insert into RB_TEST values (1,2,3,4);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table RB_TEST modify T3 INVISIBLE;

Table altered.

SQL> desc RB_TEST
Name                                                                                                 Null?    Type
------------------------------------------------------------------ -------- -----------------------------------------------
T1                                                                                                                 NUMBER
T2                                                                                                                 NUMBER
T4                                                                                                                 NUMBER
T3 (INVISIBLE)                                                                        NOT NULL NUMBER

SQL> insert into RB_TEST (t1,t2,t3,t4) values (1,2,3,4);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into RB_TEST (t1,t2,t4) values (1,2,4);
insert into RB_TEST (t1,t2,t4) values (1,2,4)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("RB"."RB_TEST"."T3")

Test case with Primary Key COLUMN

SQL> create table RB_TEST
  2     (t1 number not null,
  3      t2 number,
  4      t3 number,
  5      t4  number,
  6   CONSTRAINT RB_TEST_t1_pk PRIMARY KEY (T1));

Table created.

SQL> insert into RB_TEST (t1,t2,t3,t4) values (1,2,3,4);

1 row created.

SQL> insert into RB_TEST (t1,t2,t3,t4) values (1,2,3,4);
insert into RB_TEST (t1,t2,t3,t4) values (1,2,3,4)
*
ERROR at line 1:
ORA-00001: unique constraint (RB.RB_TEST_T1_PK) violated

SQL> commit;

Commit complete.

SQL> alter table RB_TEST modify T1 INVISIBLE;

Table altered.

SQL> desc RB_TEST
Name                                                                                               Null?    Type
---------------------------- ---------------------------------------------------------------------------
T2                                                                                                                 NUMBER
T3                                                                                                                 NUMBER
T4                                                                                                                 NUMBER
T1 (INVISIBLE)                                                                        NOT NULL NUMBER

SQL> insert into RB_TEST (t1,t2,t3,t4) values (2,2,3,4);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into RB_TEST values (2,3,4);
insert into RB_TEST values (2,3,4)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("RB"."RB_TEST"."T1")

2.  Cluster,Temporary and External Tables won't support the Invisible column feature

3. Application/Development should aware of invisible column table.  If they use SELECT * or
INSERT statement doesn't mention the column list is going to corrupt the data in the table.

SQL> CREATE TABLE RB_TEST
  2    (emp_id NUMBER NOT NULL,
  3     LAST_NAME VARCHAR2(100),
  4     FIRST_NAME VARCHAR2(100),
  5     CONSTRAINT RB_TEST_EMP_ID_pk PRIMARY KEY (EMP_ID));

Table created.

SQL>
SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX')
  2  FROM sys.col$
  3  WHERE obj# =
  4  (
  5    SELECT obj# FROM sys.obj$ WHERE name = 'RB_TEST'
  6  );

NAME                       COL#    INTCOL#    SEGCOL# TO_CHAR(PROPE
-------------------- ---------- ---------- ---------- -------------
EMP_ID                        1          1          1             0
LAST_NAME                     2          2          2             0
FIRST_NAME                    3          3          3             0

SQL> insert into RB_TEST VALUES(100, 'THANGARAJ','RAJABASKAR');

1 row created.

SQL>
SQL>
SQL> commit;

Commit complete.

SQL> alter table RB_TEST modify LAST_NAME INVISIBLE;

Table altered.

SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX')
  2  FROM sys.col$
  3  WHERE obj# =
  4  (
  5    SELECT obj# FROM sys.obj$ WHERE name = 'RB_TEST'
  6  );

NAME                       COL#    INTCOL#    SEGCOL# TO_CHAR(PROPE
-------------------- ---------- ---------- ---------- -------------
EMP_ID                        1          1          1             0
LAST_NAME                     0           2          2     400000020
FIRST_NAME                    2          3          3             0

SQL> alter table RB_TEST modify LAST_NAME VISIBLE;

Table altered.

SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX')
  2  FROM sys.col$
  3  WHERE obj# =
  4  (
  5    SELECT obj# FROM sys.obj$ WHERE name = 'RB_TEST'
  6  );

NAME                       COL#    INTCOL#    SEGCOL# TO_CHAR(PROPE
-------------------- ---------- ---------- ---------- -------------
EMP_ID                        1          1          1             0
LAST_NAME                     3          2          2             0
FIRST_NAME                    2          3          3             0

-- Column order was changed in data dictionary table.

SQL> insert into RB_TEST VALUES(101, 'THANGARAJ','RAJABASKAR');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from RB_TEST;

    EMP_ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
       100 RAJABASKAR           THANGARAJ
       101 THANGARAJ            RAJABASKAR

This is really nice feature and but i am not sure where we can use this feature ?
Even if we use this feature there is lot of chance to corrupt the data from my own opinion !!!


Reference: Julian dyke Blog and Oracle Documentation.
Wish You Happy New Year ... Great Year Ahead !!!