dbms_xplan: unexpected expected number of rows (E-Rows)

  • From: Eric Valk <listeric1@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 30 Mar 2009 13:51:11 +0200

Hello List,

Without showing the SQL statement, could someone please
explain the, for me, unexpected number of expected rows (E-Rows)
in line 5.
Note: all indexes are supporting primary key constraints (thus
the columns involved are NOT NULL).

| Id  | Operation                     | Name                | Starts |
E-Rows | A-Rows |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |      1
|        |      1 |
|   1 |  NESTED LOOPS                 |                     |      1 |
1 |      1 |
|   2 |   NESTED LOOPS                |                     |      1 |
1 |      1 |
|   3 |    TABLE ACCESS BY INDEX ROWID| HAN_T_INKOOPDEAL    |      1 |
1 |      1 |
|*  4 |     INDEX UNIQUE SCAN         | HAN_T_INKOOPDEAL_PK |      1 |
1 |      1 |
|   5 |    TABLE ACCESS BY INDEX ROWID| HAN_T_AANVRAAG      |      1 |
4718K|      1 |
|*  6 |     INDEX UNIQUE SCAN         | HAN_T_AANVRAAG_PK   |      1 |
1 |      1 |
|   7 |   TABLE ACCESS BY INDEX ROWID | REL_T_FACTEENH      |      1 |
683 |      1 |
|*  8 |    INDEX UNIQUE SCAN          | REL_T_FACTEENH_PK   |      1 |
1 |      1 |

The numbers 4718K and 683 correspond with the number of rows in the tables.
HAN_T_INKOOPDEAL has 225k+ rows.

Why would display_cursor show the number of rows in the table instead of 1
(what I would
expect). Is this expected behaviour and if it is could you point me to a
document
(preferably by Oracle) that explains this?

Regards
Eric

Other related posts:

  • » dbms_xplan: unexpected expected number of rows (E-Rows) - Eric Valk