ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Index on derived column?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Index on derived column?

    Hi

    I have a table MYTABLE that contains column VRID

    VRID is a char(10) alphanumeric ID. All values should be all upper case, and with no spaces. But sometimes incorrect values have slipped in.

    Correct:
    AB123CD

    Incorrect - should have been AB123CD:
    ab123cd
    AB123 CD
    Ab123cd
    etc...

    In order to match on this column using a correct value, matching any of the possible incorrect values, I do this:
    Code:
    exec sql select * from MYTABLE
    where upper(replace(VRID,' ','')) = :somevalue;
    If I wanted to make this search efficient, am I right in thinking it would it be as simple as creating this index:
    Code:
    create index MYTABLEI1 on MYTABLE (upper(replace(VRID,' ','')));
    And the SQL processor will recognise that there is an index matching the exact scalar transform I am doing on column VRID in my select statement, and use the index?


  • #2
    P.S. correcting the incorrect values isn't currently practical for various reasons, for now I just care about efficient matching.

    Comment


    • #3
      My guess would be yes - the index would help.

      But why guess? What does the index advisor say?

      Comment


      • #4
        Vectorspace,

        It will only if you continue to use the same derived column evaluation in the where predicate.
        Code:
        Select * from MYTABLE where upper(replace(VRID,' ','')) = :somevalue
        Jim

        Comment

        Working...
        X