Re: Native Hash Full Join and Pagination Query 11g

From: Stalin <stalinsk_at_gmail.com>
Date: Tue, 3 Nov 2009 10:01:40 -0800
Message-ID: <c5363d3a0911031001q7fddee3cld5160a7c49f7ecaf_at_mail.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 - 12:01:40 CST