Oracle Hints Tutorial for improving performance
APPEND
PARALLEL
JOIN
INDEX
NO_INDEX
SELECT /*+ FIRST_ROWS(10) */ * FROM emp WHERE deptno = 10;

SELECT /*+ ALL_ROWS */ * FROM emp WHERE deptno = 10;

SELECT /*+ NO_INDEX(emp emp_dept_idx) */ * FROM emp, dept WHERE emp.deptno = dept.deptno;

SELECT /*+ INDEX(e,emp_dept_idx) */ * FROM emp e WHERE e.deptno = 10;

-- SELECT /*+ INDEX(scott.emp,emp_dept_idx) */ * FROM scott.emp;

SELECT /*+ AND_EQUAL(e,emp_dept_idx) */ * FROM emp e;

SELECT /*+ INDEX_JOIN(e,emp_dept_idx) */ * FROM emp e;

SELECT /*+ PARALLEL_INDEX(e,emp_dept_idx , 8) */ * FROM emp e;

SELECT /*+ LEADING (dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno;

SELECT /*+ PARALLEL(8) CACHE (e) FULL (e) */ * FROM emp e ;

SELECT /*+ PARALLEL FULL (e) */ * FROM emp e ;

SELECT /*+ PARALLEL USE_MERGE (emp dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno;
-- SORT Merge Join

SELECT /*+ PARALLEL USE_HASH (emp dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno;
-- Hash Join

SELECT /*+ PARALLEL */ * FROM emp e ;

INSERT /*+ APPEND */ INTO mytmp select /*+ CACHE (e) */ *from emp e;
commit;



Oracle Hints Tutorial for improving performance, SQL Tutorial for beginners, PLSQL Tutorial for beginners, SQL Performance tuning, PLSQL Performance tuning, sql hits, oracle hints, oracle hints for performance tuning, oracle hints tuning, how to use oracle hints

свернуть