[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