Specific query performance in 11i worse than in 9i
We just upgraded from Oracle 9i to 11g. The following query (used in a view) worked great in 9i. But in 11g, performance is an issue. The query is complicated and so it was divided up like it is
for readability and maintainability. Each individual subquery is very fast. And like I said, in 9i the whole thing was very fast.
The explain plan in 11g is crazy. Look at the row counts near the top:
18E
18E
18E
11P
80T
567G
So this is obviously a problem. The query only returns 134 rows. The driver table (bnd_policy_claim) has 134 rows.