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:
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...