[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，可以发现使用索引。