Home » RDBMS Server » Performance Tuning » optimize query (10g)
optimize query [message #353490] Tue, 14 October 2008 00:56 Go to next message
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}
Re: optimize query [message #353503 is a reply to message #353490] Tue, 14 October 2008 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: optimize query [message #353509 is a reply to message #353505] Tue, 14 October 2008 01:23 Go to previous messageGo to next message
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.
Re: optimize query [message #353664 is a reply to message #353490] Tue, 14 October 2008 09:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: optimize query [message #354133 is a reply to message #353664] Thu, 16 October 2008 09:24 Go to previous message
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.
Previous Topic: Getting Plan of SQL query executed in the past (merged)
Next Topic: tuning a view
Goto Forum:
  


Current Time: Tue Dec 06 08:29:58 CST 2016

Total time taken to generate the page: 0.29978 seconds