wordpress移动端页面/优化营商环境发言材料
(原发表于2010-12-1)
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：Steven Feuerstein
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我的模式中有一个表，里面有1,000,000行数据，定义如下：
CREATE TABLE plch_parts
(
partnum INTEGER
, partname VARCHAR2 (10)
)
/
ALTER TABLE plch_parts ADD CONSTRAINT
pky_plch_parts PRIMARY KEY ( partnum )
USING INDEX
/
CREATE UNIQUE INDEX un_plch_parts_name
ON plch_parts (partname)
/
我们有个批处理任务，它重复地从这个表中读取数据，使用的方法有好几种，包括读取所有数据行，以及通过partnum寻找partname，或者通过partname寻找partnum。它执行很慢，我决定创建一个包，利用包级别的变量(在包级别，而非包中的过程或者函数所声明的变量)的会话持久性来优化读取的性能。包头的定义如下：
CREATE OR REPLACE PACKAGE plch_parts_lookup
IS
FUNCTION onerow (partnum_in IN plch_parts.partnum%TYPE)
RETURN plch_parts%ROWTYPE;
FUNCTION onerow (
partname_in IN plch_parts.partname%TYPE)
RETURN plch_parts%ROWTYPE;
END plch_parts_lookup;
/
哪些选项提供的包体实现了onerow函数，使得两个函数的执行时间：
(a)比在plch_parts表上重复执行SQL查询更快，并且
(b)不管表中数据有多少，两个函数每次执行时间都差不多相同?
假设：
(a)这个代码执行时的会话可以访问无限量的内存;
(b)传递给这些函数的参数在当前的plch_parts表中都存在;
(c)表中的partnum都是不超过5,000,000的正整数
-----------
(A)
CREATE OR REPLACE PACKAGE BODY plch_parts_lookup
IS
TYPE plch_parts_aat IS TABLE OF plch_parts%ROWTYPE
INDEX BY PLS_INTEGER;
pky_plch_parts_aa plch_parts_aat;
TYPE un_plch_parts_name_aat IS TABLE OF plch_parts.partnum%TYPE
INDEX BY plch_parts.partname%TYPE;
un_plch_parts_name_aa un_plch_parts_name_aat;
PROCEDURE load_arrays
IS
BEGIN
FOR rec IN (SELECT * FROM plch_parts)
LOOP
pky_plch_parts_aa (rec.partnum) := rec;
un_plch_parts_name_aa (rec.partname) := rec.partnum;
END LOOP;
END load_arrays;
FUNCTION onerow (partnum_in IN plch_parts.partnum%TYPE)
RETURN plch_parts%ROWTYPE
IS
BEGIN
RETURN pky_plch_parts_aa (partnum_in);
END;
FUNCTION onerow (
partname_in IN plch_parts.partname%TYPE)
RETURN plch_parts%ROWTYPE
IS
BEGIN
RETURN pky_plch_parts_aa (un_plch_parts_name_aa (partname_in));
END;
BEGIN
load_arrays;
END plch_parts_lookup;
(B)
CREATE OR REPLACE PACKAGE BODY plch_parts_lookup
IS
TYPE plch_parts_aat IS TABLE OF plch_parts%ROWTYPE
INDEX BY PLS_INTEGER;
pky_plch_parts_aa plch_parts_aat;
TYPE un_plch_parts_name_aat IS TABLE OF plch_parts%ROWTYPE
INDEX BY plch_parts.partname%TYPE;
un_plch_parts_name_aa un_plch_parts_name_aat;
PROCEDURE load_arrays
IS
BEGIN
FOR rec IN (SELECT * FROM plch_parts)
LOOP
pky_plch_parts_aa (rec.partnum) := rec;
un_plch_parts_name_aa (rec.partname) := rec;
END LOOP;
END load_arrays;
FUNCTION onerow (partnum_in IN plch_parts.partnum%TYPE)
RETURN plch_parts%ROWTYPE
IS
BEGIN
RETURN pky_plch_parts_aa (partnum_in);
END;
FUNCTION onerow (
partname_in IN plch_parts.partname%TYPE)
RETURN plch_parts%ROWTYPE
IS
BEGIN
RETURN un_plch_parts_name_aa (partname_in);
END;
BEGIN
load_arrays;
END plch_parts_lookup;
(C)
CREATE OR REPLACE PACKAGE BODY plch_parts_lookup
IS
TYPE plch_parts_aat IS TABLE OF plch_parts%ROWTYPE
INDEX BY PLS_INTEGER;
pky_plch_parts_aa plch_parts_aat;
TYPE un_plch_parts_name_idx IS INDEX ON pky_plch_parts_aa (plch_parts.partname);
PROCEDURE load_arrays
IS
BEGIN
FOR rec IN (SELECT * FROM plch_parts)
LOOP
pky_plch_parts_aa (rec.partnum) := rec;
END LOOP;
END load_arrays;
FUNCTION onerow (partnum_in IN plch_parts.partnum%TYPE)
RETURN plch_parts%ROWTYPE
IS
BEGIN
RETURN pky_plch_parts_aa (partnum_in);
END;
FUNCTION onerow (
partname_in IN plch_parts.partname%TYPE)
RETURN plch_parts%ROWTYPE
IS
BEGIN
RETURN pky_plch_parts_aa (un_plch_parts_name_idx (partname_in));
END;
BEGIN
load_arrays;
END plch_parts_lookup;
(D)
CREATE OR REPLACE PACKAGE BODY plch_parts_lookup
IS
TYPE plch_parts_aat IS TABLE OF plch_parts%ROWTYPE
INDEX BY PLS_INTEGER;
pky_plch_parts_aa plch_parts_aat;
PROCEDURE load_array
IS
BEGIN
FOR rec IN (SELECT * FROM plch_parts)
LOOP
pky_plch_parts_aa (rec.partnum) := rec;
END LOOP;
END load_array;
FUNCTION onerow (partnum_in IN plch_parts.partnum%TYPE)
RETURN plch_parts%ROWTYPE
IS
BEGIN
RETURN pky_plch_parts_aa (partnum_in);
END;
FUNCTION onerow (partname_in IN plch_parts.partname%TYPE)
RETURN plch_parts%ROWTYPE
IS
l_index PLS_INTEGER := pky_plch_parts_aa.FIRST;
BEGIN
LOOP
EXIT WHEN l_index IS NULL
OR partname_in = pky_plch_parts_aa (l_index).partname;
l_index := pky_plch_parts_aa.NEXT (l_index);
END LOOP;
IF l_index IS NOT NULL
THEN
RETURN pky_plch_parts_aa (l_index);
ELSE
RETURN NULL;
END IF;
END;
BEGIN
load_array;
END plch_parts_lookup;