Как принудительно использовать индекс для столбцов в операторе слияния в Oracle

Я работаю над Oracle 10gR2.

У меня есть инструкция MERGE для таблицы TBL_CUSTOMER. TBL_CUSTOMER содержит столбец USERNAME, содержащий адреса электронной почты. Данные, хранящиеся в этой таблице, нечувствительны к регистру, так как входящие данные могут быть в верхнем регистре, нижнем регистре или любой комбинации регистров.

При объединении данных я должен убедиться, что я сравниваю данные без учета случая. Я создал базовый индекс функций в столбце USERNAME как UPPER (USERNAME).

MERGE INTO tbl_customer t
      USING (SELECT /*+ dynamic_sampling(a 2) */  NVL(
                                                      (x.username||decode((x.cnt+x.rn-1),0,null,(x.cnt+x.rn-1))),
                                                      t1.cust_username
                                                     ) community_id
                               ,DECODE (source_system_name,'SYS1', t1.cust_firstname,t1.cust_username) display_name
                               ,t1.cust_username
                              ,t1.cust_id cust_id
                              ,t1.cust_account_no cust_account_no
                              ,t1.cust_creation_date
                              ,t1.source_system_name
                              ,t1.seq_no
                              ,nvl(t1.cust_email,'NULLEMAIL') cust_email
                              ,t1.file_name
                              ,t1.diakey
                              ,t1.sourcetupleidcustmer
                              ,DECODE (source_system_name,'SYS1','DefaultPassword',t1.cust_password) cust_password
             FROM   gtt_customer_data t1,
                    (SELECT a.username,
                            cust_id,
                            row_number() over(partition by lower(a.username) order by  seq_no) rn,
                            (SELECT count(community_id)FROM TBL_customer where regexp_like (lower(community_id) ,'^'||lower(a.username)||'[0-9]*$'))cnt
                     FROM   gtt_cust_count_name a  
                    ) x
             WHERE  t1.cust_status = 'A'
             AND    x.cust_id(+)  = t1.cust_id
             AND    nvl(t1.op_code,'X') <> 'D'
             AND    t1.cust_id is not null
             AND    cust_email is not null
            ) a
      ON    ( 
             (a.sourcetupleidcustmer = t.source_tuple_id AND a.source_system_name =t.created_by)
             OR 
             ( upper(a.cust_email) = upper(t.username) AND a.source_system_name ='SYS2' )
            )

Когда я проверяю план объяснения, индекс на основе функции для USERNAME не используется. Я заметил, что если я удаляю условие ИЛИ, используется индекс, но я не могу его удалить из-за сложной бизнес-логики.

Как я могу заставить этот индекс использоваться?


sql oracle indexing oracle10g sql-tuning
person Incognito    schedule 18.11.2011    source источник
comment
Ваша подсказка dynamic_sampling не сработает. Вы должны переместить его внутрь встроенного представления X.   -  person Jon Heller    schedule 19.11.2011

Ответы (1)


arrow_upward
2
arrow_downward

Oracle позволяет создавать индексы на основе функций, в вашем случае upper(username). Вы также можете попробовать подсказку INDEX в запросе, но я думаю, что в вашем случае индекс на основе функций является гораздо лучшим решением.

Индексы BTree обычно не используются, если поле индекса является аргументом функции (при условии, что функция в WHERE не является покрывающим индексом). Например, WHERE trunc(date_field) = trunc(sysdate()) не будет использовать индекс для date_field, но будет использовать индекс для (trunc(date_field)).

person a1ex07    schedule 18.11.2011
comment
Спасибо за ваше предложение Алекс. Позвольте мне попробовать создать индекс так, как вы предложили - (UPPER (ИМЯ ПОЛЬЗОВАТЕЛЯ)) - person Incognito; 18.11.2011