Re: Merge join and sorts

I think it's a bug - I've never been able to get the inner and outer tables to merge
without reporting this unnecessary sort, even when constructing the data to make
it highly likely. (Even in 9.0.1)

And the 10032 trace shows that the sort takes place - and that the data being sorted is already arriving in order.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Alex Bolenok wrote in message ...


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

>

>

>