tags when posting plans (and use the preview tab to make sure you placed them correctly), otherwise they are unreadable. Also, it would be much easier to tune the query if we had rowsource statistics (to produce, do ALTER SESSION SET STATISTICS_LEVEL = ALL or place gather_plan_statistics hint inside the statement, and use format=>'iostats last' option when displaying the plan with dbms_xplan.display_cursor). Or post the output of SQL real-time monitor, it's even simpler to obtain. Best regards, Nikolay
The presence of STORAGE FULL in the execution plan together with the option storage ( ) in the predicate part of this execution plan is an indication that your query is able to do Smart Scan and also a predicate offload reducing the amount of data flowing between the cellserv and the database server tier. That's already a good point.
* 6 TABLE ACCESS STORAGE FULL WCRS_CLAIM_DETAIL 10M 317M 848K (1) 00:00:27 6 - storage("CURRENT_SNAPSHOT_IND"='Y') filter("CURRENT_SNAPSHOT_IND"='Y')