【SQL 提示 之三】
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql hint 提示】SQL
- MySQL SQL hint 提示MySql
- 10個SQL技巧之三：進行總計算SQL
- springboot配置sql提示Spring BootSQL
- Oracle SQL效能最佳化系列講座之三(轉)OracleSQL
- SQL經典練習題48道之三(20-25)SQL
- Oracle 12c 新SQL提示（hint）OracleSQL
- Bad SQL 優化加提示 /*+ordered */SQL優化
- oracle sqlplus SQL提示符OracleSQL
- 設定你的SQL提示符SQL
- SQL語法提示工具SQL Prompt 釋出v10.6SQL
- Sql最佳化(五) hint（提示）介紹SQL
- 總結SQL語句中的優化提示SQL優化
- SQL hint中正確使用use_nl提示SQL
- Sql server 2005遷移至Oracle系列之三：在Oracle中建立sql中的常見函式SQLServerOracle函式
- RMAN 提示符下執行SQL語句SQL
- oracle sql hint提示_BITMAP CONVERSION FROM ROWIDSOracleSQL
- SQL Server 2005命令提示實用工具SQLServer
- 【SQL 提示 之二】index_ss Index Skip HintSQLIndex
- PL/SQL Developer中輸入SQL語句時如何自動提示欄位SQLDeveloper
- 精讀《手寫 SQL 編譯器 - 錯誤提示》SQL編譯
- [20180927]修改sql prompt提示.txtSQL
- PL/SQL 連線 Oralce 提示 Could not initialize oci.dllSQL
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- 巧妙設定 SQL Server獲得智慧程式碼提示SQLServer
- SQL Server 2008中SQL增強之三：Merge(在一條語句中使用Insert,Update,Delete)SQLServerdelete
- Redis 快取之三Redis快取
- webpack外掛之三Web
- 教你如何解決SQL server中提示物件名無效SQLServer物件
- 登入Oracle錯誤提示Error 6 initializing SQL*PlusOracleErrorSQL
- ORACLE記憶體管理 之三 PGA v$sql_workarea_histogram v$pga_target_adviceOracle記憶體SQLHistogram
- SQL調優（SQL TUNING）並行查詢提示（Hints）之pq_distribute的使用SQL並行
- zt_Oracle 10gR2新SQL提示——opt_paramOracle 10gSQL
- webpack系列之三resolveWeb
- git操作之三：git resetGit
- 【MySQL】5.7新特性之三MySql
- go微服務系列之三Go微服務
- 【MySQL】死鎖案例之三MySql