如何查看 Oracle 索引的内容？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3459931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I see the contents of an Oracle index?
提问by Moeb
Is it possible to have a look at what is there inside an index using SQL*Plus?
是否可以使用 SQL*Plus 查看索引中的内容？
If I have a table like this:
如果我有一张这样的表：
Table A
------------------------
rowid | id name
123 | 1 A
124 | 4 G
125 | 2 R
126 | 3 P
where idis the primary key, I expect the index to be something like this
id主键在哪里，我希望索引是这样的
index on A.id
-------------
id rowid
1 123
2 125
3 126
4 124
Is there some SQL query using which I can actually see the contents of an index?
是否有一些 SQL 查询使用我可以实际查看索引的内容？
回答by Adam Musch
There's not a simple query - you can dump table or index blocks to trace files, but you have to identify the block you're interested in. Dion Cho has an exampleof how you can process this block dump with SQL, but it's not for the faint of heart.
没有一个简单的查询 - 您可以将表或索引块转储到跟踪文件，但您必须确定您感兴趣的块。 Dion Cho 有一个示例，说明如何使用 SQL 处理此块转储，但它不适用于胆小的人。
However, you can do this:
但是，您可以这样做：
select /* index_ffs (a [name of index]) */
id, rowid
from a
where id is not null
order by id, rowid;
Oracle doesn't write index entries where all of the values being indexed are null, so if id is nullable, we need to filter those out. The index_ffs hint forces Oracle to satisfy the query reading the index blocks, not the data blocks. This "solution" doesn't show the data which would be in root or branch blocks, whereas a block dump can.
Oracle 不会写入所有被索引的值都为空的索引条目，因此如果 id 可以为空，我们需要将它们过滤掉。index_ffs 提示强制 Oracle 满足读取索引块而不是数据块的查询。这个“解决方案”不显示根块或分支块中的数据，而块转储可以。
回答by HankerPL
If you are indexing EMP_ID - so all values from that particular column are indexed as leafs - e.g. B-Tree ASC index - on left site of the leafs part you should see MIN(indexed_value) or right site you should see MAX(indexed_value) - for DESC in opposite. But if you are asking for BRANCHES/ROOT values I can't help you - but in general it is not that important. explain plan for:
如果您正在索引 EMP_ID - 所以来自该特定列的所有值都被索引为叶子 - 例如 B-Tree ASC 索引 - 在叶子部分的左侧站点您应该看到 MIN(indexed_value) 或右侧站点您应该看到 MAX(indexed_value) -反之 DESC。但是，如果您要求 BRANCHES/ROOT 值，我帮不了您——但总的来说，这并不重要。解释计划：
select **min**(emp_id)
from emp
where emp_id < 100;
select **max**(emp_id)
from emp
where emp_id < 100;
and compare it to each other (CARDINALITY 1) - it read only one row from object IND_EMP_ID
并将其相互比较（CARDINALITY 1） - 它仅从对象 IND_EMP_ID 中读取一行
and then explain plan for:
select **min**(employee_id), **max**(employee_id)
from employees
where employee_id < 100;
--CARDINALITY 40- between min and max only 40 different values in index are placed - all values from column emp_id.
-- CARDINALITY 40- 在 min 和 max 之间只放置了 40 个不同的索引值 - 列 emp_id 中的所有值。
Do not forget that even for PK you can still using B-TREE/BITMAP INDEX UNIQUE or NON-UNIQUE.
不要忘记，即使对于 PK，您仍然可以使用 B-TREE/BITMAP INDEX UNIQUE 或 NON-UNIQUE。
