Re: Using Partitions With Date Range Question

In article <961226216.10391.0.nnrp-10.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> David,
>
> I think the question requires a lot more
> detailed investigation.
>

Without question.

> 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.
>

Correct.

> 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.
>

True.

> You seem to imply that all indexes
> will have to be locally prefixed -

Never did I say or imply that. What I said was:

"The only performance loss you should experience is on queries that do not utilize the local prefixed index on the partitioned table."

Notice I said "the local prifixed index". This implies one, not many, indexes. My statement is true since Oracle will scan all partitions for data if the local prefixed index is not used. One local prefixed index is all that is needed; I never stated otherwise. All other indexes can be local non-prefixed to allow other queries to utilize index scans rather than table scans to return data.

> 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.
>

Precisely the reason for the local prefixed index. Utilization of this index should prevent scanning of unrelated partitions, thus eliminating the extraneous I/O such searches would require.

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

Yet another point I made in answering the third question. I believe that you misread or misinterpreted my response to this posting. I hope that this has cleared up any misunderstanding you may have.

> --
>
> 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.
> >
>
>

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.