We have a table structure with parent and child records inside the same table. They are referenced via a column (parentId) which references the parent (and null on the top parent records). Also, we have a requested date column we do all of our querying by but the only records which have a value for this are the parent records.
Since all of our queries are filtering on the parent results initially and use this date, we want to paritition the table on this date range but understand this isn't possible due to there being null values on the records for the child records.
Aside from creating a "dummy" value (some arbitrary date that would never be caught in our filter) on each and every child record in the database, what other option do we have? Is it not possible to somehow create a separate partition for all of the null values that we never query for anyway?