表1:memberinfo(memberid,...)
表2:memRecord(memberid,transTime,..)
重要字段列出来,其它的省略,在这3个列上面创建了索引
查询语句如下:
select * from (Select CurrencyID,memberid as mId , ...(其它列省略) from MEMRECORD
where IsRevocation = 1 and transTime >= to_date('05/13/2011 11/59/59', 'MM/dd/yyyy HH24/MI/ss') and transTime <= to_date('05/15/2011 12/00/00', 'MM/dd/yyyy HH24/MI/ss') and mcomid =64
group by CurrencyID) cc,memberinfo mm where mm.memberid=cc.mId order by mm.currencyid执行计划如下:PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 454407902--------------------------------------------------------------------------------
--------------| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |--------------------------------------------------------------------------------
--------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 144 | 20304 | 19266 (
1)| 00:03:52 || 1 | SORT ORDER BY | | 144 | 20304 | 19266 (
1)| 00:03:52 ||* 2 | HASH JOIN | | 144 | 20304 | 19265 (
1)| 00:03:52 || 3 | VIEW | | 144 | 13824 | 19208 (
1)| 00:03:51 |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| 4 | HASH GROUP BY | | 144 | 5616 | 19208 (
1)| 00:03:51 ||* 5 | TABLE ACCESS BY INDEX ROWID| MEMRECORD | 34569 | 1316K| 19204 (
1)| 00:03:51 ||* 6 | INDEX RANGE SCAN | TRANSTIME | 34579 | | 121 (
1)| 00:00:02 || 7 | TABLE ACCESS FULL | MEMBERINFO | 6496 | 285K| 56 (PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2)| 00:00:01 |--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("MM"."MEMBERID"="CC"."MID")
5 - filter("ISREVOCATION"=1 AND "MCOMID"=64)PLAN_TABLE_OUTPUTMEMRECORD表走了索引,MEMRECORD数据量非常的大,MEMBERINFO保存用户的一些信息,数据比较少
奇怪的是第二个表 并没有走索引,memberid这个列上面建了索引。如何做才能走索引呢,这样数据量大了提高效率
不走索引的话,看了一下数据库执行的Gets,太大了,如果是走索引的话,应该会好很多
表2:memRecord(memberid,transTime,..)
重要字段列出来,其它的省略,在这3个列上面创建了索引
查询语句如下:
select * from (Select CurrencyID,memberid as mId , ...(其它列省略) from MEMRECORD
where IsRevocation = 1 and transTime >= to_date('05/13/2011 11/59/59', 'MM/dd/yyyy HH24/MI/ss') and transTime <= to_date('05/15/2011 12/00/00', 'MM/dd/yyyy HH24/MI/ss') and mcomid =64
group by CurrencyID) cc,memberinfo mm where mm.memberid=cc.mId order by mm.currencyid执行计划如下:PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 454407902--------------------------------------------------------------------------------
--------------| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |--------------------------------------------------------------------------------
--------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 144 | 20304 | 19266 (
1)| 00:03:52 || 1 | SORT ORDER BY | | 144 | 20304 | 19266 (
1)| 00:03:52 ||* 2 | HASH JOIN | | 144 | 20304 | 19265 (
1)| 00:03:52 || 3 | VIEW | | 144 | 13824 | 19208 (
1)| 00:03:51 |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| 4 | HASH GROUP BY | | 144 | 5616 | 19208 (
1)| 00:03:51 ||* 5 | TABLE ACCESS BY INDEX ROWID| MEMRECORD | 34569 | 1316K| 19204 (
1)| 00:03:51 ||* 6 | INDEX RANGE SCAN | TRANSTIME | 34579 | | 121 (
1)| 00:00:02 || 7 | TABLE ACCESS FULL | MEMBERINFO | 6496 | 285K| 56 (PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2)| 00:00:01 |--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("MM"."MEMBERID"="CC"."MID")
5 - filter("ISREVOCATION"=1 AND "MCOMID"=64)PLAN_TABLE_OUTPUTMEMRECORD表走了索引,MEMRECORD数据量非常的大,MEMBERINFO保存用户的一些信息,数据比较少
奇怪的是第二个表 并没有走索引,memberid这个列上面建了索引。如何做才能走索引呢,这样数据量大了提高效率
不走索引的话,看了一下数据库执行的Gets,太大了,如果是走索引的话,应该会好很多
也不能单纯看一下gets大就认为性能存在问题。
你可以先收集一下两表最新的统计数据。
然后再看一下执行计划,oracle自己判断比人为更准确。