Create Schema - Bundling DDL

Learnt something newly today in Oracle, its about bundling DDL's in a single Transaction.

Create Schema Statement available in Oracle allows you to combine multiple create tables, create views and Grants in a single Transaction. If all the statments executes successfully then Oracle commits the Transaction or else it will be rolled back entirely.

test@9IR2> create schema authorization test
  2     create table t(x number,y varchar2(10),z date)
  3     create view v as select * from t
  4     grant select on t to public
  5     grant select on v to public;

Schema created.

Elapsed: 00:00:00.01

test@9IR2>
test@9IR2> desc t;
Name        Null?    Type
---------- -------- ---------------
X                    NUMBER
Y                    VARCHAR2(10)
Z                    DATE

test@9IR2>
test@9IR2> drop table t ;

Table dropped.

Elapsed: 00:00:00.01
test@9IR2> drop view v;

View dropped.

Elapsed: 00:00:00.01

test@9IR2>
test@9IR2>

Now the Transaction suceeded entirely and the Table and View will become VALID schema objects. If the Create Schema fails it will never create Table and View, it will rollback the entire Transaction.

test@9IR2>
test@9IR2> create schema authorization test
  2     create table t(x number,y varchar2(10),z date)
  3     create view v as select * from test_foo
  4     grant select on t to public
  5     grant select on v to public;

create schema authorization test
*
ERROR at line 1:
ORA-02427: create view failed

Elapsed: 00:00:00.01

test@9IR2>
test@9IR2> desc t;

ERROR:
ORA-04043: object t does not exist

test@9IR2> desc v;
ERROR:
ORA-04043: object v does not exist

test@9IR2>
test@9IR2>

Shared Pool Utilization With Dynamic Predicates

How Can I declare multiple cursors based on the values passed through a procedure and only the WHERE condition will change ?

Lets see how this can be achieved using Secure application contexts to " bind " inputs and using String concatenation (||)

Using secure application context it will be.

rajesh@10GR2> create or replace context my_ctx using p1;

Context created.

Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> create or replace procedure p1(
  2             p_empno in number default null,
  3             p_sal   in number default null,
  4             p_deptno in number default null
  5  )
  6  as
  7     l_query varchar2(4000) default ' select * from emp p1 where 1 = 1 ';
  8     l_rec   emp%rowtype;
  9     l_cursor sys_refcursor;
 10  begin
 11
 12     if p_empno is not null then
 13             dbms_session.set_context('my_ctx','l_empno',p_empno);
 14             l_query := l_query ||' and empno = sys_context(''my_ctx'',''l_empno'') ' ;
 15     end if;
 16
 17     if p_sal is not null then
 18             dbms_session.set_context('my_ctx','l_sal',p_sal);
 19             l_query := l_query ||' and sal > sys_context(''my_ctx'',''l_sal'') ' ;
 20     end if;
 21
 22     if p_deptno is not null then
 23             dbms_session.set_context('my_ctx','l_deptno',p_deptno);
 24             l_query := l_query ||' and deptno = sys_context(''my_ctx'',''l_deptno'') ' ;
 25     end if;
 26
 27     open l_cursor for l_query;
 28     loop
 29             fetch l_cursor into l_rec;
 30             exit when l_cursor%notfound;
 31             dbms_output.put_line (l_rec.empno||','||l_rec.ename||','||to_char(l_rec.hiredate,'dd-mon-yyyy'));
 32     end loop;
 33
 34     close l_cursor;
 35  end;
 36  /

Procedure created.

Elapsed: 00:00:00.07
rajesh@10GR2>

Using String concatenation (||) it will be

rajesh@10GR2> create or replace procedure p2(
  2             p_empno in number default null,
  3             p_sal   in number default null,
  4             p_deptno in number default null
  5  )
  6  as
  7     l_query varchar2(4000) default ' select * from emp p2 where 1 = 1 ';
  8     l_rec   emp%rowtype;
  9     l_cursor sys_refcursor;
 10  begin
 11
 12     if p_empno is not null then
 13             l_query := l_query ||' and empno ='||p_empno ;
 14     end if;
 15
 16     if p_sal is not null then
 17             l_query := l_query ||' and sal > '||p_sal ;
 18     end if;
 19
 20     if p_deptno is not null then
 21             l_query := l_query ||' and deptno = '||p_deptno ;
 22     end if;
 23
 24     open l_cursor for l_query;
 25     loop
 26             fetch l_cursor into l_rec;
 27             exit when l_cursor%notfound;
 28             dbms_output.put_line (l_rec.empno||','||l_rec.ename||','||to_char(l_rec.hiredate,'dd-mon-yyyy'));
 29     end loop;
 30
 31     close l_cursor;
 32  end;
 33  /

Procedure created.

Elapsed: 00:00:00.04
rajesh@10GR2>

Now both the procedure P1 and P2 remain the same functionality but their implementation differs. Lets see how Shared pool is getting utilized during these routines ( P1, P2) are execute in database.


rajesh@10GR2>
rajesh@10GR2> begin
  2     for i in 1..10
  3     loop
  4             p1(p_deptno=>i);
  5             p2(p_deptno=>i);
  6     end loop;
  7  end;
  8  /
7782,CLARK,09-JUN-81
7839,KING,17-NOV-81
7934,MILLER,23-JAN-82
7782,CLARK,09-JUN-81
7839,KING,17-NOV-81
7934,MILLER,23-JAN-82

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@10GR2>


Now looking at the Shared Pool contents, the query generated by the procedure P1 using Secure Application context is

rajesh@10GR2>
rajesh@10GR2> select sql_text
  2  from v$sqlarea
  3  where upper(sql_text) like '%SELECT%FROM%EMP%P1%'
  4  and upper(sql_text) not like '%V$SQLAREA%'
  5  /

SQL_TEXT
-----------------------------------------------------------------------------------------------
 select * from emp p1 where 1 = 1  and deptno = sys_context('my_ctx','l_deptno')

Elapsed: 00:00:00.04
rajesh@10GR2>

Now looking at the Shared Pool contents, the query generated by the procedure P2 NOT using Secure Application will be

rajesh@10GR2> select sql_text
  2  from v$sqlarea
  3  where upper(sql_text) like '%SELECT%FROM%EMP%P2%'
  4  and upper(sql_text) not like '%V$SQLAREA%'
  5  /

SQL_TEXT
-----------------------------------------------------------------------------------------------
 select * from emp p2 where 1 = 1  and deptno = 7
 select * from emp p2 where 1 = 1  and deptno = 9
 select * from emp p2 where 1 = 1  and deptno = 2
 select * from emp p2 where 1 = 1  and deptno = 4
 select * from emp p2 where 1 = 1  and deptno = 3
 select * from emp p2 where 1 = 1  and deptno = 8
 select * from emp p2 where 1 = 1  and deptno = 6
 select * from emp p2 where 1 = 1  and deptno = 5
 select * from emp p2 where 1 = 1  and deptno = 10
 select * from emp p2 where 1 = 1  and deptno = 1

10 rows selected.

Elapsed: 00:00:00.06
rajesh@10GR2>

So, Using BIND ( Secure Application context ) you will be having Single SQL in shared pool where it will get reused. Without BIND variables you will be ended up with different SQL in shared pool for each values in predicates.

Cursor to HTML

A Generic function that can take ref-cursor as input and print HTML as Output.

rajesh@10GR2> CREATE OR REPLACE FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR)
  2  RETURN CLOB
  3  IS
  4    lRetVal      CLOB;
  5    lHTMLOutput  XMLType;
  6    lXSL         CLOB;
  7    lXMLData     XMLType;
  8    lContext     DBMS_XMLGEN.CTXHANDLE;
  9  begin
 10     -- get a handle on the ref cursor --
 11     lContext := DBMS_XMLGEN.NEWCONTEXT(rf);
 12     -- setNullHandling to 1 (or 2) to allow null columns to be displayed --
 13     DBMS_XMLGEN.setNullHandling(lContext,1);
 14     -- create XML from ref cursor --
 15     lXMLData := DBMS_XMLGEN.GETXMLTYPE(lContext,DBMS_XMLGEN.NONE);
 16
 17     -- this is a generic XSL for Oracle's default XML row and rowset tags --
 18     -- " " is a non-breaking space --
 19     lXSL := lXSL || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
 20     lXSL := lXSL || q'[<xsl:stylesheet version="1.0"
 21     xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
 22     lXSL := lXSL || q'[ <xsl:output method="html"/>]';
 23     lXSL := lXSL || q'[ <xsl:template match="/">]';
 24     lXSL := lXSL || q'[ <html>]';
 25     lXSL := lXSL || q'[  <body>]';
 26     lXSL := lXSL || q'[   <table border="1">]';
 27     lXSL := lXSL || q'[     <tr bgcolor="cyan">]';
 28     lXSL := lXSL || q'[      <xsl:for-each select="/ROWSET/ROW[1]/*">]';
 29     lXSL := lXSL || q'[       <th><xsl:value-of select="name()"/></th>]';
 30     lXSL := lXSL || q'[      </xsl:for-each>]';
 31     lXSL := lXSL || q'[     </tr>]';
 32     lXSL := lXSL || q'[     <xsl:for-each select="/ROWSET/*">]';
 33     lXSL := lXSL || q'[      <tr>]';
 34     lXSL := lXSL || q'[       <xsl:for-each select="./*">]';
 35     lXSL := lXSL || q'[        <td><xsl:value-of select="text()"/> </td>]';
 36     lXSL := lXSL || q'[       </xsl:for-each>]';
 37     lXSL := lXSL || q'[      </tr>]';
 38     lXSL := lXSL || q'[     </xsl:for-each>]';
 39     lXSL := lXSL || q'[   </table>]';
 40     lXSL := lXSL || q'[  </body>]';
 41     lXSL := lXSL || q'[ </html>]';
 42     lXSL := lXSL || q'[ </xsl:template>]';
 43     lXSL := lXSL || q'[</xsl:stylesheet>]';
 44
 45     -- XSL transformation to convert XML to HTML --
 46     lHTMLOutput := lXMLData.transform(XMLType(lXSL));
 47
 48     -- convert XMLType to Clob --
 49     lRetVal := lHTMLOutput.getClobVal();
 50
 51     -- Close Context ---
 52     dbms_xmlgen.closeContext(lContext);
 53
 54     RETURN lRetVal;
 55
 56  end fncRefCursor2HTML;
 57  /

Function created.

Elapsed: 00:00:00.14
rajesh@10GR2>
rajesh@10GR2> variable x refcursor;
rajesh@10GR2> variable y clob;
rajesh@10GR2>
rajesh@10GR2> set autoprint on;
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> begin
  2     open :x for select * from dept;
  3     :y := fncRefCursor2HTML(:x);
  4  end;
  5  /

PL/SQL procedure successfully completed.


Y
--------------------------------------------------------------------------------
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<th>DEPTNO</th>
<th>DNAME</th>
<th>LOC</th>
<th>LOCATION_ID</th>
</tr>
<tr>
<td>10</td>
<td>ACCOUNTING</td>
<td>NEW YORK</td>
<td>1</td>
</tr>
<tr>
<td>20</td>
<td>RESEARCH</td>
<td>DALLAS</td>
<td>2</td>
</tr>
<tr>
<td>30</td>
<td>SALES</td>
<td>CHICAGO</td>
<td>1</td>
</tr>
<tr>
<td>40</td>
<td>OPERATIONS</td>
<td>BOSTON</td>
<td>1</td>
</tr>
</table>
</body>
</html>

Elapsed: 00:00:00.03
rajesh@10GR2>

VPD Column Masking

Learnt recently from documentation, its about Oracle 10g Column level VPD. Here is the snippet of code to demonstrate this.

rajesh@10GR2> create table emp
  2  as
  3  select     empno,
  4             ename,
  5             deptno,
  6             dbms_random.random as ssn
  7  from scott.emp;

Table created.

Elapsed: 00:00:00.26
rajesh@10GR2> create or replace function foo(p_schema in varchar2,p_object_name in varchar2)
  2  return varchar2
  3  as
  4  begin
  5     if ( p_schema = user ) then
  6             return '1 = 1';
  7     else
  8             return '1=0';
  9     end if;
 10  end;
 11  /

Function created.

Elapsed: 00:00:00.09
rajesh@10GR2>

Now, lets say that we need to hide this SSN details from others. This can be done by using new features available dbms_rls API in Oracle 10g database.

rajesh@10GR2> begin
  2     dbms_rls.add_policy(
  3             object_schema =>user,
  4             object_name =>'EMP',
  5             policy_name => 'SSN_HIDING',
  6             function_schema => user,
  7             policy_function =>'FOO',
  8             statement_types =>'SELECT',
  9             sec_relevant_cols => 'SSN',
 10             sec_relevant_cols_opt => dbms_rls.all_rows
 11             );
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> select * from emp;

     EMPNO ENAME          DEPTNO        SSN
---------- ---------- ---------- ----------
      7369 SMITH              20  407740162
      7499 ALLEN              30 -128160633
      7521 WARD               30  378242413
      7566 JONES              20 -542663968
      7654 MARTIN             30 1929020028
      7698 BLAKE              30  408643855
      7782 CLARK              10 -569575368
      7788 SCOTT              20 1718524881
      7839 KING               10 -2.139E+09
      7844 TURNER             30 -1.033E+09
      7876 ADAMS              20 -607357748
      7900 JAMES              30 2099341193
      7902 FORD               20 1979593316
      7934 MILLER             10 -615781088

14 rows selected.

Elapsed: 00:00:00.01
rajesh@10GR2> 
rajesh@10GR2> connect scott/tiger
Connected.
scott@10GR2>
scott@10GR2> select * from rajesh.emp;

     EMPNO ENAME          DEPTNO        SSN
---------- ---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14 rows selected.

Elapsed: 00:00:00.07
scott@10GR2>
scott@10GR2>

Parsing - Refcursors Vs Normal Cursors

Learnt recently, Its all about parsing with Ref-cursors compared with Normal cursors. Here is the snippet of code to demonstrate that.

rajesh@10GR2> create table t_process
  2  as
  3  select rownum as key
  4  from all_objects
  5  where rownum <= 10;

Table created.

Elapsed: 00:00:00.14
rajesh@10GR2>

Now a procedure that uses Ref-cursors to get the result sets.

rajesh@10GR2> Create or replace procedure  P1(p_key in number)
  2  as
  3      x sys_refcursor;
  4  begin
  5      open x for
  6      select * from t_process RCur
  7      where key = p_key;
  8
  9      close X;
 10  exception
 11          when others then
 12              if x%isopen then
 13                  close x;
 14              end if;
 15          raise_application_error (-20458,sqlerrm);
 16  end;
 17  /

Procedure created.

Elapsed: 00:00:00.14
rajesh@10GR2>

Now a procedure that uses Normal ( parameter ) cursors to get the result sets.

rajesh@10GR2> Create or replace procedure  P2(p_key in number)
  2  as
  3      cursor c(p_in number) is
  4     select * from t_process Cur
  5     where key = p_in;
  6  begin
  7     open c(p_key);
  8
  9      close c;
 10  exception
 11          when others then
 12              if c%isopen then
 13                  close c;
 14              end if;
 15          raise_application_error (-20458,sqlerrm);
 16  end;
 17  /

Procedure created.

Elapsed: 00:00:00.06
rajesh@10GR2>

After calling the procedure P1, P2 from an Anonymous blocks like this

rajesh@10GR2> begin
  2     for i in 1..500
  3     loop
  4                p1(i);
  5                p2(i);
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:54.76
rajesh@10GR2>

Now Tkprof shows me

********************************************************************************
SELECT *
FROM
 T_PROCESS RCUR WHERE KEY = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      500      0.00       0.01          0          0          0           0
Execute    500      0.07       0.06          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1000      0.07       0.08          0          0          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100     (recursive depth: 1)

********************************************************************************

SELECT *
FROM
 T_PROCESS CUR WHERE KEY = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    500      0.09       0.07          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      501      0.09       0.07          0          0          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100     (recursive depth: 1)

********************************************************************************

A Ref-cursor is always parsed with each and every OPEN call - they are never cached.

If you are NOT returning the cursor to a client, you are NOT doing dynamic-sql then you should not use Ref-cursors.

Invoker Rights - Shared pool Utilization

When using Invoker Rights we have a single procedure access data in different schema, depending on who is running the query at run time. Hence there will be multiple copies of SQL statements available in shared pool one per each user. But when using definer rights procedure, there is at most once copy of SQL statement in shared pool that will be reused by multiple users.

I, created Ten users via this script:

rajesh@10GR2> begin
  2     for x in 1..10
  3     loop
  4             execute immediate ' create user u'||x||' identified by u'||x ;
  5             execute immediate ' grant connect,resource to u'||x;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
rajesh@10GR2>

rajesh@10GR2> create table t(x number);

Table created.

Elapsed: 00:00:00.04
rajesh@10GR2> insert into t values (1);

1 row created.

Elapsed: 00:00:00.01
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> create or replace procedure dr_proc
  2  as
  3     l_count number;
  4  begin
  5     select count(*) into l_count from t dr_proc;
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> create or replace procedure ir_proc
  2     authid current_user
  3  as
  4     l_count number;
  5  begin
  6     select count(*) into l_count from t ir_proc;
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> grant execute on dr_proc to public;

Grant succeeded.

Elapsed: 00:00:00.04
rajesh@10GR2> grant execute on ir_proc to public;

Grant succeeded.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>

Now, executing this script in all the Ten users that we created above.

create table t(x number);
insert into t values (1);
commit;
exec rajesh.ir_proc;
exec rajesh.dr_proc;

We can investigate our shared pool (v$sqlarea) to see what happened.


rajesh@10GR2>
rajesh@10GR2> EXEC print_table(' select sql_text, sharable_mem, version_count,loaded_versions, parse_calls, optimizer_mode from v$sqlarea where UPPER(sql_text) like ''%PROC%'' and lower(sql_text) not like ''%v$sqlarea%'' ');

     SQL_TEXT------------ SELECT COUNT(*) FROM T DR_PROC
     SHARABLE_MEM-------- 8643
     VERSION_COUNT------- 1
     LOADED_VERSIONS----- 1
     PARSE_CALLS--------- 10
     OPTIMIZER_MODE------ ALL_ROWS
--------------------------------------------
     SQL_TEXT------------ SELECT COUNT(*) FROM T IR_PROC
     SHARABLE_MEM-------- 76971
     VERSION_COUNT------- 10
     LOADED_VERSIONS----- 10
     PARSE_CALLS--------- 10
     OPTIMIZER_MODE------ ALL_ROWS
--------------------------------------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.51
rajesh@10GR2>

This shows clearly that Invoker rights routine has made ten different copies of SQL's available in shared pool but definer rights had made only a single copy as expected.

Bitmap Indexes on IOT.

Learnt newly from oracle docs..Its about Bitmap Indexes on IOT. Bitmap indexes cannot be created on IOT tables unless you have a mapping table defined.

rajesh@10GR2> create table t(
  2     owner,
  3     object_name,
  4     object_type,
  5     object_id,
  6     created,
  7     deptno,
  8     constraint t_pk primary key(object_id)
  9  )organization index
 10  nologging
 11  as
 12  select owner,object_name,object_type,object_id,created,mod(rownum,10)
 13  from all_objects;

Table created.

Elapsed: 00:00:07.79
rajesh@10GR2>
rajesh@10GR2> create bitmap index t_bitmap on t(deptno);
create bitmap index t_bitmap on t(deptno)
                                *
ERROR at line 1:
ORA-28669: bitmap index can not be created on an IOT with no mapping table

Elapsed: 00:00:00.01

This error clearly specifies that Bitmap indexes cannot be created on non mapping IOT segments with no
mapping table.The solution is to design the Mapping table with IOT segments. Specify MAPPING TABLE to instruct the database to create a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table.

rajesh@10GR2> create table t(
  2             owner,
  3             object_name,
  4             object_type,
  5             object_id,
  6             created,
  7             deptno,
  8             constraint t_pk primary key(object_id)
  9  )organization index mapping table
 10  nologging
 11  as
 12  select owner,object_name,object_type,object_id,created,mod(rownum,10)
 13  from all_objects;

Table created.

Elapsed: 00:00:09.03
rajesh@10GR2>
rajesh@10GR2> create bitmap index t_bitmap on t(deptno);

Index created.

Elapsed: 00:00:00.45
rajesh@10GR2>
rajesh@10GR2> SELECT table_name,iot_type, uo.object_id
  2  FROM user_tables ut, user_objects uo
  3  where iot_type like 'IOT%'
  4  and uo.object_name = ut.table_name
  5  /

TABLE_NAME                     IOT_TYPE      OBJECT_ID
------------------------------ ------------ ----------
SYS_IOT_MAP_151777             IOT_MAPPING      151778
T                              IOT              151777

Elapsed: 00:00:00.43
rajesh@10GR2>

This mapping table ( SYS_IOT_MAP_151777 ) is nothing but a Heap organized table having mapping of local to physical rowids.

rajesh@10GR2> select *
  2  from sys_iot_map_151777
  3  where rownum <=5;

SYS_NC_01
-----------------------------------------------------------
*BAHABJMDwjIP/g
*BAHABJMDwjIR/g
*BAHABJMDwjIS/g
*BAHABJMDwjIT/g
*BAHABJMDwjIU/g

Elapsed: 00:00:00.09
rajesh@10GR2>

If the index-organized table is partitioned, then the mapping table is also partitioned and its partitions have the same name and physical attributes as the base table partitions.

rajesh@10GR2> create table t(
  2             owner,
  3             object_name,
  4             object_type,
  5             object_id,
  6             created,
  7             deptno,
  8             constraint t_pk primary key(object_id)
  9  )organization index mapping table
 10  nologging
 11  partition by range(object_id)
 12  (
 13     partition p_1 values less than(10000),
 14     partition p_2 values less than(20000),
 15     partition p_3 values less than(30000),
 16     partition p_4 values less than(40000),
 17     partition p_5 values less than(50000),
 18     partition p_max values less than (maxvalue)
 19  )
 20  as
 21  select owner,object_name,object_type,object_id,created,mod(rownum,10)
 22  from all_objects;

Table created.

Elapsed: 00:00:10.28
rajesh@10GR2> SELECT table_name, ut.partition_name,uo.object_id
  2  FROM user_tab_partitions ut, user_objects uo
  3  where uo.object_name = ut.table_name
  4  and  uo.subobject_name = ut.partition_name
  5  /

TABLE_NAME                     PARTITION_NAME                  OBJECT_ID
------------------------------ ------------------------------ ----------
SYS_IOT_MAP_151805             P_MAX                              151818
SYS_IOT_MAP_151805             P_5                                151817
SYS_IOT_MAP_151805             P_4                                151816
SYS_IOT_MAP_151805             P_3                                151815
SYS_IOT_MAP_151805             P_2                                151814
SYS_IOT_MAP_151805             P_1                                151813
T                              P_MAX                              151811
T                              P_5                                151810
T                              P_4                                151809
T                              P_3                                151808
T                              P_2                                151807
T                              P_1                                151806

12 rows selected.

Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2>

So how converting a IOT segments having non-mapping table to a mapping table in real world Applications? Now that can be done by re-organizing the table online using dbms_redefinition API.

rajesh@10GR2>
rajesh@10GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "RAJESH"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_TYPE" VARCHAR2(19),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "CREATED" DATE NOT NULL ENABLE,
        "DEPTNO" NUMBER,
         CONSTRAINT "T_PK" PRIMARY KEY ("OBJECT_ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"
 PCTTHRESHOLD 50

Elapsed: 00:00:08.95
rajesh@10GR2> create table t_part
  2  (
  3     OWNER                   VARCHAR2(30),
  4     OBJECT_NAME     VARCHAR2(30),
  5     OBJECT_TYPE     VARCHAR2(19),
  6     OBJECT_ID               NUMBER,
  7     CREATED         DATE,
  8     DEPTNO          NUMBER,
  9     constraint t_part_pk primary key(object_id)
 10  )
 11  organization index mapping table;

Table created.

Elapsed: 00:00:00.34
rajesh@10GR2>
rajesh@10GR2> begin
  2     dbms_redefinition.can_redef_table(
  3             uname=>user,
  4             tname=>'T'
  5     );
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
rajesh@10GR2>
rajesh@10GR2> begin
  2     dbms_redefinition.start_redef_table(
  3             uname=>user,
  4             orig_table=>'T',
  5             int_table=>'T_PART');
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.89
rajesh@10GR2>
rajesh@10GR2> variable x number;
rajesh@10GR2>
rajesh@10GR2> begin
  2     dbms_redefinition.copy_table_dependents(
  3             uname=>user,
  4             orig_table=>'T',
  5             int_table=>'T_PART',
  6             num_errors=>:x);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.78
rajesh@10GR2> begin
  2     dbms_redefinition.finish_redef_table(
  3             uname=>user,
  4             orig_table=>'T',
  5             int_table=>'T_PART');
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.84
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "RAJESH"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(30),
        "OBJECT_TYPE" VARCHAR2(19),
        "OBJECT_ID" NUMBER,
        "CREATED" DATE,
        "DEPTNO" NUMBER,
         CONSTRAINT "T_PART_PK" PRIMARY KEY ("OBJECT_ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"
 PCTTHRESHOLD 50 MAPPING TABLE



Elapsed: 00:00:02.98
rajesh@10GR2>

Cardinality feedback in 11G

when you have a pipelined function - the estimated cardinality is computed based on your blocksize - the default number of rows that will come from it are based on your database block size. I have an 8k block size so..

rajesh@11GR2> create or replace function parse_list(p_varchar_list varchar2,p_delimiter in varcha
  2  return vcarray
  3  pipelined
  4  as
  5  begin
  6  for x in (      select
  7     trim( substr (txt,
  8     instr (txt, p_delimiter, 1, level  ) + 1,
  9     instr (txt, p_delimiter, 1, level+1)
 10     - instr (txt, p_delimiter, 1, level) -1 )
 11     )
 12     as discrete_value
 13     from   (select  p_delimiter||p_varchar_list||p_delimiter txt from dual)
 14     connect by level <= length(p_varchar_list)-length(replace(p_varchar_list,p_delimiter,'')
 15    loop
 16               pipe row(x.discrete_value);
 17    end loop;
 18  end;
 19  /

Function created.

Elapsed: 00:00:00.01
rajesh@11GR2>
rajesh@11GR2> variable x varchar2(40);
rajesh@11GR2> exec :x := 'A_B_C';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> set serveroutput off;
rajesh@11GR2>
rajesh@11GR2> select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t;

COLUMN_VALUE
-------------
A
B
C

Elapsed: 00:00:00.01
rajesh@11GR2>
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  2da7fr1xbuv36, child number 5
-------------------------------------
select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t

Plan hash value: 58440541

------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


13 rows selected.

The optimizer guesses 8168 rows. It all probability - the real number of rows is not anywhere near 8168. If we use this estimated row count in a bigger query - we'll probably end up with the wrong plan.


rajesh@11GR2>
rajesh@11GR2> select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t;

COLUMN_VALUE
-------------
A
B
C

Elapsed: 00:00:00.00
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  2da7fr1xbuv36, child number 6
-------------------------------------
select * from table ( cast( parse_list(:x,'_') as vcarray ) ) t

Plan hash value: 58440541

------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |     6 |    12 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement


17 rows selected.

Elapsed: 00:00:00.07
rajesh@11GR2>

 Apparently, now it hard parsed that query - it did not reuse the plan. We know that it hard parsed since it just created child #6 - and we can see the plan is different - the row count is much lower.


Now, in this example only the estimated row counts changed - The actual plan is same ( since the query is very simple ) will this affects real world application codes?


rajesh@11GR2> create table t
  2  nologging
  3  as
  4  select * from all_objects;

Table created.

Elapsed: 00:00:05.87
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t
  2  ( case when owner = 'SCOTT' then owner
  3        when owner = 'APPQOSSYS' then owner
  4        when owner = 'OWBSYS' then owner
  5        else null end
  6  ) nologging;

Index created.

Elapsed: 00:00:00.48
rajesh@11GR2>
rajesh@11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'T',
  5     estimate_percent=>100,
  6     method_opt=>'for all columns size 254',
  7     cascade =>true);
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.01
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> variable y varchar2(40);
rajesh@11GR2> exec :y := 'SCOTT_APPQOSSYS_OWBSYS';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> with datas as
  2  (
  3    select * from table ( cast( parse_list(:y,'_') as vcarray ) )
  4  )
  5  select t.*
  6  from t , datas
  7  where case when owner ='SCOTT' then owner
  8             when owner ='APPQOSSYS' then owner
  9             when owner ='OWBSYS' then owner
 10             else null end  = datas.column_value;

 .....

11 rows selected.

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6vy77gcct2fgy, child number 1
-------------------------------------
with datas as (   select * from table ( cast( parse_list(:y,'_') as
vcarray ) ) ) select t.* from t , datas where case when owner ='SCOTT'
then owner            when owner ='APPQOSSYS' then owner
when owner ='OWBSYS' then owner            else null end  =
datas.column_value

Plan hash value: 3303169200

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |       |       |  1002 (100)|          |
|   1 |  MERGE JOIN                         |            |   195M|    18G|  1002  (97)| 00:00:13 |
|   2 |   TABLE ACCESS BY INDEX ROWID       | T          | 71757 |  6867K|     5   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN                  | T_IND      |    11 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                         |            |  8168 | 16336 |    30   (4)| 00:00:01 |
|   5 |    COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."SYS_NC00016$"=VALUE(KOKBF$))
       filter("T"."SYS_NC00016$"=VALUE(KOKBF$))


27 rows selected.

Elapsed: 00:00:00.09


as opposed to


rajesh@11GR2> with datas as
  2  (
  3    select * from table ( cast( parse_list(:y,'_') as vcarray ) )
  4  )
  5  select t.*
  6  from t , datas
  7  where case when owner ='SCOTT' then owner
  8             when owner ='APPQOSSYS' then owner
  9             when owner ='OWBSYS' then owner
 10             else null end  = datas.column_value;

 .....

11 rows selected.

Elapsed: 00:00:00.12
rajesh@11GR2>
rajesh@11GR2> select * from table ( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6vy77gcct2fgy, child number 2
-------------------------------------
with datas as (   select * from table ( cast( parse_list(:y,'_') as
vcarray ) ) ) select t.* from t , datas where case when owner ='SCOTT'
then owner            when owner ='APPQOSSYS' then owner
when owner ='OWBSYS' then owner            else null end  =
datas.column_value

Plan hash value: 4190747592

-------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |            |       |       |    35 (100)|          |
|   1 |  NESTED LOOPS                      |            |    22 |  2200 |    35   (0)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |     6 |    12 |    29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID      | T          |     4 |   392 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                | T_IND      |     1 |       |     0   (0)|          |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."SYS_NC00016$"=VALUE(KOKBF$))

Note
-----
   - cardinality feedback used for this statement


29 rows selected.

Elapsed: 00:00:00.10
rajesh@11GR2>

Unusable Index - Drop Segments - 11GR2

Setting the Index ( B*Tree / Bitmaps/ Function based ) to unusable state will drop the underlying storage segments. Here is the code piece to demonstrate that.

rajesh@11GR2> create table t(
  2     x ,
  3     y ,
  4     z
  5  )
  6  nologging
  7  as
  8  select     level as x,
  9             rpad('*',40,'*') as y,
 10             sysdate as dt
 11  from dual
 12  connect by level <= 1000000;

Table created.

Elapsed: 00:00:15.31
rajesh@11GR2>
rajesh@11GR2> create unique index t_ind on t(x,z) nologging;

Index created.

Elapsed: 00:00:08.43
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks****************  3328
l_total_bytes*****************  27262976
l_unused_blocks***************  58
l_unused_bytes****************  475136
l_last_used_extent_file_id****  4
l_last_used_extent_block_id***  17152
l_last_used_block*************  70
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  1
l_fs2_bytes*******************  8192
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  3209
l_full_bytes******************  26288128

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.95
rajesh@11GR2>
rajesh@11GR2> alter index t_ind unusable;

Index altered.

Elapsed: 00:00:02.21
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks****************  0
l_total_bytes*****************  0
l_unused_blocks***************  0
l_unused_bytes****************  0
l_last_used_extent_file_id****  0
l_last_used_extent_block_id***  0
l_last_used_block*************  0
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  0
l_full_bytes******************  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.15
rajesh@11GR2> create bitmap index t_ind on t(z) nologging;

Index created.

Elapsed: 00:00:02.15
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks****************  32
l_total_bytes*****************  262144
l_unused_blocks***************  4
l_unused_bytes****************  32768
l_last_used_extent_file_id****  4
l_last_used_extent_block_id***  4784
l_last_used_block*************  4
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  1
l_fs2_bytes*******************  8192
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  23
l_full_bytes******************  188416

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
rajesh@11GR2> alter index t_ind unusable;

Index altered.

Elapsed: 00:00:00.04
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks****************  0
l_total_bytes*****************  0
l_unused_blocks***************  0
l_unused_bytes****************  0
l_last_used_extent_file_id****  0
l_last_used_extent_block_id***  0
l_last_used_block*************  0
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  0
l_full_bytes******************  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.10
rajesh@11GR2> create index t_ind on t(x,0) nologging;

Index created.

Elapsed: 00:00:04.82
rajesh@11GR2> SELECT index_name, index_type
  2  FROM user_indexes
  3  WHERE index_name ='T_IND'
  4  /

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
T_IND                          FUNCTION-BASED NORMAL

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks****************  2560
l_total_bytes*****************  20971520
l_unused_blocks***************  2
l_unused_bytes****************  16384
l_last_used_extent_file_id****  4
l_last_used_extent_block_id***  16384
l_last_used_block*************  126
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  1
l_fs2_bytes*******************  8192
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  2509
l_full_bytes******************  20553728

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> alter index t_ind unusable;

Index altered.

Elapsed: 00:00:00.01
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks****************  0
l_total_bytes*****************  0
l_unused_blocks***************  0
l_unused_bytes****************  0
l_last_used_extent_file_id****  0
l_last_used_extent_block_id***  0
l_last_used_block*************  0
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  0
l_full_bytes******************  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@11GR2>

PARTITIONED OUTER JOINS - 10G

What i had learnt today is about Partitioned outer joins introduced in Oracle database 10g.

Data is stored in sparse form. That is if no value exists for a given time (date) then row rows exists in sales table.
Lets setup a simple example, All I want to do is compare this month sale's with last month sales.

Start with this data.

rajesh@10GR2> select * from sales;

ITEM  TXN_DT       TOTAL_AMT
----- ----------- ----------
A     01-JUN-2010        210
A     01-JUL-2010        270
A     01-AUG-2010        240
A     01-OCT-2010        327
A     01-NOV-2010        312
B     01-JUN-2010        410
B     01-AUG-2010        570
B     01-SEP-2010        640
B     01-OCT-2010        427
B     01-NOV-2010        312

10 rows selected.

Elapsed: 00:00:00.01
rajesh@10GR2>

Notice, how i am missing here. No sales for Item 'A' in SEP-2010 and for Item 'B' in JUL-2010. But how can i compare this month Transaction amount with previous month transaction amount?


rajesh@10GR2> select  item,
  2             txn_dt,
  3             lag(txn_dt) over(partition by item order by txn_dt) as previous_txn_dt,
  4             total_amt,
  5             lag(total_amt) over(partition by item order by txn_dt) as previous_txn_amt
  6  from sales
  7  order by item, txn_dt;

ITEM  TXN_DT     PREVIOUS_T TOTAL_AMT PREVIOUS_TXN_AMT
----- ---------- ---------- --------- ----------------
A     01-JUN-10                   210
A     01-JUL-10  01-JUN-10        270              210
A     01-AUG-10  01-JUL-10        240              270
A     01-OCT-10  01-AUG-10        327              240
A     01-NOV-10  01-OCT-10        312              327
B     01-JUN-10                   410
B     01-AUG-10  01-JUN-10        570              410
B     01-SEP-10  01-AUG-10        640              570
B     01-OCT-10  01-SEP-10        427              640
B     01-NOV-10  01-OCT-10        312              427

10 rows selected.

Notice how the output is compared, Item 'A' OCT-2010 compared with AUG-2010, But that isn't what i needed. I need to compare with last_month (NULL since SEP-2010 for item 'A' does not exists in sales table).
So, how can the missing data be filled for proper comparison? The way to achieve this is with the following query.

rajesh@10GR2> with datas as
  2  (
  3     select add_months( to_date('01/JUN/2010','dd/mon/yyyy') , (column_value - 1) ) as gap_dates
  4     from table(vtable(7))
  5  ),
  6  unique_items as
  7  (
  8     select distinct item from sales
  9  ),
 10  dates_items as
 11  ( select * from datas, unique_items )
 12  select     d.item,
 13             d.gap_dates as current_month,
 14             lag(d.gap_dates) over(partition by d.item order by d.gap_dates) as previous_month,
 15             nvl(s.total_amt,0) as current_month_total_amt,
 16             lag(s.total_amt,1,0) over(partition by d.item order by d.gap_dates) as previous_month_total_amt
 17  from sales s , dates_items d
 18  where s.item (+) = d.item
 19  and   s.txn_dt (+) = d.gap_dates
 20  order by d.item, d.gap_dates
 21  /

ITEM  CURRENT_MO PREVIOUS_M CURRENT_MONTH_TOTAL_AMT PREVIOUS_MONTH_TOTAL_AMT
----- ---------- ---------- ----------------------- ------------------------
A     01-JUN-10                                 210                        0
A     01-JUL-10  01-JUN-10                      270                      210
A     01-AUG-10  01-JUL-10                      240                      270
A     01-SEP-10  01-AUG-10                        0                      240
A     01-OCT-10  01-SEP-10                      327
A     01-NOV-10  01-OCT-10                      312                      327
A     01-DEC-10  01-NOV-10                        0                      312
B     01-JUN-10                                 410                        0
B     01-JUL-10  01-JUN-10                        0                      410
B     01-AUG-10  01-JUL-10                      570
B     01-SEP-10  01-AUG-10                      640                      570
B     01-OCT-10  01-SEP-10                      427                      640
B     01-NOV-10  01-OCT-10                      312                      427
B     01-DEC-10  01-NOV-10                        0                      312

14 rows selected.

Elapsed: 00:00:00.06

But with the introduction of PARTITIONED OUTER JOINS in Oracle 10g, this can be achieved very simple.

rajesh@10GR2> with datas as
  2  (
  3     select add_months( to_date('01/JUN/2010','dd/mon/yyyy') , (column_value - 1) ) as gap_dates
  4     from table(vtable(7))
  5  )
  6  select     s.item,
  7             d.gap_dates as current_month,
  8             lag(d.gap_dates) over(partition by s.item order by d.gap_dates) as previous_month,
  9             nvl(s.total_amt,0) as current_month_total_amt,
 10             lag(s.total_amt,1,0) over(partition by s.item order by d.gap_dates) as previous_month_total_amt
 11  from datas d left outer join sales s partition by (item)
 12     on (d.gap_dates = s.txn_dt )
 13  order by s.item, d.gap_dates
 14  /

ITEM  CURRENT_MO PREVIOUS_M CURRENT_MONTH_TOTAL_AMT PREVIOUS_MONTH_TOTAL_AMT
----- ---------- ---------- ----------------------- ------------------------
A     01-JUN-10                                 210                        0
A     01-JUL-10  01-JUN-10                      270                      210
A     01-AUG-10  01-JUL-10                      240                      270
A     01-SEP-10  01-AUG-10                        0                      240
A     01-OCT-10  01-SEP-10                      327
A     01-NOV-10  01-OCT-10                      312                      327
A     01-DEC-10  01-NOV-10                        0                      312
B     01-JUN-10                                 410                        0
B     01-JUL-10  01-JUN-10                        0                      410
B     01-AUG-10  01-JUL-10                      570
B     01-SEP-10  01-AUG-10                      640                      570
B     01-OCT-10  01-SEP-10                      427                      640
B     01-NOV-10  01-OCT-10                      312                      427
B     01-DEC-10  01-NOV-10                        0                      312

14 rows selected.

Elapsed: 00:00:00.04
rajesh@10GR2>

Hash Clusters

Learnt something newly from oracle product documentation its about Hash clusters storage and data access mechanisms.

rajesh@11GR2> create cluster emp_dept_hash_clust (deptno number)
  2  size 8192 HASHKEYS 100;

Cluster created.

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Elapsed: 00:00:00.14
rajesh@11GR2> exec show_space(user,'EMP_DEPT_HASH_CLUST','CLUSTER');
l_total_blocks****************  112
l_total_bytes*****************  917504
l_unused_blocks***************  2
l_unused_bytes****************  16384
l_last_used_extent_file_id****  4
l_last_used_extent_block_id***  632
l_last_used_block*************  6
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  101
l_full_bytes******************  827392

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> select ( 100* 8192)/8192 as blocks from dual;

    BLOCKS
----------
       100

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> create table emp_clust(
  2     empno,
  3     ename,
  4     sal,
  5     hiredate,
  6     deptno
  7  )
  8  cluster emp_dept_hash_clust (deptno)
  9  as
 10  select level, 'x',dbms_random.random,sysdate,case when mod(level,100) = 0 then 1 else mod(level,100) end as deptno
 11  from dual
 12  connect by level <= 1000000;

Table created.

Elapsed: 00:00:32.03
rajesh@11GR2>
rajesh@11GR2> exec dbms_stats.gather_table_stats(user,'EMP_CLUST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.53
rajesh@11GR2>

select empno,deptno,ename,hiredate
from emp_clust
where deptno = 3

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.07          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.00       0.07          1        155          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.00       0.14          1        157          0       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS HASH EMP_CLUST (cr=155 pr=1 pw=0 time=7178 us)

Oracle database applies the HASH function on Hash clustered key to locate the block that contains requested data. But be aware that Hash clusters are blind to Range scans.

select empno,deptno,ename,hiredate
from emp_clust
where deptno between 3 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      201      0.32       0.22         71       4679          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      203      0.32       0.22         71       4679          0       30000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 

Rows     Row Source Operation
-------  ---------------------------------------------------
  30000  TABLE ACCESS FULL EMP_CLUST (cr=4679 pr=71 pw=0 time=66023 us cost=1243 size=730980 card=40610)

Now we can see that oracle doesn't apply HASH function to locate the block, instead it full scan the Table to answer the queries. But in case of Indexed clusters oracle will still use clusters to answer queries involving Range scans.

rajesh@11GR2> create cluster emp_dept_clust (deptno number);

Cluster created.

Elapsed: 00:00:00.28
rajesh@11GR2>
rajesh@11GR2> create index idx_emp_dept_clu1 on cluster emp_dept_clust;

Index created.

Elapsed: 00:00:00.14
rajesh@11GR2> create table emp_clust(
  2     empno,
  3     ename,
  4     sal,
  5     hiredate,
  6     deptno
  7  )
  8  cluster emp_dept_clust (deptno)
  9  as
 10  select level, 'x',dbms_random.random,sysdate,case when mod(level,100) = 0 then 1 else mod(level,100) end as deptno
 11  from dual
 12  connect by level <= 1000000;

Table created.

Elapsed: 00:00:36.10
rajesh@11GR2> exec dbms_stats.gather_table_stats(user,'EMP_CLUST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.34

select empno,deptno,ename,hiredate
from emp_clust
where deptno between 3 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      201      0.01       0.02          0        324          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      203      0.01       0.02          0        324          0       30000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 

Rows     Row Source Operation
-------  ---------------------------------------------------
  30000  TABLE ACCESS CLUSTER EMP_CLUST (cr=324 pr=0 pw=0 time=18460 us cost=36 size=730980 card=40610)
      3   INDEX RANGE SCAN IDX_EMP_DEPT_CLU1 (cr=4 pr=0 pw=0 time=14 us cost=3 size=0 card=40610)(object id 76415)