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

There is always the Pr*C option, which would always allow you to add a length column in front of the long, otherwise for a simple fix and if constraints aren't too much of a problem you could do it the other way round -

Create new table as select all the other columns from the old table.

Update the old table set all the unnecessary columns to null (or create a view which hides them).

It won't be hugely efficient, and will waste space, but it will get the job done in SQL.

There is a PL/SQL (dbms_sql) solution in your version of 7, but it requires getting each long 32K at a time with an insert and then append - and that's pretty nasty in terms of efficiency too.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Sybrand Bakker wrote in message
<934390687.24207.0.pluto.d4ee154e_at_news.demon.nl>...
>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.
Received on Wed Aug 11 1999 - 13:26:40 CDT