ITPub博客

首页 > 数据库 > Oracle > oracle优化--表优化(宽表与父子表关联性能差异)

oracle优化--表优化(宽表与父子表关联性能差异)

原创 Oracle 作者:wysyl1990 时间:2018-08-13 15:54:38 0 删除 编辑

一、创建宽表并写入数据


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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2017-08-01

  • 博文量
    13
  • 访问量
    11827