Re: indexing null values curious case?

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> 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
> row#1[8006] flag: -------, lock: 2, len=13
> col 0; len 2; (2): c1 03
> col 1; NULL
> col 2; len 6; (6): 01 c0 21 dc 00 02
> but things as expected, index doesn't store entries when the leading
> column is null.
>
> 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))
> Index: IDX
> resc_io: 1039.000000 resc_cpu: 237414695
> ix_sel: 0.000000 ix_sel_with_filters: 1.000000
> Access Path: index (FFS)
> Cost: 1045.663711 Resp: 1045.663711 Degree: 1
> 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