1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00957: duplicate column name when trying >create index

Discussion in 'Server Administration and Options' started by Popeye.Tom, Aug 11, 2011.

  1. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    Hi all,

    I hope this is the correct location for this question!

    I have been instructed to execute the following command (by PTC, the manufacturer of the 3rd party tool sitting on top of Oracle), as the db User for their product:

    sql>create index EPMFAMILYTABLECELL$PTC2 on EPMFAMILYTABLECELL(idA3A3,idA2A2, classnameA2A2, idA3A3, classnamekeyB3, idA3B3, defi
    nedHere, inherited) compress 1;

    There are actually 4 total to be created, the structure of the command is very similar, two indexes on each of two tables. All four commands cause Oracle to gripe:
    ERROR at line 1:
    ORA-00957: duplicate column name



    I can identify that this db user is the owner of two tables corresponding to the names specified. Looking at just the one table, I also do know these details:

    Table Name Index Name Column Name
    EPMFAMILYTABLECELL EPMFAMILYTABLECELL$PTC1 idA3A3
    EPMFAMILYTABLECELL EPMFAMILYTABLECELL$COMPOSITE1 idA3B3
    EPMFAMILYTABLECELL PK_EPMFAMILYTABLECELL idA2A2

    That is the complete list of indexes for this table. The two indexes that I've been asked to create, EPMFAMILYTABLECELL$PTC2 and ~$PTC3 do not exist.

    What is wrong with the specified columns and what already exists for this table that causes the error:
    ERROR at line 1:
    ORA-00957: duplicate column name

    I see that some of the column names specified in the command already exit, but idA3B3 does not exist in this table.

    Looking at just this one >create index..., Which column specifications within this command would you advise be changed? (I'm not going to implement immediately, let their support engineer advice changes tomorrow, but I want to have a little more knowledge to judge their advice!)

    Thank you,
    Tom
     
  2. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    I cannot see straight...It was pointed out to me that the column idA3B3 does already exist for the index EPMFAIMLYTABLECELL$COMPOSITE1.

    It was also pointed out, in the OTN group that having columns named as idA2A2, idA3A3 and idA3B3 is not good, double bad is using idA2A2 as the primary key. Why would this be bad and what would be better?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    sql>create index EPMFAMILYTABLECELL$PTC2 on EPMFAMILYTABLECELL(idA3A3,idA2A2, classnameA2A2, idA3A3, classnamekeyB3, idA3B3, defi
    nedHere, inherited) compress 1;

    I hope this helps. The column is dupicated in the create index statement as you can see.
     
    Popeye.Tom likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Simply because some other index includes a column won't throw an error, especially the error you're seeing. If you attempt to index the same column list twice Oracle will complain that such an index already exists. Your issue, as shown in my prior response, is that someone included the column name twice in the same create index statement. Check the other statements for such occurrences.

    The naming 'convention' is not descriptive and can be prone to typographical errors. In my opinion column names should bear some indication of their purpose and/or data content; these names are indicative of, well, nothing except a propensity to obscure.
     
  5. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    Thank you Zargon!

    I understand completely - obfuscation for the sake of clarification, or something like that! I do find them causing me to stop and pause and make sure I have them correct when I type - and I'm not bad at keyboarding, but those give me fits! I certainly cannot change them, in this instance, as I do not know how their application is calling them, but I will keep that in mind if I'm ever doing something on my own.

    So, if I were to drop one of the idA3A3 out of that command's argument, would it be best to drop the first to have:

    >create index.....(idA2A2, classnameA2A2, idA3A3, classnamekeyB3, idA3B3, definedHere, inherited) compress 1;

    Or the second instance... Or, does it not matter?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It can matter as you're choosing between making idA3A3 the leading column of the index (meaning it's likely used by itself somewhere in some query where clause) and burying it somewhere in the column list making it only really useful if the first three columns are used in a where clause or if the data is non-selective enough to allow Oracle to use an index skip-scan. You need to get this clarified by the vendor before you make any changes.
     
  7. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    Answered! Re: ORA-00957: duplicate column name when trying >create index

    Thank you again, David.

    I didn't expect a reply from the application support engineer until tomorrow, he's US east coast time zone. But, he did just respond to my email and I think he got caught out by those wonderfully named columns! He has corrected all four create index command arguments. The new parenthetical for the one now reads:

    (idA3A3,idA2A2, classnameA2A2, classnamekeyB3, idA3B3, definedHere, inherited)

    Stay cool there in Reno - I'm a little cooler today, up in Medford, OR...86F :cool:

    Cheers,
    Tom
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Answered! Re: ORA-00957: duplicate column name when trying >create index

    As I suspected the idA3A3 column (which sounds like a secret submarine base in the Baltic sea) should be the leading column. Depending upon the selectivity of the data (as I said before) Oracle can use an index in a number of ways -- see here for an example of building an index and how it can behave given a variety of circumstances.