Re: less expensive way to fetch column names?

Thanks Connor,

This is neat.. but'll require giving permissions to sys tables.. I'll compare w/ the PL/SQL approach (which should be no better in terms of performance) and report back.

On Mon, 05 Apr 2004 18:58:54 +0800, Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:

>NetComrade wrote:
>>
>> is there are a less expensive way to get column names from a table
>> then doing
>> SELECT LOWER(column_name)
>> FROM user_tab_columns
>> WHERE table_name ='my_table'
>> ORDER BY column_id
>> .......
>> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>> remove NSPAM to email
>
>(I've only got 9.2 here so your version may vary..)
>
>user_tab_columns is based on user_tab_cols, which looks like
>
>create or replace view USER_TAB_COLS
> (TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
> DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
> DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
> DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
> CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
> GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
> V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN,
> SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID)
>as
>select o.name,
> c.name,
> decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2',
>'VARCHAR2'),
> 2, decode(c.scale, null,
> decode(c.precision#, null, 'NUMBER',
>'FLOAT'),
> 'NUMBER'),
> 8, 'LONG',
> 9, decode(c.charsetform, 2, 'NCHAR VARYING',
>'VARCHAR'),
> 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
> 58, nvl2(ac.synobj#, (select o.name from obj$ o
> where o.obj#=ac.synobj#), ot.name),
> 69, 'ROWID',
> 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
> 105, 'MLSLABEL',
> 106, 'MLSLABEL',
> 111, nvl2(ac.synobj#, (select o.name from obj$ o
> where o.obj#=ac.synobj#), ot.name),
> 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
> 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
> 121, nvl2(ac.synobj#, (select o.name from obj$ o
> where o.obj#=ac.synobj#), ot.name),
> 122, nvl2(ac.synobj#, (select o.name from obj$ o
> where o.obj#=ac.synobj#), ot.name),
> 123, nvl2(ac.synobj#, (select o.name from obj$ o
> where o.obj#=ac.synobj#), ot.name),
> 178, 'TIME(' ||c.scale|| ')',
> 179, 'TIME(' ||c.scale|| ')' || ' WITH TIME
>ZONE',
> 180, 'TIMESTAMP(' ||c.scale|| ')',
> 181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME
>ZONE',
> 231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL
>TIME ZONE',
> 182, 'INTERVAL YEAR(' ||c.precision#||') TO
>MONTH',
> 183, 'INTERVAL DAY(' ||c.precision#||') TO
>SECOND(' ||
> c.scale || ')',
> 208, 'UROWID',
> 'UNDEFINED'),
> decode(c.type#, 111, 'REF'),
> nvl2(ac.synobj#, (select u.name from user$ u, obj$ o
> where o.owner#=u.user# and o.obj#=ac.synobj#), ut.name),
> c.length, c.precision#, c.scale,
> decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),
> decode(c.col#, 0, to_number(null), c.col#), c.deflength,
> c.default$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt,
> decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), h.timestamp#,
>h.sample_size,
> decode(c.charsetform, 1, 'CHAR_CS',
> 2, 'NCHAR_CS',
> 3, NLS_CHARSET_NAME(c.charsetid),
> 4, 'ARG:'||c.charsetid),
> decode(c.charsetid, 0, to_number(NULL),
> nls_charset_decl_len(c.length, c.charsetid)),
> decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
> decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
> h.avgcln,
> c.spare3,
> decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B',
>'C'),
> 96, decode(bitand(c.property, 8388608), 0, 'B',
>'C'),
> null),
> decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
> decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO',
>'YES'),
> decode(bitand(ac.flags, 2), 2, 'NO',
> decode(bitand(ac.flags, 4), 4, 'NO',
> decode(bitand(ac.flags, 8), 8,
>'NO',
> 'N/A')))),
> decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32,
>'YES',
> 'NO')),
> decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8,
>'YES',
> 'NO')),
> decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#
>from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.coltype$ ac, sys.obj$
>ot,
> sys.user$ ut
>where o.obj# = c.obj#
> and o.owner# = userenv('SCHEMAID')
> and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
> and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
> and ac.toid = ot.oid$(+)
> and ot.type#(+) = 13
> and ot.owner# = ut.user#(+)
> and (o.type# in (3, 4) /* cluster,
>view */
> or
> (o.type# = 2 /* tables, excluding iot - overflow and nested
>tables */
> and
> not exists (select null
> from sys.tab$ t
> where t.obj# = o.obj#
> and (bitand(t.property, 512) = 512 or
> bitand(t.property, 8192) = 8192))))
>/
>
>You could probably get some benefit by cutting out the stuff you are not
>interested in to get a more streamlined version. A quick look at the
>above and you could possibly get down to something like:
>
>select o.name,
> c.name
>from sys.col$ c, sys.obj$ o
>where o.obj# = c.obj#
> and o.owner# = userenv('SCHEMAID')
> and (o.type# in (3, 4) /* cluster,
>view */
> or
> (o.type# = 2 /* tables, excluding iot - overflow and nested
>tables */
> and
> not exists (select null
> from sys.tab$ t
> where t.obj# = o.obj#
> and (bitand(t.property, 512) = 512 or
> bitand(t.property, 8192) = 8192))))
>
>
>hth
>connor
>--
>-------------------------------
>Connor McDonald
>http://www.oracledba.co.uk
>Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Mon Apr 05 2004 - 15:27:38 CDT