玉面飞龙的BLOG

When Oracle create a single column B-tree index, NULL values will not be stored in index blocks. But if we create combine index, as long as whole index columns are not null, NULL value can be stored in index.

But those NULL values are not stored in index blocks in order. This is what I found today.

let us look example,

drop table test;

create table test(A number,B number,C number,D varchar(20));

create index test_ix1 on test(A,B,C,’a');

Usually we use index to avoid SORTS. Look at below SQL , its plan is what we expects. It can scan the index entries in order  to avoid sort by column C.

select * from
(select /*+ first_rows */ * from  test   where
A = 1000 and B=100 and C>0 order by C
) where rownum<100;

—————————————————————-
| Id  | Operation                     | Name     | Rows  | Cost
—————————————————————-
|   0 | SELECT STATEMENT              |          |     1 |  1
|*  1 |  COUNT STOPKEY                |          |       |
|   2 |   VIEW                        |          |     1 |  1
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST     |     1 |  1
|*  4 |     INDEX RANGE SCAN          | TEST_IX1 |     1 |  2
—————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 - filter(ROWNUM<100)
4 - access(”A”=1000 AND “B”=100 AND “C”>SYSDATE@! AND “C” IS NOT NULL)
filter(”A”=1000 AND “B”=100 AND “C”>SYSDATE@!)

If we modify where clause to “A = 1000 and B is null and C>0 order by C”, the plan changed. It would do sorts.

select * from
(select /*+ first_rows */ * from  test   where
A = 1000 and B is null and C>0 order by C
) where rownum<100;

—————————————————————–
| Id  | Operation                      | Name     | Rows  | Cost
—————————————————————–
|   0 | SELECT STATEMENT               |          |     1 |  2
|*  1 |  COUNT STOPKEY                 |          |       |
|   2 |   VIEW                         |          |     1 |  2
|*  3 |    SORT ORDER BY STOPKEY       |          |     1 |  2
|   4 |     TABLE ACCESS BY INDEX ROWID| TEST     |     1 |  1
|*  5 |      INDEX RANGE SCAN          | TEST_IX1 |     1 |  2
—————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 - filter(ROWNUM<100)
3 - filter(ROWNUM<100)
5 - access(”A”=1000 AND “B” IS NULL AND “C”>SYSDATE@! AND “C” IS NOT NULL)
filter(”C”>SYSDATE@!)

Why does the SQL can NOT make use of indexed column C to avoid memory sorts ? Is it related to NULL at second column?

Think that NULL value are UN-comparable.  It means that Oracle can’t judge which NULL is bigger or smaller than another NULL.  So how Oracle judge who is bigger, (NULL,1)  and (NULL,100), who is bigger?

When oracle insert “(NULL,1)” and “(NULL,2)” into index block? how does Oracle order them in leaf block?

Let me dump index leaf blocks to prove my guess.

begin
for i in 1..10000 loop
insert into test values(i,null,mod(i,10),’a');
end loop;
commit;
end;
/

Leaf block dump
===============
header address 4403368540=0×10676125c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 1
kdxconro 277
kdxcofbo 590=0×24e
kdxcofeo 3354=0xd1a
kdxcoavs 2764
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
kdxleprv 4224497=0×4075f1
kdxledsz 0
kdxlebksz 8032
row#0[8015] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 19
col 1; NULL
col 2; len 2; (2):  c1 05
col 3; len 6; (6):  00 40 75 ed 01 c8
row#1[7998] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 1a
col 1; NULL
col 2; len 2; (2):  c1 06
col 3; len 6; (6):  00 40 75 ed 01 c9
row#2[7981] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 1b
col 1; NULL
col 2; len 2; (2):  c1 07
col 3; len 6; (6):  00 40 75 ed 01 ca
row#3[7964] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 1c
col 1; NULL
col 2; len 2; (2):  c1 08
col 3; len 6; (6):  00 40 75 ed 01 cb
row#4[7947] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 1d
col 1; NULL
col 2; len 2; (2):  c1 09
col 3; len 6; (6):  00 40 75 ed 01 cc
row#5[7930] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 1e
col 1; NULL
col 2; len 2; (2):  c1 0a
col 3; len 6; (6):  00 40 75 ed 01 cd
row#6[7914] flag: ——, lock: 2, len=16
col 0; len 3; (3):  c2 62 1f
col 1; NULL
col 2; len 1; (1):  80
col 3; len 6; (6):  00 40 75 ed 01 ce
row#7[7897] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 20
col 1; NULL
col 2; len 2; (2):  c1 02
col 3; len 6; (6):  00 40 75 ed 01 cf
row#8[7880] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 21
col 1; NULL
col 2; len 2; (2):  c1 03
col 3; len 6; (6):  00 40 75 ed 01 d0
row#9[7863] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 22
col 1; NULL
col 2; len 2; (2):  c1 04
col 3; len 6; (6):  00 40 75 ed 01 d1
row#10[7846] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 23
col 1; NULL
col 2; len 2; (2):  c1 05
col 3; len 6; (6):  00 40 75 ed 01 d2
row#11[7829] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 24
col 1; NULL
col 2; len 2; (2):  c1 06
col 3; len 6; (6):  00 40 75 ed 01 d3
row#12[7812] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 25
col 1; NULL
col 2; len 2; (2):  c1 07
col 3; len 6; (6):  00 40 75 ed 01 d4
row#13[7795] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 26
col 1; NULL
col 2; len 2; (2):  c1 08
col 3; len 6; (6):  00 40 75 ed 01 d5
row#14[7778] flag: ——, lock: 2, len=17
col 0; len 3; (3):  c2 62 27
col 1; NULL
col 2; len 2; (2):  c1 09
col 3; len 6; (6):  00 40 75 ed 01 d6

let us look at “col 2″, it values is apparently not in ordered.

It proves what I guess. Oracle think each NULL are different with other NULL.  We can think that they ‘re still in ordered, but not in “bigger or smaller ” order. So we can NOT use third indexed column to avoid memory sorts when querying b=null.