查看oracle索引N A,index status N/A??

最新推荐文章于 2023-09-28 11:35:43 发布

Ezreal JI

最新推荐文章于 2023-09-28 11:35:43 发布

阅读量626

点赞数

文章标签：查看oracle索引N A

已经搞定了，也基本确定问题原因，昨天晚上重建了这个表上的分区索引，重建之后分区索引的统计信息丢失了，导入备份统计信息就ok了

未将rman环境的统计信息import进来前的statistics如下：

SQL> select * from dba_ind_statistics where TABLE_NAME='NTL_ASSIGNED_TASK';

OWNER INDEX_NAME TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS

----------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------------------ --------------------- ------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ---------- ----------------- ------------------- ----------- ------------- ------------ ---------- --------------- -----------

TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 554636 1616 695 1233 18088544 65589637 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_LTCHID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 634413 40886488 1 1 56203199 56684029 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_LTSHID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 2 483 46256 1 1 54890 55017 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_ORGTYAPT TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 275245 37855605 1 1 55064247 63248259 0 2011-1-29 0:3 NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_UM TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 352590 10918 26 3937 45381989 61846789 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_OLD_IFU TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 658101 21890584 1 1 30897719 62437577 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASKLASTCON TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 1182120 34116015 1 1 53700102 55538378 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_UPDT TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 1671884 461259 55 497 14155425 63685922 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_TFIRSTUM TMRLIFEDATA NTL_ASSIGNED_TASK INDEX NO NO ALL

TMRLIFEDATA IX_ASSIGNED_TASK_RETIME TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 139036 93 1486 12825 1198948 39999184 0 2011-1-26 23: NO NO ALL YES

TMRLIFEDATA PK_ASSIGNED_TASK_ID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 2 277306 63455223 1 1 47059334 63455223 254914 2011-1-27 22: YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_LTCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_LTCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_LTSHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 2 432 46256 1 1 46146 46256 46256 2011-1-26 23: YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_LTSHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 1 51 8761 1 1 8744 8761 8761 2011-1-25 5:2 YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_ORGTYAPT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_ORGTYAPT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION NO NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_UM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_UM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_OLD_IFU TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_OLD_IFU TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASKLASTCON TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASKLASTCON TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_UPDT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_UPDT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_TFIRSTUM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION NO NO ALL

TMRLIFEDATA IX_ASSIGNED_TASK_TFIRSTUM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION NO NO ALL

TMRLIFEDATA IX_ASSIGNED_TASK_RETIME TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION YES NO ALL YES

TMRLIFEDATA IX_ASSIGNED_TASK_RETIME TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION YES NO ALL YES

31 rows selected

Import进来后的statistics如下：

SQL> select a.* from dba_ind_statistics a where TABLE_NAME='NTL_ASSIGNED_TASK';

--------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------------------ --------------------- ------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ---------- ----------------- ------------------- ----------- ------------- ------------ ---------- --------------- -----------

TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 533620 871 612 19678 17139679 63282319 124876 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_LTCHID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 657749 57970425 1 1 60176651 60681343 106648 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_LTSHID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 2 521 58067 1 1 57933 58067 58067 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_ORGTYAPT TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 266825 48438255 1 1 53107169 60819336 236143 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_UM TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 380703 10542 49 6318 48456019 66414810 1959110 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_OLD_IFU TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 728443 31673686 1 1 32949340 67165455 107049 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASKLASTCON TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 1268062 52527923 1 1 59564333 62585846 57894 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_UPDT TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 1626355 52571 76 664 14095075 60070812 380070 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_TFIRSTUM TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 2 154542 5685002 1 9 52017182 55326182 388788 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_RETIME TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 3 157542 91 1731 14842 1350696 44638680 306295 2011-2-24 0:3 YES NO NO

TMRLIFEDATA PK_ASSIGNED_TASK_ID TMRLIFEDATA NTL_ASSIGNED_TASK INDEX 2 287803 65811056 1 1 49297480 65811056 257022 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 3 183694 1928 95 2655 5119560 20087486 117773 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 3 392009 328 1195 39682 13015991 46658539 138782 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_LTCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 3 439617 13264880 1 1 13215346 13264880 32135 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_LTCHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 2 169010 42412344 1 1 41986384 42412344 271524 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_LTSHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 2 463 48574 1 1 48458 48574 48574 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_LTSHID TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 1 58 9493 1 1 9475 9493 9493 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_ORGTYAPT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 3 95696 12245564 1 1 14290745 20781640 243006 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_ORGTYAPT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 3 198871 40370987 1 1 43326234 46669714 271282 2011-2-24 0:2 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_UM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 3 129984 11017 14 839 7713615 21007991 1740640 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_UM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 3 246258 7501 220 34837 38913565 45064339 206420 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_OLD_IFU TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 3 328632 6400251 1 1 7172946 20131279 68915 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_OLD_IFU TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 3 382588 24460641 1 1 24873943 45332863 135197 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASKLASTCON TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 3 740517 12557120 1 1 14181760 15651192 22932 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASKLASTCON TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 3 443971 36685698 1 1 41435948 42437074 106482 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_UPDT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 3 1432505 124230 61 545 12740556 21728228 171550 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_UPDT TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 3 308914 25403 35 199 1756477 45300779 1442990 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_TFIRSTUM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 2 41032 2314290 1 6 14152441 14689499 403466 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_TFIRSTUM TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 2 120298 4027830 1 9 40189111 43066622 406688 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_RETIME TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_ACTIVE 1 PARTITION 2 370 18 20 157 2836 100362 100362 2011-2-24 0:3 YES NO NO

TMRLIFEDATA IX_ASSIGNED_TASK_RETIME TMRLIFEDATA NTL_ASSIGNED_TASK PT_ASSITASK_RECSTAT_RECLAIM 2 PARTITION 3 152704 87 1755 14839 1291079 42827898 294768 2011-2-24 0:3 YES NO NO

SQL>

Ezreal JI

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
查看oracle索引N A,index status N/A??

已经搞定了，也基本确定问题原因，昨天晚上重建了这个表上的分区索引，重建之后分区索引的统计信息丢失了，导入备份统计信息就ok了未将rman环境的统计信息import进来前的statistics如下：SQL> select * from dba_ind_statistics where TABLE_NAME='NTL_ASSIGNED_TASK';OWNER INDEX_N...
复制链接

扫一扫