来源：蜘蛛抓取(WebSpider)
时间：2017-10-20 11:16
标签：
见父母的次数
填空题_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
阅读已结束，下载文档到电脑
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，方便使用
还剩2页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢博客访问： 43173
博文数量： 13
注册时间：
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
欢迎大家加入ORACLE超级群：
免费解决各种ORACLE问题
本次测试来源于QQ群里聊天记录：关于sql走filter过滤的时候，子查询执行的次数。因为我们知道子查询执行的次数，决定了我们SQL的性能
1，数据库的版本
SQL> select * from v$
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release
10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
10.2.0.4.0&&&&& Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
2，创建测试表与索引
SQL> create table scott.htz1 as select * from
Table created.
SQL> create table scott.htz2 as select * from
Table created.
SQL> create table scott.htz3 as select * from
Table created.
SQL> create index scott.ind_htz3_object_owner on
scott.htz3(object_id,owner);
Index created.
3，驱动表无重复值
SQL> select *
& 2&&&& from scott.htz1 a, scott.htz2 b
& 3&&& where a.object_id = b.object_id
& 4&&&&& and a.object_id in (select object_id
& 5&&&&&&&&&&&&&&&&&&&&&&&&&&& from scott.htz3 c
& 6&&&&&&&&&&&&&&&&&&&&&&&&&& where c.owner =
& 7& &&&&&&&&&&&&&&&&&&&&&&&&&&&and a.owner =
no rows selected
SQL> @plan_by_last.sql
SQL> set echo off
Enter value for sqlid:
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('&sqlid',null),null,'ALLSTATS LAST'))
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('',null),null,'ALLSTATS
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
select *&&&
from scott.htz1 a, scott.htz2 b&&
where a.object_id = b.object_id&&&&
and a.object_id in (select object_id
&&&&&&&&&&&&&&&&&&&&&& from scott.htz3 c&&&&&&&&&&&&&&&&&&&&&&&&& where c.owner =
'SCOTT'&&&&&&&&&&&&&&&&&&&&&&&&&&& and
a.owner = 'SYS')
Plan hash value:
----------------------------------------------------------------------------------------------------------------------------------
Operation&&&&&&&&&& | Name&&&&&&&&&&&&&&&&& | Starts | E-Rows | A-Rows
|&& A-Time&& | Buffers |& OMem |&
1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|*& 1 |& FILTER&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |&&&&& 1 |&&&&&&& |&&&&&
0 |00:00:00.96 |&& 47310 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 2 |&& HASH JOIN&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |&&&&& 1 |&
38536 |& 5:00.60
|&&& 1386 |& 7548K|&
2031K| 8473K (0)|
|&& 3 |&&& TABLE ACCESS FULL| HTZ1&&&&&&&&&&&&&&&&& |&&&&& 1 |&
38535 |& 5:00.05
|&&&& 693 |&&&&&& |&&&&&&
|&&&&&&&&& |
|&& 4 |&&& TABLE ACCESS FULL| HTZ2&&&&&&&&&&&&&&&&& |&&&&& 1 |&
50965 |& 5:00.10 |&&&& 693 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 5 |&& FILTER&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |& 50068 |&&&&&&& |&&&&& 0 |00:00:00.23 |&& 45924 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 6 |&&& INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER
|& 22962 |&&&&&
1 |&&&&& 0 |00:00:00.10 |&& 45924 |&&&&&& |&&&&&&
|&&&&&&&&& |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
&& 1 - filter(
IS NOT NULL)
access("A"."OBJECT_ID"="B"."OBJECT_ID")
filter(:B1='SYS')
access("OBJECT_ID"=:B1 AND "C"."OWNER"='SCOTT')
&& - dynamic
sampling used for this statement
这里filter执行了50068次，而INDEX只执行了22962次，这里是因为有主表查询，把主表的过滤列推过来了
31 rows selected.
SQL> select count(*)
& 2&&&& from scott.htz1 a, scott.htz2 b
& 3&&& where a.object_id = b.object_
& COUNT(*)
----------
&&&& 50068
4，有重复的值
SQL> insert into scott.htz1 select * from
scott.htz1;
50068 rows created.
Commit complete.
SQL> select count(*)
& 2&&&& from scott.htz1 a, scott.htz2 b
& 3&&& where a.object_id = b.object_
& COUNT(*)
----------
&&& 100136
这里看到两个表连接后还回了100316行记录
SQL> select count(distinct a.object_id)
count_distinct
& 2&&&& from scott.htz1 a, scott.htz2 b
& 3&&& where a.object_id = b.object_
COUNT_DISTINCT
--------------
&&&&&&&& 50068
这里看到表还回的不同值为50068个
SQL> @plan_by_last.sql
SQL> set echo off
Enter value for sqlid:
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('&sqlid',null),null,'ALLSTATS LAST'))
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('',null),null,'ALLSTATS
PLAN_TABLE_OUTPUT
--------------------------------------------------
1gktx239rcv6f, child number 0
-------------------------------------
select *&&&
from scott.htz1 a, scott.htz2 b&&
where a.object_id = b.object_id&&&&
and a.object_id in (select object_id
&&&&&&&&&&&&&
from scott.htz3 c&&&&&&&&&&&&&&&&&&&&&&&&& where c.owner =
'SCOTT'&&&&&&&&&&&&&&&&&&&&&&&&&&& and
a.owner = 'SYS')
Plan hash value:
-------------------------------------------------------------------------------------------------------------------------------------------
Operation&&&&&&&&&& | Name&&&&&&&&&&&&&&&&& | Starts | E-Rows | A-Rows
|&& A-Time&& | Buffers | Reads& |&
OMem |& 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|*& 1 |& FILTER&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |&&&&& 1 |&&&&&&& |&&&&&
0 |00:00:01.29 |&& 48000 |&&&& 68 |&&
&&&&|&&&&&& |&&&&&&&&& |
|*& 2 |&& HASH JOIN&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |&&&&& 1 |&
38536 |&&& 100K|00:00:00.81
|&&& 2076 |&&&& 68 |&&&
13M|& 2031K|&& 17M (0)|
|&& 3 |&&& TABLE ACCESS FULL| HTZ1&&&&&&&&&&&&&&&&& |&&&&& 1 |&
38535 |&&& 100K|00:00:00.10
|&&& 1383 |&&&&& 0 |&&&&&& |&&&&&&
|&&&&&&&&& |
|&& 4 |&&& TABLE ACCESS FULL| HTZ2&&&&&&&&&&&&&&&&& |&&&&& 1 |&
50965 |& 5:00.10
|&&&& 693 |&&&& 68 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 5 |&& FILTER&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& | &50068 |&&&& &&&|&&&&&
0 |00:00:00.24 |&& 45924 |&&&&& 0 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 6 |&&& INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER
|& 22962 |&&&&& 1 |&&&&&
0 |00:00:00.10 |&& 45924 |&&&&& 0 |&&&&&& |&&&&&&
|&&&&&&&&& |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
&& 1 - filter(
IS NOT NULL)
access("A"."OBJECT_ID"="B"."OBJECT_ID")
filter(:B1='SYS')
access("OBJECT_ID"=:B1 AND "C"."OWNER"='SCOTT')
&& - dynamic
sampling used for this statement
通过执行计划，我们可以看到FILTER这里仍然50068，说明是DISCOUNT的值的总行数，而不是主表还回的行数
30 rows selected.
5，连接列包括多个空值的情况
SQL> update scott.htz1 set object_id='' where
rownum<10000;
9999 rows updated.
Commit complete.
SQL> update scott.htz2 set object_id='' where
rownum<10000;
9999 rows updated.
Commit complete.
SQL> select count(distinct a.object_id)
count_distinct
& 2&&&& from scott.htz1 a, scott.htz2 b
& 3&&& where a.object_id = b.object_
COUNT_DISTINCT
--------------
&&&&&&& &40069
连接列还回的DISCOUNT的值
SQL> select *
& 2&&&& from scott.htz1 a, scott.htz2 b
& 3&&& where a.object_id = b.object_id
& 4&&&&& and a.object_id in (select object_id
& 5&&&&&&&&&&&&&&&&&&&&&&&&&&& from scott.htz3 c
& 6&&&&&&&&&&&&&&&&&&&&&&&&&& where c.owner =
& 7&&&&&&&&&&&&&&&&&&&&&&&&&&&& and a.owner =
no rows selected
SQL> @plan_by_last.sql
SQL> set echo off
Enter value for sqlid:
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('&sqlid',null),null,'ALLSTATS LAST'))
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('',null),null,'ALLSTATS
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1gktx239rcv6f, child number 0
-------------------------------------
select *&&&
from scott.htz1 a, scott.htz2 b&&
where a.object_id = b.object_id&&&&
and a.object_id in (select object_id
&&&&&&&&&&&&&
from scott.htz3 c&&&&&&&&&&&&&&&&&&&&&&&&& where c.owner =
'SCOTT'&&&&&&&&&&&&&&&&&&&&&&&&&&& and
a.owner = 'SYS')
Plan hash value:
-------------------------------------------------------------------------------------------------------------------------------------------
Operation&&&&&&&&&& | Name&&&&&&&&&&&&&&&&& | Starts | E-Rows | A-Rows
|&& A-Time&& | Buffers | Reads& |&
OMem |& 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|*& 1 |& FILTER&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |&&&&& 1 |&&&&&&& |&&&&&
0 |00:00:01.15 |&& 34646 |&&&& 19 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 2 |&& HASH JOIN&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |&&&&& 1 |&
38536 |& 8:00.86
|&&& 2076 |&&&& 19 |&&&
12M|& 2029K|&& 15M (0)|
|&& 3 |&&& TABLE ACCESS FULL| HTZ1&&&&&&&&&&&&&&&&& |&&&&& 1 |&
38535 |&&& 100K|00:00:00.10
|&&& 1383 |&&&& 19 |&&&&&& |&&&&&&
|&&&&&&&&& |
|&& 4 |&&& TABLE ACCESS FULL| HTZ2&&&&&&&&&&&&&&&&& |&&&&& 1 |&
50965 |& 5:00.09
|&&&& 693 |&&&&& 0 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 5 |&& FILTER&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&& |& 40069 |&&&&&&& |&&&&& 0 |00:00:00.18 |&& 32570 |&&&&& 0 |&&&&&& |&&&&&&
|&&&&&&&&& |
|*& 6 |&&& INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER
|& 16285 |&&&&& 1 |&&&&&
0 |00:00:00.07 |&& 32570 |&&&&& 0 |&&&&&& |&&&&&&
|&&&&&&&&& |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
&& 1 - filter(
IS NOT NULL)
access("A"."OBJECT_ID"="B"."OBJECT_ID")
filter(:B1='SYS')
access("OBJECT_ID"=:B1 AND "C"."OWNER"='SCOTT')
&& - dynamic
sampling used for this statement
30 rows selected.
通过上面的测试，我们可以看到FILTER过滤的时候，子查询执行的次数为count(DISCOUNT& 主键连接列)
阅读(2646) | 评论(0) | 转发(0) |
上一篇：没有了
相关热门文章
给主人留下些什么吧！~~
请登录后评论。ThinkPHP教程 列表
相关参考课程ThinkPHP 统计查询统计查询
在应用中我们经常会用到一些统计数据，例如当前所有（或者满足某些条件）的用户数、所有用户的最大积分、学生的平均成绩等等，ThinkPHP 为这些统计操作提供了一系列的内置方法：
：统计数据行数
：统计某个字段最大数据
：统计某个字段最小数据
：统计某个字段平均数据
：统计某个字段数据之和
上述统计查询方法都是独立的方法且支持连贯操作。
count() 方法用于统计数据行数。
public function read(){
$Dao = M('User');
// 获取用户数：
$userCount = $Dao-&count();
// 添加条件：
$userCount2 = $Dao-&where('uid & 10')-&count();
$this-&assign('userCount', $userCount);
$this-&display();
上例中，两个查询语句实际执行的 SQL 为：
SELECT COUNT(*) AS tp_count FROM user LIMIT 1
SELECT COUNT(*) AS tp_count FROM user WHERE uid & 10 LIMIT 1
可以在模板中直接输出得到的统计数据：
共有用户 {$userCount} 人。
max() 方法用于统计某个字段最大数据。
统计用户最大积分例子：
$maxScore = $Dao-&max('score');
实际执行的 SQL 为：
SELECT MAX(score) AS tp_max FROM user LIMIT 1
min() 统计某个字段最小数据。
获取积分大于 0 的用户的最小积分例子：
$minScore = $Dao-&where('score&0')-&min('score');
实际执行的 SQL 为：
实际执行SQL：SELECT MIN(score) AS tp_min FROM user WHERE score&0 LIMIT 1
avg() 统计某个字段平均数据。
获取用户的平均积分例子：
$avgScore = $Dao-&avg('score');
实际执行的 SQL 为：
SELECT AVG(score) AS tp_avg FROM user LIMIT 1
sum() 统计某个字段数据之和。
统计积分排名前 10 名用户的积分之和：
$sumScore = $Dao-&order('score DESC')-&limit('10')-&sum('score');
实际执行的 SQL 为：
SELECT SUM(score) AS tp_sum FROM user ORDER BY score DESC LIMIT 10
所有的统计查询如 select() 方法一样。均支持连贯操作的使用，根据实际情况添加不同的查询条件。
《》系列教程
ThinkPHP Where 条件
ThinkPHP 连贯操作
本章节内容共分 8 部分：1.
ThinkPHP 统计查询7.
<(我爱开发网) — 提供最好的 、、、 及
