Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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
              ') )
*/
share|improve this answer
add comment

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') )
*/
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.