一、创建宽表并写入数据 SQL> create table test 2 ( 3 id number, 4 co11 varchar2(2000), 5 col2 varchar2(2000), 6 col3 varchar2(2000), 7 col4 varchar2(2000), 8 col5 varchar2(2000) 9 ); Table created.
SQL> insert into test values (1,dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000));
1 row created.
SQL> commit;
Commit complete. 二、创建父子表并写入数据 SQL> create table test1 2 ( 3 id number, 4 co11 varchar2(2000), 5 col2 varchar2(2000), 6 col3 varchar2(2000) 7 ); Table created.
SQL> insert into test1 values (1,dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000));
1 row created.
SQL> commit;
Commit complete. SQL> create table test2 2 ( 3 pid number, 4 col4 varchar2(2000), 5 col5 varchar2(2000) 6 ); Table created. SQL> insert into test2 values (1,dbms_random.string('u', 2000),dbms_random.string('u', 2000)); 1 row created. SQL> commit; Commit complete. 三、将数据写入磁盘，并查看两类表的块信息（可以看见test表出现了行连接，另外两张表正常） SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.test where id=1; B F ---------- ---------- 243 4 SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.test1 where id=1; B F ---------- ---------- 255 4 SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.test2 where pid=1; B F ---------- ---------- 175 5 SQL> alter system dump datafile 4 block 243; System altered. tl: 4018 fb: --H-F--- lb: 0x1 cc: 3 nrid: 0x010000f7.0 col 0: [ 2] c1 02 col 1: [2000] SQL> alter system dump datafile 4 block 255; System altered. tab 0, row 0, @0x819 tl: 6015 fb: --H-FL-- lb: 0x1 cc: 4 col 0: [ 2] c1 02 col 1: [2000] SQL> alter system dump datafile 5 block 175; System altered. tab 0, row 0, @0xfec tl: 4012 fb: --H-FL-- lb: 0x1 cc: 3 col 0: [ 2] c1 02 col 1: [2000] 四、未建立索引与主外键情况下，对比执行计划（宽表性能更佳） SQL ID: 82jj6fu5ssxu8 Plan Hash: 1357081020 select * from scott.test where id=1 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 2 0.00 0.00 0 8 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 8 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS FULL TEST (cr=8 pr=0 pw=0 time=35 us cost=3 size=5023 card=1) ===================================================================== SQL ID: 3m458gjqd93k4 Plan Hash: 497311279 select ID,CO11,COL2,COL3,COL4,COL5 from scott.test1,scott.test2 where ID=PID and ID=1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 34 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 14 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 48 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 HASH JOIN (cr=14 pr=0 pw=0 time=84 us cost=6 size=5036 card=1) 1 1 1 TABLE ACCESS FULL TEST1 (cr=7 pr=0 pw=0 time=16 us cost=3 size=3019 card=1) 1 1 1 TABLE ACCESS FULL TEST2 (cr=7 pr=0 pw=0 time=5 us cost=3 size=2017 card=1) 五、创建索引与主外键后，对比执行计划（发现宽表的性能仍略优于表关联） SQL> conn scott/scott Connected. SQL> alter table test add constraint pk_id primary key(id); Table altered. SQL> alter table test1 add constraint pk_test1_id primary key(id); Table altered. SQL> ALTER TABLE TEST2 ADD CONSTRAINT FK_TEST2_ID FOREIGN KEY(PID ) REFERENCES TEST1(ID); Table altered. SQL> create index idx_pid on test2(pid); Index created. ====================================================================== SQL ID: 8a8520gkcv1gx Plan Hash: 2011057250 SELECT ID,CO11,COL2,COL3,COL4,COL5 FROM scott.test1,scott.test2 where ID=PID AND ID=1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 110 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 114 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=22 us cost=2 size=5036 card=1) 1 1 1 TABLE ACCESS BY INDEX ROWID TEST1 (cr=2 pr=0 pw=0 time=13 us cost=1 size=3019 card=1) 1 1 1 INDEX UNIQUE SCAN PK_TEST1_ID (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)(object id 87500) 1 1 1 TABLE ACCESS BY INDEX ROWID TEST2 (cr=2 pr=0 pw=0 time=6 us cost=1 size=2017 card=1) 1 1 1 INDEX RANGE SCAN IDX_PID (cr=1 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 87501) ========================================================================= SQL ID: 82jj6fu5ssxu8 Plan Hash: 1131473842 select * from scott.test where id=1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS BY INDEX ROWID TEST (cr=3 pr=0 pw=0 time=35 us cost=1 size=5023 card=1) 1 1 1 INDEX UNIQUE SCAN PK_ID (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)(object id 87499)
