现在我有 3表 表 a (id,code,其它字段)
表 b (id,code,其它字段)
表c (id,code,其它字段)现在在 a b c三表中分别给 code 加上了索引create index a_idex on a(code) 像这个写的索引
下面是我要问的问题了 select a.* from a
left join b on a.code=b.code
left join c on c.code=a.code发现只有c表走了索引select a.* from a
left join c on c.code=a.code
left join b on a.code=b.code
发现只有b表走了索引
select * from a
left join c on c.code=a.code
left join b on a.code=b.code
发现不走索引这是什么情况 如何让他们都走索引呢?
索引
表 b (id,code,其它字段)
表c (id,code,其它字段)现在在 a b c三表中分别给 code 加上了索引create index a_idex on a(code) 像这个写的索引
下面是我要问的问题了 select a.* from a
left join b on a.code=b.code
left join c on c.code=a.code发现只有c表走了索引select a.* from a
left join c on c.code=a.code
left join b on a.code=b.code
发现只有b表走了索引
select * from a
left join c on c.code=a.code
left join b on a.code=b.code
发现不走索引这是什么情况 如何让他们都走索引呢?
索引
如果要走索引的话 可强制 /*+index(表,索引)*/
/*+index(表,索引)*/ 这样不是注释吗?
select a.* from a
join b on a.code=b.code
join c on c.code=a.code
SQL> select a.* from a
2 left join b on a.code=b.code
3 left join c on c.code=a.code; ID CODE
---------- ----------
1 10
2 20
3 30
Execution Plan
----------------------------------------------------------
Plan hash value: 2010575168-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 156 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 156 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER| | 3 | 117 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 3 | 78 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | B_IDX | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | C_IDX | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 4 - access("A"."CODE"="B"."CODE"(+))
5 - access("C"."CODE"(+)="A"."CODE")Note
-----
- dynamic sampling used for this statement (level=2)SQL> select a.* from a
2 left join c on c.code=a.code
3 left join b on a.code=b.code; ID CODE
---------- ----------
1 10
2 20
3 30
Execution Plan
----------------------------------------------------------
Plan hash value: 3294154415-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 156 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 156 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER| | 3 | 117 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 3 | 78 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | C_IDX | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | B_IDX | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 4 - access("C"."CODE"(+)="A"."CODE")
5 - access("A"."CODE"="B"."CODE"(+))Note
-----
- dynamic sampling used for this statement (level=2)SQL> select * from a
2 left join c on c.code=a.code
3 left join b on a.code=b.code; ID CODE ID CODE ID CODE
---------- ---------- ---------- ---------- ---------- ----------
1 10
2 20 2 20
3 30 3 30 3 30
Execution Plan
----------------------------------------------------------
Plan hash value: 3001270149---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 234 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 234 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 3 | 156 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | A | 3 | 78 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| C | 1 | 26 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | C_IDX | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | B | 1 | 26 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | B_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 5 - access("C"."CODE"(+)="A"."CODE")
7 - access("A"."CODE"="B"."CODE"(+))Note
-----
- dynamic sampling used for this statement (level=2)
a表作为驱动表,把A表的数据和主要B、C表做外连接,主要做过数据采集,code列的值分布,ORACLE自行判断的。
---查看索引的惟一值和总数,看看索引字段的重复值多少
select DISTINCT_KEYS,NUM_ROWS,CLUSTERING_FACTOR From user_ind_statistics a where a.index_name = '索引名称';建议以下查询出来的数量比较一下,
select count(*) from b ,a where a.code = b.code;
select count(*) from b;
---------
select count(*) from c,a where a.code = b.code;
select count(*) from c;
若以上结果量差异相对小,或许ORACLE会走全表扫描