Oracle DBA Forums > Ordered Hint and Table Join Sequence

Full Version: Ordered Hint and Table Join Sequence

Oracle DBA Forums > Oracle > Oracle Forum

brennan

Jul 21 2008, 03:50 AM

Let's say I have three tables.

Table 1 contains 10000 rows
Table 2 contains 5000 rows
Table 3 contains 4000 rows

When joining table 1 and 2, it will return 2000 rows.
If i used ORDERED hint, which table whould be better to be placed as driving table? Table will the least rows? or joined tables which return the smallest result set (for example, table 1 and table 2 joined return 2000 rows)?

Which table sequence is better.

Sequence 1
SELECT /*+ ORDERED*/ column a.1, b.2, c.3
FROM table_1 a
, table_2 b
, table_3 c
WHERE a.column1=b.column2
and a.column3 = 2008
and a.column1 = c.column1

Sequence 2
SELECT /*+ ORDERED*/ column a.1, b.2, c.3
FROM table_2 a
, table_1 b
, table_3 c
WHERE a.column1=b.column2
and a.column3 = 2008
and a.column1 = c.column1

Sequence 3
SELECT /*+ ORDERED*/ column a.1, b.2, c.3
FROM table_3 a
, table_2 b
, table_1 c
WHERE a.column1=b.column2
and a.column3 = 2008
and a.column1 = c.column1

SteveC

Jul 21 2008, 11:02 AM

You want to have the smallest result set to work with up front, or looked at another way, eliminate as much data as you can early on. It won't necessarily be driven by the size of the table, but more from what is returned from it. Also, the relative sizes of 4, 5 and 10K may be insignificant. When you deal with millions or tens of millions, you will see a more pronounced difference in performance.

brennan

Jul 21 2008, 11:07 AM

4k, 5k and 10k are just examples. I am dealing with million records for each table. Therefore, I would like to know whether the driving table should be the one with the least rows or the one which returns the smallest result set.

Thanks for your reply

SteveC

Jul 21 2008, 11:20 AM

QUOTE

Join order can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work to access rows that do not affect the result. This leads to three general rules:

*Avoid a full-table scan if it is more efficient to get the required rows through an index.
*Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.
*Choose the join order so as to join fewer rows to tables later in the join order.

HAL9000

Jul 21 2008, 11:22 AM

"If i used ORDERED hint, which table whould be better to be placed as driving table?"

Good question! It depends on what you are optimizing for.

- If you are optimizing for least resources (I/O), just "set autotrace on", run the query, and choose the one with the smallest amount of consistent gets.

- If you are optimizing for response time, "set timing on" and see which runs fastest

Good overview here:

http://www.dba-oracle.com/t_table_join_order.htm

brennan

Jul 21 2008, 08:27 PM

Is there any impact/difference for the table sequence in both SQLs?

SELECT /*+ ORDERED*/ column a.1, b.2
FROM table_1 a
, table_2 b
WHERE a.column1=b.column2

SELECT /*+ ORDERED*/ column a.1, b.2
FROM table_2 a
, table_1 b
WHERE a.column1=b.column2

SteveC

Jul 21 2008, 09:36 PM

What does an explain plan for each show?

brennan

Jul 22 2008, 12:51 AM

Based on the execution plans below, which one is better?

CODE

SELECT /*+ ordered*/
                     a.client_id
                   , b.policy_number
                   , b.plan
            FROM client_base a, policy_base b
          WHERE a.policy_number = b.policy_number
              AND a.client_sex = 'F'

Description Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 9642 1124632 41611384
HASH JOIN 9642 1124632 41611384
INDEX FAST FULL SCAN IX1_POLICY 3560 2132420 23456620
TABLE ACCESS FULL client_base 3340 2849 74074

CODE

SELECT /*+ ordered*/
                     a.client_id
                   , b.policy_number
                   , b.plan
            FROM policy_base b, client_base a
          WHERE a.policy_number = b.policy_number
              AND a.client_sex = 'F'

Description Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 7000 1124632 41611384
HASH JOIN 7000 1124632 41611384
TABLE ACCESS FULL client_base 3340 2849 74074
INDEX FAST FULL SCAN IX1_POLICY 3560 2132420 23456620

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.