Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance

Question

This is as a result of this question.
So I'm working with a query that looks like this:

select count(*) 
from table1 
where col1 = 123 
and col2 = '1';

The problem is this: col2 is defined as a CHAR(1) and all sessions set NLS_SORT = 'BINARY_CI' and NLS_COMP = 'LINGUISTIC' . (col1 is a NUMBER column.)

First off, without the NLS settings on for the sessions, defining a multicolumn extended stat over (col1,col2) works fine and produces appropriate cardinality.

But with NLS_SORT = 'BINARY_CI' and NLS_COMP = 'LINGUISTIC', it doesn't use the extended stats. I believed its much like an index and the NLS settings - we had to define indexes with (NLSSORT("COL2",'nls_sort=''BINARY_CI''')) .

So, I implemented a multi column extended stats over ("COL1", (NLSSORT("COL2",'nls_sort=''BINARY_CI''')) . But it still doesn't work (but an index defined the same will work).

In the predicate information, it shows that with the NLS settings on, col2 = '1' is transformed into: NLSSORT(INTERNAL_FUNCTION(col2),'nls_sort=''BINARY_CI''')=HEXTORAW('3100') . I believe that the INTERNAL_FUNCTION() is due to col2 being defined as CHAR(1). I can't change that.

How can I create a multicolumn extended stat column group for these columns given my conditions?

Answer 1

I think your only option here is to 'roll your own' pseudo-multicolumn stats - as extended statistics cannot be created on Virtual Columns. For example:

testbed:

drop table table1;
create table table1(col1 integer, col2 char(1));
insert into table1(col1,col2) select mod(level,10), '0' from dual connect by level<=1000;
insert into table1(col1,col2) select mod(level,10)+100, '1' from dual connect by level<=1000;
commit;
select count(*) from table1 where col1=1 and col2='0';
/*
COUNT(*)
--------
     100
*/

first try with normal histograms (note 'rows' estimate is 'poor'):

exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col1=1 and col2='0';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    50 |   250 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |    50 |   250 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"=1 AND "COL2"='0')
*/

now create virtual concatenated column (note 'rows' estimate is 'good'):

alter table table1 add col21 generated always as (col2||col1); 
exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col21='01';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   100 |   900 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |   100 |   900 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL21"='01')
*/

finally repeat with NLS parameters set:

alter session set nls_sort='BINARY_CI';
alter session set nls_comp='LINGUISTIC';
explain plan for select * from table1 where col21='01';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   100 |   900 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |   100 |   900 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("COL21",'nls_sort=''BINARY_CI''')=HEXTORAW('303100
              ') )
*/

Answer 2

without index:

drop table table1;
create table table1(col1 integer, col2 char(1));
insert into table1(col1,col2) select mod(level,10), '0' from dual connect by level<=1000;
insert into table1(col1,col2) select mod(level,10)+100, '1' from dual connect by level<=1000;
exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col1=1 and col2='0';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    50 |   250 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |    50 |   250 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("COL1"=1 AND "COL2"='0')*/

with index:

alter session set nls_sort='BINARY_CI';
alter session set nls_comp='LINGUISTIC';
create index i_table1 on table1(col1,nlssort("COL2",'nls_sort=''BINARY_CI'''));
exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col1=1 and col2='0';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   100 |   800 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1   |   100 |   800 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_TABLE1 |   100 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("COL1"=1 AND NLSSORT(INTERNAL_FUNCTION("COL2"),'nls_sort=''BINARY_
              CI''')=HEXTORAW('3000') )
*/