|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
|
1)创建测试表
test@CISCOSYS>
create
table
t
as
select
*
from
dba_objects;
表已创建。
test@CISCOSYS>
update
t
set
object_id =rownum ;
已更新50967行。
2)使用
count
(*)进行统计
test@CISCOSYS>
select
count
(*)
from
t;
COUNT
(*)
----------
50967
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
Time
|
-------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 |
TABLE
ACCESS
FULL
| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
-
dynamic
sampling used
for
this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
764 consistent gets
0 physical reads
0 redo
size
410 bytes sent via SQL*Net
to
client
385 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
0 sorts (memory)
0 sorts (disk)
1
rows
processed
3)使用
COUNT
(列）进行统计
test@CISCOSYS>
select
count
(*)
from
t;
COUNT
(*)
----------
50967
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
Time
|
-------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 |
TABLE
ACCESS
FULL
| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
-
dynamic
sampling used
for
this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
764 consistent gets
0 physical reads
0 redo
size
410 bytes sent via SQL*Net
to
client
385 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
0 sorts (memory)
0 sorts (disk)
1
rows
processed
解释一下 :物理读为0，是因为创建表的时候，数据已经载入
load
buffer.
可以使用
test@CISCOSYS>
alter
system flush buffer_cache;
通过比较
COUNT
(*) 和
Count
(列） ,两种情况的COST 是完全一样的。
继续试验！！！
为表创建索引
test@CISCOSYS>
create
index
idx_t_id
on
t(object_id);
索引已创建。
test@CISCOSYS>
alter
system flush buffer_cache;
系统已更改。
test@CISCOSYS>
select
count
(*)
from
t;
COUNT
(*)
----------
50967
已用时间: 00: 00: 00.26
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
Time
|
-------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 |
TABLE
ACCESS
FULL
| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
-
dynamic
sampling used
for
this statement
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
765 consistent gets
705 physical reads
0 redo
size
410 bytes sent via SQL*Net
to
client
385 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
0 sorts (memory)
0 sorts (disk)
1
rows
processed
test@CISCOSYS>
select
count
(object_id)
from
t;
COUNT
(OBJECT_ID)
----------------
50967
已用时间: 00: 00: 00.09
执行计划
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------------
--
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------------
--
| 0 |
SELECT
STATEMENT | | 1 | 13 | 30 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
| 2 |
INDEX
FAST
FULL
SCAN| IDX_T_ID | 44475 | 564K| 30 (4)| 00:00:01
|
--------------------------------------------------------------------------------
--
Note
-----
-
dynamic
sampling used
for
this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
181 consistent gets
477 physical reads
0 redo
size
418 bytes sent via SQL*Net
to
client
385 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
0 sorts (memory)
0 sorts (disk)
1
rows
processed
在这里，用
COUNT
(列）比
COUNT
(*)要快。通过比较执行计划。可以看出
COUNT
(*)不能用到索引，而
COUNT
(列)可以
继续试验！！！
将键值设为非空
test@CISCOSYS>
alter
table
T
modify
object_id
not
null
;
表已更改。
已用时间: 00: 00: 01.34
test@CISCOSYS>
alter
system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 00.01
test@CISCOSYS>
select
count
(*)
from
t;
COUNT
(*)
----------
50967
已用时间: 00: 00: 00.31
执行计划
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 |
INDEX
FAST
FULL
SCAN| IDX_T_ID | 44475 | 30 (4)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-
dynamic
sampling used
for
this statement
统计信息
----------------------------------------------------------
205 recursive calls
0 db block gets
213 consistent gets
496 physical reads
0 redo
size
410 bytes sent via SQL*Net
to
client
385 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
5 sorts (memory)
0 sorts (disk)
1
rows
processed
test@CISCOSYS>
alter
system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 00.04
test@CISCOSYS>
select
count
(object_id)
from
t;
COUNT
(OBJECT_ID)
----------------
50967
已用时间: 00: 00: 00.20
执行计划
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 |
INDEX
FAST
FULL
SCAN| IDX_T_ID | 44475 | 30 (4)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-
dynamic
sampling used
for
this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
181 consistent gets
477 physical reads
0 redo
size
418 bytes sent via SQL*Net
to
client
385 bytes received via SQL*Net
from
client
2 SQL*Net roundtrips
to
/
from
client
0 sorts (memory)
0 sorts (disk)
1
rows
processed
将一些记录object_id置为
null
.
test@CISCOSYS>
alter
table
t
modify
(object_id number
null
);
表已更改。
test@CISCOSYS>
update
t
set
object_id=
null
where
object_id<=10;
已更新10行。
test@CISCOSYS>
select
count
(*)
from
t;
COUNT
(*)
----------
50967
已用时间: 00: 00: 00.00
test@CISCOSYS>
select
count
(object_id)
from
t;
COUNT
(OBJECT_ID)
----------------
50957
发现
count
(*)和
count
(列）记录不一样。也就是说，两个功能上根本不是等价的。
如果一个列上存在索引，且非空。
COUNT
(*)和
COUNT
(列)功能相当。
反之，
COUNT
(*) 和
COUNT
(列）两者功能本身就功能不同，不应等同对待。
|
基于案例学SQL
本文转自 randy_shandong 51CTO博客，原文链接：http://blog.51cto.com/dba10g/1354315，如需转载请自行联系原作者
