Re: where clause filter from an array or use temp table?
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 20 Dec 2014 13:12:32 +0000
A nice article about cardinality for table functions:
http://www.oracle-developer.net/display.php?id=427
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@xxxxxxxx> 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: @OracleSK
> Jeff C <backseatdba@xxxxxxxxx> 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))))
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
