I would analyze the table if possible after creating the indexes.
Krishna Kandula
Sr. Data Analyst
Oracle Certified Professional DBA
Tiermed Systems LLc.
952 345 2050(O)
317 840 1926(C)
-----Original Message-----
From: Subbarao Punnamaraju
[mailto:oracledba-ezmlmshield-x27728600.[Email address protected]
Sent: Tuesday, November 30, 2004 3:13 PM
To: LazyDBA Discussion
Subject: SQL Help.
Hi:
I observed a strange behaviour ( I am sure, some of you might have seen
this and had a solution for it ) when we try to get records from a table
with a QUERY against a particular MERCHANT_ID.
In tha table, we have nearly 3 million records and we have 12-15 unique
MERCHANTS in the Table. There are varied number of records against each
of these 15 MERCHANTS in the table.
When we access our application to retrieve records against a particular
MERCHANT who has least records against his/her name, it is taking more
time than the one who is having MAX. Records against his/her name. What
could be the issue?
I really appreciate if some one can suggest me the reason for this?
Thanks
-Subbarao
Here is the Table and the SQL Query:
CREATE TABLE BILLLING (
STATUS VARCHAR2 (10) NOT NULL,
CUSTOMER_ID VARCHAR2 (64),
MERCHANT_ID VARCHAR2 (64),
MERCHANT_TXN_ID VARCHAR2 (64),
AUTH_SRVC_TXN_ID VARCHAR2 (64),
BILL_SRVC_TXN_ID VARCHAR2 (64),
TIMESTAMP DATE NOT NULL,
SEQ NUMBER NOT NULL,
TXN_ID NUMBER NOT NULL,
)
TABLESPACE TABLES1;
CREATE INDEX ITM_BILLING ON
BILLING(MERCHANT_ID)
TABLESPACE INDEX1 ;
CREATE INDEX ITS_BILLING ON
BILLING(TIMESTAMP)
TABLESPACE INDEX1 ;
CREATE UNIQUE INDEX ITX_BILLING ON
BILLING(TXN_ID, SEQ)
TABLESPACE INDEX1;
SELECT STATUS, CUSTOMER_ID,MERCHANT_ID,
MERCHANT_TXN_ID,AUTH_SRVC_TXN_ID,BILL_SRVC_TXN_ID,
TIMESTAMP, SEQ, TXN_ID
FROM BILLING
WHERE (STATUS = ? OR BA_STATUS = ?)
AND MERCHANT_ID = ?
ORDER BY TXN_ID DESC;
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page