Home » RDBMS Server » Performance Tuning » Conditions On Virtual Columns Degrades Performance (Oracle 9i,9.2.0.6.0,XP)
Conditions On Virtual Columns Degrades Performance [message #322331] Fri, 23 May 2008 02:22 Go to next message
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

Re: Conditions On Virtual Columns Degrades Performance [message #322333 is a reply to message #322331] Fri, 23 May 2008 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
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
Re: Conditions On Virtual Columns Degrades Performance [message #322334 is a reply to message #322333] Fri, 23 May 2008 02:35 Go to previous messageGo to next message
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
Re: Conditions On Virtual Columns Degrades Performance [message #322337 is a reply to message #322334] Fri, 23 May 2008 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The solution depends on information you didn't post.

Regards
Michel
Re: Conditions On Virtual Columns Degrades Performance [message #322358 is a reply to message #322337] Fri, 23 May 2008 04:06 Go to previous messageGo to next message
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


Re: Conditions On Virtual Columns Degrades Performance [message #322375 is a reply to message #322358] Fri, 23 May 2008 05:13 Go to previous messageGo to next message
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
Re: Conditions On Virtual Columns Degrades Performance [message #322384 is a reply to message #322375] Fri, 23 May 2008 05:32 Go to previous messageGo to next message
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
Re: Conditions On Virtual Columns Degrades Performance [message #322508 is a reply to message #322384] Fri, 23 May 2008 22:23 Go to previous messageGo to next message
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
Re: Conditions On Virtual Columns Degrades Performance [message #322513 is a reply to message #322508] Fri, 23 May 2008 23:47 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks rleishman for your suggestions
I will try this out.

Regards,
Rajat Ratewal
Previous Topic: New to Tuning
Next Topic: optimizer path via DBLINKS
Goto Forum:
  


Current Time: Sun Nov 03 07:08:10 CST 2024