| Why the index isn't used? [message #432145] |
Sun, 22 November 2009 01:25  |
luye
Messages: 2
Registered: November 2009
|
Junior Member |
|
|
Hi, all
I met a performance issue about sql query.
I hava a table which table struction is:
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( ,
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
)
and there is an index created on the REC_UPD_DT column: create index BL_LOGI01 on BL_LOG (REC_UPD_DT)
There are more than 200 million records in that table.
I ran a sql statement on that table:
SELECT * FROM BL_LOG A
WHERE A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')
It took about 6 minutes to get the query result.
I opened the index monitoring by " alter index BL_LOGI01 monitoring usage; ", and ran that statement again, and queried v$object_usage view by "select * from v$object_usage", found the index BL_LOGI01 didn't used.
Who can tell me why?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
