如果表字段太多，如果表中有些字段比较大，即便是你只查有限的几个字段，在做表关联和全表扫的时候，由于扫描的数据块多，性能方面还是会不理想。因为oracle扫描的时候是按照块为单位扫描，读取的时候也是按块为单位读取，所以这种功能无法在sql层面上优化的
如果表字段太多，如果表中有些字段比较大，即便是你只查有限的几个字段，在做表关联和全表扫的时候，由于扫描的数据块多，性能方面还是会不理想。因为oracle扫描的时候是按照块为单位扫描，读取的时候也是按块为单位读取，所以这种功能无法在sql层面上优化的时候，可以考虑做数据的垂直切分，下面来做个试验：
--制造数据不做垂直切分
create table test(
a number,
b varchar2(4000),
c varchar2(4000),
d varchar2(4000),
e varchar2(4000),
f varchar2(4000),
g varchar2(4000),
h varchar2(4000)
);
insert into test
select rownum,
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1),
rpad('*', 4000, 1)
from dual
connect by rownum commit;
create table test1 as select * from test;
--制造数据做垂直切分
create table test_cuizhi(
a number
);
insert into test_cuizhi
select rownum
from dual
connect by rownum commit;
create table test_cuizhi1 as select * from test_cuizhi;
--开始测试，只是取两个最小的字段
sql> set timing on
sql> set autotrace traceonly
sql> select t.a,t1.a from test t, test1 t1 where t.a=t1.a;
已选择100000行。
已用时间: 00: 00: 53.17
执行计划
----------------------------------------------------------
plan hash value: 2400077556
----------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
----------------------------------------------------------------------------
| 0 | select statement | | 44504 | 1129k| 173k (1)| 00:34:38 |
|* 1 | hash join | | 44504 | 1129k| 173k (1)| 00:34:38 |
| 2 | table access full| test | 44504 | 564k| 87801 (1)| 00:17:34 |
| 3 | table access full| test1 | 117k| 1490k| 85344 (1)| 00:17:05 |
----------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access(t.a=t1.a)
note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
52 recursive calls
0 db block gets
795627 consistent gets
534917 physical reads
0 redo size
1664840 bytes sent via sql*net to client
73664 bytes received via sql*net from client
6668 sql*net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed
sql> /
已选择100000行。
已用时间: 00: 00: 33.36
执行计划
----------------------------------------------------------
plan hash value: 2400077556
----------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
----------------------------------------------------------------------------
| 0 | select statement | | 44504 | 1129k| 173k (1)| 00:34:38 |
|* 1 | hash join | | 44504 | 1129k| 173k (1)| 00:34:38 |
| 2 | table access full| test | 44504 | 564k| 87801 (1)| 00:17:34 |
| 3 | table access full| test1 | 117k| 1490k| 85344 (1)| 00:17:05 |
----------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access(t.a=t1.a)
note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
795446 consistent gets
552087 physical reads
0 redo size
1664840 bytes sent via sql*net to client
73664 bytes received via sql*net from client
6668 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
sql> select t.a,t1.a from test_cuizhi t, test_cuizhi1 t1 where t.a=t1.a;
已选择100000行。
已用时间: 00: 00: 06.17
执行计划
----------------------------------------------------------
plan hash value: 2501302817
-------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes |tempspc| cost (%cpu)| time |
-------------------------------------------------------------------------------------------
| 0 | select statement | | 88629 | 2250k| | 310 (2)| 00:00:04 |
|* 1 | hash join | | 88629 | 2250k| 2168k| 310 (2)| 00:00:04 |
| 2 | table access full| test_cuizhi | 88629 | 1125k| | 42 (3)| 00:00:01 |
| 3 | table access full| test_cuizhi1 | 101k| 1288k| | 39 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access(t.a=t1.a)
note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
52 recursive calls
0 db block gets
7139 consistent gets
153 physical reads
0 redo size
1664840 bytes sent via sql*net to client
73664 bytes received via sql*net from client
6668 sql*net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed
sql> /
已选择100000行。
已用时间: 00: 00: 06.06
执行计划
----------------------------------------------------------
plan hash value: 2501302817
-------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes |tempspc| cost (%cpu)| time |
-------------------------------------------------------------------------------------------
| 0 | select statement | | 88629 | 2250k| | 310 (2)| 00:00:04 |
|* 1 | hash join | | 88629 | 2250k| 2168k| 310 (2)| 00:00:04 |
| 2 | table access full| test_cuizhi | 88629 | 1125k| | 42 (3)| 00:00:01 |
| 3 | table access full| test_cuizhi1 | 101k| 1288k| | 39 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access(t.a=t1.a)
note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7008 consistent gets
0 physical reads
0 redo size
1664840 bytes sent via sql*net to client
73664 bytes received via sql*net from client
6668 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed