indexes are truncated after table is truncated?

Question

我碰到了一个题目是：Which three descriptions are correct about the effects of the TRUNCATE command on a table?
(Choose three.)
A. The corresponding indexes for the table are also truncated.
B. Delete triggers on the table are fired during the execution of the TRUNCATE command.
C. Very little or no undo data is generated during the execution of the TRUNCATE command.
D. The child table is truncated when the TRUNCATE command is applied on the parent table.
E. The highwater  (HWM) is set to point to the first useable data block in the table segment对于选项A,我做如下测试：SQL> create table zzw_temp10 as select * from zzw_temp;表已创建。SQL> select * from zzw_temp10;COMM
--------------------------------------------------------------------------------223hls999saakj
223hls9990820
sah223842999saakj
223923429990242
dsfaahlspoursaakj
INM2455TRPJlyth;
INM2455TRPJlyth
INM2455TRPJULL
INMTRPHOLWL已选择9行。SQL> create unique index idx_comm on zzw_temp10(comm);索引已创建。SQL> desc all_indexes
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------- OWNER                                     NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 INDEX_TYPE                                         VARCHAR2(27)
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         CHAR(5)
 UNIQUENESS                                         VARCHAR2(9)
 COMPRESSION                                        VARCHAR2(8)
 PREFIX_LENGTH                                      NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 PCT_THRESHOLD                                      NUMBER
 INCLUDE_COLUMN                                     NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 PCT_FREE                                           NUMBER
 LOGGING                                            VARCHAR2(3)
 BLEVEL                                             NUMBER
 LEAF_BLOCKS                                        NUMBER
 DISTINCT_KEYS                                      NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                            NUMBER
 AVG_DATA_BLOCKS_PER_KEY                            NUMBER
 CLUSTERING_FACTOR                                  NUMBER
 STATUS                                             VARCHAR2(8)
 NUM_ROWS                                           NUMBER
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 DEGREE                                             VARCHAR2(40)
 INSTANCES                                          VARCHAR2(40)
 PARTITIONED                                        VARCHAR2(3)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 BUFFER_POOL                                        VARCHAR2(7)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 PCT_DIRECT_ACCESS                                  NUMBER
 ITYP_OWNER                                         VARCHAR2(30)
 ITYP_NAME                                          VARCHAR2(30)
 PARAMETERS                                         VARCHAR2(1000)
 GLOBAL_STATS                                       VARCHAR2(3)
 DOMIDX_STATUS                                      VARCHAR2(12)
 DOMIDX_OPSTATUS                                    VARCHAR2(6)
 FUNCIDX_STATUS                                     VARCHAR2(8)
 JOIN_INDEX                                         VARCHAR2(3)
 IOT_REDUNDANT_PKEY_ELIM                            VARCHAR2(3)
 DROPPED                                            VARCHAR2(3)SQL> select index_name,table_name from all_indexes where table_name='ZZW_TEMP10'
;INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_COMM                       ZZW_TEMP10SQL> truncate table zzw_temp10;表被截断。SQL> select index_name,table_name from all_indexes where table_name='ZZW_TEMP10'
;INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_COMM                       ZZW_TEMP10SQL> select index_name,table_name from all_indexes where table_name='ZZW_TEMP10'
;INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_COMM                       ZZW_TEMP10SQL> select * from zzw_temp10;未选定行SQL> insert into zzw_temp10 values('comm');已创建 1 行。SQL> insert into zzw_temp10 values('comm');
insert into zzw_temp10 values('comm')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (ORACLE.IDX_COMM)
SQL>好像A是不正确的，但是答案中有A，本人甚是迷惑，请大家给解释一下吧，谢谢

Answer 1

你的测试刚好表明没问题呀你是唯一索引，中以插入一行comm，但第二行一产的数据插入就违反唯一性条件了

Answer 2

SQL> select index_name,table_name from all_indexes where table_name='ZZW_TEMP10' 
; INDEX_NAME                    TABLE_NAME 
------------------------------ ------------------------------ 
IDX_COMM                      ZZW_TEMP10 说明index仍然存在。A答案正确SQL> insert into zzw_temp10 values('comm'); 已创建 1 行。 SQL> insert into zzw_temp10 values('comm'); 
insert into zzw_temp10 values('comm') 
* 
第 1 行出现错误: 
ORA-00001: 违反唯一约束条件 (ORACLE.IDX_COMM) 你建的IDX_COMM索引是unique key index吧。

Answer 3

SQL> insert into zzw_temp10 values('comm');已创建 1 行。SQL> insert into zzw_temp10 values('comm');
insert into zzw_temp10 values('comm')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (ORACLE.IDX_COMM) 
这个为什么违反了唯一约束呢？前面表中也没有comm这个值的记录呀。

Answer 4

SQL> create table aa as select * from (select * from regulations.rgs_text where rownum<10);
 
Table createdSQL> create index i_aa on aa(file_data);
 
Index createdSQL> select index_name,num_rows from dba_indexes where index_name like 'I_AA';
 
INDEX_NAME                       NUM_ROWS
------------------------------ ----------
I_AA                                    9
 
SQL> select segment_name,bytes,blocks from dba_segments where segment_name like 'I_AA';
 
SEGMENT_NAME                                                                          BYTES     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
I_AA                                                                                 131072         16
 
SQL> truncate table aa;
 
Table truncatedSQL> exec dbms_stats.gather_index_stats('SYS','I_AA');
 
PL/SQL procedure successfully completed
 
SQL> select index_name,num_rows from dba_indexes where index_name like 'I_AA';
 
INDEX_NAME                       NUM_ROWS
------------------------------ ----------
I_AA                                    0
 
SQL> select segment_name,bytes,blocks from dba_segments where segment_name like 'I_AA';
 
SEGMENT_NAME                                                                          BYTES     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
I_AA                                                                                  65536          8
 

Answer 5

ORA-00001 unique constraint (string.string) violatedCause: An UPDATE or INSERT statement attempted to insert a duplicate key.Action: Either remove the unique restriction or do not insert the key.

Answer 6

SQL> insert into zzw_temp10 values('comm'); 已创建 1 行。这里不是做了一条进去了么

Answer 7

A. The corresponding indexes for the table are also truncatedtruncate 只是删除了表中的记录，并不会改变表的结构及依赖约束所以truncate表后表的索引依然存在,但是表和索引所占用的空间会恢复到初始大小

Answer 8

那对的呀 就是A啊
The corresponding indexes for the table are also truncated. 
索引里的值被删除掉，你插入了一个COMM，索引里新增一项
你再次插入COMM，这是索引就判断出重复值了 所以失败