Table function parameters don't seem to be working

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Wed, 01 Nov 2006 22:29:52 GMT
Message-ID: <Ad92h.24307$e66.1889@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. Received on Wed Nov 01 2006 - 16:29:52 CST