Cardinality hint for collections in Oracle 10g.
584255Jul 9 2008 — edited Jul 9 2008Guys,
I have a question about the cardinality hint. How useful is it in Oracle 10g since now the dynamic_sampling level in 10g has been set to 2.
From the explain plans that I have generated, I can see that for queries which have collections casted to tables, the plans generated in 10g do not really need the hint.
For example if I have a query in which the collection has been casted to a table, and I load it with 5000 rows, then the explain generated in 10g gives me a row count of 5000 (Collection Iterator Pickler fetch). So is it really beneficial to keep the hint as part of the query in 10g or to remove it? The trace outputs tell me that it is safer to remove, but I would like to know from you folks as you guys would have more knowledge/experience on this.
I am currently porting my application packages from Oracle 9i to 10g release 2, hence the above question. Any info will be appreciated.
Thanks in advance