SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?
Nov 29, 2010
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
after merging two partitions into single partition (partition is by list) of a table ,when i analyzed the table it is giving this error : ORA-01502 INDEX TEST.PK_ID or partition of such index is in unusable state.
2437312:ORA-12801: error signaled in parallel query server P009 2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state
and tried to rebuild the index and i got following error.
ORA-14086: a partitioned index may not be rebuilt as a whole.
The table size for the index is large. we need to rebuild the hole index.
how to partition and index my table for a special problem.
The table:
CREATE TABLE TEST ( ID NUMBER PRIMARY KEY, U_VALUE NUMBER NOT NULL, -- Ranges from 0 - 30.000.000 O_VALUE NUMBER NOT NULL, -- Ranges from U_VALUE - 30.000.000 CREATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL, UPDATE_TS TIMESTAMP NOT NULL, ITEM_TYPE NUMBER NOT NULL --<< Only 4 different values >> );
As you can see, U_VALUE is ALWAYS lower than O_VALUE.I need to have the CREATE_TS in either main- or subpartition do drop the partitions after some time so i don,t have to use DELETE statements. The table has 360 millions rows.
The application has only 8 query which will always use a WHERE clause like this:
SELECT * FROM TEST WHERE U_VALUE <= :1 AND O_VALUE => :2 AND ITEM_TYPE = :3
1. Is there any good technique how to create a good index for the queries (application will execute 120 queries per second)?
Index with following PARTITIONS. Index rebuild is extremely slow. Below 2 Alter index ..rebuild... took 10 hours to complete. Because of this queries which based on this index are really slow.
SYS@DB AS SYSDBA> select partition_name,tablespace_name,bytes/1024 KB from dba_segments where segment_name='KSTN'; PARTITION_NAME TABLESPACE_NAME KB ------------------------------ ------------------------------ ---------- REB_IDX_1 TS_REB 64 REB_IDX_2 TS_REB 64 REB_IDX_3 INDEX 64 REB_IDX_4 TS_REB 64
Is there any rule in documentation, when create partition index? For tables, it is said to partition when table is greater than 2GB, but what about index? WHat size it should have to partition?
ALTER TABLE SNYT.PART_ESTD SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22') INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22) update indexes;
(per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1401247200346349807) ================================================================= If Global index used
ALTER TABLE SNYT.PART_ESTD SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22') INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22) UPDATE GLOBAL INDEXES;
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0 14:33:05 06/26/12 begin logging 14:33:05 06/26/12 event 14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I 14:33:16 06/26/12 14:33:16 06/26/12 [code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read
I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.
The DDL of the partition table/indexes: ========================= CREATE TABLE TESTME ( INST_NO CHAR(3) NOT NULL, ACCT_NO CHAR(16) NOT NULL, REC_NO CHAR(9) NOT NULL, TRAN_TYPE CHAR(2) DEFAULT ' ', STAT CHAR(2) DEFAULT ' ', [code]...
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530; Table created. SQL> create index i_test on test(id); Index created. SQL> SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
I'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.
After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.
--Monitor an index to see if it's used alter index SAMPLE_INDEX monitoring usage; select * from v$object_usage where index_name = 'SAMPLE_INDEX'; alter index SAMPLE_INDEX nomonitoring usage;
