OraFAQ Forum: Performance Tuning » Conditions On Virtual Columns Degrades Performance

Home » RDBMS Server » Performance Tuning » Conditions On Virtual Columns Degrades Performance (Oracle 9i,9.2.0.6.0,XP)

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

Conditions On Virtual Columns Degrades Performance [message #322331]

Fri, 23 May 2008 02:22

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Dear All,

I have three tables t1,t2 and t3.

In t1,t2 and t3 tables bulk of data is quite
large.Around 80000 to 100000 rows in each table.

SELECT * FROM 
(  
SELECT 
   t1.c1,
   (select count(1) from t2 where t2.c1=t1.c1)cond1,
   (select count(1) from t3 where t3.c1=t1.c1)cond2,
from 
  t1
)a
where a.cond1-a.cond2>0 and a.cond2>0

Applying this condition

 where a.cond1-a.cond2>0 and a.cond2>0

degarde performance.

How this query can be restructured

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322333 is a reply to message #322331]

Fri, 23 May 2008 02:27

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

Senior Member
Account Moderator

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

Regards
Michel

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322334 is a reply to message #322333]

Fri, 23 May 2008 02:35

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Dear Michel,
Can't you just tell me the solution for this.

Is their any analytical function available that can work
On this.

Regards,
Rajat Ratewal

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322337 is a reply to message #322334]

Fri, 23 May 2008 02:53

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

Senior Member
Account Moderator

The solution depends on information you didn't post.

Regards
Michel

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322358 is a reply to message #322337]

Fri, 23 May 2008 04:06

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Dear Michel,

The main problem is with where clause.Actually if query is run without where condition the data is kept in the buffer(around 32000 rows) which is quite large.

And then when where condition is applied it again checks the buffer to eliminate the rows resulting only 10-15 rows.But to eliminate these rows i have to use extra select clause on top because i can't use cond1 and cond2 in main where clause.So this is causing high consistent gets becuase again data in buffer is checked.

I wan't to move where condition in the main query so that i can reduce my rows read in the buffer.But how can i use where clause on these conditions.

   (select count(1) from t2 where t2.c1=t1.c1)cond1
   (select count(1) from t3 where t3.c1=t1.c1)cond2

Is their any function in oracle that i can use to eliminate the extra select * clause on main query. i.e use cond1 and cond2 in main query where clause.

Hope this clarifies.

Regards,
Rajat Ratewal

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322375 is a reply to message #322358]

Fri, 23 May 2008 05:13

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

SELECT
FROM t1
JOIN (
   SELECT c1, count(*) AS c1_count
   FROM   t2
   GROUP BY c1
) t2
ON t2.c1 = t1.c1
JOIN (
   SELECT c1, count(*) AS c1_count
   FROM   t3
   GROUP BY c1
) t3
ON t3.c1 = t1.c1
WHERE t2.c1_count > t3.c1_count

Then read this article about why you should never use scalar sub-queries.

Ross Leishman

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322384 is a reply to message #322375]

Fri, 23 May 2008 05:32

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Dear rleishman,
Thanks for replying.But you have used joins on tables.I used the query to avoid the unnecessary pulling data from tables.Because when you join two tables the data of joining tables must be read in buffer first and then join condition applies.

This slows down the query further.

please correct me if i am wrong.

Regards,
Rajat Ratewal

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322508 is a reply to message #322384]

Fri, 23 May 2008 22:23

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Try the SQL. Read the article.

If the performance is not improved, post the TKPROF output of a SQL Trace.

Ross Leishman

Report message to a moderator

Re: Conditions On Virtual Columns Degrades Performance [message #322513 is a reply to message #322508]

Fri, 23 May 2008 23:47

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Thanks rleishman for your suggestions
I will try this out.

Regards,
Rajat Ratewal

Report message to a moderator

Previous Topic:	New to Tuning
Next Topic:	optimizer path via DBLINKS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 21:34:24 CDT 2024