[20240913]关于X$的索引疑问.txt

[20240913]关于X$的索引疑问.txt --//前不久我写一个查询X$索引的脚本，发现一个问题，查询不到一些X$表的索引,今天才明白为什么,做一个记录: 1.环境: SYS@book> @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. $ cat xind.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   xind.sql -- Purpose:     query X$ index -- Author:      lfree -- -- Usage: --     @ xind <x$table_name,...> --     @ xind <x$kglob,x$kgldp> -------------------------------------------------------------------------------- set termout off column column_string new_value column_string format a200 select decode('&1','','1=1','1','1=1','1=1','1=1','table_name in ('||''''||replace(upper('&1'),',',''',''')||''')' ) column_string from dual ; set termout on select * from V$INDEXED_FIXED_COLUMN where 1=1 and (&column_string); 2.问题提出: SYS@book> @ xind x$kglcursor no rows selected --//查询不到索引.而实际上执行如下： SYS@book> select * from x$kglcursor where kglobt03='aaaa'; no rows selected SYS@book> @dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  gstvqddc7m9am, child number 0 ------------------------------------- select * from x$kglcursor where kglobt03='aaaa' Plan hash value: 3402452187 ------------------------------------------------------------------------------------- | Id  | Operation               | Name                | E-Rows |E-Bytes| Cost (%CPU)| ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                     |        |       |     1 (100)| |*  1 |  FIXED TABLE FIXED INDEX| X$KGLCURSOR (ind:2) |      1 | 21804 |     0   (0)| ------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / "X$KGLCURSOR"@"SEL$1" Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("KGLOBT03"='aaaa') --//看执行计划明显使用索引.而实际上查看gv$fixed_table定义： SYS@book> @ v2 gv$fixed_table Show SQL text of views matching "gv$fixed_table"... no rows selected VIEW_NAME                      TEXT ------------------------------ ---------------------------------------------------------------------------------------------------- GV$FIXED_TABLE                 select inst_id,kqftanam, kqftaobj, 'TABLE', indx, con_id from x$kqfta union all select                                inst_id,kqfvinam, kqfviobj, 'VIEW', 65537, con_id from x$kqfvi union all select inst_id,kqfdtnam,                                kqfdtobj, 'TABLE', 65537, con_id from x$kqfdt --//分成3个部分,其中x$kqfta, x$kqfdt涉及到表. --//x$kqfta – headline information about the x$ structures – name, size, column count. --//x$kqfvi – a list of the names of the dynamic performance views (mostly pairs of v$/gv$) --//x$kqfvt – a list of the select statements that define the views in x$kqfvi --//x$kqfdt – a list of "derived tables": a cross between synonyms and views of a few of the structures in x$kqfta ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//x$kqfco – a list of the "columns" in each of the tables in x$kqfta (but not x$kqfvi and x$kqfdt) --//而x$kglcursor来之x$kqfdt,按照上面的提示类似于视图或者synonyms的信息. SYS@book> column KQFDTEQU format a30 SYS@book> column KQFDTNAM format a30 SYS@book> SELECT *  FROM x$kqfdt where kqfdtnam like 'X$KGL%'; ADDR                   INDX    INST_ID     CON_ID   KQFDTOBJ KQFDTNAM                       KQFDTEQU ---------------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ 0000000016DE99F0         10          1          0 4294951056 X$KGLTABLE                     X$KGLOB 0000000016DE9A18         11          1          0 4294951057 X$KGLBODY                      X$KGLOB 0000000016DE9A40         12          1          0 4294951058 X$KGLTRIGGER                   X$KGLOB 0000000016DE9A68         13          1          0 4294951059 X$KGLINDEX                     X$KGLOB 0000000016DE9A90         14          1          0 4294951060 X$KGLCLUSTER                   X$KGLOB 0000000016DE9AB8         15          1          0 4294951061 X$KGLCURSOR                    X$KGLOB 0000000016DE9AE0         16          1          0 4294952684 X$KGLCURSOR_CHILD_SQLID        X$KGLOB 0000000016DE9B08         17          1          0 4294952680 X$KGLCURSOR_CHILD_SQLIDPH      X$KGLOB 0000000016DE9B30         18          1          0 4294952683 X$KGLCURSOR_CHILD              X$KGLOB 0000000016DE9B58         19          1          0 4294953372 X$KGLCURSOR_PARENT             X$KGLOB 0000000016DE9B80         20          1          0 4294953759 X$KGLSQLTXL                    X$KGLOB 11 rows selected. --//很明显这些X$派生于X$KGLOB,这样就很好理解为什么我前面的xind.sql脚本查询不到索引,实际上来自X$KGLOB. SYS@book> @ xind x$kglob TABLE_NAME                     INDEX_NUMBER COLUMN_NAME                    COLUMN_POSITION     CON_ID ------------------------------ ------------ ------------------------------ --------------- ---------- X$KGLOB                                   1 KGLNAHSH                                     0          0 X$KGLOB                                   2 KGLOBT03                                     0          0 --//x$kglob的KGLOBT03字段是有索引的。 3.建立新的xind.sql查询脚本： $ cat xind.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   xind.sql -- Purpose:     query X$ index -- Author:      lfree -- -- Usage: --     @ xind <x$table_name,...> --     @ xind <x$kglob,x$kgldp> -------------------------------------------------------------------------------- set termout off column column_string new_value column_string format a200 --select decode('&1','','1=1','1','1=1','1=1','1=1','table_name in ('||''''||replace(upper('&1'),',',''',''')||''')' ) column_string1 from dual ; select decode('&1','','1=1','1','1=1','1=1','1=1',''''||replace(upper('&1'),',',''',''')||'''') column_string from dual ; set termout on --//select * from V$INDEXED_FIXED_COLUMN where 1=1 and table_name in (&column_string); set feedback off column derived_tables format a30 SELECT NULL derived_tables, V$INDEXED_FIXED_COLUMN.*   FROM V$INDEXED_FIXED_COLUMN  WHERE 1 = 1 AND  table_name in (&column_string) -- WHERE 1 = 1 AND  regexp_like (table_name ,upper('&1')) ; SELECT x$kqfdt.KQFDTNAM derived_tables, V$INDEXED_FIXED_COLUMN.*   FROM V$INDEXED_FIXED_COLUMN, x$kqfdt  WHERE  1 = 1        AND V$INDEXED_FIXED_COLUMN.table_name = x$kqfdt.KQFDTEQU        AND x$kqfdt.kqfdtnam in (&column_string) --     AND regexp_like ( x$kqfdt.kqfdtnam ,upper('&1')) order by x$kqfdt.KQFDTNAM ,INDEX_NUMBER ; prompt set feedback 6 SYS@book> @ xind x$kgldp,x$kglob,x$kglcursor,X$KCCFNCDB DERIVED_TABLES TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION     CON_ID -------------- ---------- ------------ ----------- --------------- ----------                X$KGLOB               1 KGLNAHSH                  0          0                X$KGLOB               2 KGLOBT03                  0          0                X$KGLDP               1 KGLNAHSH                  0          0 DERIVED_TABLES TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION     CON_ID -------------- ---------- ------------ ----------- --------------- ---------- X$KCCFNCDB     X$KCCFN               1 FNNUM                     0          0 X$KGLCURSOR    X$KGLOB               1 KGLNAHSH                  0          0 X$KGLCURSOR    X$KGLOB               2 KGLOBT03                  0          0