Re: Oracle optimizer problem

On 2 May 2002 08:35:20 -0700, delgrandedp_at_navsea.navy.mil (Don Del Grande) wrote:

>When I run the following query:
>
>DECLARE
> vd_data_dt DATE;
>BEGIN
> vd_data_dt = TO_DATE('05/02/2002', 'MM/DD/YYYY');
> INSERT INTO mytable
> (<fields>)
> SELECT <fieldlist>
> FROM table1 t1, table2 t2,...
> WHERE ...
> AND t1.data_dt > vd_data_dt;
>
>it returns with a "max extents exceeded on temp tablespace" error.
>However, if I replace the last line with:
>
> AND t1.data_dt > TO_DATE('05/02/2002', 'MM/DD/YYYY')
>
>then there is no problem.
>Any ideas why using a local variable rather than something returned
>from TO_DATE would cause the query to change so dramatically? (Note
>that removing the entire last line works as well.)

Using histograms (hardcoded literal) vs. not using histograms (variable), so different execution plans. Without seeing the execution plans and the full statement it is not possible to determine where the problem exactly is, the query might be rewritten in such a fashion this doesn't occur, but you provide insufficient clues.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu May 02 2002 - 11:59:42 CDT