Como usar o Optimizer Hint no forms 6

[rapsf]

Boa noite,
Alguém já usou o campo "Optimizer Hint" no forms 6.
Preciso trazer o resultado de uma consulta ordenando pelo índice do campo,
Porem não sei usar a expressão correta para descrever o índice no forms.

Ex, porém não sei como escrever no forms:

Selecionar tudo

/* +INDEX_DESC (nome_tabela  nome_indice)*/

Obrigado.

[dr_gori]

Conforme o HELP, basta colocar o HINT mesmo, sem o /* */
Veja:

Consider a form that contains a block named DeptBlock based on the DEPT table. If the end user enters a criteria of " > 25 " for the DEPTNO column and executes the query, the default SELECT statement that Oracle Forms generates to query the appropriate rows from the database is as follows:

Selecionar tudo

SELECT DEPTNO,DNAME,LOC,ROWID FROM DEPT WHERE (DEPTNO > 25) 

The designer can use SET_BLOCK_PROPERTY to set the Optimizer Hint property to request that the Oracle Server attempt to optimize the SQL statement for best response time:

Selecionar tudo

Set_Block_Property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS'); 
SELECT /*+ FIRST_ROWS */ DEPTNO,DNAME,LOC,ROWID FROM DEPT WHERE (DEPTNO > 25)

No seu caso, bastaria colocar:

Selecionar tudo

INDEX_DESC (nome_tabela  nome_indice)

[rapsf]

Opa, obrigado pela ajuda, porém fiz os testes e parece que o Forms não está aceitando hints.
No caso preciso ordenar o resultado de uma consulta pelo nome, mas se uso order by demora muito,
com o hint é muito rápido via banco, mas pelo forms não esta aceitando.

att.

[dr_gori]

Mas como você pretende ordenar uma coluna sem um ORDER BY ?
A única forma de garantir que as linhas venham ordenadas é usando ORDER BY.

Fiquei em dúvida...

[rapsf]

Thomas,
É que tenho aqui uma tabela CLIENTES com a coluna "NOME(varchar2(100))" com indice, estou tentando usar o hint INDEX_ASC (indice da coluna nome) para me trazer o resultado ordenado por nomes.
Pelos testes que estou realizando, a diferença no resultado com relação ao order by está quando no campo nome possui espaços antes do nome ou algum caracter especial, mas o resultado é praticamente o mesmo com um tempo de resposta maior do que com order by.
Com o order by a consulta demora muito.

[dr_gori]

Acho que está havendo uma confusão aí.

O HINT apenas vai fazer com que o índice seja usado de forma ascendente ou descendente.
O HINT não ordena dados.

Exemplo:
Vamos falar do INDEX_DESC.
Isso é útil quando você sabe que a informação que você busca é digamos "a última" do índice. (exemplo, você está buscando as últimas datas de uma tabela. Aí nesta tabela existe um índice de data. Como você SABE que o objetivo do programa é buscar as últimas datas, você manda o HINT dizendo pro índice ser usado do maior pro menor.
Outro exemplo é quando você ta buscando um MAX. O que interessa são os maiores.

Ou seja, o índice vai ser buscado de frente pra tráz, ou o contrário usando esses hints. Não quer dizer que vai ordenar.
Pra garantir que a ordenação ocorra, você precisa usar ORDER BY.

Quem sabe você manda pra gente sua query, ou um pedaço dela pra gente entender o problema...

[dr_gori]

Fiz uns testes aqui pra comprovar isso, e percebi que realmente ele ORDENA usando o HINT.
Veja o exemplo, criei um índice no campo ENAME da EMP:

Selecionar tudo

create index emp_ind_ename on emp (ename)

Agora, vou fazer um select: (repare o campo ENAME)

Selecionar tudo

SQL> SELECT * FROM EMP WHERE ename like '%';

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81             5000                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81             2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81             2450                    10
      7566 JONES      MANAGER         7839 02-APR-81             2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87             3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81             3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80              800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81             1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81             1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81             1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81             1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87             1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81              950                    30
      7934 MILLER     CLERK           7782 23-JAN-82             1300                    10

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=14 Bytes=1218)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=5 Card=14 Bytes =1218)

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
       1201  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

Neste caso, como eu não coloquei o HINT ele não está forçando o uso do índice, fez FULL, os dados vieram bagunçados.

Agora, eu vou repetir exatamente a MESMA query, mas com o HINT:
(O LIKE é pra ele forçar o uso do índice. Se eu não utilizo a coluna, ele faz FULLSCAN e não usa o índice, e as linhas vem bagunçadas).

Selecionar tudo

SQL> SELECT /*+ INDEX_DESC( EMP emp_ind_ename)*/ * FROM EMP WHERE ename like '%';

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 22-FEB-81             1250        500         30
      7844 TURNER     SALESMAN        7698 08-SEP-81             1500          0         30
      7369 SMITH      CLERK           7902 17-DEC-80              800                    20
      7788 SCOTT      ANALYST         7566 19-APR-87             3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82             1300                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81             1250       1400         30
      7839 KING       PRESIDENT            17-NOV-81             5000                    10
      7566 JONES      MANAGER         7839 02-APR-81             2975                    20
      7900 JAMES      CLERK           7698 03-DEC-81              950                    30
      7902 FORD       ANALYST         7566 03-DEC-81             3000                    20
      7782 CLARK      MANAGER         7839 09-JUN-81             2450                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81             2850                    30
      7499 ALLEN      SALESMAN        7698 20-FEB-81             1600        300         30
      7876 ADAMS      CLERK           7788 23-MAY-87             1100                    20

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=1218)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=14 Bytes=1218)

   2    1     INDEX (FULL SCAN DESCENDING) OF 'EMP_IND_ENAME' (INDEX) (Cost=1 Card=1)

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
       1275  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

Repare que agora, as linhas vieram ordenadas ao contrário, pois eu usei INDEX_DESC.

Vou fazer o mesmo teste com INDEX_ASC:

Selecionar tudo

SQL> SELECT /*+ INDEX_ASC( EMP emp_ind_ename)*/ * FROM EMP WHERE ENAME LIKE '%';

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87             1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81             1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81             2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81             2450                    10
      7902 FORD       ANALYST         7566 03-DEC-81             3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81              950                    30
      7566 JONES      MANAGER         7839 02-APR-81             2975                    20
      7839 KING       PRESIDENT            17-NOV-81             5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81             1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82             1300                    10
      7788 SCOTT      ANALYST         7566 19-APR-87             3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80              800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81             1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-81             1250        500         30

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=1218)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=14 Bytes=1218)

   2    1     INDEX (FULL SCAN) OF 'EMP_IND_ENAME' (INDEX) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
       1262  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

Agora, o plano de execução com um ORDER BY, sem HINT:

Selecionar tudo

SELECT * FROM EMP ORDER BY ENAME;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=14 Bytes=1218)

   1    0   SORT (ORDER BY) (Cost=6 Card=14 Bytes=1218)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=5 Card=14 Bytes=1218)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1262  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

Conclusão:
* Com o ORDER BY, realmente o custo aumentou.

* Este sistema de Hints até funciona em alguns casos. Mas eu não usaria em hipótese alguma, pois caso o otimizador mude o plano e decida usar um FULL TABLE SCAN, automaticamente, ele não vai mais usar o índice e seus dados virão bagunçados. Em todos esses anos, eu nunca vi alguém usar esse sistema pra ordenar linhas.

* Conversei com meu colega Mauro de Bittencourt sobre esse assunto, porque eu achei super bizarro isso tudo. Ele disse que já viu isso numa outra empresa, mas é num banco de dados antigo, que o otimizador era por REGRA. Neste caso, o plano de execução nunca ia mudar, então pra ele, não era tão perigoso utilizar esse método.

Gostaria de saber a opinião de outras pessoas sobre isso...