truncating a empty table [message #647844] |
Tue, 09 February 2016 23:26 |
|
arudesai
Messages: 7 Registered: July 2015
|
Junior Member |
|
|
Hi,
I am executing a package for a date range suppose for 1 month .. it has used table 'ABC' which truncats and loads every time. below is the secenerio
1) truncate table 'ABC' used run the package for date range 1 month, execution time is 1 min 17 sec. records in table 37000
2)truncate tables 'ABC used run the package for date range 1 day, execution time 5 sec. records in table 0
3)run the package for date range 1 month without truncat 'ABC (as table is empty), execution time 1 hr 56 min records in table 37000
can some one help me understand oracle behavior here.
Thanks,
Aru
[Updated on: Wed, 10 February 2016 00:32] Report message to a moderator
|
|
|
|
Re: truncating a empty table [message #647865 is a reply to message #647844] |
Wed, 10 February 2016 07:03 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
>> used table 'ABC' which truncats and loads every time
Are the stats updated everytime?
Truncate will leave the stats as-is.
With stale stats, optimizer could get delirious.
Try again with the updated stats and observe.
Then try ThomasG's recommendation. The best way to check what Oracle does in terms of measurable units.
[Updated on: Wed, 10 February 2016 07:04] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: truncating a empty table [message #647929 is a reply to message #647902] |
Thu, 11 February 2016 12:35 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Just curious, what are you loading the table for? How are you loading it (sqlldr, a procedure?) If it is the same query, have you thought about using a materialized view that is refreshed on a daily schedule?
|
|
|
|
|
Re: truncating a empty table [message #647950 is a reply to message #647933] |
Fri, 12 February 2016 07:27 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Setup a DBMS_JOB or a DBMS_SCHEDULER job to run once a month on the day and time you want. Middle of the night is cool and do something likee
delete from my_table;
insert into my_table
select col1,col2,col3...
from ...
where ...;
commit;
This way it doesn't matter how long it takes. Run it on the off shift time and the existing data isn't replaced until the commit is issued.
|
|
|
|
Re: truncating a empty table [message #647980 is a reply to message #647895] |
Sat, 13 February 2016 06:57 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
arudesai wrote on Thu, 11 February 2016 09:03just for curiosity.. what would be impact of analyze command My understanding of this (which may be incorrect - I haven't bothered to validate it) is that ANALYZE may destroy the more sophisticated statistics (particularly histograms) that DBMS_STATS may have gathered.
|
|
|
Re: truncating a empty table [message #648023 is a reply to message #647980] |
Mon, 15 February 2016 02:07 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
John Watson wrote on Sat, 13 February 2016 12:57arudesai wrote on Thu, 11 February 2016 09:03just for curiosity.. what would be impact of analyze command My understanding of this (which may be incorrect - I haven't bothered to validate it) is that ANALYZE may destroy the more sophisticated statistics (particularly histograms) that DBMS_STATS may have gathered.
Yup.
Still the only way to get chained rows though, as far as I know. Which is a bummer.
|
|
|