约束对效率的影响

 约束对效率的影响
＝＝＝＝＝＝＝＝＝＝
sqlplus scott/tiger

SQL> create table t1 pctfree 0
  2  as
  3  select * from dba_objects where 1=2;

Table created.

SQL> alter table T1
  2  add constraint T1_PK primary key (OBJECT_ID);

Table altered.
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size' 
and b.value > 0

SQL> 
SQL> set timing on
17:07:23 SQL> set time on                                                               
17:07:24 SQL> create table t2 pctfree 0                                      
17:07:44   2  as                                                             
17:07:44   3  select                                                         
17:07:44   4         t.Owner           ,                                     
17:07:44   5         t.Object_Name     ,                                     
17:07:44   6         t.Subobject_Name  ,                                     
17:07:44   7         rownum obid ,                                 
17:07:44   8         t.Data_Object_Id  ,                                     
17:07:44   9         t.Object_Type     ,                                     
17:07:44  10         t.Created         ,                                     
17:07:44  11         t.Last_Ddl_Time   ,                                     
17:07:44  12         t.Timestamp       ,                                     
17:07:44  13         t.Status          ,                                     
17:07:44  14         t.Temporary       ,                                     
17:07:44  15         t.Generated       ,                                     
17:07:44  16         t.Secondary                                             
17:07:44  17  from (select * from dba_objects where rownum <=20000) t,       
17:07:44  18       (select rownum rn from dba_objects where rownum <=200) d; 
                                                                            
                                                                             

Elapsed: 00:01:04.59
18:36:37 SQL> 18:36:37 SQL> 18:36:37 SQL> 18:36:37 SQL> 18:36:37 SQL> 
18:42:08 SQL>  select a.name, b.value
18:42:13   2   from v$statname a, v$mystat b
18:42:13   3   where a.statistic# = b.statistic#
18:42:13   4   and a.name = 'redo size' 
18:42:13   5   and b.value > 0
18:42:15   6  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
red