optimize this!

From: FC <flavio_at_tin.it>
Date: Sun, 03 Oct 2004 10:46:55 GMT
Message-ID: <zMQ7d.31170$75.1500304@news3.tin.it>

Hi,
I am trying to optimize the following sql statement (oracle 10g) but I can't get anything better than an index range scan.

select distinct to_char(shopping_date,'MONTH/YYYY') d, trunc(shopping_date,'MM') r
from bills
where owner_id = 25
order by 2 desc

     (null) SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=39 Bytes=390) (null)

      1 0 _SORT (ORDER BY) (Cost=3 Card=39 Bytes=390) (null)
      2 1 _SORT (UNIQUE) (Cost=2 Card=39 Bytes=390) (null)
      3 2 _INDEX (RANGE SCAN) Of BILLS_IDX2 (INDEX) (Cost=1 Card=45

Bytes=450) (null)

Table bills comes with the following indexes (table and indexes have been analyzed beforehand)

1. a primary key index on column id which is not part of this query.
2. a btree index on columns owner_id and id (used by other queries)
3. a btree index on columns owner_id and shopping_date (this one is actually picked by the optimizer)
4. a function index on owner_id, to_char(shopping_date,'MONTH/YYYY'), trunc(shopping_date,'MM') which is ignored due to low selectivity I presume.

So, my question is, given the fact that the query is expected to select around 50 rows from a table containing some 10.000 rows, is the index range scan on index number 3 the best I can get?

Does it make any sense to define function indexes with functions like trunc that "flatten" values and therefore reduce selectivity?

Of course in my case it does not make sense to keep this function index as it is not going to be used ever.

Thanks,
Flavio Received on Sun Oct 03 2004 - 05:46:55 CDT