23 March, 2014

Plan HASH_VALUE remains the same for the same Execution Plan, even if ROWS and COST change

Here is a simple demo that shows that the Plan Hash_Value does not consider the ROWS and COST but only the Execution Plan.  Thus, even with more rows added into a table, if the Execution Plan remains the same for a query, it is independent of the number of ROWS and the COST changing.

SQL> -- create the table
SQL> create table branch_list
  2  (country_code varchar2(3), branch_code number, branch_city varchar2(50));

Table created.

SQL> 
SQL> -- create an index
SQL> create index branch_list_cntry_ndx
  2  on branch_list(country_code);

Index created.

SQL> 
SQL> 
SQL> 
SQL> -- populate it with 100 rows, one third being 'IN'
SQL> insert into branch_list
  2  select decode(mod(rownum,3),0,'IN',1,'US',2,'US'), rownum, dbms_random.string('X',32)
  3  from dual
  4  connect by level < 101
  5  /

100 rows created.

SQL> 
SQL> -- gather statistics
SQL> exec dbms_stats.gather_table_stats('','BRANCH_LIST');

PL/SQL procedure successfully completed.

SQL> 
SQL> -- get an execution plan
SQL> explain plan for
  2  select branch_code, branch_city
  3  from branch_list
  4  where country_code = 'IN'
  5  /

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 513528032

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |    50 |  1950 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BRANCH_LIST           |    50 |  1950 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BRANCH_LIST_CNTRY_NDX |    50 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("COUNTRY_CODE"='IN')

14 rows selected.

SQL> 
SQL> 
SQL> -- add another 400 rows, none of them being 'IN'
SQL> insert into branch_list
  2  select decode(mod(rownum,6),0,'SG',1,'US',2,'US',3,'US',4,'AU',5,'UK'), rownum+100, dbms_random.string('X',32)
  3  from dual
  4  connect by level < 401
  5  /

400 rows created.

SQL> 
SQL> -- update statistics
SQL> exec dbms_stats.gather_table_stats('','BRANCH_LIST');

PL/SQL procedure successfully completed.

SQL> 
SQL> -- get the execution plan again
SQL> explain plan for
  2  select branch_code, branch_city
  3  from branch_list
  4  where country_code = 'IN'
  5  /

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 513528032

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |    33 |  1320 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BRANCH_LIST           |    33 |  1320 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BRANCH_LIST_CNTRY_NDX |    33 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("COUNTRY_CODE"='IN')

14 rows selected.

SQL> 
SQL> select column_name, histogram
  2  from user_tab_columns
  3  where table_name = 'BRANCH_LIST';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
COUNTRY_CODE                   FREQUENCY
BRANCH_CODE                    NONE
BRANCH_CITY                    NONE

SQL> select count(*) from user_tab_histograms
  2  where table_name = 'BRANCH_LIST'
  3  and column_name = 'COUNTRY_CODE'
  4  /

  COUNT(*)
----------
         5

SQL> 


After the addition of 400 rows to a 100 row table, the distribution of rows has changed. At the second Gather_Table_Stats call, Oracle has properly omputed a Frequency Histogram on the COUNTRY_CODE column for the 5 countries ('IN','US','SG','AU','UK').  The estimate for the COUNTRY_CODE='IN' is now more accurate.

However, what I want to demonstrate here is that although "ROWS" (and "BYTES for that many ROWS) and "COST" have changed in the new Execution Plan, the PLAN HASH VALUE ("513528032") remains the same.  Thus, the PLAN HASH VALUE is independent of changes to the ROWS/BYTES and COST.  The Execution Plan, per se, hasn't changed.
.
.
.



1 comment:

Sayan Malakshinov said...

Great explanation By Randolf Geist: http://oracle-randolf.blogspot.ru/2009/07/planhashvalue-how-equal-and-stable-are.html