Problems with a plan

From: Mladen Gogala <no_at_email.here.invalid>
Date: Mon, 4 Apr 2011 15:28:36 +0000 (UTC)
Message-ID: <pan.2011.04.04.15.28.35_at_email.here.invalid>

I have a "top query" that is exhibiting a strange problem. The query looks like this:

Select Article# From
(

Select
  a.article#
From
  Articles A, Article_Tags At
Where
  A.Article# = At.Article# And
  At.Tag# = 340384
  Order By A.Published Desc
) where rownum < 30;

The plan that Oracle comes with includes a full index scan on the very large ARTICLES table:

PLAN_TABLE_OUTPUT

---

| 0 | SELECT STATEMENT | | 29 | 377 | 4368
(8)| 00:00:14 |

|*  1 |  COUNT STOPKEY		 |		      |       |       |
   |	      |

|   2 |   VIEW			 |		      | 16198 |   205K|  

4368
(8)| 00:00:14 |

|* 3 | SORT ORDER BY STOPKEY | | 16198 | 442K| 4368
(8)| 00:00:14 |

PLAN_TABLE_OUTPUT

---

|* 4 | HASH JOIN | | 16198 | 442K| 4364
(8)| 00:00:14 |

| 5 | TABLE ACCESS CLUSTER| ARTICLE_TAGS | 16198 | 205K| 10
(0)| 00:00:01 |

|*  6 |       INDEX UNIQUE SCAN  | TAG_CLU_IND	      |     1 |       
|     1

(0)| 00:00:01 |

| 7 | INDEX FAST FULL SCAN| ARTICLES_PUBLISHED | 5293K| 75M| 4210

PLAN_TABLE_OUTPUT

---

(5)| 00:00:13 |

---

---

Predicate Information (identified by operation id):

---

   1 - filter(ROWNUM<30)
   3 - filter(ROWNUM<30)

PLAN_TABLE_OUTPUT

---

   4 - access("A"."ARTICLE#"="AT"."ARTICLE#")    6 - access("AT"."TAG#"=340384)

22 rows selected.

Elapsed: 00:00:00.11
SQL> The FIRST_ROWS(30) hint should be an automatic consequence of using the ROWNUM<30 condition. This plan can only be improved with a direct USE_NL hint:

explain plan for
Select Article# From
(

Select /*+ USE_NL(A,AT) */
  a.article#
From
  Articles A, Article_Tags At
Wher 2 e
  A.Article# = At.Article# And
  At.T 3 ag# = 340384
  Order By 4 A.Published Desc
) where rownum < 30;dbms_xplan.display);) where rownum < 30;

select * from table(dbms_xplan.display);

Explained.

Elapsed: 00:00:00.08
SQL> SQL>
PLAN_TABLE_OUTPUT

---

Plan hash value: 866945626

---

---

| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |

---

---

PLAN_TABLE_OUTPUT

---

| 0 | SELECT STATEMENT | | 29 | 377 | 16256
(1)| 00:00:49 |

|*  1 |  COUNT STOPKEY		 |		      |       |       |
   |	      |

|   2 |   VIEW			 |		      | 16198 |   205K| 

16256
(1)| 00:00:49 |

|* 3 | SORT ORDER BY STOPKEY | | 16198 | 442K| 16256
(1)| 00:00:49 |

PLAN_TABLE_OUTPUT

---

| 4 | NESTED LOOPS | | 16198 | 442K| 16252
(1)| 00:00:49 |

| 5 | TABLE ACCESS CLUSTER| ARTICLE_TAGS | 16198 | 205K| 10
(0)| 00:00:01 |

|*  6 |       INDEX UNIQUE SCAN  | TAG_CLU_IND	      |     1 |       
|     1

(0)| 00:00:01 |

|*  7 |      INDEX RANGE SCAN	 | ARTICLES_PUBLISHED |     1 |    15 
|     1

PLAN_TABLE_OUTPUT

---

(0)| 00:00:01 |

---

---

Predicate Information (identified by operation id):

---

   1 - filter(ROWNUM<30)
   3 - filter(ROWNUM<30)

PLAN_TABLE_OUTPUT

---

   6 - access("AT"."TAG#"=340384)
   7 - access("A"."ARTICLE#"="AT"."ARTICLE#")

22 rows selected.

Elapsed: 00:00:00.12
SQL> Now, my question is why is Oracle optimizer picking the plan with the full index scan? Costs of both plans is the same, which is also not quite clear to me. The hinted plan executes in 2 seconds, while the original plan takes full 27 seconds to execute. I thought that ROWNUM<n condition should imply an automatic FIRST_ROWS(n) hint, which, according to the documentation, should favor nested loops joins over the hash joins. It seems to me that CBO is skewed to prefer hash join in almost all cases. Not even FIRST_ROWS(1) hint helps. The version is 10.2.0.5, 64 bit. Table ARTICLE_TAGS is clustered with an another two tables, not the ARTICLES table.
I experimented with various things, like setting OPTIMIZER_INDEX_CACHING to 0 (it's 70 by default), but nothing short of USE_NL hint actually helped.

-- 
http://mgogala.byethost5.com

Received on Mon Apr 04 2011 - 10:28:36 CDT