Re: Native Hash Full Join and Pagination Query 11g

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: