Гость
Форумы
[новые:0]
/ IBM DB2, WebSphere, IMS, U2
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
select count full table scan
[новые:0]
16 сообщений из 16, страница 1 из 1
|
|
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
Привет всем! У меня есть запрос Код: sql 1. в этой таблице есть первичный ключ COPY_ID Но план запроса использует full table scan. Почему explan Snapshot DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009 Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool ******************** EXPLAIN INSTANCE ******************** DB2_VERSION: 09.07.8 SOURCE_NAME: SYSSH200 SOURCE_SCHEMA: NULLID SOURCE_VERSION: EXPLAIN_TIME: 2015-11-24-10.43.58.003761 EXPLAIN_REQUESTER: DB2INST1 Database Context: ---------------- Parallelism: None CPU Speed: 3.463858e-07 Comm Speed: 100 Buffer Pool size: 178428 Sort Heap size: 200 Database Heap size: 2670 Lock List size: 10000 Maximum Lock List: 30 Average Applications: 1 Locks Available: 96000 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 65 ---------------- QUERYNO: 1 QUERYTAG: Statement Type: Select Updatable: No Deletable: No Query Degree: 1 Original Statement: ------------------ select count(*) from nabrk.copy Optimized Statement: ------------------- SELECT Q3.$C0 FROM (SELECT COUNT(*) FROM (SELECT $RID$ FROM NABRK.COPY AS Q1) AS Q2) AS Q3 Access Plan: ----------- Total Cost: 19295 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 1 GRPBY ( 2) 19295 10736 | 481739 TBSCAN ( 3) 19253.3 10736 | 481739 TABLE: NABRK COPY Q1 Extended Diagnostic Information: -------------------------------- Diagnostic tables do not exist. No extended Diagnostic Information is available. Plan Details: ------------- 1) RETURN: (Return Result) Cumulative Total Cost: 19295 Cumulative CPU Cost: 1.00104e+09 Cumulative I/O Cost: 10736 Cumulative Re-Total Cost: 329.064 Cumulative Re-CPU Cost: 9.49992e+08 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 19295 Estimated Bufferpool Buffers: 10736 Arguments: --------- BLDLEVEL: (Build level) DB2 v9.7.0.8 : s130316 HEAPUSE : (Maximum Statement Heap Usage) 80 Pages PREPTIME: (Statement prepare time) 16 milliseconds STMTHEAP: (Statement heap size) 65536 Input Streams: ------------- 3) From Operator #2 Estimated number of rows: 1 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.$C0 2) GRPBY : (Group By) Cumulative Total Cost: 19295 Cumulative CPU Cost: 1.00104e+09 Cumulative I/O Cost: 10736 Cumulative Re-Total Cost: 329.064 Cumulative Re-CPU Cost: 9.49991e+08 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 19295 Estimated Bufferpool Buffers: 10736 Arguments: --------- AGGMODE : (Aggregation Mode) COMPLETE GROUPBYC: (Group By columns) FALSE GROUPBYN: (Number of Group By columns) 0 ONEFETCH: (One Fetch flag) FALSE Input Streams: ------------- 2) From Operator #3 Estimated number of rows: 481739 Number of columns: 0 Subquery predicate ID: Not Applicable Output Streams: -------------- 3) To Operator #1 Estimated number of rows: 1 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.$C0 3) TBSCAN: (Table Scan) Cumulative Total Cost: 19253.3 Cumulative CPU Cost: 8.806e+08 Cumulative I/O Cost: 10736 Cumulative Re-Total Cost: 287.347 Cumulative Re-CPU Cost: 8.29556e+08 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 12.8671 Estimated Bufferpool Buffers: 10736 Arguments: --------- LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) SEQUENTIAL ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SPEED : (Assumed speed of scan, in sharing structures) FAST TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY THROTTLE: (Scan may be throttled, for scan sharing) TRUE VISIBLE : (May be included in scan sharing structures) TRUE WRAPPING: (Scan may start anywhere and wrap) TRUE Input Streams: ------------- 1) From Object NABRK.COPY Estimated number of rows: 481739 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.$RID$ Output Streams: -------------- 2) To Operator #2 Estimated number of rows: 481739 Number of columns: 0 Subquery predicate ID: Not Applicable Objects Used in Access Plan: --------------------------- Schema: NABRK Name: COPY Type: Table Extended Statistics Information: -------------------------------- Tablespace Context: ------------------- Name: USERSPACE1 Overhead: 12.670000 Transfer Rate: 0.180000 Prefetch Size: 32 Extent Size: 32 Type: System managed Partition Group Name: NULLP Buffer Pool Identifier: 0 Base Table Statistics: ---------------------- Name : COPY Schema: NABRK Number of Columns: 12 Number of Pages with Rows: 10735 Number of Pages: 10735 Number of Rows: 481739 Table Overflow Record Count: 1 Width of Rows: 30 Time of Creation: 2015-09-17-15.30.22.161977 Last Statistics Update: 2015-11-24-10.02.17.967009 Primary Tablespace: USERSPACE1 Tablespace for Indexes: USERSPACE1 Tablespace for Long Data: NULLP Number of Referenced Columns: 0 Number of Indexes: 6 Volatile Table: No Number of Active Blocks: -1 Number of Column Groups: 0 Number of Data Partitions: 1 Average Row Compression Ratio: -1.000000 Percent Rows Compressed: -1.000000 Average Compressed Row Size: -1 Statistics Type: U Indexes defined on the table: ----------------------------- Name : COPY_BR_PART_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 2 Index Leaf Pages: 2264 Index Tree Levels: 3 Index First Key Cardinality: 272033 Index Full Key Cardinality: 284038 Index Cluster Ratio: -1 Index Cluster Factor: 0.461824 Time of Creation: 2015-09-17-15.30.24.597241 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: 284038 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 2264.000000 Fetch Avg Random Pages: 21692.000000 Index RID Count: 481780 Index Deleted RID Count: 41 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 81260 Pair 2: 1278 34871 Pair 3: 2130 22641 Pair 4: 2982 18506 Pair 5: 4260 15443 Pair 6: 6390 12669 Pair 7: 7242 12012 Pair 8: 9372 10973 Pair 9: 10224 10744 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : COPY_BR_ID_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 1726 Index Tree Levels: 3 Index First Key Cardinality: 272033 Index Full Key Cardinality: 272033 Index Cluster Ratio: -1 Index Cluster Factor: 0.461780 Time of Creation: 2015-09-17-15.30.24.576256 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 1726.000000 Fetch Avg Random Pages: 21692.000000 Index RID Count: 481780 Index Deleted RID Count: 41 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 81253 Pair 2: 1278 34873 Pair 3: 2130 22643 Pair 4: 2982 18506 Pair 5: 4260 15443 Pair 6: 6390 12668 Pair 7: 7242 12011 Pair 8: 9372 10973 Pair 9: 10224 10744 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : COPY_BARCODE_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 3812 Index Tree Levels: 3 Index First Key Cardinality: 337091 Index Full Key Cardinality: 337091 Index Cluster Ratio: -1 Index Cluster Factor: 0.279722 Time of Creation: 2015-09-17-15.30.24.557499 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 5 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 262.000000 Index Avg Sequential Pages: 5.000000 Fetch Avg Sequential Pages: 15.000000 Index Avg Random Pages: 3222.000000 Fetch Avg Random Pages: 62447.000000 Index RID Count: 481759 Index Deleted RID Count: 20 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 309355 Pair 2: 426 280557 Pair 3: 1278 238577 Pair 4: 2556 184901 Pair 5: 4686 101197 Pair 6: 6390 40597 Pair 7: 7242 16383 Pair 8: 7668 13217 Pair 9: 10224 10998 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : COPY_INVENTORY_NUMBER_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 2701 Index Tree Levels: 3 Index First Key Cardinality: 286772 Index Full Key Cardinality: 286772 Index Cluster Ratio: -1 Index Cluster Factor: 0.298974 Time of Creation: 2015-09-17-15.30.24.540530 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 161.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 41.000000 Index Avg Random Pages: 2701.000000 Fetch Avg Random Pages: 70586.000000 Index RID Count: 481758 Index Deleted RID Count: 19 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 316034 Pair 2: 426 286904 Pair 3: 852 264543 Pair 4: 2982 175857 Pair 5: 4686 111191 Pair 6: 6390 51395 Pair 7: 7242 27486 Pair 8: 7668 19250 Pair 9: 10224 10735 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : SQL150917153022180 Schema: SYSIBM Unique Rule: Unique key index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 2 Index Leaf Pages: 3001 Index Tree Levels: 3 Index First Key Cardinality: 272033 Index Full Key Cardinality: 481739 Index Cluster Ratio: -1 Index Cluster Factor: 0.462951 Time of Creation: 2015-09-17-15.30.22.161977 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: 481739 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 3001.000000 Fetch Avg Random Pages: 21690.000000 Index RID Count: 481781 Index Deleted RID Count: 42 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 81247 Pair 2: 1278 34879 Pair 3: 2130 22641 Pair 4: 2982 18505 Pair 5: 4260 15443 Pair 6: 6390 12669 Pair 7: 7242 12012 Pair 8: 9372 10973 Pair 9: 10224 10744 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : SQL150917153022160 Schema: SYSIBM Unique Rule: Primary key index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 2329 Index Tree Levels: 3 Index First Key Cardinality: 481739 Index Full Key Cardinality: 481739 Index Cluster Ratio: -1 Index Cluster Factor: 0.533138 Time of Creation: 2015-09-17-15.30.22.161977 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 2329.000000 Fetch Avg Random Pages: 355144.000000 Index RID Count: 481758 Index Deleted RID Count: 19 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 57272 Pair 2: 1278 25074 Pair 3: 1704 18487 Pair 4: 2130 15804 Pair 5: 3408 13744 Pair 6: 5112 12637 Pair 7: 5964 12354 Pair 8: 8946 11107 Pair 9: 9372 10991 Pair 10: 10735 10735 Pair 11: 10735 10735 ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 08:33 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
olzhas, Добрый день. Создайте оптимзационный профиль для этого запроса, укажите в нем этот индекс. Потом получите план запроса и сравните его цену с текущей. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 09:18 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
А интересно. Код: plaintext 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 10:17 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
Mark Barinstein, сделал я профиль, настроил на индекс, правда не на первичный (на него почему то не получилось), а на другой, он хоть и содержит дубликаты, но не имеет пустых значений. и запрос и правда стал дороже. Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. при чем почти по всем параметрам IXSCAN выигрывает у TBSCAN, но Cumulative Total Cost все равно выше? почему? explain с использование индекса DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009 Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool ******************** EXPLAIN INSTANCE ******************** DB2_VERSION: 09.07.8 SOURCE_NAME: SYSSH200 SOURCE_SCHEMA: NULLID SOURCE_VERSION: EXPLAIN_TIME: 2015-11-24-14.27.05.354978 EXPLAIN_REQUESTER: DB2INST1 Database Context: ---------------- Parallelism: None CPU Speed: 3.463858e-07 Comm Speed: 100 Buffer Pool size: 178428 Sort Heap size: 200 Database Heap size: 2670 Lock List size: 10000 Maximum Lock List: 30 Average Applications: 1 Locks Available: 96000 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 65 ---------------- QUERYNO: 1 QUERYTAG: Statement Type: Select Updatable: No Deletable: No Query Degree: 1 Profile Information: -------------------- OPT_PROF: (Optimization Profile Name) MY_SCHEMA.MY_NAME STMTPROF: (Statement Profile Name) Guidelines for my simplest query Original Statement: ------------------ select count(*) from nabrk.copy c Optimized Statement: ------------------- SELECT Q3.$C0 FROM (SELECT COUNT(*) FROM (SELECT $RID$ FROM NABRK.COPY AS Q1) AS Q2) AS Q3 Access Plan: ----------- Total Cost: 22526.8 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 1 GRPBY ( 2) 22526.8 1727 | 481739 IXSCAN ( 3) 22485.1 1727 | 481739 INDEX: NABRK COPY_BR_ID_IDX Q1 Extended Diagnostic Information: -------------------------------- Diagnostic tables do not exist. No extended Diagnostic Information is available. Plan Details: ------------- 1) RETURN: (Return Result) Cumulative Total Cost: 22526.8 Cumulative CPU Cost: 9.66819e+08 Cumulative I/O Cost: 1727 Cumulative Re-Total Cost: 328.898 Cumulative Re-CPU Cost: 9.49513e+08 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 22526.8 Estimated Bufferpool Buffers: 1728 Arguments: --------- BLDLEVEL: (Build level) DB2 v9.7.0.8 : s130316 HEAPUSE : (Maximum Statement Heap Usage) 80 Pages PREPTIME: (Statement prepare time) 328 milliseconds STMTHEAP: (Statement heap size) 65536 Input Streams: ------------- 3) From Operator #2 Estimated number of rows: 1 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.$C0 2) GRPBY : (Group By) Cumulative Total Cost: 22526.8 Cumulative CPU Cost: 9.66818e+08 Cumulative I/O Cost: 1727 Cumulative Re-Total Cost: 328.898 Cumulative Re-CPU Cost: 9.49512e+08 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 22526.8 Estimated Bufferpool Buffers: 1728 Arguments: --------- AGGMODE : (Aggregation Mode) COMPLETE GROUPBYC: (Group By columns) FALSE GROUPBYN: (Number of Group By columns) 0 ONEFETCH: (One Fetch flag) FALSE Input Streams: ------------- 2) From Operator #3 Estimated number of rows: 481739 Number of columns: 0 Subquery predicate ID: Not Applicable Output Streams: -------------- 3) To Operator #1 Estimated number of rows: 1 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.$C0 3) IXSCAN: (Index Scan) Cumulative Total Cost: 22485.1 Cumulative CPU Cost: 8.46383e+08 Cumulative I/O Cost: 1727 Cumulative Re-Total Cost: 287.181 Cumulative Re-CPU Cost: 8.29077e+08 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 25.7208 Estimated Bufferpool Buffers: 1728 Arguments: --------- LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) 1726 PREFETCH: (Type of Prefetch) NONE ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY Input Streams: ------------- 1) From Object NABRK.COPY_BR_ID_IDX Estimated number of rows: 481739 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.$RID$ Output Streams: -------------- 2) To Operator #2 Estimated number of rows: 481739 Number of columns: 0 Subquery predicate ID: Not Applicable Objects Used in Access Plan: --------------------------- Schema: NABRK Name: COPY Type: Table (reference only) Schema: NABRK Name: COPY_BR_ID_IDX Type: Index Base Table For Index Not Already Shown: --------------------------------------- Schema: NABRK Name: COPY Tablespace name: USERSPACE1 Tablespace overhead: 12.670000 Tablespace transfer rate: 0.180000 Prefetch page count: -1 Container extent page count: 32 Long tablespace name: USERSPACE1 Extended Statistics Information: -------------------------------- Tablespace Context: ------------------- Name: USERSPACE1 Overhead: 12.670000 Transfer Rate: 0.180000 Prefetch Size: 32 Extent Size: 32 Type: System managed Partition Group Name: NULLP Buffer Pool Identifier: 0 Base Table Statistics: ---------------------- Name : COPY Schema: NABRK Number of Columns: 12 Number of Pages with Rows: 10735 Number of Pages: 10735 Number of Rows: 481739 Table Overflow Record Count: 1 Width of Rows: 30 Time of Creation: 2015-09-17-15.30.22.161977 Last Statistics Update: 2015-11-24-10.02.17.967009 Primary Tablespace: USERSPACE1 Tablespace for Indexes: USERSPACE1 Tablespace for Long Data: NULLP Number of Referenced Columns: 0 Number of Indexes: 6 Volatile Table: No Number of Active Blocks: -1 Number of Column Groups: 0 Number of Data Partitions: 1 Average Row Compression Ratio: -1.000000 Percent Rows Compressed: -1.000000 Average Compressed Row Size: -1 Statistics Type: U Indexes defined on the table: ----------------------------- Name : COPY_BR_PART_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 2 Index Leaf Pages: 2264 Index Tree Levels: 3 Index First Key Cardinality: 272033 Index Full Key Cardinality: 284038 Index Cluster Ratio: -1 Index Cluster Factor: 0.461824 Time of Creation: 2015-09-17-15.30.24.597241 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: 284038 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 2264.000000 Fetch Avg Random Pages: 21692.000000 Index RID Count: 481780 Index Deleted RID Count: 41 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 81260 Pair 2: 1278 34871 Pair 3: 2130 22641 Pair 4: 2982 18506 Pair 5: 4260 15443 Pair 6: 6390 12669 Pair 7: 7242 12012 Pair 8: 9372 10973 Pair 9: 10224 10744 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : COPY_BR_ID_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: Yes Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 1726 Index Tree Levels: 3 Index First Key Cardinality: 272033 Index Full Key Cardinality: 272033 Index Cluster Ratio: -1 Index Cluster Factor: 0.461780 Time of Creation: 2015-09-17-15.30.24.576256 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 1726.000000 Fetch Avg Random Pages: 21692.000000 Index RID Count: 481780 Index Deleted RID Count: 41 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 81253 Pair 2: 1278 34873 Pair 3: 2130 22643 Pair 4: 2982 18506 Pair 5: 4260 15443 Pair 6: 6390 12668 Pair 7: 7242 12011 Pair 8: 9372 10973 Pair 9: 10224 10744 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : COPY_BARCODE_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 3812 Index Tree Levels: 3 Index First Key Cardinality: 337091 Index Full Key Cardinality: 337091 Index Cluster Ratio: -1 Index Cluster Factor: 0.279722 Time of Creation: 2015-09-17-15.30.24.557499 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 5 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 262.000000 Index Avg Sequential Pages: 5.000000 Fetch Avg Sequential Pages: 15.000000 Index Avg Random Pages: 3222.000000 Fetch Avg Random Pages: 62447.000000 Index RID Count: 481759 Index Deleted RID Count: 20 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 309355 Pair 2: 426 280557 Pair 3: 1278 238577 Pair 4: 2556 184901 Pair 5: 4686 101197 Pair 6: 6390 40597 Pair 7: 7242 16383 Pair 8: 7668 13217 Pair 9: 10224 10998 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : COPY_INVENTORY_NUMBER_IDX Schema: NABRK Unique Rule: Duplicate index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 2701 Index Tree Levels: 3 Index First Key Cardinality: 286772 Index Full Key Cardinality: 286772 Index Cluster Ratio: -1 Index Cluster Factor: 0.298974 Time of Creation: 2015-09-17-15.30.24.540530 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 161.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 41.000000 Index Avg Random Pages: 2701.000000 Fetch Avg Random Pages: 70586.000000 Index RID Count: 481758 Index Deleted RID Count: 19 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 316034 Pair 2: 426 286904 Pair 3: 852 264543 Pair 4: 2982 175857 Pair 5: 4686 111191 Pair 6: 6390 51395 Pair 7: 7242 27486 Pair 8: 7668 19250 Pair 9: 10224 10735 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : SQL150917153022180 Schema: SYSIBM Unique Rule: Unique key index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 2 Index Leaf Pages: 3001 Index Tree Levels: 3 Index First Key Cardinality: 272033 Index Full Key Cardinality: 481739 Index Cluster Ratio: -1 Index Cluster Factor: 0.462951 Time of Creation: 2015-09-17-15.30.22.161977 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: 481739 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 3001.000000 Fetch Avg Random Pages: 21690.000000 Index RID Count: 481781 Index Deleted RID Count: 42 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 81247 Pair 2: 1278 34879 Pair 3: 2130 22641 Pair 4: 2982 18505 Pair 5: 4260 15443 Pair 6: 6390 12669 Pair 7: 7242 12012 Pair 8: 9372 10973 Pair 9: 10224 10744 Pair 10: 10735 10735 Pair 11: 10735 10735 Name : SQL150917153022160 Schema: SYSIBM Unique Rule: Primary key index Used in Operator: No Page Fetch Pairs: Available Number of Columns: 1 Index Leaf Pages: 2329 Index Tree Levels: 3 Index First Key Cardinality: 481739 Index Full Key Cardinality: 481739 Index Cluster Ratio: -1 Index Cluster Factor: 0.533138 Time of Creation: 2015-09-17-15.30.22.161977 Last Statistics Update: 2015-11-24-10.02.17.967009 Index Sequential Pages: 0 Index First 2 Keys Cardinality: -1 Index First 3 Keys Cardinality: -1 Index First 4 Keys Cardinality: -1 Index Avg Gap between Sequences: 0.000000 Fetch Avg Gap between Sequences: 0.000000 Index Avg Sequential Pages: 0.000000 Fetch Avg Sequential Pages: 0.000000 Index Avg Random Pages: 2329.000000 Fetch Avg Random Pages: 355144.000000 Index RID Count: 481758 Index Deleted RID Count: 19 Index Empty Leaf Pages: 0 Avg Partition Cluster Ratio: -1 Avg Partition Cluster Factor: -1.000000 Data Partition Cluster Factor: 1.000000 Data Partition Page Fetch Pairs: Not Available Page Fetch Pairs information: ----------------------------- Number of Page Fetch Pairs: 11 Buffer Size Page Fetches -------------------------------------------------------- Pair 1: 100 57272 Pair 2: 1278 25074 Pair 3: 1704 18487 Pair 4: 2130 15804 Pair 5: 3408 13744 Pair 6: 5112 12637 Pair 7: 5964 12354 Pair 8: 8946 11107 Pair 9: 9372 10991 Pair 10: 10735 10735 Pair 11: 10735 10735 ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 11:49 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
olzhas, зато Cumulative CPU Cost: 1.00104e+09 Cumulative CPU Cost: 9.66819e+08 разница 26 раз (советую такие простыни заворачивать в [spoiler][fix], а не просто в [spoiler]) ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 12:17 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
PREFETCH: (Type of Prefetch) SEQUENTIAL и PREFETCH: (Type of Prefetch) NONE ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 12:21 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
Victor Metelitsa, На сколько я знаю этот параметр говорит о том что таблица будет считываться по несколько блоков за раз (в нашем случае 32) а индекс получается по одному. А почему индекс не может так же считываться по несколько блоков? структура не позволяет? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 13:28 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
olzhasVictor Metelitsa, На сколько я знаю этот параметр говорит о том что таблица будет считываться по несколько блоков за раз (в нашем случае 32) а индекс получается по одному. Что, на мой взгляд, и объясняет разницу в стоимости. А почему индекс не может так же считываться по несколько блоков? структура не позволяет? А вот это я не знаю. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 13:45 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
сделал реорг индексов. вырос параметр sequental_pages. и запрос начал использовать индекс. получается что блоки теперь в более "правильном" порядке. С этим вопросом вроде как понятно. теперь другое. Код: sql 1. b.br_id является первичным ключем. c.br_id не допускает пустых значений. Оракл в данной ситуации выбрасывает join и используется только одну таблицу. db2 же зачем то соединяет таблицы, хотя в этом смысла нету. Почему? к вопросу почему бы не убрать лишний join. Код генерируется построителем запросов со сложной логикой, не хотелось бы его менять. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 13:49 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
olzhasсделал реорг индексов. вырос параметр sequental_pages. и запрос начал использовать индекс. получается что блоки теперь в более "правильном" порядке. С этим вопросом вроде как понятно. теперь другое. Я бы не сказал, что как-то понятно. Разница должна быть отражена в какой-то статистике, потому что оптимизатор не монетку подбрасывает, а на чём-то основывается, а основывается он на статистике. Что изменилось? Распределение или Cluster Factor, к примеру, при неизменности таблицы поменяться не могли, сколько индексы не перестраивай. Они могли уменьшиться в размерах разве что. Код: sql 1. b.br_id является первичным ключем. c.br_id не допускает пустых значений. Оракл в данной ситуации выбрасывает join и используется только одну таблицу. db2 же зачем то соединяет таблицы, хотя в этом смысла нету. Почему? к вопросу почему бы не убрать лишний join. Код генерируется построителем запросов со сложной логикой, не хотелось бы его менять.[/quot] Почему же нет смысла? А foreign key наличествует? Откуда известно, что для c.br_id найдётся b.br_id? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 14:00 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
olzhas Код: sql 1. b.br_id является первичным ключем. c.br_id не допускает пустых значений. Оракл в данной ситуации выбрасывает join и используется только одну таблицу. db2 же зачем то соединяет таблицы, хотя в этом смысла нету. Почему? к вопросу почему бы не убрать лишний join. Код генерируется построителем запросов со сложной логикой, не хотелось бы его менять.Внешний ключ из copy на br есть? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 14:08 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
Mark Barinstein, Нет foreign key не было, я даже как то забыл на это посмотреть, был уверен что он есть. После его создания все работает как надо. Я бы не сказал, что как-то понятно. Разница должна быть отражена в какой-то статистике, потому что оптимизатор не монетку подбрасывает, а на чём-то основывается, а основывается он на статистике. Что изменилось? Распределение или Cluster Factor, к примеру, при неизменности таблицы поменяться не могли, сколько индексы не перестраивай. Они могли уменьшиться в размерах разве что. в syscat.indexes после реорганизации изменились 2 параметра SEQUENTIAL_PAGES и DENSITY, CLUSTERFACTOR остался же таким, что такое Распределение я не знаю. Судя по описанию SEQUENTIAL_PAGES отвечает фрагментацию индекса. видимо фрагментированный индекс тяжелее считывать. до реорганизации SEQUENTIAL_PAGES был 0, после приблизился к количеству листовых блоков. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 14:31 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
http://www.ibm.com/developerworks/data/library/techarticle/dm-1307optimizerunstats/ "multiple indexes for a table. Indexes for a table are stored in a single index object (with the exception of non-partitioned indexes of a partitioned table) and therefore, the pages of one index can be intermixed with the pages of another index." во как. и "DB2 10.1 provides new prefetching capabilities where index fragmentation no longer has a significant detrimental effect to index scan performance. This is discussed in more detail later in the article. " ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 15:36 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
Как я понял, "раньше" (до 10) надо было, чтобы страницы были "рядом" и "упорядочены", а "теперь" в ряде случаев упорядоченность не нужна. В самом деле, select count(*) без where не нужна никакая упорядоченность. Индексные страницы так и остаются в общей куче, это минус, в результате индексы могут быть использованы реже, чем могли бы при раздельном хранении, или их надо регулярно перестраивать. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 24.11.2015, 22:23 |
|
||
|
select count full table scan
|
|||
|---|---|---|---|
|
#18+
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0058907.html всё-таки я плохо понимаю эти вещи. Хороших картинок с разбором примеров не хватает. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 25.11.2015, 00:38 |
|
||
16 сообщений из 16, страница 1 из 1
|
|
Форумы
[новые:0]
/ IBM DB2, WebSphere, IMS, U2
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
select count full table scan
[новые:0]
