【SQL开源代码栏目提醒】:网学会员SQL开源代码为您提供26.1.2.sql参考,解决您在26.1.2.sql学习中工作中的难题,参考学习。
--提示
explain plan for
select /*+ all_rows */ employee_id,last_name,salary,job_id
from employees
where employee_id=7566;
explain plan for
select /*+ first_rows(10) */ employee_id,last_name,salary,job_id
from employees
where employee_id=7566;
select * from table(dbms_xplan.display());
SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;
explain plan for
select /*+cluster(emp) */ empno,ename,salary
from emp
where salary>1500;
select * from table(dbms_xplan.display());
explain plan for
select /*+ hash(test111) */ variety,id,name
from test111
where id>10;
explain plan for
SELECT /*+ INDEX (employees emp_department_ix)*/
employee_id, department_id
FROM employees
WHERE department_id > 50;
explain plan for
SELECT /*+ NO_INDEX(employees EMP_EMP_ID_PK,IDX_EMP_DEPT_EMPID) */ employee_id
FROM employees
WHERE employee_id > 100;
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ INDEX_ASC(employees EMP_EMP_ID_PK) */ employee_id
FROM employees
WHERE employee_id > 100;
explain plan for
SELECT /*+ INDEX_COMBINE(e idx_bitmap_employees_deptno) */ employee_id,first_name,department_id
FROM employees_test
WHERE department_id=10;
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ INDEX_JOIN(e idx_employees_test_job_id,idx_employees_test_manager_id) */
job_id,manager_id
FROM employees_test e
WHERE job_id >='ST_CLERK'
and manager_id>119;
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ INDEX_FFS(e idx_first_last_name) */ first_name,last_name
FROM employees_test e;
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ NO_INDEX_FFS(e idx_first_last_name) */ first_name,last_name
FROM employees_test e;
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ INDEX_SS(e idx_first_last_name) */
employee_id,first_name,last_name
FROM employees_test e
WHERE last_name='King';
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ NO_INDEX_SS(e idx_first_last_name) */
employee_id,first_name,last_name
FROM employees_test e
WHERE last_name='King';
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
FROM (SELECT *
FROM employees e) v
WHERE v.last_name = 'Smith';
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ USE_CONCAT */
job_id,manager_id
FROM employees_test e
WHERE job_id >='ST_CLERK'
and manager_id>119;
select * from table(dbms_xplan.display());
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'SERIAL'));
explain plan for
select /*+ all_rows index(CUSTOMERS_PK) */ *
from customers
where customer_id<130
select * from table(dbms_xplan.display());
explain plan for
SELECT /*+ qb_name(qb) full(@qb e2) */
e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
( SELECT *
FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary = ( SELECT max(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id )
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
explain plan for
select /* qb_name(qb) NO_UNNEST(@qb e2) */
*
from v;