Re: indexing null values curious case?
- From: Andy Sayer <andysayer@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Thu, 21 Nov 2019 12:38:52 +0000
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..
It’s quite the opposite, Oracle gives you a free frequency histogram for
null values with the num_nulls column. It will use these statistics when
relevant.
Thanks,
Andy
On Thu, 21 Nov 2019 at 12:18, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
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
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
Sent: 21 November 2019 11:07
To: Andy Sayer
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: indexing null values curious case?
Apparently looks like this entire section dealing with the null is not
developed in its entirety.. the following is a different case as well
regarding the nulls.
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;
insert into temp (roll, name, mark2) select rownum,
dbms_random.string(0,20), round(dbms_random.value(0,200) from dual connect
by level < 10;
insert into temp (roll, name, mark1) select rownum,
dbms_random.string(0,20), round(dbms_random.value(0,200) from dual connect
by level < 10;
commit;
create index idx on temp(mark1, mark2);
Here i have only 10 rows from each of the columns as null;
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
row#1[7992] flag: -------, lock: 2, len=14
col 0; len 3; (3): c2 02 04
col 1; NULL
col 2; len 6; (6): 01 c0 21 de 00 02
row#2[8006] flag: -------, lock: 0, len=13
col 0; NULL
col 1; len 2; (2): c1 02
col 2; len 6; (6): 01 c0 21 de 00 01
row#3[7978] flag: -------, lock: 2, len=14
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@xxxxxxxxx<mailto:vishnupotukanuma@xxxxxxxxx>> 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@xxxxxxxxx<mailto:
andysayer@xxxxxxxxx>> 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@xxxxxxxxx<mailto:vishnupotukanuma@xxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
