| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: What is sys_op_opnsize, exactly?
Niall,
I don't have a 10.2 database to play with, but the SYS_OP_OPSIZE function is certainly returning byte-length for NUMBER datatypes as well. Oracle uses a fixed-length (1-byte) exponent and a variable-length mantissa for numerics, so using 2 bytes to represent the value of "1" is entirely expected.
Using another method to validate the results of SYS_OP_OPSIZE, a block dump of a table with eight rows consisting of the values you used in your example:
SQL> create table x (n1 number); SQL> insert into x values (1); SQL> insert into x values (9); SQL> insert into x values (99); SQL> insert into x values (100); SQL> insert into x values (101); SQL> insert into x values (999); SQL> insert into x values (1000); SQL> insert into x values (1001); SQL> commit; SQL> select file_id, block_id, blocks from dba_extents2 where segment_name = 'X';
...(returned with FILE_ID =8, BLOCK_ID=59521, BLOCKS=8)...
SQL> alter system dump datafile 1 block min 59521 block max 59528;
Following is the text from a 10.1 trace file containing the block dump. Keeping mind that Oracle uses 99's complement base-100 arithmetic for numeric values, the trace file contains:
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02* <=== note that length is "2"
<=== exponent "c1" in 99's
complement is 97-99
is "-2" in base-100 is dec "0"
<=== mantissa "02" in base-100
is dec "1"
tab 0, row 1, @0x1f94
col 0: [ 2] c1 0a * <=== note that length is "2"tab 0, row 2, @0x1f8e
** <=== exponent "c1" in 99's
complement is 97-99 is "-2" in base-100 is dec "0" <=== mantissa "0a" in base-100 is dec "9"
col 0: [ 2] c1 64* <=== note that length is "2"tab 0, row 3, @0x1f88
** <=== exponent "c1" in 99's
complement is 97-99 is "-2" in base-100 is dec "0" <=== mantissa "64" in base-100 is dec "99"*
col 0: [ 2] c2 02* <=== note that length is "2"tab 0, row 4, @0x1f81
** <=== exponent "c2" in 99's
complement is 97-98 is "-1" in base-100 is decimal "1" <=== mantissa "02" in base-100 is decimal "1"
col 0: [ 3] c2 02 02 * <=== note that length is "3"**
** <=== exponent "c2" in 99's
complement is 97-98
is "-1" in base-100
is base-10 "1"
<=== mantissa "02" in base-100
is "1" (multiply by 100)
plus base-100 "02"
or base-10 "1"*
tab 0, row 5, @0x1f7a
col 0: [ 3] c2 0a 64* <=== note that length is "3"
** <=== exponent "c2" in 99's
complement is 97-98
is "-1" in base-100 is
base-10 "1"
<=== mantissa "0a" in base-100
is "9" (multiply by 100)
plus base-100 "64"
or base-10 "99"*
tab 0, row 6, @0x1f74
col 0: [ 2] c2 0b* <=== note that length is "2"tab 0, row 7, @0x1f6d
** <=== exponent "c2" in 99's
complement is 97-98 is "-1" in base-100 is base-10 "1" <=== mantissa "0b" in base-100 is "10" (multiply by 100)
col 0: [ 3] c2 0b 02* <=== note that length is "3"
<=== exponent "c2" in 99's
complement is 97-98
is "-1" in base-100 is
base-10 "1"
<=== mantissa "0b" in base-100
is "10" (multiply by 100)
plus base-100 "02" or
base-10 "1"*
end_of_block_dump
So, the byte-lengths are the same as those values returned by SYS_OP_OPSIZE. The apparent inconsistency of the value of "999" taking more space than value "1000" is due to the fact that this internal representation filters out "insignificant digits" (e.g. zeroes) while compressing "significant digits" (e.g. non-zeroes) into much-more-compact base-100 (rather than octal, hex, or decimal) representation.
The difference is even more noticeable if those of you with 10.2 database instances try: "select sys_op_opsize(9999999999), sys_op_opsize(10000000000) from dual"... :-)
Hope this helps...
-Tim
> However experimentation with numbers rather suggests that something > else is going on.. > > NIALL @ nl102 >select > 2 sys_op_opnsize(1), > 3 sys_op_opnsize(9), > 4 sys_op_opnsize(99), > 5 sys_op_opnsize(100), > 6 sys_op_opnsize(101), > 7 sys_op_opnsize(999), > 8 sys_op_opnsize(1000), > 9 sys_op_opnsize(1001) > 10 from dual; > > SYS_OP_OPNSIZE(1) SYS_OP_OPNSIZE(9) SYS_OP_OPNSIZE(99) > SYS_OP_OPNSIZE(100) SYS_OP_OPNSIZE(101) SYS_OP_OPNSIZE(999) > ----------------- ----------------- ------------------ > ------------------- ------------------- ------------------- > SYS_OP_OPNSIZE(1000) SYS_OP_OPNSIZE(1001) > -------------------- -------------------- > 2 2 > 2 2 3 3 > 2 3
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 10 2006 - 09:17:14 CDT
 |
 |