9I数据库迁移到10G后同样的查询语句为什么执行时间变慢了很多。

Question

---------------在10g下执行10分钟（以前在9i下是10秒就可以出来）--------------------- SQL codeselect  * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM
                                 left join     TIA_BUSY_TIME TIA_BUSY_TIME ON   TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT
                                 where TIA_BUSY_TIME.COMPRESS_DATE = '2010-01-25'
                       
                                  and  TIA_BUSY_TIME.NE_TYPE=10000
------------下面是执行计划-----------------
SELECT STATEMENT, GOAL = CHOOSE 48544 954880 261637120 
HASH JOIN 48544 954880 261637120 
  TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 160 3981 103506 
  INDEX RANGE SCAN NPMDB COMPRESS_DATE_INX 19 3981 
  PARTITION RANGE ALL 48331 6408935 1589415880 
  TABLE ACCESS FULL NPMDB TPA_UNIC_BTS_SUM 48331 6408935 1589415880 
--------------------修改条件执行这样条件查询速度飞快10秒--------------------- 
select  * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM 
                                left join    TIA_BUSY_TIME TIA_BUSY_TIME ON  TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT 
                                where    TIA_BUSY_TIME.FIRST_RESULT = '2010-01-25 19:00:00'  
                                  and  TIA_BUSY_TIME.NE_TYPE=10000 
------------下面是执行计划-----------------
SELECT STATEMENT, GOAL = CHOOSE 371 130673 35804402 
HASH JOIN 371 130673 35804402 
  TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 245 553 14378 
  INDEX RANGE SCAN NPMDB NE_ID_INX 13 553 
  TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_BTS_SUM 124 236 58528 
  INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS 4 236
 ---------------在9i执行很快10秒--------------------- SQL codeselect  * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM
                                 left join     TIA_BUSY_TIME TIA_BUSY_TIME ON   TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT
                                 where TIA_BUSY_TIME.COMPRESS_DATE = '2010-01-25'
                       
                                  and  TIA_BUSY_TIME.NE_TYPE=10000
------------下面是执行计划-----------------
SELECT STATEMENT, GOAL = CHOOSE 205 49768 11994088 
TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_BTS_SUM 2 492 96924 
  NESTED LOOPS 205 49768 11994088 
  TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 3 101 4444 
    INDEX RANGE SCAN NPMDB COMPRESS_DATE_INX 2 16 
  INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS 1 492 
-------------------- 
--------------------修改条件执行这样条件查询速度一样很快10秒--------------------- 
select  * from TPA_UNIC_BTS_SUM TPA_UNIC_BTS_SUM 
                                left join    TIA_BUSY_TIME TIA_BUSY_TIME ON  TIA_BUSY_TIME.FIRST_RESULT =TPA_UNIC_BTS_SUM.FIRST_RESULT 
                                where    TIA_BUSY_TIME.FIRST_RESULT = '2010-01-25 19:00:00'  
                                  and  TIA_BUSY_TIME.NE_TYPE=10000 
------------下面是执行计划-----------------
SELECT STATEMENT, GOAL = CHOOSE 7 106 25546 
HASH JOIN 7 106 25546 
  TABLE ACCESS BY INDEX ROWID NPMDB TIA_BUSY_TIME 3 101 4444 
  INDEX RANGE SCAN NPMDB NE_ID_INX 2 40 
  TABLE ACCESS BY GLOBAL INDEX ROWID NPMDB TPA_UNIC_BTS_SUM 3 492 96924 
  INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS 2 1 我想原因是  9i  两种条件查询 都可以 INDEX RANGE SCAN NPMDB UQ_TPA_UNIC_BTS  所以查询很快，为什么10g 不能这样呢10g硬件条件比较9i是高了很多的。

Answer 1

PARTITION RANGE ALL分区表？

Answer 2

纯属猜测这个表按COMPRESS_DATE做了RANGE PARTITION是吧，
通常RANGE方式分区字段数据类型是number或者date型，可是按照你的sql来看，这个字段似乎是varchar2类型，这种分区可能会有问题，查询时候分区失去效用，无法比较大小？变成了全表扫描而不是分区扫描如果COMPRESS_DATE的值不多，你可以考虑改成LIST PARTITION，如果较多，还是把这个字段数据类型改成date吧

Answer 3

刚刚看了 COMPRESS_DATE 全部都是 date型。。实在是找不到问题原因。 
2个库表和索引全部一样。  上面SQL涉及到的表我也都再次核对过表结构和索引关键字完全一样。

Answer 4

贴出完整的建表语句看看，包括tablespace信息的。

Answer 5

-- Create table
create table TPA_UNIC_BTS_SUM
(
  RECORD_ID             INTEGER not null,
  FIRST_RESULT          DATE not null,
  COMPRESS_DATE         DATE,
  NE_ID                 INTEGER not null,
  NE_TYPE               INTEGER not null,
  TIME_ID               INTEGER not null,
  TIME_TYPE             INTEGER not null,
  BUSY_TYPE             INTEGER default 0 not null,
  SV_CAT_ID             INTEGER default -1 not null,
  SV_ID                 INTEGER default -1 not null,
  NE2_ID                INTEGER default -1 not null,
  NE2_TYPE              INTEGER default -1 not null,
  SUM_LEVEL             INTEGER,
  S_HOUR                INTEGER not null,
  S_WEEKDAY             INTEGER not null,
  S_MONTH               INTEGER not null,
  FLAG_CURRENT          INTEGER,
  FLAG_FRAME            INTEGER,
  CHECK_1               INTEGER,
  CHECK_2               INTEGER,
  CHECK_3               INTEGER,
  CHECK_4               INTEGER,
  COVERED_MINUTES       INTEGER,
  TCH_TRAFFIC           FLOAT,
  TCH_TRAFFIC_H         FLOAT,
  AVAIL_TCH_NBR         FLOAT,
  AVAIL_SDCCH_NBR       FLOAT,
  ATT_TCH_OVRFL         FLOAT,
  ATT_SDCCH_OVRFL       FLOAT,
  SDCCH_TRAFFIC         FLOAT,
  RAND_ACC_ATT          FLOAT,
  RAND_ACC_SUC          FLOAT,
  TCH_CALL_REQ          FLOAT,
  TCH_CALL_SEIZ         FLOAT,
  TCH_CALL_REQ_NHO      FLOAT,
  TCH_SEIZE_NHO         FLOAT,
  DROP_CALL_TCH         FLOAT,
  DROP_CALL_SDCCH       FLOAT,
  TCH_HO_DROP           FLOAT,
  A_SDCCH_SEIZ_HO       FLOAT,
  HO_I_REQ              FLOAT,
  HO_I_SUC              FLOAT,
  TCH_NBR               FLOAT,
  SDCCH_NBR             FLOAT,
  AVAIL_CARRIER         FLOAT,
  BSC_AVE_LOAD          FLOAT,
  BSC_MAX_LOAD          FLOAT,
  TCH_OVERFLOW_NUM      FLOAT,
  BF_HO_REQ             FLOAT,
  BF_S_HO               FLOAT,
  INSERT_TIME           DATE default sysdate,
  WORST_CELL            INTEGER,
  IDLE_CELL             INTEGER,
  BUSY_CELL             INTEGER,
  SUPER_BUSY_CELL       INTEGER,
  OVFL_CELL             INTEGER,
  HEAVY_OVFL_CELL       INTEGER,
  DROP_CALL_TCH_2007    FLOAT,
  TCH_CALL_REQ_NHO_2007 FLOAT,
  TCH_CALL_REQ_2007     FLOAT,
  RAND_ACC_ATT_2007     FLOAT,
  TCH_SEIZE_NHO_2007    FLOAT,
  RAND_ACC_SUC_2007     FLOAT
)
partition by range (S_MONTH)
(
  partition SYS_P4973 values less than (2)
    tablespace APMDBS2
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4974 values less than (3)
    tablespace APMDBS3
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4975 values less than (4)
    tablespace APMDBS4
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4976 values less than (5)
    tablespace APMDBS5
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4977 values less than (6)
    tablespace APMDBS6
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4978 values less than (7)
    tablespace APMDBS7
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4979 values less than (8)
    tablespace APMDBS8
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4980 values less than (9)
    tablespace APMDBS9
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4981 values less than (10)
    tablespace APMDBS10
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4982 values less than (11)
    tablespace APMDBS11
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4983 values less than (12)
    tablespace APMDBS12
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P4984 values less than (13)
    tablespace APMDBS1
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table TPA_UNIC_BTS_SUM
  add constraint PK_TPA_UNIC_BTS primary key (RECORD_ID, S_MONTH)
  using index 
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table TPA_UNIC_BTS_SUM
  add constraint UQ_TPA_UNIC_BTS unique (FIRST_RESULT, NE_ID, NE_TYPE, SUM_LEVEL, SV_ID, SV_CAT_ID, BUSY_TYPE, NE2_ID, NE2_TYPE, S_MONTH)
  using index 
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index IX1_UNIC_BTS_SUM on TPA_UNIC_BTS_SUM (COMPRESS_DATE, NE_ID)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-------------------------------------
-- Create table
create table TIA_BUSY_TIME
(
  FIRST_RESULT  DATE not null,
  COMPRESS_DATE DATE,
  NE_ID         INTEGER not null,
  NE_TYPE       INTEGER not null
)
tablespace NPMDBS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index COMPRESS_DATE_INX on TIA_BUSY_TIME (COMPRESS_DATE, NE_TYPE, NE_ID)
  tablespace NPMDBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index DATE_TIME_INX on TIA_BUSY_TIME (COMPRESS_DATE, NE_TYPE, FIRST_RESULT)
  tablespace NPMDBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index NE_ID_INX on TIA_BUSY_TIME (FIRST_RESULT, NE_ID, NE_TYPE)
  tablespace NPMDBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Answer 6

怀疑快的那个建了local索引，慢的那个忘了建local索引了！

Answer 7

   恩！是啊！
   是啊！分区表没必要建主键。但可以建多个索引。个人觉得。
   建索引的时候最好考虑建本地索引，这样速度会快很多。索引字段为按时间分区的那个字段。
   
  还有，最好就是另建一个job，专门用来创建分区表中的子分区，最好也按月份来创建。
  最后记得删除运行一段时间的分区表数据。因为当系统上线半年或一年之后会产生很多数据。
  这时候就得考虑删除分区数据了！