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

[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.