About hint "leading"

最新推荐文章于 2024-08-21 21:03:14 发布

转载最新推荐文章于 2024-08-21 21:03:14 发布 · 1.1k 阅读

文章标签：

#table #access #join #oracle

oracle 专栏收录该内容

47 篇文章

订阅专栏

本文通过创建四个表并使用不同的驱动表进行连接查询，展示了Oracle如何根据指定的驱动表优化查询路径。通过设置查询注释来引导Oracle从特定表开始连接过程，实现更高效的查询执行计划。

From link: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:30441579130547

The driving tables, the first tables accessed.

when you join t1 to t2 to t3 to t4 we could go

t3 -> t4 -> t2 -> t1
t1 -> t2 -> t3 -> t4
t4 -> t3 -> t2 -> t1

and so on -- leading says "use this table to start the join chain"

ops$tkyte@ORA9IR2> create table t1 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2> create table t2 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2> create table t3 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2> create table t4 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ leading( t3 ) */ *
2    from t1, t2, t3, t4
3   where t1.x = t2.y
4     and t2.x = t3.y
5     and t3.x = t4.y
6 /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=82 Bytes=8528)
   1    0   HASH JOIN (Cost=11 Card=82 Bytes=8528)
   2    1     HASH JOIN (Cost=8 Card=82 Bytes=6396)
   3    2       HASH JOIN (Cost=5 Card=82 Bytes=4264)
   4    3         TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=82 Bytes=2132)
   5    3         TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=2132)
   6    2       TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=2132)
   7    1     TABLE ACCESS (FULL) OF 'T4' (Cost=2 Card=82 Bytes=2132)

Here we said "start with t3", So Oracle is going to drive with T3, join it to T2, join
that to T1 and then join all of that with T4...

ops$tkyte@ORA9IR2> select /*+ leading( t4 ) */ *
2    from t1, t2, t3, t4
3   where t1.x = t2.y
4     and t2.x = t3.y
5     and t3.x = t4.y
6 /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=82 Bytes=8528)
   1    0   HASH JOIN (Cost=11 Card=82 Bytes=8528)
   2    1     HASH JOIN (Cost=8 Card=82 Bytes=6396)
   3    2       HASH JOIN (Cost=5 Card=82 Bytes=4264)
   4    3         TABLE ACCESS (FULL) OF 'T4' (Cost=2 Card=82 Bytes=2132)
   5    3         TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=82 Bytes=2132)
   6    2       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=2132)
   7    1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=2132)

ops$tkyte@ORA9IR2> set autotrace off

Now we said to start with T4, and Oracle goes T4 to T3 to T2 to T1....