正文
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出结果
应该多考虑产品一些实现.
他们是耗费了很多经理的产出物
有时候不仅要看网上的东西,还应该多学习产品里面优秀的设计要点.
