Blog do Marcio Portes

Quem nunca ouviu dizer, que devemos evitar o uso de funções em comparações na clausula WHERE, porque podemos invalidar o uso de índice caso esse exista. É uma boa prática de programação e o que vou demonstrar abaixo não a invalida, porém, mostra a capacidade do otimizador em identificar as informações que damos a ele e como é importante alimentarmos o Oracle com as constraints (primary key, not null, foreign key, checks, etc).

Iniciamos o teste com um tabela com aproximadamente 52 mil registros, nela, haverá duas constraints - uma primary key (o_id) e um check (oname), também haverá um índice normal na coluna (oname). Depois de coletar as estatísticas, farei uma consulta com o predicado UPPER(oname) = 'valor' cujo o uso invalidaria o indice, porém em nosso exemplo, o otimizador sabe que a coluna não permite nulo e só permite valor em UPPER case, então ele faz a dedução e decide usar o índice.

Essa capacidade de entender e decidir usar o índice já está valendo desde a versão 9i. Juntamente com essa demonstração, gostaria de mostrar algumas melhorias que vieram com o 10g. Na demo, há uma query principal que faz a projeção das colunas que estou selecionando e uma subquery, onde busco o predicado UPPER e o max( da primary key ). Agora é possível nomear as subqueries e as queries de uma sentença com o hint /*+ qb_name() */ (Query Block Name) e a partir dessa mesma versão, o dbms_xplan dá mais informações, inclusive dos nomes destas queries.

Preparando o exemplo e coletando estatísticas

ops$marcio:LX10G> create table t (
  2          o_id            number constraint t_pk primary key,
  3          oname           varchar2(30) not null,
  4          padding         varchar2(200),
  5          constraint t_chk_oname check ( oname = upper(oname) )
  6  );

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create index t_i1 on t ( oname );

Index created.

ops$marcio:LX10G>
ops$marcio:LX10G> insert /*+ append */ into t
  2  select object_id,
  3         substr(upper(object_name),1,30),
  4         lpad('x',200,'x')
  5    from dba_objects
  6  /

51925 rows created.

ops$marcio:LX10G>
ops$marcio:LX10G> begin
  2          dbms_stats.gather_table_stats(
  3                  user,
  4                  'T',
  5                  cascade => true
  6          );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Limpar a plan_table

ops$marcio:LX10G>
ops$marcio:LX10G> delete from plan_table;

6 rows deleted.

ops$marcio:LX10G> commit;

Commit complete.

ops$marcio:LX10G>
ops$marcio:LX10G> explain plan for
  2  select /*+ qb_name( principal ) */ *
  3    from t t1
  4   where o_id = ( select /*+ qb_name ( maxid ) */ max(o_id)
  5                    from t t2
  6                   where upper(oname) = 'ALL_OBJECTS' )
  7
  8  /

Explained.

Acima está o exemplo. Note o upper(oname) na linha 6, isso normalmente invalidaria o índice, mas como pode ser visto abaixo, ele usou o índice T_I1, que está relacionado a esta coluna (oname), sem que o índice fosse do tipo FBI (Function Based Index), ou seja, eu não preparei um índice especial para que ele assumisse, não, o índice é um b-tree normal. Note também, os argumentos null, null, 'ALL' que passo para o dbms_xplan.display, isso é novo no 10g e abaixo, repare nas 3 seções que o resultado traz, deixa bem mais fácil a vida da pessoa que está trabalhando com otimização de queries inclusive com os nomes das queries que usei apenas para ilustrar o exemplo.

ops$marcio:LX10G>
ops$marcio:LX10G> select * from table( dbms_xplan.display( null, null, 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2855282288

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |   229 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T    |     1 |   229 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN            | T_PK |     1 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE              |      |     1 |    30 |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T    |     1 |    30 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T_I1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - PRINCIPAL / T1@PRINCIPAL
   2 - PRINCIPAL / T1@PRINCIPAL
   3 - MAXID
   4 - MAXID     / T2@MAXID
   5 - MAXID     / T2@MAXID

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

   2 - access("O_ID"= (SELECT /*+ QB_NAME ("MAXID") */ MAX("O_ID") FROM "T"
              "T2" WHERE "ONAME"='ALL_OBJECTS' AND UPPER("ONAME")='ALL_OBJECTS'))
   5 - access("ONAME"='ALL_OBJECTS')
       filter(UPPER("ONAME")='ALL_OBJECTS')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "O_ID"[NUMBER,22], "T1"."ONAME"[VARCHAR2,30],
       "T1"."PADDING"[VARCHAR2,200]
   2 - "T1".ROWID[ROWID,10], "O_ID"[NUMBER,22]
   3 - (#keys=0) MAX("O_ID")[22]
   4 - "O_ID"[NUMBER,22]
   5 - "T2".ROWID[ROWID,10]

39 rows selected.

Labels: New Feature, Performance