Re: Partitioning tables in Oracle8

larionov_at_yahoo.com wrote in article
<6kqfdt$93p$1_at_nnrp1.dejanews.com>...
>
>
> So if there is no index on the table,
> an insert would cause full table scan?
> Otherwise how would Oracle know what partition should the new row
go into?
>

The syntax for partition tables includes an upper bound on each partition:

        .... values less than (xxxxx)
The upper bound of the previous partition is also the implicit lower bound
of the current table.

Oracle keeps these bounds in the dictionary cache, so that when a row has to be inserted it can check the row content against the list of bounds in the cache and select the correct table.

> >
> > Performance can be dramatically improved if your
> > partition is correctly selected.
> >
>
> I understand it is very beneficial for selects. How about
> inserts, updates and deletes?
> How many additional constraints are imposed on the table/partitions
> and how would it slow down inserts and updates?
>

I don't have any strong figures on this against realistic data set yet.
However, When I tried to do a direct load against I partition table in 8.0.3 it kept crashing the session.

All the major jobs I have done with 8 to date have done inserts to explicitly named partitions because of my assumptions of the overhead on checking the constraints. Give me two weeks though, I am just starting a big performance test (heading for 2M rows per hour) on a 10-partition table. Received on Sun May 31 1998 - 03:25:25 CDT