Database Design-Vertical Data splitting

If there are too many fields in the table and some fields in the table are relatively large, even if you only query a limited number of fields, due to the large number of data blocks scanned during table Association and full table scan, performance is still unsatisfactory. Because oracle scans by block and reads by block, this function cannot be optimized at the SQL level, we can consider vertical data splitting. Here is an experiment:

-- Manufacturing data is not vertically split
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;

-- Vertical splitting of manufacturing data
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;

-- Start the test, but take the two smallest Fields
SQL> set timing on
SQL> set autotrace traceonly
SQL> select t. a, t1.a from test t, test1 t1 where t. a = t1.a;
Row 100000 has been selected.
Used time: 00: 00: 53.17
Execution Plan
----------------------------------------------------------
Plan hash value: 2400077556
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select statement | 44504 | 1129K | 173 K (1) | 00:34:38 |
| * 1 | hash join | 44504 | 1129K | 173 K (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". "")
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
52 recursive CILS
0 db block gets
795627 consistent gets
534917 physical reads
0 redo size
1664840 bytes sent via SQL * Net to client
73664 bytes encoded ed via SQL * Net from client
6668 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>/
Row 100000 has been selected.
Used time: 00: 00: 33.36
Execution Plan
----------------------------------------------------------
Plan hash value: 2400077556
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select statement | 44504 | 1129K | 173 K (1) | 00:34:38 |
| * 1 | hash join | 44504 | 1129K | 173 K (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". "")
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
795446 consistent gets
552087 physical reads
0 redo size
1664840 bytes sent via SQL * Net to client
73664 bytes encoded ed 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;
Row 100000 has been selected.
Used time: 00: 00: 06.17
Execution Plan
----------------------------------------------------------
Plan hash value: 2501302817
Bytes -------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------
| 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 |
Bytes -------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("T". "A" = "T1". "")
Note
-----
-Dynamic sampling used for this statement


Statistics
----------------------------------------------------------
52 recursive CILS
0 db block gets
7139 consistent gets
153 physical reads
0 redo size
1664840 bytes sent via SQL * Net to client
73664 bytes encoded ed via SQL * Net from client
6668 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed


SQL>/
Row 100000 has been selected.
Used time: 00: 00: 06.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2501302817
Bytes -------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------
| 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 |
Bytes -------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("T". "A" = "T1". "")
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
7008 consistent gets
0 physical reads
0 redo size
1664840 bytes sent via SQL * Net to client
73664 bytes encoded ed via SQL * Net from client
6668 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed