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

From: Stefan Koehler <contact_at_soocs.de>
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-l
Received on Sat Dec 20 2014 - 11:11:09 CET

Original text of this message