Re: Number of Columns in a Table & SQL Performance ?

On Tue, Mar 10, 2009 at 9:13 AM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:

> While I agree fully that 400 columns in a table is bit excessive, but
> E-Business suite 11i is quite famous for this issue. For example, there are
> tables with 729 columns.
>
> [ Sorry for long winded SQL. Due to dictionary size access to dba_ views
> hangs and so accessing underlying tables directly. ]
>
> select /*+ no_merge (col1) */ o.obj#, name, namespace ,
> decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
> 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
> 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
> 11, 'PACKAGE BODY', 12, 'TRIGGER',
> 13, 'TYPE', 14, 'TYPE BODY',
> 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21,
> 'LOB',
> 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
> 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA
> RESOURCE',
> 32, 'INDEXTYPE', 33, 'OPERATOR',
> 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
> 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
> 42, 'MATERIALIZED VIEW',
> 43, 'DIMENSION',
> 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
> 48, 'CONSUMER GROUP',
> 51, 'SUBSCRIPTION', 52, 'LOCATION',
> 55, 'XML SCHEMA', 56, 'JAVA DATA',
> 57, 'SECURITY PROFILE', 59, 'RULE',
> 62, 'EVALUATION CONTEXT',
> 'UNDEFINED') ,cnt
> from (
> select * from
> (
> select obj#, count(*) cnt from sys.col$ group by obj# having
> count(*)>150
> order by 2 desc
> ) where rownum <100 ) col1 ,
> sys.obj$ o
> where col1.obj#=o.obj#
> and o.type#=2
> /
>
> OBJ# NAME NAMESPACE DECODE(O.TYPE#,0,'
> COL CNT
> ---------- ------------------------------ ---------- ------------------
> ----------
> 163169 EDWCMPALLCLASSES 1
> TABLE 729
> 5117656 IGF_AP_LI_CSS_INTS 1
> TABLE 719
> 196447 IGF_AP_CSS_INTERFACE_ALL 1
> TABLE 716
> 32304 ECE_STAGE 1
> TABLE 519
> 41640 OE_LINE_ACKS 1
> TABLE 507
> 5118251 IGF_AP_LI_ISIR_INTS 1
> TABLE 505
> 5114978 ICX_CAT_ITEMS_GT 1
> TABLE 500
> 5116122 ICX_CAT_UPLOAD_IT_DUMP 1
> TABLE 500
> 5119406 IGF_AP_ISIR_INTS_ALL 1
> TABLE 499
> 172404 EDW_ITEMS_M 1
> TABLE 495
> 196016 IGF_AP_ISIR_INTRFACE_ALL 1
> TABLE 490
> 5111252 CSI_HISTORY_ARCHIVE 1
> TABLE 473
> 41598 OE_LINES_IFACE_ALL 1
> TABLE 467
>
> ...
>
> --
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com

--
http://www.freelists.org/webpage/oracle-l