Can you index an Oracle UDT?

by

The question:

Can you index an Oracle User-Defined Table Type?

CREATE OR REPLACE TYPE MY_OBJECT AS OBJECT(ID int, key varchar2(5), value varchar2(10));
CREATE TYPE MY_OBJECT_TABLE AS TABLE OF MY_OBJECT;

Given the above, how do I define an index on MY_OBJECT_TABLE’s id column?

Executable Sample:

CREATE OR REPLACE TYPE MY_OBJECT AS OBJECT(ID int, key varchar2(5), value varchar2(10));
/
CREATE TYPE MY_OBJECT_TABLE AS TABLE OF MY_OBJECT;
/
CREATE TABLE MY_STORAGE_TABLE (ID int PRIMARY KEY, key varchar2(5), value varchar2(10));
/
CREATE UNIQUE INDEX MY_STORAGE_TABLE_UK ON MY_STORAGE_TABLE(key);
/
INSERT INTO MY_STORAGE_TABLE(ID,key,value) VALUES(1, 'a','b');
commit;

DECLARE
  v_row MY_OBJECT;
  v_table MY_OBJECT_TABLE;
  v_result varchar2(10);
BEGIN
  v_row := MY_OBJECT(1,'a','b');
  v_table := MY_OBJECT_TABLE(v_row);
  SELECT value INTO v_result FROM MY_STORAGE_TABLE WHERE EXISTS(SELECT 1 FROM table(v_table) x WHERE MY_STORAGE_TABLE.id=x.id);
  dbms_output.put_line(v_result);
END;
/

I’m not sure how to get the query plan for the sample, but I’m pretty sure it’d show a full table scan for MY_STORAGE_TABLE as there’s no index on MY_OBJECT_TABLE for a HASH JOIN.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

A collection can’t be indexed because it’s a variable that only exists in your session’s memory.

You can get the execution plan within a PL/SQL block using dbms_xplan.display_cursor. As it’s a table function, you can query it in a cursor FOR loop:

declare
    v_row my_object := my_object(1,'a','b');
    v_table my_object_table := my_object_table(v_row);
    v_result varchar2(10);
begin
    select /*+ gather_plan_statistics */ value into v_result
    from   my_storage_table
    where  exists
           ( select 1 from table(v_table) x
             where  my_storage_table.id=x.id );

    dbms_output.put_line(v_result);

    for r in (
        select p.plan_table_output
        from   table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -OUTLINE +NOTE +PREDICATE +IOSTATS +REPORT')) p
    )
    loop
        dbms_output.put_line(r.plan_table_output);
    end loop;
end;

(You probably don’t need all those options in this case, it’s just the script I use. I added /*+ gather_plan_statistics */ to include the actual cardinalities, again probably not useful in this case. Also I gave your PK index a more human-readable name.)

b
SQL_ID  9twnr3q7yk6j3, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ VALUE FROM MY_STORAGE_TABLE WHERE 
EXISTS ( SELECT 1 FROM TABLE(:B1 ) X WHERE MY_STORAGE_TABLE.ID=X.ID )
 
Plan hash value: 3471312807
 
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |
|   1 |  NESTED LOOPS                        |                     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   2 |   NESTED LOOPS                       |                     |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   3 |    SORT UNIQUE                       |                     |      1 |      1 |      1 |00:00:00.01 |       0 |  2048 |  2048 | 2048  (0)|
|   4 |     COLLECTION ITERATOR PICKLER FETCH|                     |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|*  5 |    INDEX UNIQUE SCAN                 | MY_STORAGE_TABLE_PK |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID        | MY_STORAGE_TABLE    |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("MY_STORAGE_TABLE"."ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

So the query is using the PK index to access the ID embedded in your object collection variable. I’m not sure what other index you need.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment