當前位置：首頁 > 编程语言 > php >内容正文

php

php 排序不影响索引,为什么不能通过索引避免排序操作呢??

發(fā)布時間：2023/12/2 php 25 豆豆

生活随笔收集整理的這篇文章主要介紹了 php 排序不影响索引,为什么不能通过索引避免排序操作呢?? 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

謝謝各位的回復(fù),尤其是棉花糖ONE!

忘了說了,我的數(shù)據(jù)庫版本是:10.2.0.4 的

剛才做了一個10053事件.CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END=1 確實被重寫成了STOCK_NUM>0.

在這種情況下:

SELECT * FROM

(

select /*+ INDEX(zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND STOCK_NUM>0 ORDER BY CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END,price

)

WHERE ROWNUM<=1;

PRICE

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 3473024397

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

|? ?0 | SELECT STATEMENT? ?? ?? ?? ???|? ?? ?? ?? ???|? ???1 |? ? 13 |? ? 10? ?(0)| 00:00:01 |

|*??1 |??COUNT STOPKEY? ?? ?? ?? ?? ? |? ?? ?? ?? ???|? ?? ? |? ?? ? |? ?? ?? ?? ?|? ?? ?? ? |

|? ?2 |? ?VIEW? ?? ?? ?? ?? ?? ?? ?? ?|? ?? ?? ?? ???|? ???2 |? ? 26 |? ? 10? ?(0)| 00:00:01 |

|*??3 |? ? TABLE ACCESS BY INDEX ROWID| ZSJ_TEST? ???| 50798 |? ? 97M|? ? 10? ?(0)| 00:00:01 |

|*??4 |? ???INDEX RANGE SCAN? ?? ?? ? | IND_ZSJ_TEST |? ???2 |? ?? ? |? ???9? ?(0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - filter("STOCK_NUM">0)

4 - access("PRODUCTNAME"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')

Statistics

----------------------------------------------------------

0??recursive calls

0??db block gets

43??consistent gets

0??physical reads

0??redo size

512??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

oracle找到productname=rpad('A',2000,'A'),順著這個索引走,直至找到第一個滿足"STOCK_NUM">0的數(shù)據(jù),

因為是ORDER BY CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END,price,而這個索引中case when=0的排在前頭,必須走完case when=0的才能找到第一個滿足條件的數(shù)據(jù)

所以它的邏輯讀大致應(yīng)該是:

height + case when=0的葉塊 + 這些葉塊的回訪表的IO= 9 + ceil(16943*25/(50803+25)) + 25 =9+9+25=43

基本上是一樣的.

所以create index ind5_zsj_test on zsj_test(productname,CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END desc,price); 后

SELECT * FROM

(

select /*+ INDEX(zsj_test ind5_zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND STOCK_NUM>0 ORDER BY CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END desc,price

)

WHERE ROWNUM<=1;

應(yīng)該是找到的第一個索引條目就是滿足條件的,所以的邏輯io基本上就是: height + 一個回訪表的操作=10

PRICE

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 4033270375

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

|? ?0 | SELECT STATEMENT? ?? ?? ?? ???|? ?? ?? ?? ?? ?|? ???1 |? ? 13 |? ? 10? ?(0)| 00:00:01 |

|*??1 |??COUNT STOPKEY? ?? ?? ?? ?? ? |? ?? ?? ?? ?? ?|? ?? ? |? ?? ? |? ?? ?? ?? ?|? ?? ?? ? |

|? ?2 |? ?VIEW? ?? ?? ?? ?? ?? ?? ?? ?|? ?? ?? ?? ?? ?|? ???2 |? ? 26 |? ? 10? ?(0)| 00:00:01 |

|*??3 |? ? TABLE ACCESS BY INDEX ROWID| ZSJ_TEST? ?? ?| 50798 |? ? 97M|? ? 10? ?(0)| 00:00:01 |

|*??4 |? ???INDEX RANGE SCAN? ?? ?? ? | IND5_ZSJ_TEST |? ???2 |? ?? ? |? ???9? ?(0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - filter("STOCK_NUM">0)

4 - access("PRODUCTNAME"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAA')

Statistics

----------------------------------------------------------

0??recursive calls

0??db block gets

10??consistent gets

0??physical reads

0??redo size

512??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

確實是這樣的.

*******************

而且

SELECT * FROM

(

select /*+ INDEX(zsj_test ind4_zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND sign(stock_num)=1 ORDER BY price

)

WHERE ROWNUM<=1;

訪問所有sign(stock_num)=1的葉塊,造成很高的邏輯IO,近1.7W

改寫成

SELECT * FROM

(

select /*+ INDEX(zsj_test ind4_zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND sign(stock_num)=1 ORDER BY sign(stock_num),price

)

WHERE ROWNUM<=1;

PRICE

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 16471340

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

|? ?0 | SELECT STATEMENT? ?|? ?? ?? ?? ?? ?|? ???1 |? ? 13 |? ???9? ?(0)| 00:00:01 |

|*??1 |??COUNT STOPKEY? ???|? ?? ?? ?? ?? ?|? ?? ? |? ?? ? |? ?? ?? ?? ?|? ?? ?? ? |

|? ?2 |? ?VIEW? ?? ?? ?? ? |? ?? ?? ?? ?? ?|? ???2 |? ? 26 |? ???9? ?(0)| 00:00:01 |

|*??3 |? ? INDEX RANGE SCAN| IND4_ZSJ_TEST |? ?508 |? ?997K|? ???9? ?(0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - access("PRODUCTNAME"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' AND

SIGN("STOCK_NUM")=1)

Statistics

----------------------------------------------------------

0??recursive calls

0??db block gets

9??consistent gets

0??physical reads

0??redo size

512??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

不回訪表,不排序,邏輯io:9,確實是我想要的結(jié)果.

雖然說,AND sign(stock_num)=1 ORDER BY sign(stock_num),price和

AND sign(stock_num)=1 ORDER BY price應(yīng)該是等價的,后者也應(yīng)該是9這個邏輯讀的,但不明白為什么不是這樣.

但至少找到了我想要的結(jié)果,找到了一種解決方案.

總結(jié)

以上是生活随笔為你收集整理的php 排序不影响索引,为什么不能通过索引避免排序操作呢??的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。