轉載大師的讀書筆記 2

 

 

Optimizer Operations

How the Optimizer Performs Operations
    How the CBO Evaluates IN-List Iterators
        當 IN clause 指定了特定的值，並且在該列上有索引，最佳化器選擇 IN-list iterator。如果有多個 OR clauses 使用相同的索引，那麼最佳化器選擇更高效的 IN-list iterator，而不使用 CONCATENATION or UNION ALL。
   
    How the CBO Evaluates Concatenation
        當不同的條件使用 OR clause 連線起來，並且不同的條件都可以透過不同的索引生成較好的執行計劃，那麼 Concatenation 是很有用的。
        HINT：
          USE_CONCAT
          NO_EXPAND 會禁止使用 Concatenation，他其實是阻止 QUERY 擴充套件為多個 QUERY
        當一下情況時不要使用 Concatenation：
          1.OR conditions 在同一個列上，可以使用 IN-list，後者更高效。
          2.每一個 concatenation 都重複昂貴的步驟。
     
      How the CBO Evaluates Remote Operations
          影響執行計劃的因素：
            1.Network round trips 比物理和邏輯 I/Os 昂貴幾個數量級
            2.如果遠端資料庫不是 Oracle 資料庫，最佳化器無法獲得遠端資料庫的任何 statistics
          一般來說，最佳化器在訪問本地表之前先訪問遠端表
   

      How the CBO Executes Distributed Statements
          1.如果 SQL 語句中的所有表來自同一個遠端資料庫，Oracle 把語句傳送給遠端資料庫，遠端資料庫執行完之後把結果發還給本地資料庫。
          2.如果表來自不同的資料庫，Oracle 把語句拆分，每一個部分訪問單個資料庫上的表，把他們分別傳送給各資料庫，各資料庫執行自己部分的語句，並把結果發還給本地資料庫，本地資料庫再執行語句的其餘處理部分。
   
          如果是 CBO，最佳化器會考慮遠端資料庫上的索引，就像本地資料庫一樣，還會考慮遠端的 statistics，此外，在估計訪問的 cost 時，還會考慮資料的位置，比如遠端的一個全表掃描比本地相同表的全表掃描估計的 cost 要高。
          對於 RBO，最佳化器不會考慮遠端資料庫上的索引。
   
      How the CBO Executes Sort Operations
          SORT UNIQUE
              如果使用了 DISTINCT clause 或者 unique values 在下一步中需要，就會發生 SORT UNIQUE
     
          SORT AGGREGATE
              SORT AGGREGATE 實際上不發生 sort，他使用於對整個 set of rows 進行聚合計算。
     
          SORT GROUP BY
              SORT GROUP BY 用於對不同組上的資料進行聚合計算，這種情況下 sort 是需要的，sort 用於將行拆分成不同的組。
     
          SORT JOIN
              在 SORT MERGE JOIN 中，如果資料需要根據 join key 排序，就會發生 SORT JOIN。
     
          SORT ORDER BY
              當語句中使用 ORDER BY，並且沒有任何索引適合這種排序方式，那麼 SORT ORDER BY 就需要。
     
      How the CBO Executes Views
          以下情況 CBO 產生 VIEW：
              1.語句中有沒有被 decomposed 的 View
              2.語句中有 temporary or inline view 被使用
     
      How the CBO Evaluates Constants
          常量的計算只在語句被最佳化時執行一次，而不是每次語句被執行的時候。
          比如：salary > 24000/12 會被最佳化器簡化為 salary > 2000
          最佳化器不會跨過比較符簡化表示式，salary*12 > 24000 不能被簡化為 salary > 2000，因此寫語句時應儘量用常量和列作比較，而不要將列作計算之後再去比較。
   
      How the CBO Evaluates the UNION and UNION ALL Operators
          對於將 OR clauses 組合為一個複合語句，或者將一個複雜語句分解為包含簡單 select 語句的複合語句很有用，他們更易於最佳化和理解。
          就和 concatenation 一樣，如果 UNION ALL 重複了昂貴的操作，就不應該使用。
   
      How the CBO Evaluates the LIKE Operator
          對於沒有萬用字元的 like 條件，最佳化器會將他簡化為等於操作
              last_name LIKE 'SMITH' --&gt> last_name = 'SMITH'
          但這種簡化只能用於變長的型別，對於固定長度的，比如 CHAR(10) 就不能簡化，因為等於操作遵循 blank-padded semantics，而 like 不是（此規則只適合 9i 以上）。
              CREATE TABLE ducks (f CHAR(6), v VARCHAR2(6));
              INSERT INTO ducks VALUES ('DUCK', 'DUCK');
              commit;
              select * from ducks where f='DUCK';         <              F            V
              ------------ ------
              DUCK         DUCK
              select * from ducks where f like 'DUCK';    <              no rows selected
              以上結果在 9i 上有效，8i 下兩者都返回行
     
      How the CBO Evaluates the IN Operator
          最佳化器將 IN comparison operator 條件擴充套件為等價的 equality comparison operators and OR logical operators 條件
   
      How the CBO Evaluates the ANY or SOME Operator
          1.列表
                最佳化器將其擴充套件為等價的 comparison operators and OR logical operators 條件
                   salary > ANY (:first_sal, :second_sal) -> salary > :first_sal OR salary > :second_sal
          2.子查詢
                最佳化器將其轉化為等價的 EXISTS operator and a correlated subquery 條件
                   x > ANY (SELECT salary
                        FROM employees
                        WHERE job_id = 'IT_PROG')
                變為
                   EXISTS (SELECT salary
                     FROM employees
                     WHERE job_id = 'IT_PROG'
                     AND x > salary)
             
      How the CBO Evaluates the ALL Operator
          1.列表
                最佳化器將其擴充套件為等價的 comparison operators and AND logical operators 條件
          2.子查詢
                最佳化器將其轉化為等價的 ANY comparison operator and a complementary comparison operator 條件
                  x > ALL (SELECT salary
                      FROM employees
                      WHERE department_id = 50)
                變為
                  NOT (x <= ANY (SELECT salary
                        FROM employees
                        WHERE department_id = 50) )
              然後再進一步根據 ANY Operator 的轉換規則再將其轉換：
                  NOT EXISTS (SELECT salary
                        FROM employees
                        WHERE department_id = 50
                        AND x <= salary)
               
      How the CBO Evaluates the BETWEEN Operator
          最佳化器總是將 BETWEEN 條件用 >= and <= 條件來代替
   
      How the CBO Evaluates the NOT Operator
          最佳化器使用除去 NOT logical operator 並使用相反的 comparison operator 代替原來 comparison operator 的方法來簡化條件，使 NOT logical operator 消除。
          最佳化器會將 NOT 傳遞到子條件中，以便儘可能的簡化條件，即使子條件中會產生更多的 NOTs：
              NOT (salary < 1000 OR commission_pct IS NULL)
          =>
              NOT salary < 1000 AND commission_pct IS NOT NULL
          =>
              salary >= 1000 AND commission_pct IS NOT NULL
     
      How the CBO Evaluates Transitivity
          如果兩個條件涉及到同一個 column，且這個 column 的其中一個條件是和 constant expressions 進行比較，那麼有時候最佳化器會推斷出一個條件，這個推斷的條件可以使用 index access path，而原始的條件卻不能使用：
              WHERE column1 comp_oper constant
                AND column1 = column2
          其中：
              comp_oper 為任何比較操作：=, !=, ^=, , >, <=, or >=
              constant 為任何常量表示式（不能為其他 column）：SQL functions, literals, bind variables, and correlation variables
          這時，最佳化器會推斷一個條件：
              column2 comp_oper constant
          如果 column2 上有索引，就能使用該索引
          注：Transitivity 只用於 CBO
   
      How the CBO Optimizes Common Subexpressions
          公共的子表示式最佳化是一種啟發式的最佳化，可以鑑別、移出、收集在各 disjunctive (OR) branches 中的公共子表示式，絕大數情況下，可以減少 join 的數量。
          在滿足一下情況時，可使用公共子表示式最佳化（從最頂層至最內層的順序）：
              1.頂層條件是一個 disjunction（幾個以 or 連線的條件）
              2.每個 or 分支中是 simple predicate 或者 a conjunction（幾個以 and 連線的條件）
              3.每個 and 分支中是 simple predicate 或者 a disjunction of simple predicates
              4.表示式在每個 or 分支中都出現，即公共子表示式
                simple predicate 只不含有 AND or OR 連線的條件
           滿足以上條件的公共子表示式，最佳化器會將其移到最頂層，去除重複，再和被移去公共子表示式的原 disjunction 做 conjunction，這樣可以減少 join 操作。
   
      How the CBO Evaluates DETERMINISTIC Functions
          某些情況，最佳化器不需要計算 user-written function 的值，而用以前計算的值來代替他。
          這種 function 必須有一定的限制：
              1.Function 的返回值不能隨著 package variables、database、session parameters 的不同而改變
              2.如果 function 被重定義了，那麼他的返回值和以前的要保持一致
              3.使用預計算結果代替執行 function 必須沒有重大副作用
          使用 DETERMINISTIC 關鍵字建立的 function 告訴 Oracle 該 function 滿足以上限制，Oracle 不會去檢查該 function，即使 function 很明顯不滿足以上限制，因此，程式設計師應負責檢查以上限制，只有滿足了才能加 DETERMINISTIC 關鍵字。
   
   
How the Optimizer Transforms SQL Statements
      How the CBO Transforms ORs into Compound Queries
          如果一個查詢包含多個用 OR 連線的條件，最佳化器會將其轉換為用 UNION ALL 連線的混合查詢，如果轉換後的語句執行更加高效
              1.如果每個條件都可以單獨使用 index access path，那麼可以轉換。最佳化器將轉換後的語句生成執行計劃，他們用不同的索引多次訪問表，並把結果放到一起
              2.如果任何一個條件使用 full table scan，那麼不會轉換，因為一個 full table scan 和幾個 index scan 結合的效率不如直接使用一個 full table scan 效率高
              3.對於 CBO，最佳化器會估計並比較轉換前後的語句所需的 COST，並決定是否轉換
              4.CBO 不會轉換 IN-lists 或者條在在同一列上的 OR 操作，而使用 INLIST iterator operator
     
      How the CBO Unnests Subqueries
          最佳化複雜語句，最佳化器選擇下面兩種方法的其中一種：
              1.如果複雜語句可以轉換為等同的 join 語句，那麼先轉換，再最佳化 join 語句，這樣可以利用 join 語句的優勢
              2.如果無法轉換，那麼最佳化複雜語句本身
                 子查詢包含 aggregate functions，比如 AVG 的複雜語句，無法轉換為 join 語句
       
      How the CBO Merges Views
          merge view 的方法：
                在語句中將 view 的名字用 view 基表的名字代替，將 view 中的條件加到語句的條件中去
      
          Mergeable and Nonmergeable Views
              merge 只適合 select-project-join views，即 view 中只包含 selections, projections, and joins
              如果 view 中包含以下元素，不能 merge：
                1.Set operators (UNION, UNION ALL, INTERSECT, MINUS) 
                2.A CONNECT BY clause 
                3.A ROWNUM pseudocolumn 
                4.Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list 
              當 view 中包含以下元素時，只有 Complex View Merging is enabled 的時候才能 merge：
                1.A GROUP BY clause 
                2.A DISTINCT operator in the select list 
              如果 view 中包含多個基表，那麼當該 view 在 outer join 語句的右邊時，無法 merge，如果只有一個基表，那麼可以使用 complex view merging，即使 an expression in the view can return a nonnull value for a NULL.
              如果一個查詢語句包含 CURSOR expression，那麼即使 view 可以被正常 merge 最佳化器也不會將它 merge
      
      Complex View Merging
          如果 complex view merging is enabled 的，那麼包含 GROUP BY clause or DISTINCT 的 view 可以被 merge
          Complex merging 還可以用於 merge an IN subquery，只要 subquery 是不相關的
          Complex merging 不是 cost-based 的，必須用 OPTIMIZER_FEATURES_ENABLE or the MERGE hint 開啟才能使用，如果沒有 hint or parameter setting，最佳化器會使用另外一種方法：pushing predicates
      
      How the CBO Pushes Predicates
          最佳化器可以將訪問 nonmergeable view 的 query block 透過 pushing the query block's predicates inside the view's query 來轉換
    
      How the CBO Applies an Aggregate Function to the View
          最佳化器可以轉換包含 aggregate function 的 query，透過將 aggregate function 應用到 view's query
      
      How the CBO Executes Views in Outer Joins
          如果 view 在一個 outer join 的右邊： 
              1.如果 view 只有一個基表，最佳化器使用 view merging
              2.如果 view 有多個基表，最佳化器可以 push the join predicate into the view
        
      How the CBO Accesses the View's Rows with the Original Statement
          如果最佳化器無法將訪問 view 的語句轉換為等價的訪問基表的語句，比如 view 中包含 ROWNUM pseudocolumn，view 不能被 merge，也不能 push predicate，那麼 Oracle 執行 view 中的語句，獲得結果集，original statement 將其當作一個表來處理。
      
      How the CBO Executes Compound Queries
          為了為混合查詢選擇執行計劃，最佳化器首先為混合查詢的每個部分選擇執行計劃，然後用 set operator 聯合各 resulting row sources 

轉引自：