Oracle Designer 2.1.2: Don't have statistics on CG_REF_CODES!

From: Roy Brokvam <roy.brokvam_at_conax.com>
Date: Fri, 5 Jan 2001 16:50:34 +0100
Message-ID: <i3m56.10000$v46.322676_at_news1.oke.nextra.no>


In the designer-generated Table API package cg$<table-name>, there is a procedure named validate_domain. This procedure does something like:

   SELECT null
    FROM CG_REF_CODES RC
    WHERE RC.RV_HIGH_VALUE IS NULL
    AND cg$rec.<column-name> = RC.RV_LOW_VALUE     AND RC.RV_DOMAIN = '<domain-name>'

If the column is a non-varchar2 column, an implicit conversion from varchar2 is done on RV_LOW_VALUE. With statistics on CG_REF_CODES, the optimizer may choose to evaluate the second last expression before the last expression. This results in an exception if there exist records in CG_REF_CODES with RV_LOW_VALUE values that can't be converted to the column's type. Without statistics, however, the optimizer defaults to evaluating the last expression first, filtering away all records that don't belong to the given domain name before any conversion is done on RV_LOW_VALUE.

--
Roy Brokvam

Theory is when you know why it doesn't work.
Practice is when it works, but you don't know why.
We combine theory and practice;
It doesn't work, and we don't know why.
Received on Fri Jan 05 2001 - 16:50:34 CET

Original text of this message