thanks sybrand.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ttcu5cev8ac9b3_at_corp.supernews.com>...
> "mark" <mark_brehmen_at_yahoo.com> wrote in message
> news:fa4781e4.0110232323.4cc6a539_at_posting.google.com...
> > Hello all
> >
> > This is a view description of ALL_TABLES i got from TOAD(see end). It
> > would be helpful if someone could tell me about the bitand functions.
> > some stuff i read about this bit operators is going over my head :-(.
> >
> > I have seen Thomas Kytes functions to convert from Base 2 to Hex and
> > such. It really beats me how it can be useful when we talk about a
> > database. I mean why would anyone want to concern themselves with
> > hexadecimals in a database. Or for that matter try to convert from
> > decimal system to base 2? Can anyone give an example?
> >
> > I did not study computer science. So if someone could help with this i
> > will be grateful.
> >
> >
> > CREATE OR REPLACE VIEW ALL_TABLES ( OWNER,
> > TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME,
> > PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,
> > INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
> > PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING,
> > BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS,
> > AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS,
> > NUM_FREELIST_BLOCKS, DEGREE, INSTANCES, CACHE,
> > TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED,
> > IOT_TYPE, TEMPORARY, NESTED, BUFFER_POOL
> > ) AS select u.name, o.name,decode(bitand(t.property, 32), 32, null,
> > decode(bitand(t.property, 64), 64, null, ts.name)),
> > decode(bitand(t.property, 1024), 0, null, co.name),
> > decode(bitand(t.property, 512), 0, null, co.name),
> > decode(bitand(t.property, 32), 0, mod(t.pctfree$, 100), null),
> > decode(bitand(t.property, 32), 0, t.pctused$, null),
> > decode(bitand(t.property, 32), 0, t.initrans, null),
> > decode(bitand(t.property, 32), 0, t.maxtrans, null),
> > s.iniexts * ts.blocksize, s.extsize * ts.blocksize,
> > s.minexts, s.maxexts, s.extpct,
> > decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1,
> > s.groups),
> > decode(bitand(t.property, 32), 32, null,
> > decode(bitand(t.flags, 32), 0, 'YES', 'NO')),
> > decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
> > t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln,
> > t.avgspc_flb, t.flbcnt,
> > lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
> > lpad(decode(t.instances, 32767, 'DEFAULT',
> > nvl(t.instances,1)),10),
> > lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
> > decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
> > t.samplesize, t.analyzetime,
> > decode(bitand(t.property, 32), 32, 'YES', 'NO'),
> > decode(bitand(t.property, 64), 64, 'IOT',
> > decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
> > null)),
> > decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
> > decode(bitand(t.property, 8192), 8192, 'YES',
> > decode(bitand(t.property, 1), 0, 'NO', 'YES')),
> > decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE',
> > NULL)
> > from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t,
> > sys.obj$ o
> > where o.owner# = u.user#
> > and o.obj# = t.obj#
> > and bitand(t.property, 1) = 0
> > and t.bobj# = co.obj# (+)
> > and t.ts# = ts.ts#
> > and t.file# = s.file# (+)
> > and t.block# = s.block# (+)
> > and t.ts# = s.ts# (+)
> > and (o.owner# = userenv('SCHEMAID')
> > or o.obj# in
> > (select oa.obj#
> > from sys.objauth$ oa
> > where grantee# in ( select kzsrorol
> > from x$kzsro
> > )
> > )
> > or /* user has system privileges */
> > exists (select null from v$enabledprivs
> > where priv_number in (-45 /* LOCK ANY TABLE */,
> > -47 /* SELECT ANY TABLE */,
> > -48 /* INSERT ANY TABLE */,
> > -49 /* UPDATE ANY TABLE */,
> > -50 /* DELETE ANY TABLE */)
> > )
> > )
>
> What Oracle seems to have done in the dictionary is to compress several
> YES/NO or boolean fields in one column.
> The column functions as a mask
> 7 6 5 4 3 2 1 0
> 1 1 0 0 1 1 0 1
>
> Apparently to know whether a table is an index organised table they need to
> retrieve information from bit 6 ( 2 to the power of 6 is 64)
> when you calculate t.property AND 64
> you calculate the following
>
> 110001101
> and
>
> 010000000
>
> 1 and 0 returns 0
> 1 and 1 returns 1
> so this calculation will
> return (if you apply it bit for bit 1)
> 0100000 so 64
>
> So that's why they have this decode
> decode(bitand(t.property, 64), 64, 'IOT',
> decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW'
>
> which simply means
> 'if bit 6 of the property column is 1, this is an index organized table,
> otherwise test whether bit 9 is 1 (2^9 = 512),and return IOT_OVERFLOW
>
> It is a very efficient way to store information in one single column, though
> admittedly very complicated.
>
> You should leave the all_tables view at what it is, so I'm not sure what you
> wan t to do with it.
>
> Hth,
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Sat Oct 27 2001 - 02:08:47 CDT
