I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.
So, i am trying to use a query like this:
FROM SNAP S
INNER JOIN TABLE_A A
ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
ON S.DAY_CODE = B.DAY_CODE
AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923
Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It
runs for more than 12 hours and no results ).
If i change the query to: