Re: BTW LONG: how about Oracle itself ?

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 06 May 2004 11:26:59 +0200
Message-ID: <c7d0d4$5v4$1@news.BelWue.DE>

Howard J. Rogers wrote:
> Jan Gelbrich wrote:
>

>> BTW, how about Oracles´ own tables containing LONGs ?
>> In 8i (8.1.7.3 - yaya, old version, but still in service widely), I 
>> find a
>> lot of them:
>>
>> SQL> select table_name from dba_tab_columns where data_type = 'LONG';

>
>
> [Almighty Snip]
>

>> 193 Zeilen ausgewählt.

>
>
> Anything wrong with doing a count(table_name) and saving us all some
> bandwidth?

In addition, not all reported columns actually are in a real table, but are some Data Dictionary Views.

The following sql ( not to be used in production systems) shows this clearly (or I made a silly mistake and will gladly receive any corrections):

select 	dtc.table_name,
	column_name,
	column_id,
	max_id,
	decode (column_id, max_id, 'LAST', 'MIDDLE') position
from
     (select table_name from dba_tables where owner = 'SYS') dt,
     (select table_name, column_name, column_id from dba_tab_columns where data_type = 'LONG') dtc,
     (select table_name, max(column_id) max_id from dba_tab_columns group by table_name) dtm

where dt.table_name = dtc.table_name

   and dtc.table_name = dtm.table_name

returns on my 10g test environment 34 lines, 31 reporting the long column somewhere in the middle.

Given the number of objects in the DD, I'd hardly take this as an advice or hint that longs are to be used in any current application. In fact, a small variation of the query give 30 tables with CLOBs, so the transition to CLOBs instead them already started.

I'm in no position to comment on the usage of longs within the Data Dictionary, but personally I assume that they are still there for a reason, however the nature of that reason we can only speculate.

Cheers,

Holger Received on Thu May 06 2004 - 04:26:59 CDT