[20241205]truncate table and index.txt

[20241205]truncate table and index.txt --//链接给出一个例子，https://connor-mcdonald.com/2024/12/04/kris-kringle-the-database-truncate-and-indexes/ --//truncate table后unusable的索引自动生效。测试看看。 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试： SCOTT@book01p> create table t as select * from all_objects; Table created. SCOTT@book01p> create index i_t_object_id on t(object_id); Index created. SCOTT@book01p> @ ind2 t Display indexes where table or index name matches t... TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- SCOTT                T                              I_T_OBJECT_ID                     1 OBJECT_ID INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS                         PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT -------------------- ------------------------------ ------------------------------ ---------- ---- ------------------------------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ --------- SCOTT                T                              I_T_OBJECT_ID                  NORMAL     NO   VALID                          NO   N     2        155         69968      69968       3547 2024-12-05 15:46:29 1      VISIBLE SCOTT@book01p> alter index I_T_OBJECT_ID unusable; Index altered. SCOTT@book01p> @ ind2 I_T_OBJECT_ID Display indexes where table or index name matches I_T_OBJECT_ID... TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- SCOTT                T                              I_T_OBJECT_ID                     1 OBJECT_ID INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS                         PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT -------------------- ------------------------------ ------------------------------ ---------- ---- ------------------------------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ --------- SCOTT                T                              I_T_OBJECT_ID                  NORMAL     NO   UNUSABLE                       NO   N     2        155         69968      69968       3547 2024-12-05 15:46:29 1      VISIBLE --//STATUS=UNUSABLE. SCOTT@book01p> truncate table t; Table truncated. SCOTT@book01p> @ ind2 I_T_OBJECT_ID Display indexes where table or index name matches I_T_OBJECT_ID... TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- SCOTT                T                              I_T_OBJECT_ID                     1 OBJECT_ID INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS                         PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT -------------------- ------------------------------ ------------------------------ ---------- ---- ------------------------------ ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ --------- SCOTT                T                              I_T_OBJECT_ID                  NORMAL     NO   VALID                          NO   N     2        155         69968      69968       3547 2024-12-05 15:46:29 1      VISIBLE --//可以发现truncate table t;后索引自动变为VALID。 SCOTT@book01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book01p> select count(*) from t;   COUNT(*) ----------          0 1 row selected. SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT -------------------------------------- SQL_ID  cyzznbykb509s, child number 0 ------------------------------------- select count(*) from t Plan hash value: 3095383276 ------------------------------------------------------------------------------------------------------------------------- | Id  | Operation             | Name          | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |               |      1 |        |    45 (100)|          |      1 |00:00:00.01 |       4 | |   1 |  SORT AGGREGATE       |               |      1 |      1 |            |          |      1 |00:00:00.01 |       4 | |   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID |      1 |  69968 |    45   (3)| 00:00:01 |      0 |00:00:00.01 |       4 | ------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / "T"@"SEL$1" --//object_id字段为not null，可以发现使用索引。