1、表结构
CREATE TABLE OPENAPI.TD_REPORT_ORDER_CHANNEL_DATA
(
LOGDATE VARCHAR2(10 BYTE),
LOGMONTH VARCHAR2(8 BYTE),
SPCHID VARCHAR2(20 BYTE),
PRODUCTID VARCHAR2(20 BYTE),
CPID VARCHAR2(20 BYTE),
D1 VARCHAR2(10 BYTE),
D2 VARCHAR2(10 BYTE),
D3 VARCHAR2(10 BYTE),
D4 VARCHAR2(10 BYTE),
D5 VARCHAR2(10 BYTE),
D6 VARCHAR2(10 BYTE),
D7 VARCHAR2(10 BYTE),
D8 VARCHAR2(10 BYTE),
D9 VARCHAR2(10 BYTE),
D10 VARCHAR2(10 BYTE),
D11 VARCHAR2(10 BYTE),
D12 VARCHAR2(10 BYTE),
CONTENTNAME VARCHAR2(200 BYTE),
PARTNAME VARCHAR2(200 BYTE),
REPORTTYPE VARCHAR2(2 BYTE),
CONTENTID VARCHAR2(20 BYTE),
SERVER VARCHAR2(5 BYTE),
PROVINCE VARCHAR2(50 BYTE)
)
TABLESPACE OPENAPI
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (LOGDATE)
(
PARTITION P_20130301 VALUES LESS THAN ('2013-03-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130401 VALUES LESS THAN ('2013-04-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130501 VALUES LESS THAN ('2013-05-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130601 VALUES LESS THAN ('2013-06-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130701 VALUES LESS THAN ('2013-07-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130801 VALUES LESS THAN ('2013-08-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130901 VALUES LESS THAN ('2013-09-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20131001 VALUES LESS THAN ('2013-10-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20131101 VALUES LESS THAN ('2013-11-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20131201 VALUES LESS THAN ('2013-12-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20140101 VALUES LESS THAN ('2014-01-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;2、查询语句(先后对logdate字段建立了全局索引和分区索引,查询都没有使用到索引)
select * from TD_REPORT_ORDER_CHANNEL_DATA where logdate = '2013-05-15' 3、查询语句(如此,便用到了索引)
select * from TD_REPORT_ORDER_CHANNEL_DATA where logdate = '20130515' 求解释!!!Oracle
CREATE TABLE OPENAPI.TD_REPORT_ORDER_CHANNEL_DATA
(
LOGDATE VARCHAR2(10 BYTE),
LOGMONTH VARCHAR2(8 BYTE),
SPCHID VARCHAR2(20 BYTE),
PRODUCTID VARCHAR2(20 BYTE),
CPID VARCHAR2(20 BYTE),
D1 VARCHAR2(10 BYTE),
D2 VARCHAR2(10 BYTE),
D3 VARCHAR2(10 BYTE),
D4 VARCHAR2(10 BYTE),
D5 VARCHAR2(10 BYTE),
D6 VARCHAR2(10 BYTE),
D7 VARCHAR2(10 BYTE),
D8 VARCHAR2(10 BYTE),
D9 VARCHAR2(10 BYTE),
D10 VARCHAR2(10 BYTE),
D11 VARCHAR2(10 BYTE),
D12 VARCHAR2(10 BYTE),
CONTENTNAME VARCHAR2(200 BYTE),
PARTNAME VARCHAR2(200 BYTE),
REPORTTYPE VARCHAR2(2 BYTE),
CONTENTID VARCHAR2(20 BYTE),
SERVER VARCHAR2(5 BYTE),
PROVINCE VARCHAR2(50 BYTE)
)
TABLESPACE OPENAPI
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (LOGDATE)
(
PARTITION P_20130301 VALUES LESS THAN ('2013-03-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130401 VALUES LESS THAN ('2013-04-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130501 VALUES LESS THAN ('2013-05-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130601 VALUES LESS THAN ('2013-06-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130701 VALUES LESS THAN ('2013-07-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130801 VALUES LESS THAN ('2013-08-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20130901 VALUES LESS THAN ('2013-09-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20131001 VALUES LESS THAN ('2013-10-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20131101 VALUES LESS THAN ('2013-11-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20131201 VALUES LESS THAN ('2013-12-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20140101 VALUES LESS THAN ('2014-01-01')
LOGGING
NOCOMPRESS
TABLESPACE OPENAPI
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;2、查询语句(先后对logdate字段建立了全局索引和分区索引,查询都没有使用到索引)
select * from TD_REPORT_ORDER_CHANNEL_DATA where logdate = '2013-05-15' 3、查询语句(如此,便用到了索引)
select * from TD_REPORT_ORDER_CHANNEL_DATA where logdate = '20130515' 求解释!!!Oracle
看下结果
2)可能跟统计信息有关,重新分析table以及index.贴出execution plan 加 index hint 后,也贴出execution plan
20130515 ==>用索引看上去和时间格式有关、你这里有日期转换函数吗?