Re: 2 LONGS in 1 TABLE

jkline_at_iconstl.net (John Kline) wrote:
>In article <1995Jun19.215525.24485_at_wmdhh.wmd.de>,
> rose_at_wmdhh.wmd.de (Axel Rose) wrote:
>>Hi Netters,
>>
>>Current available versions of Oracle (7.1) do not support more than
>>a single long column per table
 

>>1.
>>What workarounds do you use?
>
>It is sometimes a problem. I actually find I have more problems with Oracle
>not being able to create a table from a "select" with a long column.
>
>Actually, In our environment whenever I have a column that will end up being
>signifigantly larger than the rest of the columns combined (varchar2 (2000) or
>long) I move the offending column into a separate table. This saves time and
>prevents oracle from scanning through all that data when searching the table.
>

that is a good idea. Putting a long into a table may significantly increase the time to do a full table scan on that table. Breaking them out works well.

>>2.
>>Are views a usable solution?
>
>Nope. Oracle is far too clever to let you slip that one by it.
>

Yes, views are a very usable solution. The following spooled output shows how to do it. I don't understand the above comment.

SQL> create table a1 ( x int, y long )
  2 /

Table created.

SQL> create table a2 ( x int, z long )
  2 /

Table created.

SQL> create view a1_2
  2 as
  3 select a1.x, a1.y, a2.z
  4 from a1, a2
  5 where a1.x = a2.x
  6 /

View created.

SQL> desc a1_2

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        NUMBER(38)
 Y                                        LONG
 Z                                        LONG

---------------------------------------------------------------------------

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

>
>cheers,
>
>
>John Kline | .sig under construction
>jtkline_at_icon-stl.net |
>
Received on Thu Jun 22 1995 - 00:00:00 CEST