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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 ?