您可以索引 Oracle UDT 嗎？

您可以索引 Oracle 使用者定義的表類型嗎？

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;

鑑於上述情況，如何在 MY_OBJECT_TABLE 的 id 列上定義索引？

可執行樣本：

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;
/

我不確定如何獲取範例的查詢計劃，但我很確定它會顯示 MY_STORAGE_TABLE 的全表掃描，因為 MY_OBJECT_TABLE 上沒有 HASH JOIN 的索引。

無法索引集合，因為它是僅存在於會話記憶體中的變數。

您可以使用 PL/SQL 塊獲取執行計劃dbms_xplan.display_cursor。由於它是一個表函式，您可以在游標 FOR 循環中查詢它：

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;

（在這種情況下，您可能不需要所有這些選項，它只是我使用的腳本。我添加/*+ gather_plan_statistics */以包含實際的基數，在這種情況下也可能沒有用。另外，我給您的 PK 索引提供了一個更易讀的名稱。 )

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)

因此查詢使用 PK 索引來訪問嵌入在對象集合變數中的 ID。我不確定您還需要什麼其他索引。

引用自：https://dba.stackexchange.com/questions/308769