Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> processing results of table value functions

processing results of table value functions

From: roger <xrsr_at_rogerware.com>
Date: Thu, 30 Oct 2003 22:51:22 GMT
Message-ID: <Xns9424983C1AEBrsrrogerwarecom@204.127.199.17>

Suppose I've got a pipeline table function that returns some subset of the rows of a table "customers" based on some input parameters - something like this:

  create or replace package x
  as
    type customers_t is table of customers%rowtype;     function filter_customers (val int) return customers_t pipelined;   end x;

I can use the function like this:

  select * from table( x.filter_customers(val) );

Now, it is likely that even these filtered views of the underlying table will return many many rows, and that further filtering and/or sorting is desired.

The underlying table has indexes in place to facilitate this. That is, a query like

  select * from customers where name = 'Foo';

would be covered by an index on the customers.name column.

However, if the pipeline function is used in place of the table itself, I'm guessing that the indexes on the underlying table are unusable.
For example in a query like this

  select * from table(x.filter_customers(val)) order by name;

is going to have to scan/sort all of the rows resulting from the table function call, rather than using the index.

I had thought to use table functions to restrict the visible set of data that would then be the basis of further queries - essentially, to use them to implement some access control rules that are implemented by other tables in the system.

Now, I'm not so sure if that's a good idea.

If this made any sense, any tips, thoughts, suggestions would be welcomed.

Thanks. Received on Thu Oct 30 2003 - 16:51:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US