Oracle Illustrated


June 25, 2010  6:06 AM

Implement ROLLING Partition using INTERVAL partition (11g)

Lakshmi Venkatesh Profile: Lakshmi Venkatesh
Object Creation scripts                    
                       
1 – Table creation script                    
                       
drop table rolling_test                    
                       
                       
CREATE TABLE rolling_test                    
(ID number,                      
joining_date DATE)                    
partition by range (joining_date)                    
INTERVAL (numtodsinterval (1, ‘DAY’))                  
(                      
PARTITION jun25 VALUES LESS                  
THAN(TO_DATE(’26-06-2010′,’dd-mm-yyyy’)),                  
PARTITION jun26 VALUES LESS                  
THAN(TO_DATE(’27-06-2010′,’dd-mm-yyyy’)),                  
PARTITION jun27 VALUES LESS                  
THAN(TO_DATE(’28-06-2010′,’dd-mm-yyyy’))                  
);                      
                       
                       
CREATE INDEX rolling_test_idx ON rolling_test (joining_date) LOCAL;                
                       
2 – Insert script                    
                       
insert into rolling_test values (1, trunc(sysdate))                  
                       
insert into rolling_test values (2, trunc(sysdate) +1)                  
                       
insert into rolling_test values (3, trunc(sysdate) + 2)                  
                       
insert into rolling_test values (4, trunc(sysdate) + 3)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 4)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 7)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 8)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 9)                  
                       
3 – Procedure to maintain ROLLING PARTITION logic                
                       
                       
/* Author – Lakshmi V                    
   Description – Parameters accepted P_COUNT - minimum number of partitions to be left for use; P_TABLE_NAME – Table name for which rolling partition to be maintained     
  and out parameter P_ERROR_TEXT                  
                         - Fetches the count of partitions from ALL_Tab_partitions table for the table name.            
   - Check if the total count of partitions in ALL_TAB_PARTITIONS is more than the REQUIRED MINIMUM NUMBER of partitions to be retained in the table    
   - Fetches the partition name to be dropped from ALL_TAB_PARTITIONS for the table. Always the FIRST PARTITION POSITION needs to be dropped    
   - Checks whether the fetched partition is the LAST FORMALLY CREATED PARTITION in RANGE partition list. Note: Oracle does not allow us to drop this partition   
  (in this case JUN27 cannot be dropped)                
   - If it’s the last partition in formally created partition – then, set the INTERVAL partition to NULL; DROP the partition; Then SET the INTERVAL partition back to its  
  original value from ALL_PART_TABLES                
   - If it’s a normal partition – then, drop the partition */                
                       
CREATE OR REPLACE PROCEDURE ROLLING_PART(p_count IN NUMBER, p_table_name IN VARCHAR2, p_error_text OUT VARCHAR2) AS        
    v_count NUMBER := 0;                    
    v_drop_partition VARCHAR2(100);                  
    v_error_lvl   NUMBER;                    
    v_error_text  VARCHAR2(1000);                  
    v_count1      NUMBER;                    
    v_in_count    NUMBER;                    
    v_interval    VARCHAR2(100);                    
BEGIN                      
    v_error_lvl := 1;                    
    v_error_text := ‘While selecting count from all_tab_partitions';                
    SELECT COUNT(1) INTO v_count FROM all_tab_partitions                
        WHERE table_name = UPPER(p_table_name);                
                       
    IF v_count > p_count THEN                    
    /* Check whether its the last formally created partion in range partition list */              
    SELECT COUNT(1) INTO v_count1 FROM all_tab_partitions                
        WHERE table_name = UPPER(p_table_name)                
               AND INTERVAL = ‘NO';                  
                       
    SELECT partition_name INTO v_drop_partition FROM all_tab_partitions               
            WHERE table_name = UPPER(p_table_name)                
              AND partition_position = 1;                  
                       
        IF ( v_count1 = 1 ) THEN                     
            select interval INTO v_interval from ALL_PART_TABLES where table_name = p_table_name;            
          DBMS_OUTPUT.PUT_LINE(‘v_interval ‘ || v_interval);                     
                       
         /* If its the last partition in the RANGE PARTITION then, set the INTERVAL to null, drop the partition and reset the interval again */          
            EXECUTE IMMEDIATE ‘alter table ‘ || p_table_name || ‘ set INTERVAL()';              
          DBMS_OUTPUT.PUT_LINE(‘PARTITION to be dropped ‘ || v_drop_partition);              
            EXECUTE IMMEDIATE ‘ALTER TABLE ‘ || p_table_name || ‘ DROP PARTITION ‘ || v_drop_partition;             
         EXECUTE IMMEDIATE ‘alter table ‘ || p_table_name || ‘ SET INTERVAL( ‘|| v_interval ||’)';            
        ELSE                      
        IF LENGTH(v_drop_partition) >0 THEN                  
            v_error_lvl := 3;                    
            v_error_text := ‘While dropping partition';                  
            DBMS_OUTPUT.PUT_LINE(‘PARTITION to be dropped ‘ || v_drop_partition);              
            EXECUTE IMMEDIATE ‘ALTER TABLE ‘ || p_table_name || ‘ DROP PARTITION ‘ || v_drop_partition;             
        END IF;                      
       END IF;                      
    END IF;                      
    EXCEPTION                    
    WHEN NO_DATA_FOUND THEN                  
        DBMS_OUTPUT.PUT_LINE(‘NOTHING TO PARTITION’);                
    WHEN OTHERS THEN                    
        DBMS_OUTPUT.PUT_LINE(‘Error at level ‘|| v_error_lvl ||’ text -‘|| v_error_text);              
        v_error_text := p_error_Text;                  
END;                      
/                      

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

PROOF OF CONCEPT

Step 1 – Create table, index, populate table & create procedure                                      
                                             
Step 2 – select * from rolling_test                                          
                                             
ID JOINING_DATE                                          
1 25/06/2010                                          
2 26/06/2010                                          
3 27/06/2010                                          
4 28/06/2010                                          
5 29/06/2010                                          
5 02/07/2010                                          
5 03/07/2010                                          
5 04/07/2010                                          
                                             
                                             
select * from all_tab_partitions where table_name = ‘ROLLING_TEST’                                      
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
Jun25 TO_DATE(‘ 2010-06-26 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
Jun26 TO_DATE(‘ 2010-06-27 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
Jun27 TO_DATE(‘ 2010-06-28 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3   (Note: Jun27 is the LAST PARTITION in the range partition list – rest of them are automatically created)                  
SYS_P112384 TO_DATE(‘ 2010-06-29 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4                                        
SYS_P112385 TO_DATE(‘ 2010-06-30 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 5                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 6                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 7                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 8                                        
                                             
Step 3 – Run the procedure to maintain only the LATEST 3 days of data (POC)                                    
                                             
                                             
declare                                            
    v_error_text VARCHAR2(1000);                                          
begin                                            
 rolling_part(3, ‘ROLLING_TEST’, v_error_text);                                        
end;                                             
/                                            
                                             
After running for FIRST TIME lets check ALL_TAB_PARTITIONS table                                      
                                             
select partition_name, high_value, partition_position from all_tab_partitions where table_name = ‘ROLLING_TEST’                              
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION   Note: Jun25 partition (first partition) is gone                              
JUN26 TO_DATE(‘ 2010-06-27 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
JUN27 TO_DATE(‘ 2010-06-28 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112384 TO_DATE(‘ 2010-06-29 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
SYS_P112385 TO_DATE(‘ 2010-06-30 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 5                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 6                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 7                                        
                                             
                                             
Lets run this for 3 more times then check ALL_TAB_PARTITIONS table                                      
                                             
declare                                            
    v_error_text VARCHAR2(1000);                                          
begin                                            
 rolling_part(3, ‘ROLLING_TEST’, v_error_text);                                        
end;                                             
/                                            
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112385 TO_DATE(‘ 2010-06-30 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4                                        
                                             
                                             
Run 1 more time                                            
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
                                             
                                             
Final time – now it should not drop anything ! – lets check                                        
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION   Note: It has not dropped any data                              
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
                                             
                                             
Lets try to insert and drop again                                          
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
SYS_P112389 TO_DATE(‘ 2010-07-06 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4    -> newly inserted record; partition created                              
                                             
                                             
Run the script again                                            
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1   SYS_P112386 partition (first partition in previous list is dropped)                          
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112389 TO_DATE(‘ 2010-07-06 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        

February 21, 2010  9:17 AM

SPLIT ONE COLUMN WITH DELIMITER TO MULTIPLE COLUMNS

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

If a delimiter is available on a column and if that needs to be split to multiple columns, then we usually use INSTR and SUBSTR to split into multiple columns and insert into table. Following is a simple demonstration without using multiple INSTR’s and SUBSTR’s and insert one column with a specified delimiter into multiple columns.

Step 1 : Create table script

drop table test_tab

create table test_tab
(col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20),
col5 varchar2(20),
col6 varchar2(20),
col7 varchar2(20),
col8 varchar2(20),
col9 varchar2(20),
col10 varchar2(20));

Step 2. PL/SQL script

set serveroutput on size 100000
declare
v_data VARCHAR2(200);
begin
SELECT ‘INSERT INTO TEST_TAB VALUES (‘
||””
||replace(qry.dat, ‘$’, ”’,”’)
|| REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ”’,”’)
||””
||’)’
INTO v_data
from
(SELECT
10 – (length(a.dat) – length(translate( a.dat, chr(0)||’$’, chr(0)))) LENGTH2, a.dat
FROM
(select ‘abcd$efgh$hijk%lmn$uvp’ dat from dual) a) qry;

dbms_output.put_line(‘Value of v_data is’ || v_data);

EXECUTE IMMEDIATE v_Data;
END;

SQL> @col_tocols.sql
21 /
Value of v_data isINSERT INTO TEST_TAB VALUES
(‘abcd’,’efgh’,’hijk%lmn’,’uvp
‘,”,”,”,”,”,”)

PL/SQL procedure successfully completed.

OUTPUT -

SQL> select * from test_Tab;

COL1 COL2 COL3
——————– ——————– ——————–
COL4 COL5 COL6
——————– ——————– ——————–
COL7 COL8 COL9
——————– ——————– ——————–
COL10
——————–
abcd efgh hijk%lmn
uvp

Notes

Line 1 – SELECT ‘INSERT INTO TEST_TAB VALUES (‘
Line 2 – ||””
Line 3 – ||replace(qry.dat, ‘$’, ”’,”’)
Line 4 – || REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ”’,”’)
Line 5 – ||””
Line 6 – ||’)’
Line 7 – INTO v_data
Line 8 – from
Line 9 – (SELECT
Line 10 – 10 – (length(a.dat) – length(translate( a.dat, chr(0)||’$’, chr(0)))) Line 11 – LENGTH2, a.dat
Line 12 – FROM
Line 13 – (select ‘abcd$efgh$hijk%lmn$uvp’ dat from dual) a) qry;

1. Line 3 – replace(qry.dat, ‘$’, ”’,”’) -> Search character $ would be replaced with ‘,’.

2. Line 4 – REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ”’,”’) -> In the above example there are 3 “$” symbols – these will be replaced with ‘,’. For 3 search strings “$” this will be split to 3 columns only. But, in the table there are 10 columns. So, to fill rest of the columns use RPAD function.

3. Line 10 & 11 – Translate function will replace one to one character. chr(0) is NULL. chr(0)||’$’ would be trranslated to chr(0) ignoring the ‘$’ which would give the LENGTH of the string excluding the search character.

Now, formula is –
10 MINUS (Total length of the string MINUS Length of the string excluding search character) -> This will give the length of left out columns in the table.

10 – (22 – 19) => 7.


February 21, 2010  9:16 AM

Not Exists vs. Analytical functions

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Analytical Functions – Introduced in Oracle 8i. There are different families of functions available and with each release new functions are introduced to the family tree. The below is a quick demonstration of using Analytical functions in place of NOT EXISTS.

Note: Similar logic can be applied to EXISTS also.

This is particularly useful for huge sets of data joined across multiple tables.
Following is a simple demonstration.

Case 1 – NOT EXIST vs USE OF ANALYTICAL FUNCTION

Requirement – Pick only those objects which are referenced in – PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE.
and not part of the rest.

ie., say,

TABLE_NAME OBJECT_TYPE
TAB1 PACKAGE
TAB1 PACKAGE BODY
TAB1 [ TRIGGER ]
TAB1 INDEX

Say, in the above example TRIGGER is not part of the expected in-list [ PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE ]. So, this should not be taken for consideration.

TABLE_NAME OBJECT_TYPE
TAB2 PACKAGE
TAB2 PACKAGE BODY
TAB2 INDEX

Above is part of the in-list, it should be included to the select.

Step 1 – Create table

Drop table KEY_TABLE

CREATE TABLE KEY_TABLE (table_name varchar2(50), referenced_in varchar2(50), object_id number)

Step 2 – Populate data

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct ‘TAB1′, object_type
from all_objects) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct ‘TAB2′, object_type
from all_objects where
OBJECT_TYPE IN (
‘PACKAGE’, ‘INDEX’, ‘TABLE’, ‘FUNCTION’, ‘PROCEDURE’)
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct ‘TAB3′, object_type
from all_objects where
OBJECT_TYPE IN (
‘PACKAGE’, ‘PACKAGE BODY”PROCEDURE’)
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct ‘TAB4′, object_type
from all_objects where
OBJECT_TYPE IN (
‘PACKAGE’, ‘PACKAGE BODY”PROCEDURE’, ‘TRIGGER’)
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct ‘TAB5′, object_type
from all_objects where
OBJECT_TYPE IN (
‘PACKAGE BODY’, ‘PACKAGE’, ‘INDEX’, ‘TABLE’, ‘FUNCTION’, ‘PROCEDURE’)
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct
‘TAB’||rownum * 20
, object_type
from all_objects) a

Step 3 – QUERY

— USING NOT EXISTS

SELECT /*+ ALL_ROWS */ * from key_table ktab
WHERE ktab.REFERENCEd_IN in (‘PACKAGE’, ‘PACKAGE BODY’, ‘INDEX’, ‘TABLE’, ‘FUNCTION’, ‘PROCEDURE’)
AND NOT EXISTS
(SELECT 1 FROM key_Table elim
WHERE elim.object_id = ktab.object_id
AND elim.REFERENCEd_IN not in (‘PACKAGE’, ‘PACKAGE BODY’, ‘INDEX’, ‘TABLE’, ‘FUNCTION’, ‘PROCEDURE’))

— USING ANALYTICAL FUNCTIONS

SELECT /*+ ALL_ROWS */ ktab.*,
COUNT(1) OVER (PARTITION BY referenced_in, table_name order by referenced_in, table_name) COUNT_TOTAL,
(CASE WHEN ktab.referenced_in in (‘PACKAGE’, ‘PACKAGE BODY’, ‘INDEX’, ‘TABLE’, ‘FUNCTION’, ‘PROCEDURE’) THEN
1
else
0
END) COUNT_ELIM
FROM key_table_1 ktab


February 21, 2010  9:16 AM

Few more examples – Analytical Functions

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

FINDING MISSING SEQUENCE / NUMBER from a table

SELECT /*+ ALL_ROWS */
pre_gap+1 start_gap, max_seq_gap
FROM
(SELECT /*+ ALL_ROWS */ seq_column post_gap, LAG(seq_column,1,0) OVER (ORDER BY seq_column) pre_gap ,
(SELECT /*+ ALL_ROWS */ MAX(seq_column) FROM table_name) max_seq_gap
FROM table_name)
WHERE pre_gap != 0
AND post_gap – pre_gap > 1

Delete duplicates

DELETE FROM table_name tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY ORDER BY ) duplicate FROM table_name ) qry
WHERE qry.duplicate > 1);


February 21, 2010  9:16 AM

Performance Tuning – NORMAL INSERT VS MULTI-TABLE INSERT

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Multi-table insert is introduced in ORACLE 9i. The main advantage is, it is simple to code and easy to maintain; just using a single INSERT script multiple tables and multiple data can be inserted into single / multiple tables.

1. Multi-table inserts is not possible across db links.
2. if you want to access sequence then it should be in the INSERT INTO clause and not in Select clause
3. FORALL can be used with Multi-table inserts
4. COLLECTIONS can be used in multi-table inserts
5. Insert-FIRST and Pivot table insert are also in the same lines.

My opinion – I prefer to use Multi-table inserts if possible in place of single table inserts.

STEP 1 – TABLE CREATE SCRIPTS:

QUICK LOOK AT THE RESULTS -

Multi-table Insert : Elapsed: 00:00:22.28
Normal Insert : Elapsed: 00:00:38.08

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

drop table gtt_stage

CREATE GLOBAL TEMPORARY TABLE gtt_stage on commit preserve rows
as select a.rowid gtt_rid, a.* from coll_temp_source a where 1 = 2

create sequence dummy_seq1 start with 1

drop table coll_temp_target;
drop table coll_temp_target2;
drop table coll_temp_target3;
drop table coll_temp_target4;

CREATE TABLE COLL_TEMP_TARGET
(OWNER VARCHAR2(30)
,OBJECT_NAME VARCHAR2(30)
,SUBOBJECT_NAME VARCHAR2(30)
,OBJECT_ID VARCHAR2(30));

CREATE TABLE COLL_TEMP_TARGET2
(OBJECT_ID NUMBER
,DATA_OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(18)
,CREATED DATE
,LAST_DDL_TIME DATE);

CREATE TABLE COLL_TEMP_TARGET3
(OBJECT_ID NUMBER
,TIMESTAMP VARCHAR2(19)
,STATUS VARCHAR2(7)
,TEMPORARY VARCHAR2(1));

CREATE TABLE COLL_TEMP_TARGET4
(OBJECT_ID NUMBER
,GENERATED VARCHAR2(1)
,SECONDARY VARCHAR2(1));

STEP 2 – MULTI-TABLE INSERT SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, dummy_seq1.nextval obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT ALL
WHEN 1 = 1 THEN
INTO coll_temp_target VALUES
(ownr, obj_nm, sub_obj, obj_id)
WHEN 1 = 1 THEN
INTO coll_temp_target2 VALUES
(obj_id, data_obj, obj_type, crtd, last_ddl)
WHEN 1 = 1 THEN
INTO coll_temp_target3 VALUES
(obj_id, time_stmp, stus, temp)
WHEN 1 = 1 THEN
INTO coll_temp_target4 VALUES
(obj_id, gen, sec)
SELECT
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT ALL
WHEN 1 = 1 THEN
INTO coll_temp_target VALUES
(ownr, obj_nm, sub_obj, obj_id)
WHEN 1 = 1 THEN
INTO coll_temp_target2 VALUES
(obj_id, data_obj, obj_type, crtd, last_ddl)
WHEN 1 = 1 THEN
INTO coll_temp_target3 VALUES
(obj_id, time_stmp, stus, temp)
WHEN 1 = 1 THEN
INTO coll_temp_target4 VALUES
(obj_id, gen, sec)
SELECT
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM gtt_stage ctgs ;

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @1_temptab_multi.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.28

ONT TABLE INSERT SCRIPT

NOTES:

STEP 3a – Single table Insert

truncate table coll_temp_target;
truncate table coll_temp_target2;
truncate table coll_temp_target3;
truncate table coll_temp_target4;

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

SCRIPT –

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
, dummy_seq1.nextval data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT
INTO coll_temp_target
SELECT
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
FROM gtt_stage ctgs ;

INSERT
INTO coll_temp_target2
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
FROM gtt_stage ctgs ;

INSERT
INTO coll_temp_target3
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
FROM gtt_stage ctgs ;

INSERT
INTO coll_temp_target4
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM gtt_stage ctgs ;

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @1_temptab_onetable.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.08


February 21, 2010  9:15 AM

Performance Tuning – TEMPORARY TABLE VS COLLECTION

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

STEP 1 – TABLE CREATE SCRIPTS:

QUICK LOOK AT THE RESULTS :

Temporary table : Elapsed: 00:00:17.84
Collections : Elapsed: 00:00:41.21

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

drop table gtt_stage

CREATE GLOBAL TEMPORARY TABLE gtt_stage on commit preserve rows
as select a.rowid gtt_rid, a.* from coll_temp_source a where 1 = 2

create sequence dummy_seq1 start with 1

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 AS SELECT * FROM ALL_OBJECTS WHERE 1 = 2

STEP 2 – RUN COLLECTIONS SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT *
FROM coll_temp_source;

BEGIN
g_error_level := 1;
OPEN cur_coll_temp;

LOOP
g_error_level := 2;
FETCH cur_coll_temp BULK COLLECT INTO g_object_tab LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP
IF g_object_tab(i).owner = ‘SYS’ THEN
g_object_tab(i).secondary := ‘Y';
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
END IF;

IF g_object_tab(i).object_type = ‘JAVA CLASS’ THEN
g_object_tab(i).temporary := ‘Y';
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
END IF;
END LOOP;

FORALL i IN g_object_tab.FIRST .. g_object_tab.LAST
INSERT INTO coll_temp_target5 VALUES g_object_tab(i);

COMMIT;

END LOOP;

g_error_level := 10;
CLOSE cur_coll_temp;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @1_coll.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.21
SQL>

STEP 3 – RUN TEMPORARY TABLE BASED SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, dummy_seq1.nextval obj_id
, ctgs.data_object_id data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT
INTO coll_temp_target5
SELECT /*+ ALL_ROWS */
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.object_id obj_id
, ctgs.data_object_id data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, DECODE(object_type , ‘JAVA CLASS’, ‘Y’, ctgs.TEMPORARY) temp
, ctgs.GENERATED gen
, DECODE(ctgs.OWNER, ‘SYS’, ‘Y’, ctgs.SECONDARY) sec
FROM gtt_stage ctgs ;

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @l_temp.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.84
SQL>

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


February 21, 2010  9:15 AM

Performance Tuning – MERGE BASED ON TEMPORARY TABLES VS MERGE BASED ON COLLECTIONS

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Merge – It is always better than separate INSERT / UPDATE and DELETE operations (Delete operation is added in 11g). FORALL Merge is pretty much possible while using COLLECTIONS. FORALL Merge is obviously faster than FOR LOOP Merge, as it enables bulk processing.

My opinion – If there is a choice between FORALL Merge and Merge using Global Temporary tables, its better to use the later. Following is a simple demonstration.

STEP 1 – TABLE CREATE SCRIPTS:

QUICK LOOK AT RESULTS -

Merge based on Temporary table : Elapsed: 00:00:35.68
Merge based on Collections : Elapsed: 00:11:20.27
Merge based on actual table : Elapsed: 00:00:37.30

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

drop table gtt_stage

CREATE GLOBAL TEMPORARY TABLE gtt_stage on commit preserve rows
as select a.rowid gtt_rid, a.* from coll_temp_source a where 1 = 2

create sequence dummy_seq1 start with 1

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000.

STEP 2 – MERGE + TEMP TABLE SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER
, ctgs.OBJECT_NAME
, ctgs.SUBOBJECT_NAME
, rownum
, ctgs.data_object_id
, ctgs.OBJECT_TYPE
, ctgs.CREATED
, ctgs.LAST_DDL_TIME
, ctgs.TIMESTAMP
, ctgs.STATUS
, ctgs.TEMPORARY
, ctgs.GENERATED
, ctgs.SECONDARY
FROM coll_temp_source ctgs ORDER BY ROWID;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO min_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm,
ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 1
ORDER BY 1;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO max_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm, ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 0
UNION
SELECT /*+ ALL_ROWS */ MAX (ROWID)
FROM coll_temp_source;

g_error_level := 2;
FORALL i IN min_rid_tab.FIRST .. min_rid_tab.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ INDEX (ctgs, gtt_stage_idx) */
ctgs.OWNER OWNER
, ctgs.OBJECT_NAME OBJECT_NAME
, ctgs.SUBOBJECT_NAME SUBOBJECT_NAME
, ctgs.object_id object_id
, ctgs.data_object_id data_object_id
, ctgs.OBJECT_TYPE OBJECT_TYPE
, ctgs.CREATED CREATED
, ctgs.LAST_DDL_TIME LAST_DDL_TIME
, ctgs.TIMESTAMP TIMESTAMP
, ctgs.STATUS STATUS
, DECODE(object_type , ‘JAVA CLASS’, ‘Y’, ctgs.TEMPORARY) TEMPORARY
, ctgs.GENERATED GENERATED
, DECODE(ctgs.OWNER, ‘SYS’, ‘Y’, ctgs.SECONDARY) SECONDARY
FROM gtt_stage ctgs WHERE gtt_rid BETWEEN min_rid_tab(i) AND max_rid_tab(i)) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
( tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @merge_temp.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:35.68
SQL>

STEP 3 – MERGE + COLLECTIONS Scripts

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000

SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_OWNER IS TABLE OF COLL_TEMP_SOURCE.OWNER%type;
TYPE g_OBJECT_NAME IS TABLE OF COLL_TEMP_SOURCE.OBJECT_NAME%type;
TYPE g_SUBOBJECT_NAME IS TABLE OF COLL_TEMP_SOURCE.SUBOBJECT_NAME%type;
TYPE g_OBJECT_ID IS TABLE OF COLL_TEMP_SOURCE.OBJECT_ID%type;
TYPE g_DATA_OBJECT_ID IS TABLE OF COLL_TEMP_SOURCE.DATA_OBJECT_ID%type;
TYPE g_OBJECT_TYPE IS TABLE OF COLL_TEMP_SOURCE.OBJECT_TYPE%type;
TYPE g_CREATED IS TABLE OF COLL_TEMP_SOURCE.CREATED%type;
TYPE g_LAST_DDL_TIME IS TABLE OF COLL_TEMP_SOURCE.LAST_DDL_TIME%type;
TYPE g_TIMESTAMP IS TABLE OF COLL_TEMP_SOURCE.TIMESTAMP%type;
TYPE g_STATUS IS TABLE OF COLL_TEMP_SOURCE.STATUS%type;
TYPE g_TEMPORARY IS TABLE OF COLL_TEMP_SOURCE.TEMPORARY%type;
TYPE g_GENERATED IS TABLE OF COLL_TEMP_SOURCE.GENERATED%type;
TYPE g_SECONDARY IS TABLE OF COLL_TEMP_SOURCE.SECONDARY%type;

g_OWNER_t g_OWNER;
g_OBJECT_NAME_t g_OBJECT_NAME;
g_SUBOBJECT_NAME_t g_SUBOBJECT_NAME;
g_OBJECT_ID_t g_OBJECT_ID;
g_DATA_OBJECT_ID_t g_DATA_OBJECT_ID;
g_OBJECT_TYPE_t g_OBJECT_TYPE;
g_CREATED_t g_CREATED;
g_LAST_DDL_TIME_t g_LAST_DDL_TIME;
g_TIMESTAMP_t g_TIMESTAMP;
g_STATUS_t g_STATUS;
g_TEMPORARY_t g_TEMPORARY;
g_GENERATED_t g_GENERATED;
g_SECONDARY_t g_SECONDARY;

g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source;

BEGIN
g_error_level := 1;
OPEN cur_coll_temp;

LOOP
g_error_level := 2;
FETCH cur_coll_temp BULK COLLECT INTO g_OWNER_t
,g_OBJECT_NAME_t
,g_SUBOBJECT_NAME_t
,g_OBJECT_ID_t
,g_DATA_OBJECT_ID_t
,g_OBJECT_TYPE_t
,g_CREATED_t
,g_LAST_DDL_TIME_t
,g_TIMESTAMP_t
,g_STATUS_t
,g_TEMPORARY_t
,g_GENERATED_t
,g_SECONDARY_t LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FORALL i IN g_owner_t.FIRST .. g_owner_t.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ ALL_ROWS */
g_OWNER_t(i) owner
,g_OBJECT_NAME_t(i) object_name
,g_SUBOBJECT_NAME_t(i) subobject_name
,g_OBJECT_ID_t(i) object_id
,g_DATA_OBJECT_ID_t(i) data_object_id
,g_OBJECT_TYPE_t(i) object_type
,g_CREATED_t(i) created
,g_LAST_DDL_TIME_t(i) last_ddl_time
,g_TIMESTAMP_t(i) timestamp
,g_STATUS_t(i) status
, DECODE(g_OBJECT_TYPE_t(i) , ‘JAVA CLASS’, ‘Y’, g_TEMPORARY_t(i)) TEMPORARY
,g_GENERATED_t(i) generated
,DECODE(g_OWNER_t(i), ‘SYS’, ‘Y’, g_SECONDARY_t(i)) secondary
FROM dual ) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
(tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);

COMMIT;

END LOOP;

g_error_level := 10;
CLOSE cur_coll_temp;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @merge_coll.sql;

Elapsed: 00:11:20.27

Cancelled the run –
Stopped after processing 58800 760475 records.

STEP 4 – MERGE + TABLE Script

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO min_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm,
ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 1
ORDER BY 1;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO max_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm, ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 0
UNION
SELECT /*+ ALL_ROWS */ MAX (ROWID)
FROM coll_temp_source;

g_error_level := 2;
FORALL i IN min_rid_tab.FIRST .. min_rid_tab.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ ALL_ROWS */
ctgs.OWNER OWNER
, ctgs.OBJECT_NAME OBJECT_NAME
, ctgs.SUBOBJECT_NAME SUBOBJECT_NAME
, ctgs.object_id object_id
, ctgs.data_object_id data_object_id
, ctgs.OBJECT_TYPE OBJECT_TYPE
, ctgs.CREATED CREATED
, ctgs.LAST_DDL_TIME LAST_DDL_TIME
, ctgs.TIMESTAMP TIMESTAMP
, ctgs.STATUS STATUS
, DECODE(object_type , ‘JAVA CLASS’, ‘Y’, ctgs.TEMPORARY) TEMPORARY
, ctgs.GENERATED GENERATED
, DECODE(ctgs.OWNER, ‘SYS’, ‘Y’, ctgs.SECONDARY) SECONDARY
FROM coll_temp_source ctgs WHERE rowid BETWEEN min_rid_tab(i) AND max_rid_tab(i)) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
( tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @merge_table.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:37.30
SQL>

Notes –
1. Merge based on TEMP table is way faster for huge data set !!
2. FORALL is possible for MERGE statement
3. Collections can be used in MERGE – its ok for small sets of data. Use global temporary for large sets of data.

The above example for temporary table picks small chunks of data and processes the same. This is achieved by referencing ROWID.


February 21, 2010  9:14 AM

Performance Tuning – Collections Vs. Pipelined Table Function

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

PIPELINED Table functions enables to return sets of data when the next set of data is being processed. This is obviously better than normal TABLE functions. Because, normal TABLE functions picks and processes all the data at one shot then returns it – so its particularly useful for ETL (Extraction – Transformation – Load).

My opinion – For not too complex operations its better to use COLLECTIONS / GLOBAL TEMPORARY Tables rather than Pipelined Table functions. As the extraction / transformation and load is faster than via pipelined table functions. Following is a quick demonstration.

TABLE CREATION SCRIPTS

SQL> drop table coll_temp_source;

Table dropped.

SQL> CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level SQL>
Table dropped.

SQL>
Table created.

SQL> SQL>
SQL> drop sequence dummy_seq1;

Sequence dropped.

SQL> create sequence dummy_seq1 start with 1
2 /

Sequence created.

PIPELINED TABLE FUNCTION CREATION SCRIPT

SET TIMING ON

CREATE OR REPLACE FUNCTION pipe_fun (p_cursor IN SYS_REFCURSOR )
RETURN t_expected PIPELINED
PARALLEL_ENABLE (PARTITION p_cursor BY ANY)
AS
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;
l_row t_validate_row := t_validate_row(NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL,NULL);

BEGIN

LOOP
g_error_level := 2;
FETCH p_cursor BULK COLLECT INTO g_object_tab LIMIT 300;

FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP

g_error_level := 3;
l_row.OWNER := g_object_tab(i).OWNER ;
l_row.OBJECT_NAME := g_object_tab(i).OBJECT_NAME ;
l_row.SUBOBJECT_NAME := g_object_tab(i).SUBOBJECT_NAME ;
l_row.DATA_OBJECT_ID := g_object_tab(i).DATA_OBJECT_ID ;
l_row.OBJECT_TYPE := g_object_tab(i).OBJECT_TYPE ;
l_row.CREATED := g_object_tab(i).CREATED ;
l_row.LAST_DDL_TIME := g_object_tab(i).LAST_DDL_TIME ;
l_row.TIMESTAMP := g_object_tab(i).TIMESTAMP ;
l_row.STATUS := g_object_tab(i).STATUS ;
l_row.TEMPORARY := g_object_tab(i).TEMPORARY ;
l_row.GENERATED := g_object_tab(i).GENERATED ;
l_row.SECONDARY := g_object_tab(i).SECONDARY ;
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
l_row.object_id := v_object_id;

IF l_row.owner = ‘SYS’ THEN
l_row.secondary := ‘Y';
END IF;
IF l_row.object_type = ‘JAVA CLASS’ THEN
l_row.temporary := ‘Y';
END IF;
PIPE ROW (l_row);
END LOOP;
EXIT WHEN p_cursor%NOTFOUND;
END LOOP;
CLOSE p_cursor;
RETURN;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @pipe_func.sql

Function created.

Elapsed: 00:00:00.10
SQL>
SQL> INSERT INTO COLL_TEMP_TARGET5
SELECT *
FROM TABLE(
pipe_fun (
cursor(SELECT * FROM COLL_TEMP_SOURCE))) 2 3 4 5 ;

761125 rows created.

Elapsed: 00:01:38.91
SQL>

COLLECTIONS

SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT *
FROM coll_temp_source;

BEGIN
g_error_level := 1;
OPEN cur_coll_temp;

LOOP
g_error_level := 2;
FETCH cur_coll_temp BULK COLLECT INTO g_object_tab LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
IF g_object_tab(i).owner = ‘SYS’ THEN
g_object_tab(i).secondary := ‘Y';
END IF;

IF g_object_tab(i).object_type = ‘JAVA CLASS’ THEN
g_object_tab(i).temporary := ‘Y';
END IF;
END LOOP;

FORALL i IN g_object_tab.FIRST .. g_object_tab.LAST
INSERT INTO coll_temp_target5 VALUES g_object_tab(i);

COMMIT;

END LOOP;

g_error_level := 10;
CLOSE cur_coll_temp;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error level is – ‘ || g_error_level );
dbms_output.put_line(‘Error message is – ‘ || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @collections.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:58.99
SQL>


February 21, 2010  9:14 AM

Performance Tuning – FOR LOOP EXECUTE IMMEDIATE VS FORALL EXECUTE IMMEDIATE

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Usually FORALL is possible only with DML operations (Insert / Update / Delete / Merge). From Oracle 9i it is possible with EXECUTE IMMEDIATE.

This is just for example purpose only – FORALL is really useful for bulk processing. Though in the below example a direct FORALL – INSERT is possible, have used EXECUTE IMMEDATE to demonstrate its use.

TABLE CREATION SCRIPTS

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum < 201

drop table coll_temp_target

CREATE TABLE coll_temp_target AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum = 5

STEP 1 – USING FORALL

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;

FORALL i IN 1 .. status_tab.count
EXECUTE IMMEDIATE
‘INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)’
USING status_tab(i), object_id_tab(i) , owner_tab(i);

END;
/

STEP 2 – USING FOR.. LOOP

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum @exec.sql

PL/SQL procedure successfully completed.

SQL> select * from coll_temp_target;

STATUS OBJECT_ID OWNER
——- ———- ——————————
VALID 79400 SYS
VALID 69022 SYS
VALID 84837 SYS
VALID 75686 SYS
VALID 84402 SYS
VALID 71636 SYS
VALID 85043 SYS
VALID 85098 SYS
VALID 71312 SYS
VALID 73222 SYS

10 rows selected.


February 21, 2010  9:13 AM

Read Hexa-decimal file via External table

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

External table was introduced in Oracle 9i which can be used to read file from ORACLE DIRECTORY. Multiple files can be read at the same time. It can also read hexa-decimal files. The following example demonstrates the use of external table to read from hexa-decimal file.

TEST : External table to read from hexa-decimal delimited with different hexa-decimal values.

FILE

Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3

create or replace directory ext_dir as ‘C:\’

drop table tp_emp_ext;

create table tp_emp_ext
(Emp_name_field varchar2(200),
Emp_name_value varchar2(200),
Dept_id_field varchar2(200),
Dept_id_value varchar2(200))
organization external (
type oracle_loader
default directory ext_dir
access parameters ( records delimited by newline
nodiscardfile
nologfile
fields terminated by 0X’11’
missing field values are null
REJECT ROWS WITH ALL NULL
FIELDS
(Emp_name_field char(200) terminated by 0X’02’,
Emp_name_value char(200) terminated by 0X’03’,
Dept_id_field char(200) terminated by 0X’02’,
Dept_id_value char(200) terminated by 0X’03’)
)
location (‘samp_text.dat’)
)
reject limit unlimited

select * from tp_emp_ext

EMP_NAME_FIELD EMP_NAME_VALUE DEPT_ID_FIELD DEPT_ID_VALUE

Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: