Hi all,

just wondered if anyone would be able to shed any light on an issue we are currently having.

We have 2 tables which we union using a view. Selecting information from this view is fine, and indexes on the underlying tables is used correctly.

For example:

Select v.*
From View v
Where v.id = x

However, if we need to join with another table table, the optimizer seems to be creating a temporary table and then sequentially scanning.

For example:

Select v.*, o.*
From View v, Other o
Where v.id = o.id
And o.id = x

This is causing our queries to take a long time to complete due to the size of the table being created by the view (3 million records).

Any help appreciated!

Hi Lemm,

Are you sure there are statistics available on the table under the view. If this isn't solving the problem please sen din info like exact version and both access paths.

Hope ths helps,

Rob

Hi,
Please, take the SQL that generates the view and runs this with set explain on.

Gustavo.