Оптимизация запроса(структуры)

[Вопрос]

Дата: 25.11.2009 16:48:28

Здравствуйте!
Имеется табличка с аттрибутами некоего договора, аттрибуты версионные

"ATTR4DOG_ID"	"CREATED"	"DOG_ID"	"TYPE$ATTR_ID"	"VERSNUM"
1	25.11.09	"1"	200	1
2	25.11.09	"1"	201	1
3	25.11.09	"1"	202	1
4	25.11.09	"1"	203	1
25	25.11.09	"1"	200	2
26	25.11.09	"1"	201	2
27	25.11.09	"1"	202	2
28	25.11.09	"1"	203	2
29	25.11.09	"1"	204	2
36	25.11.09	"2"	200	3
37	25.11.09	"2"	201	3
38	25.11.09	"2"	202	3
39	25.11.09	"2"	203	3

dog_id - иднтификатор договора,
TYPE$ATTR_ID - идентификатор атрибута.
хочу получить список всех атрибутов с максимальными значениями versnum, делаю так:

select 
  *
from
  attr4dog ad,
  (
    select 
      a.type$attr_id,
      max(a.versnum) max_vers
    from
      attr4dog a
    where
      a.dog_id=1
    group by
      a.type$attr_id
  )
  aa
where
  aa.max_vers=ad.versnum
and aa.type$attr_id=ad.type$attr_id

при этом вот такой план:

Execution Plan
----------------------------------------------------------
Plan hash value: 2332433546

-----------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name			  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|			  |    14 |   756 |    14   (0)| 00:01:12 |
|   1 |  NESTED LOOPS			|			  |	  |	  |	       |	  |
|   2 |   NESTED LOOPS			|			  |    14 |   756 |    14   (0)| 00:01:12 |
|   3 |    VIEW 			|			  |    12 |   312 |	2   (0)| 00:00:11 |
|   4 |     HASH GROUP BY		|			  |    12 |   108 |	2   (0)| 00:00:11 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| ATTR4DOG		  |    66 |   594 |	2   (0)| 00:00:11 |
|   6 |       INDEX FULL SCAN		| I$ATTR4DOG$TYPE$ATTR_ID |    78 |	  |	1   (0)| 00:00:06 |
|*  7 |    INDEX RANGE SCAN		| I$ATTR4DOG$TYPE$ATTR_ID |	7 |	  |	0   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS BY INDEX ROWID	| ATTR4DOG		  |	1 |    28 |	1   (0)| 00:00:06 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(TO_NUMBER("A"."DOG_ID")=1)
   7 - access("AA"."TYPE$ATTR_ID"="AD"."TYPE$ATTR_ID")
   8 - filter("AA"."MAX_VERS"="AD"."VERSNUM")


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  8  consistent gets
	  0  physical reads
	  0  redo size
       1202  bytes sent via SQL*Net to client
	419  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 11  rows processed

Вопросы:
1. Можно ли оптимизировать запрос до более лучшего с т. з. производительности?
2. Какие индексы лучше всего сделать с оглядкой на вопрос в пункте 1?
3. Каким образом лучше всего вычислять следующие значения для versnum при вставке данных?
4. Стоит ли использовать prymary key в виде столбца ATTR4DOG_ID(sequence) или отказаться от него в пользу составного DOG_ID+TYPE$ATTR_ID+VERSNUM?

-2-

Дата: 25.11.2009 17:05:22

[Вопрос],

row_number() over(partition by dog_id, ?attr_id? order by versnum desc) = 1

andrey_anonymous

Дата: 25.11.2009 17:11:08

select ad.*
     , max(versnum) over(partition by type$attr_id) max_vers
from
  attr4dog ad

suPPLer

Дата: 25.11.2009 17:48:55

andrey_anonymous

select ad.*
     , max(versnum) over(partition by type$attr_id) max_vers
from
  attr4dog ad

Я так понимаю, за кадром осталось заворачивание в этого в подзапрос и фильтр versnum = max_vers... ТС, вот ещё способы в дополнение к двум указанным ранее, но Вы выбирайте, исходя из плана и тестов на Ваших данных:

2.

select *
  from ATTR4DOG a
 where a.versnum = (select max(versnum) from ATTR4DOG where DOG_ID = a.DOG_ID and type$attr_id = a.type$attr_id)

select max(a.ATTR4DOG_ID) keep (dense_rank first order by a.VERSNUM desc)
     , max(a.created) keep (dense_rank first order by a.VERSNUM desc)
     , a.dog_id
     , a.TYPE$ATTR_ID
     , max(a.versnum)
  from ATTR4DOG a
 group by a.dog_id, a.type$attr_id

[Вопрос]

Дата: 25.11.2009 18:12:11

Как бы это не было странно, но во всех преложенных вариантах появляется FTS по таблице. :(

suPPLer

Дата: 25.11.2009 18:15:18

[Вопрос],

а Вы добавляли Ваш фильтр по айдишнику договора к этим вариантам?

[Вопрос]

Дата: 25.11.2009 18:35:23

Конечно. либо также как в воем случае делаем FULL INDEX SCAN, либо Full TAble Scan.
Спасибо всем ответившим, пищу для ума получил, буду думать!