Oracle執(zhí)行計(jì)劃——使用indexfullscan的幾種情況-創(chuàng)新互聯(lián)

常見有三種情況都有用到index full scan.

10年的日喀則網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。營銷型網(wǎng)站的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整日喀則建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。創(chuàng)新互聯(lián)從事“日喀則網(wǎng)站設(shè)計(jì)”,“日喀則網(wǎng)站推廣”以來，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

1. 查詢列就是索引列

2. 對索引列進(jìn)行order by時(shí)

3. 對索列進(jìn)行聚合計(jì)算時(shí)

通過案例學(xué)調(diào)優(yōu)之--Index FULL SCAN和Index FAST FULL SCAN

Index FULL SCAN 和ndex FAST FULL SCAN工作原理：

Index FULL SCAN 和Index FAST FULL SCAN的適用情況：適用于我們想選擇的列都包含在索引里邊時(shí)，這時(shí)候就可以使用IFS或者FFS來代替全表掃描來得到想要的結(jié)果。

INDEX FULL SCAN：
HINT寫法：INDEX(表名索引名)
原理：ORACLE定位到索引的ROOT BLOCK，然后到BRANCH BLOCK（如果有的話），再定位到第一個(gè)LEAF BLOCK, 然后根據(jù)LEAF BLOCK的雙向鏈表順序讀取。它所讀取的塊都是有順序的，也是經(jīng)過排序的。
INDEX FAST FULL SCAN：
HINT寫法：INDEX_FFS(表名索引名)
原理：從段頭開始，讀取包含位圖塊，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK，讀取的順序完全有物理存儲(chǔ)位置決定，并采取多塊讀，每次讀取DB_FILE_MULTIBLOCK_READ_COUNT個(gè)塊。查詢某個(gè)表記錄總數(shù)的時(shí)候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的。

Fast Full Index Scans :
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

http://download-west.oracle.com/doc…imops.htm#51111

Full Table Scans :
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement’s WHERE clause.

When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.

http://download-west.oracle.com/doc…imops.htm#44852

出處：

/tupian/20230522/1582044

網(wǎng)頁題目：Oracle執(zhí)行計(jì)劃——使用indexfullscan的幾種情況-創(chuàng)新互聯(lián)
鏈接URL：http://muchs.cn/article2/dspcic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、微信公眾號(hào)、網(wǎng)站內(nèi)鏈、網(wǎng)站建設(shè)、品牌網(wǎng)站建設(shè)、小程序開發(fā)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容