一个Distinct的优化

本文介绍了一个具体的SQL查询优化案例,通过调整SQL语句结构,显著降低了数据库缓冲区获取次数,提高了查询效率。优化前后的执行计划对比显示了性能提升的具体细节。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一个Distinct的优化。[@more@]

一个Distinct的优化。

ISN3000万笔,SCARTON600万笔

SQL

16:07:02 SQL> set autotrace on;

16:07:19 SQL> SELECT COUNT(DISTINCT(SCARTON.SCARNO)) MAX_SCARTON

16:07:59 2 FROM SCARTON,ISN WHERE SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1 AND SCARTON.SCARNO=ISN.SCARNO;

192

Elapsed: 00:00:00.93

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=1 Bytes=34)

1 0 SORT (GROUP BY)

2 1 NESTED LOOPS (Cost=67 Card=6 Bytes=204)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SCARTON' (Cost=7 Car

d=6 Bytes=174)

4 3 INDEX (RANGE SCAN) OF 'SCARTON2' (NON-UNIQUE) (Cost=

3 Card=6)

5 2 PARTITION RANGE (ALL)

6 5 PARTITION HASH (ALL)

7 6 INDEX (RANGE SCAN) OF 'ISN7' (NON-UNIQUE) (Cost=10

Card=1 Bytes=5)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

21593 consistent gets

99 physical reads

0 redo size

522 bytes sent via SQL*Net to client

656 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

优化后的SQL

16:08:02 SQL> SELECT COUNT(SCARTON.SCARNO)

16:09:22 2 FROM SCARTON WHERE exists (select 1 from ISN,SCARTON where SCARTON.SCARNO=ISN.SCARNO and SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1)

16:09:22 3 and SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1;

192

Elapsed: 00:00:00.08

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=12)

1 0 SORT (AGGREGATE)

2 1 FILTER

3 2 INDEX (RANGE SCAN) OF 'SCARTON2' (NON-UNIQUE) (Cost=3

Card=6 Bytes=72)

4 2 NESTED LOOPS (Cost=27469280 Card=5505130 Bytes=1211128

60)

5 4 PARTITION RANGE (ALL)

6 5 PARTITION HASH (ALL)

7 6 INDEX (FULL SCAN) OF 'ISN7' (NON-UNIQUE) (Cost=2

4330 Card=27444950 Bytes=137224750)

8 4 INDEX (UNIQUE SCAN) OF 'SCARTON1' (UNIQUE) (Cost=1 C

ard=1 Bytes=17)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

20 consistent gets

2 physical reads

0 redo size

532 bytes sent via SQL*Net to client

656 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

仅仅这个SQL的改动减少了Production DB20%的总buffer gets.

而且发现CBO在估计partition tablecost颇有失水准

改动通过多次不同值static sql和Bind Variable下的稳定性测试。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10856805/viewspace-1000690/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/10856805/viewspace-1000690/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值