Re: Question about partitioning...

Konstantinos Agouros wrote:
> Hi,
>
> I have the following problem: I have a table into which every day a few million
> lines are added using sqlldr in direct mode. I do some work on the data and
> two days later it is deleted. Currently this is done with delete from table
> where date < the_day_before_yesterday.
> This of course is terribly slow (Sun E450 running Oracle 8.1.6).
> So the answer for this would be to partition the table by day and create a
> new partition for each day and then instead of delete from drop partition.
> But: Since sqlldr in directmode is very fast but also ignores some things
> (like free blocks in the middle of a datafile) will the partitions be honored?
> When do I have to create new partitions before or after the sqlldr-command?
>
> Konstantin

G,day. Yes you can sqlload into partitions, and you do need to define the partitions before loading.

Have a look at case 8 in the $ORACLE_HOME/rdbms/demo directory. The sqlload instructions in the oarcle manuals (Utilities) are pretty comprehensive.

One thing to bear in mind is that adding/dropping partitions can have nasty consequence for any indexes that are defined on the tables and NOT declared as LOCAL. (i.e. not partitioned on the same basis as the underlying tables. ).. If you have a GLOBAL index on a table and don't rebuild it after a partition drop or add, you'll end up with an index in an unsable state. Given your slow machine, you'll need to go down the LOCAL route.

One trick often used especially when the sqlload will actually take a significant enough time, is to declare a new partition on the target table with a one-block extent, but also create a new temporary table wit the same layout, do the sqlload into this temporary table, then do block swap by means of the EXCHANGE command. The stuff you've loaded now belongs to the target table, and the temporary table with its one empty block can be dropped out of the way.

Regards
John S Findlay. Received on Mon Aug 12 2002 - 12:39:54 CDT