Re: where clause filter from an array or use temp table?
Date: Sat, 20 Dec 2014 11:11:09 +0100 (CET)
Message-ID: <1454706120.492675.1419070269047.open-xchange_at_app01.ox.hosteurope.de>
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-lReceived on Sat Dec 20 2014 - 11:11:09 CET