oracle利用索引關聯獲得資料的方法
and_equal:
這種方式需要查詢條件裡面包括所有索引列,然後取得每個索引中得到的rowid列表,然後對這些列表做merge join,過濾出相同的rowid後再去表中獲取資料或者直接從索引中獲得資料.and_equal有一些限制,比如它只對單列索引有效,只對非唯一索引有效,使用到的索引不能超過5個,查詢條件只能是”=”.在10g中,and_equal已經被廢棄了,只能透過hint才能生效.
create table test as select * from dba_objects;
create index ind_test_owner on test(owner);
create index ind_test_object_name on test(object_name);
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where wner=’test’ and object_name=’test’;
Execution Plan
———————————————————-
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|
|* 1 | AND-EQUAL | | | | |
|* 2 | INDEX RANGE SCAN| IND_TEST_OWNER | 1 | | 1 (0)|
|* 3 | INDEX RANGE SCAN| IND_TEST_OBJECT_NAME | 2 | | 1 (0)|
——————————————————————————-
如果查詢條件只包含owner
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where wner=’test’;
Execution Plan
———————————————————-
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 29 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)|
———————————————————————————–
修改owner,object_name為非空
alter table test modify(owner not null);
alter table test modify(object_name not null);
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where wner=’test’;
Execution Plan
———————————————————-
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 29 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)|
———————————————————————————–
效果一樣
查詢條件是”>”的情況
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where owner>’test’ and object_name=’test’;
Execution Plan
———————————————————-
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)|
—————————————————————————————–
查詢條件是in的情況
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where owner in(’test’,'dba’) and object_name=’test’;
Execution Plan
———————————————————-
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)|
—————————————————————————————–
再來看一下回表的情況
SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where wner=’test’ and object_name=’test’;
Execution Plan
———————————————————-
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)|
| 2 | AND-EQUAL | | | | |
|* 3 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)|
|* 4 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)|
—————————————————————————————–
先透過and_equal取得rowid列表,然後從表中返回資料.
index_join:
index join顧名思義是對index進行關聯,oracle透過hash index join的方式實現了避免對錶的訪問.所有的資料都從索引中直接獲得.它不受查詢條件影響,可以是唯一索引,也可以是多列索引.
SQL 10G>select/*+ index_join(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where wner=’test’ and object_name=’test’;
Execution Plan
———————————————————-
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 29 | 3 (34)|
|* 1 | VIEW | index$_join$_001 | 1 | 29 | 3 (34)|
|* 2 | HASH JOIN | | | | |
|* 3 | INDEX RANGE SCAN| IND_TEST_OWNER | 1 | 29 | 1 (0)|
|* 4 | INDEX RANGE SCAN| IND_TEST_OBJECT_NAME | 1 | 29 | 1 (0)|
——————————————————————————–
可以不帶查詢條件,只不過由index range scan變成了index fast full scan
SQL 10G>select/*+ index_join(test ind_test_owner ind_test_object_name)*/ owner,object_name from test
2 ;
Execution Plan
———————————————————-
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
————————————————————————————
| 0 | SELECT STATEMENT | | 51984 | 1472K| 478 (2)|
| 1 | VIEW | index$_join$_001 | 51984 | 1472K| 478 (2)|
|* 2 | HASH JOIN | | | | |
| 3 | INDEX FAST FULL SCAN| IND_TEST_OWNER | 51984 | 1472K| 153 (2)|
| 4 | INDEX FAST FULL SCAN| IND_TEST_OBJECT_NAME | 51984 | 1472K| 322 (1)|
————————————————————————————
如果不是所有資料都能從索引獲得,那麼將不會使用index join
SQL 10G>select/*+ index_join(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where wner=’test’ and object_name=’test’;
Execution Plan
———————————————————-
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)|
———————————————————————————–
index_combine:
index combine最早是用在bitmap index上的,在9i開始oracle預設可以使用在btree索引上,這是由_b_tree_bitmap_plans引數來控制的.oracle將btree索引中獲得的rowid資訊透過BITMAP CONVERSION FROM ROWIDS的步驟轉換成bitmap進行匹配,然後匹配完成後透過BITMAP CONVERSION TO ROWIDS再轉換出rowid獲得資料或者回表獲得資料.
SQL 10G>select/*+ index_combine(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where wner=’test’ and object_name=’test’;
Execution Plan
———————————————————-
———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|
| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 29 | 2 (0)|
| 2 | BITMAP AND | | | | |
| 3 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 4 | INDEX RANGE SCAN | IND_TEST_OWNER | | | 1 (0)|
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 6 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | | | 1 (0)|
———————————————————————————————
回表取資料的情況
SQL 10G>select/*+ index_combine(test ind_test_owner ind_test_object_name)*/ owner,object_name,object_type from test where wner=’test’ and object_name=’test’;
Execution Plan
———————————————————-
———————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 40 | 2 (0)|
| 2 | BITMAP CONVERSION TO ROWIDS | | | | |
| 3 | BITMAP AND | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 5 | INDEX RANGE SCAN | IND_TEST_OWNER | | | 1 (0)|
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 7 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | | | 1 (0)|
———————————————————————————————-
不帶查詢條件的情況,index combine將不被使用
SQL 10G>select/*+ index_combine(test ind_test_owner ind_test_object_name)*/ owner,object_name from test
2 ;
Execution Plan
———————————————————-
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————–
| 0 | SELECT STATEMENT | | 51984 | 1472K| 1480 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 51984 | 1472K| 1480 (1)|
| 2 | INDEX FULL SCAN | IND_TEST_OWNER | 51984 | | 123 (2)|
———————————————————————————–
index_combine會是and_equal的很好的替代者,隨著and_equal的退出,index_combine將更多得被我們看到。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9399028/viewspace-680224/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 對Oracle效能資料的獲得。Oracle
- 如獲取獲取關聯資料的文件跟模型的關聯資料集呢模型
- 如何利用VoC資料獲得客戶需求的全景檢視?
- 關於如何獲取資料的方法
- 異構資料庫的關聯查詢 oracle hsodbc 關聯mysql資料庫OracleMySql
- 如何在前臺獲取模型的關聯資料模型
- 如何利用資料視覺化和分析來獲得實時洞察?視覺化
- (利用索引)大資料查詢索引大資料
- Oracle中利用函式索引處理資料傾斜案例Oracle函式索引
- qt獲得本地IP的方法QT
- 利用Dbutils實現往兩個有關聯的表新增資料
- ABAP 從檢視獲得資料
- 如何獲得SQL Server索引使用情況SQLServer索引
- Redis全文搜尋教程之建立索引並關聯源資料Redis索引
- 關聯式資料庫索引設計和優化器前言資料庫索引優化
- 獲取Oracle資料庫awr報告方法Oracle資料庫
- 利用索引提高SQL Server資料處理的效率索引SQLServer
- 利用ODBC實現Domino和關聯式資料庫的互操作 (轉)資料庫
- ZABBIX伺服器端無法獲得AGENT資料解決方法伺服器
- 記錄獲得ORACLE的transaction IDOracle
- AylaNetworks獲得AWS物聯網能力資質認證
- oracle資料庫獲取指定表的列的相關資訊Oracle資料庫
- 關於Mysql索引的資料結構MySql索引資料結構
- 【Oracle】-【索引】先查資料再建索引,還是先建索引再插資料?Oracle索引
- Oracle 11g impdp 先資料後索引匯入方法Oracle索引
- Mysql: 利用強制索引去掉重資料MySql索引
- Spring中獲得dataSurce物件的方法Spring物件
- [轉]Mysql資料庫相關資料索引MySql資料庫索引
- Jmeter 請求 websocket 獲得的響應資料不全JMeterWeb
- 獲得資料庫操作日誌的三種方式資料庫
- 關於InnoDB表資料和索引資料的儲存索引
- 關於 Oracle 分割槽索引的正確 DROP 和 TRUNCATE 方法Oracle索引
- oracle資料庫獲取繫結變數的各種方法Oracle資料庫變數
- 中國電信主導國際電聯首個大資料標準獲得批准大資料
- SQL Server資料庫建立新使用者及關聯資料庫的方法教程SQLServer資料庫
- lastIndexOf方法——獲取字元最後的索引ASTIndex字元索引
- 關於模型關聯 獲取不到關聯資訊 求教模型
- SQLAIchemy資料模型關聯SQLAI模型