Home » SQL & PL/SQL » SQL & PL/SQL » Why the index isn't used?
Why the index isn't used? [message #432145] Sun, 22 November 2009 01:25 Go to next message
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(Cool,
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?

Re: Why the index isn't used? [message #432146 is a reply to message #432145] Sun, 22 November 2009 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/t/84315/102589/
Also read OraFAQ Forum Guide, Performance Tuning section.

Regards
Michel
Re: Why the index isn't used? [message #432155 is a reply to message #432145] Sun, 22 November 2009 04:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you gather statistics after creating the index (or filling the table)?
Previous Topic: Update a 2nd table using trigger on the first
Next Topic: drop table
Goto Forum:
  


Current Time: Wed Sep 28 02:23:01 CDT 2016

Total time taken to generate the page: 0.07367 seconds