Will writing an inner join be better or creating a view?

[ameya_kulkarni3]

hi,

I have a question.

I have 3 fairly heavy tables.I have created a view using those 3 tables..but the performance is high.

I am thinking about writing an inner join query and try with those 3 tables.

Can u suggest which option would be better?

[former_member182354]

If those tables is used on frequent basis then view is better but keep in mind that it will occupy an extra memory space..

Raghav

[Former Member]

Hi,

no extra memory - well, some bytes to store the SQL statement inside the database

a view is nothing but a stored SQL statement. If you join in this statement you have a view with a join.

For the database it's always a SQL statement that must be analyzed to get the best execution plan.

bye

yk

[Former Member]

Hi

I hope you can go for FOR ALL ENTRIES than joins and views.

Please check the possibilty and go for all entries

Regards

Rasheed

[Former Member]

Hi ,

You can try to create new index for this tables maybe it will help

when you select the data.

lilach

[Former Member]

Hi,

I am thinking about writing an inner join query and try with those 3 tables.

Views also use inner join internally. So I doubt it would improve the performance. What you need to make sure is that you have joined the tables in the view in the correct manner , preferebly key/ foreign key fields are used.

And yes for all entries does not improve the performance.

regards,

Advait

[Former Member]

Hi,

I suggest you work with Inner join statements. Working with views is a bit tricky as your

table join conditions might not give the desired results in certain scenarios.

Regards

Sajimon Chandran

[Former Member]

hi,

In my opinion view will be a better option than using inner join. it will definitely perform better.

thanx.

abhijit

[Former Member]

Hi,

View will be better than inner join.

Thanks

[Former Member]

Hi,

Views also use joins internally, so better to use for all entries for performance.

Thanks,

Krishna...

[ThomasZloch]

Cars also use engines internally, so better to use bicycle for performance.

You're welcome.

Thomas

[Former Member]

>

> Cars also use engines internally, so better to use bicycle for performance.
> You're welcome.
> Thomas

Best answer I've seen in a very long time Thomas.

Rob

[Former Member]

>

> hi,
> 
> I have a question. 
> I have 3 fairly heavy tables.I have created a view using those 3 tables..but the performance is high.
> 
> I am thinking about writing an inner join query and try with those 3 tables.
> 
> Can u suggest which option would be better?

Hi,

you compare peas with apples.

a view is nothing but a stored SQL statement. If you join in this statement you have a view with a join.

For the database it's always a SQL statement that must be analyzed to get the best execution plan.

Joining big tables (without an efficient WHERE filter) is a mass data task an will take time.

What you will get are full table scans of the tables and a merge operation of the 3 involved tables on

the database side.

Join on the complete index key and if you have a WHERE clause that will eliminate 90- 95% of the data

you will get an efficient index access.

But it really , really depends on the data volume and the indexes you have.

Btw:

• Check for cartesian products (possible when you forgot necessary join conditions ) , wich easily could kill your database performance.

bye

yk

[Former Member]

Hi,

Views also use joins internally, so it makes no differance to use joins or views,

better use for all entries.

Thanks & Regards,

Krishna..

[ThomasZloch]

Are you reading the other replies to the questions?

> better use for all entries.

There have been interesting discussions on join vs. fae here, there is blogs etc., just have look around before jumping in with nonsense like that.

Best regards

Thomas

[former_member194613]

For all the FOR ALL ENTRIES lovers ... there is no proof for these reappearing recommendation.

There is nearly nobody who receives forum points, who recommends FOR ALL ENTRIES instead of Joins. What is the reason ???

It is easier to prove the opposite. A Join is a nested loop inside the database, a FOR ALL ENTRIES is partly outside of the database. FOR ALL ENTRIES works in blocks, joins on totals.

FOR ALL ENTRIES are not recommded on really large tables, because the chances are to high that

too many records are trnasferred.

People prefer FOR ALL ENTRIES, because JOINs are not so easy to understand. Joins can go wrong, but with a bit of understanding they can be fixed.

Some Joins are slow and can not be fixed, but then the FOR ALL ENTRIES would be extremely slow.

There are several kinds of views:

+ projection views, i.e. only one table involved just fields reduced

+ join views, several tables, joins conditions stored in dictionary

+ materialized views, here the joined data are actually stored in the database. Storing and synchronisation has to be done manually.

Only the last one creates real overhead. It should be the exception.

Join Views and Joins are nearly identical. The view is better for reuse. The join is better in complicated, becuase if the access goes wrong, it can often be fixed by adding a hint. Hints can not be added to views.

Siegfried

[matt]

>

> People prefer FOR ALL ENTRIES, because JOINs are not so easy to understand. Joins can go wrong, but with a bit of understanding they can be fixed.

Funny that - I always found JOINs easier to understand...

[Former Member]

Hi Matt,

Agree: If you can write it in 1 statement, just do it.

It's really hard to read a procedural code instead of a well written formatted SQL statement.

You don't care about HOW the data will be retrieved only WHAT data.

But it needs some practice reading it, though.

SQL is an art

Bye

yk

[Former Member]

Hi,

Just want to give some opinion on this.

First, it's not fair to give recommendation if we do not know what's the type of table used.

If it is cluster table, I would say avoid join. If it is buffered table, I would also say avoid join.

If they all are transaction table which are not buffered and are not cluster tables, I still think it's better to use join condition if tables involved in the join condition not more than 3.

Regarding why people love to use for all entries compare to join, I think it's related to simplicity of for all entries statement.

If we take dailly conversation as example, it would be more difficult to comprehend if someone talk to you with long sentences and maybe like one period per paragraph. But if the person talk with several sort sentences it will be a lot easier to understand for normal conversation. That is why for all entries is prefered by most people because they look at it as easier to understand and makes people assume the more complex statement in your program the poorer the performance will be.

Regards,

Abraham

[ThomasZloch]

> If it is cluster table, I would say avoid join.

You have no alternative, joins don't work with cluster tables.

> If it is buffered table, I would also say avoid join.

At least think twice, maybe compare runtimes if in doubt.

> If they all are transaction table which are not buffered and are not cluster tables, I still think it's better to use join condition if tables involved in the join condition not more than 3.

That limit of 3 is arbitrary, they work fine with more tables, if constructed properly. The limit is 25 (as I recently learned) or what the CBO can handle, whatever comes first (probably the latter).

> Regarding why people love to use for all entries compare to join, I think it's related to simplicity of for all entries statement.

> If we take dailly conversation as example, it would be more difficult to comprehend if someone talk to you with long sentences and maybe like one period per paragraph. But if the person talk with several sort sentences it will be a lot easier to understand for normal conversation. That is why for all entries is prefered by most people because they look at it as easier to understand and makes people assume the more complex statement in your program the poorer the performance will be.

I don't want to pick your creative comparison apart, but I think a) joins are not difficult to understand, once the fundamental concept is understood, and b) the follow-on problems that FAE often cause, e.g. data in multiple internal tables instead of one, will lead to overall more confusing code.

Thomas

[former_member194613]

@Abraham,

in principle you are right about the table type, but there are only a few cluster tables, I guess a cluster table would have been mentioned.

Buffered tables, absolutely right, but with large tables probability is low that buffered tables are involved.

Your argument ... more or less what I said, people avoid unterstanding long sentences Joins are not so difficult to understand that is right. But the options for processing of a join are actually not simple.

To analyse a join with problems takes a while even for me.

Siegfried

[Former Member]

This message was moderated.