{"id":292,"date":"2012-01-14T21:07:15","date_gmt":"2012-01-14T13:07:15","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=292"},"modified":"2014-01-14T21:12:15","modified_gmt":"2014-01-14T13:12:15","slug":"sql%e8%b0%83%e4%bc%9803-%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92%e7%9a%84%e8%ae%bf%e9%97%ae%e8%b7%af%e5%be%84","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=292","title":{"rendered":"SQL\u8c03\u4f1803–\u6267\u884c\u8ba1\u5212\u7684\u8bbf\u95ee\u8def\u5f84"},"content":{"rendered":"

Optimizer operators<\/p>\n

\n
    \n
  1. \u884c\u6e90\u64cd\u4f5c\n
      \n
    1. Unary Operations:\u4e00\u5143\u8fd0\u7b97,\u5373\u5355\u8868\u7684\u67e5\u8be2;<\/li>\n
    2. Binary Operations:\u4e8c\u5143\u8fd0\u7b97,\u4e24\u8868\u7684\u8fde\u63a5;<\/li>\n
    3. N-ary Operations:\u591a\u5143\u8fd0\u7b97;<\/li>\n<\/ol>\n<\/li>\n
    4. \u4e3b\u8981\u7684\u7ed3\u6784\u548c\u8bbf\u95ee\u8def\u5f84:\n
        \n
      1. \u8868:\n
          \n
        1. Full Table Scan<\/span>;<\/li>\n
        2. Rowid Scan:\u5f88\u5c11\u4f7f\u7528,\u591a\u7528\u5728\u5185\u90e8\u7684\u67d0\u4e00\u4e2a\u6b65\u9aa4;<\/li>\n
        3. Sample Table Scan:\u5f88\u5c11\u4f7f\u7528;<\/li>\n<\/ol>\n<\/li>\n
        4. \u7d22\u5f15:\n
            \n
          1. Index Scan(Unique);<\/li>\n
          2. Index Scan(Range);<\/li>\n
          3. Index Scan(Full);<\/li>\n
          4. Index Scan(Fast Full);<\/li>\n
          5. Index Scan(Skip);<\/li>\n
          6. Index Scan(Index Join);<\/li>\n
          7. Using Bitmap Indexes;<\/li>\n
          8. Combining Bitmap Indexes;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
          9. \u7d22\u5f15\u7684\u57fa\u672c\u6982\u5ff5:\n
              \n
            1. B-Tree Indexes:\u5e73\u8861\u6811\u7d22\u5f15,\u6700\u5e38\u89c1\u7684\u7d22\u5f15;\n
                \n
              1. \u6b63\u5e38\u7d22\u5f15; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
              2. \u57fa\u4e8e\u51fd\u6570\u7684\u7d22\u5f15:\n
                  \n
                1. \u521b\u5efa\u51fd\u6570\u7d22\u5f15\u76f8\u5f53\u4e8e\u5728\u8868\u4e0a\u6dfb\u52a0\u4e00\u4e2a\u4f2a\u5217; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                2. \u67e5\u770b\u5b9a\u4e49; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                3. IOT(Index-Organized Table):\u5c06\u8868\u7ed3\u6784\u6574\u4f53\u653e\u5165\u7d22\u5f15\u4e2d,\u800c\u4e14\u6309\u7167\u4e3b\u952e\u8fdb\u884c\u6392\u5e8f,\u4e00\u5b9a\u8981\u6709\u4e3b\u952e,\u975e\u4e3b\u952e\u7684\u5217\u4e00\u5b9a\u8981\u843d\u5728\u7d22\u5f15\u6761\u76ee\u91cc;<\/li>\n
                4. Bitmap Indexes;\n
                  \n
                    \n
                  1. \u53ef\u4ee5\u7d22\u5f15\u7a7a\u503c;<\/li>\n
                  2. \u9002\u5f53\u53d1\u751f\u8f6c\u6362:TO ROWIDS\/FROM ROWIDS\/COUNT;<\/li>\n
                  3. \u53ef\u4ee5\u8fdb\u884c\u7684\u64cd\u4f5c:MERGE\/AND\/OR\/MINUS\/KEY ITERATION,\u4f4d\u8fd0\u7b97\u7684\u901f\u5ea6\u5f88\u5feb;<\/li>\n
                  4. \u4f4d\u56fe\u7d22\u5f15\u53ef\u4ee5\u8fdb\u884cSINGLE VALUE\/ RANGE SCAN\/ FULL SCAN\u626b\u63cf;<\/li>\n
                  5. \u7f3a\u70b9\u662f\u4f4d\u56fe\u7d22\u5f15\u4e0d\u80fd\u7ecf\u5e38\u66f4\u65b0,\u6548\u7387\u5f88\u5dee;<\/li>\n<\/ol>\n<\/div>\n<\/li>\n
                  6. Cluster Indexes;\n
                    \n
                      \n
                    1. \u5982\u679c\u8981\u505a\u4e24\u4e2a\u8868\u7684\u5173\u8054\u67e5\u8be2\u5219\u6700\u5c11\u67e5\u8be2\u4e24\u4e2a\u5757;<\/li>\n
                    2. CLUSTER\u628a\u4e24\u4e2a\u8868\u6309\u7167\u5173\u8054\u7684\u5b57\u6bb5\u628a\u8bb0\u5f55\u5b58\u653e\u5728\u540c\u4e00\u4e2a\u5757\u4e0a;\u8fd9\u6837\u53ea\u7528\u67e5\u4e00\u4e2a\u5757\u5373\u53ef;\u67e5\u627e\u65f6\u6548\u7387\u63d0\u9ad8\u4e00\u500d;<\/li>\n
                    3. \u7528\u5728\u603b\u662f\u5173\u8054\u67e5\u8be2\u4e24\u4e2a\u8868\u7684\u60c5\u51b5,\u4e00\u822c\u662f\u4e0d\u7528\u7684;ORACLE\u5185\u90e8\u5927\u91cf\u4f7f\u7528;<\/li>\n
                    4. cluster\u4e0a\u7684\u7d22\u5f15\u4e0d\u80fd\u6307\u5b9a\u5217,\u5fc5\u987b\u4f7f\u7528\u6240\u6709\u7684\u5217;<\/li>\n
                    5. \u57fa\u4e8ecluster\u7684\u8868\u6ca1\u6709segment;<\/li>\n<\/ol>\n<\/div>\n<\/li>\n<\/ol>\n<\/li>\n
                    6. \u7d22\u5f15\u7684\u5c5e\u6027:\n
                        \n
                      1. \u952e\u538b\u7f29;<\/li>\n
                      2. \u53cd\u8f6c\u952e\u503c(\u8003\u70b9<\/span>):\u53ef\u4ee5\u9632\u6b62\u7d22\u5f15\u5757\u4e89\u7528(buffer busy wait),\u53ea\u652f\u6301\u7b49\u5f0f\u8fde\u63a5,\u4e0d\u652f\u6301\u8303\u56f4\u626b\u63cf;<\/li>\n
                      3. \u987a\u5e8f\/\u5012\u5e8f;<\/li>\n<\/ol>\n<\/li>\n
                      4. \u7d22\u5f15\u548cNULL\u503c:\n
                          \n
                        1. NULL\u503c\u4e0e\u7d22\u5f15\u7684\u5173\u7cfb:\n
                            \n
                          1. \u57fa\u4e8e\u5355\u5217\u7684\u552f\u4e00\u7d22\u5f15,\u53ef\u4ee5\u591a\u6b21\u63d2\u5165NULL\u503c(NULL <> NULL),\u56e0\u4e3a\u7d22\u5f15\u5e76\u4e0d\u5b58\u50a8NULL\u503c;<\/li>\n
                          2. \u57fa\u4e8e\u591a\u5217\u7684\u7b26\u5408\u7d22\u5f15,\u5c3d\u7ba1\u5168\u4e3aNULL\u7684\u503c\u53ef\u4ee5\u591a\u6b21\u63d2\u5165([NULL, NULL] <> [NULL, NULL]),\u7d22\u5f15\u4e5f\u4e0d\u4f1a\u5b58\u50a8,\u4f46\u4e0d\u5168\u4e3aNULL\u7684\u91cd\u590d\u884c\u5219\u4e0d\u80fd\u91cd\u590d\u63d2\u5165,;<\/li>\n<\/ol>\n<\/li>\n
                          3. NULL\u503c\u4e0e\u6267\u884c\u8ba1\u5212:\n
                              \n
                            1. \u5982\u679c\u5217\u7684\u5c5e\u6027\u5141\u8bb8\u4e3aNULL,\u6761\u4ef6\u4e3aIS NULL\u7684\u8bdd,\u80af\u5b9a\u8d70\u5168\u8868\u626b\u63cf,\u56e0\u4e3a\u7d22\u5f15\u4e0d\u4fdd\u5b58NULL\u503c;<\/li>\n
                            2. \u5982\u679c\u5217\u7684\u5c5e\u6027\u5141\u8bb8\u4e3aNULL,\u6761\u4ef6\u4e3aIS NOT NULL\u7684\u8bdd,\u4f1a\u8d70\u5168\u7d22\u5f15\u626b\u63cf;<\/li>\n
                            3. \u5982\u679c\u5217\u7684\u5c5e\u6027\u4e3aNOT NULL,\u6761\u4ef6\u4e3aIS [NOT] NULL\u7684\u8bdd,\u8d70\u7d22\u5f15\u626b\u63cf;<\/li>\n
                            4. \u7ec4\u5408\u7d22\u5f15\u7684\u8bdd,\u5982\u679c\u6761\u4ef6\u4e2d\u53ea\u51fa\u73b0\u4e00\u5217\u7684\u8bdd\u8ddf\u5355\u5217\u7d22\u5f15\u4e00\u6837;<\/li>\n
                            5. \u7ec4\u5408\u7d22\u5f15\u7684\u8bdd,\u5982\u679c\u6761\u4ef6\u4e2d\u51fa\u73b0\u4e24\u5217,\u4f1a\u4f18\u5148\u9009\u62e9\u8d70\u7d22\u5f15;<\/li>\n<\/ol>\n<\/li>\n
                            6. IS NULL\u4f7f\u7528\u7d22\u5f15\u7684\u529e\u6cd5:\n
                                \n
                              1. \u5728NULL\u7684\u5217\u4e0a\u521b\u5efa\u51fd\u6570\u7d22\u5f15:nvl(column_name, -1),\u67e5\u8be2\u7684\u65f6\u5019\u6761\u4ef6\u6307\u5b9a\u51fd\u6570\u7d22\u5f15: where\u00a0nvl(column_name, -1) = -1;<\/li>\n
                              2. \u4e3aNULL\u7684\u5217\u6dfb\u52a0\u9ed8\u8ba4\u503c;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                              3. \u7d22\u5f15\u7684\u7ba1\u7406:\n
                                  \n
                                1. \u63d2\u5165\u6570\u636e\u540e\u518d\u521b\u5efa\u7d22\u5f15,\u5bf9\u4e8eDW\u6765\u8a00;<\/li>\n
                                2. \u5728\u9002\u5f53\u7684\u8868\u548c\u5217\u4e0a\u52a0\u7d22\u5f15;<\/li>\n
                                3. \u6ce8\u610f\u7ec4\u5408\u7d22\u5f15\u7684\u987a\u5e8f;<\/li>\n
                                4. \u63a7\u5236\u7d22\u5f15\u7684\u6570\u91cf:\u6bcf\u6dfb\u52a0\u4e00\u4e2a\u7d22\u5f15,DML\u7684\u6548\u7387\u4e0b\u964d3\u500d,\u5b98\u65b9\u63a8\u8350\u6700\u591a\u52a07\u4e2a\u7d22\u5f15;<\/li>\n
                                5. \u5220\u9664\u4e0d\u4f7f\u7528\u7684\u7d22\u5f15;<\/li>\n
                                6. \u4e3a\u7d22\u5f15\u6307\u5b9a\u5355\u72ec\u7684\u8868\u7a7a\u95f4;<\/li>\n
                                7. \u521b\u5efa\u7d22\u5f15\u65f6\u4f7f\u7528\u5e76\u884c,NOLOGGING\u53c2\u6570;<\/li>\n
                                8. COALESCING\u662f\u5408\u5e76\u76f8\u90bb\u53f6\u5b50\u8282\u70b9,rebuild\u5219\u53ef\u4ee5\u51cf\u5c11\u7d22\u5f15\u6811\u7684\u9ad8\u5ea6;<\/li>\n<\/ol>\n<\/li>\n
                                9. \u68c0\u6d4b\u7d22\u5f15\u662f\u5426\u88ab\u4f7f\u7528\u4e86:\n
                                    \n
                                  1. \u6dfb\u52a0\u5bf9\u67d0\u4e2a\u7d22\u5f15\u7684\u76d1\u63a7:ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE;<\/li>\n
                                  2. \u67e5\u770b\u76d1\u89c6\u7684\u5bf9\u8c61\u4f7f\u7528\u60c5\u51b5:SELECT * FROM v$object_usage;\u9ed8\u8ba4\u662f\u6ca1\u6709\u4efb\u4f55\u7684\u76d1\u89c6\u7684; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                  3. \u4f7f\u7528\u6b64\u7d22\u5f15\u540e\u518d\u67e5\u770b; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"\"\"<\/li>\n
                                  4. \u53d6\u6d88\u7d22\u5f15\u76d1\u63a7:ALTER INDEX EMP_EMP_ID_PK NOMONITORING USAGE;\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                  5. \u7f3a\u70b9:\u6bcf\u6b21\u53ea\u80fd\u6dfb\u52a0\u4e00\u4e2a\u7d22\u5f15,\u800c\u4e14\u4e0d\u8bb0\u5f55\u7d22\u5f15\u4f7f\u7528\u7684\u6b21\u6570;<\/li>\n
                                  6. \u4e0d\u4f7f\u7528\u7d22\u5f15\u7684\u539f\u56e0:\n
                                    \n
                                      \n
                                    1. \u88ab\u68c0\u7d22\u7684\u5217\u4e0a\u7528\u4e86\u51fd\u6570;<\/li>\n
                                    2. \u6570\u636e\u7c7b\u578b\u4e0d\u5339\u914d;\u53d1\u751f\u9690\u58eb\u8f6c\u6362\u662f\u8f6c\u5316\u5de6\u8fb9\u7684\u5217,\u800c\u4e0d\u662f\u53f3\u8fb9\u7684\u5217;<\/li>\n
                                    3. \u7edf\u8ba1\u4fe1\u606f\u662f\u5426\u6700\u65b0;<\/li>\n
                                    4. \u5217\u662f\u5426\u662f\u7a7a\u503c;<\/li>\n
                                    5. \u7d22\u5f15\u6548\u7387\u592a\u4f4e;<\/li>\n<\/ol>\n<\/div>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                    6. \u5404\u79cd\u8bbf\u95ee\u8def\u5f84\u7684\u539f\u7406\u53ca\u4f7f\u7528\u573a\u666f:\n
                                        \n
                                      1. Full Table Scan:\n
                                          \n
                                        1. \u4f1a\u6267\u884cMultiblock Reads,\u53c2\u8003\u521d\u59cb\u5316\u53c2\u6570:db_file_multiblock_read_count; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                        2. \u4f1a\u8bfb\u53d6HWM(High-Water Mark)\u4ee5\u4e0b\u6240\u6709\u88ab\u683c\u5f0f\u5316\u7684\u5757;<\/li>\n
                                        3. \u8fc7\u7a0b\u4e2d\u53ef\u80fd\u4f1a\u8fc7\u6ee4\u67d0\u4e9b\u8bb0\u5f55;<\/li>\n
                                        4. \u7528\u5728\u8981\u83b7\u5f97\u5927\u91cf\u8bb0\u5f55\u7684\u65f6\u5019,\u6bd4\u7d22\u5f15\u626b\u63cf\u66f4\u5feb;<\/li>\n
                                        5. \u4f7f\u7528\u7684\u573a\u666f:\n
                                            \n
                                          1. \u6ca1\u6709\u5408\u9002\u7684\u7d22\u5f15;<\/li>\n
                                          2. \u8fc7\u6ee4\u6761\u4ef6\u4e0d\u597d,\u751a\u81f3\u662f\u6ca1\u6709\u8fc7\u6ee4\u6761\u4ef6;<\/li>\n
                                          3. \u8868\u592a\u5c0f,\u8bb0\u5f55\u6570\u5f88\u5c11;<\/li>\n
                                          4. \u9700\u8981\u5e76\u884c\u626b\u63cf,\u5e76\u884c\u626b\u63cf\u4e00\u5b9a\u4e0d\u8d70\u7d22\u5f15,\u5982\u679c\u786e\u5b9a\u662f\u5168\u8868\u7684\u8bdd\u53ef\u4ee5\u8003\u8651\u5e76\u884c:SELECT\u00a0\/*+ PARALLEL(d 4) *\/<\/span>\u00a0* FROM departments d; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                          5. \u52a0\u5168\u8868\u626b\u63cf\u7684hint\u65f6:SELECT\u00a0\/*+ FULL (d)*\/<\/span>\u00a0* FROM departments d WHERE department_id = 10; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"\"\"<\/li>\n
                                          6. IS NULL\u7684\u64cd\u4f5c; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                          7. ROWID Scan:\n
                                              \n
                                            1. \u6839\u636e\u8bb0\u5f55\u7684rowid\u67e5\u8be2,\u6700\u5feb\u7684\u8bbf\u95ee\u65b9\u5f0f,\u4f46\u4e0d\u7ecf\u5e38\u4f7f\u7528,\u53ef\u80fd\u4f1a\u51fa\u73b0\u5728\u6267\u884c\u8ba1\u5212\u7684\u67d0\u4e2a\u6b65\u9aa4\u4e2d;<\/li>\n
                                            2. \u4f7f\u7528\u7684\u65b9\u6cd5: \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"\"\"<\/li>\n<\/ol>\n<\/li>\n
                                            3. Sample Table Sacns:\u57fa\u672c\u4e0d\u7528,SELECT * FROM departments SAMPLE BLOCK (10) SEED (1);\"\"<\/li>\n
                                            4. Index Unique Scan:\u6761\u4ef6\u4e2d\u6307\u5b9a\u4e86\u4e3b\u952e\u5217\u6216\u8005\u552f\u4e00\u952e\u7684\u5217\u5c31\u8d70\u552f\u4e00\u952e\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                            5. Index Range Sacn:\n
                                                \n
                                              1. \u8fc7\u6ee4\u7684\u6761\u4ef6\u4e0a\u4e0d\u662f\u4e3b\u952e\/\u552f\u4e00\u7d22\u5f15,\u5c31\u4f1a\u8d70\u7d22\u5f15\u8303\u56f4\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                              2. \u5982\u679c\u5bf9\u6709\u7d22\u5f15\u7684\u5217\u6392\u5012\u5e8f\u5c31\u4f1a\u6709\u7d22\u5f15\u5012\u5e8f\u626b\u63cf;(\u56e0\u4e3a\u7d22\u5f15\u672c\u8eab\u662f\u6392\u5e8f\u7684,\u6240\u4ee5\u6267\u884c\u8ba1\u5212\u4e2d\u4e0d\u4f1a\u6709\u6392\u5e8f\u7684\u6b65\u9aa4,\u6309\u7167\u7d22\u5f15\u5217\u6392\u5e8f\u6548\u7387\u4f1a\u9ad8;) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                              3. \u8d70\u51fd\u6570\u7d22\u5f15\u7684\u4f8b\u5b50,\u4e5f\u662f\u7d22\u5f15\u8303\u56f4\u626b\u63cf\u7684\u4e00\u79cd; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                                              4. Index Full Sacn vs Index Fast Full Sacn:\n
                                                  \n
                                                1. \u51fa\u73b0\u7684\u6761\u4ef6:\n
                                                    \n
                                                  1. SELECT\u4e0eWHERE\u5b50\u53e5\u51fa\u73b0\u7684\u6240\u6709\u7684\u5217\u5fc5\u987b\u5b58\u5728\u7d22\u5f15,\u800c\u4e14\u4e3a\u975e\u7a7a\u5217,\u56e0\u4e3a\u7d22\u5f15\u4e0d\u5b58\u653eNULL\u503c;<\/li>\n
                                                  2. \u8fd4\u56de\u7684\u6570\u636e\u603b\u884c\u5360\u636e\u7d22\u5f15\u768410%\u4ee5\u4e0a\u7684\u6bd4\u4f8b;<\/li>\n<\/ol>\n<\/li>\n
                                                  3. Index Full Sacn:\n
                                                      \n
                                                    1. \u5b8c\u5168\u6309\u7167\u7d22\u5f15\u5b58\u50a8\u7684\u987a\u5e8f\u4f9d\u6b21\u8bbf\u95ee\u6574\u4e2a\u7d22\u5f15\u6811,\u5f53\u8bbf\u95ee\u5230\u53f6\u5b50\u8282\u70b9\u65f6,\u6309\u7167\u53cc\u5411\u94fe\u8868\u65b9\u5f0f\u8bfb\u53d6\u76f8\u8fde\u7684\u8282\u70b9\u503c;<\/li>\n
                                                    2. \u4f7f\u7528Single Read,\u4f1a\u4ea7\u751fdb file sequential reads\u4e8b\u4ef6;<\/li>\n
                                                    3. \u5bf9\u4e8e\u7d22\u5f15\u5217\u4e0a\u7684\u6392\u5e8f,\u603b\u662f\u4f1a\u4f7f\u7528Index Full Scan;<\/li>\n
                                                    4. \u7d22\u5f15\u5217\u4e0ais not null\u7684\u64cd\u4f5c,\u4f1a\u8d70\u5168\u7d22\u5f15\u626b\u63cf;<\/li>\n<\/ol>\n<\/li>\n
                                                    5. Index Fast Full Sacn:\n
                                                        \n
                                                      1. \u5bf9\u4e8e\u7d22\u5f15\u7684\u5206\u652f\u7ed3\u6784\u53ea\u662f\u7b80\u5355\u7684\u83b7\u53d6,\u7136\u540e\u626b\u63cf\u6240\u6709\u7684\u53f6\u8282\u70b9,\u5bfc\u81f4\u7d22\u5f15\u7ed3\u6784\u6ca1\u6709\u8bbf\u95ee,\u83b7\u5f97\u7684\u6570\u636e\u6ca1\u6709\u6839\u636e\u7d22\u5f15\u952e\u7684\u987a\u5e8f\u6392\u5e8f,\u8bfb\u53d6\u6548\u7387\u9ad8.\u4f46\u662f\u5982\u679cSQL\u8bed\u53e5\u4e2d\u6709\u6392\u5e8f\u64cd\u4f5c\u7684\u8bdd,\u8fd8\u8981\u989d\u5916\u591a\u505a\u4e00\u6b21\u6392\u5e8f;<\/li>\n
                                                      2. \u5728\u4f7f\u7528Index Fast Full Sacn\u65f6,\u4f7f\u7528Multiblock Read,\u4f1a\u4ea7\u751fdb file scattered reads,db_file_multiblock_read_count\u53c2\u6570\u7684\u8bbe\u7f6e\u5f88\u91cd\u8981;<\/li>\n
                                                      3. \u7edf\u8ba1\u884c\u6570,\u5982count(*)\u7684\u64cd\u4f5c\u603b\u662f\u4f1a\u4f7f\u7528Index [Fast] Full Scan\u7684;<\/li>\n
                                                      4. \u4f1a\u4f7f\u7528\u5927\u91cf\u7684\u5185\u5b58\u548cCPU\u8d44\u6e90;<\/li>\n<\/ol>\n<\/li>\n
                                                      5. Index [Fast] Full Scan\u7684\u4f8b\u5b50; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"\"\"<\/li>\n<\/ol>\n<\/li>\n
                                                      6. Index Skip Scan:\n
                                                          \n
                                                        1. \u521b\u5efa\u4e86\u590d\u5408\u7d22\u5f15,\u4f46\u662f\u6761\u4ef6\u4e2d\u53ea\u6709\u590d\u5408\u7d22\u5f15\u4e2d\u7684\u7b2c\u4e8c\u5217,\u800c\u4e14\u5f53\u7b2c\u4e00\u5217\u7684distinct\u503c\u4e0d\u591a\u65f6,\u4f1a\u53d1\u751f\u8df3\u8dc3\u626b\u63cf;<\/li>\n
                                                        2. \u521b\u5efa\u4e00\u4e2a\u6d4b\u8bd5\u8868,\u548c\u4e00\u4e2a\u8054\u5408\u7d22\u5f15,\u5f53\u7b2c\u4e00\u5217\u53ef\u9009\u503c\u5c11\u800c\u6761\u4ef6\u4e2d\u53ea\u67e5\u627e\u7b2c\u4e8c\u5217\u65f6,\u53d1\u751f\u8df3\u8d8a\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                        3. \u5982\u679c\u7b2c\u4e00\u5217\u7684\u53ef\u9009\u503c\u5f88\u591a,\u6761\u4ef6\u4e2d\u67e5\u627e\u7b2c\u4e8c\u5217\u7684\u8bdd,\u53d1\u751f\u5168\u8868\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                                                        4. Index Join Scan:\u67e5\u8be2\u7684\u5217\u90fd\u4e0d\u4e3a\u7a7a,\u800c\u4e14\u90fd\u6709\u7d22\u5f15\u624d\u4f1a\u51fa\u73b0\u8054\u5408\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                        5. AND-EQUAL\u64cd\u4f5c:\u4e24\u5217\u90fd\u6709\u7d22\u5f15,\u5206\u522b\u626b\u63cf\u4e24\u5217\u83b7\u5f97\u8bb0\u5f55\u7684rowid,\u7136\u540e\u518d\u53d6rowid\u7684\u4ea4\u96c6;<\/li>\n
                                                        6. Bitmap Index:\n
                                                            \n
                                                          1. Bitmap\u7684\u5355\u503c\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                          2. Bitmap\u7684\u8303\u56f4\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                                          3. Bitmap\u7684\u8fed\u4ee3\u64cd\u4f5c\u64cd\u4f5c; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                          4. Bitmap\u7684AND\u64cd\u4f5c; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                                          5. \u6392\u5e8f\u64cd\u4f5c:\n
                                                              \n
                                                            1. Sort Operator:\n
                                                                \n
                                                              1. AGGREGATE:\u5728group\u64cd\u4f5c\u7528\u4f1a\u7528\u5230,\u7edf\u8ba1\u7ed3\u679c;<\/li>\n
                                                              2. UNIQUE:\u8bc4\u4f30\u662f\u5426\u91cd\u590d;<\/li>\n
                                                              3. JOIN:\u505a\u5408\u5e76\u64cd\u4f5c;<\/li>\n
                                                              4. GROUP BY,ORDER BY:\u5728group by\u548corder by\u7684\u65f6\u5019\u4f7f\u7528;<\/li>\n<\/ol>\n<\/li>\n
                                                              5. Hash Operator:\n
                                                                  \n
                                                                1. GROUP BY:\u5728group by\u64cd\u4f5c\u65f6\u4f7f\u7528;<\/li>\n
                                                                2. UNIQUE:\u8ddfSORT UNIQUE\u4e00\u6837;<\/li>\n<\/ol>\n<\/li>\n
                                                                3. 10g\u4e4b\u540e\u7ed3\u679c\u9ed8\u8ba4\u4e0d\u6392\u5e8f,\u5982\u679c\u60f3\u8981\u6392\u5e8f\u540e\u7684\u7ed3\u679c,\u5e94\u8be5\u603b\u662f\u4f7f\u7528ORDER BY\u5b57\u53e5;<\/li>\n<\/ol>\n<\/li>\n
                                                                4. Buffer Sort:\n
                                                                    \n
                                                                  1. BUFFER SORT\u4e0d\u662f\u4e00\u79cd\u6392\u5e8f,\u800c\u662f\u4e00\u79cd\u4e34\u65f6\u8868\u7684\u521b\u5efa\u65b9\u5f0f;<\/li>\n
                                                                  2. BUFFER\u8868\u793a\u5728\u5185\u5b58\u4e2d\u5b58\u653e\u4e86\u4e00\u5f20\u4e34\u65f6\u8868;<\/li>\n
                                                                  3. SORT\u6765\u4fee\u9970BUFFER\u8868\u793a\u5177\u4f53\u518d\u5185\u5b58\u7684\u4ec0\u4e48\u5730\u65b9:\u5728PGA\u7684SQL\u5de5\u4f5c\u533a\u7684\u6392\u5e8f\u533a;<\/li>\n
                                                                  4. BUFFER SORT\u7684\u4f8b\u5b50: \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                                                                  5. INLIST ITERATOR:\n
                                                                      \n
                                                                    1. \u662f\u7531\u4e8eIN\u64cd\u4f5c\u5f15\u8d77\u7684,\u8981\u5173\u6ce8\u8fed\u4ee3\u7684\u6b21\u6570,\u4e00\u6b21\u8fed\u4ee3\u5c31\u8981\u6709\u4e00\u6b21\u8bbf\u95ee,\u5982\u679c\u6ca1\u6709\u7d22\u5f15\u53ef\u80fd\u6027\u80fd\u95ee\u9898\u4f1a\u5f88\u4e25\u91cd;<\/li>\n
                                                                    2. \u53ef\u4ee5\u4f7f\u7528UNION ALL\u64cd\u4f5c\u4ee3\u66ff;<\/li>\n
                                                                    3. INLIST ITERATOR\u7684\u4f8b\u5b50; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"\"\"<\/li>\n<\/ol>\n<\/li>\n
                                                                    4. \u89c6\u56fe\u7684\u64cd\u4f5c:\n
                                                                        \n
                                                                      1. Merge View:\u662f\u5c06View\u7684\u5b9a\u4e49\u548c\u5916\u90e8\u67e5\u8be2\u5408\u5e76,\u9ad8\u6548\u7684\u65b9\u5f0f;<\/li>\n
                                                                      2. No Merge View:\u5148\u5c06View\u7684\u6570\u636e\u53d6\u51fa\u6765\u518d\u505a\u5916\u90e8\u6761\u4ef6\u7684\u8fc7\u6ee4,\u6548\u7387\u4f4e;<\/li>\n<\/ol>\n<\/li>\n
                                                                      3. \u6267\u884c\u8ba1\u5212\u4e2d\u7684Count\u548cCount Stopkey:oracle\u6570\u636e\u5e93\u7684\u4f18\u5316\u5173\u4e8erownum\u64cd\u4f5c;\n
                                                                          \n
                                                                        1. \u5728\u67e5\u8be2\u4e2d\u6709\u65f6\u4f7f\u7528\u5230\u4f2a\u5217rownum,\u5bf9\u4f7f\u7528\u4f2a\u5217rownum\u7684\u67e5\u8be2,\u4f18\u5316\u5668\u8981\u4e48\u4f7f\u7528count\u64cd\u4f5c,\u8981\u4e48\u4f7f\u7528count stopkey\u64cd\u4f5c\u6765\u5bf9rownum\u8ba1\u6570\u5668\u8fdb\u884c\u589e\u91cf(\u6ce8\u610f:\u8fd9\u91cc\u7684count\u64cd\u4f5c\u548ccount stopkey\u64cd\u4f5c\u4e0ecount\u51fd\u6570\u6ca1\u6709\u4efb\u4f55\u5173\u7cfb).\u5982\u679c\u5bf9rownum\u4f2a\u5217\u5e94\u7528\u4e00\u4e2a\u9650\u5b9a\u6761\u4ef6,\u5982:where rownum<10,\u5219\u4f7f\u7528count stopkey\u64cd\u4f5c;\u5982\u679c\u4e0d\u4e3aRownum\u4f2a\u5217\u6307\u5b9a\u9650\u5b9a\u6761\u4ef6,\u5219\u662f\u4f7f\u7528count\u64cd\u4f5c;<\/li>\n
                                                                        2. \u4e0d\u5728Rownum\u4f2a\u5217\u4e0a\u4f7f\u7528\u9650\u5b9a\u6761\u4ef6:SELECT employee_id, ROWNUM FROM employees;(employee_id\u662f\u4e3b\u952e)\u4e3a\u4e86\u5b8c\u6210\u8fd9\u4e2a\u67e5\u8be2,\u4f18\u5316\u5668\u6267\u884c\u4e00\u4e2a\u5168\u7d22\u5f15\u626b\u63cf(\u4e3b\u952e\u7d22\u5f15),\u540e\u8ddf\u4e00\u4e2acount\u64cd\u4f5c\u751f\u6210\u6bcf\u4e2a\u884c\u7684rownum\u503c,count\u64cd\u4f5c\u4e0d\u9700\u8981\u7b49\u5f85\u5f97\u5230\u6574\u4e2a\u8bb0\u5f55\u96c6,\u968f\u7740\u4eceemployee\u8868\u4e2d\u8fd4\u56de\u8bb0\u5f55,rownum\u8ba1\u6570\u5668\u8fdb\u884c\u589e\u91cf,\u4ece\u800c\u786e\u5b9a\u6bcf\u4e2a\u8bb0\u5f55\u7684rownum;\"\"<\/li>\n
                                                                        3. \u5728rownum\u4f2a\u5217\u4e0a\u4f7f\u7528\u4e00\u4e2a\u9650\u5b9a:SELECT employee_id, ROWNUM FROM employees WHERE ROWNUM < 10;\u4e3a\u4e86\u5b9e\u65bd\u9650\u5b9a\u6761\u4ef6,\u4f18\u5316\u5668\u7528count stopkey\u64cd\u4f5c\u4ee3\u66ffcount\u64cd\u4f5c,\u5b83\u5c06rownum\u4f2a\u5217\u7684\u589e\u91cf\u503c\u4e0e\u9650\u5b9a\u6761\u4ef6\u4e2d\u6307\u5b9a\u7684\u503c\u8fdb\u884c\u6bd4\u8f83,\u5982\u679crownum\u4f2a\u5217\u7684\u503c\u5927\u4e8e\u9650\u5b9a\u6761\u4ef6\u4e2d\u6307\u5b9a\u7684\u503c,\u5219\u67e5\u8be2\u4e0d\u518d\u8fd4\u56de\u66f4\u591a\u7684\u884c;\"\"<\/li>\n
                                                                        4. \u5728where\u5b50\u53e5\u4e2d\u4e0d\u80fd\u4f7f\u7528rownum>10\u8fd9\u6837\u7684\u64cd\u4f5c,\u53ea\u80fd\u4f7f\u7528rownum<10\u8fd9\u6837\u7684\u64cd\u4f5c;<\/li>\n<\/ol>\n<\/li>\n
                                                                        5. Min\/Max and First Row\u64cd\u4f5c:\u5f53\u4f7f\u7528MAX\/MIN\u51fd\u6570\u65f6\u53d1\u751f; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                                        6. \u8fde\u63a5\u7684\u65b9\u5f0f:\n
                                                                            \n
                                                                          1. \u4e00\u4e2a\u8fde\u63a5\u5b9a\u4e49\u4e86\u4e24\u4e2a\u884c\u6e90\u7684\u5173\u7cfb,\u4e5f\u662f\u5408\u5e76\u4e24\u4e2a\u884c\u6e90\u95f4\u6570\u636e\u7684\u65b9\u6cd5;<\/li>\n
                                                                          2. \u4e3b\u8981\u7531\u8fde\u63a5\u7684\u8c13\u8bcd\u6240\u63a7\u5236,\u5b9a\u4e49\u4e86\u5bf9\u8c61\u95f4\u7684\u5173\u7cfb; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                                          3. \u8fde\u63a5\u7684\u65b9\u6cd5:\n
                                                                              \n
                                                                            1. Nested Loops:\n
                                                                                \n
                                                                              1. \u5bf9\u4e8e\u88ab\u8fde\u63a5\u7684\u6570\u636e\u5b50\u96c6\u8f83\u5c0f\u7684\u60c5\u51b5,\u5d4c\u5957\u5faa\u73af\u662f\u4e2a\u8f83\u597d\u7684\u9009\u62e9;<\/li>\n
                                                                              2. \u8fd4\u56de\u7b2c\u4e00\u6761\u8bb0\u5f55\u6700\u5feb\u7684\u65b9\u5f0f;<\/li>\n
                                                                              3. \u8fd9\u79cd\u60c5\u51b5\u4e0b,\u5185\u8868\u88ab\u5916\u8868\u9a71\u52a8,\u5916\u8868\u8fd4\u56de\u7684\u6bcf\u4e00\u884c\u90fd\u8981\u5728\u5185\u8868\u4e2d\u68c0\u7d22\u627e\u5230\u5b83\u5339\u914d\u7684\u884c,\u56e0\u6b64\u6574\u4e2a\u67e5\u8be2\u8fd4\u56de\u7684\u7ed3\u679c\u96c6\u4e0d\u80fd\u592a\u5927(eg:<1w);<\/li>\n
                                                                              4. \u8981\u628a\u8fd4\u56de\u5b50\u96c6\u8f83\u5c0f\u7684\u8868\u4f5c\u4e3a\u9a71\u52a8\u8868,\u800c\u4e14\u5185\u6807\u7684\u8fde\u63a5\u5b57\u6bb5\u4e0a\u4e00\u5b9a\u8981\u6709\u7d22\u5f15;<\/li>\n
                                                                              5. \u4f7f\u7528USE_NL(table_name1 table_name2)\u53ef\u662f\u5f3a\u5236CBO\u6267\u884c\u5d4c\u5957\u5faa\u73af\u8fde\u63a5;<\/li>\n<\/ol>\n<\/li>\n
                                                                              6. Sort-Merge Join:\n
                                                                                  \n
                                                                                1. \u901a\u5e38\u60c5\u51b5\u4e0b\u6563\u5217\u8fde\u63a5\u7684\u6548\u679c\u90fd\u6bd4\u6392\u5e8f\u5408\u5e76\u8fde\u63a5\u8981\u597d,\u7136\u800c\u5982\u679c\u884c\u6e90\u5df2\u7ecf\u88ab\u6392\u8fc7\u5e8f,\u5728\u6267\u884c\u6392\u5e8f\u5408\u5e76\u8fde\u63a5\u65f6\u4e0d\u9700\u8981\u518d\u6392\u5e8f\u4e86,\u8fd9\u65f6\u6392\u5e8f\u5408\u5e76\u8fde\u63a5\u7684\u6027\u80fd\u4f1a\u4f18\u4e8e\u6563\u5217\u8fde\u63a5;<\/li>\n
                                                                                2. \u53ef\u4ee5\u4f7f\u7528USE_MERGE(table_name1 table_name2)\u6765\u5f3a\u5236\u4f7f\u7528\u6392\u5e8f\u5408\u5e76\u8fde\u63a5;<\/li>\n
                                                                                3. Sort Merge join\u4f7f\u7528\u7684\u60c5\u51b5:\n
                                                                                    \n
                                                                                  1. \u7528\u5728\u6ca1\u6709\u7d22\u5f15;<\/li>\n
                                                                                  2. \u6570\u636e\u5df2\u7ecf\u6392\u5e8f\u7684\u60c5\u51b5;<\/li>\n
                                                                                  3. \u4e0d\u7b49\u4ef7\u5173\u8054;<\/li>\n
                                                                                  4. HASH_JOIN_ENABLED=FALSE;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                                                                  5. Hash Join:\n
                                                                                      \n
                                                                                    1. \u6563\u5217\u8fde\u63a5\u662fCBO\u505a\u5927\u6570\u636e\u96c6\u8fde\u63a5\u65f6\u5e38\u7528\u7684\u65b9\u5f0f,\u4f18\u5316\u5668\u4f7f\u7528\u4e24\u4e2a\u8868\u4e2d\u8f83\u5c0f\u7684\u8868(\u884c\u6e90)\u5229\u7528\u8fde\u63a5\u952e\u5728\u5185\u5b58\u4e2d\u5efa\u7acb\u6563\u5217\u8868,\u7136\u540e\u626b\u63cf\u8f83\u5927\u7684\u8868\u5e76\u63a2\u6d4b\u6563\u5217\u8868,\u627e\u51fa\u4e0e\u6563\u5217\u8868\u5339\u914d\u7684\u884c;<\/li>\n
                                                                                    2. \u8fd9\u79cd\u65b9\u5f0f\u9002\u7528\u4e8e\u8f83\u5c0f\u7684\u8868\u5b8c\u5168\u53ef\u4ee5\u653e\u4e8e\u5185\u5b58\u4e2d\u7684\u60c5\u51b5,\u8fd9\u6837\u603b\u6210\u672c\u5c31\u662f\u8bbf\u95ee\u4e24\u4e2a\u8868\u7684\u6210\u672c\u4e4b\u548c,\u4f46\u662f\u5728\u8868\u5f88\u5927\u7684\u60c5\u51b5\u4e0b\u5e76\u4e0d\u80fd\u5b8c\u5168\u653e\u5165\u5185\u5b58,\u8fd9\u65f6\u4f18\u5316\u5668\u4f1a\u5c06\u5b83\u5206\u5272\u6210\u82e5\u5e72\u4e0d\u540c\u7684\u5206\u533a,\u4e0d\u80fd\u653e\u5165\u5185\u5b58\u7684\u90e8\u5206\u5c31\u628a\u8be5\u5206\u533a\u5199\u5165\u78c1\u76d8\u7684\u4e34\u65f6\u6bb5,\u6b64\u65f6\u8981\u6709\u8f83\u5927\u7684\u4e34\u65f6\u6bb5\u4ece\u800c\u5c3d\u91cf\u63d0\u9ad8I\/O\u7684\u6027\u80fd;<\/li>\n
                                                                                    3. \u4e5f\u53ef\u4ee5\u7528USE_HASH(table_name1 table_name2)\u63d0\u793a\u6765\u5f3a\u5236\u4f7f\u7528\u6563\u5217\u8fde\u63a5,\u5982\u679c\u4f7f\u7528\u6563\u5217\u8fde\u63a5HASH_AREA_SIZE\u521d\u59cb\u5316\u53c2\u6570\u5fc5\u987b\u8db3\u591f\u7684\u5927,\u5982\u679c\u662f10g\u4ee5\u540e,Oracle\u5efa\u8bae\u4f7f\u7528SQL\u5de5\u4f5c\u533a\u81ea\u52a8\u7ba1\u7406,\u8bbe\u7f6eWORKAREA_SIZE_POLICY \u4e3aAUTO,\u7136\u540e\u8c03\u6574PGA_AGGREGATE_TARGET\u5373\u53ef;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                                                                    4. \u8fde\u63a5\u65b9\u5f0f\u7684\u6bd4\u8f83:\n
                                                                                        \n
                                                                                      1. Hash join\u7684\u5de5\u4f5c\u65b9\u5f0f\u662f\u5c06\u4e00\u4e2a\u8868(\u901a\u5e38\u662f\u5c0f\u4e00\u70b9\u7684\u90a3\u4e2a\u8868)\u505ahash\u8fd0\u7b97,\u5c06\u5217\u6570\u636e\u5b58\u50a8\u5230hash\u5217\u8868\u4e2d,\u4ece\u53e6\u4e00\u4e2a\u8868\u4e2d\u62bd\u53d6\u8bb0\u5f55,\u505ahash\u8fd0\u7b97,\u5230hash\u5217\u8868\u4e2d\u627e\u5230\u76f8\u5e94\u7684\u503c,\u505a\u5339\u914d;<\/li>\n
                                                                                      2. Nested loops\u5de5\u4f5c\u65b9\u5f0f\u662f\u4ece\u4e00\u5f20\u8868\u4e2d\u8bfb\u53d6\u6570\u636e,\u8bbf\u95ee\u53e6\u4e00\u5f20\u8868(\u901a\u5e38\u662f\u7d22\u5f15)\u6765\u505a\u5339\u914d,nested loops\u9002\u7528\u7684\u573a\u5408\u662f\u5f53\u4e00\u4e2a\u5173\u8054\u8868\u6bd4\u8f83\u5c0f\u7684\u65f6\u5019,\u6548\u7387\u4f1a\u66f4\u9ad8;<\/li>\n
                                                                                      3. Merge Join \u662f\u5148\u5c06\u5173\u8054\u8868\u7684\u5173\u8054\u5217\u5404\u81ea\u505a\u6392\u5e8f,\u7136\u540e\u4ece\u5404\u81ea\u7684\u6392\u5e8f\u8868\u4e2d\u62bd\u53d6\u6570\u636e,\u5230\u53e6\u4e00\u4e2a\u6392\u5e8f\u8868\u4e2d\u505a\u5339\u914d,\u56e0\u4e3amerge join\u9700\u8981\u505a\u66f4\u591a\u7684\u6392\u5e8f,\u6240\u4ee5\u6d88\u8017\u7684\u8d44\u6e90\u66f4\u591a,\u901a\u5e38\u6765\u8bb2,\u80fd\u591f\u4f7f\u7528merge join\u7684\u5730\u65b9,hash join\u90fd\u53ef\u4ee5\u53d1\u6325\u66f4\u597d\u7684\u6027\u80fd,Merge Join\u592a\u6d88\u8017PGA;<\/li>\n<\/ol>\n<\/li>\n
                                                                                      4. \u8fde\u63a5\u7684\u7c7b\u578b:\n
                                                                                          \n
                                                                                        1. [\u4e0d]\u7b49\u503c\u8fde\u63a5\u548c\u81ea\u7136\u8fde\u63a5;<\/li>\n
                                                                                        2. \u5916\u8fde\u63a5:\u5168\u8fde\u63a5,\u5de6\u5916\u8fde\u63a5,\u53f3\u5916\u8fde\u63a5;(\u5916\u8fde\u63a5:+\u53f7\u653e\u90a3\u8fb9,\u54ea\u8fb9\u8bb0\u5f55\u5c11;)<\/li>\n
                                                                                        3. \u534a\u8fde\u63a5:EXISTS\u5b50\u53e5;<\/li>\n
                                                                                        4. \u53cd\u8fde\u63a5:NOT IN\u5b57\u53e5;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                                                                        5. \u591a\u884c\u6e90\u7684\u64cd\u4f5c\n
                                                                                            \n
                                                                                          1. FILTER;<\/li>\n
                                                                                          2. CONCATENATION;<\/li>\n
                                                                                          3. UNION [ALL]<\/li>\n
                                                                                          4. INTERSECT;<\/li>\n
                                                                                          5. MINUS;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/div>\n
                                                                                            <\/div>\n
                                                                                            — Full Table Scan;<\/div>\n
                                                                                            SELECT * FROM departments WHERE manager_id = 100;<\/div>\n
                                                                                            SELECT \/*+ PARALLEL(d 4) *\/ * FROM departments d;<\/div>\n
                                                                                            SELECT * FROM departments d WHERE department_id = 10;
                                                                                            \nSELECT \/*+ FULL (d)*\/ * FROM departments d WHERE department_id = 10;<\/div>\n
                                                                                            <\/div>\n
                                                                                            — ROWID Sacn;<\/div>\n
                                                                                            SELECT * FROM departments WHERE ROWID = ‘AAAMiZAAFAAAAA4AAI’;
                                                                                            \nSELECT * FROM departments WHERE ROWID = (
                                                                                            \nSELECT rowid FROM departments\u00a0 WHERE manager_id = 100);<\/div>\n
                                                                                            <\/div>\n
                                                                                            — \u51fd\u6570\u7d22\u5f15\u7684\u4f8b\u5b50;<\/div>\n
                                                                                            CREATE INDEX idx_employees_fun_firstname ON employees (upper(first_name));
                                                                                            \nSELECT * FROM user_tab_cols WHERE table_name = ‘EMPLOYEES’;
                                                                                            \nSELECT * FROM user_ind_expressions WHERE index_name = ‘IDX_EMPLOYEES_FUN_FIRSTNAME’;<\/div>\n
                                                                                            <\/div>\n
                                                                                            — Index Skip Scan\u7684\u4f8b\u5b50;
                                                                                            \nCREATE TABLE skip_test AS
                                                                                            \nSELECT object_id, object_name, decode(object_type, ‘VIEW’, ‘VIEW’, ‘TABLE’) AS object_flag, object_type
                                                                                            \nFROM dba_objects WHERE ROWNUM <= 3000;
                                                                                            \nCREATE INDEX idx_skip_test ON skip_test(object_flag, object_id);
                                                                                            \nEXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
                                                                                            \nSELECT * FROM skip_test WHERE object_id = 100;
                                                                                            \n— \u5982\u679c\u8054\u5408\u7d22\u5f15\u7b2c\u4e00\u5217\u7684\u5019\u9009\u503c\u592a\u591a,\u5219\u53d1\u751f\u5168\u8868\u626b\u63cf;
                                                                                            \nDROP INDEX idx_skip_test;
                                                                                            \nCREATE INDEX idx_skip_test ON skip_test(object_type, object_id);
                                                                                            \nEXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
                                                                                            \nSELECT * FROM skip_test WHERE object_id=100;<\/div>\n
                                                                                            <\/div>\n
                                                                                            — \u4f4d\u56fe\u7d22\u5f15\u7684\u4f8b\u5b50;<\/div>\n
                                                                                            CREATE TABLE bitmap_test AS
                                                                                            \nSELECT ROWNUM rn, MOD(ROWNUM, 4) bit,
                                                                                            \nCASE MOD(ROWNUM, 2) WHEN 0 THEN ‘M’ ELSE ‘F’ END gender
                                                                                            \nFROM dual CONNECT BY ROWNUM < 1000;
                                                                                            \nCREATE BITMAP INDEX bmp_bitmap_bit on bitmap_test (bit);
                                                                                            \nCREATE BITMAP INDEX bmp_bitmap_gender on bitmap_test (gender);
                                                                                            \nEXEC dbms_stats.gather_table_stats(USER, ‘bitmap_test’, CASCADE => TRUE);
                                                                                            \nALTER SESSION optimizer_mode = ‘FIRST_ROWS_1000’;
                                                                                            \nSELECT * FROM bitmap_test WHERE bit = 3;
                                                                                            \nSELECT * FROM bitmap_test WHERE bit > 2;
                                                                                            \nSELECT * FROM bitmap_test WHERE bit IN (2, 3);
                                                                                            \nSELECT * FROM bitmap_test WHERE bit IN (2, 3) AND gender = ‘M’;<\/div>\n","protected":false},"excerpt":{"rendered":"

                                                                                            Optimizer operators \u884c\u6e90\u64cd\u4f5c Unary Operations:\u4e00\u5143\u8fd0\u7b97,\u5373\u5355\u8868\u7684\u67e5\u8be2; […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[8],"class_list":["post-292","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-performance"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=292"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/292\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=292"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}