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?
Oracle Database

Avatar of undefined
Last Comment
xoxomos

8/22/2022 - Mon
Steve Wales

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)
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 :-)
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
                                                              .......................................................................;
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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.
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.
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>
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Steve Wales

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
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
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>
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
k_murli_krishna

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
xoxomos

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
xoxomos

ASKER
Thanks all.