Home » RDBMS Server » Server Administration » exchange partition with update indexes issue (11.2.0.1 windows xp)
exchange partition with update indexes issue [message #530213] Mon, 07 November 2011 03:54 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
I exchange a partition with into a normal table with UPDATE INDEXES,but i found the index of both table are marked UNUSABLE? why
SQL> Create Table tb_hxl_list_part
  2  (
  3    statedate Number,
  4    provcode  Number
  5  )
  6  Partition By List(provcode)
  7  (
  8    Partition p_1 Values(1)
  9  );

Table created.

SQL> Create Unique Index idx_tb_hxl_list_part On tb_hxl_list_part(provcode) Local;

Index created.

SQL> Insert Into tb_hxl_list_part Values(20111101,1);

1 row created.

SQL> commit;

Commit complete.


SQL> Select status From dba_ind_partitions aa
  2  Where aa.index_name = 'IDX_TB_HXL_LIST_PART';

STATUS
--------
USABLE

SQL> Create Table tb_hxl_list_part_bak
  2  (
  3    statedate Number,
  4    provcode  Number
  5  );

Table created.

SQL> Create Unique Index idx_hxl_list_part_bak On tb_hxl_list_part_bak(provcode);

Index created.

SQL> Select status From dba_indexes bb
  2  Where bb.index_name = 'IDX_HXL_LIST_PART_BAK';

STATUS
--------
VALID

SQL> Alter Table tb_hxl_list_part
  2  Exchange Partition p_1
  3  With Table TB_HXL_LIST_PART_bak UPDATE Indexes;

Table altered.

SQL> Select status From dba_ind_partitions aa
  2  Where aa.index_name = 'IDX_TB_HXL_LIST_PART';

STATUS
--------
UNUSABLE

SQL> Select status From dba_indexes bb
  2  Where bb.index_name = 'IDX_HXL_LIST_PART_BAK';

STATUS
--------
UNUSABLE
Re: exchange partition with update indexes issue [message #530286 is a reply to message #530213] Mon, 07 November 2011 09:02 Go to previous messageGo to next message
John Watson
Messages: 4479
Registered: January 2010
Location: Global Village
Senior Member
You need to use the INCLUDING INDEXES clause. There is a nice example in the Recent Blog Posts. Read it.
Re: exchange partition with update indexes issue [message #530590 is a reply to message #530286] Wed, 09 November 2011 03:48 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
John, if there is a global index of a table,exchange partition with the keyword update global indexes,and the global index is valid.
Re: exchange partition with update indexes issue [message #530591 is a reply to message #530590] Wed, 09 November 2011 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, don't forget to ALWAYS feedback.
And it does not hurt to thank to given the time we spent to help you in these last 4 months.

Regards
Michel
Re: exchange partition with update indexes issue [message #530598 is a reply to message #530591] Wed, 09 November 2011 04:01 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Previous Topic: OEM and Audit Vault
Next Topic: control file reuse
Goto Forum:
  


Current Time: Wed Aug 20 19:50:14 CDT 2014

Total time taken to generate the page: 0.05607 seconds