当前位置：文江博客话题详情

Oracle 中的嵌套表对象是什么？

发布于 2024-08-20 18:43:36 字数 106 浏览 6 评论 0原文

有人能解释一下 Oracle 中的嵌套表对象是什么吗？在构建系统之间的接口时，我发现了一个对我来说很奇怪的列 SYS_NC00054$。经过一番研究，我发现它是我创建的基于函数的索引中的嵌套表对象。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

温柔女人霸气范 2024-08-27 18:43:36

基于函数的索引与嵌套表不同。

常规索引是针对实际列构建的……

create index emp_job_idx on emp (job)
/

而基于函数的索引是基于应用于列的函数构建的。例如，当我们查询没有时间元素的日期列时，我们可能可以使用什么索引...

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

当我们查询 USER_IND_COLUMNS 时，索引列在第一种情况下显示，但在第二种情况下不显示，因为我们没有对实际的索引列进行索引柱子。相反，我们看到的是系统生成的“列”...

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>

我们可以在 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>

嵌套表

嵌套表有所不同：它们是用户定义的它们可用于定义 ORDBMS 表中的 PL/SQL 集合或列。

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>

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>

回复收藏 0 原文