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
|
|
|
|
|
|
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
|
|
|
|
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
|
|
|
|
|