Wrong index being used when selecting top rows

Viewed 609

I have a simple query, which selects top 200 rows ordered by one of the columns filtered by other indexed column. The confusion is why is that the query plan in PL/SQL Developer shows that this index is used only when I'm selecting all rows, e.g.:

SELECT * FROM
(
 SELECT *
 FROM cr_proposalsearch ps
 WHERE UPPER(ps.customerpostcode) like 'MK3%'
 ORDER BY ps.ProposalNumber DESC
)
WHERE ROWNUM <= 200

Plan shows that it uses index CR_PROPOSALSEARCH_I1, which is an index on two columns: PROPOSALNUMBER & UPPER(CUSTOMERNAME), this takes 0.985s to execute: query with ROWNUM

If I get rid of ROWNUM condition, the plan is what I expect and it executes in 0.343s: query without ROWNUM

Where index XIF25CR_PROPOSALSEARCH is on CR_PROPOSALSEARCH (UPPER(CUSTOMERPOSTCODE));

How come?

EDIT: I have gathered statistics on cr_proposalsearch table and both query plans now show that they use XIF25CR_PROPOSALSEARCH index.

4 Answers
Related