两张表,部分字段不同,但建的索引、主键相同,而且都是按照日期来分区。
相同的一条SQL语句:
select ObjectNumber,ObjectName,StartTime from T_M_SectorDOHour
where (ObjectNumber in (select oid from mod_negroup_info where group_id=186544))
and (CityID=12101) and (trunc(StartTime)=to_date('2016-02-15','yyyy-mm-dd'))
其执行计划:
SELECT STATEMENT, GOAL=ALL_ROWS
NESTED LOOPS
PARTITION RANGE ALL
TABLE ACCESS FULL T_M_SECTORDOHOUR
INDEX UNIQUE SCAN PK_MOD_NEGROUP_INFO
看上去貌似没问题。
但另一张表,相同的SQL语句,表名换一下,其执行计划变成了:
SELECT STATEMENT, GOAL=ALL_ROWS
NESTED LOOPS
NESTED LOOPS
INDEX FAST FULL SCAN PK_MOD_NEGROUP_INFO
INDEX RANGE SCAN IDX_T_M_SECTOR1XHOUR1_OBJECT
TABLE ACCESS BY GLOBAL INDEX ROWID T_M_SECTOR1XHOUR
貌似进行了全表扫描,没有用到分区。
但第二张表的索引都是VALID的。索引如下:
IDX_T_M_SECTOR1XHOUR1_OBJECT Normal OBJECTNUMBER
IDX_T_M_SECTOR1XHOUR1_TIME Normal STARTTIME
T_M_SECTOR1XHOUR1_PRIMARY Unique OBJECTNUMBER, STARTTIME, CITYID如果我直接改成查分区,应该可以。但如果要查多天估计就...
请求各位大侠帮忙
相同的一条SQL语句:
select ObjectNumber,ObjectName,StartTime from T_M_SectorDOHour
where (ObjectNumber in (select oid from mod_negroup_info where group_id=186544))
and (CityID=12101) and (trunc(StartTime)=to_date('2016-02-15','yyyy-mm-dd'))
其执行计划:
SELECT STATEMENT, GOAL=ALL_ROWS
NESTED LOOPS
PARTITION RANGE ALL
TABLE ACCESS FULL T_M_SECTORDOHOUR
INDEX UNIQUE SCAN PK_MOD_NEGROUP_INFO
看上去貌似没问题。
但另一张表,相同的SQL语句,表名换一下,其执行计划变成了:
SELECT STATEMENT, GOAL=ALL_ROWS
NESTED LOOPS
NESTED LOOPS
INDEX FAST FULL SCAN PK_MOD_NEGROUP_INFO
INDEX RANGE SCAN IDX_T_M_SECTOR1XHOUR1_OBJECT
TABLE ACCESS BY GLOBAL INDEX ROWID T_M_SECTOR1XHOUR
貌似进行了全表扫描,没有用到分区。
但第二张表的索引都是VALID的。索引如下:
IDX_T_M_SECTOR1XHOUR1_OBJECT Normal OBJECTNUMBER
IDX_T_M_SECTOR1XHOUR1_TIME Normal STARTTIME
T_M_SECTOR1XHOUR1_PRIMARY Unique OBJECTNUMBER, STARTTIME, CITYID如果我直接改成查分区,应该可以。但如果要查多天估计就...
请求各位大侠帮忙
select ObjectNumber,ObjectName,StartTime from T_M_SectorDOHour partition(P20160215)
where (ObjectNumber in (select oid from mod_negroup_info where group_id=186544))
and (CityID=12101)头大...
select * from T_M_Sector1xHour partition(P20160215)
执行计划又是正确的:PARTITION RANGE SINGLE搞不懂
我从A表复制了一样的表到B表,包括表结构、分区、索引。
然后insert into B select * from A where ...
现在发现,B表的分区无效。
SQL>exec dbms_stats.gather_table_stats(user,'t_name',cascade => true) ;
PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
P20160214 244560 7930 0 0
P20160215 244560 7930 0 0
查询里面,如果带上OBJECTNUMBER这个索引列,就不行。其他没有建索引的列,就是正常的,能按分区来查。
这又是为什么呢...