Re: What is sys_op_opnsize, exactly?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 10 Aug 2006 10:40:01 +0100
Message-ID: <7765c8970608100240p6cd05d98j5e2f88c34433e42b@mail.gmail.com>

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)

1 row selected.

NIALL @ nl102 >select length(c4) from t1;

LENGTH(C4)

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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 10 2006 - 04:40:01 CDT