Re: missing link in my 10053 trace
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: martin.a.berger@xxxxxxxxx
- Date: Wed, 8 Jun 2011 14:17:55 -0700
Looks the selectivity is being calculated as 0 for OOR predicate
because of the histogram and rounded up to 1 row - so I don't think
it's 1%.
"Using prorated density: 0.000000 of col #3 as selectvity of
out-of-range/non-existent value pred.
(also looks like we have a spelling mistake for "selectvity")
Here is an example:
http://pastebin.com/Esif77MU
On Wed, Jun 8, 2011 at 1:17 PM, Martin Berger <martin.a.berger@xxxxxxxxx> wrote:
> maybe someone can help me with my interpretation of a 10053 trace file.
> DB: 11.2.0.2.0 - 64bit
> I have a small query with a little error, which causes big troubles.
> The relevant part of the query is
> WHERE ....
> AND inst_prod_type=003
> AND setid='COM01'
>
> but INST_PROD_TYPE is VARCHAR2.
>
> this leads to
> filter[ (TO_NUMBER("INST_PROD_TYPE")=3 AND "SETID"='COM01') ]
>
> based on this TO_NUMBER ( I guess!) the optimiser takes a fix
> selectivity of 1%.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
