I have a table Orders that stores orders, with fields:

Id
Date
Amount
Cost
Currency

I tried the following query:

SELECT SUM(Amount)-SUM(NFC1)
FROM Orders
WHERE Date BETWEEN '20121101' AND '20121231'
      AND Currency = 'EUR'

Now, according to Oracle SQL Developer, what makes the query slow is the Currency = 'EUR' filter, since the other operations have much lower cost.

I checked the indexes and I have an index on Id, and another index on Date. It seems to me, by the query analysis, that the DBMS first finds the records matching the required dates and then scans the whole table to find the records having Currency='EUR'. Currency is a VARCHAR.

Is there any way to optimize the query? I mean, is there a way to avoid the full scan?

From a general point of view, is it possible to prevent the DBMS from performing a full table scan after records have already been filtered by date, but rather find the records that match the Currency among those who have already been filtered by date?

Thanks a lot

It seems to me, by the query analysis, that the DBMS first finds the records matching the required dates and then scans the whole table to find the records having Currency='EUR'. Currency is a VARCHAR.

It does not scan the whole table.

Rather, it takes the row pointers (rowid's or the PRIMARY KEY values if the table is an IOT) from the index records and looks up the currency in the table rows in a nested loop. Since the index you're using does not contain Currency, it needs to be looked up somehow to do the filtering.

To work around this, you would need to create a composite index on (Currency, Date)

If creating another index is not an option, you may try creating a MATERIALIZED VIEW and create an index on that.