Tuning Advisor : Erreur avec l'utilisation de Order by

[Arvulis]

Bonjour à tous,

je suis en 10.2.0.3 sur du solaris 64bits et en faisant du tuning sur une requete, j'obtiens ceci :

Code :

1
2
3
4
5
6
7
 
- The optimizer could NOT merge the VIEW at line ID 6 of the execution plan.
  The optimizer cannot merge a VIEW that contains an "ORDER BY" clause unless
  the statement IS a "DELETE" OR an "UPDATE" AND the parent query IS the top
  most query IN the statement.
- The optimizer could NOT merge the VIEW at line ID 4 of the execution plan.
  The optimizer cannot merge a VIEW that contains "ROWNUM" pseudo COLUMN

Avec une requete du style :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT numberofnewsbydomain (10, 'france', '1,2,3,4,5,6,7,8,9,11') AS ID, 
       NULL AS title, NULL AS summary, NULL AS published_on, 0 AS weight, 
       0 AS id_language, NULL AS code_language, 0 AS id_provider, 
       NULL AS code_provider, NULL AS updated_on 
  FROM DUAL 
UNION --ALL 
SELECT selectednews.ID AS ID, nn1.title AS title, nn1.summary AS summary, 
       nn1.published_on AS published_on, nn1.weight AS weight, 
       nn1.id_language AS id_language, 
       (SELECT ll.code 
          FROM mm_language ll 
         WHERE ll.ID = nn1.id_language) AS code_language, 
       nn1.id_provider AS id_provider, 
       (SELECT pp.code 
          FROM mm_newsprovider pp 
         WHERE pp.ID = nn1.id_provider) AS code_provider, 
       nn1.updated_on AS updated_on 
  FROM mm_newsitem nn1 
       JOIN 
       (SELECT a.* 
          FROM (SELECT /*+ FIRST_ROWS +*/ 
                       ROWNUM rnum, a.* 
                  FROM (SELECT   /*+ INDEX_COMBINE(NN) */ 
                                 nn.ID AS ID 
                            FROM mm_newsitem nn 
                           WHERE contains (nn.text_concat, '(usa)') > 0 
                             AND nn.id_provider IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 11) 
                        ORDER BY nn.published_on DESC) a 
                 WHERE ROWNUM <= 1124) a 
         WHERE rnum >= 15 OR rnum <= 4) selectednews ON selectednews.ID = nn1.ID;

Quelqu'un aurais t'il un avis ?

[mnitu]

Bref, il dit qui lui est impossible de faire le "view merging" à cause de la clause Order By or due à l’utilisation de la pseudo colonne RowNum

13.4.1.1.1 View Merging
Each view referenced in a query is expanded by the parser into a separate query block. The query block essentially represents the view definition, and therefore the result of a view. One option for the optimizer is to analyze the view query block separately and generate a view subplan. The optimizer then processes the rest of the query by using the view subplan in the generation of an overall query plan. This technique usually leads to a suboptimal query plan, because the view is optimized separately from rest of the query.

The query transformer then removes the potentially suboptimal plan by merging the view query block into the query block that contains the view. Most types of views are merged. When a view is merged, the query block representing the view is merged into the containing query block. Generating a subplan is no longer necessary, because the view query block is eliminated.