Re: Native Hash Full Join and Pagination Query 11g

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Tue, 3 Nov 2009 10:08:15 +0900
Message-ID: <43c2e3d60911021708j7b4d4ad6s639583d828c8490_at_mail.gmail.com>

What if you apply one of following hints?

• opt_param('_optimizer_native_full_outer_join', 'off')
• use_nl
• no_native_full_outer_join

  ---

Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================

2009/11/3 Stalin <stalinsk_at_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
>
>
>

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

Received on Mon Nov 02 2009 - 19:08:15 CST