Oracle – Understanding Property Column in sys.col$

oracle

I would like to ask what are the number presenting in Property column in sys.col$ table? When I count distinct, it returns 39 rows. And base on my own research, I know for sure that the property = 0, it means that column uses Byte semantic and property = 8388608, it uses Char semantic. Below is a query that I found on the internet:

"select DECODE (type#,
1, DECODE (BITAND (property, 8388608), 0, 'B', 'C'),
96, DECODE (BITAND (property, 8388608), 0, 'B', 'C'),
Null)
from sys.col$;"

Again, I try to understand what other numbers are.

Thank you so much

Khang

Best Answer

Does it really matter? It is an undocumented internal dictionary table. It is a number representing some flags set.

Just have a look at the definition of views such as DBA_TAB_COLS_V$.

select dbms_metadata.get_ddl('VIEW', 'DBA_TAB_COLS_V$', 'SYS') from dual;

There you can find stuff like below, example:

   ...
   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#,
   -- warning! If you update stats related info, make sure to also update
   -- GTT session private stats in cdoptim.sql
   case when nvl(h.row_cnt,0) = 0 then 'NONE'
        when exists(select 1 from sys.histgrm$ hg
                    where c.obj# = hg.obj# and c.intcol# = hg.intcol#
                      and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'
        when bitand(h.spare2, 64) > 0
          then 'TOP-FREQUENCY'
        when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
              (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
            then 'FREQUENCY'
        else 'HEIGHT BALANCED'
   end,                                                          /* HISTOGRAM */
   decode(bitand(c.property, 1024), 1024,
          (select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
           from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
           and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
           cl.intcol# = rc.intcol#(+)),
          decode(bitand(c.property, 1), 0, c.name,
                 (select tc.name from sys.attrcol$ tc
                  where c.obj# = tc.obj# and c.intcol# = tc.intcol#))),
   decode(bitand(c.property, 17179869184), 17179869184, 'YES',
          decode(bitand(c.property, 32), 32, 'NO', 'YES')),
   decode(bitand(c.property, 68719476736), 68719476736, 'YES', 'NO'),
   decode(bitand(c.property, 137438953472 + 274877906944),
                 137438953472, 'YES', 274877906944, 'YES', 'NO'),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 8796093022208),
                                      8796093022208, 'YES', 'NO')),
   ...
Related Question