Native Hash Full Join and Pagination Query 11g
- From: Stalin <stalinsk@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Nov 2009 10:05:04 -0800
All,
I'm trying to tune a pagination query that uses Full Join but so far i
had no luck in making it run faster. Anyone had come across similar
query and got it run reasonably faster. The problem being though actId
is indexed and primary key of Accounts Table, Full Join makes the
index unusable. However, If i change Full Join to Left Join, the query
runs within Sub seconds. Any idea to workaround this.
SELECT * FROM (
SELECT tmp.*, rownum rnum FROM (
SELECT c.customerId, subscriberId, name, city, phone1, email1, a.actId
FROM Accounts a FULL JOIN Customer c ON a.customerid =
c.customerid ORDER BY a.actId
) tmp WHERE rownum <= 10
) WHERE rnum >= 1
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
378K(100)| |
|* 1 | VIEW | | 10 | 5930 | |
378K (1)| 01:15:41 |
|* 2 | COUNT STOPKEY | | | | |
| |
| 3 | VIEW | | 2940K| 1626M| |
378K (1)| 01:15:41 |
|* 4 | SORT ORDER BY STOPKEY | | 2940K| 1662M| 1766M|
378K (1)| 01:15:41 |
| 5 | VIEW | VW_FOJ_0 | 2940K| 1662M| |
8066 (6)| 00:01:37 |
|* 6 | HASH JOIN FULL OUTER| | 2940K| 1643M| |
8066 (6)| 00:01:37 |
| 7 | TABLE ACCESS FULL | CUSTOMER | | | |
2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | ACCOUNTS | 2940K| 53M| |
7981 (5)| 00:01:36 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=1)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
6 - access("A"."CUSTOMERID"="C"."CUSTOMERID")
Left Join.
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 4 (100)| |
|* 1 | VIEW | | 10 |
5930 | 4 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | |
| | |
| 3 | VIEW | | 10 |
5800 | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 10 |
5860 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ACCOUNTS | 2940K|
53M| 4 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | CT_DEVICE_PK | 10 |
| 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 |
567 | 0 (0)| |
|* 8 | INDEX UNIQUE SCAN | CT_CUSTOMER_PK | 1 |
| 0 (0)| |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=1)
2 - filter(ROWNUM<=10)
8 - access("A"."CUSTOMERID"="C"."CUSTOMERID")
Thanks,
Stalin
Sol10, 11.1.0.7
--
//www.freelists.org/webpage/oracle-l
Other related posts:
