Home » SQL & PL/SQL » SQL & PL/SQL » Make use of UNUSABLE_INDEX by uisnh HInt (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
Make use of UNUSABLE_INDEX by uisnh HInt [message #654136] Wed, 27 July 2016 05:38 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

Is there any way to make use the index which is UNUSABLE (Other than REBUILD Option & SKIP_UNUSABLE_INDEXES Parameter)?

Following the is code snippet :

CREATE  TABLE  INDEX_NOTUSED (ID NUMBER PRIMARY KEY  ) ; 
INSERT INTO  INDEX_NOTUSED SELECT  ROWNUM+10  FROM DUAL CONNECT BY LEVEL  < 10  ;
SELECT  *  FROM USER_IND_COLUMNS  WHERE TABLE_NAME  ='INDEX_NOTUSED';
ALTER INDEX  SYS_C00123779  unusable    ;

Thanks
Sai Pradyumn

Re: Make use of UNUSABLE_INDEX by uisnh HInt [message #654143 is a reply to message #654136] Wed, 27 July 2016 07:01 Go to previous messageGo to next message
John Watson
Messages: 8558
Registered: January 2010
Location: Global Village
Senior Member
No. It is unusable.
Re: Make use of UNUSABLE_INDEX by uisnh HInt [message #654149 is a reply to message #654143] Wed, 27 July 2016 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
SKIP_UNUSABLE_INDEXES doesn't make the index usable, it just allows you to modify data in the table that the index is on.
Re: Make use of UNUSABLE_INDEX by uisnh HInt [message #654151 is a reply to message #654136] Wed, 27 July 2016 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67879
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the real question behind this one?

Re: Make use of UNUSABLE_INDEX by uisnh HInt [message #654482 is a reply to message #654151] Wed, 03 August 2016 13:37 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
An unusable index can only be skipped, never used. If you want to use the index you HAVE to rebuild it.

[Updated on: Wed, 03 August 2016 13:37]

Report message to a moderator

Previous Topic: Replace Accented Characters
Next Topic: Count group data
Goto Forum:
  


Current Time: Sat Jun 12 22:17:58 CDT 2021