## NOT IN and NULL

I posted about Unexpected results in June 2005. Here are more results from NOT IN and NULL

``````select * from dual
WHERE (1) NOT IN (SELECT NULL FROM DUAL);
no rows selected
``````

``````select * from dual
WHERE (1,1) NOT IN (SELECT NULL,1 FROM DUAL);
no rows selected
``````

However, and this surprised me,
``````select * from dual
WHERE (1,1) NOT IN (SELECT NULL,2 FROM DUAL);

D
-
X``````

ðŸ™„

Probably the expression
where (a,b) not in (select c,d from t)
is translated into
where (a!=c or b!=d) — first row of t
and (a!=c or b!=d) — second row of t
— and …

## MIN(DISTINCT X)

What is doing the MIN(DISTINCT X) call? Basically, every distinct value of X is passed to the MIN function. Well, it is probably of very little interest as the MIN function is very fast and processing less rows than MIN(X) should not boost the performance because of the overhead of sorting distinct values.

However, if you write your own aggregate, distinct may be interesting!

``````create type myudag_type as object
(
myudag INTEGER,
static function ODCIAggregateInitialize(
sctx IN OUT myudag_type)
return number,
member function ODCIAggregateIterate(
self IN OUT myudag_type,
value IN INTEGER)
return number,
member function ODCIAggregateTerminate(
self IN myudag_type,
returnValue OUT INTEGER,
flags IN number)
return number,
member function ODCIAggregateMerge(
self IN OUT myudag_type,
ctx2 IN myudag_type)
return number
);
/

create or replace type body myudag_type is
static function ODCIAggregateInitialize(
sctx IN OUT myudag_type)
return number is
begin
sctx := myudag_type(0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
self IN OUT myudag_type,
value IN INTEGER)
return number is
begin
-- doing nothing will cost you a lot !!!
for i in 1..1000000 loop null; end loop;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self IN myudag_type,
returnValue OUT INTEGER,
flags IN number) return number is
begin
returnValue := self.myudag;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self IN OUT myudag_type,
ctx2 IN myudag_type)
return number is
begin
return ODCIConst.Success;
end;
end;
/

CREATE FUNCTION myudag (
input INTEGER)
RETURN INTEGER
AGGREGATE USING myudag_type;
/

SQL> select myudag(deptno) from emp;

MYUDAG(DEPTNO)
--------------
0

Elapsed: 00:00:00.57
SQL> select myudag(distinct deptno) from emp;

MYUDAG(DISTINCTDEPTNO)
----------------------
0

Elapsed: 00:00:00.13

``````

## next patchset for Oracle 10gR2

10.2.0.4 should be available this year on Linux x86. Check Metalink Certification for different plateforms.

Certify – Additional Info Oracle Database – Enterprise Edition Version 10gR2 On Linux (x86)

Operating System: Linux (x86) Version SLES-10
Oracle Database – Enterprise Edition Version 10gR2
N/A Version N/A
Status: Certified
Product Version Note:
None available for this product.
Certification Note:
``````Existing patch sets:
10.2.0.2
10.2.0.3
10.2.0.4 Q4CY2007
``````

## ContentDB installation

Yesterday I installed ContentDB. The installation is fairly straightforward.

• OID 10.1.4.0.1
• Install the Infrastructure, with the Identity Management and Repository, including Internet Directory, Single Sign-on, Delegated Administration Services and Directory provisioning

This will create a 10.1.0.5 database. The default parameters are too low for contentDB.

``````
alter system set
processes=250
scope=spfile
sga_max_size=629145600
scope=spfile
shared_pool_size=184549376
scope=spfile
java_pool_size=125829120
scope=spfile
db_cache_size=150994944
scope=spfile
scope=spfile
scope=spfile
job_queue_processes=10
scope=spfile
session_max_open_files=50
scope=spfile
open_cursors=400
scope=spfile
star_transformation_enabled=true
scope=spfile
pga_aggregate_target=203423744
scope=spfile;
``````

It is recommended to stop the application server infrastructure before restarting the database to make the parameters above effective.
``````
\$ \$ORACLE_HOME/bin/emctl stop iasconsole
\$ \$ORACLE_HOME/opmn/bin/opmnctl stopall
\$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup
\$ \$ORACLE_HOME/opmn/bin/opmnctl startall
\$ \$ORACLE_HOME/bin/emctl start iasconsole
``````

It is now possible to install ContentDB in this database. Of course the ContentDB could be installed in a separate database, it does not have to be the same as the infrastructure database.

• Content Database 10.2.0.0.0
• Install the ContentDB

That’s all. There now two application server instances, one for the infrastructure and one for the content database.

To launch the Content Database web interface, just go to the http server of the ContentDB installation, something like http://server:7779.

``````\$ /app/oracle/product/10.1.2/cdb_1/bin/opmnctl status -l

Processes in Instance: CONTENTDB01.srv01
-------------------+----------+------------------------
ias-component      | status   | ports
-------------------+----------+------------------------
DSA                | Down     | N/A
HTTP_Server        | Alive    | http1:7779,http2:7202
dcm-daemon         | Alive    | N/A
OC4J               | Alive    | ajp:12503,rmi:12403,...
WebCache           | Alive    | http:7778,invalidati...
Content            | Alive    | node_dms_http:53900,...
Content            | Alive    | node_manager_locator...
``````

ContentDB interface let you upload and download files. You can use it to keep your documentation in a single location. It has versioning capabilities too.

## Oracle OpenWorld Unconference

Oracle OpenWorld will host the

On the wiki.oracle.com Wiki, you can create your own unconference session.

I have proposed a session about SQL Model :
SQL Model

## 11g on Linux x86_64

Niall Litchfield just wrote about 11g availability on 64bits linux.

Happy Birthday Niall

## Database Internals Newsletter October 2007

Oracle Support started a series about database internals.

 Volume 1: October, 2007

## What is bigger than infinity?

Nan

``````
select
BINARY_DOUBLE_INFINITY INF,
BINARY_DOUBLE_NAN NAN,
greatest(BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_NAN) GRE
from t;

INF NAN GRE
--- --- ---
Inf Nan Nan
``````

Nan means not a number. It could be square root of -1, log of -1, 0/0, acos(1000), Inf-Inf, etc…

``````
select
SQRT(-1d),
LN(-1d),
0/0d,
acos(1000d),
BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
from t;
SQR LN- 00D ACO BIN
--- --- --- --- ---
Nan Nan Nan Nan Nan
``````

According to the doc, it is greater than any value, inclusive positive infinity.

To check if a value is nan, it could be compared to BINARY_DOUBLE_NAN.
`where :z = BINARY_DOUBLE_NAN`
There is a function NANVL(:z, :y) which evaluates to :y when :z is equal Nan. if :z is not equal to Nan and :y is not null, then it evaluates to :z. NANVL evaluates to NULL when :z or :y is null.

``````
select NANVL(1,null) from dual;
NANVL
------
[null]
``````