Solved

Partitioning Indexes Question

Posted on 2012-12-22
13
1,402 Views
Last Modified: 2012-12-30
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?
0
Comment
Question by:xoxomos
  • 10
  • 2
13 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38717183
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)
0
 

Author Comment

by:xoxomos
ID: 38717211
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 :-)
0
 

Author Comment

by:xoxomos
ID: 38717219
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
                                                              .......................................................................;
0
 

Author Comment

by:xoxomos
ID: 38717233
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.
0
 

Author Comment

by:xoxomos
ID: 38717329
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.
0
 

Author Comment

by:xoxomos
ID: 38717354
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>
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 100 total points
ID: 38717372
It doesn't say that there is an index of that name, it says that those columns already exist in another index somewhere.

Does that table have a primary key ?  If you add a primary key constraint it automatically builds an index.

Check the values in dba_ind_columns for that table as well as dba_constraints.  Do you have a Primary Key (P) constraint using those columns ?
0
 

Author Comment

by:xoxomos
ID: 38717385
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.
0
 

Author Comment

by:xoxomos
ID: 38717396
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
0
 

Author Comment

by:xoxomos
ID: 38717784
SQL> select constraint_name from dba_constraints
  2  where table_name = 'ACTIVITY+ACCUMULATOR'
  3  and owner = 'BB_BB60_STATS';

no rows selected

SQL>
0
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 400 total points
ID: 38720215
All above can be explained in the following:

1) PK constraint is a rule that prohibits multiple rows from having the same value in the same column or combination of columns.

2) Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key.

3) Thus, when your table ACTIVITY_ACCUMULATOR was created with primary key as CONTENT_PK1, a unique index is created by Oracle with same name as primary key constraint name

4) Your very first statement in your very first post:
CREATE INDEX bb_bb60_stats.activity_accumulator_iecontent
 ON bb_bb60_stats.activity_accumulator (content_pk1) LOCAL;

This runs through since this is a duplicate index unlike the unique index created by Oracle to enforce uniqueness. Index with (same name in the same schema) OR (same type on the same set of coulmn(s) with matching sort specifiers i.e ASC or DESC) are not allowed.

Now you are trying to create an index partition but the top main part i.e. CREATE INDEX is exactly same as above. The error you are getting is not about index already exists but syntax error in creating index partition described as below:

ORA-14010:
this physical attribute may not be specified for an index partition
Cause:       unexpected option was encountered while parsing physical attributes of an index partition; valid options for Range or Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE; only TABLESPACE may be specified for Hash partitions
Action:       remove invalid option(s) from the list of physical attributes of an index partition

Please go through the following link on how to create table partitions and extending on that as how to create index partitions:

Partitioned Tables and Indexes
http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm
0
 

Assisted Solution

by:xoxomos
xoxomos earned 0 total points
ID: 38720501
Thanks krishna, looks like you are right here.  I've started creating indexes script again.
0
 

Author Closing Comment

by:xoxomos
ID: 38730573
Thanks all.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
Value of 0's not appearing. 9 50
consolidate 4 lines of oracle query output to 1 line 4 44
dates - loop 12 41
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now