Home » RDBMS Server » Performance Tuning » Tuning Error with Order by clause (10.2.0.3, Solaris64)
Tuning Error with Order by clause [message #318838] Thu, 08 May 2008 03:10 Go to next message
arvulis
Messages: 3
Registered: May 2008
Junior Member
Hello,

My instance is in 10.2.0.3 on solaris 64bits. When I use SQL TUNING ADVISOR on one query, I have this problem...

- 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


my query is like :

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; 



What's your opinion about that ?
Re: Tuning Error with Order by clause [message #318850 is a reply to message #318838] Thu, 08 May 2008 03:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This is telling you that the following inline view must be COMPLETELY evaluated before it can be joined to mm_newsitem.
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)

If mm_newsitem is going to join to every row in the inline view, then it's not a problem. But if mm_newsitem only matches a small proportion of the inline view, then it is not possible to evaluate just the matching rows from the inline view; the entire thing still needs to be evaluated in full before non-matching rows can be filtered out.

Ross Leishman
Re: Tuning Error with Order by clause [message #318894 is a reply to message #318838] Thu, 08 May 2008 07:06 Go to previous messageGo to next message
arvulis
Messages: 3
Registered: May 2008
Junior Member
Thanks for your fast reply !
What do you means by "evaluated" and "filtered out" ?
Could a view resolve the problem ?
Re: Tuning Error with Order by clause [message #319495 is a reply to message #318894] Sun, 11 May 2008 22:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No. A view will not help.

Instead of joining mm_newsitem to the inline-view select of mm_newsitem, why don't you just select all of the columns you want for the inline-view?

Ross Leishman

[Updated on: Sun, 11 May 2008 22:25]

Report message to a moderator

Previous Topic: Problem on understanding cardinality of index
Next Topic: Reorganizing Vs Rebuilding Indexes
Goto Forum:
  


Current Time: Sun Dec 11 08:12:37 CST 2016

Total time taken to generate the page: 0.07848 seconds