【SQL 提示 之三】

楊奇龍發表於2010-10-22


SQL> create table t as select * from  dba_objects;
表已建立。
SQL> create index idx_t on t (object_id);
索引已建立。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL 過程已成功完成。
SQL> select /*+ full(t) */ * from t;
執行計劃
----------------------------------------------------------                     
Plan hash value: 1601196873                                                    
--------------------------------------------------------------------------     
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
--------------------------------------------------------------------------     
|   0 | SELECT STATEMENT  |      | 68298 |  6736K|   276   (1)| 00:00:04 |     
|   1 |  TABLE ACCESS FULL| T    | 68298 |  6736K|   276   (1)| 00:00:04 |     
--------------------------------------------------------------------------     
SQL> select /*+ index(t idx_t) */ * from t;
執行計劃
----------------------------------------------------------                     
Plan hash value: 1601196873                                                    
--------------------------------------------------------------------------     
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
--------------------------------------------------------------------------     
|   0 | SELECT STATEMENT  |      | 68298 |  6736K|   276   (1)| 00:00:04 |     
|   1 |  TABLE ACCESS FULL| T    | 68298 |  6736K|   276   (1)| 00:00:04 |     
--------------------------------------------------------------------------     

SQL> select /*+ index(t idx_t) */ * from t where object_id>1;
執行計劃
----------------------------------------------------------                     
Plan hash value: 1594971208                                                    
--------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time |   
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       | 68298 |  6736K|  1189   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T | 68298 |  6736K|  1189   (1)| 00:00:15 |
|*  2 |   INDEX RANGE SCAN    | IDX_T | 68298 |       |   153   (1)| 00:00:02 |                

                                                         
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
   2 - access("OBJECT_ID">1)   
SQL> set linesize 120
SQL> set autot trace stat
SQL> select /*+ index(t idx_t) */ * from t where object_id>1;
已選擇68298行。
統計資訊
----------------------------------------------------------                                     

                       
          0  recursive calls 
          0  db block gets  
      10218  consistent gets
          0  physical reads 
          0  redo size  
    7807613  bytes sent via SQL*Net to client 
      50499  bytes received via SQL*Net from client
       4555  SQL*Net roundtrips to/from client  
          0  sorts (memory) 
          0  sorts (disk) 
      68298  rows processed
SQL> set autot traceonly
SQL> select /*+ index(t idx_t) */ * from t where object_id>1;
已選擇68298行。
執行計劃
---------------------------------------------------------- 
Plan hash value: 1594971208  
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 68298 |  6736K|  1189   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 68298 |  6736K|  1189   (1)| 00:00:15 |
|*  2 |   INDEX RANGE SCAN          | IDX_T | 68298 |       |   153   (1)| 00:00:02 |
------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">1)
統計資訊
---------------------------------------------------------- 
          0  recursive calls    
          0  db block gets 
      10218  consistent gets
          0  physical reads
          0  redo size 
    7807613  bytes sent via SQL*Net to client
      50499  bytes received via SQL*Net from client 
       4555  SQL*Net roundtrips to/from client 
          0  sorts (memory)
          0  sorts (disk) 
      68298  rows processed
SQL> select /*+ full(t) */ * from t;
已選擇68298行。
執行計劃
----------------------------------------------------------  
Plan hash value: 1601196873   
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 68298 |  6736K|   276   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T    | 68298 |  6736K|   276   (1)| 00:00:04 |
--------------------------------------------------------------------------


統計資訊
----------------------------------------------------------    
          1  recursive calls
          0  db block gets 
       5493  consistent gets
          0  physical reads
          0  redo size  
    7807613  bytes sent via SQL*Net to client
      50499  bytes received via SQL*Net from client 
       4555  SQL*Net roundtrips to/from client  
          0  sorts (memory) 
          0  sorts (disk) 
      68298  rows processed
SQL> select /*+ no_index(t idx_t) */ * from t where object_id=1;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
-------------------------------------------------------------------------- 
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   101 |   275   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   101 |   275   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=1)  

SQL> select /*+ index_desc(t idx_t) */ * from t where object_id<200;

已選擇191行。
執行計劃
----------------------------------------------------------
Plan hash value: 2821899338   
-------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |       |   194 | 19594 |     5   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS BY INDEX ROWID | T     |   194 | 19594 |     5   (0)| 00:00:01 | 
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_T |   194 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<200)  
       filter("OBJECT_ID"<200)
SQL> create bitmap index ibm_t on t (object_name);
索引已建立。
SQL> select  /*+ index_combine (t ibm_t) */ * from t;
已選擇68298行。

執行計劃
----------------------------------------------------------
Plan hash value: 2891273134  
-------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT             |       | 68298 |  6736K|  1861   (1)| 00:00:23 | 
|   1 |  TABLE ACCESS BY INDEX ROWID | T     | 68298 |  6736K|  1861   (1)| 00:00:23 | 
|   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | IBM_T |       |       |            |          |
--------------------------------------------------------------------------------------  
統計資訊
SQL> select  /*+ index_ffs (t idx_t) */ object_id from t where object_id <200;
已選擇191行。
執行計劃
----------------------------------------------------------   
Plan hash value: 2497555198 
------------------------------------------------------------------------------  
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |   194 |   970 |    44   (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_T |   194 |   970 |    44   (3)| 00:00:01 |
------------------------------------------------------------------------------ 
Predicate Information (identified by operation id):  
---------------------------------------------------
   1 - filter("OBJECT_ID"<200)
統計資訊
SQL> select  /*+ index_jion (t idx_t ibm_t) */ object_id from t
  2  where object_id >200 and status ='vaild';

未選定
執行計劃
---------------------------------------------------------- 
Plan hash value: 1601196873   -----------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    11 |   275   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    11 |   275   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id): 
---------------------------------------------------                                            

                       
   1 - filter("STATUS"='vaild' AND "OBJECT_ID">200)  
                                         
SQL> select  /*+ index_jion (t idx_t ibm_t) */ object_id from t
  2  where object_id >200 and object_name='T';
執行計劃
----------------------------------------------------------  
Plan hash value: 1178319173 
--------------------------------------------------------------------------------------  
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |       |     2 |    60 |     1   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS BY INDEX ROWID | T     |     2 |    60 |     1   (0)| 00:00:01 | 
|   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |          | 
|*  3 |    BITMAP INDEX SINGLE VALUE | IBM_T |       |       |            |          | 
-------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):    
--------------------------------------------------- 
   1 - filter("OBJECT_ID">200)  
   3 - access("OBJECT_NAME"='T') 
SQL> create bitmap index bitmap_t on t (status);
索引已建立。
SQL> select  /*+ index_jion (t idx_t ibm_t) */ object_id from t
  2  where object_id >200 and status ='vaild';
執行計劃
----------------------------------------------------------  
Plan hash value: 1188740217  
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    11 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | T        |     1 |    11 |     1   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|          |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BITMAP_T |       |       |            |          |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID">200) 
   3 - access("STATUS"='vaild')

SQL> select  /*+ index_jion (t idx_t ibm_t) */ object_id from t
  2  where object_id < 200 and status ='vaild';

未選定行
執行計劃
----------------------------------------------------------  
Plan hash value: 1188740217
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    11 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | T        |     1 |    11 |     1   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|          |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BITMAP_T |       |       |            |          |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<200) 
   3 - access("STATUS"='vaild')

SQL> select  /*+ index_join (t idx_t ibm_t) */ object_id from t
  2  where object_id < 200 and status ='vaild';

未選定
執行計劃
----------------------------------------------------------
Plan hash value: 2966373114
---------------------------------------------------------------------------------------       

| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    11 |     4 (25)|00:00:01

|                     
|*  1 |  VIEW                         | index$_join$_001 |     1 |    11 |     4 (25)| 00:00:01

|                     
|*  2 |   HASH JOIN                   |                  |       |       |            |       |

                    
|   3 |    BITMAP CONVERSION TO ROWIDS|                  |     1 |    11 |     1   (0)|00:00:01

|                     
|*  4 |     BITMAP INDEX SINGLE VALUE | BITMAP_T         |       |       |            |       |
|*  5 |    INDEX RANGE SCAN           | IDX_T            |     1 |    11 |   3  (34)|00:00:01 |

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

----                     
                                                                                               

                       
Predicate Information (identified by operation id):                                            

                       
---------------------------------------------------
   1 - filter("STATUS"='vaild' AND "OBJECT_ID"<200)
   2 - access(ROWID=ROWID)   
   4 - access("STATUS"='vaild')
   5 - access("OBJECT_ID"<200)
SQL> create table t1 as select 1 id ,object_name from dba_objects
  2  where rownum <10000;

表已建立。

SQL> set autot trace exp stat
SQL> create index idx_id_name on t1 (id,object_name);
索引已建立。
SQL> insert into t1 select 2 , object_name from dba_objects;
已建立68303行。
SQL> set autot off
SQL> insert into t1 select 3 , object_name from dba_objects;
已建立68303行。
SQL> insert into t1 select 4 , object_name from dba_objects;
已建立68303行。
SQL> set autot traceonly
SQL> select /*+ full(t) */ owner ,object_name,object_id ,count(*)
  2  from t
  3  group by owner,object_name,object_id ;
已選擇68298行。
執行計劃
----------------------------------------------------------                                     
Plan hash value: 47235625                                                                      
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 68298 |  2401K|       |   926   (1)| 00:00:12 |
|   1 |  HASH GROUP BY     |      | 68298 |  2401K|  6440K|   926   (1)| 00:00:12 |
|   2 |   TABLE ACCESS FULL| T    | 68298 |  2401K|       |   275   (1)| 00:00:04 |
-----------------------------------------------------------------------------------
統計資訊
---------------------------------------------------------- 
        325  recursive calls   
          0  db block gets  
       1068  consistent gets
          2  physical reads 
          0  redo size 
    2992057  bytes sent via SQL*Net to client
      50499  bytes received via SQL*Net from client 
       4555  SQL*Net roundtrips to/from client
          4  sorts (memory) 
          0  sorts (disk)
      68298  rows processed

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-676591/,如需轉載,請註明出處,否則將追究法律責任。

相關文章