How to Use Optimizer Hint in Forms 6

[rapsf]

Good evening,
Someone has already used the "Optimizer Hint" field in Forms 6.
I need to bring the result of an appointment by ordering the field index,
but I do not know how to use the expression Correct to describe the index in Forms.

EX, but I do not know how to write in forms:

Select all

/* +INDEX_DESC (nome_tabela  nome_indice)*/

Thanks.

[dr_gori]

According to the help, simply put the hint, without the / * * /
see:

Consider the Form That Contains a Block Named DeptBlock [/ I] BASED on the Dept Table. IF The End User Enters the 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 A follows:

Select all

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:

Select all

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

/

In your case, it would be enough to put:

Select all

INDEX_DESC (nome_tabela  nome_indice)

: -O

[rapsf]

Oops, thanks for the help, however I did the tests and it seems that the forms is not accepting HINTS.
In case I need to order the result of a consultation by the name, but if I use order by long,
with Hint is very fast via bank, but by the forms is not accepting.

Att.

[dr_gori]

But how do you plan to order a column without an order by?
The only way to ensure that the lines come sorted is using Order BY.

I was in doubt ...

[rapsf]

Thomas,
is that I have a clients here with the "Name (Varchar2 (100))" column "with index, I'm trying to use the Hint Index_asc (name of the name column) Bring the result ordered by names.
by the tests I am doing, the difference in the result regarding Order By is when in the Name field has spaces before the name or some special character, but the result is practically the same with a longer response time than with order by.
With Order by the query takes a lot.

[dr_gori]

I think there's a mess there.

HINT will only cause the index to be used ascending or descending.
Hint does not order data.

Example:
Let's talk about index_desc.
This is useful when you knows that the information you seek is to say "the last" of the index. (Example, you are looking for the latest dates from a table. Then in this table there is a date index. As you know the goal of the program is to get the latest dates, you send the HINT saying to the index to be used from the highest leader.
Another example is when you are looking for a max. What matters are the biggest.

That is, the index will be searched for ahead for a branch, or otherwise using these HINTS. It does not mean it will sort.
to ensure that ordination occur, you need to use order by .

Who knows you send us to us Your query, or a piece of it to understand the problem ...

[dr_gori]

I did some tests here to prove it, and I realized that it actually commands using Hint.
See the example, I created an index in the EMP ENAM field:

Select all

create index emp_ind_ename on emp (ename)

Now, I'll do a Select: (Repair the Ename field)

Select all

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>

In this case, as I did not put Hint it is not forcing the use of the index, made Full, the data came messy.

Now, I will repeat exactly the same query, but with Hint:
(the like is for him to force the use of the index. If I do not use the column, it Fullscan and does not use the index, and the lines come messy).

Select all

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>

Note that now, the lines came to the contrary, as I used index_desc.

I will do the same test with index_asc:

Select all

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> 
 

Now, the execution plan with an Order By, without Hint:

Select all

 
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

Conclusion:
* With Order BY, the cost increased.

* This HINTS system even works in some cases. But I would not use any hypothesis, because if the optimizer changes the plan and decides to use a full table scan automatically, it will no longer use the index and your data will come messy. In all these years, I'vê never seen anyone using this system to order lines.

* I talked to my Mauro Mauro from Bittencourt on this subject because I found it super bizarre all this. He said he has seen this in another company, but it is an old database, which the optimizer was per rule. In this case, the execution plan would never change, so for him, it was not so dangerous to use this method.

I would like to know other people's opinion on this ...