Создание индекса по функции Max Decode

Question

У нас есть таблица, в которой хранится информация о пользователях в парах имен и значений атрибутов. В этой таблице мы создали представление путем транспонирования строк в столбцы с помощью декодирования. 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

Во время плана объяснения выполняется полное сканирование при запросе поля имени. Индекс на основе функций в этом случае неприменим, так как индекс не будет работать с группой по функциям. Есть ли способ, которым я могу создать индекс на FirstName атрибут?

Любая помощь будет оценена.

Answer 1

Это может не помочь производительности, но вы можете просто написать запрос как:

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;

(Примечание: я предпочитаю CASE в DECODE(), но я оставляю вашу оригинальную логику.)

Oracle имеет хороший оптимизатор, но возможно, что вложенный with повлияло на это, так что это может работать лучше. Вы также можете попробовать индекс на user_details(user_id, attribute_name, attribute_value) чтобы увидеть, если это улучшает производительность.

Answer 2

"Есть ли способ создать индекс для атрибута FirstName?"

Индексы построены на столбцах таблицы. Таблица, лежащая в основе вашего представления, имеет общую структуру пар (ключ, значение). Этот подход кажется гибким и экономит время для разработчиков приложений, но пользователь платит цену за низкую производительность.

И, конечно же, вы по-прежнему определяете фиксированный набор атрибутов, только структура выражается в виде, а не в таблице. Так какую гибкость вы приобрели на самом деле?

Если по какой-либо причине вы не хотите определять фиксированный набор атрибутов, вам следует выбрать поддерживаемый подход, такой как XML или JSON в 12c. По крайней мере, их атрибуты индексируются. Узнать больше.