主页 / dba / 问题 / 25494
Accepted
rfusca
Asked: 2012-10-06 05:28:54 +0800 CST

多列扩展统计、NLS_SORT 和 NLS_COMP、CHAR 列以及我存在的祸根

  • 772

这是这个问题的结果。
所以我正在处理一个看起来像这样的查询：

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

问题是这样的： col2被定义为一个CHAR(1)和所有会话集NLS_SORT = 'BINARY_CI'和NLS_COMP = 'LINGUISTIC'。（col1是一NUMBER列。）

首先，在没有为会话启用 NLS 设置的情况下，定义一个多列扩展统计(col1,col2)可以正常工作并产生适当的基数。

但是对于NLS_SORT = 'BINARY_CI'and NLS_COMP = 'LINGUISTIC'，它不使用扩展统计信息。我相信它很像一个索引和 NLS 设置——我们必须用(NLSSORT("COL2",'nls_sort=''BINARY_CI''')).

因此，我在("COL1", (NLSSORT("COL2",'nls_sort=''BINARY_CI''')). 但它仍然不起作用（但定义相同的索引将起作用）。

在谓词信息中，它显示在启用 NLS 设置的情况下，col2 = '1'转换为： NLSSORT(INTERNAL_FUNCTION(col2),'nls_sort=''BINARY_CI''')=HEXTORAW('3100')。我相信这INTERNAL_FUNCTION()是由于col2被定义为CHAR(1)。我无法改变这一点。

根据我的条件，如何为这些列创建多列扩展统计列组？

oracle-11g-r2

2 个回答

  1. Best Answer

    我认为您在这里唯一的选择是“滚动自己的”伪多列统计信息——因为无法在 Virtual Columns 上创建扩展统计信息。例如：

    试验台：

    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
*/

    首先尝试使用正常直方图（注意“行”估计是“差”）：

    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')
*/

    现在创建虚拟串联列（注意“行”估计是“好”）：

    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')
*/

    最后重复 NLS 参数集：

    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
              ') )
*/
    • 2

  2. 没有索引：

    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')*/

    带索引：

    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') )
*/
    • 1

相关问题