Re: Optimize Table

On Feb 1, 11:10 am, mansoor..._at_gmail.com wrote:
> On Feb 1, 9:38 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
>
>
>
> > <mansoor..._at_gmail.com> wrote in message
>
> >news:3f65d008-6ee3-49d7-95ff-00c20d9dcc53_at_s12g2000prg.googlegroups.com...
>
> > > On Jan 31, 8:35 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > >> On Jan 31, 8:56 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
>
> > >> > <mansoor..._at_gmail.com> wrote in message
>
> > >> >news:b31143cc-e006-4b0a-9d21-dc36a2550c3f_at_y5g2000hsf.googlegroups.com...>
> > >> >There is command 'Optimize Table' in MySQL. What is the equivalent of
> > >> > > this in Oracle?
> > >> > > And if none, how to optimize an Oracle (10g) table?
>
> > >> > > Thx.
>
> > >> > What does optimize table in your sql do?
> > >> > Jim
>
> > >> Had one decided to use google.com one would have found this:
>
> > >>http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html
>
> > >> wherein the OPTIMIZE TABLE command is explained. Basically it repairs
> > >> split (chained/migrated) rows, compacts free space, repairs indexes
> > >> and updates table statistics. Thus, in 10.2 and later releases, which
> > >> create statistics automagically, an
>
> > >> alter table <tablename> move;
>
> > >> followed by an
>
> > >> alter index <indexname> rebuild;
>
> > >> would perform similar 'magic'. Of course the antiquated exp/drop/imp
> > >> scenario could still be used to 'optimize' the table. In either case
> > >> the table is unavailable for use during the operation as both products
> > >> lock the object for the duration of the process.
>
> > >> David Fitzjarrell
>
> > > Will the alter table <tablename> move; command work without specifying
> > > any tablespace name?
>
> > > Also how would you rebuild all the indexes of a table without
> > > specifying or knowing the names of indexes.
>
> > > I'm searching google but so far haven't found anything.
>
> > > Thanks
>
> > There are ways but why? What problem are you trying to solve?
> > Jim- Hide quoted text -
>
> > - Show quoted text -
>
> I need to convert a MySQL script to ORACLE. It has a "Optimize Table
> <tablename>" statement in there.

Just remove this statement.

> It also has "INSERT IGNORE..." which means it ignores unique key
> constraints and inserts a row anyway.
>

Why would anyone in their sane mind want to allow bad data to be loaded into their database? If a unique constraint is there, it's there for a reason - yet you want to ignore it and load whatever garbage there is without any validation. If this data is going to be "fixed" somehow later, you could employ DEFERRABLE INITIALLY DEFERRED unique constraint on the table in question, so that Oracle will insert whatever you tell it to into the table and "ignore" the constraint until the transaction ends, but it will still validate the data against the constraint on commit (and will force you to rollback or fix the data if validation fails.)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Feb 01 2008 - 03:29:38 CST