Performance Degrade After analyze table

well cost means nothing

cardinality is obtained from statistics

Quote:

---

Originally posted by gnagesh
Thanks Sanjay.
SELECT USER_NO,VALUE_LEFT,EXPIRE_DATE
FROM MST_USER
WHERE ACCOUNT_NO != '7594299380771992' /*'3839059713682615'*/
AND SALE_STATUS='V'
AND (MST_USER.STATUS_ID='A' OR MST_USER.STATUS_ID='C')
AND VALUE_LEFT = 0
AND ROWNUM <= 1
I have an composite Index on this table based on ACCOUNT_NO,SALE_STATUS, STATUS_ID, VALUE_LEFT

---

The only improvement you might be able to get on the execution plan is to get Oracle to use a fast full index scan, maybe parallelized.

Alternatively you might modify your index. Having ACCOUNT_NO as the first column prevents the index being used in a conventional range scan for optimizing this query -- move it down the list, or onto a different index, and you may get a better response.

Quote:

---

Originally posted by SANJAY_G
But ain't the cost and cardinality for FTS is same for both plans?

Sanjay

---

As pando says oracle will estimate cost and cardinality BEFORE!!! real execution query fom stats tables, but not AFTER execution as result of query.
REAL statistics of execution (AFTER QUERY) u can see in

Statistics
----------------------------------------------------------
...
41207 consistent gets
3186 physical reads
...

and

Statistics
----------------------------------------------------------
...
1 consistent gets
0 physical reads
...
That is differrence between two plans.

Hi All Participants and Guru's

Thank you very much for your valuable suggestions and contributions. And I apologize for the delay in response. Finally I solved this performance issue by creating one more composite index on sale_status,status_id and value_left. Now the query elapsed time is almost 00:00:00.

Thanks Once again.

Best regards

Hi all,

We have seen a lot of suggestions but Iam curious to know as to why the column rownum has not been included in the concatenated index. Could someone explain this for me.

Regards,

K.Diwakar

Quote:

---

Originally posted by diwakar
Hi all,

We have seen a lot of suggestions but Iam curious to know as to why the column rownum has not been included in the concatenated index. Could someone explain this for me.

Regards,

K.Diwakar

---

Hi
ROWNUM is a pseudocolumn. A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

You can use ROWNUM to limit the number of rows returned by a query.

Hope this helps.

Quote:

---

Originally posted by gnagesh
Hi
ROWNUM is a pseudocolumn. A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

You can use ROWNUM to limit the number of rows returned by a query.

Hope this helps.

---

Thanks for your answer....

Regards,

K.Diwakar