oracle优化--表优化（宽表与父子表关联性能差异）

一、创建宽表并写入数据

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31479729/viewspace-2199943/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31479729/viewspace-2199943/