Re: optimize this!

"Gerard H. Pille" <ghp_at_skynet.be> wrote in message news:416452F4.10800_at_skynet.be...
> 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
>

Hi Geert,
It doesn't change, but I am satisfied with the results I got so far, thanks!

Bye,
Flavio

PS: "say no more, say no more..." Received on Wed Oct 06 2004 - 16:59:05 CDT