Início / dba / Perguntas / 25494
Accepted
rfusca
Asked: 2012-10-06 05:28:54 +0800 CST

Estatísticas estendidas de várias colunas, NLS_SORT e NLS_COMP, colunas CHAR e a ruína da minha existência

  • 772

Isso é resultado dessa pergunta .
Então, estou trabalhando com uma consulta que se parece com isso:

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

O problema é este: col2é definido como um CHAR(1)e todas as sessões definidas NLS_SORT = 'BINARY_CI'e NLS_COMP = 'LINGUISTIC'. ( col1é uma NUMBERcoluna.)

Em primeiro lugar, sem as configurações de NLS ativadas para as sessões, definir uma estatística estendida de várias colunas (col1,col2)funciona bem e produz cardinalidade apropriada.

Mas com NLS_SORT = 'BINARY_CI'e NLS_COMP = 'LINGUISTIC', ele não usa as estatísticas estendidas. Achei muito parecido com um índice e as configurações do NLS - tivemos que definir índices com (NLSSORT("COL2",'nls_sort=''BINARY_CI''')).

Então, implementei estatísticas estendidas de várias colunas em ("COL1", (NLSSORT("COL2",'nls_sort=''BINARY_CI''')). Mas ainda não funciona (mas um índice definido da mesma forma funcionará).

Nas informações do predicado, mostra que com as configurações do NLS ativadas, col2 = '1'é transformado em: NLSSORT(INTERNAL_FUNCTION(col2),'nls_sort=''BINARY_CI''')=HEXTORAW('3100'). Eu acredito que o INTERNAL_FUNCTION()é devido a col2ser definido como CHAR(1). Eu não posso mudar isso.

Como posso criar um grupo de colunas estatísticas estendidas de várias colunas para essas colunas, dadas minhas condições?

oracle-11g-r2

2 respostas

  1. Best Answer

    Acho que sua única opção aqui é 'rolar suas próprias' estatísticas pseudo-multicolunas - já que estatísticas estendidas não podem ser criadas em colunas virtuais . Por exemplo:

    banco de ensaio:

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

    primeiro tente com histogramas normais (observe que a estimativa de 'linhas' é 'ruim'):

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

    agora crie uma coluna virtual concatenada (observe que a estimativa de 'linhas' é 'boa'):

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

    finalmente, repita com os parâmetros NLS definidos:

    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. sem índice:

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

    com índice:

    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

relate perguntas