Re: Native Hash Full Join and Pagination Query 11g

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


Other related posts: