Re: changing data definition

nah only unrecoverable ... we would like to have some redo if possible ...

so i guess direct insert + no logging is faster than create as select * unrecoverable ... but i think with nologgin option create will prb be a bit faster ...

but like i said create can't convert number types .. say number to number(10,2)

gives conversion errors....

--ray

"Christopher M. Day" wrote:
>
> Ray,
>
> Did you try using create table as select * ..with the nologging or
> unrecoverable option as the comparison to your pl/sql skeleton ?
>
> Chris
>
> Ray Wang wrote:
> >
> > yeah, i've written up pl/sql to create skeleton of the table and do
> > direct insert with nologgin option ...
> > turns out to be faster than create table as select *
> >
> > only drawback is that redo is not written but thats not a big deal in
> > this situation.
> >
> > thanks anyway
> >
> > --ray
> >
> > alanlee88_at_my-dejanews.com wrote:
> > >
> > > The only way that I can think of is to create a temporary table with exactly
> > > the same definition and populate it with all the data. Empty that column and
> > > change the precision (alter table modify..blah..) and do a insert into
> > > original_table (select * from tmp_table where key1=key2..).
> > >
> > > Hope it helps.
> > >
> > > Alan Lee
> > > BSI Consulting
> > > Houston, Texas
> > >
> > > In article <3709DC81.93C858EB_at_student.unsw.edu.au>,
> > > Ray Wang <z2156808_at_student.unsw.edu.au> wrote:
> > > > how can i alter a columns type from number to number(15,2)
> > > >
> > > > of course the column is populated .... i need a way to make this
> > > > conversion without emptying the columns.
> > > >
> > > > say
> > > >
> > > > alter table tablename ( id number(15,2));
> > > >
> > > > id used to be number
> > > >
> > > > oracle would spit out cannot decrease precision ... blah blah.
> > > > i understand the inconsistency involved but is there any way around this
> > > > problem?
> > > >
> > > > i know i can spool to a flat file and do a direct load .
> > > > or
> > > > create a skeleton and insert
> > > >
> > > > both of which will have a time penalty as opposing to
> > > > just alter.
> > > >
> > > > is there anyway to do a create table that can do the data type
> > > > conversion implicitly?
> > > >
> > > > bear in mind i am altering thecolumn type here not data type ...
> > > >
> > > > any help will greatly appreciated.
> > > >
> > > > thanks
> > > >
> > > > --ray
> > > >
> > >
> > > -----------== Posted via Deja News, The Discussion Network ==----------
> > > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Apr 12 1999 - 08:03:07 CDT