1 2 Previous Next 23 Replies Latest reply on Jun 17, 2016 8:01 AM by Jonathan Lewis

    function-based indexes

    Rosario Vigilante

      Hello to all

       

      I don't understand, where I am wrong,

      when I run

       

      select rowid, b.*
      
      from tableb b where
      K_PRESTA is null and
      rowid > (
      select min(rowid)
       FROM tableb a
      WHERE A.K_CODE = B.K_CODE
      and A.TIPORD = B.TIPORD
      and A.ANNO = B.ANNO
      and a.DATA_P = trunc(b.DATA_P)
      and A.K_PRESTA = B.K_PRESTA);
      

       

      I get no record

      then


      CREATE UNIQUE INDEX IU_PRESENZAPAZ ON tableb (
          CASE WHEN K_PRESTA is NULL THEN "K_CODE" else  null END,
          CASE WHEN K_PRESTA is NULL THEN "TIPORD" else  null END,
          CASE WHEN K_PRESTA is NULL THEN "ANNO" else  null END,
          CASE WHEN K_PRESTA is NULL THEN "DATA_P"  else  null END,
          CASE WHEN K_PRESTA is NULL THEN "KIND_P"  else  null END,
          CASE WHEN K_PRESTA is NULL THEN "K_PRESTA" else  null
          END)
      TABLESPACE tbs1;
      

       

      I get

       

      ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

       

       

      Sure I lost anything

       

      Thanks in advance

       

      (DB 11gR2)

        • 1. Re: function-based indexes
          Solomon Yakobson

          Since you are creating unique index column

          "K_CODE"

          "TIPORD"

          "ANNO"

          "DATA_P" 

          "KIND_P" 

          "K_PRESTA"

           

          value combinations must be unique for all rows where K_PRESTA is NULL.

           

          SY.

          1 person found this helpful
          • 2. Re: function-based indexes
            KayK

            Hi Rosario,

            your index looks a little strange for me.

            I expect you have 2 or more rows in your table where the column K_PRESTA is not null and then all columns in your index will be null and that is unique.

            Try it without the keyword unique.

            regards
            Kay

            1 person found this helpful
            • 3. Re: function-based indexes
              John Stegeman

              I guess you have multiple rows with K_PRESTA having NULL.

               

              Your query won't find them, because NULL is never equal to NULL

              1 person found this helpful
              • 4. Re: function-based indexes
                Solomon Yakobson

                Also,

                 

                CASE WHEN K_PRESTA is NULL THEN "K_PRESTA" else  null  END

                 

                doesn't make much no sense since it always produces NULL.

                 

                SY.

                1 person found this helpful
                • 5. Re: function-based indexes
                  Rosario Vigilante

                  Thank you

                   

                   

                  My goal is Just this

                   

                  I do not want to have duplicates in the columns indicated above.

                  I'll do this by trigger: before Insert trigger


                  Thanks

                   

                  • 6. Re: function-based indexes
                    Chris Hunt

                    Like KayK, my guess is that you have two or more rows where K_PRESTA IS NOT NULL, but why guess?

                    SELECT CASE WHEN K_PRESTA is NULL THEN "K_CODE" else  null END,

                           CASE WHEN K_PRESTA is NULL THEN "TIPORD" else  null END,

                           CASE WHEN K_PRESTA is NULL THEN "ANNO" else  null END,

                           CASE WHEN K_PRESTA is NULL THEN "DATA_P"  else  null END,

                           CASE WHEN K_PRESTA is NULL THEN "KIND_P"  else  null END,

                           CASE WHEN K_PRESTA is NULL THEN "K_PRESTA" else  null END,

                           COUNT(*)

                    FROM  tableb

                    GROUP BY CASE WHEN K_PRESTA is NULL THEN "K_CODE" else  null END,

                             CASE WHEN K_PRESTA is NULL THEN "TIPORD" else  null END,

                             CASE WHEN K_PRESTA is NULL THEN "ANNO" else  null END,

                             CASE WHEN K_PRESTA is NULL THEN "DATA_P"  else  null END,

                             CASE WHEN K_PRESTA is NULL THEN "KIND_P"  else  null END,

                             CASE WHEN K_PRESTA is NULL THEN "K_PRESTA" else  null END

                    HAVING COUNT(*) > 1

                    /

                    1 person found this helpful
                    • 7. Re: function-based indexes
                      Rosario Vigilante

                      Thanks

                       

                      It's so

                      SELECT CASE WHEN K_PRESTA is NULL THEN "K_CODE" else  null END a1,
                             CASE WHEN K_PRESTA is NULL THEN "TIPORD" else  null END a2,
                             CASE WHEN K_PRESTA is NULL THEN "ANNO" else  null END a3,
                             CASE WHEN K_PRESTA is NULL THEN "DATA_P"  else  null END a4,
                             CASE WHEN K_PRESTA is NULL THEN "KIND_P"  else  null END a5,
                             CASE WHEN K_PRESTA is NULL THEN "K_PRESTA" else  null END a6,
                             COUNT(*)  ntot
                      
                      FROM  RI_PRESENZE_G
                      
                      GROUP BY CASE WHEN K_PRESTA is NULL THEN "K_CODE" else  null END,
                               CASE WHEN K_PRESTA is NULL THEN "TIPORD" else  null END,
                               CASE WHEN K_PRESTA is NULL THEN "ANNO" else  null END,
                               CASE WHEN K_PRESTA is NULL THEN "DATA_P"  else  null END,
                               CASE WHEN K_PRESTA is NULL THEN "KIND_P"  else  null END,
                               CASE WHEN K_PRESTA is NULL THEN "K_PRESTA" else  null END
                      HAVING COUNT(*) > 1
                      order by 3
                      
                      
                      1231    2    2011    09/09/2011 00:00:00    P        2
                      1231    2    2011    10/09/2011 00:00:00    P        2
                      1885    1    2013    17/07/2013 10:00:00    P        2
                      12      1    2015    18/01/2015 00:00:00    P        5
                      801     1    2015    04/04/2015 00:00:00    P        3
                      1276    1    2015    09/06/2015 00:00:00    P        2
                                                                                   1499392
                      

                       

                      Thank you to all; Solomon, Kay, John and Chris

                      • 8. Re: function-based indexes
                        John Stegeman
                        I'll do this by trigger: before Insert trigger

                        Oh, no you won't. That won't work

                        1 person found this helpful
                        • 9. Re: function-based indexes
                          Solomon Yakobson

                          Rosario Vigilante wrote:

                           

                          TI do not want to have duplicates in the columns indicated above.

                          I'll do this by trigger: before Insert trigger

                          This will not work, as John already noted. Question is do you want to keep existing duplicates and prevent future duplicates or you don't want duplicates at all. For latter, decide which of duplicate rows to keep, delete the rest of the duplicates and create your unique index. For former you'll have to create calculated columns for each expression in your index then create non-unique index on calculated columns and unique key with NOVALIDATE using that index.
                          SY.
                          1 person found this helpful
                          • 10. Re: function-based indexes
                            Chris Hunt

                            So, it looks like you already have 16 rows where the indexed columns - K_CODE etc. - are already duplicated. And you have one and a half million rows where K_PRESTA is non-null.

                             

                            It's not clear what you mean by "I do not want to have duplicates in the columns indicated above". Do you want every value of, say, K_CODE to be unique across the whole table, or want every combination of values in K_CODE,TIPORD,ANNO,DATA_P,KIND_P,K_PRESTA to be unique, or something else.


                            If you can clearly define what should be unique within the table, run a query to find any rows which break your uniqueness rule, and fix your data so that all rules are met; you should be able to build a unique index to enforce that rule in the future. Taking a guess at your requirement, it might be that you want all rows to either (a) have a value in K_PRESTA or (b) have a unique combination of values in K_CODE,TIPORD,ANNO,DATA_P and KIND_P. In that case you could (I think) do this:


                            CREATE UNIQUE INDEX ON tableb

                            (K_CODE,TIPORD,ANNO,DATA_P,KIND_P,

                            CASE WHEN K_PRESTA IS NOT NULL THEN rowid END)


                            Obviously, you've got to fix your existing 16 duplicate rows before you try this.

                            1 person found this helpful
                            • 11. Re: Re: function-based indexes
                              Rosario Vigilante
                              
                              

                              Thanks again for your help and time

                               

                              Yes,


                              >>> unique combination of values in K_CODE,TIPORD,ANNO,DATA_P and KIND_P this is that I want, only when column K_presta is null.


                              I thought I could get this with unique index function-based indexes



                              Now I created this procedure:  before insert trigger


                              CREATE or replace TRIGGER check_presenzeday  before insert ON RI_PRESENZE_G
                                  referencing NEW as NEW  OLD AS OLD
                              FOR EACH ROW
                              declare
                              esiste number(3) := 0;
                              BEGIN
                                  select count(*) into esiste from  RI_PRESENZE_G A
                                      WHERE A.K_CODE = :NEW.K_CODE
                                      and A.TIPORD = :NEW.TIPORD
                                      and A.ANNO = :NEW.ANNO
                                      and trunc(A.DATA_P)  = trunc(:NEW.DATA_P)
                                      and A.KIND_P  = :NEW.KIND_P
                                      and A.K_PRESTA is null
                                      and A.tipord = '2';
                                     
                                      if esiste > 0 then
                                              raise_application_error(-20001, 'duplicate');
                                              ROLLBACK;
                                      else
                                        select count(*) into esiste from  RI_ANAGRAFICA A
                                        WHERE A.K_CODE = :NEW.K_CODE
                                        and A.TIPORD = :NEW.TIPORD
                                        and A.ANNO = :NEW.ANNO
                                        and data_d is not null and  data_d < trunc(:NEW.DATA_P);
                                          if esiste > 0 then
                                                raise_application_error(-20001, 'data dimissione precedente');
                                                ROLLBACK;
                                          END IF;
                                      END IF;
                              
                              EXCEPTION
                                WHEN OTHERS THEN
                                      raise_application_error(-20001, SQLERRM);
                                      ROLLBACK;
                              END;
                              
                              
                              

                               

                               

                              and yes, I know this is not the best solution, but


                              Thanks

                              • 12. Re: function-based indexes
                                rp0428

                                I hope someone, anyone, will tell me I'm not losing my mind.

                                 

                                Because I don't see how that 'correct' answer or ANY of the replies can possibly solve OPs real problem.

                                 

                                I don't understand, where I am wrong,

                                when I run

                                 

                                1. select rowid, b.* 
                                2.  
                                3. from tableb b where 
                                4. K_PRESTA is null and 
                                5. rowid > ( 
                                6. select min(rowid) 
                                7. FROM tableb a 
                                8. WHERE A.K_CODE = B.K_CODE 
                                9. and A.TIPORD = B.TIPORD 
                                10. and A.ANNO = B.ANNO 
                                11. and a.DATA_P = trunc(b.DATA_P) 
                                12. and A.K_PRESTA = B.K_PRESTA); 

                                 

                                I get no record

                                You will NEVER get meaningful rows with a query that compares ROWID in one table (table b) with ROWID in another table (table a).

                                 

                                Except for the special case of a clustered table ROWID values in tables do NOT overlap and are unique for each table.

                                 

                                So your query is fundamentally flawed before you even begin trying to create an index.

                                • 13. Re: function-based indexes
                                  Andrew Sayer

                                  You're losing your mind. Both tables are  tableb

                                   

                                  Op, that is an absolutely classic example of an abuse of triggers. What happens when a duplicate is inserted in another session but not committed yet?

                                  Use a unique constraint to police uniqueness .

                                  And what is up with that error handling? When others then raise application error ?

                                  • 14. Re: function-based indexes
                                    Jarkko Turpeinen

                                    rp0428 kirjoitti:

                                     

                                    I hope someone, anyone, will tell me I'm not losing my mind.

                                     

                                    Because I don't see how that 'correct' answer or ANY of the replies can possibly solve OPs real problem.

                                     

                                    well not losing your mind but presbyopia is suspect number one

                                    1 2 Previous Next