Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Помогите создать правильный запрос для MView !! Умирает на сутки  [new]
makondo
Member

Откуда:
Сообщений: 428
Есть таблица CMPND с полями ID NUMBER PK и индексированным IDNUMBER varchar2(30) + еще 7-8 важных полей.
Есть нормализованная таблица CMPNDPARAVAL, связанная с CMPND по полю CMPND_ID :
  
  USRDB_ID       NUMBER                         NOT NULL,
  CMPND_ID       NUMBER                         NOT NULL,
  CMPNDPARAM_ID  NUMBER                         NOT NULL,
  ID             NUMBER                         NOT NULL,
  VALNUM         NUMBER,
  VALCHAR        VARCHAR2(3000 BYTE),..

В ней хранятся значения полей, которые добавляет пользователь. Если пользовательское поле числовое, то заполняется valnum, если текстовое - valchar.

Поскольку запросы с участием ограничений на пользовательские поля работают довольно медленно, была идея сделать для важных запросных полей материализованные вьюшки. Сделать я их хотел через "левые" соединения. Это получилось по 2 причинам:
1. у конкретного ID в CMPND может не быть строчки в CMPNDPARAMVAL для конкретного поля.
2. Мат.представление нельзя, оказывается, задавать с подзапросами типа

SELECT c.ID, c.idnumber, c.molweight,
       (SELECT valchar
          FROM cmpndparamval
         WHERE cmpnd_id = c.ID
           AND cmpndparam_id = 124
           AND usrdb_id = c.usrdb_id
           AND ROWNUM < 2) prodcode,
       (SELECT valchar
          FROM cmpndparamval
         WHERE cmpnd_id = c.ID
           AND cmpndparam_id = 74
           AND usrdb_id = c.usrdb_id
           AND ROWNUM < 2) saltdata,
       (SELECT valnum
          FROM cmpndparamval
         WHERE cmpnd_id = c.ID
           AND cmpndparam_id = 286
           AND usrdb_id = c.usrdb_id
           AND ROWNUM < 2) weight,
       (SELECT valchar
          FROM cmpndparamval
         WHERE cmpnd_id = c.ID
           AND cmpndparam_id = 282
           AND usrdb_id = c.usrdb_id
           AND ROWNUM < 2) blocking,
       (SELECT valchar
          FROM cmpndparamval
         WHERE cmpnd_id = c.ID
           AND cmpndparam_id = 285
           AND usrdb_id = c.usrdb_id
           AND ROWNUM < 2) clib
  FROM cmpnd c
 WHERE usrdb_id = 70



Результирующий запрос для мвьюшки генерируется процедурой в пакете и исполняется через EXECUTE IMMEDIATE.
Самое интересное, что на тестовых примерах он хорошо работал (мало выбранных полей: 2-3, мало записей: 100-200 тыс. ) но на миллионе записей и 5-6 полями процедура умирает при созданиии примерно такой виьюшки:

CREATE MATERIALIZED VIEW MV_70__17  TABLESPACE USERS NOCACHE LOGGING NOPARALLEL REFRESH FORCE START WITH TO_DATE('14-03-2007 23.10.00','dd-mm-yyyy hh24.mi.ss') NEXT SYSDATE+1 AS 

SELECT c.ID, c.idnumber, c.molweight, cpv1.valchar "CLIB",
       cpv2.valchar "PRODCODE", cpv3.valnum "WEIGHT", cpv4.valchar "BLOCKING",
       cpv5.valchar "ADDINFO"
  FROM cmpnd c LEFT JOIN cmpndparamval cpv1 ON c.ID = cpv1.cmpnd_id
                                          AND cpv1.usrdb_id = 70
                                          AND cpv1.cmpndparam_id = 285
       LEFT JOIN cmpndparamval cpv2 ON c.ID = cpv2.cmpnd_id
                                  AND cpv2.usrdb_id = 70
                                  AND cpv2.cmpndparam_id = 124
       LEFT JOIN cmpndparamval cpv3 ON c.ID = cpv3.cmpnd_id
                                  AND cpv3.usrdb_id = 70
                                  AND cpv3.cmpndparam_id = 286
       LEFT JOIN cmpndparamval cpv4 ON c.ID = cpv4.cmpnd_id
                                  AND cpv4.usrdb_id = 70
                                  AND cpv4.cmpndparam_id = 282
       LEFT JOIN cmpndparamval cpv5 ON c.ID = cpv5.cmpnd_id
                                  AND cpv5.usrdb_id = 70
                                  AND cpv5.cmpndparam_id = 125
 WHERE c.usrdb_id = 70




Оно и понятно - план показывает, что все соединения проводятся через HASH JOINs и OUTER HASH JOINs :

Plan
SELECT STATEMENT  CHOOSECost : 36 474 Bytes : 1 147 228 320 Cardinality : 179 760								
	18 HASH JOIN OUTER  (((((CMPND to CMPND) to CMPNDPARAMVAL) to CMPNDPARAMVAL) to CMPNDPARAMVAL) to CMPNDPARAMVAL) to CMPNDPARAMVALc.ID  = cpv5.cmpnd_id (+)Cost : 36 474 Bytes : 1 147 228 320 Cardinality : 179 760							
		15 HASH JOIN OUTER  ((((CMPND to CMPND) to CMPNDPARAMVAL) to CMPNDPARAMVAL) to CMPNDPARAMVAL) to CMPNDPARAMVALc.ID  = cpv4.cmpnd_id (+)Cost : 19 435 Bytes : 870 218 160 Cardinality : 179 760						
			12 HASH JOIN OUTER  (((CMPND to CMPND) to CMPNDPARAMVAL) to CMPNDPARAMVAL) to CMPNDPARAMVALc.ID  = cpv2.cmpnd_id (+)Cost : 7 738 Bytes : 593 208 000 Cardinality : 179 760					
				9 HASH JOIN OUTER  ((CMPND to CMPND) to CMPNDPARAMVAL) to CMPNDPARAMVALc.ID  = cpv1.cmpnd_id (+)Cost : 1 460 Bytes : 316 197 840 Cardinality : 179 760				
					6 HASH JOIN OUTER  (CMPND to CMPND) to CMPNDPARAMVALc.ID  = cpv3.cmpnd_id (+)Cost : 524 Bytes : 39 187 680 Cardinality : 179 760			
						4 VIEW CH2.index$_join$_001 Cost : 36 Bytes : 29 840 160 Cardinality : 179 760		
							3 HASH JOIN  CMPND to CMPNDBytes : 29 840 160 Cardinality : 179 760	
								1 INDEX RANGE SCAN NON-UNIQUE CH2.CMPND_IDNUMBER aliased as cc.USRDB_ID  = 70Cost : 29 Bytes : 29 840 160 Cardinality : 179 760Partition # : 8 Partitions accessed #2
								2 INDEX FAST FULL SCAN UNIQUE CH2.CMPND_STRUCT aliased as cCost : 29 Bytes : 29 840 160 Cardinality : 179 760Partition # : 9 Partitions accessed #2
						5 INDEX RANGE SCAN NON-UNIQUE CH2.IX_CMPNDPARAMVAL_VALNUM2 aliased as cpv3cpv3.usrdb_id (+)  = 70 , cpv3.cmpndparam_id (+)  = 286Cost : 2 Bytes : 171 704 Cardinality : 3 302Partition # : 10 Partitions accessed #2		
					8 TABLE ACCESS BY LOCAL INDEX ROWID CH2.CMPNDPARAMVAL aliased as cpv1Cost : 15 Bytes : 5 088 382 Cardinality : 3 302Partition # : 11 Partitions accessed #2			
						7 INDEX RANGE SCAN NON-UNIQUE CH2.CMPNDPARAMVAL_VALCHAR aliased as cpv1cpv1.usrdb_id (+)  = 70 , cpv1.cmpndparam_id (+)  = 285Cost : 2 Cardinality : 3 302Partition # : 12 Partitions accessed #2		
				11 TABLE ACCESS BY LOCAL INDEX ROWID CH2.CMPNDPARAMVAL aliased as cpv2Cost : 15 Bytes : 5 088 382 Cardinality : 3 302Partition # : 13 Partitions accessed #2				
					10 INDEX RANGE SCAN NON-UNIQUE CH2.CMPNDPARAMVAL_VALCHAR aliased as cpv2cpv2.usrdb_id (+)  = 70 , cpv2.cmpndparam_id (+)  = 124Cost : 2 Cardinality : 3 302Partition # : 14 Partitions accessed #2			
			14 TABLE ACCESS BY LOCAL INDEX ROWID CH2.CMPNDPARAMVAL aliased as cpv4Cost : 15 Bytes : 5 088 382 Cardinality : 3 302Partition # : 15 Partitions accessed #2					
				13 INDEX RANGE SCAN NON-UNIQUE CH2.CMPNDPARAMVAL_VALCHAR aliased as cpv4cpv4.usrdb_id (+)  = 70 , cpv4.cmpndparam_id (+)  = 282Cost : 2 Cardinality : 3 302Partition # : 16 Partitions accessed #2				
		17 TABLE ACCESS BY LOCAL INDEX ROWID CH2.CMPNDPARAMVAL aliased as cpv5Cost : 15 Bytes : 5 088 382 Cardinality : 3 302Partition # : 17 Partitions accessed #2						
			16 INDEX RANGE SCAN NON-UNIQUE CH2.CMPNDPARAMVAL_VALCHAR aliased as cpv5cpv5.usrdb_id (+)  = 70 , cpv5.cmpndparam_id (+)  = 125Cost : 2 Cardinality : 3 302Partition # : 18 Partitions accessed #2	




Что делать???? Может, кто знает, как помочь моей беде?...
23 мар 07, 17:48    [3935392]     Ответить | Цитировать Сообщить модератору
 Re: Помогите создать правильный запрос для MView !! Умирает на сутки  [new]
UDW
Member

Откуда: Самара
Сообщений: 1096
Возникает вопрос: а зачем тут мат. вьюшка???
Куда проще было-бы перезаполнять табличку через trunc/insert as
И мороки меньше и подзапросы сработают.
Дел в том, что FORCE в вашем случае смысла не имеет вовсе.
23 мар 07, 17:57    [3935453]     Ответить | Цитировать Сообщить модератору
 Re: Помогите создать правильный запрос для MView !! Умирает на сутки  [new]
makondo
Member

Откуда:
Сообщений: 428
Ну насчет матвьюшки, возможно , вы и правы. Единственное ее преимущество - то, что можно при создании регулировать периодическое обновление.

Но проблема не в ней, а в способе создания запроса.
Хм.. Хм... с поселектами работает много быстрее. Да, наверное, это возможный выход.

Это я попробую!

И все же - возможно как-то задать последний запрос, чтобы он выполнялся во вменяемое время для n=1 миллиона строк в главной таблице и n*6 строк в подчиненной?
23 мар 07, 18:21    [3935574]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить