Re: Optimize Table

On Feb 1, 3:22 pm, mansoor..._at_gmail.com wrote:
> 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 -
>
> Sorry. Insert Ignore still doesn't insert the duplicate row but it
> doesn't throw an error (ignores it) so the script can go on.
> Is there any equivalent in PL/SQL or u have to use exceptions?
>
> thx- Hide quoted text -
>
> - Show quoted text -

Ok got it from internet. DML ERROR LOGGING in oracle 10g

One more thing. How do u load a text file ( feilds delimited by " and rows by newline) into a given table.
I know external table is involved but dont you have to specify the columns in the CREATE TABLE statement.
I cant do that because I dont know the columns. How to create the external table from a given table and read data from the text file to it.

Thx. Received on Fri Feb 01 2008 - 04:49:32 CST