>>I am using SYS(3054) to check the Rushmore Optimization Level on an inner join.
>>All info displayed indicates that the two tables are fully optimized.
>>
>>When there is at least 1 record selected the last line displays:
>>'joining table a and table b using index tab x'.
>>When there are no records selected the last line displays:
>>'joining table a and table b (Cartesian product)'.
>>
>>Is the SQL statement fully optimized?
>>
>>TIA.
>
>Think of the mathematical version of Cartesian product when you see that final line. That is, when table number one has X records and table number two has Y records their Cartesian product contains X*Y records. For each record in table number one it is joined to every record in table number two. The result of the join contains X*Y records. I do not know if officially speaking it is 'RUSHMORE OPTIMIZED' but performing a Cartesian product of two tables get VERY SLOW when the tables get large. For example, two tables of one thousand records each result in a cursor of one million records.

Dave,

When the query * does * generate records, it contains * only * the records I expected to see (about 6). If I multiply the # of records in table 'A' by 'B', there are hundreds.
Are you referring to an intermediate result set (when you say x*y) or have I misunderstood?