代码:


SQL> drop table test;


表已删除。



SQL> create table test as select object_id,object_name,object_type from all_objects where object_type='SYNONYM';


表已创建。



SQL> create index ind_test on test(object_id,object_type) compute statistics;


索引已创建。



SQL> set autotrace traceonly exp;
SQL> select object_type from test;


执行计划
----------------------------------------------------------                      
Plan hash value: 1357081020                                                     

                                                                                
--------------------------------------------------------------------------      

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      

--------------------------------------------------------------------------      

|   0 | SELECT STATEMENT  |      | 18683 |   200K|    32   (4)| 00:00:01 |      

|   1 |  TABLE ACCESS FULL| TEST | 18683 |   200K|    32   (4)| 00:00:01 |      

--------------------------------------------------------------------------      

                                                                                
Note                                                                            
-----                                                                           

   - dynamic sampling used for this statement                                   



SQL> select object_id from test where object_type='SYNONYM';


执行计划
----------------------------------------------------------                      
Plan hash value: 4122759097                                                     

                                                                                
--------------------------------------------------------------------------------

-                                                                               

                                                                                

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     
|                                                                               

                                                                                

--------------------------------------------------------------------------------

-                                                                               

                                                                                

|   0 | SELECT STATEMENT     |          | 18683 |   437K|    19   (6)| 00:00:01 
|                                                                               

                                                                                

|*  1 |  INDEX FAST FULL SCAN| IND_TEST | 18683 |   437K|    19   (6)| 00:00:01 
|                                                                               

                                                                                

--------------------------------------------------------------------------------

-                                                                               

                                                                                

                                                                                
Predicate Information (identified by operation id):                             

---------------------------------------------------                             

                                                                                

   1 - filter("OBJECT_TYPE"='SYNONYM')                                          

                                                                                
Note                                                                            
-----                                                                           

   - dynamic sampling used for this statement                                   



SQL> alter table test modify object_id not null;


表已更改。



SQL> desc test;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 OBJECT_ID                                 NOT NULL NUMBER

 OBJECT_NAME                                        VARCHAR2(30)

 OBJECT_TYPE                                        VARCHAR2(19)


SQL> select object_type from test;


执行计划
----------------------------------------------------------                      
Plan hash value: 4122759097                                                     

                                                                                
--------------------------------------------------------------------------------

-                                                                               

                                                                                

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     
|                                                                               

                                                                                

--------------------------------------------------------------------------------

-                                                                               

                                                                                

|   0 | SELECT STATEMENT     |          | 18683 |   200K|    19   (6)| 00:00:01 
|                                                                               

                                                                                

|   1 |  INDEX FAST FULL SCAN| IND_TEST | 18683 |   200K|    19   (6)| 00:00:01 
|                                                                               

                                                                                

--------------------------------------------------------------------------------

-