At 09:54 AM 10/10/2005 +0530, you wrote:

>Hi,
>
>I am facing problems with the GROUP BY clause. It takes a lot of time to
>return with the values.
>
>I am trying the following:
>
>SELECT COUNT(1) FROM VW_INSERT_RIP WHERE USER_KEY = '8888'
>
>This query takes 214 seconds to return the value - 785,000 records from
>60,000,000 records.

This query doesn't have a group by clause. All it does is to limp through
the entire record set by your view, one row at a time, from beginning to
end, deciding whether to add the row to its count, or not, depending on the
value it finds for USER_KEY. In a grouped query, it will count the records
*after* grouping.

Try this instead:

select user_key, count(*) from vw_insert
where user_key = '8888'
group by 1

>The view internally joins 3 tables. It has index on the FUND_PRICE table
>(with 60,000,000 odd records) but not on the other lighter tables.

Indexes can be crucial to joins and searches. If there is no index on
user_key (or if the join specifications are poor) then nothing is fast. So
214 seconds to do a bad join and then walk through 60 million records
sounds pretty quick to me (even without knowing what you are concealing in
the view. :-))

Would you please remove your company's Disclaimer blurb from list
mails. It is totally pointless in list mail, besides wasting bandwidth and
filling up everyone's inbox with extra garbage.

^ heLen