2011/2/10 Tobias Brox <tobi...@gmail.com> > On 4 February 2011 04:46, Josh Berkus <j...@agliodbs.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
