Home » RDBMS Server » Performance Tuning » Insert from 2 million rows
Insert from 2 million rows [message #217490] Fri, 02 February 2007 09:08 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi,

I have following table which has gathered statistics

SQL> select INITIAL_EXTENT, NEXT_EXTENT, num_rows,blocks,avg_row_len,sample_size
2 from dba_tables
3 where table_name='SMCBOM_LH_DATE_TEMP'
4 ;

INITIAL_EXTENT NEXT_EXTENT NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE
-------------- ----------- --------- --------- ----------- -----------
163840 3710976 2184650 33984 107 2184650

it has about 2 million rows. Sample Data for above table is as following

ALLOY	PLANNER_CODE	OPERATIONSEQ	DEPARTMENT_CLASS	DEPARTMENT	LOAD_HOUR	OFFSET_DATE
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/19/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/20/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/21/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/22/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/23/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/24/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/25/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/26/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/27/2007
INCONEL alloy 625	WIRE ROD	150	CD	WP	8.45402793	3/28/2007


I need to execute following sql and insert data into another table

SELECT /*+ ALL_ROWS */  DEPARTMENT_CLASS,
                        DEPARTMENT,
                        SMCBOM_FLEX_BUDGET.GET_PERIOD(OFFSET_DATE),
                        SUM(LOAD_HOUR)
FROM     SMCBOM.SMCBOM_LH_DATE_TEMP
GROUP BY DEPARTMENT_CLASS,
         DEPARTMENT,
         SMCBOM_FLEX_BUDGET.GET_PERIOD(OFFSET_DATE)

SMCBOM_FLEX_BUDGET.GET_PERIOD is package function which gives me GL period for OFFSET DATE. 


The first time i executed with some sample of data it took almost 30 mins but i have killed the session. I need your advise on how can i select data with the function and insert into another table with some faster throughput.

Thanks in advance
Re: Insert from 2 million rows [message #217501 is a reply to message #217490] Fri, 02 February 2007 09:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does this function do (ie what is the code behind it): SMCBOM_FLEX_BUDGET.GET_PERIOD(OFFSET_DATE)

I suspect that that is the cause of your problems. Even if that function executes in 0.01 seconds, calling it 2,000,000 times will still add 5.5 hours to the length of your query.
Re: Insert from 2 million rows [message #217520 is a reply to message #217501] Fri, 02 February 2007 11:18 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi,

Thanks for the reply.

I have replace the function with sub select.

Actually it takes the offset date and finds the period value from another table.


SELECT PERIOD_NAME              
FROM GL_DATE_PERIOD_MAP
WHERE  trunc(accounting_date) = v_date


but still the operation takes long time....

Re: Insert from 2 million rows [message #217542 is a reply to message #217520] Fri, 02 February 2007 12:40 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
This has worked as wonder. Just took 39 Seconds...

select 
   sm.data_set_name,
   sm.department_class,         
   sm.department,       
   per.period_name,   
   sum(sm.load_hour),
   -1,
   sysdate,
   -1,
   sysdate       
from smcbom.smcbom_lh_date_temp sm,
     gl_date_period_map per
where trunc(per.accounting_date) = sm.offset_date
group by 
   sm.data_set_name,
   sm.department_class,         
   sm.department,       
   per.period_name

381 rows selected.

Elapsed: 00:00:39.56

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (GROUP BY)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'GL_DATE_PERIOD_MAP'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'SMCBOM_LH_DATE_TEMP'




Statistics
----------------------------------------------------------
          9  recursive calls
        133  db block gets
      25719  consistent gets
      42520  physical reads
         0  redo size
     42766  bytes sent via SQL*Net to client
      3192  bytes received via SQL*Net from client
        27  SQL*Net roundtrips to/from client
         2  sorts (memory)
         1  sorts (disk)
       381  rows processed
Previous Topic: Basic question in INDEXing
Next Topic: Full table scan
Goto Forum:
  


Current Time: Fri Dec 13 00:34:25 CST 2024