Forum Stats

  • 3,783,338 Users
  • 2,254,760 Discussions
  • 7,880,368 Comments

Discussions

Is there a PL/SQL equivalent to filtered indexes?

Dibs_501
Dibs_501 Member Posts: 6
edited Feb 29, 2016 12:39PM in SQL & PL/SQL

Greetings!

Last week I queries this community as I was looking for a way to set up an index on my table that performed the following: for any given value of aval_tplts_id, there are not 2 other rows (X and Y) such that X.aval_tplts_id_defined_by=Y.aval_tplts_alt_id_defined_by and
X.aval_tplts_alt_id_defined_by=Y.aval_tplts_id_defined_by.

I got several replies, all of them very helpful and was provided with the following piece of code:

CREATE UNIQUE INDEX dependency_unique
     ON template_dependencies ( aval_tplts_id
      , LEAST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)
      , GREATEST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)
      );


This works perfectly, except in cases where two rows of identical aval_tplts_id values also have a value of NULL for aval_tplts_alt_id_defined_by.


My initial thought was to insert a WHERE clause or similar somewhere in the index creation, though I don't see any way to do that directly in PL/SQL (I found some documentation for SQL Server involving this, though it doesn't appear to be a feature in Oracle).

Is there any way to create this index to ignore the value of aval_tplts_alt_id_defined_by when that value is NULL?

Tagged:

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,544 Blue Diamond
    edited Feb 29, 2016 11:37AM Accepted Answer

    No, that'll propagate the null

    try....

    CREATE UNIQUE INDEX dependency_unique

      ON template_dependencies (

        aval_tplts_id,

        coalesce(Least(aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by), aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by),

        coalesce(Greatest(aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by), aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)

    )

    /

    Dibs_501

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Feb 29, 2016 11:17AM
    Dibs_501 wrote:
    
    Greetings!
    
    Last week I queries this community as I was looking for a way to set up an index on my table that performed the following: for any given value of aval_tplts_id, there are not 2 other rows (X and Y) such that X.aval_tplts_id_defined_by=Y.aval_tplts_alt_id_defined_by and
    X.aval_tplts_alt_id_defined_by=Y.aval_tplts_id_defined_by. I got several replies, all of them very helpful and was provided with the following piece of code: CREATE UNIQUE INDEX dependency_unique
         ON template_dependencies ( aval_tplts_id
          , LEAST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)
          , GREATEST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)
          );

    This works perfectly, except in cases where two rows of identical aval_tplts_id values also have a value of NULL for aval_tplts_alt_id_defined_by.
    My initial thought was to insert a WHERE clause or similar somewhere in the index creation, though I don't see any way to do that directly in PL/SQL (I found some documentation for SQL Server involving this, though it doesn't appear to be a feature in Oracle). Is there any way to create this index to ignore the value of aval_tplts_alt_id_defined_by when that value is NULL?

    Sure, you can just add a CASE in both the LEAST and GREATEST portions.

    Case when aval_tplts_alt_id_defined_by is NULL then null else ... end

    Cheers,

    Dibs_501
  • Paulzip
    Paulzip Member Posts: 8,544 Blue Diamond
    edited Feb 29, 2016 11:34AM

    The trouble with least and greatest is is that if any parameter passed to is null, the result is null.  So you might be better doing null checks before.  Something like....

    CREATE UNIQUE INDEX dependency_unique

      ON template_dependencies (

          aval_tplts_id,

          case

            when aval_tplts_id_defined_by is null then aval_tplts_alt_id_defined_by

            when aval_tplts_alt_id_defined_by is null then aval_tplts_id_defined_by

            else LEAST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)

          end,

          case

            when aval_tplts_id_defined_by is null then aval_tplts_alt_id_defined_by

            when aval_tplts_alt_id_defined_by is null then aval_tplts_id_defined_by

            else GREATEST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)

          end

    )

    /


    - OR -


    CREATE UNIQUE INDEX dependency_unique

      ON template_dependencies (

        aval_tplts_id,

        coalesce(Least(aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by), aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by),

        coalesce(Greatest(aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by), aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)

    )

    /


    Dibs_501
  • Dibs_501
    Dibs_501 Member Posts: 6
    edited Feb 29, 2016 11:33AM

    Thank you.  I should note, the aval_tplts_id and aval_tplts_id_defined_by fields are mandatory; only the aval_tplts_alt_id_defined_by field is optional.  But your reply is still very helpful.

  • Dibs_501
    Dibs_501 Member Posts: 6
    edited Feb 29, 2016 11:33AM

    Great, so like this?

    CREATE UNIQUE INDEX dependency_unique

    ON template_dependencies (aval_tplts_id

                , LEAST (aval_tplts_id_defined_by, (CASE WHEN aval_tplts_alt_id_defined_by IS NULL THEN NULL ELSE aval_tplts_alt_id_defined_by END))

                , GREATEST (aval_tplts_id_defined_by, (CASE WHEN aval_tplts_alt_id_defined_by IS NULL THEN NULL ELSE aval_tplts_alt_id_defined_by END))

                );

  • Paulzip
    Paulzip Member Posts: 8,544 Blue Diamond
    edited Feb 29, 2016 11:37AM Accepted Answer

    No, that'll propagate the null

    try....

    CREATE UNIQUE INDEX dependency_unique

      ON template_dependencies (

        aval_tplts_id,

        coalesce(Least(aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by), aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by),

        coalesce(Greatest(aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by), aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)

    )

    /

    Dibs_501
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Feb 29, 2016 11:43AM
    Dibs_501 wrote:
    
    Great, so like this?
    
    CREATE UNIQUE INDEX dependency_unique
    ON template_dependencies (aval_tplts_id
                , LEAST (aval_tplts_id_defined_by, (CASE WHEN aval_tplts_alt_id_defined_by IS NULL THEN NULL ELSE aval_tplts_alt_id_defined_by END))
                , GREATEST (aval_tplts_id_defined_by, (CASE WHEN aval_tplts_alt_id_defined_by IS NULL THEN NULL ELSE aval_tplts_alt_id_defined_by END))
                );
    

    Almost,

    You'd want

    Case when aval_tplts_alt_id_defined_by is null then null else

    LEAST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)

    end


    And then another one for the GREATEST


    Case when aval_tplts_alt_id_defined_by is null then null else

    GREATEST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)

    end


    Cheers,

    Dibs_501
  • Unknown
    edited Feb 29, 2016 12:05PM
    This works perfectly, except in cases where two rows of identical aval_tplts_id values also have a value of NULL for aval_tplts_alt_id_defined_by.
    
    My initial thought was to insert a WHERE clause or similar somewhere in the index creation, though I don't see any way to do that directly in PL/SQL (I found some documentation for SQL Server involving this, though it doesn't appear to be a feature in Oracle).
    
    

    You say that functional index 'works perfectly'.

    Are users REALLY adding that complex predicate to their queries?

    Please post an example query and the actual execution plan showing that index is being used.

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Feb 29, 2016 12:10PM
    rp0428 wrote:
    
    This works perfectly, except in cases where two rows of identical aval_tplts_id values also have a value of NULL for aval_tplts_alt_id_defined_by.
    
    My initial thought was to insert a WHERE clause or similar somewhere in the index creation, though I don't see any way to do that directly in PL/SQL (I found some documentation for SQL Server involving this, though it doesn't appear to be a feature in Oracle).
    
    
    You say that functional index 'works perfectly'.
    
    Are users REALLY adding that complex predicate to their queries?
    
    Please post an example query and the actual execution plan showing that index is being used.
    

    You may have missed the original thread for this, but the intent of the index isn't for performance purposes, it's for preventing "duplicates" as defined by the OP.

    Cheers,

  • Dibs_501
    Dibs_501 Member Posts: 6
    edited Feb 29, 2016 12:15PM

    Like @Tubby stated, the intent is to prevent duplicate entries.  Here is the original thread.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,412 Red Diamond
    edited Feb 29, 2016 12:39PM

    Hi,

    Dibs_501 wrote:
    
    Greetings!
    
    Last week I queries this community as I was looking for a way to set up an index on my table that performed the following: for any given value of aval_tplts_id, there are not 2 other rows (X and Y) such that X.aval_tplts_id_defined_by=Y.aval_tplts_alt_id_defined_by and
    X.aval_tplts_alt_id_defined_by=Y.aval_tplts_id_defined_by. I got several replies, all of them very helpful and was provided with the following piece of code: CREATE UNIQUE INDEX dependency_unique
        ON template_dependencies ( aval_tplts_id
          , LEAST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)
          , GREATEST (aval_tplts_id_defined_by, aval_tplts_alt_id_defined_by)
          );

    This works perfectly, except in cases where two rows of identical aval_tplts_id values also have a value of NULL for aval_tplts_alt_id_defined_by. ...

    You said that the index doesn't do what you want when aval_tplts_alt_id is NULL, but what do you want in that case?

    Post a CREATE TABLE statement (relevant columns only) and a few INSERT statements, some of which should work, and some of which should fail because they violate the uniqueness requirement.  Point out which should work and which should fail.

    It might help if there is some value that you know will never occur in that column.  For example, if aval_tplts_alt_id is always a positive integer, then you might want to use

    NVL (aval_tplts_alt_id, -1)
    
    

    instead of just aval_tplts_alt_id as an argument to LEAST and GREATEST.

This discussion has been closed.