Even when a very small subpartition was joined to the large table, when running the statement in serial mode it took up to 15 minutes to complete the statement due to full tablescan on the large table.Now we started to check all the usual things that might go wrong and mislead the optimizer, but all the things we checked worked out to be reasonably set including all the table, index and column sta