oracle数据库大表查询,求助Oracle大表查询优化

weixin_39528467

于 2021-04-03 10:02:30 发布

237

文章标签： oracle数据库大表查询

本帖最后由 javaxsky 于 2013-4-9 10:16 编辑

请教你写关于数据库大表查询优化问题；

我现在有张表，数据保存一个星期的，每天数据大概有2000W左右按小时去分区，创建分区索引查询时，感觉效率还是很低不能接收，能否帮我分析下。

查询条件BEGINTIME and USERLOGINNAME or AP_ID or AC_ID，表创建的组合索引【BEGINTIME , AC_ID,AP_ID,USERLOGINNAME】,分区主键BEGINTIME

关于查询的执行计划：

select * from tpr_station_user_list a where a.begintime >= to_date('2013-04-06 14:00:00', 'yyyy-mm-dd hh24:mi:ss') and a.begintime

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 73706609

--------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 71 | 25418 | 9 (45)| 00:00:01 | | |

| 1 | PARTITION RANGE ITERATOR | | 71 | 25418 | 9 (45)| 00:00:01 | 6 | 8 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TPR_STATION_USER_LIST | 71 | 25418 | 9 (45)| 00:00:01 | 6 | 8 |

|* 3 | INDEX RANGE SCAN | IDX_TPR_STATION_USER_LIST | 7 | | 8 (50)| 00:00:01 | 6 | 8 |

PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------

3 - access("A"."BEGINTIME">=TO_DATE('2013-04-06 14:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "A"."USERLOGINNAME" LIKE

PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 'xxxxxx %' AND "A"."BEGINTIME"

filter("A"."USERLOGINNAME" LIKE 'xxxxxx%')

17 rows selected.