Re: Add/Drop partition and CBO statistics

Hi list:

    I did another add partition last monday evening, and that time everything is ok. Maybe statistics did not change much(like high value in tables/indexes).     I will do more research and give feedback to you .     And I think there must be other DBAs also using partitions, why not share your pains and gains?

Regards.
Zhu Chao.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, December 04, 2003 4:49 AM

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

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