2

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

APC
  • 144,005
  • 19
  • 170
  • 281
Peter Frey
  • 361
  • 4
  • 17
  • Is there a common key which identifies these *"1 million rows"*? If so that's what you partition by, and Oracle will be smart enough to figure it out. – APC Aug 21 '19 at 08:09
  • @APC; Basically yes. But right now we did not partition by this key yet. So I guess this is what we are going to do next. – Peter Frey Aug 21 '19 at 08:13
  • @APC: just to doublecheck: the key is a sorting by some "date value" this would be enough to make sure all the important rows are in partition 1. But most selects would not ask for that date value. Will oracle still be smart enough to check partition 1 first? – Peter Frey Aug 21 '19 at 08:19
  • 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 '19 at 10:24
  • Do all these (99.9%) queries use the same or similar WHERE clauses? How do you know they can satisfied by this subset? Can you write one query which reliably identifies all those records? – APC Aug 21 '19 at 10:26
  • Basically, the 100+ mio are "all our articles" and the 1- Mio are our "recently sold articles". So most sql look like Select some_information from MV where artikel_id in (select some need). Most work is done only for recently sold articles and the MV will be regernated often enough so that sorting by latest sold day would catch most important information fairly well. However this date is currently not even in the MV because it is irrelevant for most selects. – Peter Frey Aug 21 '19 at 10:52

2 Answers2

0

Partial answer 2)

You can say to Oracle from which partition do you want to select data:

SELECT *
FROM partitioned_table PARTITION (partition_name) s
WHERE ....;
MichalSv
  • 567
  • 5
  • 10
  • Well yes. I don't want to change my colleaques SQL. And once in a while they will need other data, too, so I cannot reduce the MV to the 99,9%. Therefore I wondered if there where a solution to tricks with the optimizer. – Peter Frey Aug 22 '19 at 05:23
  • What is your current partitioning key? – BobC Aug 22 '19 at 15:01
  • Current key is the article ID. This is actually going to be part of most the calls, However, as there is not any helpful sorting mechanism in this key, partitioning by the key does also split all the relevant IDs across the partitions. – Peter Frey Aug 26 '19 at 07:12
0

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

Peter Frey
  • 361
  • 4
  • 17