Home » SQL & PL/SQL » SQL & PL/SQL » insert approach
insert approach [message #186731] Wed, 09 August 2006 04:48 Go to next message
rambo13
Messages: 3
Registered: June 2006
Junior Member
I need to insert data into a table<IA_CLIENT_COST_OBJECT>.I have written the following query.Eache select statement fetches around
100,000 records.So if you check below there are 400,000 records.All the queries are almost similar except in each one we refer a different
view at one place 1)SMART_ALLOCS.TST_APAC_CCENTRE_REG_CODES
2)SMART_ALLOCS.TST_US_CCENTRE_REG_CODES
3)SMART_ALLOCS.TST_GER_CCENTRE_REG_CODES
4)SMART_ALLOCS.TST_UK_CCENTRE_REG_CODES

Please suggest me if there are any better approaches


INSERT INTO IA_CLIENT_COST_OBJECT

(SELECT TMP_VIEW.C_Client_Centre_ID_2
, TMP_VIEW.C_Client_Centre_ID
, TMP_VIEW.C_Service_ID
, DATA_SOURCES.SOURCE_NAME C_Service_Description
, TMP_VIEW.C_Data_Value
, TMP_VIEW.C_Units
, TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MM') EXTR_DATE
, 'ACTUAL' SCENARIO
, TO_CHAR(TMP_VIEW.MONTH_DATE, 'YYYY/MM/DD') MONTH_DATE
FROM DATA_SOURCES
, (SELECT INVENTORY_UPLOAD.cost_centre_code || '_' || INVENTORY_UPLOAD.source_id C_Client_Centre_ID_2
, INVENTORY_UPLOAD.cost_centre_code C_Client_Centre_ID
, INVENTORY_UPLOAD.source_id C_Service_ID
, SUM(INVENTORY_UPLOAD.data_value) C_Data_Value
, SUM(INVENTORY_UPLOAD.no_of_units) C_Units
, INVENTORY_UPLOAD.MONTH MONTH_DATE
FROM INVENTORY_UPLOAD, SMART_ALLOCS.TST_APAC_CCENTRE_REG_CODES
WHERE INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_APAC_CCENTRE_REG_CODES.cost_centre_code
AND INVENTORY_UPLOAD.MONTH = '01 Mar 2006'
GROUP BY INVENTORY_UPLOAD.cost_centre_code, INVENTORY_UPLOAD.source_id, INVENTORY_UPLOAD.MONTH
) TMP_VIEW
WHERE TMP_VIEW.C_SERVICE_ID = DATA_SOURCES.source_id
UNION
SELECT TMP_VIEW.C_Client_Centre_ID_2
, TMP_VIEW.C_Client_Centre_ID
, TMP_VIEW.C_Service_ID
, DATA_SOURCES.SOURCE_NAME C_Service_Description
, TMP_VIEW.C_Data_Value
, TMP_VIEW.C_Units
, TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MM') EXTR_DATE
, 'ACTUAL' SCENARIO
, TO_CHAR(TMP_VIEW.MONTH_DATE, 'YYYY/MM/DD') MONTH_DATE
FROM DATA_SOURCES
, (SELECT INVENTORY_UPLOAD.cost_centre_code || '_' || INVENTORY_UPLOAD.source_id C_Client_Centre_ID_2
, INVENTORY_UPLOAD.cost_centre_code C_Client_Centre_ID
, INVENTORY_UPLOAD.source_id C_Service_ID
, SUM(INVENTORY_UPLOAD.data_value) C_Data_Value
, SUM(INVENTORY_UPLOAD.no_of_units) C_Units
, INVENTORY_UPLOAD.MONTH MONTH_DATE
FROM INVENTORY_UPLOAD, SMART_ALLOCS.TST_US_CCENTRE_REG_CODES
WHERE INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_US_CCENTRE_REG_CODES.cost_centre_code
AND INVENTORY_UPLOAD.MONTH = '01 Mar 2006'
GROUP BY INVENTORY_UPLOAD.cost_centre_code, INVENTORY_UPLOAD.source_id, INVENTORY_UPLOAD.MONTH
) TMP_VIEW
WHERE TMP_VIEW.C_SERVICE_ID = DATA_SOURCES.source_id
UNION
SELECT TMP_VIEW.C_Client_Centre_ID_2
, TMP_VIEW.C_Client_Centre_ID
, TMP_VIEW.C_Service_ID
, DATA_SOURCES.SOURCE_NAME C_Service_Description
, TMP_VIEW.C_Data_Value
, TMP_VIEW.C_Units
, TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MM') EXTR_DATE
, 'ACTUAL' SCENARIO
, TO_CHAR(TMP_VIEW.MONTH_DATE, 'YYYY/MM/DD') MONTH_DATE
FROM DATA_SOURCES
, (SELECT INVENTORY_UPLOAD.cost_centre_code || '_' || INVENTORY_UPLOAD.source_id C_Client_Centre_ID_2
, INVENTORY_UPLOAD.cost_centre_code C_Client_Centre_ID
, INVENTORY_UPLOAD.source_id C_Service_ID
, SUM(INVENTORY_UPLOAD.data_value) C_Data_Value
, SUM(INVENTORY_UPLOAD.no_of_units) C_Units
, INVENTORY_UPLOAD.MONTH MONTH_DATE
FROM INVENTORY_UPLOAD, SMART_ALLOCS.TST_GER_CCENTRE_REG_CODES
WHERE INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_GER_CCENTRE_REG_CODES.cost_centre_code
AND INVENTORY_UPLOAD.MONTH = '01 Mar 2006'
GROUP BY INVENTORY_UPLOAD.cost_centre_code, INVENTORY_UPLOAD.source_id, INVENTORY_UPLOAD.MONTH
) TMP_VIEW
WHERE TMP_VIEW.C_SERVICE_ID = DATA_SOURCES.source_id
UNION
SELECT TMP_VIEW.C_Client_Centre_ID_2
, TMP_VIEW.C_Client_Centre_ID
, TMP_VIEW.C_Service_ID
, DATA_SOURCES.SOURCE_NAME C_Service_Description
, TMP_VIEW.C_Data_Value
, TMP_VIEW.C_Units
, TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MM') EXTR_DATE
, 'ACTUAL' SCENARIO
, TO_CHAR(TMP_VIEW.MONTH_DATE, 'YYYY/MM/DD') MONTH_DATE
FROM DATA_SOURCES
, (SELECT INVENTORY_UPLOAD.cost_centre_code || '_' || INVENTORY_UPLOAD.source_id C_Client_Centre_ID_2
, INVENTORY_UPLOAD.cost_centre_code C_Client_Centre_ID
, INVENTORY_UPLOAD.source_id C_Service_ID
, SUM(INVENTORY_UPLOAD.data_value) C_Data_Value
, SUM(INVENTORY_UPLOAD.no_of_units) C_Units
, INVENTORY_UPLOAD.MONTH MONTH_DATE
FROM INVENTORY_UPLOAD, SMART_ALLOCS.TST_UK_CCENTRE_REG_CODES
WHERE INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_UK_CCENTRE_REG_CODES.cost_centre_code
AND INVENTORY_UPLOAD.MONTH = '01 Mar 2006'
GROUP BY INVENTORY_UPLOAD.cost_centre_code, INVENTORY_UPLOAD.source_id, INVENTORY_UPLOAD.MONTH
) TMP_VIEW
WHERE TMP_VIEW.C_SERVICE_ID = DATA_SOURCES.source_id)
Re: insert approach [message #186832 is a reply to message #186731] Wed, 09 August 2006 15:07 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post the EXPPLAIN plan of your SELECT.
Re: insert approach [message #186861 is a reply to message #186731] Wed, 09 August 2006 22:49 Go to previous messageGo to next message
rambo13
Messages: 3
Registered: June 2006
Junior Member
I actually needed suggestions on the approach.We are actually planning what approach to take

The main suggestion I wanted from you is....which is better performance wise out of the below 2:

Approach1

A script containing 20 Insert into <stage table> select col1,col2,col3 etc from <source tables><join condns>.
Each of the Insert statement is pulling into different stage table i.e I am populating data for 20 stage tables.Some of the Insert statements have complex select queries.More than that each select may fetch around 100,000 records.That means after I have executed this insert file, I would have done20*100,000 records data transfer.

Approach 2
Write a pl/sql procedure for each table 2 do a bulk insert,that means around 20 procedures.Embed them in a package.We are loading data each month.So create another procedure(with a in parameter as DATAMONTH) inside the package which calls all other 20 procedures.

Which approach is better and faster.Please consider that Min 50,000 to Max 1,50,000 records in each fetch using Select.
Re: insert approach [message #186864 is a reply to message #186731] Wed, 09 August 2006 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>which is better performance wise out of the below 2:
Benchmark your own tests & report the results.
Re: insert approach [message #187612 is a reply to message #186731] Mon, 14 August 2006 12:41 Go to previous message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
I would combine all views into one unionied subquery.
That supposed to eliminate 4 scans/seeks of DATA_SOURCES and INVENTORY_UPLOAD.

INSERT INTO IA_CLIENT_COST_OBJECT
(SELECT TMP_VIEW.C_Client_Centre_ID_2
, TMP_VIEW.C_Client_Centre_ID
, TMP_VIEW.C_Service_ID
, DATA_SOURCES.SOURCE_NAME C_Service_Description
, TMP_VIEW.C_Data_Value
, TMP_VIEW.C_Units
, TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MM') EXTR_DATE
, 'ACTUAL' SCENARIO
, TO_CHAR(TMP_VIEW.MONTH_DATE, 'YYYY/MM/DD') MONTH_DATE
FROM DATA_SOURCES
, (SELECT INVENTORY_UPLOAD.cost_centre_code || '_' || INVENTORY_UPLOAD.source_id C_Client_Centre_ID_2
, INVENTORY_UPLOAD.cost_centre_code C_Client_Centre_ID
, INVENTORY_UPLOAD.source_id C_Service_ID
, SUM(INVENTORY_UPLOAD.data_value) C_Data_Value
, SUM(INVENTORY_UPLOAD.no_of_units) C_Units
, INVENTORY_UPLOAD.MONTH MONTH_DATE
FROM INVENTORY_UPLOAD
WHERE
exists (
select 1 from SMART_ALLOCS.TST_APAC_CCENTRE_REG_CODES
where INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_APAC_CCENTRE_REG_CODES.cost_centre_code
UNION ALL
select 1 from
SMART_ALLOCS.TST_US_CCENTRE_REG_CODES
where INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_US_CCENTRE_REG_CODES.cost_centre_code
UNION ALL
select 1 from
SMART_ALLOCS.TST_GER_CCENTRE_REG_CODES
where INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_GER_CCENTRE_REG_CODES.cost_centre_code
UNION ALL
select 1 from
SMART_ALLOCS.TST_UK_CCENTRE_REG_CODES
where INVENTORY_UPLOAD.cost_centre_code = SMART_ALLOCS.TST_UK_CCENTRE_REG_CODES.cost_centre_code
)
AND INVENTORY_UPLOAD.MONTH = '01 Mar 2006'
GROUP BY INVENTORY_UPLOAD.cost_centre_code, INVENTORY_UPLOAD.source_id, INVENTORY_UPLOAD.MONTH
) TMP_VIEW
WHERE TMP_VIEW.C_SERVICE_ID = DATA_SOURCES.source_id
Previous Topic: Reset Sequence
Next Topic: Function
Goto Forum:
  


Current Time: Fri Dec 09 17:24:45 CST 2016

Total time taken to generate the page: 0.20901 seconds