Is there a PL/SQL equivalent to filtered indexes?

Dibs_501

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?

Paulzip

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)

)

/

Tubby

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,

Paulzip

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

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

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

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)

)

/

Tubby

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,

Unknown

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

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

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

Frank Kulash

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.