The table is partitioned range-hash at model_year and vehicle_key(subparition).
I am going to implement star transformation after this performance issue get resolved. I tried enable star transformation at session level, and then check the explain plan. The plan was much better. But now, because the entire database is doing very bad, so it didn't make too much difference in response time.
Did you set optimizer_mode = all_rows while you enabled star transformation at your database? What would be the best for optimizer_mode for a database warehouse environment? Currently, our optimizer_mode = choose.