時(shí)間：2015-06-28 00:00:00 來(lái)源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評(píng)論(1)
?????有的時(shí)候，使用錯(cuò)誤的索引會(huì)導(dǎo)致Oracle數(shù)據(jù)庫(kù)的效率明顯下降，通過(guò)一些方法或者是技巧可以有效的避免這個(gè)問(wèn)題:
這個(gè)例子中,如果我想使用idx_a而不是idx_b.
| ?? SQL>?create?table?test 2?(a?int,b?int,c?int,d?int); Table?created. SQL>?begin 2?for?i?in?1..50000 3?loop 4?insert?into?mytest?values(i,i,i,i); 5?end?loop; 6?commit; 7?end; 8?/ PL/SQL?procedure?successfully?completed. SQL>?create?index?idx_a?on?mytest(a,b,c); Index?created. SQL>?create?index?idx_b?on?mytest(b); Index?created. |
如表mytest,有字段a,b,c,d，在a,b,c上建立聯(lián)合索引idx_a(a,b,c)，在b上單獨(dú)建立了一個(gè)索引idx_b(b)。
在正常情況下，where a=? and b=? and c=?會(huì)用到索引idx_a，where b=?會(huì)用到索引idx_b
比如：
| ?? SQL>?analyze?table?mytest?compute?statistics; Table?analyzed. SQL>?select?num_Rows?from?user_tables?where?table_name='MYTEST'; NUM_ROWS ---------- 50000 SQL>?select?distinct_keys?from?user_indexes?where?index_name='IDX_A'; DISTINCT_KEYS ------------- 50000 SQL>?set?autotrace?traceonly SQL>?select?d?from?mytest 2?where?a=10?and?b=10?and?c=10; Execution?Plan ---------------------------------------------------------- Plan?hash?value:?1542625214 -------------------------------------------------------------------------------- ------ |?Id?|?Operation?|?Name?|?Rows?|?Bytes?|?Cost?(%CPU)|?Time | -------------------------------------------------------------------------------- ------ |?0?|?SELECT?STATEMENT?|?|?1?|?16?|?2?(0)|?00:0 0:01?| |?1?|?TABLE?ACCESS?BY?INDEX?ROWID|?MYTEST?|?1?|?16?|?2?(0)|?00:0 0:01?| |*?2?|?INDEX?RANGE?SCAN?|?IDX_A?|?1?|?|?1?(0)|?00:0 0:01?| -------------------------------------------------------------------------------- ------ Predicate?Information?(identified?by?operation?id): --------------------------------------------------- 2?-?access("A"=10?AND?"B"=10?AND?"C"=10) Statistics ---------------------------------------------------------- 1?recursive?calls 0?db?block?gets 4?consistent?gets 0?physical?reads 0?redo?size 508?bytes?sent?via?SQL*Net?to?client 492?bytes?received?via?SQL*Net?from?client 2?SQL*Net?roundtrips?to/from?client 0?sorts?(memory) 0?sorts?(disk) 1?rows?processed SQL>?select?d?from?mytest 2?where?b=500; Execution?Plan ---------------------------------------------------------- Plan?hash?value:?530004086 -------------------------------------------------------------------------------- ------ |?Id?|?Operation?|?Name?|?Rows?|?Bytes?|?Cost?(%CPU)|?Time | -------------------------------------------------------------------------------- ------ |?0?|?SELECT?STATEMENT?|?|?1?|?8?|?2?(0)|?00:0 0:01?| |?1?|?TABLE?ACCESS?BY?INDEX?ROWID|?MYTEST?|?1?|?8?|?2?(0)|?00:0 0:01?| |*?2?|?INDEX?RANGE?SCAN?|?IDX_B?|?1?|?|?1?(0)|?00:0 0:01?| -------------------------------------------------------------------------------- ------ Predicate?Information?(identified?by?operation?id): --------------------------------------------------- 2?-?access("B"=500) Statistics ---------------------------------------------------------- 1?recursive?calls 0?db?block?gets 4?consistent?gets 0?physical?reads 0?redo?size 508?bytes?sent?via?SQL*Net?to?client 492?bytes?received?via?SQL*Net?from?client 2?SQL*Net?roundtrips?to/from?client 0?sorts?(memory) 0?sorts?(disk) 1?rows?processed |
#p#副標(biāo)題#e#
但是在這樣一個(gè)條件下：where a=? and b=? and c=? group by b會(huì)用到哪個(gè)索引呢?在索引的分析數(shù)
關(guān)鍵詞標(biāo)簽：Oracle
相關(guān)閱讀
熱門文章
Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法
oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例
誤刪Oracle數(shù)據(jù)庫(kù)實(shí)例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例 Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法 ORACLE SQL 判斷字符串是否為數(shù)字的語(yǔ)句 ORACLE和SQL語(yǔ)法區(qū)別歸納(1) oracle grant 授權(quán)語(yǔ)句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法