Oracle使用临时表与直接关联的性能比较

摘要

自己的数据库水平还是太low了. 
之前有很多店理解过. 但是一直理解的不深入.
比如我们这边有很多使用临时表存储中间结果数据
然后对结果数据进行关联查询的

直接关联查询

SELECT
	col.TABLE_NAME,
	col.column_name 
FROM
	user_constraints con,
	user_cons_columns col 
WHERE
	con.constraint_name = col.constraint_name 
	AND con.constraint_type = 'P' 
	AND col.Table_name NOT IN (
	SELECT
		table_name 
	FROM
		(
		SELECT
			count( col.TABLE_NAME ),
			col.TABLE_NAME 
		FROM
			user_constraints con,
			user_cons_columns col 
		WHERE
			con.constraint_name = col.constraint_name 
			AND con.constraint_type = 'P' 
			AND col.TABLE_NAME IN ( SELECT TABLE_NAME FROM user_tables WHERE num_rows > 1 ) 
		GROUP BY
			col.TABLE_NAME 
		HAVING
			(
			count( col.TABLE_NAME )) > 1 
		)) 
	AND col.TABLE_NAME IN ( SELECT TABLE_NAME FROM user_tables WHERE num_rows > 1 )

SQL说明

从 user_constraints 取出来唯一主键的名称
从 user_cons_columns 取出来唯一主键对应的表名和列名
从 user_tables 取出来存在数据的表信息.
然后排除掉有符合主键的情况. 

得到单一主键的表名和列名信息. 

耗时和执行计划分析

使用navicat 执行此段SQL
我这边大概 200G的数据库 1万多个表的情况下
耗时6550 秒 一小时五十分钟. 

使用sqlplus 展示执行计划

set autotrace on 
set autotrace traceonly 
set autotrace traceonly explain
set autotrace traceonly statistics
set pagesize 2000
set linesize 2000

explain plan for somesql
查看具体执行计划
select * from table(dbms_xplan.display());

简历两个中间表进行查询的结果

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE zhaobsh001';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/
create table zhaobsh001 as select col.TABLE_NAME,col.column_name from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.TABLE_NAME in (select TABLE_NAME from user_tables where num_rows > 1 );
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE zhaobsh002';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/
create table zhaobsh002 as select table_name  from (select count(col.TABLE_NAME),col.TABLE_NAME from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.TABLE_NAME in (select TABLE_NAME from user_tables where num_rows > 1 ) group by col.TABLE_NAME  having(count(col.TABLE_NAME)) > 1 ) ;
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE zhaobsh003';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/
create table zhaobsh003  as select  table_name,column_name from zhaobsh001 where  TABLE_NAME not in (select table_name from zhaobsh002) ;

select 'select count(upper('||column_name||')) as  '|| table_name ||' , upper('||column_name||') from '||table_name||' group by upper('||column_name||') having count(upper('||column_name||')) >1 ; ' from zhaobsh003

子表查询的情况

删除表耗时:0.02秒左右
创建三个表的分别耗时:
zhaobsh001: 2.954秒
zhaobsh002: 3.163秒
zhaobsh003: 0.018秒

查询SQL集合耗时:0.13秒
累计耗时: 8秒钟左右
性能提升非常明显. (从6000多秒到不到10秒钟)

感想

不要总是想一句SQL出结果
应该多考虑产品一些实现.
他们是耗费了很多经理的产出物
有时候不仅要看网上的东西,还应该多学习产品里面优秀的设计要点.