{"id":1305,"date":"2016-01-08T11:46:28","date_gmt":"2016-01-08T03:46:28","guid":{"rendered":"http:\/\/www.821121.com\/?p=1305"},"modified":"2021-01-07T11:56:50","modified_gmt":"2021-01-07T03:56:50","slug":"%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0%e4%b9%8bexplain-plan-1","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1305","title":{"rendered":"\u5b66\u4e60\u7b14\u8bb0\u4e4bSQL-Explain Plan"},"content":{"rendered":"

Explain Plan<\/h1>\n

<\/p>\n

Full Table Scans<\/h4>\n

Full table scan will scan all the data block that under the HWM (high water mark), including the empty data block. In order to reduce the physical I\/O, one parameter DB_FILE_MUTLIBLOCK_READ_COUNT can be set to a higher value.<\/p>\n

ROWID Scans<\/h4>\n

ROWID is composed of data object id, data file id, data block id and the record slot in the data block. The fastest way to retrieve one record from one table is to use ROWID.<\/p>\n

Index Scans<\/h4>\n
    \n
  1. Index Unique Scan<\/li>\n
  2. Index Range Scan<\/li>\n
  3. Index Range Scans Descending
    \nIndex range scan descending is similar to index range scan, except it accesses the table data in descending order instead of in ascending order. The optimizer will choose this kind of index scan under two circumstances: one is the query uses the \u201cORDER BY\u2026DESC\u201d and the other one is the query uses the \u201cINDEX_DESC\u201d hint.<\/li>\n
  4. Index Skip Scan
    \nIndex Skip Scan is introduced to resolve the issue of the composite index cannot be used if the leading column is not used in the predicates.<\/li>\n
  5. Index Full Scan
    \nIndex Full Scan will be used when the following two conditions are met,
    \nAll the columns in the SELECT-list are included in the index.
    \nThere is at least one NOT NULL column in the index<\/li>\n
  6. Index Fast Full Scan
    \nThe difference between Index Fast Full Scan and Index Full Scan is that the Index Fast Full Scan will read multiple index blocks rather than one block in each I\/O operation.<\/li>\n<\/ol>\n

    B-Tree Cluster Access<\/h4>\n

    Hash Cluster Access<\/h4>\n

    Sample Table Access<\/h4>\n

    Sample table access is only available in Full Table Scans and Index Fast Full Scans. The basic syntax is as follows, <\/p>\n

    SELECT \u2026 FROM table_name SAMPLE {BLOCK option} (Sample Percent) WHERE\u2026 GROUP BY\u2026 HAVING\u2026 ORDER BY\u2026<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
    Explain Plan<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,48],"tags":[],"class_list":["post-1305","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category-sql"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1305","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1305"}],"version-history":[{"count":3,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1305\/revisions"}],"predecessor-version":[{"id":1309,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1305\/revisions\/1309"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1305"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1305"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1305"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}