Re: Performance Problem with View

Nicolas Bronke wrote:

> Following:
> I have a view on shema one. This view takes 2 second for 15000 records for
> a select count(*)
>
> From another user I created a view on this including two joins for two
> colums (Both table have around 90 records) and I am for sure that I made
> the join correctly.
> But now the select count(*) on this table needs around 200 seconds.
> Okay this should not be a big problem, if the a stored procedure needs my
> view a couple of time so that I wait around half an hour.

Difficult to work without the actual SQLs you're using.

It would seem that the join method is suspect.

For example, let's say that the join criteria is such that no indexes can be used. This then results in a full table scan for each of the join tables.

This means that for every row in your 15,000 SELECT COUNT(*), 2x90 row full table scans need to be performed. So instead of only having to process 15,000 rows for the SELECT COUNT(*), Oracle now needs to process 15,000 x (2x90) row scans for the SELECT COUNT(*).

I suggest that you run the SELECT COUNT(*) through EXPLAIN PLAN and see how Oracle will go about doing the joins to the 2 x 90 row tables.

--
Billy