有一张表,其表结构是create table BL_LOG
(
BL_BASIC_UUID NUMBER(20) not null,
BL_HISTORY_UUID NUMBER(20) not null,
BL_REF_CDE VARCHAR2(12) not null,
SEQ_NUM NUMBER(4) not null,
CRE_DT_GMT DATE,
CRE_DT_LOC DATE,
CRE_TIME_ZONE VARCHAR2(3),
CREATOR VARCHAR2(8),
CRE_OFCE VARCHAR2(3),
TYPE VARCHAR2(20),
REMARKS VARCHAR2(240),
VERSION NUMBER(3),
CHNGE_FM VARCHAR2(25),
CHNGE_TO VARCHAR2(25),
BL_STATE VARCHAR2(25),
IMAGE_STATE VARCHAR2(15),
IMAGE_TYPE VARCHAR2(40),
PRT_TYPE VARCHAR2(10),
NUM_OF_COPIES NUMBER(9),
COMPONENT VARCHAR2(12),
REC_UPD_DT DATE
)
REC_UPD_DT字段上已经建了index. create index BL_LOGI01 on BL_LOG (REC_UPD_DT)这张表记录数有2亿多条。现在有一个sql查询,
SELECT * FROM BL_LOG A
WHERE A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')这个sql需要跑将近6分钟才能出结果。我用语句alter index BL_LOGI01 monitoring usage; 来开启对索引的监控。然后我执行那句sql,然后我查询 select * from v$object_usage,发现 索引 BL_LOGI01 并没有被使用。
然后我在sql上指定索引,
SELECT /*+ INDEX(BL_LOG BL_LOGI01)*/ * FROM BL_LOG A
WHERE A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')
再次执行这个sql,发现这个索引还是没有被使用。请教各位高手,那句sql该如何写才能让索引被使用? 或者我需要怎么建索引,才能提高查询速度啊?
(
BL_BASIC_UUID NUMBER(20) not null,
BL_HISTORY_UUID NUMBER(20) not null,
BL_REF_CDE VARCHAR2(12) not null,
SEQ_NUM NUMBER(4) not null,
CRE_DT_GMT DATE,
CRE_DT_LOC DATE,
CRE_TIME_ZONE VARCHAR2(3),
CREATOR VARCHAR2(8),
CRE_OFCE VARCHAR2(3),
TYPE VARCHAR2(20),
REMARKS VARCHAR2(240),
VERSION NUMBER(3),
CHNGE_FM VARCHAR2(25),
CHNGE_TO VARCHAR2(25),
BL_STATE VARCHAR2(25),
IMAGE_STATE VARCHAR2(15),
IMAGE_TYPE VARCHAR2(40),
PRT_TYPE VARCHAR2(10),
NUM_OF_COPIES NUMBER(9),
COMPONENT VARCHAR2(12),
REC_UPD_DT DATE
)
REC_UPD_DT字段上已经建了index. create index BL_LOGI01 on BL_LOG (REC_UPD_DT)这张表记录数有2亿多条。现在有一个sql查询,
SELECT * FROM BL_LOG A
WHERE A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')这个sql需要跑将近6分钟才能出结果。我用语句alter index BL_LOGI01 monitoring usage; 来开启对索引的监控。然后我执行那句sql,然后我查询 select * from v$object_usage,发现 索引 BL_LOGI01 并没有被使用。
然后我在sql上指定索引,
SELECT /*+ INDEX(BL_LOG BL_LOGI01)*/ * FROM BL_LOG A
WHERE A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')
再次执行这个sql,发现这个索引还是没有被使用。请教各位高手,那句sql该如何写才能让索引被使用? 或者我需要怎么建索引,才能提高查询速度啊?
那请问 我把to_date去掉的话, 我那个sql该怎么写? 你能不能帮我改好?
我在那个列上建了个to_char的函数索引,然后跑下面的sql速度很快。
SELECT * FROM BL_LOG A
WHERE to_char(REC_UPD_DT, 'yyyy-mm-dd') <= '2009-02-02'
AND to_char(REC_UPD_DT, 'yyyy-mm-dd') >= '2009-01-01' 但是为什么第一个sql那么慢呢?
在where条件中使用函数to_char当然跑的速度快了
用to_date就不行了
你那个to_char的函数索引是怎样创建的?能把代码贴出来看看吗?
拜托看仔细点,我跑第一个sql时,to_char的函数索引没有建。
后来建了to_char的函数索引后,sql也做了修改,改成第二个样子。
你在4楼说你建了TO_CHAR的索引
而你的语句中确实是用了TO_CHAR函数
这样速度就快了可是你是在问第一句SQL 也就是那个是用TO_DATE函数的SQL 没错吧?
速度为什么慢
因为你没有建立TO_DATE函数索引呀
TO_CHAR函数索引在使用TO_DATE函数的时候是不会起作用的
[TEST@ora10gr1#2009-11-22/21:07:23] SQL>create table test(rq date);Table created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>create index test_idx01 on test(rq);Index created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>create index test_idx02 on test(to_char(rq,'yyyy-mm-dd'));Index created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>
[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>
[TEST@ora10gr1#2009-11-22/21:07:23] SQL>explain plan for
2 select * from test where rq < to_date('02012009', 'mmddyyyy') AND rq >= to_date('01012009', 'mmddyyyy');Explained.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 3746981722-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX01 | 1 | 9 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------- 1 - access("RQ">=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "RQ"<TO_DATE('2009-02-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))Note
-----
- dynamic sampling used for this statement19 rows selected.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>
[TEST@ora10gr1#2009-11-22/21:07:23] SQL>explain plan for
2 select * from test where to_char(rq,'yyyy-mm-dd') <= '2009-02-02' AND to_char(rq,'yyyy-mm-dd') >= '2009-01-01';Explained.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 418310500------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 9 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX02 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------- 2 - access(TO_CHAR("RQ",'yyyy-mm-dd')>='2009-01-01' AND
TO_CHAR("RQ",'yyyy-mm-dd')<='2009-02-02')Note
-----
- dynamic sampling used for this statement19 rows selected.
请看清楚楼主的sql,列上面没有用函数,根本不是这个原因!!A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy') 这个范围内的数据量多大?oracle的cbo会根据数据量走不同的执行计划
A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')
REC_UPD_DT字段上没有用函数,所以这个sql不需要对REC_UPD_DT字段建函数所引,只需要建一个普通索引就行了,建的语句类似 create index BL_LOGI01 on BL_LOG (REC_UPD_DT) 而在第二个sql中,where语句是
WHERE to_char(REC_UPD_DT, 'yyyy-mm-dd') <= '2009-02-02'
AND to_char(REC_UPD_DT, 'yyyy-mm-dd') >= '2009-01-01'
REC_UPD_DT字段上有函数to_char,所以必须要建一个函数索引,create index BL_LOGI3 on BL_LOG (to_char(rec_upd_dt , 'yyyy-mm-dd'));是不是要用函数索引,区别点在where的等号左边的列字段上是否用了函数,而不是看等号右边的写法。