Merge join and sorts

From: Alex Bolenok <abolen_at_chat.ru>
Date: Tue, 8 Jan 2002 14:22:56 +0300
Message-ID: <a1ekpt$g1m$1@news.rinet.ru>

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. Received on Tue Jan 08 2002 - 05:22:56 CST