My View performance is very slow

From: <siddu_htc_at_yahoo.com>
Date: 1 Sep 2005 04:59:22 -0700
Message-ID: <1125575962.174226.44100@o13g2000cwo.googlegroups.com>

The performance of the below view is very bad. I created below view to get the data from 2 different tables and there is a column which is not part of any table named "UPDATED",Which will be used along with other columns for qyerying.

Ex: select *from my_view_v where UPDATED = 'No' and other columns. I created indexes on base tables even then performance is bad.

The base table t1 contains 1,00,0000 records.I have a union all also in the view.

Please help.

CREATE OR REPLACE FORCE VIEW my_view_V
(SOURCE_ROW_ID,...120 columns ......,UPDATED) AS
SELECT t1.ROWID,

       --120 columns


       'No'

FROM table1 t1,
     table2 t2

WHERE Join b/we the above tables
UNION ALL
SELECT t2.ROWID,

       --120 columns


       'Yes'

FROM table1 t1,
     table3 t2

WHERE Join b/we the above tables Received on Thu Sep 01 2005 - 06:59:22 CDT