Re: formula help please
From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 06 Jan 2008 09:43:05 -0800
Message-ID: <1199641359.92518@bubbleator.drizzle.com>
>> Maxim Demenko wrote:
>>> Totti schrieb:
>>>> statments, and as i understood from you, the constraints and the
>>>> primary and foreign keys are essentials in solving such problems.
>>>
>>> Essentials in solving such problems ( as it seems for me) from
>>> Charles' post is:
>>> <quote>
>>> Oracle has no automatic understanding of how one table
>>> should be joined to another table, even if foreign keys are defined
>>> </quote>
>>> The only exception of this statement i am aware of (which is
>>> meaningless in this context, because you aren't using it - is natural
>>> join syntax).
>>> That means ( for me ) - if some tables are joined and cartesian
>>> product (leading to excessive row generation, cpu consumption and
>>> temp usage, what may appear as hang) should be avoided - then join
>>> conditions should be defined. Defining proper primary/foreign keys is
>>> not essential (but of course, may be helpful) to solve such problems...
>>>
>>> Best regards
>>>
>>> Maxim
>>
>> The other exceptions would be if you have defined BITMAP JOINS INDEXES
>> or DIMENSION objects.
>>
>> Consider, for example, the following syntax:
>>
>> CREATE BITMAP INDEX bjix_fact_prod
>> ON facts (product.prod_name)
>> FROM facts, products
>> WHERE fact.prod_id = product.prod_id;
Date: Sun, 06 Jan 2008 09:43:05 -0800
Message-ID: <1199641359.92518@bubbleator.drizzle.com>
Maxim Demenko wrote:
> DA Morgan schrieb:
>> Maxim Demenko wrote:
>>> Totti schrieb:
>>>> statments, and as i understood from you, the constraints and the
>>>> primary and foreign keys are essentials in solving such problems.
>>>
>>> Essentials in solving such problems ( as it seems for me) from
>>> Charles' post is:
>>> <quote>
>>> Oracle has no automatic understanding of how one table
>>> should be joined to another table, even if foreign keys are defined
>>> </quote>
>>> The only exception of this statement i am aware of (which is
>>> meaningless in this context, because you aren't using it - is natural
>>> join syntax).
>>> That means ( for me ) - if some tables are joined and cartesian
>>> product (leading to excessive row generation, cpu consumption and
>>> temp usage, what may appear as hang) should be avoided - then join
>>> conditions should be defined. Defining proper primary/foreign keys is
>>> not essential (but of course, may be helpful) to solve such problems...
>>>
>>> Best regards
>>>
>>> Maxim
>>
>> The other exceptions would be if you have defined BITMAP JOINS INDEXES
>> or DIMENSION objects.
>>
>> Consider, for example, the following syntax:
>>
>> CREATE BITMAP INDEX bjix_fact_prod
>> ON facts (product.prod_name)
>> FROM facts, products
>> WHERE fact.prod_id = product.prod_id;
> > Still you have to specify join conditions *explicitly* ( as opposite to > natural join, where join conditions are obtained by oracle *implicitly*) > or am i missing something obvious? > > Best regards > > Maxim
You are absolutely correct. My point was that there are several ways to give the optimizer the information.
My response was to the following sentence: "Oracle has no automatic understanding of how one table should be joined to another table." Your interpretation of "automatic" may be slightly different here. A natural join is just an uneducated guess as likely to join a.comments to b.comments as it is to join a.rid to b.rid. Bitmap Join Indexes and Dimensions are a way to create database resident information.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sun Jan 06 2008 - 11:43:05 CST