关于oralce一条查询语句的优化问题 没必要,和分区没关系重复数据高达500万,这个没什么价值如果表更新得比较频繁,普通索引就好了,不要压缩count(distinct pro_inst_id)是多少?另外,给个执行计划 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 COUNT(DISTINCTPRO_INST_ID)-------------------------- 1828041 select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 3593557855--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 198 | 103 (1)| 00:00:02 ||* 1 | TABLE ACCESS STORAGE FULL| SYS_WF_HISTORY | 2 | 198 | 103 (1)| 00:00:02 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - storage(TO_NUMBER("PRO_INST_ID")=383004) filter(TO_NUMBER("PRO_INST_ID")=383004)14 rows selected.Elapsed: 00:00:00.08Execution Plan----------------------------------------------------------Plan hash value: 3013799171---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 || 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Statistics---------------------------------------------------------- 42 recursive calls 22 db block gets 554 consistent gets 0 physical reads 0 redo size 1434 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processed 1,看执行计划发现这里发生了转换TO_NUMBER("PRO_INST_ID"),所以需要建立函数索引2,700万的数据,只看一个字段是重复的说明不了什么。建议改成分区表 不错,这个字段选择性很高,普通索引即可。问题出在字段是字符型,但是传值却用了数值型。解决方法:稍微改下写法,例如pro_inst_id='30283'即,sql条件里,在这个地方,值的两边加上引号,变成字符串或者,将这个字段类型改成数字 对的,刚才我加引号执行试了一下,真心快,看来没必要做分区表了select * from sys_wf_history where PRO_INST_ID = '383004';执行时间是0.172秒,感谢各位,真心感谢 初学ORACLE的问题:打不开管理控制台 可以用pl/sql连到xx实例上,怎样知道这个实例的ip oracle如何掌握盗版用户? 如何下载oracle? 一个很难实现的脚本! 关于oracle存储过程互相调用编译出错的问题。。 关于储存过程varchar2类型的为操作的问题? 这个oracle触发器怎么做? 存储过程报错,PLS-00905,会是什么原因? oracle 1053 服务没有及时响应启动或控制请求 SQL 语句 在删除和创建11g dbconsole 提示oracle.sysman.emcp.exception.EMConfigException: 数据库实例不可用
--------------------------
1828041 select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3593557855--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 198 | 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS STORAGE FULL| SYS_WF_HISTORY | 2 | 198 | 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - storage(TO_NUMBER("PRO_INST_ID")=383004)
filter(TO_NUMBER("PRO_INST_ID")=383004)14 rows selected.Elapsed: 00:00:00.08Execution Plan
----------------------------------------------------------
Plan hash value: 3013799171---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
42 recursive calls
22 db block gets
554 consistent gets
0 physical reads
0 redo size
1434 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
2,700万的数据,只看一个字段是重复的说明不了什么。
建议改成分区表
即,sql条件里,在这个地方,值的两边加上引号,变成字符串
或者,将这个字段类型改成数字
select * from sys_wf_history where PRO_INST_ID = '383004';
执行时间是0.172秒,感谢各位,真心感谢