Home » RDBMS Server » Performance Tuning » Primary key - unusable (Oracle - 10g)
Primary key - unusable [message #540081] Fri, 20 January 2012 03:19 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
when I check one database, I noticed that the primary key constraint is in UNUSABLE status.

But when I check the table, the table DDL itself,the below query mentioned.

ALTER INDEX "COMMON_DATA"."PK_DISE_MOBILE"  UNUSABLE ENABLE


Now can you pls suggest whether I need rebuild this index? if I rebuild this index, will it useful for me for the performance improvement? But the table has only 578234 records.

Table DDL

  CREATE TABLE "COMMON_DATA"."DISE_MOBILE_INSTALLATION"
   (    "M_DBASE_ID" VARCHAR2(2),
        "COMPANY_NUMBER" NUMBER(3,0),
        "M_ACCT_ID" NUMBER(8,0),
        "ACCOUNT_CREATION_DATE" DATE,
        "ORDER_NUMBER" VARCHAR2(15),
        "ORDER_CREATION_DATE" DATE,
        "ORDER_CREATION_METHOD" VARCHAR2(1),
        "CONTRACT_TERM" VARCHAR2(3),
        "GROUP_ID" VARCHAR2(6),
        "CORPORATE_ID" VARCHAR2(6),
        "TARIFF" VARCHAR2(6),
        "SUBSCRIPTION_NUMBER" VARCHAR2(8),
        "SUBSCRIPTION_CREATION_DATE" DATE,
        "SUBSCRIPTION_CON_DATE" DATE,
        "SUBSCRIPTION_DISCON_DATE" DATE,
        "NETWORK_ID" VARCHAR2(25),
        "COMPANY_NAME" VARCHAR2(63),
        "CONTACT_TEL_NO" VARCHAR2(15),
        "SALES_ACCOUNT" VARCHAR2(30),
        "SALES_AGENT_ID" VARCHAR2(30),
        "LEGAL_ENTITY" VARCHAR2(12),
        "SALES_ACCOUNT_CODE" VARCHAR2(12),
        "CONTRACT_ID" VARCHAR2(60),
        "ACCOUNT_TYPE_DESCRIPTION" VARCHAR2(30),
        "PACKAGE_NAME" VARCHAR2(30),
        "CONTACT_NAME" VARCHAR2(64),
        "POSTCODE" VARCHAR2(10),
        "SUB_LEDGER_CODE" VARCHAR2(3),
        "CONTRACT_EXPIRY_DATE" DATE,
        "ORDER_STATUS" VARCHAR2(25),
         CONSTRAINT "PK_DISE_MOBILE" PRIMARY KEY ("M_DBASE_ID", "M_ACCT_ID", "NETWORK_I
D")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ADDER_INDEX"
  ALTER INDEX "COMMON_DATA"."PK_DISE_MOBILE"  UNUSABLE ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 20971520 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ADDER_DATA"


And there are some other indexes in this table. those are listed below.

INDEX_NAME                     COLUMN_NAME                        TABLE_NAME
------------------------------ ---------------------------------- ------------------------------
IDX_DISE_MOBILE_AC             M_ACCT_ID                          DISE_MOBILE_INSTALLATION
IDX_DISE_MOBILE_AC             M_DBASE_ID                         DISE_MOBILE_INSTALLATION
IDX_DISE_MOBILE_NETWORK_ID     NETWORK_ID                         DISE_MOBILE_INSTALLATION
PK_DISE_MOBILE                 M_DBASE_ID                         DISE_MOBILE_INSTALLATION
PK_DISE_MOBILE                 M_ACCT_ID                          DISE_MOBILE_INSTALLATION
PK_DISE_MOBILE                 NETWORK_ID                         DISE_MOBILE_INSTALLATION

Re: Primary key - unusable [message #540083 is a reply to message #540081] Fri, 20 January 2012 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As it is the primary key (as you say), yes you have to rebuild it.

Regards
Michel
Re: Primary key - unusable [message #540085 is a reply to message #540083] Fri, 20 January 2012 03:47 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Thanks for your response Michel.

I know sometime for loading purpose, we will bring the index as UNUSABLE to speed up the loading process.

But here whether this index is in unusable status from the table createad date? or did it go to UNUSABLE state becasue of some reason after table creation? What this alter index means in this create table statment?

Also do you think the other 2 indexes will speed up the select statement, eventhough the primary key index is UNUSABLE status?

Also just before I found the below.

SQL> SELECT constraint_name, constraint_type, index_name,status   FROM dba_constraints  
WHERE table_name = 'DISE_MOBILE_INSTALLATION';

CONSTRAINT_NAME                C INDEX_NAME                     STATUS
------------------------------ - ------------------------------ --------
PK_DISE_MOBILE                 P PK_DISE_MOBILE                 ENABLED


I understand from the above output, the constraint is enabled.but the index associated with this constraint is UNUSABLE. Am I wrong?

or is it possible to have Pk with unusable index in oracle?

[Updated on: Sun, 22 January 2012 05:01] by Moderator

Report message to a moderator

Re: Primary key - unusable [message #540333 is a reply to message #540085] Sun, 22 January 2012 04:47 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi Michel,

Can you pls respond to my above questions?
Re: Primary key - unusable [message #540335 is a reply to message #540333] Sun, 22 January 2012 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add index_owner, index_name and invalid to your query on dba_constraints.

Regards
Michel
Re: Primary key - unusable [message #540372 is a reply to message #540335] Mon, 23 January 2012 02:15 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
SQL> SELECT constraint_name, constraint_type, index_name,INDEX_OWNER,INVALID,STATUS 
FROM dba_constraints WHERE table_name = 'DISE_MOBILE_INSTALLATION';

CONSTRAINT_NAME                C INDEX_NAME                     INDEX_OWNER         INVALID STATUS
------------------------------ - ------------------------------ ------------------ ------- --------
PK_DISE_MOBILE                 P PK_DISE_MOBILE                 COMMON_DATA                 ENABLED

[Updated on: Tue, 24 January 2012 05:30] by Moderator

Report message to a moderator

Re: Primary key - unusable [message #540374 is a reply to message #540372] Mon, 23 January 2012 02:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It is possible to have an unusable index and an enabled constraint, but you'll find that the table is locked for DML because Oracle can't check the constraint:
orcl> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_EMP                         VALID
PK_DEPT                        VALID

orcl> select constraint_name,status from user_constraints;

CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DEPTNO                      ENABLED
PK_DEPT                        ENABLED
PK_EMP                         ENABLED

orcl> alter table emp move;

Table altered.

orcl> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_EMP                         UNUSABLE
PK_DEPT                        VALID

orcl> select constraint_name,status from user_constraints;

CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DEPTNO                      ENABLED
PK_DEPT                        ENABLED
PK_EMP                         ENABLED

orcl> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_EMP' or partition of such index is in unusable state


orcl>
Re: Primary key - unusable [message #540415 is a reply to message #540374] Mon, 23 January 2012 07:22 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
From the above I understood, we cannot fire any DML on this table, if an index used by the primary key is unusable. pls correct me if my understanding is wrong.

But in this table DISE_MOBILE_INSTALLATION,insert is firing successfully. it is not giving any error. it is really confusing me.I am not sure how it is happening. pls let me know how to proceed further?

[Updated on: Mon, 23 January 2012 07:34]

Report message to a moderator

Re: Primary key - unusable [message #540416 is a reply to message #540415] Mon, 23 January 2012 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Verify you are inserting in the table you think you insert
2/ Try to insert a dummy row inside this table using the complete naming: schema.table

Regards
Michel
Re: Primary key - unusable [message #540530 is a reply to message #540416] Tue, 24 January 2012 05:25 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Sorry Michel. They are inserting data through SQL loader. it is not giving any error in the sql loader log. But in the alert log, ORA-20000 error is occured whenever this loading job starts.

I need your help to understand the concepts.

1. if I load the data in this table through sqlloader,pk will be getting updated but it's associated index will not be updated. Am I right or wrong?
2. select query will face slow performance when we specify the primary key columns in the where clause.
3. All DML queries will fail.
4. In my local database, when I try to gather statistics for this table, I got the below error because of the unusable index.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SATHIK',tabname => 'TESTTB1',cascade => TRUE,
granularity=> 'GLOBAL',estimate_percent => 10,block_sample
=> TRUE );
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SATHIK',tabname => 'TESTTB1',cascade => TRUE,
granularity=> 'GLOBAL',estimate_percent => 10,block_sample => T
RUE ); END;

*
ERROR at line 1:
ORA-20000: index "SATHIK"."PK_TESTTB1"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1 


But the GATHER_STATS_JOB has collected the statistics succssfully for DISE_MOBILE_INSTALLATION table. how it is possible?

SQL> select table_name,last_analyzed,owner from dba_tables where table_name ='DISE_MOBILE_INSTALLATION';

TABLE_NAME                     LAST_ANAL OWNER
------------------------------ --------- ------------------------------
DISE_MOBILE_INSTALLATION       23-JAN-12 COMMON_DATA 


5. is there anyother impact in database becasue of this unusable index?

[Updated on: Tue, 24 January 2012 05:28] by Moderator

Report message to a moderator

Re: Primary key - unusable [message #540531 is a reply to message #540530] Tue, 24 January 2012 05:34 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the constraint is enable and the index unusable you cannot insert a new value or update/delete an old one as John showed it.

For the rest, I can't say as I can't see what is the exact situation.

Regards
Michel
Previous Topic: Query execution path
Next Topic: Can anyone help to do some tuning on ORACLE 9i?
Goto Forum:
  


Current Time: Thu Mar 28 08:56:08 CDT 2024