Insert into partitioned tables and specifying the partition name
- From: amonte <ax.mount@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 10 Apr 2010 01:03:30 +0200
Hello
I wonder if is a general practice in a DWH when loading data into
partitioned tables using INSERT.. SELECT the partition name should be used
in the INSERT?
We have seen performance inestabilities when we do INSERT .. SELECT without
specifying the partition name. It is normal because there is an extra step
to calculate which partition to go but sometimes it is just too slow. For
example when inserting 25 million rows to a heap table we get constant 4
minutes. When inserting same number of rows into a 500 partitions table (the
data only goes to3 partitions) it sometimes runs in 15 minutes and sometimes
40. In the case of 40 minutes we observe the parallel slaves's physical
reads increase much slower (event PX Deq Credit: send blkd).
When specifying the partition name we get stability as well, not as fast as
compared to heap table but at least the time is predictable. So I wonder if
others specifies the partition name when doing mass loading?
TIA
Other related posts:
