Rewriting SP to CTAS to improve performance

Business Requirement

We have user tables which is used to store user and privilege information. Column AMM_FNCTY_BIT_STREAM is RAW(1025) and the BITS in the first 1024 bytes ( namely 8192 bits) represents 8192 different privilege flags. Now we need to decode the privilege column into different rows. In other words, if the bit flag in AMM_FNCTY_BIT_STREAM is 1, we need to insert one row into our new table. Therefore we need to generate 8192 rows for a single user in the worst case, where each flag is set to 1.

Oracle 11.2.0.3.11 RAC
AIX 7.1
user table : 86238 rows

Original Solution

row-by-row, commit the current transaction after inserting each 50 rows. This could execute for nearly 8 hours.

LOOP
FETCH CurUserSrno INTO UserSrno;
EXIT WHEN CurUserSrno%notfound;
EXECUTE IMMEDIATE InsertSql using UserSrno;
var_check := var_check+1;
if mod(var_check,50)=0 then
commit;
end if;
END LOOP;

Flag decode:

BITAND(TO_NUMBER(DBMS_LOB.SUBSTR(AMM.AMM_FNCTY_BIT_STREAM,
1,
CEIL((MMM.MMM_BIT_INDX_VALUE) / 8)),
'XX'),
POWER(2, MOD(MMM.MMM_BIT_INDX_VALUE - 1, 8))) <> 0

Improved Version

1. changed to CTAS ( Create Table As Select)
2. nologging table with parallelism set to 5
3. changed flag decode function to avoid type conversion
4. The SQL could complete within 1 hour, which is a significant performance improvement

create table CAOFF.PROC_USER_PRVLGE_DTLS nologging parallel 5
AS
WITH AMM AS
(
	SELECT 	AMM_ACSS_LVL_SRNO,
		AMM_UPDTD_DATE,
		AMM_CRTD_DATE,
		AMM_MKT_SRNO,
		AMM_TRDNG_MTHD_SRNO,
		AMM_ACSS_LVL_INDC,
		AMM_FNCTY_BIT_STREAM
	FROM ONL1.TRDX_ACSS_MENU_MPNG_MASTER 
	WHERE SUBSTR(AMM_ACSS_LVL_SRNO,6,1) IN (0,1,2,3,4,5,6,7,8,9) 
),
MMM AS
(
	SELECT DISTINCT MMM_BIT_INDX_VALUE 
	FROM ONL1.TRDX_MAIN_MENU_MASTER
)
SELECT /*+ PARALLEL(AMM 5) */  AMM.AMM_ACSS_LVL_SRNO AS USER_SRNO,
         MMM.MMM_BIT_INDX_VALUE AS PRIVES_SRNO,
         AMM.AMM_UPDTD_DATE AS START_DATE,
         TO_DATE('99981231', 'YYYY-MM-DD') AS END_DATE,
         AMM.AMM_CRTD_DATE,
         AMM.AMM_UPDTD_DATE,
         0  AS PREVILAGE_ST
    FROM AMM, MMM
   WHERE AMM.AMM_MKT_SRNO = 0
     AND AMM.AMM_TRDNG_MTHD_SRNO = 0
     AND AMM.AMM_ACSS_LVL_INDC = 1
     AND UTL_RAW.BIT_AND(
		AMM.AMM_FNCTY_BIT_STREAM,
		HEXTORAW(
			RPAD(
				LPAD(
					TO_CHAR(
						POWER(2, MOD(MMM.MMM_BIT_INDX_VALUE - 1,4))
					),
					CASE WHEN MOD(FLOOR((MMM.MMM_BIT_INDX_VALUE-1)/4),2)=0
							THEN
									FLOOR((MMM.MMM_BIT_INDX_VALUE-1)/4)+1+1
							ELSE
									FLOOR((MMM.MMM_BIT_INDX_VALUE-1)/4)-1+1
					END,
					'0'
				),
				2050,
				'0'
			)
	  	)
          )  HEXTORAW(LPAD('0',2050,'0'))
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s