[20231116]如何知道X表存在那些索引.txt

--//同事问的问题,一下子想不起来,看了一些资料,做一个记录:

1.环境:
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
--//以X$KGLOB为例子说明,查询V$INDEXED_FIXED_COLUMN就可以知道.
SYS@book> select * from V$INDEXED_FIXED_COLUMN where table_name='X$KGLOB';
TABLE_NAME INDEX_NUMBER COLUMN_NAME          COLUMN_POSITION
---------- ------------ -------------------- ---------------
X$KGLOB               2 KGLOBT03                           0
X$KGLOB               1 KGLNAHSH                           0

SYS@book> select * from X$KGLOB where kglobt03 = 'a5086qgsk6f7r';
SYS@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5kg2rbpjjuuv9, child number 0
-------------------------------------
select * from X$KGLOB where kglobt03 = 'a5086qgsk6f7r'
Plan hash value: 762486365
---------------------------------------------------------------------------------
| Id  | Operation               | Name            | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |        |       |     1 (100)|
|*  1 |  FIXED TABLE FIXED INDEX| X$KGLOB (ind:2) |      1 |  6820 |     0   (0)|
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / X$KGLOB@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("KGLOBT03"='a5086qgsk6f7r')
--//X$KGLOB (ind:2) 表示使用第2个索引.

SYS@book> select * from V$INDEXED_FIXED_COLUMN where table_name in ('X$KGLOB','X$KGLDP','X$KGLCURSOR');
TABLE_NAME INDEX_NUMBER COLUMN_NAME          COLUMN_POSITION
---------- ------------ -------------------- ---------------
X$KGLOB               2 KGLOBT03                           0
X$KGLOB               1 KGLNAHSH                           0
X$KGLDP               1 KGLNAHSH                           0
--//可以看到X$KGLCURSOR根本没有索引. X$KGLDP 的索引是KGLNAHSH.COLUMN_POSITION=0没有任何意义.

SYS@book> select * from sys.x$kglcursor where kglobt03 = 'a5086qgsk6f7r'
SYS@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2bhys3h03p8u9, child number 1
-------------------------------------
select * from sys.x$kglcursor where kglobt03 = 'a5086qgsk6f7r'
Plan hash value: 3402452187
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |      1 |        |       |     1 (100)|      3 |00:00:00.01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KGLCURSOR (ind:2) |      1 |      1 |  6820 |     0   (0)|      3 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------------------
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"='a5086qgsk6f7r')
--//可以发现实际上还是有索引的.
--//很明显也不能完全通过V$INDEXED_FIXED_COLUMN视图验证,存在一些例外.
--//顺便说明一下X$并不是真正意义上的表,有一些可能是数组或者链表.其索引也不是真正意义的索引.

--//找到自己以前的链接: http://blog.itpub.net/267265/viewspace-2775012/ =>[20210528]V$INDEXED_FIXED_COLUMN视图.txt
http://ermanarslan.blogspot.com/2021/04/rdbms-vindexedfixedcolumn-useful-view.html

In this context, if a performance problem appears in a query on some v$ views, it would be useful to look at the
execution plan. If we see FIXED TABLE FULL when accessing x$ tables,  we can get the information from
V$INDEXED_FIXED_COLUMN which columns of the related x$ table are indexed and then we may change our query to make the
optimizer use that index and thus solve the problem.