Wednesday, October 29, 2008

Year zero

According to Wikipedia, the year zero does not exist:

B.C. (or BC) — Before Christ. Used for years prior to AD 1, counting backwards so the year n BC is the year 1-n AD. Using these two calendar eras as historians use them means that there is no year 0 or negative year numbers.


But according to Oracle it does:

rwijk@ORA11GR1> select date '0000-01-01' from dual
2 /

DATE'0000-01-01'
-------------------
01-01-0000 00:00:00

1 row selected.

rwijk@ORA11GR1> select date '0000-12-31' from dual
2 /

DATE'0000-12-31'
-------------------
31-12-0000 00:00:00

1 row selected.


But wait, it really doesn't:

rwijk@ORA11GR1> select to_date('0000-01-01','yyyy-mm-dd') from dual
2 /
select to_date('0000-01-01','yyyy-mm-dd') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


rwijk@ORA11GR1> select to_date('0000-12-31','yyyy-mm-dd') from dual
2 /
select to_date('0000-12-31','yyyy-mm-dd') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


Or, it does:

rwijk@ORA11GR1> select to_date('31-12-0001bc','dd-mm-yyyybc') + 1 from dual
2 /

TO_DATE('31-12-0001
-------------------
01-01-0000 00:00:00

1 row selected.

rwijk@ORA11GR1> select to_date('01-01-0001ad','dd-mm-yyyybc') - 1 from dual
2 /

TO_DATE('01-01-0001
-------------------
31-12-0000 00:00:00

1 row selected.


And apparently the year zero is a leap year:

rwijk@ORA11GR1> select to_date('01-01-0001 ad','dd-mm-yyyy bc')
2 - to_date('31-12-0001 bc','dd-mm-yyyy bc')
3 from dual
4 /

TO_DATE('01-01-0001AD','DD-MM-YYYYBC')-TO_DATE('31-12-0001BC','DD-MM-YYYYBC')
-----------------------------------------------------------------------------
367

1 row selected.


Confusing?
Let's inspect the internal representation:

rwijk@ORA11GR1> select dump(date '1000-01-01')
2 , dump(date '0100-01-01')
3 , dump(date '0010-01-01')
4 , dump(date '0001-01-01')
5 from dual
6 /

DUMP(DATE'1000-01-01') DUMP(DATE'0100-01-01')
------------------------------- -------------------------------
DUMP(DATE'0010-01-01') DUMP(DATE'0001-01-01')
------------------------------ -----------------------------
Typ=13 Len=8: 232,3,1,1,0,0,0,0 Typ=13 Len=8: 100,0,1,1,0,0,0,0
Typ=13 Len=8: 10,0,1,1,0,0,0,0 Typ=13 Len=8: 1,0,1,1,0,0,0,0


1 row selected.


Note that 3*256 + 232 = 1000.

And now the same for the BC years:

rwijk@ORA11GR1> select dump(date '-1000-01-01')
2 , dump(date '-0100-01-01')
3 , dump(date '-0010-01-01')
4 , dump(date '-0001-01-01')
5 from dual
6 /

DUMP(DATE'-1000-01-01') DUMP(DATE'-0100-01-01')
-------------------------------- ---------------------------------
DUMP(DATE'-0010-01-01') DUMP(DATE'-0001-01-01')
--------------------------------- ---------------------------------
Typ=13 Len=8: 24,252,1,1,0,0,0,0 Typ=13 Len=8: 156,255,1,1,0,0,0,0
Typ=13 Len=8: 246,255,1,1,0,0,0,0 Typ=13 Len=8: 255,255,1,1,0,0,0,0


1 row selected.


A similar formula is used to calculate the year. Only backwards starting by 255,255 (1 BC).

And the year zero apparently has its own representation:

rwijk@ORA11GR1> select dump(date '0000-01-01')
2 from dual
3 /

DUMP(DATE'0000-01-01')
-----------------------------
Typ=13 Len=8: 0,0,1,1,0,0,0,0

1 row selected.


Now let's see how the to_char function deals with these dates:

rwijk@ORA11GR1> select to_char(date '0001-01-01', 'dd-mm-yyyy BC')
2 , to_char(date '0000-01-01', 'dd-mm-yyyy BC')
3 , to_char(date '-0001-01-01', 'dd-mm-yyyy BC')
4 from dual
5 /

TO_CHAR(DATE' TO_CHAR(DATE' TO_CHAR(DATE'
------------- ------------- -------------
01-01-0001 AD 00-00-0000 00 01-01-0001 BC

1 row selected.


The day and month and BC indicator are '00'.

My conclusion is that internally the year zero exists in Oracle. However, the to_date and to_char functions mask this "flaw" by raising errors or printing all zeros.

Luckily, as often, the problem isn't new. A quote from this enlightening document:

After trying many date-arithmetic calculations for dates before 1 AD, we're able to state this with confidence: Oracle is using BC/AD notation but is using the astronomical convention. Presumably some Oracle programmer in the distant past lifted an astronomer's calculation algorithm and plugged it into the Oracle engine, without realizing that the negative year numbers are all exactly one year different from BC numbers. Oracle can't fix this without wrecking existing applications, although it could help by admitting there's a year zero and allowing dates in "0 AD" rather than returning an error.

Thanks Wilco and Klaas for letting me know this interesting problem.

Saturday, October 18, 2008

DBA_DEPENDENCY_COLUMNS

In two earlier posts, about 11.1.0.6 oddities and my last one about 11.1.0.7 I mentioned not being able to find the column dependency information used in fine grained dependency tracking in the regular USER_% views of the data dictionary. I still haven't, but on the second post, Toon Koppelaars commented:

I did some research in this area a few months ago. The FGDT information is stored in sys.dependency$.d_attrs. In the value Oracle seems to bitwise encode the columns that are used.
For instance if you have table T with three columns C1, C2 and C3. And you create view V as select C1,C3 from T. Then you are using the first and third column. This is encoded as power(2,1) + power(2,3) which is 10, which (in RAW) maps to hexidecimal value 'A'. You will see a raw-value in d_attrs that ends with 'A'.
I have not yet discovered what the role of the d_reason column value is playing in this area. Would be nice if Oracle would document this...


Let's see for ourselves:

rwijk@ORA11GR1> desc sys.dependency$
Naam Null? Type
----------------------------------------- -------- ----------------------------
D_OBJ# NOT NULL NUMBER
D_TIMESTAMP NOT NULL DATE
ORDER# NOT NULL NUMBER
P_OBJ# NOT NULL NUMBER
P_TIMESTAMP NOT NULL DATE
D_OWNER# NUMBER
PROPERTY NOT NULL NUMBER
D_ATTRS RAW(2000)
D_REASON RAW(2000)

The last two columns are not used in DBA_DEPENDENCIES. To see how d_attrs is used, let's create a table with 30 columns:

rwijk@ORA11GR1> create table mytable
2 ( col1 int
3 , col2 int
4 , col3 int
5 , col4 int
6 , col5 int
7 , col6 int
8 , col7 int
9 , col8 int
10 , col9 int
11 , col10 int
12 , col11 int
13 , col12 int
14 , col13 int
15 , col14 int
16 , col15 int
17 , col16 int
18 , col17 int
19 , col18 int
20 , col19 int
21 , col20 int
22 , col21 int
23 , col22 int
24 , col23 int
25 , col24 int
26 , col25 int
27 , col26 int
28 , col27 int
29 , col28 int
30 , col29 int
31 , col30 int
32 )
33 /

Tabel is aangemaakt.

And create a view using just two columns, col1 and col3:

rwijk@ORA11GR1> create view myview as select col1, col3 from mytable
2 /

View is aangemaakt.


If we use the query that's underneath the DBA_DEPENDENCIES view, and extend it with the columns d_attrs and d_reason (printed in bold), we see this:

rwijk@ORA11GR1> select u.name owner, o.name name,
2 decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
3 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
4 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
5 11, 'PACKAGE BODY', 12, 'TRIGGER',
6 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
7 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
8 32, 'INDEXTYPE', 33, 'OPERATOR',
9 42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
10 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA',
11 59, 'RULE', 62, 'EVALUATION CONTXT',
12 92, 'CUBE DIMENSION', 93, 'CUBE',
13 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
14 'UNDEFINED') type,
15 decode(po.linkname, null, pu.name, po.remoteowner) referenced_owner,
16 po.name referenced_name,
17 decode(po.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
18 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
19 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
20 11, 'PACKAGE BODY', 12, 'TRIGGER',
21 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
22 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
23 32, 'INDEXTYPE', 33, 'OPERATOR',
24 42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
25 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA',
26 59, 'RULE', 62, 'EVALUATION CONTXT',
27 92, 'CUBE DIMENSION', 93, 'CUBE',
28 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
29 'UNDEFINED') referenced_type,
30 po.linkname referenced_link_name,
31 decode(bitand(d.property, 3), 2, 'REF', 'HARD') dependency_type
32 , d.d_attrs
33 , d.d_reason
34 from sys."_CURRENT_EDITION_OBJ" o, sys.disk_and_fixed_objects po,
35 sys.dependency$ d, sys.user$ u, sys.user$ pu
36 where o.obj# = d.d_obj#
37 and o.owner# = u.user#
38 and po.obj# = d.p_obj#
39 and po.owner# = pu.user#
40 and o.name = 'MYVIEW'
41 /

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
DEPE D_ATTRS
---- -------------------------------------------------
D_REASON
--------------------------------------------------------------------------------
RWIJK MYVIEW VIEW
RWIJK
MYTABLE
TABLE

HARD 000100000A



1 rij is geselecteerd.

d_attrs contains the raw value "000100000A". Indeed the 'A' value Toon mentioned. But what's up with those first characters? And if this a hexadecimal value, how will it behave when the 30th column is referenced, for example? This is what I did to find out:

rwijk@ORA11GR1> create procedure myproc1 as l_col1 mytable.col1%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc2 as l_col2 mytable.col2%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc3 as l_col3 mytable.col3%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc4 as l_col4 mytable.col4%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc5 as l_col5 mytable.col5%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc6 as l_col6 mytable.col6%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc7 as l_col7 mytable.col7%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc8 as l_col8 mytable.col8%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc9 as l_col9 mytable.col9%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc10 as l_col10 mytable.col10%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc11 as l_col11 mytable.col11%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc12 as l_col12 mytable.col12%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc13 as l_col13 mytable.col13%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc14 as l_col14 mytable.col14%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc15 as l_col15 mytable.col15%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc16 as l_col16 mytable.col16%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc17 as l_col17 mytable.col17%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc18 as l_col18 mytable.col18%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc19 as l_col19 mytable.col19%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc20 as l_col20 mytable.col20%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc21 as l_col21 mytable.col21%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc22 as l_col22 mytable.col22%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc23 as l_col23 mytable.col23%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc24 as l_col24 mytable.col24%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc25 as l_col25 mytable.col25%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc26 as l_col26 mytable.col26%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc27 as l_col27 mytable.col27%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc28 as l_col28 mytable.col28%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc29 as l_col29 mytable.col29%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> create procedure myproc30 as l_col30 mytable.col30%type; begin null; end;
2 /

Procedure is aangemaakt.

rwijk@ORA11GR1> select o.name name, d.d_attrs attrs
2 from sys."_CURRENT_EDITION_OBJ" o, sys.disk_and_fixed_objects po,
3 sys.dependency$ d, sys.user$ u, sys.user$ pu
4 where o.obj# = d.d_obj#
5 and o.owner# = u.user#
6 and po.obj# = d.p_obj#
7 and po.owner# = pu.user#
8 and o.name like 'MYPROC%'
9 and d.d_attrs is not null
10 order by to_number(substr(name,7))
11 /

NAME ATTRS
------------------------------ -------------------------------------------------
MYPROC1 0001000002
MYPROC2 0001000004
MYPROC3 0001000008
MYPROC4 0001000010
MYPROC5 0001000020
MYPROC6 0001000040
MYPROC7 0001000080
MYPROC8 000100000001
MYPROC9 000100000002
MYPROC10 000100000004
MYPROC11 000100000008
MYPROC12 000100000010
MYPROC13 000100000020
MYPROC14 000100000040
MYPROC15 000100000080
MYPROC16 00010000000001
MYPROC17 00010000000002
MYPROC18 00010000000004
MYPROC19 00010000000008
MYPROC20 00010000000010
MYPROC21 00010000000020
MYPROC22 00010000000040
MYPROC23 00010000000080
MYPROC24 0001000000000001
MYPROC25 0001000000000002
MYPROC26 0001000000000004
MYPROC27 0001000000000008
MYPROC28 0001000000000010
MYPROC29 0001000000000020
MYPROC30 0001000000000040

30 rijen zijn geselecteerd.

The first 8 characters are always "00010000". They probably have some meaning, but not for the purpose of finding out the column dependency information. And the rest is not a regular number in hexadecimal format. The bits are grouped byte wise, but in reverse order. With this information we can create our own dba_dependency_columns view:

rwijk@ORA11GR1> conn sys/... as sysdba
Verbonden.

sys@ORA11GR1> grant select on "_CURRENT_EDITION_OBJ" to rwijk
2 /

Toekennen is geslaagd.

sys@ORA11GR1> grant select on disk_and_fixed_objects to rwijk
2 /

Toekennen is geslaagd.

sys@ORA11GR1> grant select on dependency$ to rwijk
2 /

Toekennen is geslaagd.

sys@ORA11GR1> grant select on user$ to rwijk
2 /

Toekennen is geslaagd.

sys@ORA11GR1> grant select on col$ to rwijk
2 /

Toekennen is geslaagd.

sys@ORA11GR1> conn rwijk/...
Verbonden.

rwijk@ORA11GR1> set linesize 80
rwijk@ORA11GR1> create view dba_dependency_columns
2 as
3 select d.u_name owner
4 , d.o_name name
5 , decode
6 ( d.o_type#
7 , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'
8 , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'
9 , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT'
10 , 11, 'PACKAGE BODY', 12, 'TRIGGER'
11 , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY'
12 , 28, 'JAVA SOURCE', 29, 'JAVA CLASS'
13 , 32, 'INDEXTYPE', 33, 'OPERATOR'
14 , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION'
15 , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA'
16 , 59, 'RULE', 62, 'EVALUATION CONTXT'
17 , 92, 'CUBE DIMENSION', 93, 'CUBE'
18 , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS'
19 , 'UNDEFINED'
20 ) type
21 , nvl2( d.po_linkname, d.po_remoteowner, d.pu_name) referenced_owner
22 , d.po_name referenced_name
23 , decode
24 ( d.po_type#
25 , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'
26 , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'
27 , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT'
28 , 11, 'PACKAGE BODY', 12, 'TRIGGER'
29 , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY'
30 , 28, 'JAVA SOURCE', 29, 'JAVA CLASS'
31 , 32, 'INDEXTYPE', 33, 'OPERATOR'
32 , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION'
33 , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA'
34 , 59, 'RULE', 62, 'EVALUATION CONTXT'
35 , 92, 'CUBE DIMENSION', 93, 'CUBE'
36 , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS'
37 , 'UNDEFINED'
38 ) referenced_type
39 , d.po_linkname referenced_link_name
40 , c.name referenced_column
41 , decode(bitand(d.d_property, 3), 2, 'REF', 'HARD') dependency_type
42 from ( select obj#
43 , u_name
44 , o_name
45 , o_type#
46 , pu_name
47 , po_name
48 , po_type#
49 , po_remoteowner
50 , po_linkname
51 , d_property
52 , colpos
53 from sys."_CURRENT_EDITION_OBJ" o
54 , sys.disk_and_fixed_objects po
55 , sys.dependency$ d
56 , sys.user$ u
57 , sys.user$ pu
58 where o.obj# = d.d_obj#
59 and o.owner# = u.user#
60 and po.obj# = d.p_obj#
61 and po.owner# = pu.user#
62 and d.d_attrs is not null
63 model
64 return updated rows
65 partition by
66 ( po.obj# obj#
67 , u.name u_name
68 , o.name o_name
69 , o.type# o_type#
70 , po.linkname po_linkname
71 , pu.name pu_name
72 , po.remoteowner po_remoteowner
73 , po.name po_name
74 , po.type# po_type#
75 , d.property d_property
76 )
77 dimension by (0 i)
78 measures (0 colpos, substr(d.d_attrs,9) attrs)
79 rules iterate (1000)
80 until (iteration_number = 4 * length(attrs[0]) - 2)
81 ( colpos[iteration_number+1]
82 = case bitand
83 ( to_number
84 ( substr
85 ( attrs[0]
86 , 1 + 2*trunc((iteration_number+1)/8)
87 , 2
88 )
89 ,'XX'
90 )
91 , power(2,mod(iteration_number+1,8))
92 )
93 when 0 then null
94 else iteration_number+1
95 end
96 )
97 ) d
98 , sys.col$ c
99 where d.obj# = c.obj#
100 and d.colpos = c.col#
101 /

View is aangemaakt.

rwijk@ORA11GR1> desc dba_dependency_columns
Naam Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(18)
REFERENCED_OWNER VARCHAR2(30)
REFERENCED_NAME VARCHAR2(64)
REFERENCED_TYPE VARCHAR2(18)
REFERENCED_LINK_NAME VARCHAR2(128)
REFERENCED_COLUMN NOT NULL VARCHAR2(30)
DEPENDENCY_TYPE VARCHAR2(4)

The view lists exactly the same columns as the DBA_DEPENDENCIES view, with only one extra column called REFERENCED_COLUMN. Information in this view only shows up when column dependencies are present, so if you ever want to join this one with DBA_DEPENDENCIES, be sure to use an outer join.

A short explanation for the query itself: the inner select statement with the model clause, extends the result set with just as many rows as there are columns. The columns that are dependent have a non null colpos value, corresponding to the column position according to d_attrs. At the outer level this set is joined to sys.col$ to get the corresponding real column names. The rows that have a null value for colpos will leave the result set.

Now let's see how this works for another view:

rwijk@ORA11GR1> create view myview2 as select col2, col3, col15, col28 from mytable
2 /

View is aangemaakt.

rwijk@ORA11GR1> select *
2 from dba_dependencies
3 where name = 'MYVIEW2'
4 /

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
DEPE
----
RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE

HARD


1 rij is geselecteerd.

rwijk@ORA11GR1> select *
2 from dba_dependency_columns
3 where name = 'MYVIEW2'
4 order by to_number(substr(referenced_column,4))
5 /

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
REFERENCED_COLUMN DEPE
------------------------------ ----
RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE

COL2 HARD

RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE

COL3 HARD

RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE

COL15 HARD

RWIJK MYVIEW2 VIEW
RWIJK
MYTABLE
TABLE

COL28 HARD


4 rijen zijn geselecteerd.

Great! And for a procedure:

rwijk@ORA11GR1> create procedure p2
2 as
3 l_col18 mytable.col18%type;
4 begin
5 select col16
6 into l_col18
7 from mytable
8 where col30 = 1
9 and col4 = 23
10 ;
11 update myview2
12 set col15 = 3
13 where col2 = 4
14 ;
15 end p2;
16 /

Procedure is aangemaakt.

rwijk@ORA11GR1> select *
2 from dba_dependencies
3 where name = 'P2'
4 /

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
DEPE
----
RWIJK P2 PROCEDURE
SYS
SYS_STUB_FOR_PURITY_ANALYSIS
PACKAGE

HARD

RWIJK P2 PROCEDURE
SYS
STANDARD
PACKAGE

HARD

RWIJK P2 PROCEDURE
RWIJK
MYVIEW2
VIEW

HARD

RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE

HARD


4 rijen zijn geselecteerd.

rwijk@ORA11GR1> select *
2 from dba_dependency_columns
3 where name = 'P2'
4 order by referenced_name
5 , to_number(substr(referenced_column,4))
6 /

OWNER NAME TYPE
------------------------------ ------------------------------ ------------------
REFERENCED_OWNER
------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
------------------
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
REFERENCED_COLUMN DEPE
------------------------------ ----
RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE

COL4 HARD

RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE

COL16 HARD

RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE

COL18 HARD

RWIJK P2 PROCEDURE
RWIJK
MYTABLE
TABLE

COL30 HARD

RWIJK P2 PROCEDURE
RWIJK
MYVIEW2
VIEW

COL2 HARD

RWIJK P2 PROCEDURE
RWIJK
MYVIEW2
VIEW

COL15 HARD


6 rijen zijn geselecteerd.

Good as well. No guarantees are given however that this view will always give the right results, but for these simple examples, it does.

Wednesday, October 15, 2008

11.1.0.7

Up until now I had only installed base versions of Oracle, but this weekend I decided to install the 11.1.0.7 patch. It was a rough ride, with some really weird error messages ("DBUA thinks this is a Rerun operation" and asking to remove a file which wasn't there). Developers shouldn't do these kind of things I suppose, especially developers who don't fully read the documentation... After doing the required reading and leaving the GUI-tools, everything went fine. And on the plus side, I got a little acquainted with the new ADRCI tool, which wasn't too bad at all. Here is the proof that it worked:

rwijk@ORA11GR1> select * from v$version where rownum = 1
2 /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

1 row selected.


First things I checked were some of the 11.1.0.6 oddities I had seen and have blogged about in the past. I will list them here.


Reference partitioning

In the comment section Eugene - probably working for Oracle - told me that the problem with ORA-14400: inserted partition key does not map to any partition instead of the regular ORA-02291 foreign key violation, was fixed. So now is the time to see if this is really true. In the next SQL*Plus listing you'll see a parent table (notas), a child table ("nota_regels") and a grand child table ("nota_regel_details") being created with some content, showing how reference partitioning works. Please note the last error message.

rwijk@ORA11GR1> create table notas
2 ( id number(10)
3 , klantnummer number(10)
4 , bedrag number(10,2)
5 , datum date
6 , constraint nta_pk primary key (id)
7 )
8 partition by range (datum)
9 ( partition partitie_voor_15_dec_2007 values less than (date '2007-12-15')
10 , partition rest values less than (maxvalue)
11 )
12 /

Table created.

rwijk@ORA11GR1> create table nota_regels
2 ( id number(10) primary key
3 , nta_id number(10) not null
4 , bedrag number(10,2)
5 , aantal number(10)
6 , constraint nrl_nta_fk foreign key (nta_id) references notas(id)
7 )
8 partition by reference (nrl_nta_fk)
9 /

Table created.

rwijk@ORA11GR1> pause

rwijk@ORA11GR1> insert into notas values (1,12345,678.90,date '2007-12-12')
2 /

1 row created.

rwijk@ORA11GR1> insert into notas values (2,12345,56.78,date '2007-12-12')
2 /

1 row created.

rwijk@ORA11GR1> insert into notas values (3,12345,9876.54,date '2007-12-13')
2 /

1 row created.

rwijk@ORA11GR1> insert into notas values (4,12345,123.54,date '2007-12-18')
2 /

1 row created.

rwijk@ORA11GR1> insert into nota_regels values (1,1,500,1)
2 /

1 row created.

rwijk@ORA11GR1> insert into nota_regels values (2,3,178.90,1)
2 /

1 row created.

rwijk@ORA11GR1> insert into nota_regels values (3,4,123.45,2)
2 /

1 row created.

rwijk@ORA11GR1> select partition_name
2 , high_value
3 from user_tab_partitions
4 where table_name = 'NOTA_REGELS'
5 order by partition_position
6 /

PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------
PARTITIE_VOOR_15_DEC_2007
REST

2 rows selected.

rwijk@ORA11GR1> select *
2 from nota_regels partition (partitie_voor_15_dec_2007)
3 /

ID NTA_ID BEDRAG AANTAL
---------- ---------- ---------- ----------
1 1 500 1
2 3 178.9 1

2 rows selected.

rwijk@ORA11GR1> select *
2 from nota_regels partition (rest)
3 /

ID NTA_ID BEDRAG AANTAL
---------- ---------- ---------- ----------
3 4 123.45 2

1 row selected.

rwijk@ORA11GR1> pause

rwijk@ORA11GR1> create table nota_regel_details
2 ( id number(10) primary key
3 , nrl_id number(10) not null
4 , bedrag number(10,2)
5 , constraint nrd_nrl_fk foreign key (nrl_id) references nota_regels(id)
6 )
7 partition by reference (nrd_nrl_fk)
8 /

Table created.

rwijk@ORA11GR1> insert into nota_regel_details values (1,1,100)
2 /

1 row created.

rwijk@ORA11GR1> insert into nota_regel_details values (2,1,50)
2 /

1 row created.

rwijk@ORA11GR1> insert into nota_regel_details values (3,1,250)
2 /

1 row created.

rwijk@ORA11GR1> insert into nota_regel_details values (4,3,100)
2 /

1 row created.

rwijk@ORA11GR1> insert into nota_regel_details values (5,3,200)
2 /

1 row created.

rwijk@ORA11GR1> select partition_name
2 , high_value
3 from user_tab_partitions
4 where table_name = 'NOTA_REGEL_DETAILS'
5 order by partition_position
6 /

PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------
PARTITIE_VOOR_15_DEC_2007
REST

2 rows selected.

rwijk@ORA11GR1> select *
2 from nota_regel_details partition (partitie_voor_15_dec_2007)
3 /

ID NRL_ID BEDRAG
---------- ---------- ----------
1 1 100
2 1 50
3 1 250

3 rows selected.

rwijk@ORA11GR1> select *
2 from nota_regel_details partition (rest)
3 /

ID NRL_ID BEDRAG
---------- ---------- ----------
4 3 100
5 3 200

2 rows selected.

rwijk@ORA11GR1> pause

rwijk@ORA11GR1> insert into nota_regel_details values (6,4,200)
2 /
insert into nota_regel_details values (6,4,200)
*
ERROR at line 1:
ORA-02291: integrity constraint (RWIJK.NRD_NRL_FK) violated - parent key not found
Resolved! In 11.1.0.6 this was a ORA-14400 message. And it's much more convenient to just report issues on your blog instead of raising a service request and being told it is a feature, not a bug. Oracle should do that more often :-)


Fine Grained Dependency Tracking

I had a look around in the 11.1.0.7 data dictionary, but I still could not find a place where the column dependencies are recorded in the USER_% views.


Native compilation

Here the issue was that the data dictionary tells you a procedure is natively compiled, where in fact it is not, because of a wrong PLSQL_OPTIMIZE_LEVEL setting. In 11.1.0.7:

rwijk@ORA11GR1> alter session set plsql_warnings = 'enable:all'
2 /

Session altered.

rwijk@ORA11GR1> create procedure p1
2 is
3 begin
4 for i in 1..100000000
5 loop
6 null;
7 end loop
8 ;
9 dbms_output.put_line('p1');
10 end;
11 /

Procedure created.

rwijk@ORA11GR1> alter session set plsql_optimize_level = 1
2 /

Session altered.

rwijk@ORA11GR1> alter session set plsql_code_type = native
2 /

Session altered.

rwijk@ORA11GR1> create procedure p2
2 is
3 begin
4 for i in 1..100000000
5 loop
6 null;
7 end loop
8 ;
9 dbms_output.put_line('p2');
10 end;
11 /

SP2-0804: Procedure created with compilation warnings

rwijk@ORA11GR1> show err
Errors for PROCEDURE P2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLW-06014: PLSQL_OPTIMIZE_LEVEL <= 1 turns off native code
generation

rwijk@ORA11GR1> alter session set plsql_optimize_level = 2
2 /

Session altered.

rwijk@ORA11GR1> create procedure p3
2 is
3 begin
4 for i in 1..100000000
5 loop
6 null;
7 end loop
8 ;
9 dbms_output.put_line('p3');
10 end;
11 /

Procedure created.

rwijk@ORA11GR1> select name
2 , type
3 , plsql_code_type
4 , plsql_optimize_level
5 , plsql_warnings
6 from user_plsql_object_settings
7 where name like 'P_'
8 order by name
9 /

NAME TYPE PLSQL_CODE_TYPE PLSQL_OPTIMIZE_LEVEL PLSQL_WARNINGS
------------------------------ ------------ ------------------------------ -------------------- ------------------------------
P1 PROCEDURE INTERPRETED 2 ENABLE:ALL
P2 PROCEDURE NATIVE 1 ENABLE:ALL
P3 PROCEDURE NATIVE 2 ENABLE:ALL

3 rows selected.
So unfortunately, this one is not resolved yet.


String aggregation using the model clause

Another thing I was a little bit worried about, was the ability to do string aggregation with the model clause. It works in 10.2.0.3, but it broke in 10.2.0.4. With some bad luck, Oracle could have implemented the same breaking code from 11.1.0.6 to 11.1.0.7, but luckily:

rwijk@ORA11GR1> select deptno
2 , rtrim(ename,',') enames
3 from ( select deptno
4 , ename
5 , rn
6 from emp
7 model
8 partition by (deptno)
9 dimension by (row_number() over
10 (partition by deptno order by ename) rn
11 )
12 measures (cast(ename as varchar2(40)) ename)
13 rules
14 ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
15 )
16 )
17 where rn = 1
18 order by deptno
19 /

DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

Something else has been resolved as well, but that's something for a new post ...

Tuesday, October 14, 2008

Oracle ACE

Still in the afterglow of my Oracle OpenWorld experience, I received yet another nice Oracle surprise: an e-mail telling me I'm now an Oracle ACE.

You have been chosen based on your significant contribution and activity in the Oracle technical community. Like your fellow Oracle ACEs, you have demonstrated a proficiency in Oracle technology as well as a willingness to share your knowledge and experiences with the community.
I'm truly honored to be the 199th Oracle ACE in the world and the 12th (!) from the Netherlands. You can see my profile here. Thanks Tonguç and Nicolas for the nominations!