Speed Up Oracle Select by Cache or partition

Question

I've got a oracle MV with 100+ million rows. Much of the our code calls against this view, so I would like to keep the syntax intact and make sure these calls are very fast.

Because of business logic I know that less than 1 million rows would be enough to answer 99%+ of all calls. Say these million rows are in partition 1. I wonder:

1. Did Oracle (probably) find that out by itself and usually returns cached results instead of actually scanning the table?
2. Can I tell Oracle to always check first partition first and then go by whatever it likes to continue.
3. Can I do any else?

If (1) is the case, then I guess we are as fast as we can be. Else how could I make (2) work? or is there any (3) that I did not think about yet?

Can you help out? Greetings, Peter

Answer 1

I guess, the way I wanne do is just not possible. I post APCs comment as it seems closeds to my needs:

APC: Unlikely. Generally, partition pruning (searching just the partitions which Oracle knows have all the required records) only works with queries which use the partition key. Possibly if you have something which absolutely correlates with the date and you gather histogram stats then Oracle may be able to establish the correlation and still look in the one partition. But I can't be sure as I've only ever worked with Partitions whose key is always part of the query (what a narrow life I've led). – APC Aug 21 at 10:24