Can a type table of table%rowtype be indexed by some field of table%rowtype?-CodePudding

Why to I asked this question?

I have a table which as key that have a lot of field. Every time, I'm making a jointure, I miss a field. Therefore I have defined a pipelined function that take the key as an argument so that I am sure that I get only one element when I'm doing a jointure. But the query take more time now. The table a has an index on some fields but not the table type used by pipelined function. I would like to know if it is possible to created a index on some fields of the table%rowtype

code:

create table a ( a1 integer);

create package p_a
as
   type t_a iS TABLE of a%ROWTYPE;
   
   function f(i_a1 integer) return t_a pipelined;
end;

CREATE PACKAGE BODY p_a
AS
    CURSOR c_A (i_a1 INTEGER)
        RETURN a%ROWTYPE
    IS
        SELECT t.*
          FROM a t
         WHERE t.a1 = i_a1;

    FUNCTION f (i_a1 INTEGER)
        RETURN t_a
        PIPELINED
    IS
    BEGIN
        FOR c IN c_a (i_a1)
        LOOP
            PIPE ROW (c);
        END LOOP;
    END;
END;

with b as( select 1 b1 from dual) select * from b cross apply (table(p_a.f(b.b1)));

the question

I've tried to index the type table by a field of a table like this

create table a ( a1 integer);

create package p_a2
as
   type t_a iS TABLE of a%ROWTYPE index by a.a1%type;
   
   function f(i_a1 integer) return t_a pipelined;
end;

PLS-00315: Implementation restriction: unsupported table index type

Is what I want to do possible. If not how to solve the performance problems mentioned in the introduction?

code

CodePudding user response：

A TYPE is NOT a table and cannot be indexed.

When you do:

create package p_a
as
   type t_a iS TABLE of a%ROWTYPE;
end;
/

You are defining a type and the type is a collection data type; an instance of that type is NOT a physical table and but is more like an in-memory array.

When you create a PIPELINED function:

function f(i_a1 integer) return t_a pipelined;

It does NOT return a table; it returns the collection data type.

When you do:

type t_a iS TABLE of a%ROWTYPE index by a.a1%type;

You are NOT creating an index on a table; you are changing to a different collection data type that is an associative array (like a JavaScript object or a Python dictionary) that stores key-value pairs.

An associative array is a PL/SQL data type and (with limited exceptions in later versions for insert, update and delete statements) cannot be used in SQL statements.

When you do:

SELECT * FROM TABLE(SYS.ODCIVARCHAR2LIST('a', 'b', 'c'));

or:

SELECT * FROM TABLE(p_a.f(1));

Then you are passing a collection data type to an SQL statement and the table collection expression TABLE() is treating the collection expression as if it was a table. It is still NOT a table.

If you want to use an index on the table then use the table (without a cursor or a pipeline function):

WITH b (b1) AS (
  SELECT 1 FROM DUAL
)
SELECT *
FROM   b
       CROSS APPLY (
         SELECT a.*
         FROM   a
         WHERE  a.a1 = b.b1;
       );

CodePudding user response：

I think the first line of your question says it all: "key that have a lot of field". If I understand correctly, the table has a primary key that consists of a large number of columns and because of that writing queries becomes a challenge.

It sounds like you're trying to do something pretty complex that should not be an issue at all.

Take a step back and ask yourself - does this need to be the primary key of the table ? Or can you use a surrogate key (identity column, sequence), use that as the primary key and just create a unique index on the set of field that currently make up the primary key. It will (1) simplify your data model and (2) make writing the queries a lot easier.