Database Software

In Oracle, when indexes are created on columns that contain null values, Oracle index ignores those null values. Other engines like Sybase use the index for one reason or other even when the where clause refers to NULL values. It would be interesting to see how TT copes with this.

We create a test table in Oracle with some nulls in the column object_id.

mich@MYDB.MICH.LOCAL> create table nulltest as select * from sys.DBA_OBJECTS;

Table created.

We then create a normal index on the OBJECT_ID column

mich@MYDB.MICH.LOCAL> create index ori_index on nulltest(object_id);

Index created.

Update stats on this table

mich@MYDB.MICH.LOCAL> analyze table nulltest compute statistics;

Table analyzed.

mich@MYDB.MICH.LOCAL> set autotrace on

Use a simple SELECT statement with where clause on object_id. First try IS NOT NULL! The IS NOT NULL type WHERE clause should use the index

mich@MYDB.MICH.LOCAL> select count(*) from nulltest where object_id IS NOT NULL;

COUNT(*)
----------
73333


Execution Plan
----------------------------------------------------------
Plan hash value: 1556236596

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 46 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| ORI_INDEX | 73333 | 286K| 46 (0)| 00:00:01 | <-- uses the index
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID" IS NOT NULL)

Now try the same select for records with object_id IS NULL. The WHERE clause should not use the index and should do a table scan

1* select count(*) from nulltest where object_id IS NULL
mich@MYDB.MICH.LOCAL> /

COUNT(*)
----------
2


Execution Plan
----------------------------------------------------------
Plan hash value: 1155341373

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 293 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| NULLTEST | 2 | 8 | 293 (1)| 00:00:04 | <-- does a table scan
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID" IS NULL)

create the same nulltest table in TT and load data from Oracle.

Command> create index ori_index on nulltest(object_id);
Command> indexes nulltest;

Indexes on table CACHEADM.NULLTEST:
NULLTEST_ID1: non-unique T-tree index on columns:
OWNER
OBJECT_NAME
ORI_INDEX: non-unique T-tree index on columns:
OBJECT_ID
2 indexes found.

2 indexes found on 1 table.
Command> call ttOptUpdateStats ('NULLTEST'); -- update stats
Command> set autocommit 0
Command> set showplan 1
Command> timing 1
Command> select count(*) from nulltest where object_id IS NOT NULL;

Query Optimizer Plan:

STEP: 1
LEVEL: 1
OPERATION: TblLkSerialScan <-- doing a table scan
TBLNAME: NULLTEST
IXNAME: <NULL>
INDEXED CONDITION: <NULL>
NOT INDEXED: NULLTEST.OBJECT_ID IS NOT NULL

< 73335 >
1 row found.
Execution time (SQLExecute + Fetch Loop) = 0.008702 seconds.

-- Now try to see whether index is used for NULL values

Command> select count(*) from nulltest where object_id IS NULL;

Query Optimizer Plan:

STEP: 1
LEVEL: 1
OPERATION: RowLkTtreeScan
TBLNAME: NULLTEST
IXNAME: ORI_INDEX <-- index is used
INDEXED CONDITION: NULLTEST.OBJECT_ID = NULL
NOT INDEXED: <NULL>

< 0 >
1 row found.
Execution time (SQLExecute + Fetch Loop) = 0.000041 seconds.

So this behaviour is different from Oracle! It seems that unlike Oracle NULL values in TT are indexed.

In Oracle, you can work around this problem albeit with a bit of overhead. Going back to my original table nulltest, I will drop that index on object_id column and will create an index with a constant used as part of the index "1". The expression "1" is - a NOT NULL virtual column, a function, an expression, not a column. This will force the concatenated index to have pointers. Since you have an index row object_id\\<CONSTANT> where object_id is null but column 2 i.e. “1” below is not null then that concatenated key index value will have pointers.

mich@MYDB.MICH.LOCAL> create index nulltest_f_idx on nulltest(object_id,1);

Index created.

mich@MYDB.MICH.LOCAL> analyze table nulltest compute statistics;

Table analyzed.

mich@MYDB.MICH.LOCAL> set autotrace traceonly
mich@MYDB.MICH.LOCAL> select count(*) from nulltest where object_id IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 4114015762

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| NULLTEST_F_IDX | 1 | 4 | 1 (0)| 00:00:01 | <-- the index is now used
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID" IS NULL

Unlike Oracle TT does not allow a virtual column

Command> create index nulltest_f_idx on nulltest(object_id,1);
1001: Syntax error in SQL statement before or at: "1", character position: 51
...te index nulltest_f_idx on nulltest(object_id,1)
^
The command failed.

The conclusion to be drwan from this is that in contrast to Oracle NULL values are considered in an index in TT. Also in TT a where clause with "<COLUMN> is NOT NULL" results in table scan.