为什么临时表和普通表的查询速度相差很多？

Question

为什么临时表和普通表的查询速度相差很多？我在用多表联合查询时，为提高速度新建了一张临时表。具体代码如下：
表card和product是普通表，其中product是基本信息表。在pl/sq下建好临时表。temp.1、用临时表
  insert into temp (card_no,name,price) select card_no,name,price from product where card_no like '%%'and name like '%%' ;
 select a.card_no,b.name,b.price,nvl(sum(decode(rtrim(a.card_kind),'1',sale_num,0)),0) fscs ,......  from sub_salecard a,temp b where a.card_no=b.card_no and a.card_date>='2000-5-31' and a.card_date<='2009-5-31' group by a.card_no,b.name,b.price )
2、用普通表
   create temp as(select card_no,name,price from product where card_no like '%%'and name like '%%' );
  select a.card_no,b.name,b.price,nvl(sum(decode(rtrim(a.card_kind),'1',sale_num,0)),0) scs ,......    
  from sub_salecard a,temp b where a.card_no=b.card_no and a.card_date>='2000-5-31' and  
  a.card_date<='2009-5-31' group by a.card_no,b.name,b.price )
普通表用了10秒
临时表用了163秒。
这是为什么

Answer 1

看看执行计划有什么差别,看执行计划的方法举例如下(以查询SELECT * FROM EMP为例)SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));把查询结果给出来看看差别。

Answer 2

   1 - filter("A"."DATE"<=TO_DATE('2009-05-31 00:00:00', 'yyyy-mm-dd hh（普通表的） 3 - access("A"."DATE">=TO_DATE('1996-05-31 00:00:00', 'yyyy-mm-dd hh24:m（临时表）不知怎么上传图片只能把不同的铁出来

Answer 3

你的时间包括insert的时间吗？ 还是就后面一个查询的时间？

Answer 4

这就是执行计划不一样啊。你的“临时表”和别的表索引建的一样吗？建索引了吗？表分析了吗？你的TEMP表也只是在业务上可称作“临时表”，数据库层面这就是一普通表，不是temporary table。所以查询快慢不存在临时表的问题。

Answer 5

[Quote=引用 2 楼 skytemp 的回复:]
  1 - filter("A"."DATE" <=TO_DATE('2009-05-31 00:00:00', 'yyyy-mm-dd hh（普通表的） 
3 - access("A"."DATE">=TO_DATE('1996-05-31 00:00:00', 'yyyy-mm-dd hh24:m（临时表） 
你看，执行计划反映，主要是主表上的日期查询不同

Answer 6

你贴完整执行计划啊。你这只是过滤条件，看不出sql的完整执行过程的。还有我又看了下，怎么你贴的1和2这两个sql是一样的？
都是：
select a.card_no,b.name,b.price,nvl(sum(decode(rtrim(a.card_kind),'1',sale_num,0)),0) fscs ,......  from sub_salecard a,temp b where a.card_no=b.card_no and a.card_date>='2000-5-31' and a.card_date <='2009-5-31' group by a.card_no,b.name,b.price ) 

Answer 7

恩。开始我理解错了。你sql有条件card_date>='2000-5-31，这里有个隐式转换，但是怎么执行计划的过滤条件有"A"."DATE">=TO_DATE('1996-05-31 00:00:00'LZ你贴完整的执行计划，才能进一步分析。

Answer 8

1
2  ----------------------------------------------------------------------------------
3  | Id  | Operation                   |  Name              | Rows  | Bytes | Cost  |
4  ----------------------------------------------------------------------------------
5  |   0 | SELECT STATEMENT            |                    |       |       |       |
6  |   1 |  TABLE ACCESS BY INDEX ROWID| SUB_CARD       |       |       |    
7  |   2 |   NESTED LOOPS              |                    |       |       |       |
8  |   3 |    TABLE ACCESS FULL        | TEMP               |       |       |       |
9  |*  4 |    INDEX RANGE SCAN         | IDX_SUB_UNI_NO  |       |       |       |
10 ----------------------------------------------------------------------------------
11 
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 1- filter("A"."DATE"<=TO_DATE('2009-05-31 00:00:00', 'yyyy-mm-dd hh
16              24:mi:ss') AND "A"."DATE">=TO_DATE('1996-01-01 00:00:00', 
17              'yyyy-mm-dd hh24:mi:ss'))) 
18 4 - access("A"."UNI_NO"="B"."UNI_NO")
19 Note: rule based optimization这个是普通表
1
2  ----------------------------------------------------------------------------------
3  | Id  | Operation                   |  Name              | Rows  | Bytes | Cost  |
4  ----------------------------------------------------------------------------------
5  |   0 | SELECT STATEMENT             |                    |       |       |       |
6  |   1 |  NESTED LOOPS                |                    |       |       |       |
7  |   2 |   TABLE ACCESS BY INDEX ROWID| SUB_CARD       |       |       |       |
8  |*  3 |    INDEX RANGE SCAN          | IDX_SUB_DATE       |       |       |       |
9  |   4 |   TABLE ACCESS BY INDEX ROWID| TEMPP              |       |       |       |
10 *   5 |    INDEX UNIQUE SCAN         | SYS_C0016924        |       |       |       |
11 -----------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15 
16  3 access("A"."DATE">=TO_DATE('1996-01-01 00:00:00', 'yyyy-mm-dd hh24:m
17              i:ss') AND "A"."DATE"<=TO_DATE('2009-05-31 00:00:00', 'yyyy-m  
18              m-dd hh24:mi:ss'))
19  5 - access("A"."UNI_NO"="B"."UNI_NO")
20 
21  Note: rule based optimization
这个是临时表
语句是select * from sub_card a,temp b where a.uni_no=b.uni_no and a.date>='1996-01-01' and a.date<='2009-05-31'

Answer 9

1
2  ----------------------------------------------------------------------------------
3  | Id  | Operation                   |  Name              | Rows  | Bytes | Cost  |
4  ----------------------------------------------------------------------------------
5  |   0 | SELECT STATEMENT            |                    |       |       |       |
6  |   1 |  TABLE ACCESS BY INDEX ROWID| SUB_CARD       |       |       |    
7  |   2 |   NESTED LOOPS              |                    |       |       |       |
8  |   3 |    TABLE ACCESS FULL        | TEMP               |       |       |       |
9  |*  4 |    INDEX RANGE SCAN         | IDX_SUB_UNI_NO  |       |       |       |
10 ----------------------------------------------------------------------------------
11 
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 1- filter("A"."DATE"<=TO_DATE('2009-05-31 00:00:00', 'yyyy-mm-dd hh
16              24:mi:ss') AND "A"."DATE">=TO_DATE('1996-01-01 00:00:00', 
17              'yyyy-mm-dd hh24:mi:ss'))) 
18 4 - access("A"."UNI_NO"="B"."UNI_NO")
19 Note: rule based optimization这个是普通表
1
2  ----------------------------------------------------------------------------------
3  | Id  | Operation                   |  Name              | Rows  | Bytes | Cost  |
4  ----------------------------------------------------------------------------------
5  |   0 | SELECT STATEMENT             |                    |       |       |       |
6  |   1 |  NESTED LOOPS                |                    |       |       |       |
7  |   2 |   TABLE ACCESS BY INDEX ROWID| SUB_CARD       |       |       |       |
8  |*  3 |    INDEX RANGE SCAN          | IDX_SUB_DATE       |       |       |       |
9  |   4 |   TABLE ACCESS BY INDEX ROWID| TEMPP              |       |       |       |
10 *   5 |    INDEX UNIQUE SCAN         | SYS_C0016924        |       |       |       |
11 -----------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15 
16  3 access("A"."DATE">=TO_DATE('1996-01-01 00:00:00', 'yyyy-mm-dd hh24:m
17              i:ss') AND "A"."DATE"<=TO_DATE('2009-05-31 00:00:00', 'yyyy-m  
18              m-dd hh24:mi:ss'))
19  5 - access("A"."UNI_NO"="B"."UNI_NO")
20 
21  Note: rule based optimization
这个是临时表
语句是select * from sub_card a,temp b where a.uni_no=b.uni_no and a.date>='1996-01-01' and a.date<='2009-05-31'

Answer 10

1
2  ----------------------------------------------------------------------------------
3  | Id  | Operation                   |  Name              | Rows  | Bytes | Cost  |
4  ----------------------------------------------------------------------------------
5  |   0 | SELECT STATEMENT            |                    |       |       |       |
6  |   1 |  TABLE ACCESS BY INDEX ROWID| SUB_CARD       |       |       |    
7  |   2 |   NESTED LOOPS              |                    |       |       |       |
8  |   3 |    TABLE ACCESS FULL        | TEMP               |       |       |       |
9  |*  4 |    INDEX RANGE SCAN         | IDX_SUB_UNI_NO  |       |       |       |
10 ----------------------------------------------------------------------------------
11 
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 1- filter("A"."DATE"<=TO_DATE('2009-05-31 00:00:00', 'yyyy-mm-dd hh
16              24:mi:ss') AND "A"."DATE">=TO_DATE('1996-01-01 00:00:00', 
17              'yyyy-mm-dd hh24:mi:ss'))) 
18 4 - access("A"."UNI_NO"="B"."UNI_NO")
19 Note: rule based optimization这个是普通表
1
2  ----------------------------------------------------------------------------------
3  | Id  | Operation                   |  Name              | Rows  | Bytes | Cost  |
4  ----------------------------------------------------------------------------------
5  |   0 | SELECT STATEMENT             |                    |       |       |       |
6  |   1 |  NESTED LOOPS                |                    |       |       |       |
7  |   2 |   TABLE ACCESS BY INDEX ROWID| SUB_CARD       |       |       |       |
8  |*  3 |    INDEX RANGE SCAN          | IDX_SUB_DATE       |       |       |       |
9  |   4 |   TABLE ACCESS BY INDEX ROWID| TEMPP              |       |       |       |
10 *   5 |    INDEX UNIQUE SCAN         | SYS_C0016924        |       |       |       |
11 -----------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15 
16  3 access("A"."DATE">=TO_DATE('1996-01-01 00:00:00', 'yyyy-mm-dd hh24:m
17              i:ss') AND "A"."DATE"<=TO_DATE('2009-05-31 00:00:00', 'yyyy-m  
18              m-dd hh24:mi:ss'))
19  5 - access("A"."UNI_NO"="B"."UNI_NO")
20 
21  Note: rule based optimization
这个是临时表
语句是select * from sub_card a,temp b where a.uni_no=b.uni_no and a.date>='1996-01-01' and a.date<='2009-05-31'

Answer 11

看你的执行计划可以看出 10 *  5 |    INDEX UNIQUE SCAN        | SYS_C0016924  临时表的      9  |*  4 |    INDEX RANGE SCAN        | IDX_SUB_UNI_NO  |      |      |      | 普通表的,一个用到索引一个没用到.我想你是不是临时表的结构有问题,建议你重建下和普通表一样的结构试试.