删除分区后，索引变得无法使用，我该怎么办， [英] After drop the partition,the index became unusable,what should I do,

问题描述

我按月分区目标表进行间隔分区，并且只保留27个月的数据（因此需要每月删除最旧的分区）。
我使用下面的SQL删除它后，我运行SP，SP非常慢。

As I partitioned the target table for interval partitioning by month, and only keep 27 months data(so need drop the eldest partition monthly). After I use below SQL to drop it,I ran the SP, the SP was very slowly.

alter table target_table drop partition target_eldest_partition;

所以我取消了SP并分析了表格

So I cancel the SP and analyzed the table

ANALYZE TABLE target_table  COMPUTE STATISTICS;     

但遇到错误

Error starting at line : 12 in command -
ANALYZE TABLE per_limra COMPUTE STATISTICS
Error report -
ORA-01502: index 'target_index' or partition of such index is in unusable state
01502. 00000 -  "index '%s.%s' or partition of such index is in unusable state"
*Cause:    An attempt has been made to access an index or index partition
           that has been marked unusable by a direct load or by a DDL
           operation
*Action:   DROP the specified index, or REBUILD the specified index, or
           REBUILD the unusable index partition

所以我用Google搜索并得到了一些问题，请帮忙。

So I googled it and got some questions,Please help.

问题1：ANALYZE TABLE不重建索引，对吗？

Question 1: ANALYZE TABLE does not rebuild the index,right?

问题2：索引错误原因， *原因：........通过直接加载是什么意思？

Question 2: index error cause,what does *Cause: ........ by a direct load mean?

问题3：背景d，实际上我的目标表的索引现在通过以下SQL检查不可用。

Question 3: background ,actually the index for my target table are unusable now by checked with below SQL.

SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

创建我的分区表时使用以下SQL创建索引

I create my index when created my partitioned table with below SQL

  CREATE INDEX "schema_name"."target_index1" ON "schema_name"."target_table" ("col1") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "target_tablespace" ;

我知道在使用分区表时，索引有两种类型，全局索引和本地索引，什么我的索引定义属于哪个？似乎我没有使用分区索引，因为我没有在这里找到我的目标表分区名称。

I know when use partitioned table,there are two type for indexes, Global index and Local index, what does my index definition belong to? seems I did not use the partitioned index as I did not found my target table partition name here.

问题4：如何解决无法使用的索引问题，在删除分区后我需要一个成熟的解决方案。

Question 4: What should I do to solve the unusable index issue, I need a mature solutions after I drop the partition.

问题5：我的目标表有5000万个数据，每个月大约有1,900,000个，所以我认为使用 DBMS_STATS.GATHER_TABLE_STATS 或分析表target_table COMPUTE STATISTICS 更新统计数据是必要的，但这太慢了，还有其他任何解决方案。

Question 5:My target table has 50 million data,and for each month about 1,900,000, So I think use DBMS_STATS.GATHER_TABLE_STATSor ANALYZE TABLE target_table COMPUTE STATISTICS to update the statistics is necessary, but that was too slow,have any other solutions.

请帮忙提供建议。谢谢。

Please help give advice. Thanks.

推荐答案

• ANALYZE TABLE不会重建索引，对吗？

  • ANALYZE TABLE does not rebuild the index,right?

    我不知道但是现在你应该使用 DBMS_STATS.GATHER_TABLE_STATS 而不是 ANALYZE TABLE

    I don't know but nowadays you should use DBMS_STATS.GATHER_TABLE_STATS rather than ANALYZE TABLE

    直接加载意味着数据不是逐行插入的，而是作为批量插入，请参阅直接路径加载

    Direct load means data is not inserted row by row but as bulk, see Direct Path Load

    如果您的索引变为 UNUSABLE ，那么它必须是全局索引。

    If your index become UNUSABLE then it must be a global index.

    使用 UPDATE GLOBAL INDEXES 子句，即 alter table target_table drop partition target_eldest_partition UPDATE GLOBAL INDEXES; 或创建本地索引。

    Use the UPDATE GLOBAL INDEXES clause, i.e. alter table target_table drop partition target_eldest_partition UPDATE GLOBAL INDEXES; or create local indexes.

    取决于您编入索引的列。您也可以运行 DBMS_STATS.GATHER_INDEX_STATS ，而不是整个表。使用 DBMS_STATS.GATHER_TABLE_STATS ，您还可以指定单个分区甚至单个列。

    Depends on which columns you have indexed. Instead of entire table you can also run DBMS_STATS.GATHER_INDEX_STATS. With DBMS_STATS.GATHER_TABLE_STATS you can also specify just single partitions and even single columns.

    btw，对于很多问题，我的回答实际应该是：你查过Oracle文档了吗？或者你知道谷歌吗？根据您的屏幕截图，您可以为您的用户对象使用schema SYS 和tablespace SYSAUX 。你不应该这样做。创建自己的用户并在此模式中创建任何对象。

    btw, for many questions my answer actually should be: "Did you consult the Oracle documentation"? or "Do you know google"? According to your screenshots you use schema SYS and tablespace SYSAUX for your user objects. You should not do that. Create your own user and create any object in this schema.

    通常有三种类型的分区索引：

    In general there are three types of partitioned indexes:

    
    
    1. GLOBAL INDEX：你有一个大的索引跨越整个表。如果索引列不是分区键的一部分，则这对于UNIQUE INDEXES是必需的。实际上这样的索引没有被分区。 （如 ALL_INDEXES 所示）
    
    
    2. LOCAL INDEX：此索引的分区方式与基础表的分区方式相同。每个表分区都有一个相应的索引分区。
    
    
    3. PARTITIONED INDEX：此索引是分区的，但不同比基础表。我认为甚至可以在非分区表上创建分区索引。分区索引仅限于非常特殊的用例。实际上我无法想象这样的指数何处才有意义。
    
    
    1. GLOBAL INDEX: You have one big index spanning over entire table. This is mandatory for instance for UNIQUE INDEXES if the indexed columns are not part of the partition key. Actually such index is not partitioned. (as shown in ALL_INDEXES)
    2. LOCAL INDEX: This index is partitioned in the same way as the underlying table is. Each table partition has an according index partition.
    3. PARTITIONED INDEX: This index is partitioned but different than the underlying table. I think it is even possible to create a partitioned index on a non-partitioned table. Partitioned indexes are limited to very special use cases only. Actually I cannot imagine where such index would make sense.

    这篇关于删除分区后，索引变得无法使用，我该怎么办，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！