Есть 2 таблицы:
DBADMIN.DOCUMENT
(ID BIGINT NOT NULL DEFAULT 0,
SHOP_ID BIGINT NOT NULL,
PARTNER_ID BIGINT NOT NULL,
DOC_NUM BIGINT NOT NULL,
CLASS_NAME BIGINT NOT NULL,
DOC_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
ACPT SMALLINT NOT NULL DEFAULT 0,
SUM_UCH DECIMAL(20, 2) NOT NULL DEFAULT 0,
SUM_ZAK DECIMAL(20, 2) NOT NULL DEFAULT 0,
SUM_PROD DECIMAL(20, 2) NOT NULL DEFAULT 0,
SUM_FAKT DECIMAL(20, 2) NOT NULL DEFAULT 0
)

DBADMIN.STRINGS
(ID BIGINT NOT NULL DEFAULT 0,
SHOP_ID BIGINT NOT NULL,
PARTNER_ID BIGINT NOT NULL,
DOC_ID BIGINT NOT NULL,
TOVAR_ID BIGINT NOT NULL,
PARTIJA_ID BIGINT NOT NULL DEFAULT 0,
STRING_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CLASS_NAME BIGINT NOT NULL,
ACPT SMALLINT NOT NULL DEFAULT 0,
PRICE_UCH DECIMAL(20, 2) NOT NULL DEFAULT 0,
PRICE_ZAK DECIMAL(20, 2) NOT NULL DEFAULT 0,
PRICE_PROD DECIMAL(20, 2) NOT NULL DEFAULT 0,
PRICE_FAKT DECIMAL(20, 2) NOT NULL DEFAULT 0,
TOVAR_MOV DECIMAL(20, 3) NOT NULL DEFAULT 0
)

В DBADMIN.DOCUMENT есть primary key ID.
В DBADMIN.STRINGS есть foreign key DBADMIN.STRINGS.DOC_ID = DBADMIN.DOCUMENT.ID

В таблице DBADMIN.DOCUMENT порядка 1,5 млн. записей, DBADMIN.STRINGS порядка 9 млн.записей.

Запускаю запрос вида:

select b.tovar_id, sum(b.tovar_mov) as kolich
from dbadmin.strings b, dbadmin.document c
where b.doc_id = c.id and c.class_name = 43
and b.acpt = 0 and c.shop_id = 5
group by b.tovar_id

Почему не хватаются индексы для DBADMIN.STRINGS. Explain показывает, что идет Table access full.
Подскажите чего переделать?

1. На dbadmin.document есть индекс ind15_doc (id, class_name, shop_id).
На dbadmin.strings есть индексы, которые используются для других запросов.... С полем doc_id есть индекс, который создался при создании foreign key

2. Реорганизация и сбор статистики произведен.
3. Запрос возвращает 5153 записи, данные целиком выбираются по индексу ind15_doc.

Правда на индексе (doc_id) cardinality стоит 400000....

Когда изменяю класс оптимизации до 0 или 1, запрос работает на порядок быстрее По умолчанию стоит 5 класс.

Создал индекс на dbadmin.document по (class_name, shop_id)... Собрал статистику... Эффект отрицательный....

Сейчас старый индекс удалю, перепроверю результат....

После удаления старого индекса стало немного легче.... Надо по-лучше потестировать...

У меня DB2 v.8.1.9... В syscat.indexes нет столбца indcard... Какой подсунуть?

DOCUMENT +ID 2007-11-21 10:11:18.75 1610933
DOCUMENT +SHOP_ID 2007-11-21 10:11:18.75 34
DOCUMENT +PARTNER_ID 2007-11-21 10:11:18.75 1294
DOCUMENT +DOC_NUM 2007-11-21 10:11:18.75 292715
DOCUMENT +CLASS_NAME 2007-11-21 10:11:18.75 85
DOCUMENT +DOC_TIME 2007-11-21 10:11:18.75 1437707
DOCUMENT +ACPT 2007-11-21 10:11:18.75 2
DOCUMENT +ID+SHOP_ID+ACPT 2007-11-21 10:11:18.75 1610933
DOCUMENT +ID+DOC_TIME+SHOP_ID 2007-11-21 10:11:18.75 1610933
DOCUMENT +ID+SHOP_ID+DOC_TIME+ACPT 2007-11-21 13:16:25.937 1611757
DOCUMENT +ID+PARTNER_ID+ACPT+CLASS_NAME 2007-11-21 10:11:18.75 1610933
DOCUMENT +SHOP_ID+DOC_TIME+ID 2007-11-21 10:11:18.75 1610933
DOCUMENT +SHOP_ID+PARTNER_ID+CLASS_NAME 2007-11-21 10:11:18.75 26415
DOCUMENT +CLASS_NAME+SHOP_ID 2007-11-21 15:41:20.89 1046
STRINGS +ID 2007-11-21 00:24:27.281 6851709
STRINGS +DOC_ID 2007-11-21 00:24:27.281 412979
STRINGS +SHOP_ID 2007-11-21 00:24:27.281 33
STRINGS +TOVAR_ID 2007-11-21 00:24:27.281 51689
STRINGS +SHOP_ID+STRING_TIME 2007-11-21 00:24:27.281 284579
STRINGS +CLASS_NAME 2007-11-21 00:24:27.281 19
STRINGS +PARTIJA_ID 2007-11-21 00:24:27.281 3796526
STRINGS +DOC_ID+TOVAR_ID 2007-11-21 00:24:27.281 5171940

Mark, Вам кажется странным, что в strings идентификаторов документов меньше, чем в document? Это объясняется тем, что в document есть записи, которые not in (select doc_id from strings group by doc_id).....
На всякий случай пересобрал
STRINGS +DOC_ID 2007-11-21 16:58:24.250000 413346

Скорость запроса не зменилась...
Может тут логический косяк при проектировании? Надо было разносить по отдельным таблицам два типа document'ов?

Может что в настройках параметров базы или движка? Кучи какой-нибудь недостаточно?

Mark, я правильно понимаю, что в ссылке указываются способы оказания влияния на выбор плана оптимизатором?

Это для 8 версии актуально?

volatile изменений в план запроса не дал... Индекс по-прежнему не подхватывает...

Чего-то не пойму.... Профайл создается для клиента или для сервера?

Hunterik, а путь указывать как? Или его в \bin\ положить?

Нету у нас такой таблички:(
Mark, help me! :) Как профайл подгрузить-то?

Чтобы оптимизатор использовал профайлы нужно задавать свойства приложениям или серверу? В смысле придется лезть в код приложений или достаточно что-то где-то на сервере прописать?

Ну может кто-нибудь подкинет ссылку на DB2 v.8.2 по этому вопросу?

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002

Licensed Material - Program Property of IBM

IBM DB2 Universal Database SQL Explain Tool



******************** DYNAMIC ***************************************



==================== STATEMENT ==========================================



Isolation Level = Cursor Stability

Blocking = Block Unambiguous Cursors

Query Optimization Class = 5



Partition Parallel = No

Intra-Partition Parallel = Yes (Bind Degree = ANY )



SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DBADMIN"





SQL Statement:



select b.tovar_id, sum(b.tovar_mov) as kolich

from dbadmin.strings b, dbadmin.document c

where b.doc_id =c.id and c.class_name =43 and b.acpt =0

and c.shop_id =5

group by b.tovar_id



Intra-Partition Parallelism Degree = 4



Section Code Page = 1251



Estimated Cost = 22074.199219

Estimated Cardinality = 3279.986084



Process Using 4 Subagents

| Access Table Name = DBADMIN.DOCUMENT ID = 5,7

| | Index Scan: Name = DBADMIN.IND51_DOC ID = 15

| | | Regular Index (Not Clustered)

| | | Index Columns:

| | | | 1: CLASS_NAME (Ascending)

| | | | 2: SHOP_ID (Ascending)

| | #Columns = 0

| | Parallel Scan

| | #Key Columns = 2

| | | Start Key: Inclusive Value

| | | | | 1: 43

| | | | | 2: 5

| | | Stop Key: Inclusive Value

| | | | | 1: 43

| | | | | 2: 5

| | Index-Only Access

| | Index Prefetch: None

| | Isolation Level: Uncommitted Read

| | Lock Intents

| | | Table: Intent None

| | | Row : None

| | Sargable Index Predicate(s)

| | | Insert Into Sorted Shared Temp Table ID = t1

| | | | #Columns = 1

| | | | #Sort Key Columns = 1

| | | | | Key 1: (Ascending)

| | | | Use Partitioned Sort

| | | | Sortheap Allocation Parameters:

| | | | | #Rows = 1543

| | | | | Row Width = 12

| | | | Piped

| | | | Duplicate Elimination

| Sorted Shared Temp Table Completion ID = t1

| List Prefetch Preparation

| | Access Table Name = DBADMIN.DOCUMENT ID = 5,7

| | | #Columns = 1

| | | Fetch Using Prefetched List

| | | | Prefetch: 311 Pages

| | | Lock Intents

| | | | Table: Intent Share

| | | | Row : Next Key Share

| | | Sargable Predicate(s)

| | | | #Predicates = 2

| | | | Process Build Table for Hash Join

| Hash Join

| | Early Out: Single Match Per Outer Row

| | Estimated Build Size: 41270

| | Estimated Probe Size: 138206032

| | Bit Filter Size: 1524

| | Access Table Name = DBADMIN.STRINGS ID = 5,12

| | | #Columns = 3

| | | Volatile Cardinality

| | | Parallel Scan

| | | Relation Scan

| | | | Prefetch: Eligible

| | | Lock Intents

| | | | Table: Intent Share

| | | | Row : Next Key Share

| | | Sargable Predicate(s)

| | | | #Predicates = 1

| | | | Process Probe Table for Hash Join

| Insert Into Sorted Temp Table ID = t2

| | #Columns = 2

| | #Sort Key Columns = 1

| | | Key 1: TOVAR_ID (Ascending)

| | Sortheap Allocation Parameters:

| | | #Rows = 3280

| | | Row Width = 24

| | Piped

| Access Temp Table ID = t2

| | #Columns = 2

| | Relation Scan

| | | Prefetch: Eligible

| | Sargable Predicate(s)

| | | Partial Predicate Aggregation

| | | | Group By

| | | | Column Function(s)

| Partial Aggregation Completion

| | Group By

| | Column Function(s)

| Insert Into Asynchronous Local Table Queue ID = q1

Access Local Table Queue ID = q1 #Columns = 3

| Output Sorted

| | #Key Columns = 1

| | | Key 1: (Ascending)

Final Aggregation

| Group By

| Column Function(s)

Return Data to Application

| #Columns = 2



End of section

что-то я не найду команд

db2 set current snapshot yes
db2 set current explain mode yes

он на них ругается

вАХ!
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 08.02.2
SOURCE_NAME: SYSSH200
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2007-11-22-14.17.52.765001
EXPLAIN_REQUESTER: DBADMIN

Database Context:
----------------
Parallelism: Intra-Partition Parallelism
CPU Speed: 2.361721e-007
Comm Speed: 0
Buffer Pool size: 115000
Sort Heap size: 512
Database Heap size: 8192
Lock List size: 2048
Maximum Lock List: 80
Average Applications: 5
Locks Available: 167116

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability



---------------- STATEMENT 1 SECTION 65 ----------------
QUERYNO: 114576
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: -1

Original Statement:
------------------
select b . tovar_id , sum (b . TOVAR_MOV) as vozvrat
from DBADMIN . STRINGS b , DBADMIN . DOCUMENT c
where b . doc_id = c . id and c . class_name = 43 and b . acpt = 0
and c . shop_id = 5
group by b . tovar_id


Optimized Statement:
-------------------
SELECT Q4.$C0 AS "TOVAR_ID", Q4.$C1 AS "VOZVRAT"
FROM
(SELECT Q3.$C0, SUM(Q3.$C1)
FROM
(SELECT Q2.TOVAR_ID, Q2.TOVAR_MOV
FROM DBADMIN.DOCUMENT AS Q1, DBADMIN.STRINGS AS Q2
WHERE (Q1.shop_id = 5) AND (Q2.acpt = 0) AND (Q1.CLASS_NAME =
43) AND (Q2.doc_id = Q1.ID)) AS Q3
GROUP BY Q3.$C0) AS Q4

Access Plan:
-----------
Total Cost: 22074.2
Query Degree: 4

Rows
RETURN
( 1)
Cost
I/O
|
3279.99
GRPBY
( 2)
22073.9
56810.4
|
3279.99
LMTQ
( 3)
22073.7
56810.4
|
3279.99
GRPBY
( 4)
22072.8
56810.4
|
3279.99
TBSCAN
( 5)
22072.6
56810.4
|
3279.99
SORT
( 6)
22072.5
56810.4
|
3279.99
HSJOIN
( 7)
22070.9
56810.4
/-------+------\
3.43087e+006 1542.05
TBSCAN FETCH
( 8) ( 9)
21781.3 110.301
56496 314.381
| /----+---\
6.86173e+006 1542.05 1.61298e+006
TABLE: DBADMIN RIDSCN TABLE: DBADMIN
STRINGS ( 10) DOCUMENT
6.10407
1
|
1542.05
SORT
( 11)
6.10377
1
|
1542.05
IXSCAN
( 12)
5.67637
1
|
1.61298e+006
INDEX: DBADMIN
IND51_DOC




Extended Diagnostic Information:
--------------------------------

Diagnostic tables do no exist. No extended Diagnostic Information is available.


Plan Details:
-------------


1) RETURN: (Return Result)
Cumulative Total Cost: 22074.2
Cumulative CPU Cost: 1.58617e+010
Cumulative I/O Cost: 56810.4
Cumulative Re-Total Cost: 22071.8
Cumulative Re-CPU Cost: 1.58517e+010
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 22072.5
Estimated Bufferpool Buffers: 0

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.9.700 : s050422
STMTHEAP: (Statement heap size)
8192

Input Streams:
-------------
14) From Operator #2

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q5.VOZVRAT+Q5.TOVAR_ID


2) GRPBY : (Group By)
Cumulative Total Cost: 22073.9
Cumulative CPU Cost: 1.58603e+010
Cumulative I/O Cost: 56810.4
Cumulative Re-Total Cost: 22071.5
Cumulative Re-CPU Cost: 1.58502e+010
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 22072.5
Estimated Bufferpool Buffers: 0

Arguments:
---------
AGGMODE : (Aggregration Mode)
FINAL
GROUPBYC: (Group By columns)
TRUE
GROUPBYN: (Number of Group By columns)
1
GROUPBYR: (Group By requirement)
1: Q3.TOVAR_ID
ONEFETCH: (One Fetch flag)
FALSE

Input Streams:
-------------
13) From Operator #3

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


Output Streams:
--------------
14) To Operator #1

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q5.VOZVRAT+Q5.TOVAR_ID


3) TQ : (Table Queue)
Cumulative Total Cost: 22073.7
Cumulative CPU Cost: 1.58594e+010
Cumulative I/O Cost: 56810.4
Cumulative Re-Total Cost: 22071.3
Cumulative Re-CPU Cost: 1.58494e+010
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 22072.5
Estimated Bufferpool Buffers: 0

Arguments:
---------
LISTENER: (Listener Table Queue type)
FALSE
SORTKEY : (Sort Key column)
1: Q3.TOVAR_ID(A)
TQ TYPE : (Table queue type)
LOCAL
TQDEGREE: (Degree of Intra-Partition parallelism)
4
TQMERGE : (Merging Table Queue flag)
TRUE
TQREAD : (Table Queue Read type)
READ AHEAD
UNIQUE : (Uniqueness required flag)
FALSE

Input Streams:
-------------
12) From Operator #4

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


Output Streams:
--------------
13) To Operator #2

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


4) GRPBY : (Group By)
Cumulative Total Cost: 22072.8
Cumulative CPU Cost: 1.5856e+010
Cumulative I/O Cost: 56810.4
Cumulative Re-Total Cost: 22071.3
Cumulative Re-CPU Cost: 1.58494e+010
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 22072.5
Estimated Bufferpool Buffers: 0

Arguments:
---------
AGGMODE : (Aggregration Mode)
PARTIAL
GROUPBYC: (Group By columns)
TRUE
GROUPBYN: (Number of Group By columns)
1
GROUPBYR: (Group By requirement)
1: Q3.TOVAR_ID
ONEFETCH: (One Fetch flag)
FALSE

Input Streams:
-------------
11) From Operator #5

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


Output Streams:
--------------
12) To Operator #3

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


5) TBSCAN: (Table Scan)
Cumulative Total Cost: 22072.6
Cumulative CPU Cost: 1.58551e+010
Cumulative I/O Cost: 56810.4
Cumulative Re-Total Cost: 22071.1
Cumulative Re-CPU Cost: 1.58486e+010
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 22072.5
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD

Input Streams:
-------------
10) From Operator #6

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


Output Streams:
--------------
11) To Operator #4

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


6) SORT : (Sort)
Cumulative Total Cost: 22072.5
Cumulative CPU Cost: 1.58543e+010
Cumulative I/O Cost: 56810.4
Cumulative Re-Total Cost: 22070.9
Cumulative Re-CPU Cost: 1.58478e+010
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 22072.5
Estimated Bufferpool Buffers: 56612.3

Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
3280
ROWWIDTH: (Estimated width of rows)
24
SORTKEY : (Sort Key column)
1: Q3.TOVAR_ID(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
FALSE

Input Streams:
-------------
9) From Operator #7

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_MOV+Q3.TOVAR_ID


Output Streams:
--------------
10) To Operator #5

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_ID(A)+Q3.TOVAR_MOV


7) HSJOIN: (Hash Join)
Cumulative Total Cost: 22070.9
Cumulative CPU Cost: 1.58478e+010
Cumulative I/O Cost: 56810.4
Cumulative Re-Total Cost: 22070.9
Cumulative Re-CPU Cost: 1.58478e+010
Cumulative Re-I/O Cost: 56810.4
Cumulative First Row Cost: 22070.9
Estimated Bufferpool Buffers: 56612.3

Arguments:
---------
BITFLTR : (Hash Join Bit Filter used)
1524
EARLYOUT: (Early Out flag)
LEFT
HASHCODE: (Hash Code Size)
24 BIT
TEMPSIZE: (Temporary Table Page Size)
32768

Predicates:
----------
6) Predicate used in Join
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 6.19969e-007

Predicate Text:
--------------
(Q2.doc_id = Q1.ID)


Input Streams:
-------------
2) From Operator #8

Estimated number of rows: 3.43087e+006
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q2.TOVAR_MOV+Q2.TOVAR_ID+Q2.doc_id

8) From Operator #9

Estimated number of rows: 1542.05
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.ID


Output Streams:
--------------
9) To Operator #6

Estimated number of rows: 3279.99
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.TOVAR_MOV+Q3.TOVAR_ID


8) TBSCAN: (Table Scan)
Cumulative Total Cost: 21781.3
Cumulative CPU Cost: 1.50795e+010
Cumulative I/O Cost: 56496
Cumulative Re-Total Cost: 21781.3
Cumulative Re-CPU Cost: 1.50795e+010
Cumulative Re-I/O Cost: 56496
Cumulative First Row Cost: 5.02457
Estimated Bufferpool Buffers: 56496

Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
SCANGRAN: (Intra-Partition Parallelism Scan Granularity)
2
SCANTYPE: (Intra-Partition Parallelism Scan Type)
LOCAL PARALLEL
SCANUNIT: (Intra-Partition Parallelism Scan Unit)
PAGE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
VOLATILE: (Volatile type)
CARDINALITY

Predicates:
----------
4) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5

Predicate Text:
--------------
(Q2.acpt = 0)


Input Streams:
-------------
1) From Object DBADMIN.STRINGS

Estimated number of rows: 6.86173e+006
Number of columns: 5
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q2.$RID$+Q2.TOVAR_MOV+Q2.TOVAR_ID
+Q2.acpt+Q2.doc_id


Output Streams:
--------------
2) To Operator #7

Estimated number of rows: 3.43087e+006
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q2.TOVAR_MOV+Q2.TOVAR_ID+Q2.doc_id


9) FETCH : (Fetch)
Cumulative Total Cost: 110.301
Cumulative CPU Cost: 9.13332e+006
Cumulative I/O Cost: 314.381
Cumulative Re-Total Cost: 1.53062
Cumulative Re-CPU Cost: 6.48093e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 11.1163
Estimated Bufferpool Buffers: 116.253

Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAX RIDS: (Maximum RIDs per list prefetch request)
512
MAXPAGES: (Maximum pages for prefetch)
311
PREFETCH: (Type of Prefetch)
LIST
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Predicates:
----------
3) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0294118

Predicate Text:
--------------
(Q1.shop_id = 5)

5) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0117647

Predicate Text:
--------------
(Q1.CLASS_NAME = 43)


Input Streams:
-------------
6) From Operator #10

Estimated number of rows: 1542.05
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.$RID$(A)

7) From Object DBADMIN.DOCUMENT

Estimated number of rows: 1.61298e+006
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.shop_id+Q1.CLASS_NAME+Q1.ID


Output Streams:
--------------
8) To Operator #7

Estimated number of rows: 1542.05
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.ID


10) RIDSCN: (Row Identifier Scan)
Cumulative Total Cost: 6.10407
Cumulative CPU Cost: 4.63251e+006
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.747395
Cumulative Re-CPU Cost: 3.16462e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.10377
Estimated Bufferpool Buffers: 2

Arguments:
---------
NUMROWS : (Estimated number of rows)
1543

Input Streams:
-------------
5) From Operator #11

Estimated number of rows: 1542.05
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.$RID$(A)


Output Streams:
--------------
6) To Operator #9

Estimated number of rows: 1542.05
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.$RID$(A)


11) SORT : (Sort)
Cumulative Total Cost: 6.10377
Cumulative CPU Cost: 4.63122e+006
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.655985
Cumulative Re-CPU Cost: 2.77757e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.10377
Estimated Bufferpool Buffers: 2

Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
1543
PARTCOLS: (Table partitioning columns)
1: Q1.$RID$
ROWWIDTH: (Estimated width of rows)
12
SORTKEY : (Sort Key column)
1: Q1.$RID$(A)
SORTTYPE: (Intra-Partition parallelism sort type)
PARTITIONED
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE

Input Streams:
-------------
4) From Operator #12

Estimated number of rows: 1542.05
Number of columns: 0
Subquery predicate ID: Not Applicable


Output Streams:
--------------
5) To Operator #10

Estimated number of rows: 1542.05
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.$RID$(A)


12) IXSCAN: (Index Scan)
Cumulative Total Cost: 5.67637
Cumulative CPU Cost: 2.82153e+006
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.655985
Cumulative Re-CPU Cost: 2.77757e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 5.0496
Estimated Bufferpool Buffers: 2

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
SCANGRAN: (Intra-Partition Parallelism Scan Granularity)
38
SCANTYPE: (Intra-Partition Parallelism Scan Type)
LOCAL PARALLEL
SCANUNIT: (Intra-Partition Parallelism Scan Unit)
ROW
TABLOCK : (Table Lock intent)
INTENT NONE

Predicates:
----------
3) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0294118

Predicate Text:
--------------
(Q1.shop_id = 5)

3) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0294118

Predicate Text:
--------------
(Q1.shop_id = 5)

5) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0117647

Predicate Text:
--------------
(Q1.CLASS_NAME = 43)

5) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0117647

Predicate Text:
--------------
(Q1.CLASS_NAME = 43)


Input Streams:
-------------
3) From Object DBADMIN.IND51_DOK_MAG

Estimated number of rows: 1.61298e+006
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.$RID$+Q1.shop_id+Q1.CLASS_NAME


Output Streams:
--------------
4) To Operator #11

Estimated number of rows: 1542.05
Number of columns: 0
Subquery predicate ID: Not Applicable


Objects Used in Access Plan:
---------------------------

Schema: DBADMIN
Name: IND51_DOK_MAG
Type: Index
Time of creation: 2007-11-21-15.40.00.875001
Last statistics update: 2007-11-22-00.01.09.375000
Number of columns: 2
Number of rows: 1612983
Width of rows: -1
Number of buffer pool pages: 10576
Distinct row values: No
Tablespace name: TOWDOC
Tablespace overhead: 5.000000
Tablespace transfer rate: 0.010000
Source for statistics: Single Node
Prefetch page count: 16
Container extent page count: 16
Index clustering statistic: 0.791274
Index leaf pages: 602
Index tree levels: 2
Index full key cardinality: 1046
Index first key cardinality: 85
Index first 2 keys cardinality: 1046
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 599
Index page density: 95
Index avg sequential pages: 599
Index avg gap between sequences:0
Index avg random pages: 2
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 1614133
Index deleted RID count: 1150
Index empty leaf pages: 0
Base Table Schema: DBADMIN
Base Table Name: DOCUMENT
Columns in index:
CLASS_NAME
shop_id

Schema: DBADMIN
Name: DOCUMENT
Type: Table
Time of creation: 2005-03-02-19.32.17.859001
Last statistics update: 2007-11-22-00.01.09.375000
Number of columns: 11
Number of rows: 1612983
Width of rows: 36
Number of buffer pool pages: 10576
Distinct row values: No
Tablespace name: TOWDOC
Tablespace overhead: 5.000000
Tablespace transfer rate: 0.010000
Source for statistics: Single Node
Prefetch page count: 16
Container extent page count: 16
Table overflow record count: 0
Table Active Blocks: -1

Schema: DBADMIN
Name: STRINGS
Type: Table
Time of creation: 2005-03-02-19.33.29.062001
Last statistics update: 2007-11-22-00.19.03.531000
Number of columns: 14
Number of rows: 6861731
Width of rows: 41
Number of buffer pool pages: 56496
Distinct row values: No
Tablespace name: TOWDOC
Tablespace overhead: 5.000000
Tablespace transfer rate: 0.010000
Source for statistics: Single Node
Prefetch page count: 16
Container extent page count: 16
Table overflow record count: 0
Table Active Blocks: -1

Mark, вставил MQT, естественно все ускорилось... В новой таблице всего:) 9000 записей...
Народ! А насколько оно вообще оправданно(использование MQT)? Допустим у меня таблицы обновляются по репликации... Теоретически можно понаделать на некоторые запросы MQT.
Насколько сильно они влияют на скорость редактирования таблиц?

Да уж.... В моих случаях требуются только refresh immediate Специфика такая.... Ну да ладно... Буду в дальнейшем оптимистичнее смотреть на этот вариант...

Hunterik, есть мысль какая-нибудь? Поделитесь, если не трудно... Просто я уже добавил summary table, и в план теперь попадает она...

Mark Barinstein, беда вся в том, что таких запросов происходит сотня в минуту... Я аж сам не поверил сначала... Тоже мне игла смерти в кощее... тьфу ты(пардон), в системе имел ввиду...
Refresh тогда наверно негативно скажется... Или я ошибаюсь?

Вот еще чего... MQT же вроде нельзя реорганизовывать? Или можно? Или при refresh table ... not incremental как раз и происходит реорганизация?

Hunterik, при уровнях 0 или 1 запрос выполнялся все же медленне, чем через MQT. В план запроса попадал индекс по doc_id.

Mark Barinstein, ну да... Полнейший OLTP+BI ) И выдерживает....:) Вот только иногда слабые места выявляются:)