Home » RDBMS Server » Performance Tuning » Primary key - unusable (Oracle - 10g)
| Primary key - unusable [message #540081] |
Fri, 20 January 2012 03:19  |
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 #540085 is a reply to message #540083] |
Fri, 20 January 2012 03:47  |
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 #540372 is a reply to message #540335] |
Mon, 23 January 2012 02:15 |
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 |
John Watson
Messages: 8962
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 |
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 #540530 is a reply to message #540416] |
Tue, 24 January 2012 05:25 |
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 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 16:10:09 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
