Re: Table function parameters don't seem to be working

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:Ad92h.24307$e66.1889_at_newssvr13.news.prodigy.com...
> I'm working on Oracle 10g R2.
>
> I have a query I'm trying to write using a table function. I need to put
> a
> date range in the query which is against a partitioned table -
> partitioned
> on the date. When I run a non-table function version it takes 4 minutes
> yet
> when I run the table function version it can take 15-20 minutes or more.
> It
> almost seems as if the table function doesn't see the parameters in the
> cursor. I would like to use the table function and take advantage of the
> ability to pipe rows back and being able to manipulate the data within
> the
> function when loading tables.
>
> The non table function query:
>
> INSERT INTO clm_rx_temp
> SELECT *
> FROM claim.clm_rx
> WHERE pch_dt BETWEEN v_start_date AND v_end_date;
>
> Explain Plan:
> INSERT STATEMENT
> Load as select
> Filter
> Partition range iterator
> Table access full CLAIM.CLM_RX TABLE
>
> THe table function query:
>
> INSERT INTO clm_rx_temp
> SELECT *
> FROM TABLE ( load_clm_rx_temp
> ( CURSOR( SELECT *
> FROM claim.clm_rx
> WHERE pch_dt BETWEEN
> v_start_date AND v_end_date ) ));
>
> Explain plan:
>
> INSERT STATEMENT
> LOAD AS SELECT
> VIEW
> COLLECTION ITERATOR PICKLER load_clm_rx_temp
> procedure
> FETCH
> FILTER
> PARTITION RANGE ITERATOR
> TABLE ACCESS FULL claim.clm_rx
> TABLE
>
> I have other table functions which have greatly increased the speed of
> processing and data manipulation but this is the only one which has been
> deterimental rather than helpful.
>
> I don't have that much experience with table functions and nobody else at
> my
> office has heard of them so I'm on my own here and am stuck. Any
> advice/suggestions as to what I may be doing wrong would be greatly
> appreciated.
>
> Thanks.
>
>
>

It is possibly that this is the first case where the work done by the procedural code in the middle has a visible impact.

First step: run the two versions with sql-trace enabled to see if you can get the execution plans and row source statistics from the trace file to see if the work done by the critical SQL has changed. You could also run with event 10046 set at level 8 to see if the blocks read by the two queries match, or whether one does a lot more work than the other.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html