Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!news.maxwell.syr.edu!deine.net!fu-berlin.de!uni-berlin.de!207.106.4.85!not-for-mail From: "Alan" Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server Subject: Re: Help: Re-Oraganizing a table Date: Thu, 4 Apr 2002 13:00:34 -0500 Lines: 77 Message-ID: References: <3CAA1367.E3971F0C@exesolutions.com> NNTP-Posting-Host: 207.106.4.85 X-Trace: fu-berlin.de 1017943043 30090812 207.106.4.85 (16 [114862]) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Xref: easynews comp.databases.oracle.misc:79672 comp.databases.oracle.server:142066 X-Received-Date: Thu, 04 Apr 2002 10:55:09 MST (news.easynews.com) You can't truncate a table if it has a foreign key constraint enabled. "damorgan" wrote in message news:3CAA1367.E3971F0C@exesolutions.com... > Sorry but I just have to ask? Did someone assign you to do something simple > in the most difficult convoluted way they could think of? > > Why not just go in and do the following: > > CREATE TABLE temp AS > SELECT * > FROM mytable; > > TRUNCATE TABLE mytable; > > INSERT INTO mytable > SELECT * > FROM temp > ORDER BY primary_key_fields_or_whatever; > > DROP TABLE temp; > > Of course five minutes later it won't be organized anymore. If this is a > requirement consider using an Index Organized Table (IOT). > > Daniel Morgan > > > > Mike F wrote: > > > I am writing a perl script which will re-organie a table by the primary > > key. I have two options > > > > First, > > a)create the temp table as select * from origianl table > > b)drop all the indexes on the original table > > c)disable all the child table foreign constraints related to this table > > d)truncate the original table > > f)re-insert all the rows from that temp table > > g)re-create all the indexes > > f)re-enable all the child table constraints > > > > This approach works fine, the problems is, for some big tables with 1 > > million row, the data is not available for about 30 minutes. > > > > Second approach > > a)create the temp table as select * from origianl table > > b)create all the indexes on the temp table > > c)find those child table, and add foreign constraints pointing to the > > temp table > > d)create all the comments on the temp table and columns > > f)rename original table to temp and rename the temp table to target > > table > > > > this approach, data is always available to users, > > > > my question is, for my second approach, did I list everything I have to > > worry about? > > > > What will happen to the triggers on the table? Will it point to the > > new table or will it point to the original table which is now renamed > > to temp? Is there anything else I need to worry? > > > > Thanks > > > > > > > > -- > > Sent by dbadba62 from hotmail in area com > > This is a spam protected message. Please answer with reference header. > > Posted via http://www.usenet-replayer.com/cgi/content/new >