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

You want better than a cost of 3 ... why? Don't you have a job to do?

Personally I'd expect that a full table scan wouldn't hurt with such a small table.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)