Shulou(Shulou.com)06/01 Report--

Question: ask how to write HINT

I have a SQL that adds the following hint to specify the hash_join mode.

Select / * + ordered use_hash (a _

From a,b,c,d

Where...

Among them

An is only related to b, b is only related to c, b is only related to c, and c is only related to d

Order of magnitude: 1000 aRanges, 1 million bres, 8 million cRADs, and 1 million dazzles.

The implementation plan is:

Hash Join

-Hash Join

-Hash Join

-a

-b

-c

-d

Considering that the d-table is relatively small, can I use the d-table as the driver table and the result of the association with a prob b and c as the appearance of the table?

There seems to be no way to control this through Ordered. With the addition, memory can only be loaded with an as the driver table and b as the prob table associated with it.

The result is then used as a driver table, and so on.

Can I use Leading? Please give me the grammar. Thank you.

Answer: oracle 10g

Hash_join can forcibly control build tables through no_swap_join_inputs/swap_join_inputs, and with leading or ordered, you can control the join order before multiple tables.

For example, T1, T2, T2, T3, T4, a total of 4 tables do hash_join.

It can be achieved through ordered+no_swap_join_inputs/swap_join_inputs.

such as

If you want to achieve

(T3 hash-join (T1 hash-join T2)) hash-join T4

T1 is used as build table and T2 as hash_join, and then T3 is used as the result set of build table and T2 as hash_join, and the result set of T1 and T2 is used as build table and T4 as hash_join.

Through sql, you can write as

MYDB@MYDB10G > select

2 / * +

3 ordered

4 use_hash (T2)

5 use_hash (T3)

6 swap_join_inputs (T3)

7 use_hash (T4)

8 no_swap_join_inputs (T4)

9 * /

10 * from T1, T2, T2, T3, T4

11 where t1.object_id=t2.object_id

12 and t2.object_name=t3.object_name

13 and t3.owner=t4.owner

14 and t4.ownerships MYDB'

15 /

Time spent: 00: 00: 00.07

Carry out the plan

Plan hash value: 3494725078

-

| | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 2137 | 801k | | 182 (2) | 00:00:03 |

| | * 1 | HASH JOIN | | 2137 | 801k | | 182 (2) | 00:00:03 |

| | * 2 | HASH JOIN | | 52 | 14976 | | 167,167 (2) | 00:00:03 |

| | * 3 | TABLE ACCESS FULL | T3 | 40 | 3840 | | 15 (0) | 00:00:01 |

| | * 4 | HASH JOIN | | 11651 | 2184K | 1232k | 151K (1) | 00:00:02 |

| | 5 | TABLE ACCESS FULL | T1 | 11651 | 1092K | | 15 (0) | 00:00:01 |

| | 6 | TABLE ACCESS FULL | T2 | 11652 | 1092K | | 15 (0) | 00:00:01 |

| | * 7 | TABLE ACCESS FULL | T4 | 41 | 3936 | | 15 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-access ("T3". "OWNER" = "T4". "OWNER")

2-access ("T2". "OBJECT_NAME" = "T3". "OBJECT_NAME")

3-filter ("T3". "OWNER" = 'MYDB')

4-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID")

7-filter ("T4". "OWNER" = 'MYDB')

Note

-

-dynamic sampling used for this statement

Note: ordered means to join according to the order of the tables written after from.

Write hints, separate some clear ideas ~ ordered after from T1, T2, T3, T4 instructions first use T1 as a driver table to connect T2, how to connect? Look at the hint use_hash (T2) behind.

The way to represent join T2 is hash_join;, and then use use_hash (T3) to indicate that the way to join T3 is hash-join, so who makes the build table? Look at the following swap_join_inputs (T3) for T3 to connect the build table with the result set of t1-t2. And so on.

This is how standard hint should be written ~ use_hash (XMagneyMagnez) is not standard, so it just shows the connection mode of XMagneyMagnez.

Ordered is obsolete hints,leading is used to replace ordered ~ leading does not require the writing of sql (the order after from is not required), you can directly define the connection order in leading ~ leading and ordered cannot be used together, and there is no need to use together ~

As for "can you give an example of using Leading instead of Ordered", as mentioned above, leading can be used if the from can not be modified. Usage google, a lot of documents are used for leading: leading is strengthened in 10g ~ the join order of multiple tables can be written directly later, that is to say, the fixed order after from is not needed to use leading.

MYDB@MYDB10G > select

2 / * +

3 leading (T1 T2 T3 T4)

4 use_hash (T2)

5 use_hash (T3)

6 swap_join_inputs (T3)

7 use_hash (T4)

8 no_swap_join_inputs (T4)

9 * / * from T3, T4, T2, T1

10 where t1.object_id=t2.object_id

11 and t2.object_name=t3.object_name

12 and t3.owner=t4.owner

13 and t4.ownerships MYDB'

14 /

Time spent: 00: 00: 00.01

Carry out the plan

Plan hash value: 3494725078

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 2069 | 179k | 57 (4) | 00:00:01 |

| | * 1 | HASH JOIN | | 2069 | 179k | 57 (4) | 00:00:01 |

| | * 2 | HASH JOIN | | 50 | 3100 | 38 (3) | 00:00:01 |

| | * 3 | TABLE ACCESS FULL | T3 | 40 | 1080 | 18 (0) | 00:00:01 |

| | * 4 | HASH JOIN | | 82 | 2870 | 20 (5) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | T1 | 11584 | 248k | 16 (0) | 00:00:01 |

| | 6 | TABLE ACCESS FULL | T2 | 82 | 1066 | 3 (0) | 00:00:01 |

| | * 7 | TABLE ACCESS FULL | T4 | 41 | 1107 | 18 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("T3". "OWNER" = "T4". "OWNER")

2-access ("T2". "OBJECT_NAME" = "T3". "OBJECT_NAME")

3-filter ("T3". "OWNER" = 'MYDB')

4-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID")

7-filter ("T4". "OWNER" = 'MYDB')

MYDB@MYDB10G > select

2 / * +

3 ordered

4 use_hash (T2)

5 use_hash (T3)

6 swap_join_inputs (T3)

7 use_hash (T4)

8 no_swap_join_inputs (T4)

9 * / * from T1, T2, T2, T3, T4

10 where t1.object_id=t2.object_id

11 and t2.object_name=t3.object_name

12 and t3.owner=t4.owner

13 and t4.ownerships MYDB'

14 /

Time spent: 00: 00: 00.00

Carry out the plan

Plan hash value: 3494725078

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 2069 | 179k | 57 (4) | 00:00:01 |

| | * 1 | HASH JOIN | | 2069 | 179k | 57 (4) | 00:00:01 |

| | * 2 | HASH JOIN | | 50 | 3100 | 38 (3) | 00:00:01 |

| | * 3 | TABLE ACCESS FULL | T3 | 40 | 1080 | 18 (0) | 00:00:01 |

| | * 4 | HASH JOIN | | 82 | 2870 | 20 (5) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | T1 | 11584 | 248k | 16 (0) | 00:00:01 |

| | 6 | TABLE ACCESS FULL | T2 | 82 | 1066 | 3 (0) | 00:00:01 |

| | * 7 | TABLE ACCESS FULL | T4 | 41 | 1107 | 18 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("T3". "OWNER" = "T4". "OWNER")

2-access ("T2". "OBJECT_NAME" = "T3". "OBJECT_NAME")

3-filter ("T3". "OWNER" = 'MYDB')

4-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID")

7-filter ("T4". "OWNER" = 'MYDB'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.