今天根据 TOM的书做了个实验,关于主键的效率到底有多少影响
如下
SQL> create table cities
2as
3select username city
4from all_users;
表已创建。
SQL> alter table cities
2add constraint
3cities_pk primary key(city)
4;
表已更改。
SQL> create table with_ri
2(x char(80),
3city references cities);
表已创建。
SQL> create table without_ri
2(x char(80),
3city varchar2(30)
4);
表已创建。
SQL> show parameter sql_trace
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
sql_trace
boolean FALSE
SQL> alter session set sql_trace=true;
会话已更改。
SQL>
SQL> declare
2type array is table of varchar2(30) index by binary_integer;
3l_data array;
4begin
5select * bulk collect into l_data from cities;
6for i in 1..1000
7loop
8for j in 1 ..l_data.count
9loop
10insert into with_ri
11values('x',l_data(j));
12insert into without_ri
13values('x',l_data(j));
14end loop;
15end loop;
16end;
17/
PL/SQL 过程已成功完成。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开
C:\Documents and Settings\ljb>tkprof 'F:\oracle\admin\fayxl\udump\fayxl_ora_2340
.trc' abcd.prf sys=no;
TKPROF: Release 9.2.0.1.0 - Production on 星期五 4月 27 13:40:35 2007
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
C:\Documents and Settings\ljb>
看格式化后的跟踪文件如下，发现影响好小
********************************************************************************
INSERT into with_ri
values('x',:b1)
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.03 0.09
0 28
0 0
Execute310006.8910.82
049466267 31000
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total310016.9210.91
052266267 31000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
INSERT into without_ri
values('x',:b1)
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
1
0 0
Execute310006.2410.22
046435261 31000
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total310016.2410.22
046535261 31000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
到这里实验做好了，不过有一个细节想知道一下,建主键,建外键,TOM是这样建的啊?
SQL> create table with_ri
2(x char(80),
3city references cities);
这个city都不要定义了啊,就这样就可以了啊,表示和主表的对应类型一致啊
|
