Index Usage

[ckwan]

Hi All,
I have a question on the SELECT Statement where it involve the group by clause.

Example :
SELECT A.something,B.someotherthing
FROM table A, table B
WHERE B.date <= :date
GROUP BY A.something;

In this case, is the SQL statement needs indexes for the A.something.
After SQL selected out all the records which B.date <= :date into temporary space in memory, say may be 1 million records after that, does it means it will require perform full table scan in order to group the A.something ? or What is the best way to optimise the sql statement for the above scenario ?

Thanks

[stancho]

If your table A is not with 1 row, you will have cartesian query (isn't there a join of A and B?).
You can speed up the query with index on B.date (if you are selecting small % of all rows in B) and large SORT_AREA_SIZE for the group by.

[tamilselvan]

Where is the join condition between TableA and TableB?

Tamil