普通物理表、临时表和TABLE(函数)的执行效率对比

16:12:55普通物理表、临时表和TABLE(函数)的执行效率对比

普通物理表、临时表和TABLE的用途各不相同，普通表一般存放需要长期保存的数据，临时表存放某个事务或会话过程中的临时数据，array是oracle中的数组，也可以用于存放临时数据，或在OODB中使用。但是，优化器在做查询计划时，尤其在CBO中，没临时表和TABLE的统计数据，因此总会得出一些混乱的查询计划，往往需要我们用hint去调整。并且，由于实现机制各不相同，他们的查询效率也不一样。下面通过一个简单的测试来对比他们之间的查询效率，以便于在可选的情况下选择最优的实现方式：

创建相关对象：

创建物理表：
CREATE TABLE tmp_obj
(
     OWNER      VARCHAR2(30),
     TABLE_NAME VARCHAR2(20)
);

给物理表插入测试数据
BEGIN
  FOR i IN 1..90000 LOOP
    INSERT INTO tmp_obj VALUES(i, i);
  END LOOP;
  COMMIT;
END;

创建临时表：
CREATE GLOBAL TEMPORARY TABLE TMP_OBJ
(
  OWNER      VARCHAR2(30),
  TABLE_NAME VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

创建table类型：
CREATE OR REPLACE TYPE TY_OBJ
IS OBJECT (
   OWNER               VARCHAR2(30),
   TABLE_NAME          VARCHAR2(20)
)

CREATE OR REPLACE TYPE TY_OBJ_LST IS TABLE OF TY_OBJ

创建一张记录统计数据的表：
CREATE TABLE stat_tables (tid number, idate date, tmptime number,funtime number, phytime number);
CREATE SEQUENCE stat_id_seq START WITH 1;

创建一个函数来测试他们的查询效率：

CREATE OR REPLACE PROCEDURE P_TESTTABLE IS
  v_objtab TY_OBJ_LST;
  v_bdate TIMESTAMP;
  v_edate TIMESTAMP;
  v_number NUMBER;
  v_tmptime NUMBER;
  v_funtime NUMBER;
  v_phytime NUMBER;
BEGIN
  v_objtab := TY_OBJ_LST();
  DELETE FROM tmp_obj;
 
  FOR i IN 1..90000 LOOP
    INSERT INTO tmp_obj VALUES(i, i);
    v_objtab.EXTEND;
    v_objtab(i) := ty_obj(to_char(i), to_char(i));
  END LOOP;
 
  FOR i IN 1..100 LOOP
    dbms_output.put_line('');
    dbms_output.put_line('select from temp table ...');
    v_bdate := current_timestamp();
--    dbms_output.put_line(v_bdate);
    SELECT COUNT(*) INTO v_number FROM tmp_obj;
--    dbms_output.put_line(v_number || ' rows selected!');
    v_edate := current_timestamp();
--    dbms_output.put_line(v_edate);
    v_tmptime :=     extract( day from (v_edate-v_bdate) )*24*60*60+
                     extract( hour from (v_edate-v_bdate) )*60*60+
                     extract( minute from (v_edate-v_bdate) )*60+
                     extract( second from (v_edate-v_bdate));
    dbms_output.put_line('consumed: '||to_char(v_tmptime)||' seconds');
   
    dbms_output.put_line('');
    dbms_output.put_line('select from function table ...');
    v_bdate := current_timestamp();
--    dbms_output.put_line(v_bdate);
    SELECT COUNT(*) INTO v_number FROM TABLE(CAST(v_objtab AS TY_OBJ_LST));
--    dbms_output.put_line(v_number || ' rows selected!');
    v_edate := current_timestamp();
--    dbms_output.put_line(v_edate);
    v_funtime :=     extract( day from (v_edate-v_bdate) )*24*60*60+
                     extract( hour from (v_edate-v_bdate) )*60*60+
                     extract( minute from (v_edate-v_bdate) )*60+
                     extract( second from (v_edate-v_bdate));
    dbms_output.put_line('consumed: '||to_char(v_funtime)||' seconds');
 
    dbms_output.put_line('');
    dbms_output.put_line('select from physical table ...');
    v_bdate := current_timestamp();
--    dbms_output.put_line(v_bdate);
    SELECT COUNT(*) INTO v_number FROM phy_obj;
--    dbms_output.put_line(v_number || ' rows selected!');
    v_edate := current_timestamp();
--    dbms_output.put_line(v_edate);
    v_phytime :=     extract( day from (v_edate-v_bdate) )*24*60*60+
                     extract( hour from (v_edate-v_bdate) )*60*60+
                     extract( minute from (v_edate-v_bdate) )*60+
                     extract( second from (v_edate-v_bdate));
    dbms_output.put_line('consumed: '||to_char(v_phytime)||' seconds');
   
    INSERT INTO stat_tables (tid , idate , tmptime , funtime , phytime )
                     VALUES (stat_id_seq.nextval, SYSDATE, v_tmptime, v_funtime, v_phytime);
   
  END LOOP;
  COMMIT;
END P_TESTTABLE;

执行函数，得出他们的查询时间的统计数据：

SET SERVEROUTPUT ON SIZE 50000

EXEC P_TESTTABLE;