I have to create a new table which is basically a pull of data from 3-4 tables having similar kind of data (i.e ) all column types and domain are same exceprt a Src column that signifies the source of the data. The Src field values will be different depending on the source of the data. This table will be accessed on a date field(create_date) column and the src. These will be a range based queries. So, one option is to create a MLPPI on create_dt and Scr and keep first level partition on create_date and 2nd level partition on Src. So, any queris which have filter on create_date and src would use partition elimination on both levels. I also might have some queries that have filter only on Scr id and no filter on create_date. So, in this case it would do a partition elimination at 2nd level (but read more data blocks). I read in the manauls that the optimizer would decide between going for a partition elimination if 2nd level filter is given or full table scan based on many other factors as well. So, is it a good idea to have a MLPPI like this or use below option?
Another option for me is to create separare tables for each of these source tables with PPI on create_date and then do a union all of all the tables and create a view on top of it. and then query this view by putting filter in create_date.