Re: where clause filter from an array or use temp table?

If I've understood the discussion below correctly, there is a setting to prevent the dynamic sampling mechanism from executing table functions in order to gather stats about their result set. I can see why sites with heavy duty table functions (e.g. if they're used to drive ETL processing) might not want that enabled by default.

William Robertson

On 20 Dec 2014, at 10:12, Stefan Koehler <contact_at_soocs.de> wrote:

Hi Jeff,

>> I enabled the fix for dynamic sampling (I wonder why it is disabled?) but I wasn't seeing any differences in my tests which is a much bigger query

I wouldn't have expected a difference as the cardinality estimates are based on bind peeking and not dynamic sampling in your case. However it is a
common practice to introduce CBO enhancements/features (not bug fixes for wrong arithmetic, array handling or whatever), but keep them disabled by
default for a period of time - just think about complex view merging for example (silently parameter change "_complex_view_merging" between Oracle 8i
and 9i).

I am sorry, but i don't understand your other scenario based on the current description.

Best Regards
Stefan Koehler

Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Jeff C <backseatdba_at_gmail.com> hat am 19. Dezember 2014 um 18:39 geschrieben:
>
> Ah you are right, sorry I missed that. Thank you so much your time and help on this Stefan. I enabled the fix for dynamic sampling (I wonder why
> it is disabled?) but I wasn't seeing any differences in my tests which is a much bigger query. I think I am going to roll forward with the GTT and
> watch how it goes.
> What do you think about this other scenario with we put an OR statement to mean not filter and return all rows. Is this a bad idea. I ran a trace
> and didn't see much of a difference.
>
> where (p_values is null or (emp_id in (select column_value from table(cast(t_numbers as num_tab))))

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l