Can you index an Oracle UDT?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply