Are the times relatively close or is one signifigantly different?
Is the MERCHANT_ID passed as a parameter or is the QUERY hard-coded with a value.
IN the second case, the plan created by Oracle will be sensitive to the data, in the first case, Oracle will always use the same plan.
If Oracle is using the same plan, then I would guess that even though the merchant has fewer records, thay are scattered across the table. In the second case, where the merchant_id is hard-coded, do an explain plan for each value. I would guess that, based on your hardware the cutoff between when it is better to use a full-table scan and when it is better to access the data through the indexes is different than Oracle's assumptions. You can change Oracle's cost assumptions, but that is something only for the very skilled. Instead, I would simply add a hint to the SQL to use the best plan.
-----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