Re: Managing Large Tables

The beauty of partitioning is that, if it's done properly, there's no need to 'offline' the old_data partition, because the optimizer will automatically perform what's called 'partition pruning' -meaning that, if you've partitioned by year, let us say, then any query that includes a '...and where year=2002' will cause the optimizer to realise that it needn't bother scanning through partitions 2001, 2000 or 1999, because it knows it won't find anything of interest in them.

(Likewise, a query for something in 1999 won't trawl through all the '200x' partitions).

When you're automatically pruning out searches through millions of rows this way, you can expect the performance increase to be massive.

Regards
HJR

--
------------------------------------------
Resources for Oracle : www.hjrdba.com
============================


"anil chada" <Anil.Chada_at_oracle.com> wrote in message
news:70f5d1b6.0203261701.55c003eb_at_posting.google.com...


> Hi --

>

> We have about 10 tables which have lot of records (in millions) and

> these tables are causing performance problems.

> Most of the time users use only current year's information, so i am

> just thinking if we partition the tables and put current year's

> information in one partition and put remaining information in another

> partition

>

> My question is

> Can we somehow take the old year's partitioin off-line, so that oracle

> has to scan through this year's information only?

>

> does this approach help improve performance at all?

>

> I just want to get some ideas from you guys.

>

> Thanks

>

> Anil Chada