12 Replies Latest reply: May 8, 2012 1:24 AM by User13512691-Oracle RSS

    Question on primary key setup

    User13512691-Oracle
      Hi,

      I have doubt about primary key set up in my database. We have table like following description:
      Forexample:

      ACCT_ID Varchar2(20)
      Customer_state Number
      FLAD_ID Char

      accid_id=690904949 (distinct values)
      customer_info=999 (distinct values)
      flag_od=1 (distinct values)

      Total numbers of rows are 700993387

      Now the question is: This primary key(containing all three columns above) have primary key index created on this. I dont feel having composite index in which third column is having only one popular value(low cardinality) and also customer_info (which is also low cardinality) column.

      can i drop the index on this primary key and create index only on acct_id and customer_info individually?

      Im also thinking of creating extended statistics on acct_id and customer_info columns, is it a good idea?

      Now i have similar sort of description in other table like:

      acct_id = 690904949 (distinct values)
      customer_id = 690904949 (distinct values)
      flag_od = 1 (distinct values)

      Again similar sort of questions:

      1) Is it good to have primary key index on these three columns?
      2) Should i generate extended statistics on acct_id and customer_id, since for each value of acct_id there is only one value of customer_id? ( can i create extended statistics on these which are already part of composite primary key index?
      3) Should i drop primary key index and create individual index? And is it really required to have index on flag_od because there is only 1 distinct value.

      Answers are appreciated
        • 1. Re: Question on primary key setup
          Tubby
          user13512691 wrote:
          can i drop the index on this primary key and create index only on acct_id and customer_info individually?
          I wouldn't think so. Just because there is only 1 distinct value in the table now doesn't mean there won't be an additional value added in the future. Only you and your application team can answer that question though (it sounds to me as though you're asking if you can drop the primary key ... if not then disregard this please).
          user13512691 wrote:
          Forexample:
          accid_id=690904949 (distinct values)
          customer_info=999 (distinct values)
          flag_od=1 (distinct values)
          Something i would investigate (read : benchmarking) though would be creating the index in the following order
          flag_od, customer_info, accid_id

          and compressing the first 2 columns in the index.
          http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT1177

          As for the extended statistics ... they may help, they may not. Is there a performance problem you're trying to solve with this, or are you just attempting to be proactive here?
          • 2. Re: Question on primary key setup
            User13512691-Oracle
            Tubby wrote:
            user13512691 wrote:
            can i drop the index on this primary key and create index only on acct_id and customer_info individually?
            I wouldn't think so. Just because there is only 1 distinct value in the table now doesn't mean there won't be an additional value added in the future. Only you and your application team can answer that question though (it sounds to me as though you're asking if you can drop the primary key ... if not then disregard this please).
            I will disable the primary key constaint -> Drop primary key index -> create individual indexes -> Enable primary key constaint without index
            Something i would investigate (read : benchmarking) though would be creating the index in the following order
            flag_od, customer_info, accid_id
            Is there any specific reason you'll choose this order?
            >
            and compressing the first 2 columns in the index.
            http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT1177
            Its a good catch here because my Index size is double then Table size. Thats keep me wondering why index size is double then table?

            >
            As for the extended statistics ... they may help, they may not. Is there a performance problem you're trying to solve with this, or are you just attempting to be proactive here?
            application team is fetching this table and update a different table. So for updating that table its taking more time then usual. So yes you can say its a proactive and ractive too.
            • 3. Re: Question on primary key setup
              Tubby
              user13512691 wrote:
              Tubby wrote:
              >
              Something i would investigate (read : benchmarking) though would be creating the index in the following order
              flag_od, customer_info, accid_id
              Is there any specific reason you'll choose this order?
              Yes, because the more repetitive the value, the better compression you will achieve. I'm assuming your current primary key is defined opposite of what i suggested, hence the larger size.
              user13512691 wrote:
              application team is fetching this table and update a different table. So for updating that table its taking more time then usual. So yes you can say its a proactive and ractive too.
              You're probably better off doing some serious investigation in to the process and figuring out WHY it's taking more time. From where i'm sitting, it sounds to me like you're on a guessing trip (forgive me if that's not the case). If you can nail down the exact cause of the recent slow down, you can take action from there. Perhaps it's "taking more time than usual" because there's an unusually large batch to process to coincide with a fiscal year, or some such thing.
              • 4. Re: Question on primary key setup
                AliD
                Are the columns defined as NOT NULL or is NULL value allowed? If your business says this combo should be the primary key, then you have no choice. Otherwise it is a poor choice and you better off changing it.

                The column with most distinct value, which I suppose can be a primary key by itself, is ACCT_ID. Is there a reason it is not defined as NUMBER?

                2) Collect normal statistics first. I doubt extended stats will help here.
                3) Again, you know your business and if FLAG_OD should be part of the index. Though, it does seem weird to have it there.
                • 5. Re: Question on primary key setup
                  User13512691-Oracle
                  Tubby wrote:
                  user13512691 wrote:
                  Tubby wrote:
                  >
                  Something i would investigate (read : benchmarking) though would be creating the index in the following order
                  flag_od, customer_info, accid_id
                  Is there any specific reason you'll choose this order?
                  Yes, because the more repetitive the value, the better compression you will achieve. I'm assuming your current primary key is defined opposite of what i suggested, hence the larger size.
                  I haven't seen your document yet, so i'm assuming having change in order of index , size would reduce and it will reduce further on compressing. rt?
                  >
                  user13512691 wrote:
                  application team is fetching this table and update a different table. So for updating that table its taking more time then usual. So yes you can say its a proactive and ractive too.
                  You're probably better off doing some serious investigation in to the process and figuring out WHY it's taking more time. From where i'm sitting, it sounds to me like you're on a guessing trip (forgive me if that's not the case). If you can nail down the exact cause of the recent slow down, you can take action from there. Perhaps it's "taking more time than usual" because there's an unusually large batch to process to coincide with a fiscal year, or some such thing.
                  Actuall system has moved from old legacy system to new Oracle Exadata machine. It usually works fine in non oracle database
                  • 6. Re: Question on primary key setup
                    User13512691-Oracle
                    AliD wrote:
                    Are the columns defined as NOT NULL or is NULL value allowed? If your business says this combo should be the primary key, then you have no choice. Otherwise it is a poor choice and you better off changing it.
                    It have not NULL defined.
                    But why its a poor? :)
                    The column with most distinct value, which I suppose can be a primary key by itself, is ACCT_ID. Is there a reason it is not defined as NUMBER?
                    because acct_id will containt characters too.

                    >
                    2) Collect normal statistics first. I doubt extended stats will help here.
                    3) Again, you know your business and if FLAG_OD should be part of the index. Though, it does seem weird to have it there.
                    I'm into same situation where i believe its weird thats why i posted question here :)

                    Do you have any experience on this?
                    • 7. Re: Question on primary key setup
                      AliD
                      But why its a poor?
                      A composite index, including a VARCHAR and another column with only one value as primary key is likely to have poor performance. Also bear in mind a purpose of having PK is to use it as a reference in other tables as Foreign key. A composite index is not a good candidate for that.

                      One solution is to use a surrogate key which is a dummy column usually generated by a sequence. It is number and by definition unique. You can easily reference it as FK and have a unique index on the three previous column as business key to ensure your data is valid.
                      • 8. Re: Question on primary key setup
                        Jonathan Lewis
                        AliD wrote:
                        But why its a poor?
                        A composite index, including a VARCHAR and another column with only one value as primary key is likely to have poor performance.
                        Wrong.
                        Also bear in mind a purpose of having PK is to use it as a reference in other tables as Foreign key. A composite index is not a good candidate for that.
                        Wrong

                        >
                        One solution is to use a surrogate key which is a dummy column usually generated by a sequence. It is number and by definition unique. You can easily reference it as FK and have a unique index on the three previous column as business key to ensure your data is valid.
                        So you now have to have TWO indexes on the parent table instead of one - which will increase the undo and redo generation; and one of them is sequence-based - which might introduce extra contention on inserts; and you now have to join two tables to select on, or report, the proper primary key in some queries where you could once access a single table by its "foreign key" index; and if you thought you were going to end up partitioning on the primmary key of the parent you've just spoiled your options for partition-wise joins.

                        Regards
                        Jonathan Lewis

                        P.S. Don't worry about it too much, yours isn't the only bad advice on this page.
                        • 9. Re: Question on primary key setup
                          User13512691-Oracle
                          Hi Jonathan,

                          Thanks for reply.
                          What is was thinking is to drop the column (flag_od) which have only 1 distinct value out of million rows from composit primary key index. Would that be a good idea.? Applicaiton team have select on this table and updates the other one.
                          • 10. Re: Question on primary key setup
                            Jonathan Lewis
                            user13512691 wrote:

                            What is was thinking is to drop the column (flag_od) which have only 1 distinct value out of million rows from composit primary key index. Would that be a good idea.? Applicaiton team have select on this table and updates the other one.
                            No-one on this forum can tell you the right answer for your system. If the three column combination really is the primary key then you have to have a composite index on those three columns to allow Oracle to enforce the primary key. (Technically you could implement a system where you had made the index, but then Oracle would effectively make the table read-only because it wouldn't be able to enforce uniqueness.)

                            The fact that there is only one value recorded against one of the columns (at present) is logically irrelevant - apparently someone thinks that it's possible for a single customer to be in a single state but need two flag values.

                            Tubby's comment about changing the order of columns was accompanied by a reference to the documentation - I would guess that the documentation makes the point about better compressibility if you order the index columns most repetitive first, but I would also guess that it makes the point that there may be column orders that will make some of your queries run very inefficiently because the optimizer may be left choosing between tablescans or index fast full scans rather than using a high-precision range scan or unique scan.

                            I think you said the first index you mentioned is larger than the table. If the table is nothing more than these three columns that wouldn't be surprising - it would also suggest that you should consider the possibility of recreating the table as an index organized table. On the other hand if there is a lot more data to the table, then the nature of the code manipulating the data may fall into one of the standard patterns of usage that result in a large amount of unfilled space accumulating in the index. There are various scripts and articles on my blog discussing the causes and how to address them.

                            Regards
                            Jonathan Lewis
                            • 11. Re: Question on primary key setup
                              User13512691-Oracle
                              Jonathan Lewis wrote:
                              user13512691 wrote:

                              What is was thinking is to drop the column (flag_od) which have only 1 distinct value out of million rows from composit primary key index. Would that be a good idea.? Applicaiton team have select on this table and updates the other one.
                              No-one on this forum can tell you the right answer for your system. If the three column combination really is the primary key then you have to have a composite index on those three columns to allow Oracle to enforce the primary key. (Technically you could implement a system where you had made the index, but then Oracle would effectively make the table read-only because it wouldn't be able to enforce uniqueness.)

                              The fact that there is only one value recorded against one of the columns (at present) is logically irrelevant - apparently someone thinks that it's possible for a single customer to be in a single state but need two flag values.
                              Thanks Jonathan, i'll check the relevence of having this column in primary with application team.
                              Tubby's comment about changing the order of columns was accompanied by a reference to the documentation - I would guess that the documentation makes the point about better compressibility if you order the index columns most repetitive first, but I would also guess that it makes the point that there may be column orders that will make some of your queries run very inefficiently because the optimizer may be left choosing between tablescans or index fast full scans rather than using a high-precision range scan or unique scan.
                              Yes we are aware of that thanks.
                              I think you said the first index you mentioned is larger than the table. If the table is nothing more than these three columns that wouldn't be surprising - it would also suggest that you should consider the possibility of recreating the table as an index organized table. On the other hand if there is a lot more data to the table, then the nature of the code manipulating the data may fall into one of the standard patterns of usage that result in a large amount of unfilled space accumulating in the index. There are various scripts and articles on my blog discussing the causes and how to address them.
                              Yes table have 3 more columns and those columns allow NULLS. Ok i'll check your blog.
                              Regards
                              Jonathan Lewis