Today, I called an SQL statement, and BITMAPCONVERSION appeared in the execution plan. I checked the index and there was no bitmap index.
Today, I called an SQL statement, and bitmap conversion appeared in the execution plan. I checked the index and there was no BITMAP index.
Today I called an SQL statement, and bitmap conversion appeared in the execution plan. I checked the index and there was no BITMAP index. Oracle sometimes converts B-Tree indexes to BITMAP for SQL Execution, resulting in extremely bad execution plans. In the following plan, bitmap conversion from/to rowids is the execution plan after bitmap conversion:
SQL> SELECT *
2 FROM (select a. ASSET_ID,
3 C. CLASSIFY_CODE,
4 C. CLASSIFY_NAME,
5 V. NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR (SDO_UTIL.TO_GMLGEOMETRY (A. G3E_GEOMETRY) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 where a. CLASSIFY_ID = C. CLASSIFY_ID
9 and a. BASE_VOLTAGE_ID = V. BASE_VOLTAGE_ID (+)
10 and c. CLASSIFY_ID = '20140901'
11 AND SDO_RELATE (A. G3E_GEOMETRY,
12 sdo_geometry( 3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY (1, 1003, 1 ),
16 SDO_ORDINATE_ARRAY (113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0 )),
31 'MASK = ANYINTERACT') = 'true ')
32 where rownum <= 500;
Row 500 has been selected.
Used time: 00: 00: 06.71
Execution Plan
----------------------------------------------------------
Plan hash value: 1161815771
Bytes -------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------------------
| 0 | select statement | 500 | 130K | 263 (1) | 00:00:04 |
| * 1 | count stopkey |
| * 2 | hash join right outer | 501 | 130K | 263 (1) | 00:00:04 |
| 3 | table access full | DM_BASE_VOLTAGE | 81 | 729 | 3 (0) | 00:00:01 |
| 4 | nested loops | 501 | 64629 | 259 (1) | 00:00:04 |
| 5 | table access by index rowid | DM_CLASSIFY | 1 | 23 | 2 (0) | 00:00:01 |
| * 6 | index unique scan | IDX_DM_CLASSIFY_ID | 1 | 1 (0) | 00:00:01 |
| 7 | table access by index rowid | DM_ASSET | 501 | 53106 | 259 (1) | 00:00:04 |
| 8 | bitmap conversion to rowids |
| 9 | bitmap and |
| 10 | bitmap conversion from rowids |
| 11 | sort order by |
| * 12 | domain index | IDX_DM_ASSET_SPL | 50100 | 0 (0) | 00:00:01 |
| 13 | bitmap conversion from rowids |
| * 14 | index range scan | IDX_ASSET_CLASSIFY_ID | 50100 | 135 (1) | 00:00:02 |
Bytes -------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter (ROWNUM <= 500)
2-access ("A". "BASE_VOLTAGE_ID" = "V". "BASE_VOLTAGE_ID" (+ ))
6-access ("C". "CLASSIFY_ID" = '000000 ')
12-access ("MDSYS". "SDO_RELATE" ("A". "G3E_GEOMETRY", "MDSYS". "SDO_GEOMETRY" (3003,4326, NULL, "SDO_ELEM
_ INFO_ARRAY "(113.93897922622, 22.806658666304, 1)," SDO_ORDINATE_ARRAY "(0,114.38475977774, 22.8066
58666304,0, 114.38475977774, 22.42623522295, 0,113.93897922622, 22.42623522295, 0,113.93897922622, 22.80665
8666304,0), 'mask = ANYINTERACT ') = 'true ')
14-access ("A". "CLASSIFY_ID" = '000000 ')
Statistics
----------------------------------------------------------
76096 recursive cballs
6004 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL * Net to client
748 bytes encoded ed via SQL * Net from client
35 SQL * Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed
There are two solutions to this problem: Set _ B _tree_bitmap_plans to false, and remove the index with poor selectivity.
Try the first method:
SQL> alter session set "_ B _tree_bitmap_plans" = false;
SQL> SELECT *
2 FROM (select a. ASSET_ID,
3 C. CLASSIFY_CODE,
4 C. CLASSIFY_NAME,
5 V. NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR (SDO_UTIL.TO_GMLGEOMETRY (A. G3E_GEOMETRY) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 where a. CLASSIFY_ID = C. CLASSIFY_ID
9 and a. BASE_VOLTAGE_ID = V. BASE_VOLTAGE_ID (+)
10 and c. CLASSIFY_ID = '20140901'
11 AND SDO_RELATE (A. G3E_GEOMETRY,
12 sdo_geometry( 3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY (1, 1003, 1 ),
16 SDO_ORDINATE_ARRAY (113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0 )),
31 'MASK = ANYINTERACT') = 'true ')
32 where rownum <= 500;
Row 500 has been selected.
Used time: 00: 00: 06.51
Execution Plan
----------------------------------------------------------
Plan hash value: 1161815771
Bytes -------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------------------
| 0 | select statement | 500 | 130K | 263 (1) | 00:00:04 |
| * 1 | count stopkey |
| * 2 | hash join right outer | 501 | 130K | 263 (1) | 00:00:04 |
| 3 | table access full | DM_BASE_VOLTAGE | 81 | 729 | 3 (0) | 00:00:01 |
| 4 | nested loops | 501 | 64629 | 259 (1) | 00:00:04 |
| 5 | table access by index rowid | DM_CLASSIFY | 1 | 23 | 2 (0) | 00:00:01 |
| * 6 | index unique scan | IDX_DM_CLASSIFY_ID | 1 | 1 (0) | 00:00:01 |
| 7 | table access by index rowid | DM_ASSET | 501 | 53106 | 259 (1) | 00:00:04 |
| 8 | bitmap conversion to rowids |
| 9 | bitmap and |
| 10 | bitmap conversion from rowids |
| 11 | sort order by |
| * 12 | domain index | IDX_DM_ASSET_SPL | 50100 | 0 (0) | 00:00:01 |
| 13 | bitmap conversion from rowids |
| * 14 | index range scan | IDX_ASSET_CLASSIFY_ID | 50100 | 135 (1) | 00:00:02 |
Bytes -------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter (ROWNUM <= 500)
2-access ("A". "BASE_VOLTAGE_ID" = "V". "BASE_VOLTAGE_ID" (+ ))
6-access ("C". "CLASSIFY_ID" = '000000 ')
12-access ("MDSYS". "SDO_RELATE" ("A". "G3E_GEOMETRY", "MDSYS". "SDO_GEOMETRY" (3003,4326, NULL, "SDO_ELEM
_ INFO_ARRAY "(113.93897922622, 22.806658666304, 1)," SDO_ORDINATE_ARRAY "(0,114.38475977774, 22.8066
58666304,0, 114.38475977774, 22.42623522295, 0,113.93897922622, 22.42623522295, 0,113.93897922622, 22.80665
8666304,0), 'mask = ANYINTERACT ') = 'true ')
14-access ("A". "CLASSIFY_ID" = '000000 ')
Statistics
----------------------------------------------------------
76096 recursive cballs
6002 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL * Net to client
748 bytes encoded ed via SQL * Net from client
35 SQL * Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed
DM_ASSET has over million data records, while CLASSIFY_ID has only 39 values, 38 of which can be indexed. The number of values is very large, and the typical data is uneven.
Select CLASSIFY_ID, count (1) from DM_ASSET group by CLASSIFY_ID order by 2;
7002386 1
7002369 3
7002381 13
7002513 16
7002349 18
7002333 36
7002474 166
7002502 276
7002345 1131
7002344 1423
7002382 1709
7002359 1791
7002510 6340
7002374 6684
7002358 7286
7002361 8750
7002379 11189
7002340 12622
7002473 12717
7002348 14901
7002360 15722
7002335 17939
7002336 21492
7002500 28304
7002363 42883
7002343 49865
7002472 51327
7002321 76098
7002373 89485
7002515 110765
7002378 128512
7002380 147776
7002499 235166
7002370 271190
7002501 370439
7002398 456259
7002496 830986
7002401 4361079
Try the second method: Delete the index on CLASSIFY_ID.
SQL> SELECT *
2 FROM (select a. ASSET_ID,
3 C. CLASSIFY_CODE,
4 C. CLASSIFY_NAME,
5 V. NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR (SDO_UTIL.TO_GMLGEOMETRY (A. G3E_GEOMETRY) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 where a. CLASSIFY_ID = C. CLASSIFY_ID
9 and a. BASE_VOLTAGE_ID = V. BASE_VOLTAGE_ID (+)
10 and c. CLASSIFY_ID = '20140901'
11 AND SDO_RELATE (A. G3E_GEOMETRY,
12 sdo_geometry( 3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY (1, 1003, 1 ),
16 SDO_ORDINATE_ARRAY (113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0 )),
31 'MASK = ANYINTERACT') = 'true ')
32 where rownum <= 500;
Row 500 has been selected.
Used time: 00: 00: 00.50
Execution Plan
----------------------------------------------------------
Plan hash value: 4025821404
Bytes -----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------------
| 0 | select statement | 500 | 130K | 10563 (1) | 00:02:07 |
| * 1 | count stopkey |
| * 2 | hash join right outer | 501 | 130K | 10563 (1) | 00:02:07 |
| 3 | table access full | DM_BASE_VOLTAGE | 81 | 729 | 3 (0) | 00:00:01 |
| 4 | nested loops | 501 | 64629 | 10560 (1) | 00:02:07 |
| 5 | table access by index rowid | DM_CLASSIFY | 1 | 23 | 2 (0) | 00:00:01 |
| * 6 | index unique scan | IDX_DM_CLASSIFY_ID | 1 | 1 (0) | 00:00:01 |
| * 7 | table access by index rowid | DM_ASSET | 501 | 53106 | 10560 (1) | 00:02:07 |
| * 8 | domain index | IDX_DM_ASSET_SPL | 0 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter (ROWNUM <= 500)
2-access ("A". "BASE_VOLTAGE_ID" = "V". "BASE_VOLTAGE_ID" (+ ))
6-access ("C". "CLASSIFY_ID" = '000000 ')
7-filter ("A". "CLASSIFY_ID" = '000000 ')
8-access ("MDSYS". "SDO_RELATE" ("A". "G3E_GEOMETRY", "MDSYS". "SDO_GEOMETRY" (3003,4326, NULL ,"
SDO_ELEM_INFO_ARRAY "(113.93897922622, 22.806658666304, 1)," SDO_ORDINATE_ARRAY "(0,114.384
75977774, 22.806658666304, 0,114.38475977774, 22.42623522295, 0,113.93897922622, 22.42623522295, 0,
113.93897922622, 22.806658666304, 0), 'mask = ANYINTERACT ') = 'true ')
Statistics
----------------------------------------------------------
3965 recursive cballs
6006 db block gets
30388 consistent gets
3 physical reads
340 redo size
94355 bytes sent via SQL * Net to client
748 bytes encoded ed via SQL * Net from client
35 SQL * Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
500 rows processed
The problem arises again. After the index is deleted, the SQL statement is faster, but the performance of the statements that used to perform the index based on CLASSIFY_ID is incorrect. If newid is used in itpub, create a function index:
Create index IDX_ASSET_CLASSIFY_ID ON DM_ASSET (case when CLASSIFY_ID! = '000000' THEN CLASSIFY_ID else null end) nologging;
1. The preceding SQL statement is executed very quickly.
2. Modify the CLASSIFY_ID code.
If (CLASSIFY_ID = '20140901') {-- if this is the value, scan all
Select * from DM_ASSET where CLASSIFY_ID = '201312 ';
} Else {-- if this value is used, the index is used.
Select * from DM_ASSET where (case when CLASSIFY_ID! = '000000'
THEN CLASSIFY_ID else null end) = '201312'
}
