Hi everyone,
I have a query containing a parent-child pair partitioned tables and some filtering. you can consider the following psudo-code as the query template:
SELECT <clause containing fields from both tables>
FROM PARENT PARTITION(P_2017_01_01) PP,
CHILD PARTITION(P\_2017\_01\_01) PC
WHERE <Join tables on FK>
AND <Some filtering>
the tables are partitioned based a date column on daily manner. the stats gathering is done until partition P_2017_01_01. in other word, the partitions beyond this partition (P_2017_01_02 and next partitions) don't have stats gathered. when I run the query for stats gathered partitions it takes 7-8 minutes to complete while non-stats-gathered ones will finish in 20-30 seconds.
the explain plan for stats gathered partitions:
the explain plan for non-stats-gathered partitions:
As usual child table partitions contain considerable more records than their corresponding partition in parent table.
I want to know why this is happening?
Is there any hint to force the former plan to choose better plan?
Regards