【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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- springboot配置sql提示Spring BootSQL
- 10個SQL技巧之三：進行總計算SQL
- [20180927]修改sql prompt提示.txtSQL
- SQL經典練習題48道之三(20-25)SQL
- SQL語法提示工具SQL Prompt 釋出v10.6SQL
- [20210929]sql打補丁使用rule提示問題.txtSQL
- PL/SQL 連線 Oralce 提示 Could not initialize oci.dllSQL
- 精讀《手寫 SQL 編譯器 - 錯誤提示》SQL編譯
- 教你如何解決SQL server中提示物件名無效SQLServer物件
- webpack系列之三resolveWeb
- Redis 快取之三Redis快取
- webpack外掛之三Web
- DRF之三大認證
- git操作之三：git resetGit
- iOS APP安全雜談之三iOSAPP
- spring中BeanPostProcessor之三：InitDestroyAnnotationBeanPostProcessor（01）SpringBean
- kubernetes實踐之三十一：kubectl
- 升級提示 執行SQL發生錯誤！錯誤：duplicate column name: picstitleSQL
- Laravel ORM SQL 語句查詢、檢視，附贈 IDE ORM 語法提示LaravelORMSQLIDE
- mysql資料庫匯入sql檔案一直提示不成功MySql資料庫
- PbootCMS錯誤提示：執行SQL發生錯誤！錯誤：no such column: def1bootSQL
- pbootcms升級提示 執行SQL發生錯誤！錯誤：duplicate column name: picstitlebootSQL
- 安裝SQL Server時，提示VS Shell 安裝失敗，退出程式碼為 1638。SQLServer
- OpenFaaS實戰之三：Java函式Java函式
- jackson學習之三：常用API操作API
- Django基礎之三（類檢視）Django
- CSS佈局之三欄佈局CSS
- Docker教程之三Docker核心概念Docker
- mysqld_safe之三言兩語MySql
- PBOOTCMS網站程式提示“執行SQL發生錯誤！錯誤：DISK I/O ERROR”boot網站SQLError
- JAVA基礎之三-介面和抽象類Java抽象
- Elasticsearch聚合學習之三：範圍限定Elasticsearch
- 生態十年，禾之三變
- 直播與點播之三_測試、理解
- [Alink漫談之三] AllReduce通訊模型模型
- JUnit5學習之三：Assertions類
- 演算法刷題之三一維列表演算法
- 《四平青年之三傻罪途》
- JVM垃圾回收之三色標記JVM