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

:roll:

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.

Download and install Oracle Identity Management

  • 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
  db_file_multiblock_read_count=32
    scope=spfile
  db_create_file_dest='/u02/oradata'
    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.

Now, download and install Oracle ContentDB

  • 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
LogLoader          | Down     | N/A
dcm-daemon         | Alive    | N/A
OC4J               | Alive    | ajp:12503,rmi:12403,...
WebCache           | Alive    | http:7778,invalidati...
WebCache           | Alive    | administration:9400
Content            | Alive    | node_dms_http:53900,...
Content            | Alive    | node_manager_locator...

Login with user ORCLADMIN and the password you specified for IAS_ADMIN.

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.

screenshot

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]