ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle如何选择优化器

Oracle如何选择优化器

原创 Linux操作系统 作者:hjgo5 时间:2019-05-21 19:06:06 0 删除 编辑

Metalink 66484.1

                                                QUERY IN
                                                                    |
                                                                    |
                                                                   /
                             -------------------------------------------------------
                            |     Is there a hint for table access,            |
                            |     an ORDERED, FIRST_ROWS or         |
                            |             ALL_ROWS hint ?                       |
                            |                                                                   |   yes
                            | 7.3+ Does any table have PARALLEL    |-------> COST
                            |          set ?                                                  |
                            |                                                                   |
                            | 8.0+ As above and additionally,             |
                            |          Are there any:                                 |
                            |          Index Organised Tables (IOT)        |
                            |          or                                                      |
                            |          Partition Tables involved ?             |
                            |                                                                   |
                            | 8.1+ As above and additionally,             |
                            |          Are there any:                                 |
                            |          Domain Indexes (Inter Media)       |
                            |          or                                                      |
                            |          Parallelism set on Indexes               |
                            |          See [NOTE:70008.1]                        |
                            |          or                                                      |
                            |          Parallel instances set on                  |
                            |          an object                                           |
                            |          or                                                      |
                            |          Sample clauses                                 |
                            |          (Datawarehousing feature)            |
                            |          or                                                      |
                            |          Parallel CTAS                                  |
                            |          or                                                      |
                            |          Function-based indexes                  |
                            |          or                                                      |
                            |          Reverse Key Indexes                       |
                            |          or                                                      |
                            |          Query rewrite                                  |
                            |          (materialised views)                        |
                             --------------------------------------------------------
                                                                |
                                                                | no
                                                                |
                             ---------------------------------------------------------    yes
                            |                  Is there a RULE hint ?               |-------> RULE
                              -------------------------------------------------------- 
                                                                 |
                                                                 | no
                                                                 |
                            ----------------------------------------------------------
                            | Is RULE set at SYSTEM or SESSION        |  yes
                            |        level and no CHOOSE hint ?             |-------> RULE
                             ---------------------------------------------------------
                                                                  |
                                                                  | no
                                                                  |
                            ----------------------------------------------------------
                            | Is ALL_ROWS or FIRST_ROWS set at       |  yes
                            |     SYSTEM or SESSION level and no         |-------> COST
                            |     CHOOSE hint ?                                       |
                            ---------------------------------------------------------
                                                                   |
                                                                   | no
                                                                   |
                            ----------------------------------------------------------
                            | Are there statistics for any of                      |  yes
                            | the tables ?                                                   |-------> COST
                             ---------------------------------------------------------
                                                                    |
                                                                    | no
                                                                    |
                                                                 RULE

注意:

(1) Hints in view definitions or in the SELECT clause of an INSERT or

CREATE TABLE are included for the flowchart.

 

(2) Recursive SQL choose the goal base on the instance setting.

This includes SQL called from PL/SQL. Of course hints

can be used in any SQL called by PL/SQL to override this default.

  Instance Recursive
-------------------- --------------
RULE RULE
FIRST_ROWS CHOOSE
ALL_ROWS CHOOSE
CHOOSE CHOOSE

 

(3) If any table in a query has a parallel degreee greater than one

(including the default degree), Oracle uses the cost-based optimizer

for that query--even if OPTIMIZER_MODE = RULE, or if there is a RULE

hint in the query itself

 

(4) In general, be wary of the prescence of any new features as they are

likely to require the use of the Cost Based Optimizer.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/266281/viewspace-495481/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 如何使用hint
下一篇: Explain的使用
请登录后发表评论 登录
全部评论

注册时间:2005-03-03

  • 博文量
    58
  • 访问量
    42850