Re: Merge join and sorts

Alex Bolenok wrote:

> Hello all,
>
> I have two tables in my database (Oracle 8.1.7 on Linux):
>
> CREATE TABLE t_move2 (
> mov_id NUMBER NOT NULL,
> mov_cost FLOAT NOT NULL,
> ..., /* OTHER ATTRIBUTES */
> CONSTRAINT ix_move2_id
> PRIMARY KEY (mov_id)
> )
> ... /* STORAGE AND PARTITIONING OPTIONS */
> ;
>
> CREATE TABLE t_xcome2 (
> xcm_id NUMBER NOT NULL,
> xcm_move NUMBER NOT NULL,
> xcm_amount FLOAT NOT NULL,
> ..., /* OTHER ATTRIBUTES */
> CONSTRAINT ix_xcome2_id
> PRIMARY KEY (xcm_id),
> CONSTRAINT fk_xcome2_move
> FOREIGN KEY (xcm_move)
> REFERENCES t_move2 (mov_id)
> ON DELETE CASCADE
> )
> ... /* STORAGE AND PARTITIONING OPTIONS */
> ;
>
> CREATE INDEX ix_xcome2_move ON t_xcome2 (xcm_move) ... /* STORAGE AND
> PARTITIONING OPTIONS */;
>
> There is a one-to-many relation between t_move2 and t_xcome2.
>
> There are about 400.000 records in t_move2 and about 1.200.000 records in
> t_xcome2.
>
> Now I want to fetch all the records from these tables joined on relative
> attributes. I want to perform index scans on these tables so that they could
> be merged without sorting. So I write:
>
> SELECT /*+ INDEX_ASC (x ix_xcome2_move) INDEX_ASC (m ix_move2_id) USE_MERGE
> (x m) */
> xcm_amount * mov_cost
> FROM t_xcome2 x, t_move2 m
> WHERE xcm_move = mov_id
>
> Here is the plan of the query:
>
> SELECT STATEMENT
> MERGE JOIN
> TABLE ACCESS (BY GLOBAL INDEX ROWID) -- T_XCOME2
> INDEX (FULL SCAN) -- IX_XCOME2_MOVE
> SORT (JOIN)
> TABLE ACCESS (BY GLOBAL INDEX ROWID) -- T_MOVE2
> INDEX (FULL SCAN) -- IX_MOVE2_ID
>
> So the question is: why is this sort on t_move2 and can it be avoided?
>
> If I get everything right, Oracle should perform ascending index scan on
> joined fields, so it does not need to sort anything.
>
> Thanks in advance,
>
> Alex Bolenok.

Answer to your question: Merge join uses sort. Always. If you want to know why, read something about sort algorithms, Knuth for example. I don't understand hints though. If you want merge join, index search just makes it slower, much slower, about 3 times. If you want to use index scan on join fields, either remove all hints or use only one index hint, on the smaller table. The most efficient algorithm here would be full table scan on bigger table and index scan on a smaller one. Received on Wed Jan 09 2002 - 00:01:03 CST