Native Hash Full Join and Pagination Query 11g

From: Stalin <stalinsk_at_gmail.com>
Date: Mon, 2 Nov 2009 10:05:04 -0800
Message-ID: <c5363d3a0911021005j5af3ab7cj1013c7f36192d512_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 02 2009 - 12:05:04 CST