inner join Vs View Vs for all entries

[Former Member]

Hi all,

Lets say i want to select data from 2 database tables db1 and db2.

I have three options :

1) I use inner join on two database tables and select data

2) I define a Database View and then select data from View

3) I select data into internal table itab1 from db1 ( databse table1) and then select data from db2 applying for all entries on itab1.

bw options 1 and 2 ...my doubt is as far as I know its better to define a View and then select data.

But when v define a view then also we need to specify inner join condition . Database stores view definition only and when a query is triggered on view then data is selected . ultimately databse is computing inner join so why defining View is better.

Among all the three options which one is better , I know this much that for very large data "for all entries " reduces the performance.

Plz clarify my doubts

[RichHeilman]

I think using a View is the best way in this case.

<i>Database stores view definition only and when a query is triggered on view then data is selected . ultimately databse is computing inner join so why defining View is better.</i>

This may be true, lets think of it this way. If the system is really computing the inner join on the fly, then most likely it is also buffering the statement(noting the access path) so the next time this statement is executed, the access path is already there, which means it may be faster. Having the view do the join means that this can be used in more than one program. Say that you have 10 programs that all need to hit these two tables and 10 different developers are writing the select statement in there program, all of which write it a little differently. The access paths will be different as well, but if all the developers use the same view, then they are all using the same access path.

See what I mean?

Of course, this all depends if the SQl statements are buffer for views to begin with.

Regards,

Rich Heilman

[Former Member]

Hi Rich,

With reference to the thread discussion, would you suggest your "view" when we can select data using INNER JOIN on 2 tables (without proper Index on required field) against selecting data from a database VIEW (with JOIN on 4 tables - which do not have any index anyways)?

Many thanks,

Goutam.

[sridhar_k1]

I Think Difining a view and using select join is same performance wise except little over head of converting the join open sql to native sql.

When you need to use the same inner join at many places, then using view is simple instead of writing a join statement.

Regards

Sridhar

[Former Member]

Hi Rich,

got the point..view is better than bw 1st and 2nd option

What abt for all entries, which one shuld i use for all entries or views

[RichHeilman]

I think that you might be comparing apples to oranges when comparing "For ALL Entries" and "Views". It really depends on the requirement, but think in this way. WHen using "FOR ALL ENTRIES", you are doing at least two round trips to the database, using a view, you are only doing one.

Regards,

RIch Heilman