OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » optimize query (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

optimize query [message #353490]

Tue, 14 October 2008 00:56

ora_baby
Messages: 89
Registered: May 2008

Member

hi all

i have report with query

select col0, col3*col4 col6, col3+col4+col5 col7
from (
    select 
       case when col1>10 then 1 else 0 col3, 
       case when col2>20 then 1 else 0 col4,
       col*col2 col5,
       col0
    from (
       select 
         case when A then 1 else 0 col1, 
         case when B then 1 else 0 col2
         col0
       from table_T
          )
      )

i think it is enough for understand problem

for query:
total CPU =11,24; disk=33505; query=83050
and many time for execute

do you have any ideas for optimize this query?

{ps. sorry for my language}

Report message to a moderator

Re: optimize query [message #353503 is a reply to message #353490]

Tue, 14 October 2008 01:08

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is optimized yet (but there is a syntax error); I think it is enough as an answer.

Regards
Michel

[Updated on: Tue, 14 October 2008 01:08]

Report message to a moderator

Re: optimize query [message #353505 is a reply to message #353490]

Tue, 14 October 2008 01:14

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I am sorry, but there seems little to be done to optimize your query. I believe however that you are incorrect. I suspect you have actually stated your problem wrongly. I say this because once the Oracle Optimizer has finished its query merging step in query plan generation, your query is essentially this query:

select somestuff from table1
/

How is this not already optimized?

If you really want to tune this particular query then remove the expressions

Quote:

case when col1>10 then 1 else 0 col3,
case when col2>20 then 1 else 0 col4,

and replace them with these expressions

Quote:

0 col3,
0 col4,

since col1 will never be >10 and col2 will never be >20, why bother going through to overhead of CASE here.

And now you see the uselessness of posting fake code that you think is "sufficient to understand the problem". Try posting some real code with real problem description please.

If you do not understand what I means when I say "QUERY MERGING" then do a google for it and read up on it. Oracle will "compress" your query by collapsing layers of sql when possible to reduce the complexity of the sql to a minimal form. The minimal form of your query is what I posted above.

Good luck, Kevin

Report message to a moderator

Re: optimize query [message #353509 is a reply to message #353505]

Tue, 14 October 2008 01:23

ora_baby
Messages: 89
Registered: May 2008

Member

This query is approximate.
Main idea of this query to show hierarchy of query and many function such as case, sum.
Really this query other (very large) and it have many condition.

Report message to a moderator

Re: optimize query [message #353664 is a reply to message #353490]

Tue, 14 October 2008 09:05

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Yes, I understand you intent was to show the query nesting, and the use of case expressions.

Again however, I can only say that the nesting is handled by query transformation such that what you show, is really reducable to what I provided. Thus there is nothing left to do to optimize the query from a simple query construction perspective.

Kevin

Report message to a moderator

Re: optimize query [message #354133 is a reply to message #353664]

Thu, 16 October 2008 09:24

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If the lowest level of Case statements depend solely on the values returned from table T, then you might get a slight performance increase by creating a function based index to return those CASE expressions.
Of course, it's possible that you have a WHERE clause that would prevent this from happening, but you chose not to share that information with us.

Report message to a moderator

Previous Topic:	Getting Plan of SQL query executed in the past (merged)
Next Topic:	tuning a view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 04:21:58 CDT 2024