Search

Top 60 Oracle Blogs

Recent comments

NULL

NULL’s vs NOT NULL’s and Performance

When it comes to giving the cost based optimiser the best possible chance to make the “right” decisions, many DBA’s are diligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes).

However one often neglected area is that the the null-ness of columns also impacts the optimiser decisions. NULL and NOT NULL do more than just act as constraints, they also add (or detract) to the value of indexes on those columns. Here’s an example of how the null-ness of a column impacts optimizer decisions. I have a table T which is a copy of DBA_OBJECTS, indexed on OBJECT_ID.

Not In CTAS

Everyone gets caught out some of the time with NOT IN.

NOT IN is not the opposite of IN.

This came up in a (fairly typical) question on OTN recently where someone had the task of “deleting 6M rows from a table of 18M”. A common, and perfectly reasonable, suggestion for dealing with a delete on this scale is to consider creating a replacement table holding the data you do want rather than deleting the data you don’t want.  In this case, however, the query for deleting the data looked like this:


DELETE FROM EI.CASESTATUS
     WHERE CASEID NOT IN (SELECT CASEID FROM DO.STG_CASEHEADER);

The suggested code for creating the kept data was this:

Quiz night

Here’s a script to create a table, with index, and collect stats on it. Once I’ve collected stats I’ve checked the execution plan to discover that a hint has been ignored (for a well-known reason):

Cluster Nulls

Yesterday’s posting was a reminder that bitmap indexes, unlike B-tree indexes in Oracle,  do store entries where every column in the index is null. The same is true for cluster indexes – which are implemented as basic B-tree indexes. Here’s a test case I wrote to demonstrate the point.

Cluster Nulls

Yesterday’s posting was a reminder that bitmap indexes, unlike B-tree indexes in Oracle,  do store entries where every column in the index is null. The same is true for cluster indexes – which are implemented as basic B-tree indexes. Here’s a test case I wrote to demonstrate the point.

Bitmap Nulls

It’s fairly well known that in Oracle B-tree indexes on heap tables don’t hold entries where all the indexed columns are all null, but that bitmap indexes will hold such entries and execution plans can for predicates like “column is null” can use bitmap indexes. Here’s a little test case to demonstrate the point (I ran this last on 12.1.0.1):

Bitmap Nulls

It’s fairly well known that in Oracle B-tree indexes on heap tables don’t hold entries where all the indexed columns are all null, but that bitmap indexes will hold such entries and execution plans can for predicates like “column is null” can use bitmap indexes. Here’s a little test case to demonstrate the point (I ran this last on 12.1.0.1):

NVL()

Here’s a little detail that I hadn’t noticed before (and it goes back to at least 8i). This is running on 11.2.0.3, and table t1 is just all_objects where rownum <= 20000:

Some stuff about dates and timestamps in Oracle

set null ~ pagesize 40 linesize 132;
column id format 90;
column datetrunc format a30;
column dateuntrunc format a30;
column timetrunc format a30;
column timeuntrunc format a30;
create table time_size2
(
id number,
datetrunc date,
dateuntrunc date,
timetrunc timestamp,
timeuntrunc timestamp
);
insert into time_size2
select rownum, trunc(sysdate), sysdate,trunc(sysdate),sysdate
from dual;
insert into time_size2 values (2,null,null,null,null);
commit;
select * from time_size2;
select
to_char(datetrunc, 'YYYY MM DD HH24:MI:SS') "datetrunc",
to_char(dateuntrunc,'YYYY MM DD HH24:MI:SS') "dateuntrunc",
to_char(timetrunc, 'YYYY MM DD HH24:MI:SS.FF9') "timetrunc",
to_char(timeuntrunc,'YYYY MM DD HH24:MI:SS.FF9') "timeuntrunc"
from
time_size2;

Null – again

Here’s a (camouflaged) constraint definition I came across a little while ago in a production system:


create table t1 (
	v1 varchar2(10),
	constraint c1 check (v1 = 'A' or v1 = null)
);

Quick question – will the following insert statement work or return an error ?

insert into t1 values('B');

The answer is that the row gets inserted – and that’s probably not an intended result.

It’s amazing how often NULL rears its ugly head as the cause of unexpected behaviour. Remember that a constraint accepts a value if the constraint does not evaluate to false. If you put ‘B’ into the constraint definition above, we get:

       'B' = 'A' or 'B' = null
hence   FALSE or NULL
hence   NULL