Re: Why we don't want hints Was: Slow count(*) again...

2011/2/10 Tobias Brox <tobixen(at)gmail(dot)com>

> On 4 February 2011 04:46, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > "Optimizer hints are used to work around problems in the optimizer and
> > introduce upgrade and maintenance issues. We would rather have the
> > problems reported and fixed. We have discussed a more sophisticated
> > system of per-class cost adjustment instead, but a specification remains
> > to be developed."
>
> I have no clue about how hints works in Oracle ... I've never been
> working "enterprise level" on anything else than Postgres. Anyway,
> today I just came over an interesting problem in our production
> database today - and I think it would be a benefit to be able to
> explicitly tell the planner what index to use (the dev team is adding
> redundant attributes and more indexes to solve the problem - which
> worries me, because we will run into serious problems as soon as there
> won't be enough memory for all the frequently-used indexes).
>
> We have users and transactions, and we have transaction types. The
> transaction table is huge. The users are able to interactively check
> their transaction listings online, and they have some simple filter
> options available as well. Slightly simplified, the queries done
> looks like this:
>
> select * from account_transaction where account_id=? order by
> created desc limit 25;
>
> select * from account_transaction where trans_type_id in ( ...
> long, hard-coded list ...) and account_id=? order by created desc
> limit 25;
>
> and we have indexes on:
>
> account_transaction(account_id, created)
>
> account_transaction(account_id, trans_type_id, created)
>
> If the list is hard-coded, you can create partial index on
account_transaction(account_id, created desc) where trans_type_id in ( ...
long, hard-coded list ...)

--
Best regards,
Vitalii Tymchyshyn