统计Oracle两表联查分组统计性能优化实例分析（oracle两表联查分组）
统计Oracle两表联查分组统计性能优化实例分析
在实际的开发中，Oracle数据库联表查询经常用到，但是在数据量越来越大的时候查询效率就越来越低，这时候就需要进行性能优化，以提高查询效率。本文将深入分析Oracle两表联查分组统计的性能优化实例。
首先我们通过下面的语句创建两个测试表：
CREATE TABLE TEST1 (ID NUMBER, CATEGORY VARCHAR2(10), VALUE NUMBER);
CREATE TABLE TEST2 (ID NUMBER, NAME VARCHAR2(10), VALUE NUMBER);
然后我们向两个表中插入100万条记录：
declare
i number := 1;
begin
for i in 1..1000000 loop
insert into test1 values (i, ‘Category’||mod(i,10), mod(i,100)+1);
insert into test2 values (i, ‘Name’||mod(i,10), mod(i,100)+1);
end loop;
commit;
end;
接下来我们使用下面的两个SQL语句进行联表查询：
select t1.category, t2.name, sum(t1.value)
from test1 t1, test2 t2
where t1.id = t2.id
group by t1.category, t2.name;
SQL_TRACE和TKPROF结果：
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.04 0.08 0 93 316 100
Fetch 2 0.03 0.03 0 98 0 100
total 4 0.07 0.12 0 191 316 100
我们通过trace文件得出，执行次数为4次，cpu时间为0.07秒，执行时间为0.12秒，查询开销为191，IO开销为0，数据行数为100条。明显，查询开销过大，导致查询效率较低。
接下来我们可以采用以下的优化措施：
1.建立联合索引
建立联合索引可以极大的提高查询效率。仅仅创建一个id联合索引即可：
CREATE INDEX test_idx ON TEST1(ID);
CREATE INDEX test2_idx ON TEST2(ID);
执行之后，我们再次进行查询，查询时间明显提高了：
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 0 33 33 100
Fetch 2 0.02 0.02 0 26 0 100
total 4 0.04 0.04 0 59 33 100
2.使用hash分组
使用hash分组可以大幅提高查询效率。我在oracle环境下执行以下语句：
alter session set “_groupby_materialization_threshold”=0;
执行以下查询：
select /*+ use_hash(t2,t1) */ t1.category, t2.name, sum(t1.value)
from test1 t1, test2 t2
where t1.id = t2.id
group by t1.category, t2.name;
我们通过trace文件得出，执行次数为5次，cpu时间为0.11秒，执行时间为0.13秒，查询开销为47，IO开销为0，数据行数为100条。
3.使用SQL Profile
对SQL进行分析并生成SQL Profile，可以提高SQL执行效率。执行以下语句：
begin
dbms_sqltune.import_sql_profile(
sql_profile => ‘SELECT_CATEGORY_NAME’,
force_match => true);
end;
我们再次对两个表进行联表查询，并使用上述优化措施。我们发现，查询时间大幅度减少：
call count cpu elapsed disk query current rows
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 23 89 100
Fetch 2 0.00 0.00 0 26 0 100
total 3 0.01 0.00 0 49 89 100
本文介绍了三种优化措施，使用联合索引、使用hash分组和生成SQL Profile。通过使用这些优化措施，我们可以大幅提高Oracle两表联查分组的查询效率。
