Re: indexing null values curious case?

Vishnu,

Your STUDENTS example looks like a bug. (The stats for the NULL and '' case are identical, by the way, so it's likely to be related to the way that Oracle stores the '' as the DEFAULT for the column definition).

re:
select * from temp where mark1 is null; --> it always goes with a full table scan It's got to; Oracle know that there are some rows with mark1 null, some with mark2 null, so it have to asume that the two sets of rows may overlap to give a row which does not appear in thei index.

select * from temp where mark1 is null and mark2 = 123123; Now you're looking for a row which (if it exists) will be in the index. You could equally change the mark2 = 123123 to mark2 is not null.

re:
all i did was add an extra column to the index (virtual column). So now you have a column in the index which is NEVER null, so every row in the table will appear in the index, so the index can be used for mark1 is null

re
mark2 is null
The possibly paths are
index skip scan
index full scan
index fast full scan
tablescan

The choice of tablescan is almost certainly dictated by cost.

BOTTOM LINE - your students case looks like a bug relating to the special case of the way Oracle handles the DEFAULT for a constant empty string. The rest are correct behaviour.

Regards
Jonathan Lewis

even with a multi-column index, considering another scenario as the following:
create table temp (roll number, name varchar2(20), mark1 number, mark2 number);
insert into temp select rownum, dbms_random.string(0,20), round(dbms_random.value(0,100), round(dbms_random.value(0,200)) from dual connect by level < 1000000;

exec dbms_Stats.gather_table_stats('VISHNU','TEMP',CASCADE=>TRUE);

from the leaf blocks we can find that the nulls are stored in the index. The leaf blocks containing the NULLs is as follows: row#0[8019] flag: -------, lock: 0, len=13

col 0; len 2; (2):  c1 02
col 1; NULL
col 2; len 6; (6):  01 c0 21 de 00 00

col 0; len 3; (3):  c2 02 04
col 1; NULL
col 2; len 6; (6):  01 c0 21 de 00 02

col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  01 c0 21 de 00 01

col 0; NULL
col 1; len 3; (3):  c2 0d 20
col 2; len 6; (6):  01 c0 21 de 00 03

if we issue a query like select * from temp where mark1 is null; --> it always goes with a full table scan even when 10 rows only have null values and apparently the index leaf blocks do contain entries regarding the null values), for the leading as well as the tail columns as shown above. But the wierd thing is if we include both the columns mark1, mark2 in the predicate clause as following: select * from temp where mark1 is null and mark2 = 123123; the optimizer selects the index.

now comes even better part.
drop the index and create the index as follows: create index idx on temp(mark1,mark2,1); all i did was add an extra column to the index (virtual column). collected the statistics and ran the query back again. select * from temp where mark1 is null;
this time it selects the index properly which makes me wonder if they have included or written the code or made the optimizer to consider the nulls in the predicate clause only in the presence of virtual columns.

Another part I believe is that Oracle's statistics (including histogram doesn't maintain any statistics regarding nulls, which makes sense in a way), as these are only explicit cases and the concept of statistics optimizer everything resolves only around the values that are present but not null..

also an another distinct behavior observed is this regarding of whether the index is create index idx on temp(mark1,mark2)
or create index idx on temp(mark1,mark2,1);

any query with predicates involving only mark2 regarding nulls goes with full table scan forget skip scans etc etc. queries like
select * from temp where mark1 is not null and mark2 is null; select * from temp where mark2 is null;
apparently some explanation for this can be the way oracle doesn't include nulls in the histograms.. and I am tired for the day!

I can't make any assumptions as of which of the optimizer code has to be developed just with these observations, but the way oracle handles nulls should be improved consistently.

can someone please tell me if i am missing any thing here? Oracle is a like a ocean. so many concepts no wonder i became bald at a very young age.

Thanks,
Vishnu

On Thu, Nov 21, 2019 at 2:05 PM Vishnu Potukanuma <vishnupotukanuma_at_gmail.com<mailto:vishnupotukanuma_at_gmail.com>> wrote: This is reproducible case and probably hitting an undeveloped section of oracle code or a bug. Sure, let me see what I can do.

Thanks,
Vishnu

On Thu, Nov 21, 2019 at 1:55 PM Andy Sayer <andysayer_at_gmail.com<mailto:andysayer_at_gmail.com>> wrote: Hi Vishnu

File your reproducible test case with Oracle support if you suspect you’re hitting a bug.

It is not expected for the existence of a special index to change the result of a query.

If you want to find null values and the selectivity is decent for an index then I would use an index on (column_name,0).

Thanks,
Andy

On Thu, 21 Nov 2019 at 08:18, Vishnu Potukanuma <vishnupotukanuma_at_gmail.com<mailto:vishnupotukanuma_at_gmail.com>> wrote: Hi,

If all the columns in the index are null then the entries are not stored in the index. This is a known fact and empty strings are stored as null values. combining both breaks the consistency part where the query returns wrong results.

this is really a worst case as no one creates such a index as (column_name,null) or (column_name,'') as this really doesn't make sense but this breaks the consistency aspect of database as the query gives wrong result as the optimizer consider index even when the leading column is null.

the scenario is basically as following:
Create table students (student_id number, name varchar2(20); insert into students select rownum, dbms_random.string(0,20) form dual connect by level < 1000000; insert into students (name) select dbms_random.string(0,20) from dual connect by level < 100; commit;
create index idx on student(student_id,null); exec dbms_stats.gather_Table_stats('VISHNU','students',cascade=>True); select * from students where student_id is null; in this case it goes with the full table scan which is correct since both nulls are not stored.

so now we drop the index idx;
Here we create the index as this.
create index idx on student(student_id,''); exec dbms_stats.gather_Table_stats('VISHNU','students',cascade=>True); select * from students where student_id is null; now Oracle retrives the results as 0, with the index scan.

the leaf blocks store the details as following since the empty strings are treated as nulls row#0[8019] flag: -------, lock: 0, len=13

col 0; len 2; (2):  c1 02
col 1; NULL
col 2; len 6; (6):  01 c0 21 dc 00 00

col 0; len 2; (2):  c1 03
col 1; NULL
col 2; len 6; (6):  01 c0 21 dc 00 02

here the oracle goes with the index scan and returns zero results. which is inconsistent result. optimizer trace considers that index with (student_id, '');

  Access Path: TableScan
    Cost: 21017.655599 Resp: 21017.655599 Degree: 0

      Cost_io: 20995.000000  Cost_cpu: 807173669
      Resp_io: 20995.000000  Resp_cpu: 807173669
  Access Path: index (index (FFS))

      Cost_io: 1039.000000  Cost_cpu: 237414695
      Resp_io: 1039.000000  Resp_cpu: 237414695

• Costing Index IDX SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER Access Path: index (IndexOnly) Index: IDX resc_io: 5.000000 resc_cpu: 235407 ix_sel: 6.6556e-04 ix_sel_with_filters: 6.6556e-04 Cost: 5.006607 Resp: 5.006607 Degree: 1 Best:: AccessPath: IndexRange Index: IDX Cost: 5.006607 Degree: 1 Resp: 5.006607 Card: 999.000000 Bytes: 0.000000

probably we are hitting an undeveloped section of optimizer? producing a wrong plan is ok, but producing wrong results?

Thanks,
vishnu

--
http://www.freelists.org/webpage/oracle-l