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

日期： 2006-05-07 14:50:13

分享到

普通物理表、临时表和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;
然后利用PLSQL developer 7.0的直方图向导从统计表stat_tables中来生成数据对比的直方图，做一个直观的对比：

从图上可以看出，平均效率最差的是TABLE，它与其他两者对比效率相差几个数量级，这个数据库中使一个不可忽视的效率差异。其次为临时表，而普通物理表的效率最优。而第一次访问效率则是TABLE最好。

我们来简单了解一下他们各自实现机制，以便于理解为什么他们之间会存在这个效率差异。

对于物理表，它的数据是存储在物理磁盘上的。但数据第一次read时，会被load到db cache中去。根据LRU算法来决定这些数据是否会被置换出内存（如果建表时使用了cache，则会一直存储在内存中）。我们这主要是考虑这些数据在内存中被扫描。由于它的数据是会被所有会话共享的，所以就会存在锁和脏数据的问题。而由于它的数据是结构化的，所以在内存中进行数据扫描时，效率是最高的。

对于临时表，它及它的索引都是创建在临时表空间上的。当在一个会话中第一次插入数据时，才开始在用户的默认临时表空间给他分配临时段，不同的会话会分配不同的临时段。这就决定的了各个会话的同一个临时表的数据不会相互影响。临时表的数据也是结构化的。第一次读取数据后，数据也被cache到db cache中。

此外，在建表时可以通过参数指定它是事物级的还是会话级的，但是对查询效率来说是没什么影响的。还有一点，临时表的数据变化是不会产生redo log的（但会产生undo log），当然，这对查询效率也没有影响，致使顺带提一下。

对于TABLE函数（它是9i中的新特性），实际上是将一个存储在内存中的对象（以流的方式存储的）结构化以后，使这个对象能以表的方式查询。他对流对象的结构化转换就决定的它的效率大大逊于对普通表和临时表的查询效率。

以上的分析和结论，对于应用设计能起到相当的帮助。

（PLSQL developer 7.0的这个特性真不错呀^_^）
 

上一篇：各种语句的不同写法

下一篇：Oracle中的Hash Join祥解

本类中的所有文章