Can you index an Oracle UDT?



  • 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.



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2