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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 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
|
|
|
|
|
Goto Forum:
Current Time: Sun Aug 18 21:46:04 CDT 2024
|