OraFAQ Forum: Performance Tuning » Rule based tips still valid in 11g

Home » RDBMS Server » Performance Tuning » Rule based tips still valid in 11g

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

Rule based tips still valid in 11g [message #460481]

Fri, 11 June 2010 07:25

ind9
Messages: 65
Registered: January 2009

Member

Hi,
We are using Oracle 11g database release 2. I would like to know still the rule based tips will improve the performance as i heard all these are taken care in Oracle 10g itself. Some of the Tips i've provided below could you please share me information on this.

Queries like
select count(*) from emp
select count(1) from emp

OR placing driving tables at last

OR the below provided codes

[b]Total CPU = 156 sec[/b]
   
SELECT  ...                                                                       FROM emp E
WHERE emp_salary > 50000
      AND emp_type = 'MANAGER'
      AND 25 < (SELECT  count(*)
                              FROM emp
                           WHERE  emp_mgr = e.empno) 
 
[b]Total CPU = 10 sec[/b]
   
SELECT  ...                                                                       FROM emp E
WHERE   25 < (SELECT  count(*)
                              FROM emp
                           WHERE  emp_mgr = e.empno) 
      AND	emp_salary > 50000
      AND emp_type = 'MANAGER'

Thanks in advance

Report message to a moderator

Re: Rule based tips still valid in 11g [message #460487 is a reply to message #460481]

Fri, 11 June 2010 07:38

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your question is fairly vague - what do you mean by "rule based tips"?

Count(1) gets rewritten to count(*) internally by oracle so the two will perform the same. Count(*) is the semantically correct one to use.

Order of tables in the from shouldn't make any difference.

Order of predicates in the where clause shouldn't make any difference.

Report message to a moderator

Previous Topic:	Oracle Internal Query
Next Topic:	tkprof question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 07:36:45 CST 2024