Oracle DBMS_XPLAN.DISPLAY_CURSOR轮廓中的BITMAP_TREE提示

你好，

我有一个查询，不知何故 (使用no_index) 我设法让CBO产生下面的计划。

它适合我的需求，但是我很难理解大纲中的提示 (请参见bolted)。

Plan hash value: 1166215027

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |       |     8 (100)|          |      1 |00:00:00.01 |      26 |       |       |          |
|   1 |  NESTED LOOPS                            |              |      1 |      1 |   528 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |      26 |       |       |          |
|   2 |   NESTED LOOPS                           |              |      1 |   1431K|   528 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |      25 |       |       |          |
|   3 |    MERGE JOIN CARTESIAN                  |              |      1 |      1 |   501 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |      22 |       |       |          |
|   4 |     VIEW                                 |              |      1 |      1 |   122 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID         | G_PIECE      |      1 |      1 |    15 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|*  6 |       INDEX RANGE SCAN                   | PIE_REFPIECE |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|   7 |     BUFFER SORT                          |              |      1 |      1 |   379 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |      17 |  2048 |  2048 | 2048  (0)|
|   8 |      VIEW                                |              |      1 |      1 |   379 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |      17 |       |       |          |
|*  9 |       TABLE ACCESS BY INDEX ROWID BATCHED| G_PIECE      |      1 |      1 |    43 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |      17 |       |       |          |
|* 10 |        INDEX RANGE SCAN                  | G_PIECE$ADR3 |      1 |     70 |       |     1   (0)| 00:00:01 |     12 |00:00:00.01 |       4 |       |       |          |
|  11 |    BITMAP CONVERSION TO ROWIDS           |              |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |       |       |          |
|  12 |     BITMAP OR                            |              |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |       |       |          |
|  13 |      BITMAP CONVERSION FROM ROWIDS       |              |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |       |       |          |
|* 14 |       INDEX RANGE SCAN                   | PIE_REFPIECE |      1 |   1431K|       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|  15 |      BITMAP CONVERSION FROM ROWIDS       |              |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |       |       |          |
|* 16 |       INDEX RANGE SCAN                   | PIE_REFPIECE |      1 |   1431K|       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 17 |   TABLE ACCESS BY INDEX ROWID            | G_PIECE      |      1 |      1 |    27 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$3 / KR@SEL$1
   5 - SEL$3 / G_PIECE@SEL$3
   6 - SEL$3 / G_PIECE@SEL$3
   8 - SEL$2 / REQPARENT@SEL$1
   9 - SEL$2 / REQPARENT@SEL$2
  10 - SEL$2 / REQPARENT@SEL$2
  17 - SEL$1 / PARENTPARAM@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_optimizer_unnest_scalar_sq' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)
      OPT_PARAM('_optimizer_adaptive_plans' 'false')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 1)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "KR"@"SEL$1")
      NO_ACCESS(@"SEL$1" "REQPARENT"@"SEL$1")
      BITMAP_TREE(@"SEL$1" "PARENTPARAM"@"SEL$1" OR(1 1 ("G_PIECE"."REFPIECE") 2 ("G_PIECE"."REFPIECE"))) 
      LEADING(@"SEL$1" "KR"@"SEL$1" "REQPARENT"@"SEL$1" "PARENTPARAM"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SEL$1" "REQPARENT"@"SEL$1")
      USE_NL(@"SEL$1" "PARENTPARAM"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "PARENTPARAM"@"SEL$1")
      INDEX_RS_ASC(@"SEL$2" "REQPARENT"@"SEL$2" ("G_PIECE"."GPIADR3"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "REQPARENT"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "G_PIECE"@"SEL$3" ("G_PIECE"."REFPIECE"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=871): 'A6090VIS'
   2 - :2 (VARCHAR2(30), CSID=871): 'A601E2Y9'
   3 - (VARCHAR2(30), CSID=871): 'INT00000'
   4 - (VARCHAR2(30), CSID=871): 'A71T0VFB'

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

   6 - access("REFPIECE"=:B4)
   9 - filter(("REQPARENT"."GPIDEPOT"=:B2 AND "REQPARENT"."TYPPIECE"='REQUEST_LIMITE' AND "REQPARENT"."GPITYPTRIB"=:B3 AND "REQPARENT"."TYPEDOC"='C' AND
              "REQPARENT"."FG05"='O'))
  10 - access("REQPARENT"."GPIADR3"=:B1)
  14 - access("PARENTPARAM"."REFPIECE"="REQPARENT"."A")
  16 - access("PARENTPARAM"."REFPIECE"="REQPARENT"."B")
  17 - filter(("PARENTPARAM"."GPIDEPOT" IS NOT NULL AND "PARENTPARAM"."GPIDEPOT"="KR"."GPIDEPOT" AND "PARENTPARAM"."TYPPIECE"='PARAM_LIMITE'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PARENTPARAM"."REFPIECE"[VARCHAR2,24]
   2 - "KR"."GPIDEPOT"[VARCHAR2,240], "PARENTPARAM".ROWID[ROWID,10]
   3 - "KR"."GPIDEPOT"[VARCHAR2,240], "REQPARENT"."A"[VARCHAR2,150], "REQPARENT"."B"[VARCHAR2,600]
   4 - "KR"."GPIDEPOT"[VARCHAR2,240]
   5 - "GPIDEPOT"[VARCHAR2,240]
   6 - "G_PIECE".ROWID[ROWID,10]
   7 - (#keys=0) "REQPARENT"."A"[VARCHAR2,150], "REQPARENT"."B"[VARCHAR2,600]
   8 - "REQPARENT"."A"[VARCHAR2,150], "REQPARENT"."B"[VARCHAR2,600]
   9 - "LIBELLE_20_12"[VARCHAR2,150], "ST17"[VARCHAR2,600]
  10 - "REQPARENT".ROWID[ROWID,10]
  11 - "PARENTPARAM".ROWID[ROWID,10]
  12 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  14 - "PARENTPARAM".ROWID[ROWID,10]
  15 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  16 - "PARENTPARAM".ROWID[ROWID,10]
  17 - "PARENTPARAM"."REFPIECE"[VARCHAR2,24]
复制

所以这个提示: BITMAP_TREE(@ "SEL $1" "PARENTPARAM" @ "SEL $1" 或 (11 (“G_PIECE”。“REFPIECE”) 2 (“G_PIECE”。“REFPIECE”))

我不明白的是带有 (1... 我可以完全理解1 (“G_PIECE”。“REFPIECE”) 2 (“G_PIECE”。“REFPIECE”) 的部分，但是第一个数字1代表什么？

我进行了10053优化器跟踪，并将其更改为BITMAP_TREE(@ “SEL $1” “PARENTPARAM” @ “SEL $1” 或 (21 (“G_PIECE”。“REFPIECE”) 2 (“G_PIECE”。“REFPIECE”))

atom_hint显示了这种 “版本” 的提示存在，即使它使用了它，但什么也没有发生，所以我的问题是数字的第一个位置代表什么？


谢谢

BITMAP_TREE提示中列出的前1 (11 (“G_PIECE”。“REFPIECE”) 2 (“G_PIECE”。“REFPIECE”))，表示此提示相关的或分支编号。

应用所有查询转换后，我们对OR谓词的不同分支进行编号。

在提示中包括相关或分支编号，以确保提示每次都会重现相同的计划。

但是，此数字与查询中指定的OR谓词的分支之间没有关联，因为查询转换可能已经移动了它们，甚至添加了一些。

在这种情况下，BITMAP_TREE提示说，对于第一个或分支，使用具有列g_piece.refpeciteindex spec的索引和具有列g_piece.refpeciteindex spec的索引。(注意概述提示通常不使用索引名称。它们通常使用索引规范，该规范引用索引中使用的列。)