Re: Using Partitions With Date Range Question

David,

I think the question requires a lot more detailed investigation.

If the optimizer does things correctly,
and if the queries are the right sort of queries, and if the indexes are suitably designed, then performance will not
suffer.

BUT .. If the extra 45 or so partitions are
accidentally analyzed, Oracle may
be sufficiently mislead by the 'average' size of the partitions to use silly plans.

You seem to imply that all indexes
will have to be locally prefixed -
whilst I agree with the locality, the
pre-fixing is pretty undesirable for
various reasons - not the least being
the need for EVERY query to include
the partitioning date column so that
the index can be used at all.

The point about the overhead on having
excess partitions is also potentially
misleading. It is correct if the data accesses are always large but if most of the data access is high-precision the case if
very different - a very pointed query, to find just 3 rows say, from a single table should complete in about 12 logical I/Os. If you have redundant partitions to
check, each partition test will take a
minimum of 2 logical I/Os - an over head of 26 logical I/Os for 13 partitions, and 120 logical I/Os for 60 partitions.

I would consider partitioning, but I
would be looking at rolling partition
maintenance.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

ddf_dba_at_my-deja.com wrote in message <8ie92h$deu$1_at_nnrp1.deja.com>...


>In article <alv25.822$j7.40494_at_news.bc.tac.net>,

>You asked

>

>> 1.Is there any performance loss if  I set up monthly partitions for

 the next


>> 5 years or so?  These partitions would be empty for some time, so

 would it


>> be better to set them up on a month-to-month basis?

>

>The only thing you will be consuming is the initial space for each

>empty partition.  Go ahead and set them up presuming you have enough

>space to do so; performance should not suffer.

>

>> 2. Is there a performance loss if a query goes across 2 or more

 partitions?


>> I'm hoping that the answer is no and that this would be the same as

 hitting


>> 2 or more extents but I can't seem to get much info on this.

>

>The only performance loss you should experience is on queries that do

>not utilize the local prefixed index on the partitioned table.  Then

>every partition will be scanned for the query data, and if you have 60

>partitions that could take a while.  Even the empty partitions will be

>accessed, and though it won't be a large portion of time in itself

>scanning 60 partitions when only 2 contain pertinent data is a waste of

>resources.

>