表,都创建索引了,但是查询速度依然很慢,高手请指教。select sx201008.stmc stmc,(nvl(sx201008.i_kaizhangsl,0)+nvl(sx201009.i_kaizhangsl,0)+nvl(sx201010.i_kaizhangsl,0)+nvl(sx201011.i_kaizhangsl,0)) i_kaizhangsl,'预警分析' title,'2010-11-29' rq,'徐州首创' sx
,'6798023' jhsl,'80' red,'100' yellow
from (select '徐州首创' stmc,0+sum(i_zongyongl) i_kaizhangsl
from IBCS_XZ.ZW_YINGYEZ201008 a
inner join IBCS_XZ.ZW_YINGYEZ_ZB201008 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201008
and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201008
left join
(select '徐州首创' stmc,450776+sum(i_zongyongl) i_kaizhangsl
from IBCS_XZ.ZW_YINGYEZ201009 a
inner join IBCS_XZ.ZW_YINGYEZ_ZB201009 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201009 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201009 on sx201009.stmc=sx201008.stmc
left join
(select '徐州首创' stmc,-17120+sum(i_zongyongl) i_kaizhangsl
from IBCS_XZ.ZW_YINGYEZ201010 a
inner join IBCS_XZ.ZW_YINGYEZ_ZB201010 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201010 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201010 on sx201010.stmc=sx201008.stmc
left join
(select '徐州首创' stmc,sum(i_zongyongl)-16506 i_kaizhangsl
from IBCS_XZ.zw_yingyez a
inner join IBCS_XZ.zw_yingyez_zb b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201011 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201011 on sx201011.stmc=sx201008.stmc
union all
select sx201003.stmc stmc,(nvl(sx201003.i_kaizhangsl,0)+nvl(sx201004.i_kaizhangsl,0)+nvl(sx201005.i_kaizhangsl,0)+nvl(sx201006.i_kaizhangsl,0)+nvl(sx201007.i_kaizhangsl,0)+nvl(sx201008.i_kaizhangsl,0)+nvl(sx201009.i_kaizhangsl,0)+nvl(sx201010.i_kaizhangsl,0)+nvl(sx201011.i_kaizhangsl,0)) i_kaizhangsl,'预警分析' title,'2010-11-29' rq,'铜陵首创' sx
,'43677613' jhsl,'80' red,'100' yellow
from (select '铜陵首创' stmc,0+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201003 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201003 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201003
and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201003
left join
(select '铜陵首创' stmc,75278+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201004 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201004 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201004 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201004 on sx201004.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,142742+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201005 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201005 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201005 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201005 on sx201005.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,55950+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201006 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201006 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201006 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201006 on sx201006.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,-156033+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201007 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201007 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201007 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201007 on sx201007.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,82416+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201008 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201008 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201008 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201008 on sx201008.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,25169+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201009 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201009 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201009 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201009 on sx201009.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,-36730+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201010 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201010 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201010 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201010 on sx201010.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,44304+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.zw_yingyez a
inner join IBCS_TL.zw_yingyez_zb b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201011 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201011 on sx201011.stmc=sx201003.stmc上面这种SQL,在sql server执行时间2S,在oracle执行要9S。
高手请指教..
,'6798023' jhsl,'80' red,'100' yellow
from (select '徐州首创' stmc,0+sum(i_zongyongl) i_kaizhangsl
from IBCS_XZ.ZW_YINGYEZ201008 a
inner join IBCS_XZ.ZW_YINGYEZ_ZB201008 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201008
and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201008
left join
(select '徐州首创' stmc,450776+sum(i_zongyongl) i_kaizhangsl
from IBCS_XZ.ZW_YINGYEZ201009 a
inner join IBCS_XZ.ZW_YINGYEZ_ZB201009 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201009 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201009 on sx201009.stmc=sx201008.stmc
left join
(select '徐州首创' stmc,-17120+sum(i_zongyongl) i_kaizhangsl
from IBCS_XZ.ZW_YINGYEZ201010 a
inner join IBCS_XZ.ZW_YINGYEZ_ZB201010 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201010 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201010 on sx201010.stmc=sx201008.stmc
left join
(select '徐州首创' stmc,sum(i_zongyongl)-16506 i_kaizhangsl
from IBCS_XZ.zw_yingyez a
inner join IBCS_XZ.zw_yingyez_zb b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201011 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201011 on sx201011.stmc=sx201008.stmc
union all
select sx201003.stmc stmc,(nvl(sx201003.i_kaizhangsl,0)+nvl(sx201004.i_kaizhangsl,0)+nvl(sx201005.i_kaizhangsl,0)+nvl(sx201006.i_kaizhangsl,0)+nvl(sx201007.i_kaizhangsl,0)+nvl(sx201008.i_kaizhangsl,0)+nvl(sx201009.i_kaizhangsl,0)+nvl(sx201010.i_kaizhangsl,0)+nvl(sx201011.i_kaizhangsl,0)) i_kaizhangsl,'预警分析' title,'2010-11-29' rq,'铜陵首创' sx
,'43677613' jhsl,'80' red,'100' yellow
from (select '铜陵首创' stmc,0+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201003 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201003 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201003
and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201003
left join
(select '铜陵首创' stmc,75278+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201004 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201004 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201004 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201004 on sx201004.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,142742+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201005 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201005 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201005 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201005 on sx201005.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,55950+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201006 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201006 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201006 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201006 on sx201006.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,-156033+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201007 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201007 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201007 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201007 on sx201007.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,82416+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201008 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201008 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201008 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201008 on sx201008.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,25169+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201009 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201009 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201009 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201009 on sx201009.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,-36730+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201010 a
inner join IBCS_TL.ZW_YINGYEZ_ZB201010 b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201010 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201010 on sx201010.stmc=sx201003.stmc
left join
(select '铜陵首创' stmc,44304+sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.zw_yingyez a
inner join IBCS_TL.zw_yingyez_zb b on a.i_feeid=b.i_feeid
where a.i_jlzt=0 and i_y*100+i_m=201011 and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20) sx201011 on sx201011.stmc=sx201003.stmc上面这种SQL,在sql server执行时间2S,在oracle执行要9S。
高手请指教..
我出一个报表都要要4-5分钟,你可想而知了
上面所有表,数据量大约有2000万
这种SQL语句,在sql server执行速度不到2S钟,在oracle中怎么就这么慢呢(已经建索引了)。
9s 不算什么 感觉你union all后面的可以合并为一个 另外试着建联合索引
2000万数据量,才9s已经相当不错了!
你要相信Oracle的CBO优化模式。
大哥,这一堆在sql中执行也就是2S,在oracle中怎么要这么久啊?
如:用no_query_transformation取消oracle对sql的优化,
用ordered把连接顺序调整为from后面从左向右,
如果是oltp系统,可以考虑用use_nl或use_hash等,
如果是数据仓库,可以考虑用use_merge
等等。
sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201005 a
,IBCS_TL.ZW_YINGYEZ_ZB201005 b
where a.i_jlzt=0
and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20 and i_y=2010 and i_m=5
and a.i_feeid=b.i_feeid两种查询时间相差无几select
sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201005 a
,IBCS_TL.ZW_YINGYEZ_ZB201005 b
where a.i_jlzt=0
and b.i_jlzt=0
and i_xiaozhang<>20 and i_feiyongdlbh=580
and i_y=2010 and i_m=5
and a.i_feeid=b.i_feeida、b两表均建有索引CREATE UNIQUE INDEX INDEX_ZW_YINGYEZ201010_I_FEEID ON IBCS_XZ.ZW_YINGYEZ201010(I_FEEID ASC)
TABLESPACE USERSCREATE INDEX INDEX_ZW_YINGYEZ201010_NY_ST ON IBCS_XZ.ZW_YINGYEZ201010(I_Y ASC,I_M ASC,S_ST ASC)
TABLESPACE USERSCREATE INDEX INDEX_ZW_YINGYEZ201010_CID ON IBCS_XZ.ZW_YINGYEZ201010(S_CID ASC,I_Y ASC,I_M ASC)
TABLESPACE USERSCREATE INDEX INDEX_ZW_YINGYEZ201010_XZRQ ON IBCS_XZ.ZW_YINGYEZ201010(I_XiaoZhang ASC,DL_XiaoZhangRQ ASC)
TABLESPACE USERSCREATE INDEX INDEX_ZW_YINGYEZ201010_XZBH ON IBCS_XZ.ZW_YINGYEZ201010(I_XiaoZhang ASC,I_XZBH ASC)
TABLESPACE USERSCREATE UNIQUE INDEX INDEX_ZW_YINGYEZ_ZB201010_Main ON IBCS_XZ.ZW_YINGYEZ_ZB201010(I_FEEID ASC,I_JH ASC,I_JieTiJB ASC,I_FeiYongID ASC)
TABLESPACE USERS
最好把字段用表名引用一下。
试试下面这个,如果不行再把a、b换一下顺序,再试一下。
select /*+ ordered*/
sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201005 a
,IBCS_TL.ZW_YINGYEZ_ZB201005 b
where a.i_jlzt=0
and b.i_jlzt=0 and i_feiyongdlbh=580
and i_xiaozhang<>20 and i_y=2010 and i_m=5
and a.i_feeid=b.i_feeid
i_feiyongdlbh可以过滤掉大量数据,而且建有索引,这个字段放在i_xiaozhang之后没问题
from IBCS_TL.ZW_YINGYEZ_ZB201005 b, IBCS_TL.ZW_YINGYEZ201005 a
where b.i_feiyongdlbh = 580
and b.i_jlzt = 0
and b.i_feeid = a.i_feeid
and a.i_jlzt = 0
and a.i_xiaozhang <>2 0
and a.i_y = 2010
and a.i_m = 5;
select
sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201005 a
,IBCS_TL.ZW_YINGYEZ_ZB201005 b
where a.i_jlzt=0
and b.i_jlzt=0
and i_xiaozhang<>20 and i_feiyongdlbh=580
and i_y=2010 and i_m=5
and a.i_feeid=b.i_feeid
向您学习~~
如果只能用命令,那就上网搜一搜oracle分析命令怎么用的。
我平时都用Toad for Oracle的,命令怎么写不记得了。
在Toad For Oracle菜单有三个子菜单:一个Document(dbf文档)、一个Toad For Oracle 9.7(开发工具)、一个Tools(包含Quest Script Runner、SQL Monitor);这个SQL Monitor就是你说的分析SQL的工具吧。
这是一个分析平台,总部监控子公司的系统(每天定时同步子公司sql server数据到oracle)
IBCS_XZ ZW_YINGYEZ201009 USERS 146337 4906 86 932 0 238 NO NO 2010-12-7 09:49 40458 YES
IBCS_XZ ZW_YINGYEZ_ZB201009 USERS 512642 4654 82 891 0 63 NO NO 2010-12-7 09:49 144589 YES
Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | | | 54M | | | | | | |
| SORT AGGREGATE | | 1 | 22 | | | | | | | |
| MERGE JOIN CARTESIAN | | 6767M | 145G | 54M | 187:19:37 | | | | | |
| TABLE ACCESS BY INDEX ROWID | ZW_YINGYEZ201010 | 24K | 282K | 880 | 00:00:11 | | | | | |
| INDEX RANGE SCAN | INDEX_ZW_YINGYEZ201010_NY_ST| 24K | | 73 | 00:00:01 | | | | | |
| BUFFER SORT | | 288K | 2876K | 54M | 187:18:27 | | | | | |
| TABLE ACCESS FULL | ZW_YINGYEZ_ZB201010| 288K | 2876K | 2332 | 00:00:28 | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------
括号里加表的别名(注意:这里要别名)。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Plan hash value: 94781680----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 |
32 | | 3688 (3)| 00:00:45 || 1 | SORT AGGREGATE | | 1 |
32 | | | ||* 2 | HASH JOIN | | 35081 |
1096K| 688K| 3688 (3)| 00:00:45 ||* 3 | TABLE ACCESS BY INDEX ROWID| ZW_YINGYEZ201010 | 24093 |
399K| | 881 (1)| 00:00:11 |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | INDEX RANGE SCAN | INDEX_ZW_YINGYEZ201010_NY_ST | 24093 |
| | 73 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL | ZW_YINGYEZ_ZB201010 | 294K|
4313K| | 2331 (3)| 00:00:28 |----------------------------------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("A"."I_FEEID"="B"."I_FEEID")
3 - filter("A"."I_XIAOZHANG"<>20 AND "A"."I_JLZT"=0)
4 - access("A"."I_Y"=2010 AND "A"."I_M"=10)
5 - filter("B"."I_FEIYONGDLBH"=580 AND "B"."I_JLZT"=0)select /*+ index(a,b)*/
sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ201010 a
,IBCS_TL.ZW_YINGYEZ_ZB201010 b
where a.i_jlzt=0
and b.i_jlzt=0
and i_xiaozhang<>20 and i_feiyongdlbh=580
and i_y=2010 and i_m=10
and a.i_feeid=b.i_feeid
sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ_ZB201005 b, IBCS_TL.ZW_YINGYEZ201005 a
where b.i_feiyongdlbh = 580
and b.i_jlzt = 0
and b.i_feeid = a.i_feeid
and a.i_jlzt = 0
and a.i_xiaozhang <>2 0
and a.i_y = 2010
and a.i_m = 5;
我是帮你解决问题,你不要用negative的眼光去看我嘛!
另外NO_QUERY_TRANSFORMATION是不让ORACLE对SQL做等价转换,在有内联视图或子查询的时候ORACLE通常都会做转换的,所以在用HINT优化SQL的时候要加这个是保证内联视图或子查询中的HINT有效。这并不表示没有内联视图或子查询的时候就不用加NO_QUERY_TRANSFORMATION。
如果不用HINT也可以优化SQL的,你可以考虑用关系代数表示一下这条SQL,看看有什么可简化的地方,或者在业务逻辑上入手。事物之间是普遍存在联系的,总有解决问题的办法。
业务逻辑上(我想没有可以优化的地方了,财务报表一般都比较复杂^_^),用关系代数表达式表示(写不来了,忘光了⊙﹏⊙b汗),只能用HINT优化SQL了^_^我很表理解的是,ZW_YINGYEZ_ZB建有索引的,怎么还会全表扫描呢?
你试试给i_feiyongdlbh这一个字段单独建个索引,再重试一下以前试过的那些SQL。
执行计划,开销比较大的一个是ZW_YINGYEZ_ZB全表扫描,一个是HASH JOIN。
执行计划输出的HASH JOIN指的是什么呢?谢谢~~PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Plan hash value: 94781680----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 |
32 | | 3678 (3)| 00:00:45 || 1 | SORT AGGREGATE | | 1 |
32 | | | ||* 2 | HASH JOIN | | 35081 |
1096K| 688K| 3678 (3)| 00:00:45 ||* 3 | TABLE ACCESS BY INDEX ROWID| ZW_YINGYEZ201010 | 24093 |
399K| | 871 (1)| 00:00:11 |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | INDEX RANGE SCAN | INDEX_ZW_YINGYEZ201010_NY_ST | 24093 |
| | 63 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL | ZW_YINGYEZ_ZB201010 | 294K|
4313K| | 2331 (3)| 00:00:28 |----------------------------------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("A"."I_FEEID"="B"."I_FEEID")
3 - filter("A"."I_XIAOZHANG"<>20 AND "A"."I_JLZT"=0)
4 - access("A"."I_Y"=2010 AND "A"."I_M"=10)
5 - filter("B"."I_FEIYONGDLBH"=580 AND "B"."I_JLZT"=0)
SQL> desc zw_yingyez
名称 是否为空? 类型
----------------------------------------- -------- ---------------------------- I_FEEID NOT NULL NUMBER(10)
S_CID NOT NULL CHAR(16 CHAR)
S_ST NOT NULL CHAR(2 CHAR)
S_CH NOT NULL CHAR(10 CHAR)
I_CENEIXH NOT NULL NUMBER(10)
I_BENCICM NOT NULL NUMBER(10)
I_SHANGCICM NOT NULL NUMBER(10)
I_CC NOT NULL NUMBER(10)
I_TJH NOT NULL NUMBER(10)
I_JH NOT NULL NUMBER(10)
I_Y NOT NULL NUMBER(10)
I_M NOT NULL NUMBER(10)
S_HM VARCHAR2(80 CHAR)
S_DZ VARCHAR2(160 CHAR)
S_DIZHIBM VARCHAR2(12 CHAR)
I_SFFS NUMBER(10)
S_TUOSHOUHTH VARCHAR2(50 CHAR)
I_KAIZHANGSL NOT NULL NUMBER(10)
N_JE NOT NULL NUMBER(12,2)
N_YONGSHUIF NOT NULL NUMBER(12,2)
N_PAISHUIF NOT NULL NUMBER(12,2)
N_QITAF NOT NULL NUMBER(12,2)
N_SHANGCIY NOT NULL NUMBER(12,2)
N_LINGTOU NOT NULL NUMBER(12,2)
N_KAIZHANGJE NOT NULL NUMBER(12,2)
D_KAIZHANG NOT NULL TIMESTAMP(3)
DL_KAIZHANG NOT NULL NUMBER(10)
D_JIAOFEIQX NOT NULL TIMESTAMP(3)
D_ZHINAJQS NOT NULL TIMESTAMP(3)
N_YINGSHOUZNJ NOT NULL NUMBER(12,2)
N_SHISHOUZNJ NOT NULL NUMBER(12,2)
I_XIAOZHANG NOT NULL NUMBER(10)
I_CHULI NOT NULL NUMBER(10)
I_SHUILIANCL NOT NULL NUMBER(10)
I_SHOUFEIPH NOT NULL NUMBER(10)
S_SHOUFEIYBH VARCHAR2(10 CHAR)
S_SHOUFEIY VARCHAR2(30 CHAR)
DL_SHOUFEI NUMBER(10)
D_SHOUFEI TIMESTAMP(3)
DL_XIAOZHANGRQ NUMBER(10)
D_XIAOZHANGRQ TIMESTAMP(3)
I_XZBH NUMBER(10)
I_HUAZHANGBH NOT NULL NUMBER(10)
D_CHAOBIAORQ TIMESTAMP(3)
D_XIACICBRQ TIMESTAMP(3)
I_PRINT NOT NULL NUMBER(10)
I_ZHUANGTAIBM NOT NULL NUMBER(10)
S_CHAOBIAOZT VARCHAR2(300 CHAR)
I_JLZT NOT NULL NUMBER(10)
SQL> desc zw_yingyez_zb
名称 是否为空? 类型
----------------------------------------- -------- -------------------- ID NOT NULL NUMBER(10)
I_FEEID NOT NULL NUMBER(10)
S_ST NOT NULL CHAR(2 CHAR)
I_TIAOJAH NUMBER(10)
I_JH NOT NULL NUMBER(10)
I_JIETIJB NOT NULL NUMBER(10)
I_FEIYONGID NOT NULL NUMBER(10)
I_FEIYONGDLBH NOT NULL NUMBER(10)
I_KAISHIY NOT NULL NUMBER(10)
I_JIESHUY NOT NULL NUMBER(10)
I_KAISHINYL NOT NULL NUMBER(10)
I_JIESHUNYL NOT NULL NUMBER(10)
N_JG NUMBER(12,2)
N_XISHU NOT NULL NUMBER(12,2)
N_ZONGJINE NUMBER(12,2)
I_ZONGYONGL NUMBER(10)
N_BILI NOT NULL NUMBER(12,2)
I_JLZT NOT NULL NUMBER(10)
CREATE TABLE IBCS_XZ.ZW_YINGYEZ
(
I_FEEID NUMBER(10) NOT NULL,
S_CID CHAR(16 CHAR) NOT NULL,
S_ST CHAR(2 CHAR) NOT NULL,
S_CH CHAR(10 CHAR) NOT NULL,
I_CENEIXH NUMBER(10) NOT NULL,
I_BENCICM NUMBER(10) NOT NULL,
I_SHANGCICM NUMBER(10) NOT NULL,
I_CC NUMBER(10) NOT NULL,
I_TJH NUMBER(10) NOT NULL,
I_JH NUMBER(10) NOT NULL,
I_Y NUMBER(10) NOT NULL,
I_M NUMBER(10) NOT NULL,
S_HM VARCHAR2(80 CHAR),
S_DZ VARCHAR2(160 CHAR),
S_DIZHIBM VARCHAR2(12 CHAR),
I_SFFS NUMBER(10),
S_TUOSHOUHTH VARCHAR2(50 CHAR),
I_KAIZHANGSL NUMBER(10) NOT NULL,
N_JE NUMBER(12,2) NOT NULL,
N_YONGSHUIF NUMBER(12,2) NOT NULL,
N_PAISHUIF NUMBER(12,2) NOT NULL,
N_QITAF NUMBER(12,2) NOT NULL,
N_SHANGCIY NUMBER(12,2) NOT NULL,
N_LINGTOU NUMBER(12,2) NOT NULL,
N_KAIZHANGJE NUMBER(12,2) NOT NULL,
D_KAIZHANG DATE NOT NULL,
DL_KAIZHANG NUMBER(10) NOT NULL,
D_JIAOFEIQX DATE NOT NULL,
D_ZHINAJQS DATE NOT NULL,
N_YINGSHOUZNJ NUMBER(12,2) NOT NULL,
N_SHISHOUZNJ NUMBER(12,2) NOT NULL,
I_XIAOZHANG NUMBER(10) NOT NULL,
I_CHULI NUMBER(10) NOT NULL,
I_SHUILIANCL NUMBER(10) NOT NULL,
I_SHOUFEIPH NUMBER(10) NOT NULL,
S_SHOUFEIYBH VARCHAR2(10 CHAR),
S_SHOUFEIY VARCHAR2(30 CHAR),
DL_SHOUFEI NUMBER(10),
D_SHOUFEI DATE,
DL_XIAOZHANGRQ NUMBER(10),
D_XIAOZHANGRQ DATE,
I_XZBH NUMBER(10),
I_HUAZHANGBH NUMBER(10) NOT NULL,
D_CHAOBIAORQ DATE,
D_XIACICBRQ DATE,
I_PRINT NUMBER(10) NOT NULL,
I_ZHUANGTAIBM NUMBER(10) NOT NULL,
S_CHAOBIAOZT VARCHAR2(300 CHAR),
I_JLZT NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX YSAS.INDEX_ZW_YINGYEZ_CID ON IBCS_XZ.ZW_YINGYEZ
(S_CID, I_Y, I_M)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX YSAS.INDEX_ZW_YINGYEZ_I_FEEID ON IBCS_XZ.ZW_YINGYEZ
(I_FEEID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX YSAS.INDEX_ZW_YINGYEZ_NY_ST ON IBCS_XZ.ZW_YINGYEZ
(I_Y, I_M, S_ST)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX YSAS.INDEX_ZW_YINGYEZ_XZBH ON IBCS_XZ.ZW_YINGYEZ
(I_XIAOZHANG, I_XZBH)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX YSAS.INDEX_ZW_YINGYEZ_XZRQ ON IBCS_XZ.ZW_YINGYEZ
(I_XIAOZHANG, DL_XIAOZHANGRQ)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON IBCS_XZ.ZW_YINGYEZ TO YSAS;
CREATE TABLE IBCS_XZ.ZW_YINGYEZ_ZB
(
ID NUMBER(10) NOT NULL,
I_FEEID NUMBER(10) NOT NULL,
S_ST CHAR(2 CHAR) NOT NULL,
I_TIAOJAH NUMBER(10),
I_JH NUMBER(10) NOT NULL,
I_JIETIJB NUMBER(10) NOT NULL,
I_FEIYONGID NUMBER(10) NOT NULL,
I_FEIYONGDLBH NUMBER(10) NOT NULL,
I_KAISHIY NUMBER(10) NOT NULL,
I_JIESHUY NUMBER(10) NOT NULL,
I_KAISHINYL NUMBER(10) NOT NULL,
I_JIESHUNYL NUMBER(10) NOT NULL,
N_JG NUMBER(12,2),
N_XISHU NUMBER(12,2) NOT NULL,
N_ZONGJINE NUMBER(12,2),
I_ZONGYONGL NUMBER(10),
N_BILI NUMBER(12,2) NOT NULL,
I_JLZT NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX IBCS_XZ.INDEX_ZW_YINGYEZ_ZB_BH ON IBCS_XZ.ZW_YINGYEZ_ZB
(I_FEIYONGDLBH)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX IBCS_XZ.INDEX_ZW_YINGYEZ_ZB_MAIN ON IBCS_XZ.ZW_YINGYEZ_ZB
(I_FEEID, I_JH, I_JIETIJB, I_FEIYONGID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE IBCS_XZ.ZW_YINGYEZ_ZB ADD (
CONSTRAINT PK_MAIN3
PRIMARY KEY
(I_FEEID, I_JH, I_JIETIJB, I_FEIYONGID));GRANT SELECT ON IBCS_XZ.ZW_YINGYEZ_ZB TO YSAS;
--试一下下面这个SQL:
select /*+ ordered index(b)*/
sum(i_zongyongl) i_kaizhangsl
from IBCS_TL.ZW_YINGYEZ_ZB201005 b, IBCS_TL.ZW_YINGYEZ201005 a
where b.i_feiyongdlbh = 580
and b.i_jlzt = 0
and b.i_feeid = a.i_feeid
and a.i_jlzt = 0
and a.i_xiaozhang <> 20
and a.i_y = 2010
and a.i_m = 5
SELECT STATEMENT, GOAL = ALL_ROWS 6 1 117
SORT AGGREGATE 1 117
NESTED LOOPS 6 1 117
TABLE ACCESS BY INDEX ROWID MICKEY ZW_YINGYEZ_ZB 5 1 52
INDEX RANGE SCAN MICKEY INDEX_ZW_YINGYEZ_ZB_BH 1 1
TABLE ACCESS BY INDEX ROWID MICKEY ZW_YINGYEZ 1 1 65
INDEX UNIQUE SCAN MICKEY INDEX_ZW_YINGYEZ_I_FEEID 0 1 我先下班回家了,还有问题就明天再说了。
你找个dba帮你看看吧!现场技术支持好过我这样远程的,什么也看不到。
我只是一个普通的WEB开发人员,DBA应该比我懂得多。
从这个项目开始,我才关注oracle,不懂的东西很多..
我也在不断的学习
dbms_stats.gather_schema_stats统计信息不准确会影响CBO产生执行计划。
我用exp导出整个数据库的dmp文件(backup.dmp),然后用dbca新建一数据库(全局数据库名orclwqk),然后在cmd下把backup.dmp导入到新创建的数据库(orclwqk),但是会出现很多错误提示。
请问,这是什么原因呢?
大哥,你这个够快了,我那个帖子。100多万的数据汇总一下要5分钟,你是高人哪,快去帮我看看吧,把地址贴给你
http://topic.csdn.net/u/20101210/15/d67d7fd9-b6a5-4ea2-8eab-1a610b821699.html?seed=1877000010&r=70510079#r_70510079
我在上海^_^
any help is appreciated.
只有在相同的存储条件下,才有比较的意义