select * from ITEM_STG where STATUS = 'PROCESSED_CROSSREF' AND TRUNC(Creation_date) < Trunc(sysdate)-10 in explain plan showing cost as 6156 I tried to create index on creation date and status column has already index on this table after creating index we check the cost its not reduce. How to reduce the cost and time for this query.

sqloptimizationquery-optimization

LittleBitDeveloper

Reputation: 17

how to reduce the cost of the query?

select * from ITEM_STG where STATUS = 'PROCESSED_CROSSREF' AND TRUNC(Creation_date) < Trunc(sysdate)-10

in explain plan showing cost as 6156 I tried to create index on creation date and status column has already index on this table after creating index we check the cost its not reduce. How to reduce the cost and time for this query.

Upvotes: 0

Answers (1)

marcothesane

Reputation: 6721

Try:

select 
  * 
from ITEM_STG 
where STATUS = 'PROCESSED_CROSSREF' 
  AND Creation_date < Trunc(sysdate)-10

I don't even have to know the Database Management System. But if you apply * any * expression to a column in a table that you want to use as part of a join or filter condition, any index on that column will be ignored. There is no reliable way for the optimiser to guess what the expression will do to the column's value, so any cost optimising prediction is impossible. The expression could make things better, in theory, but more often than not, it makes things worse.

So the optimiser will just ignore any data access improvement database objects in evaluating the query.

Upvotes: 3

how to reduce the cost of the query?

Answers (1)

Related Questions