1 2 Previous Next 23 Replies Latest reply on Jun 17, 2016 8:01 AM by Jonathan Lewis Go to original post
      • 15. Re: function-based indexes
        Jonathan Lewis

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

         

        You can do this with a unique function-based index (or unique index/constraint on set of virtual columns, which is nearly the same thing in 11g), but I think the error that's causing you confusion is why you couldn't create the unique index when you thought you'd executed a query to show that the data was clean.

         

        First problem - one of your predicates doesn't compare the column with itself, you've got: data_p = trunc(data_p), so if you had two rows with identical values but the data_p value was not "date-only" (e.g. your row "1885    1    2013    17/07/2013 10:00:00    P        2  " from the output a couple of posts back) your query wouldn't find it.

         

        Second (generic) problem: the predicate "null = null" returns neither TRUE nor FALSE, but NULL - so your subquery isn't going to handle rows with null the way you want.

         

        Third problem: (specific implementation of 2nd problem):  your subquery is comparing k_presta with itself, and your driving query is identifying exactly those rows where k_presta is null.

         

         

        Your index solution is valid (though you don't really need the k_presta on the end) - you just need to get rid of the duplicates which you can identify (unless I've made a mistake) with a slight modification to your query:

         

        select rowid, b.*

        from tableb b where

            K_PRESTA is null and

            rowid > (

                    select min(rowid)

                     FROM tableb a

                    WHERE sys_op_map_nonnull(A.K_CODE) = sys_op_map_nonnull(B.K_CODE)

                    and sys_op_map_nonnull(A.TIPORD) = sys_op_map_nonnull(B.TIPORD)

                    and sys_op_map_nonnull(A.ANNO) = sys_op_map_nonnull(B.ANNO)

                    and sys_op_map_nonnull(a.DATA_P) = sys_op_map_nonnull(b.DATA_P)

                    and a.k_presta is null

            );

         

        Regards

        Jonathan Lewis

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

          Thank you Jonathan

           

           

           

          1) data_p = trunc(data_p) It's a typo , my error to cut/paste my query

           

           

          2) You write: the duplicates which you can identify (unless I've made a mistake) with a slight modification to your query:

           

          No error No mistake It's work fine!!

           

           

          Thanks

          • 17. Re: function-based indexes
            Randolf Geist

            Jonathan Lewis wrote:

             

            select rowid, b.*

            from tableb b where

                K_PRESTA is null and

                rowid > (

                        select min(rowid)

                         FROM tableb a

                        WHERE sys_op_map_nonnull(A.K_CODE) = sys_op_map_nonnull(B.K_CODE)

                        and sys_op_map_nonnull(A.TIPORD) = sys_op_map_nonnull(B.TIPORD)

                        and sys_op_map_nonnull(A.ANNO) = sys_op_map_nonnull(B.ANNO)

                        and sys_op_map_nonnull(a.DATA_P) = sys_op_map_nonnull(b.DATA_P)

                        and a.k_presta is null

                );

             

             

            Jonathan,

             

            just a side note: Although SYS_OP_MAP_NONNULL appeared in some part of the official 12c documentation (in some part covering Materialized Views) I think it is still undocumented as it doesn't appear in the official SQL manual.

             

            I therefore prefer to write such expressions using DECODE, which would read like this in this particular case here:

             

            select rowid, b.*

            from tableb b where

                K_PRESTA is null and

                rowid > (

                        select min(rowid)

                         FROM tableb a

                        WHERE decode(A.K_CODE, B.K_CODE, 0, 1)  = 0

                        and decode(A.TIPORD, B.TIPORD, 0, 1)  = 0

                        and decode(A.ANNO, B.ANNO, 0, 1)  = 0

                        and decode(a.DATA_P, b.DATA_P, 0, 1) = 0

                        and a.k_presta is null

                );

             

            DECODE handles the NULL case gracefully, in particular when it appears on both sides - and as a bonus, above DECODE expressions get re-written internally by the optimizer to SYS_OP_MAP_NONNULL, at least in those cases I checked the predicate section of a corresponding execution plan (it doesn't for slight variations, e.g. compare to 1 to find inequality and it doesn't rewrite etc.)

             

            Randolf

            • 18. Re: function-based indexes
              Jonathan Lewis

              Randolf,

               

              For production code I would also advise against the use of sys_op_map_nonnull(), but for a one-off check to clean the data before enforcing a constraint I wouldn't be so strict.

              That's a brilliant transformation you've discovered, though - how did you find it ?

               

              Regards

              Jonathan Lewis

              • 19. Re: function-based indexes
                Randolf Geist

                Jonathan Lewis wrote:

                 

                That's a brilliant transformation you've discovered, though - how did you find it ?

                 

                Jonathan,

                 

                I think original credits go to a discussion between Stew Ashton and Tony Hasler where Tony mentioned in a comment that the DECODE expression gets re-written to SYS_OP_MAP_NONNULL automatically, but I might remember that incorrectly, so not entirely sure. Prompted by that comment I went on to do some testing on my own. Now that you mention it, could be worth a blog note - maybe you want to publish some short note?

                 

                Randolf

                • 20. Re: function-based indexes

                  Thanks. I knew I had to be missing something but couldn't see what it was.

                  • 21. Re: function-based indexes
                    Jonathan Lewis

                    Randolf,

                     

                    Thanks,

                     

                    I've already got a draft (one of 477) that had the link to the manuals where sys_op_map_nonnull is referenced (and the link to Sayan Malakshinov's article where I first found the manual reference: Oracle SQL | SYS_OP_MAP_NONNULL is in the documentation now ); and I've just added a link back to your comment here, and a link to the blog article by Stew Ashton where he introduces that very clever little decode() as a way of comparing columns with nulls: https://stewashton.wordpress.com/2015/01/05/merge-magic-and-madness/  but I don't know how long it will take to turn the scraps into a note I can publish.

                     

                    Regards

                    Jonathan Lewis

                    • 22. Re: function-based indexes
                      and a link to the blog article by Stew Ashton where he introduces that very clever little decode() as a way of comparing columns with nulls

                      That 'clever little decode' is also in an AskTom blob from 2003 so it has been around quite a while.

                      https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1274000564279

                      • 23. Re: function-based indexes
                        Jonathan Lewis

                        I just have to smile when I learn something "new" that has been in the manuals for the last couple of decades.

                        Often,. as in this case, it's the implication of what's been stated that goes much further than the bare statement of the fact.

                         

                        Regards

                        Jonathan Lewis

                        1 2 Previous Next