身为DBA,为开发人员制定的DB标准肯定包含"尽量避免使用select * from tab",可此种查询究竟坏在哪里?试着解释一下,有不妥之处还望各位多多赐教;数据从disk呈现给client应该经历如下步骤:
disk --> SGA --> PGA --> client(不考虑direct read)
分两种情况讨论
1 索引扫描
如果select col采用覆盖索引,select *可能导致不必要的回表,这会导致上述各个步骤都增加成本;
如果select col采用索引且本身需要回表,则select *主要多消耗在后两个阶段(见下文);
2 全表扫描
oracle采用行存储,以block为最小IO单位,故当采用全表扫描时,select *和select col读取的数据块一样多,只是发送给客户端的bytes肯定是前者多,也就是说多出的消耗主要在SGA-->PGA和PGA-->client
注:project投影应该发生在SGA --> PGA
以一个小实验为例(11.2.0.3)create table t as select * from dba_objects;
set autotrace TRACE stat
SQL> select object_id from t;
104569 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8434 consistent gets
0 physical reads
0 redo size
1913663 bytes sent via SQL*Net to client
77205 bytes received via SQL*Net from client
6973 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
104569 rows processed
SQL> select * from t;
104569 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8434 consistent gets
0 physical reads
0 redo size
12638304 bytes sent via SQL*Net to client
77205 bytes received via SQL*Net from client
6973 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
104569 rows processed
select *要传送的字节多出一个数量级,最直观的反映是消耗的时间更长
10046文件
select *
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 70 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6973 0.29 0.28 0 8433 0 104569
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6975 0.31 0.30 0 8503 0 104569
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
row cache lock 15 0.00 0.00
SQL*Net message to client 6973 0.00 0.00
SQL*Net message from client 6973 0.00 1.08
********************************************************************************
select object_id
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6973 0.19 0.20 0 8433 0 104569
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6975 0.19 0.20 0 8433 0 104569
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6973 0.00 0.00
SQL*Net message from client 6973 0.00 0.43
********************************************************************************