Compound index w/ keys of differing datatype
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Compound index w/ keys of differing datatype

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    Compound index w/ keys of differing datatype

    What is the good/bad/ugly of a compound index having a varchar2() key and a number() key?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    good = it works
    bad = dunno, maybe limitation on how big your index key can be depending on your block size...?
    ugly = some people (not myself) argue that you should have sequence generated number primary keys?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    The table has an indexed primary key. In this case, records are identified frequently based on these two columns (criteria), hence the compound index.

    I'm curious if combining the differing datatypes in one index key has some inherent issues.

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by KenEwald
    I'm curious if combining the differing datatypes in one index key has some inherent issues.
    assuming proper indexing strategy is employed, no, there is no problem.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thanks Jeff.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    There is a limit to the number of concatenated columns set at 32.
    The oracle doc also indicates a limit of 75% of the block size for the indexed column. This begs the question, is that the same for a concatenated index? Or is that per column of a concatenated index.

    I've used concatenated indices many times, with differing data types with good success.

  7. #7
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by marist89
    ugly = some people (not myself) argue that you should have sequence generated number primary keys?
    It depends. Worked on a system that had about 10 layer deep tree. So the PK columns (concatenated) had 11 columns at the bottom. This made it a real pain to re work the structures when the top level table had an attribute change for the PK column. UGLY... I much preferred the PK, using non inteligent keys.(ie numeric sequences)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width