Jonathan Lewis, 10.06.2013 11:52: > If you check line 5 of the "German-sort" plan you'll see that the > join predicate hasn't been pushed into the view, and since the join > predicate is the thing that allows an indexed access path into > VU_TMP_SPARTE this is the first level explanation of the full scan. > The question is then, why is the predicate not pushed. I suspect > it's the combination of the analytic functions (window sort) and > German NLS-Sort - in other words, it's nothing to do with the index, > it's all to do with the analytic order by. > > There are many cases where a predicate on a view cannot safely be > pushed inside a view with an analytic function in case it changes the > result - selecting the first value after using a German sort then > eliminating some rows may give you a different result from > eliminating rows and then doing a German sort on the remainder. Thanks for the detailed analysis, I haven't noticed that part. In this special case we could actually change the vertrag_ext_ref column to a number do to the data being processed and then the plan is (obviously) independent from the NLS_SORT setting. This kind of supports your theory I guess. Regards Thomas -- //www.freelists.org/webpage/oracle-l