Logo MurCode
Oracle Новое сообщение

Запрос: фильтрация данных. Помогите реализовать

Sergey M
Дата: 01.06.2006 10:36:52 
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
Нужно сделать следующее: Есть базовый набор данных, это либо простая таблица, либо результат некоторого запроса. Помимо него есть таблица - фильтр, по структуре она представляет набор полей из основной. В нее заносятся правила фильтрации - значения некоторых полей в базовой таблице. Результатом должны стать все записи основной таблицы, которые не попадают ни под один фильтр.
таблица - фильтр :
id_acct NUMBER(15,0),
nacct VARCHAR2(9),
id_service NUMBER(5,0),
id_provider NUMBER(4,0),
subservice NUMBER(9,0),
в ней возможны следующие варианты записей :
         --  Id_Acct |NAcct | Id_Service | Id_Provider | SubService
         -- -----------------------------------------------------
         --1     V   |   V  |       V    |       V     |    V
         --2     V   |   V  |            |       V     |
         --3     V   |   V  |       V    |             |
         --4     V   |   V  |            |             |
         --5         |      |       V    |       V     |    V
         --6         |      |            |       V     |
         --7         |      |       V    |             |
(V означает какое то значение. пусто значит NULL)
каждое правило означает отбросить все записи основной таблицы где соответствующее поле равно заданному. Правило 1 означает отбросить по точному соответствию всех полей, правило 2 - по соответствию полей id_acct и Id_Provider, правило №6 - по Id_Service и т.д.
В качестве базовой таблицы используется результат подзапроса. (~2,5 млн строк)
В таблице фильтра ~100 тыс строк


Я пробовал два варианта (одинаково хреново работающих), ничего другого на ум не приходит.
вариант №1 - внешнее соединение с таблицей фильтров 7 раз (по одному на каждый тип условия):

  SQL Statement from editor:
   
   
        select /*+ ordered use_nl(a, ss) use_nl(EEF1, EEF2, EEF3, EEF4, EEF5, EEF6, EEF7)
                 index(EEF1 EEF_NACCT_SUBSERVICE)
                 index(EEF2 EEF_NACCT_ID_PROVIDER)
                 index(EEF3 EEF_NACCT_ID_SERVICE)
                 index(EEF4 EEF_NACCT)
                 index(EEF5 EEF_SUBSERVICE)
                 index(EEF6 EEF_ID_PROVIDER)
                 index(EEF7 EEF_ID_SERVICE)*/*
          from --kp.erkc_vc_zkh_saldo@erkc a,
                Mihsv.erkc_vc_zkh_saldo a,
               ServiceState ss,
               (select * from ExchangeExcludeFilter where IsClose = 0 and nAcct is not null and subService is not null) EEF1, -- nacct-subservice
               (select * from ExchangeExcludeFilter where IsClose = 0 and nAcct is not null and id_provider is not null and id_service is null) EEF2, -- nacct-id_provider
               (select * from ExchangeExcludeFilter where IsClose = 0 and nAcct is not null and id_provider is null and id_service is not null) EEF3, -- nacct-id_service
               (select * from ExchangeExcludeFilter where IsClose = 0 and nAcct is not null and id_provider is null and id_service is null) EEF4,     -- nacct
               (select * from ExchangeExcludeFilter where IsClose = 0 and nAcct is null  and subservice is not null) EEF5,                            -- subservice
               (select * from ExchangeExcludeFilter where IsClose = 0 and nAcct is null  and id_provider is not null and id_service is null) EEF6,    -- id_provider_id_service
               (select * from ExchangeExcludeFilter where IsClose = 0 and nAcct is null  and id_provider is null and id_service is not null) EEF7     -- id_service
         where a.acc_pu = ss.nacct
           and (a.sub_srv_pu = ss.subservice)
           and (   a.saldo != ss.balans
                or a.fio != upper (ss.fio))                                               -- óñëîâèÿ ôèëüòðà
           and EEF1.NAcct      (+) = SS.NAcct
           and EEF1.SubService (+) = SS.Subservice
           and EEF1.NAcct      is null
           and EEF1.SubService is null
  
           and EEF2.NAcct       (+) = SS.NAcct
           and EEF2.id_provider (+) = to_number (substr (ss.subservice, -3, 3))
           and EEF2.NAcct       is null
           and EEF2.Id_Provider is null
  
           and EEF3.NAcct      (+) = SS.NAcct
           and EEF3.id_service (+) = to_number (substr (ss.subservice, 1, length (ss.subservice) - 3))
           and EEF3.NAcct      is null
           and EEF3.Id_Service is null
  
           and EEF4.NAcct (+) = SS.NAcct
           and EEF4.NAcct is null
  
           and EEF5.SubService (+) = SS.SubService
           and EEF5.SubService  is null
  
           and EEF6.id_provider (+) = to_number (substr (ss.subservice, -3, 3))
           and EEF6.Id_Provider is null
  
           and EEF7.id_service (+) = to_number (substr (ss.subservice, 1, length (ss.subservice) - 3))
           and EEF7.id_service is null
  ------------------------------------------------------------
    
  Statement Id=4203132   Type=
  Cost=2,64039087471493E-308  TimeStamp=01-06-06::13::29:27
  
       (1)  SELECT STATEMENT  RULE 
     Est. Rows: 396  Cost: 10 527
    FILTER
           (32)  NESTED LOOPS OUTER
               (29)  FILTER
                   (28)  NESTED LOOPS OUTER
                       (25)  FILTER
                           (24)  NESTED LOOPS OUTER
                               (21)  FILTER
                                   (20)  NESTED LOOPS OUTER
                                       (17)  FILTER
                                           (16)  NESTED LOOPS OUTER
                                               (13)  FILTER
                                                   (12)  NESTED LOOPS OUTER
                                                       (9)  FILTER
                                                           (8)  NESTED LOOPS OUTER
                                                               (5)  NESTED LOOPS 
                                                                    Est. Rows: 396  Cost: 4 587
                                                                   (2)  TABLE ACCESS FULL MIHSV.ERKC_VC_ZKH_SALDO  [Not Analyzed] 
                                                                   (2)   Est. Rows: 2 000  Cost: 2 587 
                                                                        Tablespace: USERS
                                                                   (4)  TABLE ACCESS BY INDEX ROWID CITY.SERVICESTATE  [Not Analyzed] 
                                                                   (4)   Est. Rows: 2 000  Cost: 1 
                                                                        Tablespace: CITY
                                                                       (3)  UNIQUE INDEX UNIQUE SCAN CITY.SERVICESTATE#NEW_PK  [Not Analyzed] 
                                                                            Est. Rows: 2 000
                                                               (7)  TABLE ACCESS BY INDEX ROWID CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                                                                    Est. Rows: 1  Cost: 2
                                                                   (6)  NON-UNIQUE INDEX RANGE SCAN CITY.EEF_NACCT_SUBSERVICE  [Not Analyzed] 
                                                                        Est. Rows: 1  Cost: 1
                                                       (11)  TABLE ACCESS BY INDEX ROWID CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                                                            Est. Rows: 1  Cost: 2
                                                           (10)  NON-UNIQUE INDEX RANGE SCAN CITY.EEF_NACCT_ID_PROVIDER  [Not Analyzed] 
                                                                Est. Rows: 1  Cost: 1
                                               (15)  TABLE ACCESS BY INDEX ROWID CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                                                    Est. Rows: 1  Cost: 2
                                                   (14)  NON-UNIQUE INDEX RANGE SCAN CITY.EEF_NACCT_ID_SERVICE  [Not Analyzed] 
                                                        Est. Rows: 1  Cost: 1
                                       (19)  TABLE ACCESS BY INDEX ROWID CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                                            Est. Rows: 1  Cost: 2
                                           (18)  NON-UNIQUE INDEX RANGE SCAN CITY.EEF_NACCT  [Not Analyzed] 
                                                Est. Rows: 1  Cost: 1
                               (23)  TABLE ACCESS BY INDEX ROWID CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                                    Est. Rows: 1  Cost: 2
                                   (22)  NON-UNIQUE INDEX RANGE SCAN CITY.EEF_SUBSERVICE  [Not Analyzed] 
                                        Est. Rows: 1  Cost: 1
                       (27)  TABLE ACCESS BY INDEX ROWID CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                            Est. Rows: 1  Cost: 3
                           (26)  NON-UNIQUE INDEX RANGE SCAN CITY.EEF_ALL  [Not Analyzed] 
                                Est. Rows: 1  Cost: 2
               (31)  TABLE ACCESS BY INDEX ROWID CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                    Est. Rows: 1  Cost: 2
                   (30)  NON-UNIQUE INDEX RANGE SCAN CITY.EEF_ID_SERVICE  [Not Analyzed] 
                        Est. Rows: 1  Cost: 1

[/SRC][FIXED][/FIXED]
он хорошо работает когда в фильтрах сотни записей, на сотнях тыс. понятно умирает...

Вариант второй - это внешнее сединение через NVL ():
  SQL Statement from editor:
   
   
        select /*+ ordered use_nl(a, ss)*/*
          from 
               ExchangeExcludeFilter EEF,
  		       (select * from Mihsv.erkc_vc_zkh_saldo) a,
               ServiceState ss
         where a.acc_pu = ss.nacct
           and (a.sub_srv_pu = ss.subservice)
           and (   a.saldo != ss.balans
                or a.fio != upper (ss.fio))
           and nvl(EEF.NAcct       (+),ss.NAcct) = SS.NAcct        
           and nvl(EEF.SubService  (+),SS.Subservice) = SS.Subservice
           and nvl(EEF.id_service  (+),to_number (substr (ss.subservice, 1, length (ss.subservice) - 3))) = to_number (substr (ss.subservice, 1, length (ss.subservice) - 3))
           and nvl(EEF.id_provider (+),to_number (substr (ss.subservice, -3, 3))) = to_number (substr (ss.subservice, -3, 3))     
           
           and EEF.NAcct        is null
           and EEF.SubService   is null
           and EEF.Id_Service   is null
           and EEF.Id_provider  is null
  ------------------------------------------------------------
    
  Statement Id=11072   Type=
  Cost=2,64039087471493E-308  TimeStamp=01-06-06::13::34:14
  
       (1)  SELECT STATEMENT  RULE 
     Est. Rows: 396  Cost: 8 547
    FILTER
           (7)  NESTED LOOPS OUTER
               (5)  NESTED LOOPS 
                    Est. Rows: 396  Cost: 4 587
                   (2)  TABLE ACCESS FULL MIHSV.ERKC_VC_ZKH_SALDO  [Not Analyzed] 
                   (2)   Est. Rows: 2 000  Cost: 2 587 
                        Tablespace: USERS
                   (4)  TABLE ACCESS BY INDEX ROWID CITY.SERVICESTATE  [Not Analyzed] 
                   (4)   Est. Rows: 2 000  Cost: 1 
                        Tablespace: CITY
                       (3)  UNIQUE INDEX UNIQUE SCAN CITY.SERVICESTATE#NEW_PK  [Not Analyzed] 
                            Est. Rows: 2 000
               (6)  TABLE ACCESS FULL CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
                    Est. Rows: 2 000  Cost: 10
но там на последнем шаге access full к таблице фильтров. ТОже никуда не годится ...
Как я понимаю. при аткой постановке запроса, индекс заставить использовать его нелься в принципе ? 
пытался применить во втором варианте вместо NL MERGE JOIN но оптимизатор игнорирует хинт USE_MERGE для таблицы фильтров :
[SRC oracle]  SQL Statement from editor:
   
   
        select /*+ ordered use_merge(a, ss,EEF)*/*
          from 
               ExchangeExcludeFilter EEF,
  		       (select * from Mihsv.erkc_vc_zkh_saldo) a,
               ServiceState ss
         where a.acc_pu = ss.nacct
           and (a.sub_srv_pu = ss.subservice)
           and (   a.saldo != ss.balans
                or a.fio != upper (ss.fio))
           and nvl(EEF.NAcct       (+),ss.NAcct) = SS.NAcct        
           and nvl(EEF.SubService  (+),SS.Subservice) = SS.Subservice
           and nvl(EEF.id_service  (+),to_number (substr (ss.subservice, 1, length (ss.subservice) - 3))) = to_number (substr (ss.subservice, 1, length (ss.subservice) - 3))
           and nvl(EEF.id_provider (+),to_number (substr (ss.subservice, -3, 3))) = to_number (substr (ss.subservice, -3, 3))     
           
           and EEF.NAcct        is null
           and EEF.SubService   is null
           and EEF.Id_Service   is null
           and EEF.Id_provider  is null
  ------------------------------------------------------------
    
  Statement Id=9588   Type=
  Cost=2,64039087471493E-308  TimeStamp=01-06-06::13::36:03
  
       (1)  SELECT STATEMENT  RULE 
     Est. Rows: 396  Cost: 8 423
    FILTER
           (9)  NESTED LOOPS OUTER
               (7)  MERGE JOIN 
                    Est. Rows: 396  Cost: 4 463
                   (3)  SORT JOIN 
                        Est. Rows: 2 000  Cost: 2 647
                       (2)  TABLE ACCESS FULL MIHSV.ERKC_VC_ZKH_SALDO  [Not Analyzed] 
                       (2)   Est. Rows: 2 000  Cost: 2 587 
                            Tablespace: USERS
                   (6)  FILTER
                       (5)  SORT JOIN
                           (4)  TABLE ACCESS FULL CITY.SERVICESTATE  [Not Analyzed] 
                           (4)   Est. Rows: 2 000  Cost: 1 728 
                                Tablespace: CITY
               (8)  TABLE ACCESS FULL CITY.EXCHANGEEXCLUDEFILTER  [Not Analyzed] 
               (8)   Est. Rows: 2 000  Cost: 10 
                    Tablespace: CITY
Помогите! третий день пухну... :(
Я
Дата: 01.06.2006 11:17:09
Sergey M
Помогите! третий день пухну... :(


Не совсем понял что за to_number (substr (ss.subservice, 1, length (ss.subservice) - 3))

а если попробовать так:

select * from DataTable
MINUS
select * from ExchangeExcludeFilter F, DataTable D where
D.Id_Acct = NVL(F.Id_Acct, D.Id_Acct) and
D.NAcct = NVL(F.NAcct, D.NAcct) and
... 
D.SubService= NVL(F.SubService, D.SubService);
Sergey M
Дата: 01.06.2006 11:29:57
Все просто - to_number (substr (ss.subservice, 1, length (ss.subservice) - 3)) = Id_Provider
Поле subservice имеет вид xyyy где yyy - ID_Provider a x - Id_Service.
про MINUS: не пробовал. Попробую сейчас
Sergey M
Дата: 01.06.2006 12:05:05
Ничего хорошего не выходит.
Там где присутствует NVL в where не могу заставить его использовать merge join никак.
и индекс заставить взять для nested loops в этом случае тоже не могу.
dmidek
Дата: 01.06.2006 12:22:39
Я бы предложил посмотреть на Вашу задачу под другим углом.
Во- первых, правила не должны быть с одной стороны избыточными , а с другой взаимоисключающими. Проверьте Ваше множество правил на эти критерии.
Во - вторых, я бы попробовал реализовать эту задачу с помощью функции.
На ум приходит такой вариант.
Вы создаете новое поле в таблице, в котором динамически формируете SQL- Statement, имеющий вид

SELECT 1 from base_table
WHERE id = :bind
and not exists
(SELECT правило ....)

При проверке правил идет обращение к функции , которая склеивает все SQL- операторы UNIONами и вместо :bind поставляет конкретное значение.
Если Вы получаете 1 , то все в порядке, NO_DATA_FOUND - фильтр не пропустил.
Правила можно изменять, добавлять, удалять, сделать джоб, который например раз в день актуализирует базовые SQL- операторы.

ИМХО, тот случай, когда динамический SQL + вызов функции может себя оправдать ...
Sergey M
Дата: 01.06.2006 13:54:16
Правила не взаимоисключающие.
Избыточные - да. Но от этого не уйти.
dmidek
Дата: 01.06.2006 13:57:48
Sergey M
Правила не взаимоисключающие.
Избыточные - да. Но от этого не уйти.

Почему ? При добавлении нового правила, проверяете, не заключает ли оно в себе "старые" и если да, отвергаете его. Так сильно выиграете при дальнейшем перформансе...
ModelR
Дата: 01.06.2006 14:07:52
dmidek
Sergey M
Правила не взаимоисключающие.
Избыточные - да. Но от этого не уйти.

Почему ? При добавлении нового правила, проверяете, не заключает ли оно в себе "старые" и если да, отвергаете его. Так сильно выиграете при дальнейшем перформансе...
Точнее наверно включаете новое и удаляете заключенные в нем. А отвергаем если новое "заключено в" (т.е отсеивет не больше) старых.
2 Sergey M.
--
NOT EXISTS не пробовали? Это не совет - просто интересно, почему этот вариант не рассматривался.
dmidek
Дата: 01.06.2006 14:28:05
ModelR
dmidek
Sergey M
Правила не взаимоисключающие.
Избыточные - да. Но от этого не уйти.

Почему ? При добавлении нового правила, проверяете, не заключает ли оно в себе "старые" и если да, отвергаете его. Так сильно выиграете при дальнейшем перформансе...
Точнее наверно включаете новое и удаляете заключенные в нем. А отвергаем если новое "заключено в" (т.е отсеивет не больше) старых.

Вы правы, я это и имел в виду, просто выразился коряво.
Но отсекать ИМХО можно и нужно :)
Sergey M
Дата: 06.06.2006 05:54:33
2 dmidek : Ваш вариант с динамическим SQL не годится, потому что правила нужно обновлять слишком часто. Я пробовал делать с NOT EXISTS по - другому (без динамического sql). Пробовал также вариант с обычной функцией, проверяющей удовлетворяет записть хотя бы одному из правил или нет. Результат в обоих случаях был отрицательный (время работы неприемлемо велико. Разумный максимум для времени выполнения этого запроса на 80 тыс. правил - не более 30-50 секунд).

К вопросу об избыточности правил: избыточность, как выяснилось в результате анализа, составляет менее чем 0,2 % (одно - три избыточных правила на тысячу).

Решить задачу в приемлемое время все таки удалось !
Решение заключилось в разделении одной таблицы правил, в которой хранились правила всех типов, на 7 разных таблиц, по одной для каждого типа правила. Это позволило избежать null полей в таблицах и на каждой из таблиц создать уникальные индексы. На единой таблице уникальные индексы построить было невозможно. Селективность же неуникальных индексов была достаточно низкой (скорее, недостаточно высокой), а количество записей - большим, отсюда и все задержки.