>>>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?

A query like, SELECT NumberOne.FieldA , NumberTwo.FieldB INTO CURSOR NewCursor FROM NumberOne, NumberTwo, produces a table that is the cartesian product of NumberOne and NumberTwo.

If there are 10 records in NumberOne and 20 records in NumberTwo then NewCursor contains 10*20=200 records.
NewCursor contains two fields : FieldA and FieldB.