Re: Native Hash Full Join and Pagination Query 11g

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Wed, 4 Nov 2009 09:18:18 +0900
Message-ID: <43c2e3d60911031618v658d4c4cp441592dd7cb90bc4_at_mail.gmail.com>

Your traditional full outer join(I mean, union all) consists of 2 parts. part1: nested loops outer join - which is good for pagination part2: merge anti join - which is bad for pagination

I think that converting the part2 into nested loops anti join would meet your requirement - pagination.

1. Would you try first_rows(10) hint with native full outer hash join disabled?
2. When above try fails, could you post the outline of your execution plan? select * from table(dbms_xplan.display(...., 'outline')); This would show the query block name of the part2 and with that name you can control the plan with the appropriate hints.

   ---

Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================

2009/11/4 Stalin <stalinsk_at_gmail.com>

> No Luck there. Here is the revised Plan.
>
> SELECT * FROM (
> SELECT tmp.*, rownum rnum FROM (
> SELECT /*+ opt_param('_optimizer_native_full_outer_join', 'off')
> use_nl no_native_full_outer_join */ c.customerId, subscriberId, name,
> city, phone1, email1, a.actId
> FROM Accounts a FULL JOIN Customer c ON a.customerid =
> c.customerid ORDER BY c.name, a.actId
> ) tmp WHERE rownum <= 10
> ) WHERE rnum >= 1
>
>
> -----------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows
> | Bytes |TempSpc| Cost (%CPU)| Time |
>
> -----------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> | | | 547K(100)| |
> |* 1 | VIEW | |
> 30 | 22230 | | 547K (1)| 01:49:36 |
> |* 2 | COUNT STOPKEY | |
> | | | | |
> | 3 | VIEW | |
> 2940K| 2041M| | 547K (1)| 01:49:36 |
> |* 4 | SORT ORDER BY STOPKEY | |
> 2940K| 2439M| 2552M| 547K (1)| 01:49:36 |
> | 5 | VIEW | |
> 2940K| 2439M| | 8147 (7)| 00:01:38 |
> | 6 | UNION-ALL | |
> | | | | |
> | 7 | NESTED LOOPS OUTER | |
> 2940K| 1777M| | 8146 (7)| 00:01:38 |
> | 8 | TABLE ACCESS FULL | ACCOUNTS |
> 2940K| 187M| | 8146 (7)| 00:01:38 |
> | 9 | TABLE ACCESS BY INDEX ROWID| CUSTOMER |
> 1 | 567 | | 0 (0)| |
> |* 10 | INDEX UNIQUE SCAN | CT_CUSTOMER_PK |
> 1 | | | 0 (0)| |
> | 11 | MERGE JOIN ANTI | |
> 1 | 580 | | 1 (100)| 00:00:01 |
> | 12 | TABLE ACCESS BY INDEX ROWID| CUSTOMER |
> 1 | 567 | | 0 (0)| |
> | 13 | INDEX FULL SCAN | CT_CUSTOMER_PK |
> 1 | | | 0 (0)| |
> |* 14 | SORT UNIQUE | |
> 1 | 13 | | 1 (100)| 00:00:01 |
> |* 15 | INDEX FULL SCAN | IX_ACCT_CUSTOMER |
> 1 | 13 | | 0 (0)| |
>
> -----------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter("RNUM">=1)
> 2 - filter(ROWNUM<=10)
> 4 - filter(ROWNUM<=10)
> 10 - access("A"."CUSTOMERID"="C"."CUSTOMERID")
> 14 - access("A"."CUSTOMERID"="C"."CUSTOMERID")
> filter("A"."CUSTOMERID"="C"."CUSTOMERID")
> 15 - filter("A"."CUSTOMERID" IS NOT NULL)
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 03 2009 - 18:18:18 CST