When Oracle makes adjustments to partitions, add update global indexes and oracleindexes.

Source: Internet
Author: User

When Oracle makes adjustments to partitions, add update global indexes and oracleindexes.

When you perform ddl operations on a partition, the global index of the partition is invalid. You need to add the keyword update global indexes.

SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

Drop table t_range purge;
Create table t_range (id number not null primary key, test_date date)
Partition by range (test_date)
(
Partition p_2014_11 values less than (to_date ('2017-11-01 ', 'yyyy-mm-dd ')),
Partition p_2014_12 values less than (to_date ('2017-12-01 ', 'yyyy-mm-dd ')),
Partition p_2015_01 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd ')),
Partition p_2015_02 values less than (to_date ('2017-02-01 ', 'yyyy-mm-dd ')),
Partition p_2015_03 values less than (to_date ('2017-03-01 ', 'yyyy-mm-dd '))
);

Insert/* + append */into t_range select rownum,
To_date (to_char (sysdate-140, 'J') +
Trunc (dbms_random.value (0, 80 )),
'J ')
From dual
Connect by rownum <= 100000;

Create index ind_t_range_date on t_range (test_date) nologging;
Select * from t_range;
Exec dbms_stats.gather_table_stats (user, 't_ range', cascade => true );

Set autotrace traceonly
Select/* + index (t_range ind_t_range_date) */count (1) from t_range where
Test_date = TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
Execution Plan
----------------------------------------------------------
Plan hash value: 2542800765
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 8 | 23 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 8 |
| * 2 | index range scan | IND_T_RANGE_DATE | 1243 | 9944 | 23 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("TEST_DATE" = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss '))
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
6 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL * Net to client
415 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Set autotrace off

Alter table t_range drop partition p_2014_11;
Set autotrace traceonly
Select/* + index (t_range ind_t_range_date) */count (1) from t_range where
Test_date = TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
Select/* + index (t_range ind_t_range_date) */count (1) from t_range where
*
Row 3 has an error:
ORA-01502: Index 'test. IND_T_RANGE_DATE 'or the partition of this type of index is unavailable
Set autotrace off;

Alter index ind_t_range_date rebuild nologging;

Alter table t_range drop partition p_2014_12 Update global indexes;
Set autotrace traceonly
Select/* + index (t_range ind_t_range_date) */count (1) from t_range where
Test_date = TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
Execution Plan
----------------------------------------------------------
Plan hash value: 2542800765
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 8 | 13 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 8 |
| * 2 | index range scan | IND_T_RANGE_DATE | 1243 | 9944 | 13 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("TEST_DATE" = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss '))
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
6 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL * Net to client
415 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Set autotrace off;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.