Re: optimize this!

FC wrote:
> 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
>
>

Flavio, can you give the following a try:

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

In the good old rule-based days, this used to enable the index, one never knows with the cost-biased.

You could even add a fake " and to_char(shopping_date,'MONTH/YYYY') > 'A' "

And then there are also hints (nudge, nudge, know what I mean? hey? wink, wink), have you tried those?

Geert Received on Wed Oct 06 2004 - 15:17:56 CDT