What are nested table objects in Oracle?

Question

Can someone explain what nested table objects are in Oracle ? While building an interface between to systems I discovered what was to me an odd column SYS_NC00054$. After some research I discovered that it was a nested table object from the function based index I created.

Answer 1

Function-based indexes are different from nested tables.

A regular index is built against actual columns ...

create index emp_job_idx on emp (job)
/

... whereas a function-based index is build on functions applied to columns. For instance we might what an index we can use when we query a date column without the time element...

create index emp_hire_idx on emp(trunc(hiredate))
/

When we query USER_IND_COLUMNS, the indexed column shows up in the first case but not in the second, because we are not indexing an actual column. What we see instead is the system generated "column'....

SQL> select index_name, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP'
  4  /

INDEX_NAME                     COLUMN_NAME
------------------------------ ---------------
PK_EMP                         EMPNO
EMP_UK                         ENAME
EMP_JOB_IDX                    JOB
EMP_HIRE_IDX                   SYS_NC00010$

SQL> 

We can see the make-up of the index in USER_IND_EXPRESSIONS ...

SQL> select index_name, column_expression
  2  from user_ind_expressions
  3  where table_name = 'EMP'
  4  /

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ --------------------
EMP_HIRE_IDC                   TRUNC("HIREDATE")

SQL>

Nested Tables

Nested tables are something different: they are user-defined arrays of simple or complex types. They can be used to define PL/SQL collections or columns in an ORDBMS table. Like this...

SQL> create or replace type address_t
  2      as object
  3      (
  4          address_line_1 varchar2(70)
  5          , address_line_2 varchar2(70)
  6          , address_line_3 varchar2(70)
  7          , address_line_4 varchar2(70)
  8          , address_line_5 varchar2(70)
  9          , postcode postcodestructure
 10      ) final;
 11  /
create or replace type address_t
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


SQL>
SQL> create or replace type address_nt as table of address_t
  2  /

Type created.

SQL>
SQL> create table contact_details (
  2      person_id number not null
  3      , email_address varchar2(254)
  4      , addresses address_nt
  5      )
  6  nested table addresses store as nested_addresses
  7  /

Table created.

SQL>