Re: Optimize Table

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

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 Received on Fri Feb 01 2008 - 05:22:17 CST