| How can I optimize my query? [message #21394] |
Thu, 01 August 2002 01:51  |
Kannan
Messages: 29
Registered: September 2000
|
Junior Member |
|
|
Sir,
I have two tables namely ceth_mast (Master table
with ceth as primary key and total no. of rows: 2300 rows).
I have another transaction table called rt12
with 35 lakhs (3.5 million rows)in it. The ceth column is the foreign key in this table. The primary key is
ecc_code. Both ecc_code and ceth are indexed.
While joining the tables I used 'IN' operator and the result set was obtained in 20 minutes.
Then I replaced 'IN' with 'EXISTS' operator and
the result set was obtained in 15 minutes.
Can I improve my query with any other operator?
Query using IN operator (20 minutes).
select ceth, sum(col2), sum(col3) from rt12
where ceth in (select ceth from ceth_mast)
group by ceth;
Query using EXISTS operator (15 minutes).
select ceth, sum(col2), sum(col3) from rt12
where exists (select 'X' from ceth_mast where
ceth = rt12.ceth) group by ceth;
Can you please suggest me some other form of query which will be faster than the above 2 queries.
Regards
Kannan
|
|
|
|
| Re: How can I optimize my query? [message #21397 is a reply to message #21394] |
Thu, 01 August 2002 04:47  |
Venkata Krishnayya Kommu
Messages: 13
Registered: July 2000
|
Junior Member |
|
|
check V$sql for this. CHeck the number of disk reads. Obviously they will be very high.
Check whether there is any index on the table rt12(transaction table) on the column ceth. Mostly, creation if index will solve the problem.
HTH
Venkata Krishnayya Kommu
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
