Skip to Main Content
  • Questions

  • BITMAP_TREE hint in OUTLINE of DBMS_XPLAN.DISPLAY_CURSOR

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Georgi.

Asked: August 22, 2017 - 9:30 am UTC

Last updated: August 28, 2017 - 9:56 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello,

I have a query, somehow (using no_index) I managed to make CBO produce the plan below.

It suits my needs however I struggle to understand the hint in the outline (see 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")
      <b>BITMAP_TREE(@"SEL$1" "PARENTPARAM"@"SEL$1" OR(1 1 ("G_PIECE"."REFPIECE") 2 ("G_PIECE"."REFPIECE")))</b> 
      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]


So this hint: BITMAP_TREE(@"SEL$1" "PARENTPARAM"@"SEL$1" OR(1 1 ("G_PIECE"."REFPIECE") 2 ("G_PIECE"."REFPIECE")))

What I can't understand is the part with (1 ... I can perfectly understand 1 ("G_PIECE"."REFPIECE") 2 ("G_PIECE"."REFPIECE") but what does the first number 1 stand for ?

I conducted a 10053 optimizer trace and changed this to BITMAP_TREE(@"SEL$1" "PARENTPARAM"@"SEL$1" OR(2 1 ("G_PIECE"."REFPIECE") 2 ("G_PIECE"."REFPIECE")))

and atom_hint showed such 'version' of the hint exists even it used it but nothing happened, so my question is what does this first position of a number stand for ?


Thank you

and we said...

The first 1 listed in the BITMAP_TREE hint (1 1 ("G_PIECE"."REFPIECE") 2 ("G_PIECE"."REFPIECE")), indicates the OR branch number this hint pertains to.

We number the different branches of an OR predicate(s) after we apply all query transformations.

Including the relevant OR branch number in the hint ensure the hint will reproduce the same plan each time.

HOWEVER, there is no correlation between this number and the branches of the OR predicate(s) specified in the query, as it’s possible that the query transformations may have moved them around or even added some.

In this case the BITMAP_TREE hint says, for the first OR branch use the index with the index spec of column G_PIECE.REFPIECE and the index with the index spec of column G_PIECE.REFPIECE. (Note outlines hints don't usually use index names. They typically use an index spec which references the column(s) used in the index.)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question answered nothing more to say.

Georgi Daskalov, October 11, 2017 - 8:01 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.