| Problem sorting and inserting huge data (merged) [message #331290] |
Wed, 02 July 2008 23:04  |
ravashingravi
Messages: 7
Registered: March 2008
Location: India
|
Junior Member |
|
|
hi i have this query
INSERT /*+ append */ INTO UF_TAX
SELECT /*+ parallel(ta,2) */ ta.*,
DENSE_RANK () OVER (partition by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code
order by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code,
ta.tlg_tax_ent_seq_no )
as comp_portion_id
FROM TMP_UF_TAX ta;
its running for last 7 hours even after i removed
ORder by ta.ban,ta.seq_no
The table TMP_UF_TAX is huge containg 80 million records.
Please help me
|
|
|
|
|
|
| Problem sorting and inserting huge data [message #331297 is a reply to message #331290] |
Wed, 02 July 2008 23:27  |
ravashingravi
Messages: 7
Registered: March 2008
Location: India
|
Junior Member |
|
|
hi i have this query
INSERT /*+ append */ INTO UF_TAX
SELECT /*+ parallel(ta,2) */ ta.*,
DENSE_RANK () OVER (partition by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code
order by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code,
ta.tlg_tax_ent_seq_no )
as comp_portion_id
FROM TMP_UF_TAX ta;
I have created a index on
TMP_UF_TAX(TLG_BAN, TLG_TAX_ENT_SEQ_N0)
The table TMP_UF_TAX is huge containg 150 million records.
I want to insert the sorted data but it run out of TEMP space when i added ORDER BY.
its running for last 7 hours even after i removed
order by TLG_BAN,TLG_TAX_ENT_SEQ_N0
|
|
|
|
|
|
| Re: Problem sorting huge data [message #331308 is a reply to message #331290] |
Wed, 02 July 2008 23:44 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> its running for last 7 hours even after i removed
order by TLG_BAN,TLG_TAX_ENT_SEQ_N0
I do not see any ORDER BY clause in the posted statement.
As DENSE_RANK needs ORDER BY clause, I just doubt this statement does not end with error.
You select huge amount of data, so index is useles; it will every time end in full table scan and sort of all its rows for analytic result.
|
|
|
|
|
|
| Re: Problem sorting and inserting huge data [message #331374 is a reply to message #331330] |
Thu, 03 July 2008 03:41 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The DENSE_RANK does a sort no matter what.
So you are trying to sort AND insert 150 million rows. How long do you expect that to take?
Try JUST inserting 10 million rows WITHOUT the DENSE_RANK. That will give you a baseline. Say it is 30 minutes, so now you know that it will take 7.5 hours JUST to insert the data. But that doesn't count the sort.
Now you have to work out how long the sort will take. Try running:
SELECT *
FROM (
SELECT *
FROM TMP_UF_TAX ta
WHERE ROWNUM <= 10000000
ORDER BY ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code
order by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code,
ta.tlg_tax_ent_seq_no )
WHERE ROWNUM = 1;
The time this takes will be the time taken to sort 10M rows.
To get the A VERY ROUGH ESTIMATE of the probable time required for 150M rows, multiply by 15.
Add the two estimates together to get the total expected time. I think 7 hours is probably unreasonable.
If one of these estimates blows out to a very large number, then you can discuss PGA sizing with your DBA (for sorting performance) or disk I/O rates with your SysAdmin. But there is very little else you can do to tune this query. Sorting takes time, and IO takes time.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
