join vs. view

[Former Member]

Hi all,

I have a doubt regarding the advantage of views over joins. For example:

suppose my database view has 4 db tables in it ... say A B C D

what i require is i need to retrieve some data which is present in tables A & C ...

in this case .. which is more efficient ?

join on A & C or the view ?

thanks and regards,

Sasi

[Former Member]

As per your requirement, JOIN will work faster as it will access only two table as per condition you pass.

But VIEW is associated with four tables, so put more load on database.

Hope you understand.

Regds,

Anil

[Former Member]

Hi Sasi,

Check out this link.

Regards

P Bansal

[Former Member]

Hi ,

Join statement can be used when U need to join 2 or 3 small tables...

if U join 2 large dbtab then peformance will reduce..So view is always better !!

In ur can case to join A & C it depends on the size of the dbtab, as per

my opinion please use view for better performance.

Thanks

Kathik

[Former Member]

Hi Sasi,

From a performance standpoint, If you have a view on tables A, B , C and D and would like to retieve data only from A and C it is advisable to use a Join rather than using the View.

Using the view will force the optimizer to read the data unnecessarily from B and D and sometimes may force the Database Optimizer to perform a Full table scan on B and D ( depending on join Conditions). Also you will need to consider that when you read data from View, at the Database interface level the Optimizer will construct a Join on all the table to read data.

Let me know if you have further queries.

Regards

Prasuna.

[Former Member]

Dear Sasi,

View- A,B,C,D.

Join - A & C - In this case regarding performance join is good.

Join - A,B,C,D - In this case regarding performance both view and join are same.

Why we design View : The answer is Reusability and developer need not write the join statements.

Hope this makes you clear.

Regards

Sajid

Edited by: shaik sajid on Jul 16, 2009 2:05 PM