- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
1#
发表于 2012-3-15 22:47:45
|
查看: 4653 |
回复: 0
sys_op_lbid 函数 通过ROWID来访问INDEX的LEAF BLOCK, 来度量INDEX的好坏:
SYS_OP_LBID
Has many functional variations, but the point is to scan through index leaf blocks and calculate a measure of the quality of the index. The way it is counted, and the thing being counted, depends on the type of index (viz: bitmap, simple b-tree, local/global index, IOT, or secondary on IOT, cluster).
SYS_OP_LBID(<object_id>, <block_type>, <table_name.rowid>);
SQL> create table ML as select * from dba_objects;
Table created.
SQL> create index ind_ML ON ML(OBJECT_ID);
Index created.
SQL> SELECT object_id
2 FROM user_objects
3 WHERE object_name = 'IND_ML';
SQL> SELECT rows_per_block, count(*) blocks
2 FROM (
3 SELECT /*+ cursor_sharing_exact
dynamic_sampling(0)
no_monitoring
no_expand
index_ffs(ML,ix_ML)
noparallel_index(ML,ix_ML)
*/
sys_op_lbid(56227, 'L', ML.rowid) block_id,
COUNT(*) rows_per_block
FROM ML -- t1 sample block (100)
WHERE OBJECT_ID IS NOT NULL
GROUP BY sys_op_lbid(56227, 'L', ML.rowid))
GROUP BY rows_per_block;
4 5 6 7 8 9 10 11 12 13 14 15
ROWS_PER_BLOCK BLOCKS
-------------- ----------
485 1
479 15
449 90
478 4
225 1
448 1
6 rows selected.
SQL>
SQL> SELECT /*+ cursor_sharing_exact
2 dynamic_sampling(0)
3 no_monitoring
4 no_expand
5 index_ffs(ML,ix_ML)
6 noparallel_index(ML,ix_ML)
7 */
8 sys_op_lbid(56227, 'L', ML.rowid) block_id,
9 COUNT(*) rows_per_block
10 FROM ML -- t1 sample block (100)
11 WHERE OBJECT_ID IS NOT NULL
12 GROUP BY sys_op_lbid(56227, 'L', ML.rowid);
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9uUAAA 479
AAANujAABAAA9ukAAA 449
AAANujAABAAA9vJAAA 449
AAANujAABAAA9vKAAA 449
AAANujAABAAA9vLAAA 449
AAANujAABAAA9vTAAA 449
AAANujAABAAA9vlAAA 449
AAANujAABAAA9vmAAA 449
AAANujAABAAA9uYAAA 479
AAANujAABAAA9uZAAA 479
AAANujAABAAA9ueAAA 478
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9ugAAA 449
AAANujAABAAA9upAAA 449
AAANujAABAAA9uwAAA 449
AAANujAABAAA9uzAAA 449
AAANujAABAAA9u0AAA 449
AAANujAABAAA9u2AAA 449
AAANujAABAAA9vGAAA 449
AAANujAABAAA9vXAAA 449
AAANujAABAAA9vdAAA 449
AAANujAABAAA9vxAAA 449
AAANujAABAAA9v2AAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9v5AAA 449
AAANujAABAAA9v6AAA 225
AAANujAABAAA9uPAAA 479
AAANujAABAAA9uWAAA 479
AAANujAABAAA9uiAAA 449
AAANujAABAAA9ujAAA 449
AAANujAABAAA9unAAA 449
AAANujAABAAA9uyAAA 449
AAANujAABAAA9u4AAA 449
AAANujAABAAA9u7AAA 449
AAANujAABAAA9u9AAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9vEAAA 449
AAANujAABAAA9vHAAA 449
AAANujAABAAA9vOAAA 449
AAANujAABAAA9vPAAA 449
AAANujAABAAA9vRAAA 449
AAANujAABAAA9vWAAA 449
AAANujAABAAA9vZAAA 449
AAANujAABAAA9vcAAA 449
AAANujAABAAA9vpAAA 449
AAANujAABAAA9uNAAA 478
AAANujAABAAA9uTAAA 479
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9uaAAA 478
AAANujAABAAA9umAAA 449
AAANujAABAAA9uoAAA 449
AAANujAABAAA9uuAAA 449
AAANujAABAAA9u6AAA 449
AAANujAABAAA9vQAAA 449
AAANujAABAAA9vfAAA 449
AAANujAABAAA9viAAA 449
AAANujAABAAA9vjAAA 449
AAANujAABAAA9vrAAA 449
AAANujAABAAA9vtAAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9v1AAA 449
AAANujAABAAA9uLAAA 485
AAANujAABAAA9uhAAA 449
AAANujAABAAA9ulAAA 449
AAANujAABAAA9uxAAA 449
AAANujAABAAA9u5AAA 449
AAANujAABAAA9vDAAA 449
AAANujAABAAA9vaAAA 449
AAANujAABAAA9voAAA 449
AAANujAABAAA9vzAAA 448
AAANujAABAAA9v0AAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9uOAAA 479
AAANujAABAAA9uQAAA 479
AAANujAABAAA9ubAAA 479
AAANujAABAAA9ucAAA 479
AAANujAABAAA9u/AAA 449
AAANujAABAAA9vFAAA 449
AAANujAABAAA9vNAAA 449
AAANujAABAAA9vUAAA 449
AAANujAABAAA9vYAAA 449
AAANujAABAAA9veAAA 449
AAANujAABAAA9vhAAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9vsAAA 449
AAANujAABAAA9vwAAA 449
AAANujAABAAA9v4AAA 449
AAANujAABAAA9uMAAA 479
AAANujAABAAA9uSAAA 478
AAANujAABAAA9uVAAA 479
AAANujAABAAA9ufAAA 449
AAANujAABAAA9u+AAA 449
AAANujAABAAA9vAAAA 449
AAANujAABAAA9vBAAA 449
AAANujAABAAA9vIAAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9vbAAA 449
AAANujAABAAA9vgAAA 449
AAANujAABAAA9vkAAA 449
AAANujAABAAA9vvAAA 449
AAANujAABAAA9uRAAA 479
AAANujAABAAA9uXAAA 479
AAANujAABAAA9udAAA 479
AAANujAABAAA9uqAAA 449
AAANujAABAAA9urAAA 449
AAANujAABAAA9usAAA 449
AAANujAABAAA9utAAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9uvAAA 449
AAANujAABAAA9u1AAA 449
AAANujAABAAA9u3AAA 449
AAANujAABAAA9u8AAA 449
AAANujAABAAA9vCAAA 449
AAANujAABAAA9vMAAA 449
AAANujAABAAA9vSAAA 449
AAANujAABAAA9vVAAA 449
AAANujAABAAA9vnAAA 449
AAANujAABAAA9vqAAA 449
AAANujAABAAA9vuAAA 449
BLOCK_ID ROWS_PER_BLOCK
------------------ --------------
AAANujAABAAA9vyAAA 449
AAANujAABAAA9v3AAA 449 |
|