query with column is null is very slow
564557Sep 14 2009 — edited Sep 15 2009I have a query - select * from grv3que where gq_process_date is null;
that is very slow when returned 4 records (the table has total 60000 records).
SQL> desc grv3que
Name Null? Type
----------------------------------------- -------- ----------------------------
GQ_ID NOT NULL NUMBER(15)
GQ_MESSAGE LONG
GQ_RECEIVE_DATE DATE
GQ_PROCESS_DATE DATE
GQ_CONTROL_ID NOT NULL VARCHAR2(20)
and it has two indexes:
SQL> select index_name, index_type from dba_indexes where table_name='GRV3QUE';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
PK_GRV3QUE NORMAL
IDX_GQ_PROCESS_DATE NORMAL
Analyzing the table/indexes didn't help on the performance.
Could you help us how to tune the query? Thanks in advance!!!