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