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!

Sunday, September 28, 2008

Oracle OpenWorld, day 5

Thursday morning I decided to switch sessions, Mogens Norgaard's for Tanel Poder's session. I had to queue up, because the room was fully booked, but not everybody showed up, so all people in the line-up could enter the room. I sure was glad I switched, as this was the best session at this conference for me. The title was "Advanced Oracle Troubleshooting: No magic is needed - a systematic approach will do". He outlined a systematic approach to answer the question "What the $#*&%! is that session doing?". My personal favourite SQL*Trace collects all work done for one process or single query, but this isn't any good when the process or query doesn't return results. And that's exactly what this method was about. Tanel showed his collection of scripts that makes the querying against v$session_wait, v$session_event and v$sesstat a lot easier. And when that doesn't help, there's always the process stack of course :-). And actually it is a lot easier than it looks initially, especially with Tanel showing it. All qer... calls are just query execution row source lines, corresponding to row source operations you see in your execution plan. The meaning of each individual line can be looked at MetaLink note 175982.1. A lot of eye-openers in an awesome session. You can find the material he used here on his blog.

Then I went to see the "Oracle ACE Director Birds-of-a-Feather Tips and Techniques Panel" by Oracle Aces Lewis Cunningham, Arup Nanda, Tim Hall, Eddie Awad, Mark Rittman, Hans Forbrich and Bradley Brown. All attendees could write a question on a piece of paper, the papers were collected, Lewis read the questions and the panel answered them. Didn’t hear anything new, but it was entertaining.

Next session was Juan Loaiza session called "Oracle Exadata Architecture Overview". He explained the techniques behind the Exadata concept in more detail, even the physical layout. In short it ships less data through pipes by using smart scans (see this whitepaper), has more pipes and has bigger pipes using Infiniband. It is designed to gracefully tolerate cell brownouts. Not really relevant to an average developer, but the concept of smart scanning can be relevant when trying to understand how queries are executed in this architecture.

Then Chen Shapira’s unconference session about Streams. She explained the Streams concepts Capture, Propagate and Apply. In the OTN Streams Forum she participates in, a lot of questions are like "I configured Streams exactly like it said in the documentation, but it doesn’t work. Help!" and that’s why she spent the main part explaining how to troubleshoot, using a live demo. It’s about systematically checking all parts. Initially the source must be equal to the target. Start with Capture; check dba_capture.status to see if it is enabled or look at the alert.log to see if anything is wrong. For Apply, check the automatically created error queue. And then check the rules. I entered the session only knowing Streams is something about replication with redo logs using Advanced Queueing under the covers, and now I think I actually understand the whole process conceptually and I know where to look when something doesn’t work. So the session was very good. Check out her blog for more details on Streams and this session.

The last session of Oracle OpenWorld was Gaya Vaidyanatha’s "Oracle Performance Management: Real-World Case Studies". He’s the guy who invented the term Compulsive Tuning Disorder, explained in the "Oracle Insights: Tales of the OakTable" book. The session was a collection of war stories where the developer – of course – did something extremely stupid, how he detected the problem in the trace files and what the solution was and how much it solved. And how he made many hardware vendors extremely pissed off with his advice. Gaya is an excellent speaker: strong voice, charismatic and humorous. So this was an entertaining end of Oracle OpenWorld.

Looking back at Oracle OpenWorld I think it was a fantastic event. It’s a great place to meet like-minded people you know from the articles, blogs and OTN forums. There were a lot of very good sessions, but you have to choose them carefully or you'll end up in some marketing talk. Also compliments to the team who organized the event. I cannot fathom the work that needs to be done to make this all happen, but they did. And of course, thanks to my manager Roel, for allowing me to visit Oracle OpenWorld! Next year again?

Friday, September 26, 2008

Oracle OpenWorld, day 4

Before going to some sessions I had a walk around the two exhibition halls for the first time. Not very interesting for me: a lot of vendors exhibiting and some game rooms. The demo grounds were more interesting although it was a little bit too busy. I managed to see a preview of SQL Developer's data modeling extension, but more on that later. I was a little disappointed for not seeing a stand for PL/SQL as I would have liked to express my wish to people of the PL/SQL team to extend the sql%bulk_exceptions array with the actual error message (see this post). I had seen Bryn Llewellyn walking around in Oracle OpenWorld though.

Next I arranged to meet my ex-colleague Hans Driessen and drank a smoothie. Anjo Kolk was also supposed to be there, but he was having a hard time after Mogens' party last night according to Hans ...

The first session was Mark Drake's "Oracle Application Express and Oracle XML Database: A Match Made in the Database". Carl Backstrom was on stage too, to answer some APEX related questions. The session showed how to present XML content in APEX screens. When you just select the XMLTYPE column you'll not see the contents. Using XMLSerialize the results are a little better, but all the tags in the XML are then interpreted by the browser and effectively removed from the screen. Using XMLTransform the tags are visible. In a last variant, that I didn't manage to write down, the XML appeared all nicely. I thought the session was pretty good, although I would still have to look things up when I need it.

Next session was Rene de Vleeschauer's "Database Data Modeling with Oracle SQL Developer". Entity relationship modeling and logical data modeling can be done within SQL Developer now. However, it will be a non free option whereas SQL Developer itself is free of charge. You can import existing data models from Designer - both ERD and server models -, Erwin and from Oracle Warehouse Builder. It supports all database objects, even some less common ones. Creation scripts and alter scripts can be generated from the models. It looked like it can replace Designer's data modeling side completely. So, good work!

Now it was time for Larry's big announcement. Although there are TV screens all across the buildings, I wanted to experience it myself and went to Moscone North, hall D. I certainly wasn't alone: roughly estimated 7000 people also saw the show. Due to my bloggers pass, I had a nice seat in the center amidst press, analysts and other bloggers. Larry announced the Exadata Programmable Storage Server and the HP Oracle Database Machine and promised at least 10 times faster results. For an outstander it must have looked pretty nerdish: 7000 business people and techies applauding and cheering for a 4 crate high intelligent storage array on stage. For the large companies out there it should be spectacular though.

Then one last session, "Managing Very Very Large XML Documents with Oracle XML Database". I had a hard time hearing the story and the slides were hardly readable, so I quickly lost interest.

In the evening we all went to Treasure Island, between San Franciso and Oakland, for the appreciation event. An impressive event, to say the least. Good food, a nice boardwalk carnival and six artists, of which I saw four: the Psychedelic Furs, Seal, Elvis Costello and UB40. I had a good time and met quite a few more people whose blogs are in my Google Reader. Came back at the hotel at half past twelve.

Wednesday, September 24, 2008

Oracle OpenWorld, day 3

Tuesday was very diverse. I missed the 9AM session called "How to hack an Oracle Application Express application", because I wanted to finish my previous blogpost. However, before the third session started, I heard two people behind me talking how great this session was ...

So the first session I attended was Tom Kyte's keynote "The best way ...". I'm more a fan of technical sessions than keynotes, but this one was ok. He talked about best practices, that can only be good in your situation when the contexts are equal. If not, a best practice can easily be a nightmare. An example was the query "select * from t1, t2 where t1.id = t2.id and t1.small_distinct = :x". Two explain plans, one using two full table scans and a hash join, the other using index range scans, table accesses by rowid and a nested loop. Which plan is better? The answer is "it depends on the context". In a batch you definitely want the hash join to fetch all thousands of rows, but in an interactive application where you are only interested in the first row(s), you definitely want the indexed one. A quote I liked was "Don't tune a query - tune a process, an algorithm or the entire approach". Too often it occurs that a slow query can be tuned by not executing the query at all. I also liked this picture of Tom's favourite saying "Question Authority".

The next session was "Advanced Performance Diagnostics: What the GUI doesn't tell you", by Kurt Engeleiter and Jon Waldron. I didn't know the speakers, but the title looked interesting: advanced and something about numbers or statistics that are not visible through GUI's. They not only looked interesting to me: the room was full. However, the session was a big waste of time. Nothing advanced, only powerpointing through some basic AWR and ASH facts. And to top it off, I couldn't even follow the second speaker because he talked so fast with an Australian accent. Lesson learned: pick sessions more carefully and pick the ones from speakers you have heard of and know they are good.

Next in line was Steve Muench's "Oracle Application Development Framework 11g: New Declarative Validation, List of Values, Search and Services Features". The title says it all, I have nothing to add :-). Well ok, it was a good session with lots of new nifty features, clearly explained. The things that weren't clear, was not because of Steve's explanation, but because of my limited ADF knowledge. Declarative client side validations are great in general - provided they are validated in the database as well of course - and ADF 11g has delivered lots of it. APEX guys, please keep up!

I decided to skip Patrick Wolf's session "The Power of the Oracle Application Express Repository" in favour of Marcelo Ochoa's unconference session about REST webservices. He had only two attendees, so we got a private session about the differences between the SOAP and the REST protocol. The latter is capable of caching requests in a proxy due to an expiry date header and skip the XML-ish stuff, thus saving a lot of traffic and work. Next I talked with Marcelo about his DBPrism project. This software can be used to replace the mod_plsql engine of APEX and thus have proxy authentication inside APEX. According to Marcelo several companies already use his software for this reason and he never received bug reports, so it should be good. You can see the announcement on his blog here. I will definitely have to try it to see what's possible and what's not.

Since I skipped two sessions already, I just made it three and hang around in the OTN Lounge and talked to some now familiar people and with some new ones like Tim Hall (of ORACLE-BASE), Andreas Chatziantoniou and Hasan Tonguç Yilmaz.

The last session was Frank Nimphius' "Oracle Application Development Framework Web Application Security by Example". He showed several ways to implement client side security. The whole session I was thinking about why not implement this stuff in the database. Fortunately, in his last slides he recommended to do this as well, using features like VPD and database proxy authentication. Another plus was the live demo.

Now we'll have to wait for Larry's big "extreme performance" announcement tomorrow ...