Hi All !

lets say I have a SQL such as

SELECT c1,c2,c3,c4,c5,c6
FROM tab0
JOIN tab1 ON t1c1 =.... t1c2 = ....
JOIN tab2 ON t2c1 =.... t2c2 = ....
JOIN tab3 ON t3c1 =.... t3c2 = ....
JOIN tab4 ON t4c1 =.... t4c2 = ....
Where condition....

I noticed a significant performance enhancement by coding it as

SELECT c1,c2,c3,c4,c5,c6
FROM view1
JOIN tab4 ON t4c1 =.... t4c2 = ....
Where condition....

where view1 is defined as a view on
SELECT c1,c2,c3,c4,c5,c6
FROM tab0
JOIN tab1 ON t1c1 =.... t1c2 = ....
JOIN tab2 ON t2c1 =.... t2c2 = ....
JOIN tab3 ON t3c1 =.... t3c2 = ....

Now, in both cases, the actual materializtion takes place at run time (execution of the sql), then what would be the reason for performance enhancement.
I have seen views been used to simplify queries (coding) and implement security for end users. but not used views for performance enhancements
I would like to hear what others have experienced with views especially in the context of performance.

thank you

Anil

It should not work that way, but sometimes things do not work as well as they should. You could try to do an explain and see the optimized SQL for both options. You could also experiment with different query optimization levels (try 7 instead of default of 5) and do an explain.

But I notice that the SQL your posted is probably not the real SQL where you noticed the problem. Sometimes, posting the exact SQL and DDL (table and indexes) will reveal the reason for the difference in performance.