Link to home
Start Free TrialLog in
Avatar of xoxomos
xoxomos

asked on

Partitioning Indexes Question

Trying to create this index partition


CREATE INDEX bb_bb60_stats.activity_accumulator_iecontent
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
CREATE INDEX bb_bb60_stats.activity_accumulator_iecontent
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL
 (
  PARTITION aa_2010_04  TABLESPACE BB_BB60_STATS_INDX,
  PARTITION aa_2010_05  TABLESPACE BB_BB60_STATS_INDX,
  PARTITION aa_2010_06  TABLESPACE BB_BB60_STATS_INDX,
  PARTITION aa_2010_07  TABLESPACE BB_BB60_STATS_INDX,



Getting the message
"Index created.

PARTITION  AA_2010_04         TABLESPACE BB_BB60_STATS_DATA
*
ERROR at line 5:
ORA-14010: this physical attribute may not be specified for an index partition
"
What attribute am I specifying that I should not?
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

I don't claim to be an expert on partitioning, but it looks like you are creating the index twice with the same name ?

Oracle in general doesn't like two things of the same type in the database with the same name.  

(Unless something about partitioning requires you to do it this way, but nothing I could find in the documentation suggested that)
Avatar of xoxomos
xoxomos

ASKER

Thanks
That's what I thought also but when i removed the first SQL command and just ran the second, I got some kind of error.  I'll run over that again now and come back with the error message.  Did a search on it in mySupport, came back with basically 'returned no rows' on the Knowledge thing :-)
Avatar of xoxomos

ASKER

I'll run this now:
DROP INDEX bb_bb60_stats.activity_accumulator_iecontent;
DROP INDEX bb_bb60_stats.activity_accumulator_ie1;
DROP INDEX bb_bb60_stats.activity_accumulator_ie2;
DROP INDEX bb_bb60_stats.activity_accumulator_ie3;
DROP INDEX bb_bb60_stats.activity_accumulator_ie4;
DROP INDEX bb_bb60_stats.activity_accumulator_ie5;
DROP INDEX bb_bb60_stats.activity_accumulator_ie6;
DROP INDEX bb_bb60_stats.activity_accumulator_pk1;
CREATE INDEX bb_bb60_stats.activity_accumulator_iecontent
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie1
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie1
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie2
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie2
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie3
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie3
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie4
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie4
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie5
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie5
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie6
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
CREATE INDEX bb_bb60_stats.activity_accumulator_ie6
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;
COMMIT;
 CREATE INDEX bb_bb60_stats.activity_accumulator_pk1
                                                              .......................................................................;
Avatar of xoxomos

ASKER

It's finished dropping whatever is there now starting the CREATE.  That will run about fifty minutes, make a significant amount of noise on OEM's 'Performance' screen, the error out.
Avatar of xoxomos

ASKER

ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL
                                        *
ERROR at line 2:
ORA-01408: such column list already indexed.
When I issue the drop, it says index does not exist
When i issue the CREATE it says index does exist.
Avatar of xoxomos

ASKER

SQL query show only one non system generated index.

SQL>  select owner, index_name from dba_indexes where table_owner = 'BB_BB60_STATS'
  2  and table_name = 'ACTIVITY_ACCUMULATOR';

OWNER                          INDEX_NAME
------------------------------ ------------------------------
BB_BB60_STATS                  ACTIVITY_ACCUMULATOR_IECONTENT
BB_BB60_STATS                  SYS_IL0000122326C00013$$

SQL>
SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xoxomos

ASKER

Yes
ACTIVITY_ACCUMULATOR_IECONTENT is the index on the primary key.
For the moment I would leave that be and try creating one or more of the other indexes.
Avatar of xoxomos

ASKER

SQL>  select index_name, column_name from dba_ind_columns
  2  where table_owner = 'BB_BB60_STATS'
  3  and table_name = 'ACTIVITY_ACCUMULATOR';

INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ACTIVITY_ACCUMULATOR_IECONTENT
CONTENT_PK1
Avatar of xoxomos

ASKER

SQL> select constraint_name from dba_constraints
  2  where table_name = 'ACTIVITY+ACCUMULATOR'
  3  and owner = 'BB_BB60_STATS';

no rows selected

SQL>
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xoxomos

ASKER

Thanks all.