Prev: WPG_DOCLOAD Package
Next: Insert performance and autoextend
From: pratap.deshmukh on 25 Jun 2008 17:13
Hi,

We are using Oracle 10.2.0.3.0. I have a simple query like this -

select *
from large_table, small_table
where small_table.col = large_table.col (+)

large_table has 140 million rows. Small table can have any number of
rows.I am posting the execution plan for different scenarios where
small table has different number of records.

Case 1 - Small table has 180K records and large table has 140 million
records. The execution plan shows that small table is accessed first
followed by large_table and the large_table probes the hash table of
small table

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
180K| 122M| | 272K (3)| 01:03:42 | | |
|* 1 | HASH JOIN OUTER | |
180K| 122M| 122M| 272K (3)| 01:03:42 | | |
| 2 | PARTITION RANGE SINGLE| |
180K| 120M| | 3079 (4)| 00:00:44 | 3 | 3 |
| 3 | PARTITION LIST ALL | |
180K| 120M| | 3079 (4)| 00:00:44 | 1 | 25 |
|* 4 | TABLE ACCESS FULL | SMALL_TABLE |
180K| 120M| | 3079 (4)| 00:00:44 | 51 | 75 |
| 5 | INDEX FAST FULL SCAN | LARGE_TABLE_INDEX |
140M| 2007M| | 105K (3)| 00:24:40 | | |
------------------------------------------------------------------------------------------------------------------------------

Case 2 - Large table has 140 million records and small table has 12
million records. But this time the join order is reversed

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
12M| 8234M| | 815K (2)| 03:10:13 | | |
|* 1 | HASH JOIN RIGHT OUTER | |
12M| 8234M| 3614M| 815K (2)| 03:10:13 | | |
| 2 | INDEX FAST FULL SCAN | LARGE_TABLE_INDEX |
140M| 2007M| | 105K (3)| 00:24:40 | | |
| 3 | PARTITION RANGE SINGLE| |
12M| 8062M| | 200K (4)| 00:46:50 | 1 | 1 |
| 4 | PARTITION LIST ALL | |
12M| 8062M| | 200K (4)| 00:46:50 | 1 | 25 |
|* 5 | TABLE ACCESS FULL | SMALL_TABLE |
12M| 8062M| | 200K (4)| 00:46:50 | 1 | 25 |
------------------------------------------------------------------------------------------------------------------------------

Question is - Why is Oracle reversing the join order in Case 2 when
ideally it should be probing the large table into the hash of the
small table?

Regards,
Pratap


From: joel garry on 25 Jun 2008 18:24
On Jun 25, 2:13 pm, pratap.deshm...(a)gmail.com wrote:
> Hi,
>
> We are using Oracle 10.2.0.3.0. I have a simple query like this -
>
> select *
> from large_table, small_table
> where small_table.col = large_table.col (+)
>
> large_table has 140 million rows. Small table can have any number of
> rows.I am posting the execution plan for different scenarios where
> small table has different number of records.
>
> Case 1 - Small table has 180K records and large table has 140 million
> records. The execution plan shows that small table is accessed first
> followed by large_table and the large_table probes the hash table of
> small table
>
> ---------------------------------------------------------------------------­---------------------------------------------------
> | Id  | Operation               | Name                       | Rows  |
> Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
> ---------------------------------------------------------------------------­---------------------------------------------------
> |   0 | SELECT STATEMENT        |                            |
> 180K|   122M|       |   272K  (3)| 01:03:42 |       |       |
> |*  1 |  HASH JOIN OUTER        |                            |
> 180K|   122M|   122M|   272K  (3)| 01:03:42 |       |       |
> |   2 |   PARTITION RANGE SINGLE|                            |
> 180K|   120M|       |  3079   (4)| 00:00:44 |     3 |     3 |
> |   3 |    PARTITION LIST ALL   |                            |
> 180K|   120M|       |  3079   (4)| 00:00:44 |     1 |    25 |
> |*  4 |     TABLE ACCESS FULL   | SMALL_TABLE                |
> 180K|   120M|       |  3079   (4)| 00:00:44 |    51 |    75 |
> |   5 |   INDEX FAST FULL SCAN  | LARGE_TABLE_INDEX          |
> 140M|  2007M|       |   105K  (3)| 00:24:40 |       |       |
> ---------------------------------------------------------------------------­---------------------------------------------------
>
> Case 2 - Large table has 140 million records and small table has 12
> million records. But this time the join order is reversed
>
> ---------------------------------------------------------------------------­---------------------------------------------------
> | Id  | Operation               | Name                       | Rows  |
> Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
> ---------------------------------------------------------------------------­---------------------------------------------------
> |   0 | SELECT STATEMENT        |                            |
> 12M|  8234M|       |   815K  (2)| 03:10:13 |       |       |
> |*  1 |  HASH JOIN RIGHT OUTER  |                            |
> 12M|  8234M|  3614M|   815K  (2)| 03:10:13 |       |       |
> |   2 |   INDEX FAST FULL SCAN  | LARGE_TABLE_INDEX          |
> 140M|  2007M|       |   105K  (3)| 00:24:40 |       |       |
> |   3 |   PARTITION RANGE SINGLE|                            |
> 12M|  8062M|       |   200K  (4)| 00:46:50 |     1 |     1 |
> |   4 |    PARTITION LIST ALL   |                            |
> 12M|  8062M|       |   200K  (4)| 00:46:50 |     1 |    25 |
> |*  5 |     TABLE ACCESS FULL   | SMALL_TABLE                |
> 12M|  8062M|       |   200K  (4)| 00:46:50 |     1 |    25 |
> ---------------------------------------------------------------------------­---------------------------------------------------
>
> Question is - Why is Oracle reversing the join order in Case 2 when
> ideally it should be probing the large table into the hash of the
> small table?
>
> Regards,
> Pratap

Because at some point between 180K and 12M rows Oracle decides it is
cheaper to read the whole small table into a temp segment than to
probe 12M times with a right outer join. Do you disagree? Try a hint
and see what happens.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20080624/news_1c24urrea.html
From: Jonathan Lewis on 26 Jun 2008 17:52
<pratap.deshmukh(a)gmail.com> wrote in message
news:eba3c474-df59-43bc-ae13-99f3ec004ce4(a)m44g2000hsc.googlegroups.com...
> Hi,
>
> We are using Oracle 10.2.0.3.0. I have a simple query like this -
>
> select *
> from large_table, small_table
> where small_table.col = large_table.col (+)
>
> large_table has 140 million rows. Small table can have any number of
> rows.I am posting the execution plan for different scenarios where
> small table has different number of records.
>
> Case 1 - Small table has 180K records and large table has 140 million
> records. The execution plan shows that small table is accessed first
> followed by large_table and the large_table probes the hash table of
> small table
>
> ------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
> ------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 180K| 122M| | 272K (3)| 01:03:42 | | |
> |* 1 | HASH JOIN OUTER | |
> 180K| 122M| 122M| 272K (3)| 01:03:42 | | |
> | 2 | PARTITION RANGE SINGLE| |
> 180K| 120M| | 3079 (4)| 00:00:44 | 3 | 3 |
> | 3 | PARTITION LIST ALL | |
> 180K| 120M| | 3079 (4)| 00:00:44 | 1 | 25 |
> |* 4 | TABLE ACCESS FULL | SMALL_TABLE |
> 180K| 120M| | 3079 (4)| 00:00:44 | 51 | 75 |
> | 5 | INDEX FAST FULL SCAN | LARGE_TABLE_INDEX |
> 140M| 2007M| | 105K (3)| 00:24:40 | | |
> ------------------------------------------------------------------------------------------------------------------------------
>
> Case 2 - Large table has 140 million records and small table has 12
> million records. But this time the join order is reversed
>
> ------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
> ------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 12M| 8234M| | 815K (2)| 03:10:13 | | |
> |* 1 | HASH JOIN RIGHT OUTER | |
> 12M| 8234M| 3614M| 815K (2)| 03:10:13 | | |
> | 2 | INDEX FAST FULL SCAN | LARGE_TABLE_INDEX |
> 140M| 2007M| | 105K (3)| 00:24:40 | | |
> | 3 | PARTITION RANGE SINGLE| |
> 12M| 8062M| | 200K (4)| 00:46:50 | 1 | 1 |
> | 4 | PARTITION LIST ALL | |
> 12M| 8062M| | 200K (4)| 00:46:50 | 1 | 25 |
> |* 5 | TABLE ACCESS FULL | SMALL_TABLE |
> 12M| 8062M| | 200K (4)| 00:46:50 | 1 | 25 |
> ------------------------------------------------------------------------------------------------------------------------------
>
> Question is - Why is Oracle reversing the join order in Case 2 when
> ideally it should be probing the large table into the hash of the
> small table?
>
> Regards,
> Pratap
>
>

It's not the number of rows that counts, it's
the total size of the hash table. According to
the figures "large_table" will supply 2007M of
data, and "small_table" will supply 8062M,
which makes "large_table" the smaller data
set ... hence the build table.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


From: pratap.deshmukh on 27 Jun 2008 18:17
Thanks a lot, that helps!
 | 
Pages: 1
Prev: WPG_DOCLOAD Package
Next: Insert performance and autoextend