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
