| Insert from 2 million rows [message #217490] |
Fri, 02 February 2007 09:08  |
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  |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
