Re: What is sys_op_opnsize, exactly?
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx
- Date: Thu, 10 Aug 2006 10:40:01 +0100
Not an answer but observations.
Character types sys_op_opnsize appears to return number of bytes not length.
I presume the same is true for date and timestamp columns.
NIALL @ nl102 >create table t1(
2 c1 varchar2(10),
3 c2 char(10),
4 c3 nvarchar2(10),
5 c4 nchar(10),
6 n1 number(2,0),
7 n2 number(10),
8 d1 date,
9 ts1 timestamp,
10 ts2 timestamp with time zone);
Table created.
NIALL @ nl102 >select
2 sys_op_opnsize(c1),
3 sys_op_opnsize(c2),
4 sys_op_opnsize(c3),
5 sys_op_opnsize(c4),
6 sys_op_opnsize(n1),
7 sys_op_opnsize(n2),
8 sys_op_opnsize(d1),
9 sys_op_opnsize(ts1),
10 sys_op_opnsize(ts2)
11* from t1;
no rows selected
NIALL @ nl102 >insert into t1 values(
2 'A','A','A','A',1,1,SYSDATE,SYSTIMESTAMP,SYSTIMESTAMP);
1 row created.
NIALL @ nl102 >commit;
Commit complete.
NIALL @ nl102 >select
2 sys_op_opnsize(c1),
3 sys_op_opnsize(c2),
4 sys_op_opnsize(c3),
5 sys_op_opnsize(c4),
6 sys_op_opnsize(n1),
7 sys_op_opnsize(n2),
8 sys_op_opnsize(d1),
9 sys_op_opnsize(ts1),
10 sys_op_opnsize(ts2)
11 from t1
12 /
SYS_OP_OPNSIZE(C1) SYS_OP_OPNSIZE(C2) SYS_OP_OPNSIZE(C3) SYS_OP_OPNSIZE(C4)
SYS_OP_OPNSIZE(N1) SYS_OP_OPNSIZE(N2)
------------------ ------------------ ------------------ ------------------
------------------ ------------------
SYS_OP_OPNSIZE(D1) SYS_OP_OPNSIZE(TS1) SYS_OP_OPNSIZE(TS2)
------------------ ------------------- -------------------
1 10 2
20 2 2
7 11 13
1 row selected.
NIALL @ nl102 >select length(c2) from t1;
LENGTH(C2)
----------
10
1 row selected.
NIALL @ nl102 >select length(c4) from t1;
LENGTH(C4)
----------
10
1 row selected.
NIALL @ nl102 >
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
:(
On 8/9/06, Charles Schultz <sacrophyte@xxxxxxxxx> wrote:
OSEE 10.2.0.2 on Solaris 8
When tracing dbms_stats, I came across this system call. Anyone know what
its function is, and why dbms_stats collects the sum of this function for a
column? Character datatypes seems to always = length (ie,
sys_op_opnsize(column) = length(column)). Date datatypes seem to be 7
(length 17). The number datatypes really confuse me, as sys_op_opnsize is
not equal to length or the number of bytes used to store the value
(sys_op_opnsize(1) = 2).
I am probably missing something obvious, but this caught my curiosity.
Thanks,
--
Charles Schultz
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
