topic join vs. view in Application Development and Automation Discussions

join vs. view

Former Member — Thu, 16 Jul 2009 04:20:24 GMT

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

Re: join vs. view

Former Member — Thu, 16 Jul 2009 05:27:57 GMT

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

Re: join vs. view

Former Member — Thu, 16 Jul 2009 05:33:38 GMT

Hi Sasi,

Check out this link.

Regards

P Bansal

Re: join vs. view

Former Member — Thu, 16 Jul 2009 06:14:01 GMT

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

Re: join vs. view

Former Member — Thu, 16 Jul 2009 11:55:58 GMT

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.

Re: join vs. view

Former Member — Thu, 16 Jul 2009 12:05:30 GMT

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