Re: Add/Drop partition and CBO statistics

I've got a perfect application for partitioning by date. Each "month" is a new batch of data and everything is set for the last date of the month.

But they asked me today, if we drop a partition of old data, and then add a new partition for the next month, load it, what needs analyzed?

Is it enough to simply analyze the new partition?

--
13308 Thornridge Ct
Midlothian, VA  23112
804-744-1545


> Reply in lines.

> 

> Zhu Chao

> ----- Original Message ----- 

> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>

> Sent: Wednesday, December 03, 2003 7:29 PM

> 

> 

> > Agreed that scanning one big index is faster than many partitions.

> > 

> > Then raises the questions - I thought partitioning is for:

> > 1) ease of archiving/dropping off old partitions - drop old and create new

> > partitions in a sliding window. A single large global index negates a lot of

> > this ease - even though it is true that deletes on non-partitioned tables

> > would be even more inconvenient.

>     Yes, that is why we use local index.not Global indexes.

> 

> > 2) efficiencies in partition pruning for queries. If you are "querying whole

> > table" - why bother with partitioning?

> > The point then becomes you don't need to partition in the first place, or

> > your partitioning scheme is not appropriate?

>     Sometimes it is constrained by complex real  applications. A table has tens 

> of columns and you can only partition by one key(or several columns), and to use 

> partition elimination, the SQL must contains the partition key. So only these 

> limited SQL can use partition pruning. While in complex real life application, 

> there will always SQL with different where clause that do not use the partition 

> key at all.

>     The other constraint is business logic.We should partition according to 

> product online time, but we have ten tables to archive and only one 

> table(products) has that key, all other tables do not have that column. Adding 

> such a column to other tables need considrable application rewrite and is 

> denied. So we use product_id(the primary key of most tables) as the partition 

> key.    

>      

> > > When partitioning key is not a part of the index and you are querying

> > whole table, then it is faster to scan one big index than many smaller ones.

> > The difference is something like log rows to partcount*log (rows/partcount).

> > >

> > > > BTW, local indexes are the only way to go -- I've never

> > > > understood the point

> > > > of global indexes on partitioned tables -- maybe someone else can?

>     Global indexes are faster than local index, so if you have schedule down 

> time and need better performance, go to global index. OLTP is more suitable for 

> GLobal index, tomas kyte said in his expert one on one.

> 

> Regards

> Zhu Chao.

> 

> > Please see the official ORACLE-L FAQ: http://www.orafaq.net

> > -- 

> > Author: Binley Lim

> >   INET: Binley.Lim_at_xtra.co.nz

> > 

> > Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> > San Diego, California        -- Mailing list and web hosting services

> > ---------------------------------------------------------------------

> > To REMOVE yourself from this mailing list, send an E-Mail message

> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> > the message BODY, include a line containing: UNSUB ORACLE-L

> > (or the name of mailing list you want to be removed from).  You may

> > also send the HELP command for other information (like subscribing).

> > 

> > 

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> -- 

> Author: zhu chao

>   INET: chao_ping_at_vip.163.com

> 

> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: mkline1_at_comcast.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).