数据库设计--垂直拆分数据

it2025-07-28 4

假设表字段太多，假设表中有些字段比較大，即便是你仅仅查有限的几个字段。在做表关联和全表扫的时候，由于扫描的数据块多，性能方面还是会不理想。由于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 <= 100000;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 <= 100000;commit;create table test_cuizhi1 as select * from test_cuizhi;--開始測试，仅仅是取两个最小的字段SQL> set timing onSQL> set autotrace traceonlySQL> 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 processedSQL> /已选择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 processedSQL> 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 processedSQL> /已选择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

转载于:https://www.cnblogs.com/bhlsheji/p/4676191.html

最新回复(0)