oracle - Oracle 中针对索引组织表的 CTAS 查询

Question

是否可以在 Oracle 中编写 CTAS(create table as select ...)查询来创建索引组织表？

在网上查看并尝试了几个版本的 CTAS，但无法找到有效的示例/查询。

Answer 1

简短的回答:是的。示例，基于 JOB_HISTORY table :

表 JOB_HISTORY

SQL> select * from hr.job_history ;
  EMPLOYEE_ID START_DATE   END_DATE    JOB_ID         DEPARTMENT_ID 
          102 13-JAN-93    24-JUL-98   IT_PROG                   60 
          101 21-SEP-89    27-OCT-93   AC_ACCOUNT               110 
          101 28-OCT-93    15-MAR-97   AC_MGR                   110 
          201 17-FEB-96    19-DEC-99   MK_REP                    20 
          114 24-MAR-98    31-DEC-99   ST_CLERK                  50 
          122 01-JAN-99    31-DEC-99   ST_CLERK                  50 
          200 17-SEP-87    17-JUN-93   AD_ASST                   90 
          176 24-MAR-98    31-DEC-98   SA_REP                    80 
          176 01-JAN-99    31-DEC-99   SA_MAN                    80 
          200 01-JUL-94    31-DEC-98   AC_ACCOUNT                90

查询(用于生成样本数据)

-- every employee must do every job (only for a day at a time ...)
select E.employee_id, J.job_id
, sysdate - ( row_number() over ( order by E.employee_id, J.job_id )  ) dt
from (
  select unique employee_id from hr.job_history
) E cross join (
  select unique job_id from hr.job_history
) J ;

--
  EMPLOYEE_ID JOB_ID       DT          
          101 AC_ACCOUNT   10-FEB-20   
          101 AC_MGR       09-FEB-20   
          101 AD_ASST      08-FEB-20   
          101 IT_PROG      07-FEB-20   
          101 MK_REP       06-FEB-20   
          101 SA_MAN       05-FEB-20   
          101 SA_REP       04-FEB-20   
          101 ST_CLERK     03-FEB-20   
          102 AC_ACCOUNT   02-FEB-20   
          102 AC_MGR       01-FEB-20   
...   
          200 ST_CLERK     25-DEC-19   
          201 AC_ACCOUNT   24-DEC-19   
          201 AC_MGR       23-DEC-19   
          201 AD_ASST      22-DEC-19   
          201 IT_PROG      21-DEC-19   
          201 MK_REP       20-DEC-19   
          201 SA_MAN       19-DEC-19   
          201 SA_REP       18-DEC-19   
          201 ST_CLERK     17-DEC-19   
--56 rows selected.

CTAS

-- heap organized table
create table heap_empjobs (
  employee_id, job_id, dt
, constraint heap_empjobs_pk primary key( employee_id, job_id, dt )
) 
as
select E.employee_id, J.job_id
, sysdate - ( row_number() over ( order by E.employee_id, J.job_id )  ) dt
from (
  select unique employee_id from hr.job_history
) E cross join (
  select unique job_id from hr.job_history
) J 
;
-- Table created.

-- index organized table
create table iot_empjobs (
  employee_id, job_id, dt
, constraint iot_pk primary key( employee_id, job_id, dt ) -- also works without naming the constraint
) 
organization index
as
select E.employee_id, J.job_id
, sysdate - ( row_number() over ( order by E.employee_id, J.job_id )  ) dt
from (
  select unique employee_id from hr.job_history
) E cross join (
  select unique job_id from hr.job_history
) J
;
-- Table created.

支票

select count(*) from heap_empjobs ;
  COUNT(*) 
        56 

select count(*) from iot_empjobs ;
  COUNT(*) 
        56 


select table_name, nvl( to_char( num_rows ), 'no rows!' ) rowcount
from user_tables 
where table_name in ('HEAP_EMPJOBS', 'IOT_EMPJOBS') ;

TABLE_NAME     ROWCOUNT   
HEAP_EMPJOBS   56         
IOT_EMPJOBS    no rows! 

使用 Oracle 18c 和 11g 进行测试(参见 dbfiddle)。