Re: Native Hash Full Join and Pagination Query 11g
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: Stalin <stalinsk@xxxxxxxxx>
- Date: Wed, 4 Nov 2009 09:18:18 +0900
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@xxxxxxxxx>
> 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)
>
Other related posts:
