Home » SQL & PL/SQL » SQL & PL/SQL » How can I made db server response quickly?
How can I made db server response quickly? [message #844] Thu, 07 March 2002 15:42 Go to next message
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!
Re: How can I made db server response quickly? [message #850 is a reply to message #844] Fri, 08 March 2002 04:44 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
create indexes on product_start_date, product_end_date and end_date columns.

use explain plan or sql trace on to see execution path of sql statement. Try to avoid full table scan.
Previous Topic: Return lower case fields
Next Topic: Re: urgent..urgent..how do i update the statement which uses 2 tables
Goto Forum:
  


Current Time: Wed Oct 27 05:44:56 CDT 2021