What’s that about then?
At the risk of Tom Kyte telling me that I have given up thinking…
Any ideas why this appeared as part of a WHERE predicate (or more to the point why write it!)
… AND ( case when T.V_STATUS = 'A' then 'Active' when T.V_STATUS = 'D' then 'Delisted' else 'Unknown Status' end ) = 'Active' ) …I might have said … AND T.V_STATUS = 'A'
Extra information.
Just for fun I restricted the timespan of the query to a more sensible value and got the plan for the orignal and my new version. The original used star transforms and had a highly unlikely cardinality of 7! My version used mview rewrite and had a cardinality of 80,000. Run times were a bit different too; 2500 seconds for the case version and 245 seconds for mine!
And a later still update - there is posible performance gain for CASE to replace a NOT equals query