Re: where clause filter from an array or use temp table?
- From: Stefan Koehler <contact@xxxxxxxx>
- To: backseatdba@xxxxxxxxx
- Date: Sat, 20 Dec 2014 11:11:09 +0100 (CET)
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
Other related posts:
