Создание индекса для функции максимального декодирования

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

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


sql oracle group-by database-indexes functional-index
person Real Chembil    schedule 14.10.2015    source источник
comment
Если вам нужны предложения по производительности, вы должны показать весь запрос.   -  person Gordon Linoff    schedule 14.10.2015
comment
Спасибо @GordonLinoff. Я включил запрос.   -  person Real Chembil    schedule 14.10.2015
comment
Я не вижу предиката фильтра в запросе. Вы проецируете все строки из таблицы. Очевидно, что требуется full table scan. Когда вы фильтруете данные на основе column, имеет смысл создать index для этого столбца. Если вы фильтруете данные на основе expression, вам нужно создать function-based index в этом столбце. Вам не нужен индекс для проецирования строк. Разместите ОБЪЯСНЕНИЕ ПЛАН. См. раздел как создать и отобразить ПЛАН EXPLAIN.   -  person Lalit Kumar B    schedule 14.10.2015
comment
Спасибо @LalitKumarB. Когда я проверяю план объяснения для приведенного ниже запроса, он показывает сканирование диапазона, поскольку индекс был создан для поля user_id в таблице user_details. ВЫБЕРИТЕ * ОТ VW_GET_USERINFO, ГДЕ USER_ID = 'XYZ'. Но когда мы используем FIRST_NAME в качестве фильтра, это приводит к полному сканированию, поскольку индекс не создается.   -  person Real Chembil    schedule 14.10.2015
comment
@RealChembil Опять же, вы создаете индекс для столбца статической таблицы или выражения, которое включает столбец статической таблицы.   -  person Lalit Kumar B    schedule 14.10.2015

Ответы (2)


arrow_upward
0
arrow_downward

"Можно ли как-нибудь создать индекс для атрибута FirstName?"

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

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

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

person APC    schedule 14.10.2015

arrow_upward
0
arrow_downward

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

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), чтобы увидеть, улучшит ли это производительность.

person Gordon Linoff    schedule 14.10.2015