Creating Index on Max Decode function

Question

We have a table which stores user information in attribute name and value pairs. On this table, we have created a view by transposing the rows to columns using decode. MAX(DECODE(attribute_name,'FirstName',attribute_Value)) FirstName

CREATE OR REPLACE FORCE VIEW vw_get_userinfo
("USER_ID", "FIRSTNAME", "LASTNAME", "USEREMAIL",
  "STREET", "CITY", "STATE", "ZIPCODE", "COUNTRY")
AS
  SELECT
    t.user_id,
    t.firstname,
    t.lastname,
    t.useremail,
    t.street,
    t.city,
    t.state,
    t.country
  FROM (WITH
          tempattributes AS (SELECT
                               user_id,
                               attribute_name,
                               attribute_value
                             FROM user_details)
        SELECT
          user_id,
          MAX(DECODE(attribute_name, 'FirstName', attribute_value)) FirstName,
          MAX(DECODE(attribute_name, 'LastName', attribute_value))  LastName,
          MAX(DECODE(attribute_name, 'UserEmail', attribute_value)) UserEmail,
          MAX(DECODE(attribute_name, 'Street', attribute_value))    Street,
          MAX(DECODE(attribute_name, 'City', attribute_value))      City,
          MAX(DECODE(attribute_name, 'State', attribute_value))     State,
          MAX(DECODE(attribute_name, 'ZipCode', attribute_value))   Zipcode,
          MAX(DECODE(attribute_name, 'Country', attribute_value))   Country
        FROM tempattributes
        GROUP BY user_id
       ) t

During explain plan, a full scan is performed when querying with the first name field. The function-based index is not applicable in this case as Index won't work on the group by function. Is there any way I can create the index on the FirstName attribute?

Any help would be appreciated.

Answer 1

"Is there any way I can create the index on the FirstName attribute?"

No. Indexes are built on table columns. The table underlying your view has a generic structure of (key, value) pairs. This approach seems flexible and time-saving for application developers but the price is paid by the user in poor performance.

And of course you still end up defining a fixed set of attributes, only the structure is expressed in a view rather than a table. So what flexibility have you gained really?

If you don't want to define a fixed set of attributes, for whatever reason, you should choose a supported approach such as XML, or JSON in 12c. At least their attributes are indexable. Find out more.

Answer 2

This may not help performance, but you can just write the query as:

SELECT user_id,
       MAX(DECODE(attribute_name,'FirstName',attribute_Value)) as FirstName,
       MAX(DECODE(attribute_name,'LastName',attribute_Value)) as LastName,
       MAX(DECODE(attribute_name,'UserEmail',attribute_Value)) as UserEmail,
       MAX(DECODE(attribute_name,'Street',attribute_Value)) as Street,
       MAX(DECODE(attribute_name,'City',attribute_Value)) as City,
       MAX(DECODE(attribute_name,'State',attribute_Value)) as State,
       MAX(DECODE(attribute_name,'ZipCode',attribute_Value)) as ZipCode,
       MAX(DECODE(attribute_name,'Country',attribute_Value)) as Country
FROM user_details
GROUP BY user_id;

(Note: I much prefer CASE to DECODE(), but I'm leaving your original logic in.)

Oracle has a good optimizer, but it is possible that the nested with affected it, so this might perform better. You can also try an index on user_details(user_id, attribute_name, attribute_value) to see if that improves performance.