Re: Need to move a long column to it's own table

A copy of this was sent to "Sybrand Bakker" <postmaster_at_sybrandb.demon.nl> (if that email address didn't require changing) On Wed, 11 Aug 1999 18:57:56 +0200, you wrote:

>Hi all,
>
>Using Oracle 7.3.4 on NT4 sp3 at a customer, my firm designed an application
>with a long column in the most-heavily used table. The average record length
>is 42k, and we identified the presence of the long as the cause.
>The solution seems to be easy: create a separate table and have the primary
>key of the source table and the long there.
>So far so good. I know this question must have been answered before, and I
>did research the deja archives: how to get the data their. Create table as
>select won't work, insert select won't work, etc. I rather don't want to
>write an OCI program to accomplish this, and there doesn't seem to be a
>solution in PL/SQL.
>
>Any comments...
>
>Thanks,
>
>Sybrand Bakker, Oracle DBA
>
>

look at the sql*plus copy command. It will do it. You need a sql*net connect string that loops back to your local database (not a dblink, a sqlplus connect string, you need to be able to "sqlplus scott/tiger_at_yourdatabase"...  

For example, I just:  

create table foo
( The_Whole_View varchar2(65),

  TextLength       number,
  TheText          Long )

which is a table, sort of like all_views (which has a long)... Then I:  

SQL> copy from tkyte/tkyte_at_aria insert foo (the_whole_view, textlength, thetext ) using select owner||'.'||view_name, text_length, text from all_views;  

So the sqlplus command transformed the table for me (the columns are not the same). Also, I could have used a where clause to pick off just some rows.  

You'll want to set  

set arraysize N -- amount of rows the copy command will copy with each fetch set long N -- size of your longest long set copycommit M -- number of fetches to do before commit (N*M rows!!)  

in plus before doing this. see the manual for all the options....  

So -- what you would do is create 2 new tables. One has the primary key and the long. Using the sqlplus copy command, you'll copy the primary key and long column into this new table.

You will then create table as select all of the data from the table with the long column EXCEPT for the long column.

drop the original table. rename the second table above to have the name of the dropped table. put the indexes/constraints/etc/etc/etc on this new table.....

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 12 1999 - 06:26:38 CDT