Re: formula help please

From: DA Morgan <>
Date: Sun, 06 Jan 2008 09:43:05 -0800
Message-ID: <>

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 (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Jan 06 2008 - 11:43:05 CST

Original text of this message