(原发表于 2011-5-11)
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：
ChrisSaxon
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
假设Oracle用来explain plan的表已经定义好，并且我执行下列语句所在的schema能够访问这些表。
我创建了如下的表和数据：
CREATE TABLE plch_employees
(
emp_idINTEGER
, last_name VARCHAR2 (100)
)
/
BEGIN
INSERT INTO plch_employees
VALUES (1, 'Jobs');
INSERT INTO plch_employees
VALUES (2, 'Gates');
INSERT INTO plch_employees
VALUES (3, 'Ellison');
COMMIT;
END;
/
哪些选项会显示下列SQL语句的解释计划：
SELECT *
FROM plch_employees
WHERE emp_id = 1
(A)
EXPLAIN PLAN
FOR
SELECT *
FROM plch_employees
WHERE emp_id = 1
/
SELECT *
FROM TABLE (DBMS_XPLAN.display)
/
(B)
EXPLAIN PLAN
FOR
SELECT *
FROM plch_employees
WHERE emp_id = 1
/
EXPLAIN PLAN
FOR
SELECT * FROM DUAL
/
SELECT *
FROM TABLE (DBMS_XPLAN.display)
/
(C)
EXPLAIN PLAN
SET STATEMENT_ID = 'my_query'
FOR
SELECT *
FROM plch_employees
WHERE emp_id = 1
/
EXPLAIN PLAN
FOR
SELECT * FROM DUAL
/
SELECT *
FROM TABLE (
DBMS_XPLAN.display (
'PLAN_TABLE'
, 'my_query'))
/
(D)
EXPLAIN PLAN
SET STATEMENT_ID = 'employee_query'
FOR
SELECT *
FROM plch_employees
WHERE emp_id = 1
/
SELECT *
FROM TABLE (
DBMS_XPLAN.display (
'PLAN_TABLE'
, 'employee_query'))
/
(E)
EXPLAIN PLAN
SET STATEMENT_ID = 'employee_query'
FOR
SELECT *
FROM plch_employees
WHERE emp_id = 1
/
SELECT *
FROM TABLE (
DBMS_XPLAN.display (
'PLAN_TABLE'
, NULL))
/
(F)
EXPLAIN PLAN
SET STATEMENT_ID = 'find_employee_1'
FOR
SELECT *
FROM plch_employees
WHERE emp_id = 1
/
SELECT *
FROM TABLE (
DBMS_XPLAN.display (
'EXPLAIN_PLAN_TABLE'
, 'find_employee_1'))
/
(G)
EXPLAIN PLAN
FOR
SELECT *
FROM plch_employees
WHERE emp_id = 1
/
BEGIN
DBMS_XPLAN.display ();
END;
/
|
