How can I made db server response quickly? [message #844] |
Thu, 07 March 2002 15:42 |
mhy
Messages: 15 Registered: March 2001
|
Junior Member |
|
|
Dear sir,
I have a table named user_service_t has thirty thousands rows,the structure of this talbe is :
SQL> desc user_service_t;
Name Null? Type
----------------------------------------- -------- -----------------
REC_ID NOT NULL NUMBER
USER_ID NOT NULL NUMBER
USER_TYPE NOT NULL NUMBER(1)
BUSINESS_TYPE_ID NOT NULL NUMBER
SERVICE_ID NOT NULL NUMBER
LOGNAME NOT NULL VARCHAR2(30)
PASSWORD NOT NULL VARCHAR2(20)
ALIAS VARCHAR2(30)
PRODUCT_ID NOT NULL VARCHAR2(20)
LOGNAME_CREATE_DATE NOT NULL DATE
LOGNAME_LOGOFF_DATE DATE
PRODUCT_START_DATE DATE
PRODUCT_END_DATE DATE
FIRST_USED_DATE DATE
END_DATE DATE
LASTBILL_START_DATE DATE
LASTBILL_END_DATE DATE
STATE NOT NULL VARCHAR2(16)
PERMISSION NOT NULL VARCHAR2(20)
PAY_METHOD VARCHAR2(10)
DEVICE_TYPE NUMBER(1)
DEVICE_ID NUMBER
DEL NUMBER(1)
SPECIAL NUMBER(1)
and my query string is :
SELECT REC_ID,LOGNAME,USER_ID,PASSWORD,USER_TYPE,TO_CHAR(FIRST_USED_DATE,'ss:mi:hh24 dd mm yyyy d ddd'), TO_CHAR(END_DATE,'ss:mi:hh24 dd mm yyyy d ddd'),STATE, BUSINESS_TYPE_ID,SERVICE_ID,PRODUCT_ID,TO_CHAR(LOGNAME_CREATE_DATE,'ss:mi:hh24 dd mm yyyy d ddd'), TO_CHAR(LOGNAME_LOGOFF_DATE,'ss:mi:hh24 dd mm yyyy d ddd'),ALIAS FROM USER_SERVICE_T WHERE (LOGNAME= '000' OR ALIAS='000') AND SERVICE_ID = 101 AND ( ( SYSDATE<PRODUCT_END_DATE AND SYSDATE > PRODUCT_START_DATE) OR PRODUCT_END_DATE is NULL ) AND ( SYSDATE < END_DATE OR END_DATE is NULL);
I have an index on (LOGNAME,ALIAS,SERVICE_ID ),but it response too slow, How can I made the server response quickly?
thanks a lot!
|
|
|
|