Re: Using Partitions With Date Range Question

David and Jonathan,

Thanks for your thoughts, comments and ideas. I still think the use of partitions over using
one Oracle7 type table where my deletes would probably take quite a bit of time and would
probably require a rebuild of the indexes is the way to go.

If I was to read these correctly, I should be creating one index whose first column is the date
used to partition the table. All other indexes, although created as local, need not start with
this date.

I'm leaning toward maintaining a smaller amount of partitions that the 5 year's worth I mentioned
initially. My thought is that I got to run something on a monthly basis to archive and then delete
the partition containing the 14 month old data, so I may as well create the new partition as well.
I might use some sort of buffer where I'll always ensure that I'm 3 months ahead of the game (i.e.
if today Jan 1, then create partition for month of April). This way, if something goes wrong, I don't
have to know about at 3AM the same day.

Thanks again for your comments.

Jake.
<ddf_dba_at_my-deja.com> wrote in message news:8iltor$6f$1_at_nnrp1.deja.com...
> 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.
Received on Mon Jun 19 2000 - 00:00:00 CDT