Re: Native Hash Full Join and Pagination Query 11g
- From: Stalin <stalinsk@xxxxxxxxx>
- To: Dion Cho <ukja.dion@xxxxxxxxx>
- Date: Tue, 3 Nov 2009 10:01:40 -0800
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)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
