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