Re: Optimize Table

On Feb 1, 2:29 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

That is not my problem. My problem is to convert the script. Received on Fri Feb 01 2008 - 03:52:12 CST