what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?



  • I have a query as you can see below :

    select /*+gather_plan_statistics*/
     *
      from mi_dimcustomer t
     where t.CUSTOMER_NUM = 321937
    

    There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of the table. I used /*+gather_plan_statistics*/ hint and Dbms_xplan.display_cursor to get the real execution plan and that's what I've got :

    SQL_ID  adj0b6drg6bjd, child number 0
    -------------------------------------
    select /*+gather_plan_statistics*/* from vmi_dimcustomer t where 
    t.CUSTOMER_NUM = 321937
    

    Plan hash value: 3784660444


    | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |

    | 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 3 |
    | 1 | TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
    |* 2 | INDEX UNIQUE SCAN | IDX1_DIMCUSTOMER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |

    Predicate Information (identified by operation id):

    2 - access("CUSTOMER_NUM"=321937)

    My question is about Operation-2 , Index unique scan. As far as I know what happens in Index scan is just scanning / searching the Index Page , retrieving the desired Rowids and then using those Rowids to access the correct location in the table (Which is op-1) .I don't expect operation-2 (which is just scanning the index page) to generate any rows !So why do we see [A-rows]=1 in the execution plan for op-2? what does this number represent?

    My guess is that it would be equal to the number of ROWIDs returning from index page .

    Thanks in advance



  • Your guess is correct, it is the number of rowids that are returned from the index. This is the number of rows examined by the table access but not necessarily the number of rows returned by it (eg you have another filter which can only be applied after reading the rest of the row).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2