Re: Optimize Table

On Feb 1, 6:03 am, mansoor..._at_gmail.com wrote:
> On Feb 1, 4:22 pm, "news.verizon.net" <kenned..._at_verizon.net> wrote:
>
>
>
>
>
> > <mansoor..._at_gmail.com> wrote in message
>
> >news:065536bf-9f71-4281-a079-e86a1211a47a_at_s13g2000prd.googlegroups.com...
> > 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-Hidequotedtext -
>
> > > > - 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.
>
> > You don't need the optimize table statement to convert it to Oracle.  You
> > want to convert a text file with specific delimited characters without
> > knowing the file definition first.  Okay.  How do you choose the data types
> > for the columns?  Do you just make everything varchar2(4000)?
>
> > You can use sqlloader or an external table to get the data loaded, but you
> > need to give the definition of the columns, which ones are NULL, what are
> > their types are there any constraints. (primary key, unique and others)
>
> > You could certainly write something to take a file name, parse it out and
> > create a table.  (embedding your business rules) You could write it in
> > pl/sql using the utl_file package and dbms_sql package.  You could use APEX
> > which would give you a GUI to import a file.  It makes a guess and you can
> > define columns etc.  It is a wizard like approuch.
>
> > Jim- Hide quoted text -
>
> > - Show quoted text -
>
> The definition of columns is in the target table where I want to put
> the data. Lets say the columns in the text file match those of the
> table but I cant create an external table by defining those columns.
> Is there any other way to create an external table without defining
> columns? create the external table exactly like the target table. If
> its possible then I simply get the data inserted into the original
> table like
>
> insert into target_tbl
> select * from external_tbl
>
> If there is a simpler way without creating an external table that
> would be great.
>
> thx.- Hide quoted text -
>
> - Show quoted text -

SQL*Loader comes to mind ...

David Fitzjarrell Received on Fri Feb 01 2008 - 07:41:49 CST