oracle多表关联索引用法,多表之间的关联查询为什么没有使用上索引（在线等）...

最新推荐文章于 2023-05-05 16:28:06 发布

奶特烈

最新推荐文章于 2023-05-05 16:28:06 发布

阅读量1.2k

点赞数

文章标签： oracle多表关联索引用法

本文通过创建和填充三个表a、b和c，展示了在Oracle中多表关联查询的不同执行计划。通过设置optimizer_goal为rule、choose、first_rows和all_rows，观察查询的性能变化，探讨索引在不同查询优化策略下的使用情况。

摘要由CSDN通过智能技术生成

SQL> create table a(aid number,name varchar(10),constraint pk_a_id primary key(a

id));

表已创建。

SQL> create table b(bid number,name varchar(10),constraint pk_b_id primary key(b

id));

表已创建。

SQL> create table c(cid number,aid number,bid number,constraint pk_c_id primary

key(cid));

表已创建。

SQL> create index i_c_aid on c(aid);

索引已创建。

SQL> create index i_c_bid on c(bid);

索引已创建。

SQL> alter session set optimizer_goal=rule

2 ;

会话已更改。

SQL> insert into a values(1,'first');

已创建 1 行。

SQL> insert into a values(2,'second');

已创建 1 行。

SQL> insert into b values(1,'bfirst');

已创建 1 行。

SQL> insert into b values(2,'bsecond');

已创建 1 行。

SQL> insert into c values(1,1,1);

已创建 1 行。

SQL> insert into c values(2,1,2);

已创建 1 行。

SQL> insert into c values(3,2,2);

已创建 1 行。

SQL> insert into c values(4,2,1);

已创建 1 行。

SQL> select * from a;

AID NAME

---------- ----------

1 first

2 second

SQL> select * from b;

BID NAME

---------- ----------

1 bfirst

2 bsecond

SQL> select * from c;

CID AID BID

---------- ---------- ----------

1 1 1

2 1 2

3 2 2

4 2 1

SQL> set autotrace on

SQL> select a.aid,b.bid,c.cid from a,b,c where c.aid=a.aid and c.bid=b.bid;

AID BID CID

---------- ---------- ----------

1 1 1

1 2 2

2 2 3

2 1 4

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 NESTED LOOPS

2 1 NESTED LOOPS

3 2 TABLE ACCESS (FULL) OF 'C'

4 2 INDEX (UNIQUE SCAN) OF 'PK_B_ID' (UNIQUE)

5 1 INDEX (UNIQUE SCAN) OF 'PK_A_ID' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

548 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed

SQL> alter session set optimizer_goal=choose;

会话已更改。

SQL> select a.aid,b.bid,c.cid from a,b,c where c.aid=a.aid and c.bid=b.bid;

AID BID CID

---------- ---------- ----------

1 1 1

1 2 2

2 2 3

2 1 4

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 NESTED LOOPS

3 2 TABLE ACCESS (FULL) OF 'C'

4 2 INDEX (UNIQUE SCAN) OF 'PK_B_ID' (UNIQUE)

5 1 INDEX (UNIQUE SCAN) OF 'PK_A_ID' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

548 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed

SQL> alter session set optimizer_goal=first_rows;

会话已更改。

SQL> select a.aid,b.bid,c.cid from a,b,c where c.aid=a.aid and c.bid=b.bid;

AID BID CID

---------- ---------- ----------

1 1 1

1 2 2

2 2 3

2 1 4

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=132 Card=82 Byte

s=5330)

1 0 NESTED LOOPS (Cost=132 Card=82 Bytes=5330)

2 1 NESTED LOOPS (Cost=132 Card=82 Bytes=4264)

3 2 VIEW OF 'index$_join$_003' (Cost=132 Card=82 Bytes=319

4 3 HASH JOIN

5 4 HASH JOIN

6 5 INDEX (FAST FULL SCAN) OF 'I_C_AID' (NON-UNIQUE)

(Cost=33 Card=82 Bytes=3198)

7 5 INDEX (FAST FULL SCAN) OF 'I_C_BID' (NON-UNIQUE)

(Cost=33 Card=82 Bytes=3198)

8 4 INDEX (FAST FULL SCAN) OF 'PK_C_ID' (UNIQUE) (Cost

=33 Card=82 Bytes=3198)

9 2 INDEX (UNIQUE SCAN) OF 'PK_A_ID' (UNIQUE)

10 1 INDEX (UNIQUE SCAN) OF 'PK_B_ID' (UNIQUE)

Statistics

----------------------------------------------------------

102 recursive calls

0 db block gets

19 consistent gets

0 physical reads

0 redo size

548 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed

SQL> alter session set optimizer_goal=all_rows;

会话已更改。

SQL> select a.aid,b.bid,c.cid from a,b,c where c.aid=a.aid and c.bid=b.bid;

AID BID CID

---------- ---------- ----------

1 1 1

1 2 2

2 2 3

2 1 4

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=82 Bytes=53

30)

1 0 NESTED LOOPS (Cost=2 Card=82 Bytes=5330)

2 1 NESTED LOOPS (Cost=2 Card=82 Bytes=4264)

3 2 TABLE ACCESS (FULL) OF 'C' (Cost=2 Card=82 Bytes=3198)

4 2 INDEX (UNIQUE SCAN) OF 'PK_A_ID' (UNIQUE)

5 1 INDEX (UNIQUE SCAN) OF 'PK_B_ID' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

548 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed

楼主,没法模拟你的情况,把表分析了也不行,你在SQLPlus下执行一下,把执行计划贴出来.