{"id":1314,"date":"2016-01-12T14:10:31","date_gmt":"2016-01-12T06:10:31","guid":{"rendered":"http:\/\/www.821121.com\/?p=1314"},"modified":"2021-03-02T10:49:32","modified_gmt":"2021-03-02T02:49:32","slug":"1314","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1314","title":{"rendered":"\u5b66\u4e60\u7b14\u8bb0\u4e4bSQL-Other operations"},"content":{"rendered":"

Other operations<\/h1>\n

<\/p>\n

IN list iterator explain plan<\/h4>\n

Please note the difference between \u201cBETWEEN\u2026AND\u201d and \u201cIN list\u201d. The \u201cBETWEEN\u2026AND\u201d means a range while \u201cIN list\u201d means a list of separate values. <\/p>\n

SELECT order_id, order_type, order_amount FROM orders WHERE order_type IN (1, 2, 3);<\/code><\/pre>\n
Execution Plan <\/p>\n
SELECT STATEMENT \n   INLIST ITERATOR \n     TABLE ACCESS (BY INDEX ROWID) OF \u2018orders\u2019 \n        INDEX (RANGE SCAN) OF \u2018orders_idx1\u2019 (NON-UNIQUE)\n<\/code><\/pre>\n
Concatenation explain plan<\/h4>\n
Concatenation explain plan means the SQL statement uses \u201cOR\u201d operator to concatenate multiple query conditions associated with \u201cdifferent\u201d columns. In this case, the SQL statement will be split into multiple SELECT clauses with the best explain plan chose for each query portion, and at last combine (concatenate) the result of each query potion.
\nPlease note that only if the \u201cOR\u201d query condition is used as the driving condition will the concatenation explain plan be chosen by the optimizer; otherwise the \u201cOR\u201d query condition will be used as the filter only.
\nThe execution order of the each \u201cquery portion\u201d is starting from the last predicates (query condition) in the \u201cOR\u201d list.
\nFor example, <\/p>\n
SELECT * FROM table1 WHERE A = '10' OR B = '123';<\/code><\/pre>\n
Execution plan <\/p>\n
  CONCATENATION \n    TABLE ACCESS (BY INDEX ROWID) OF \u2018table1\u2019\n      INDEX (RANGE SCAN) OF \u2018b_idx\u2019 (NON-UNIQUE) ---- b is executed first \n    TABLE ACCESS (BY INDEX ROWID) OF \u2018table1\u2019 \n      INDEX (RANGE SCAN) OF \u2018a_idx\u2019 (NON-UNIQUE)<\/code><\/pre>\n
Sort explain plan<\/h4>\n
    \n
  • SORT (UNIQUE)
    \nThere are two possibilities for this explain plan: one is there is DISTINCT operation in the SELECT-list and the other one is there is one sub-query acting as the data provider for the main query.<\/li>\n
  • SORT (AGGREGATE)
    \nThere is no GROUP BY clause but aggregation function is used in the SELECT-list.<\/li>\n
  • SORT (GROUP BY)
    \nThere is GROUP BY clause in the SQL statement.<\/li>\n
  • SORT (JOIN)
    \nSort Merge Join.<\/li>\n
  • SORT (ORDER BY)
    \nThere is ORDER BY clause in the SQL statement.<\/li>\n<\/ul>\n
    SET operation explain plan<\/h4>\n
      \n
    • Union\/Union-All explain plan<\/li>\n
    • Intersection explain plan<\/li>\n
    • Minus explain plan<\/li>\n<\/ul>\n
      COUNT (STOPKEY) explain plan<\/h4>\n
      When the SQL statement has ROWNUM used, the explain plan will show \u201cCOUNT (STOPKEY)\u201d operation. <\/p>\n
      SELECT * FROM orders WHERE order_date = :b1 AND ROWNUM <= 20;<\/code><\/pre>\n
      Execution Plan <\/p>\n
      SELECT STATEMENT \n  COUNT (STOPKEY) \n    TABLE ACCESS (BY INDEX ROWID) OF \u2018orders\u2019 \n       INDEX (RANGE SCAN) OF \u2018order_idx2\u2019 (NON-UNIQUE)<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
      Other operations<\/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-1314","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\/1314","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=1314"}],"version-history":[{"count":2,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1314\/revisions"}],"predecessor-version":[{"id":1316,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1314\/revisions\/1316"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1314"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1314"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}