Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, satya.

Asked: March 10, 2017 - 10:52 am UTC

Last updated: March 18, 2017 - 4:03 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom ,

Good Afternoon .

I have one stored procedure which is running for long time . Could you please suggest how to optimize the code .

There are having four tables.
1.staging_product
2.stagin_retail
3.product
4.curr_retail

The procedure descripts :

In staging_product there are columns having :

LOADED_TIMESTAMP,
RECORD_STATUS,
SCAN_CODE_ID,
PRODUCT_ENG_DESCRIPTION,
sPRODUCT_PSS_ID,
IMG_URI_TXT


In staging_retail there are columns having :

LOADED_TIMESTAMP,
RECORD_STATUS,
EFFECTIVE_DATE,
STORE_ID,
SCAN_CODE_ID,
SALE_PRICE,
SALE_PRICE_XFOR_QUANTITY,
SALE_WEIGHT_SW

In product there are columns having :

prod_eng_des
cust_prod_des
pss_dept_id
img_uri_txt
scn_cd_id

In curr_retl there are columns having :

curr_retl_prc
curr_x4_amt
wt_sw
rstr_sale_sw
str_nbr
scn_cd_id

In staging_product according to different loaded_timestamp column for the same scan_code_id there may be 1000 records and there are around 6lakhs of scan_code_id , 6 lakhs X 1000 = 6 lakhs thousand records in staging_product table .

I need to take the latest record from the staging_product table i.e the scan_code_id which is landed at the end and update to the product table if the scan_code_id is there in the product else need to insert into the product table.

Like wise there is another table where according to different stores need to update and insert.

from the staging_retail the latest record according to loaded_timestamp column for each store_id and scan_code_id and need to update the curr_retl table if the same store_id and scan_code_id exists else insert to the curr_retl table.

There are having 300 stores and each store having 6 lakhs scan_code_id , 300 X 6 lakhs = 1800 lakhs
and there are arround 1000 records for each store_id and scan_code_id according to different loaded_timestamp .
so 1800 lakhs X 1000 = 1800000 lakhs .

I have used bulk collect + limit + forall , but it is taking 20 minutes for process 2 millions record . Please suggest the best way to do this ....


Thank You.














and Connor said...

I'm looking into my crystal ball to see if I can see your source code from here in Perth

CRYSTAL_BALL

Nope...my crystal ball must be malfunctioning.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ensure

A reader, March 18, 2017 - 6:38 am UTC

Pls ensure your LOADED_TIMESTAMP' ball is set correctly 😊

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library