问题:
1)table A 索引1 A.a,A.b,A.c
select * from table A where A.a = 1 and A.b = 2 and A.c = 3 可以用到索引12)
select * from table A,table B where A.a = B.a and A.b = B.b and A.c = B.c 就用不上索引1了。
其中 A表的数据量很大,B表只有60条数据。这个现象正常吗???
执行计划如下。
1)
3 -----------------------------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
5 -----------------------------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 4 | 232 | 9 (12)| 00:00:01 | | |
7 | 1 | SORT GROUP BY | | 4 | 232 | 9 (12)| 00:00:01 | | |
8 | 2 | MERGE JOIN CARTESIAN | | 70 | 4060 | 8 (0)| 00:00:01 | | |
9 | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| TP_H_SENBETSU_SHIWAKE | 1 | 53 | 5 (0)| 00:00:01 | 1 | 1 |
10 |* 4 | INDEX RANGE SCAN | IDX1_TP_H_SENBETSU_SHIWAKE | 1 | | 4 (0)| 00:00:01 | | |
11 | 5 | BUFFER SORT | | 141 | 705 | 4 (25)| 00:00:01 | | |
12 |* 6 | INDEX FAST FULL SCAN | MP_TOKEI_KENSYU_SYOSAI1_PK | 141 | 705 | 3 (0)| 00:00:01 | | |
13 -----------------------------------------------------------------------------------------------------------------------------------2)
3 -------------------------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
5 -------------------------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 68209 | 4662K| | 52915 (1)| 00:10:35 | | |
7 | 1 | SORT GROUP BY | | 68209 | 4662K| 23M| 52915 (1)| 00:10:35 | | |
8 |* 2 | HASH JOIN | | 189K| 12M| | 51084 (1)| 00:10:14 | | |
9 |* 3 | INDEX FAST FULL SCAN | MP_TOKEI_KENSYU_SYOSAI1_PK | 141 | 2397 | | 3 (0)| 00:00:01 | | |
10 | 4 | PARTITION RANGE SINGLE| | 2500K| 126M| | 51070 (1)| 00:10:13 | 1 | 1 |
11 |* 5 | TABLE ACCESS FULL | TP_H_SENBETSU_SHIWAKE | 2500K| 126M| | 51070 (1)| 00:10:13 | 1 | 1 |
12 -------------------------------------------------------------------------------------------------------------------------------
1)table A 索引1 A.a,A.b,A.c
select * from table A where A.a = 1 and A.b = 2 and A.c = 3 可以用到索引12)
select * from table A,table B where A.a = B.a and A.b = B.b and A.c = B.c 就用不上索引1了。
其中 A表的数据量很大,B表只有60条数据。这个现象正常吗???
执行计划如下。
1)
3 -----------------------------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
5 -----------------------------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 4 | 232 | 9 (12)| 00:00:01 | | |
7 | 1 | SORT GROUP BY | | 4 | 232 | 9 (12)| 00:00:01 | | |
8 | 2 | MERGE JOIN CARTESIAN | | 70 | 4060 | 8 (0)| 00:00:01 | | |
9 | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| TP_H_SENBETSU_SHIWAKE | 1 | 53 | 5 (0)| 00:00:01 | 1 | 1 |
10 |* 4 | INDEX RANGE SCAN | IDX1_TP_H_SENBETSU_SHIWAKE | 1 | | 4 (0)| 00:00:01 | | |
11 | 5 | BUFFER SORT | | 141 | 705 | 4 (25)| 00:00:01 | | |
12 |* 6 | INDEX FAST FULL SCAN | MP_TOKEI_KENSYU_SYOSAI1_PK | 141 | 705 | 3 (0)| 00:00:01 | | |
13 -----------------------------------------------------------------------------------------------------------------------------------2)
3 -------------------------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
5 -------------------------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 68209 | 4662K| | 52915 (1)| 00:10:35 | | |
7 | 1 | SORT GROUP BY | | 68209 | 4662K| 23M| 52915 (1)| 00:10:35 | | |
8 |* 2 | HASH JOIN | | 189K| 12M| | 51084 (1)| 00:10:14 | | |
9 |* 3 | INDEX FAST FULL SCAN | MP_TOKEI_KENSYU_SYOSAI1_PK | 141 | 2397 | | 3 (0)| 00:00:01 | | |
10 | 4 | PARTITION RANGE SINGLE| | 2500K| 126M| | 51070 (1)| 00:10:13 | 1 | 1 |
11 |* 5 | TABLE ACCESS FULL | TP_H_SENBETSU_SHIWAKE | 2500K| 126M| | 51070 (1)| 00:10:13 | 1 | 1 |
12 -------------------------------------------------------------------------------------------------------------------------------
解决方案 »
- 查询oracle对象创建时间/编译时间?
- oracle 树形表数据转换
- 工作时间 计算问题
- 请问这样的表如何创建?
- IP地址比较
- 怎样在SQLPLUS命令行中查看某个 TABLESPACE的DATAFILES?
- 开发文件上传保存到Oracle时,出现“ORA-03113: 通信通道的文件结束”错误。
- 關於Java中調用Oracle存儲過程,其中參數是IndexTable類型的問題
- 如何实现id自增一?
- 远程服务器的oracle在一段时间不连接,在连接后会产生异常,
- 写oracle的java sources,用到其他的jar 包?该怎么办?
- oracle开发JAVA存储过程,怎么引入外部的JAR包??
走FTS,却只返回了2.7%的数据,明显不正常
换用b表做驱动
然后,sql < SELECT * FROM TABLE(dbms_xplan.display);
有什么问题吗? 这事我第一个数据库项目,指教。呵呵。
收集过统计信息吗?执行下面这个sql呢?
select /*+ ordered use_nl(B,A) */ *
from table2 B,table1 A
where A.a = B.a and A.b = B.b and A.c = B.c注意写sql时from后面的表顺序一定同hint一样。
效果一样,还是没有用上索引。
我的sql语句没有问题,from 大表A,小表B where 也是索引的顺序。就是不知道为什么 表关联就用不上索引了。。
那你就错了。你没看明白我的那个sql,我的sql跟你说的恰恰相反。
你把你真实的sql贴出来吧。
where子句中条件与联合索引中列位置顺序无关,主要是看是否出现了前导列。
B.ROSEN_CD,
B.ROSEN_JOSYAEKI_CD,
B.ROSEN_KOSYAEKI_CD,
B.UP_DOWN_KBN,
SUM(B.DAI_JOSYA_JININ) AS DAI_JOSYA_JININ,
SUM(B.SYO_JOSYA_JININ) AS SYO_JOSYA_JININ
FROM SHIWAKE B, SYOSAI A
WHERE B.GETSUJI_KBN = 0
AND B.KEIJO_YM >= 200709
AND B.KEIJO_YM <= 200709
AND B.KENSYU4_SYU = '210'
AND B.KENSYU5_SYU = '210'
AND B.KENSYU6_SYU = '210'
AND A.PATTERN_ID <> '1'
GROUP BY A.KENSYU_GRP_CD,
B.ROSEN_CD,
B.ROSEN_JOSYAEKI_CD,
B.ROSEN_KOSYAEKI_CD,
B.UP_DOWN_KBN
ORDER BY A.KENSYU_GRP_CD,
B.ROSEN_CD,
B.ROSEN_JOSYAEKI_CD,
B.ROSEN_KOSYAEKI_CD,
B.UP_DOWN_KBN;这个就可以用上索引。但是把红色字改为。
AND A.KENSYU4_SYU = B.KENSYU4_SYU
AND A.KENSYU5_SYU = B.KENSYU5_SYU
AND A.KENSYU6_SYU = B.KENSYU6_SYU就用不上索引了from语句不是 大表在前,小表在后 效率高吗??????
COUNT(*)
----------
94
SQL> select count(*) from tbl_trade_dtl_tmp;
COUNT(*)
----------
17731974SQL> select count(*)
2 from b, tbl_trade_dtl_tmp a
3 where b.stock_code = a.stock_code
4 and b.trade_date = a.trade_date
5 ;
COUNT(*)
----------
75461
SELECT STATEMENT, GOAL = ALL_ROWS 478 473 31126243 1 33
SORT AGGREGATE 1 33
NESTED LOOPS 478 473 31126243 138031 4555023
TABLE ACCESS FULL TECDEV03 B 3 3 44466 94 1598
INDEX RANGE SCAN TECDEV03 IDX_TRADE_DTL_TMP_01 5 5 330657 1468 23488 "B"."STOCK_CODE"="A"."STOCK_CODE" AND "B"."TRADE_DATE"="A"."TRADE_DATE" 2 说了要拿b表做驱动
Plan hash value: 3802401429--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 478 (2)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 33 | | |
| 2 | NESTED LOOPS | | 138K| 4448K| 478 (2)| 00:00:06 |
| 3 | TABLE ACCESS FULL| B | 94 | 1598 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_TRADE_DTL_TMP_01 | 1468 | 23488 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 4 - access("A"."STOCK_CODE"="B"."STOCK_CODE" AND
"A"."TRADE_DATE"="B"."TRADE_DATE")
B.ROSEN_CD,
B.ROSEN_JOSYAEKI_CD,
B.ROSEN_KOSYAEKI_CD,
B.UP_DOWN_KBN,
SUM(B.DAI_JOSYA_JININ) AS DAI_JOSYA_JININ,
SUM(B.SYO_JOSYA_JININ) AS SYO_JOSYA_JININ
FROM VMP_TOKEI_KENSYU_SYOSAI A ,TP_H_SENBETSU_SHIWAKE B 大表
WHERE B.GETSUJI_KBN = 0
AND B.KEIJO_YM >= 200709
AND B.KEIJO_YM <= 200709
AND B.KENSYU4_SYU = A.KENSYU4_SYU
AND B.KENSYU5_SYU = A.KENSYU5_SYU
AND B.KENSYU6_SYU = A.KENSYU6_SYU换了下顺序
AND A.PATTERN_ID <> '1'
GROUP BY A.KENSYU_GRP_CD,
B.ROSEN_CD,
B.ROSEN_JOSYAEKI_CD,
B.ROSEN_KOSYAEKI_CD,
B.UP_DOWN_KBN
ORDER BY A.KENSYU_GRP_CD,
B.ROSEN_CD,
B.ROSEN_JOSYAEKI_CD,
B.ROSEN_KOSYAEKI_CD,
B.UP_DOWN_KBN;1 Plan hash value: 238262145
2
3 -------------------------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
5 -------------------------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 68209 | 4662K| | 52915 (1)| 00:10:35 | | |
7 | 1 | SORT GROUP BY | | 68209 | 4662K| 23M| 52915 (1)| 00:10:35 | | |
8 |* 2 | HASH JOIN | | 189K| 12M| | 51084 (1)| 00:10:14 | | |
9 |* 3 | INDEX FAST FULL SCAN | MP_TOKEI_KENSYU_SYOSAI1_PK | 141 | 2397 | | 3 (0)| 00:00:01 | | |
10 | 4 | PARTITION RANGE SINGLE| | 2500K| 126M| | 51070 (1)| 00:10:13 | 1 | 1 |
11 |* 5 | TABLE ACCESS FULL | TP_H_SENBETSU_SHIWAKE | 2500K| 126M| | 51070 (1)| 00:10:13 | 1 | 1 |
12 -------------------------------------------------------------------------------------------------------------------------------还是没用上
我晕,你这个GETSUJI_KBN是什么东西,把它放索引里干吗,还放在第一位
还有KEIJO_YM,这个是partition key吧,如果是local index的话,也不用放索引里
是自己定义的索引把?
为什么这2个条件不改放在索引里。