NO_INDEX hint and deprecated ROWID hint

Scope
-----
Many people asked how to specify ROWID hint for a SQL statement after Oracle 10g, and there is no answer by search Google.
The RowID access path is helpful when you do iterative update(query the rows, do some changes, and then update back).

Goal
----
Guide SQL access TABLE BY ROWID where there is a possible index access path.

Solution
--------

*) approach 1: NO_INDEX sql hint
*) approach 2: Suppress index scan by add expression/function to predicate column.

Result
------
After benchmark, we can see that SQL optimizer and SQL run time engine select the "TABLE ACCESS BY USER ROWID" access path by implement the our solutions.
The Logical reads(Buffers in the execution plan) reduced from 2 to 1.

Setup
-----

drop table yi01;
create table yi01
(myid number,
mycol varchar2(30),
constraint yi01_pk primary key(myid) using index
);

truncate table yi01;

insert into yi01(myid, mycol) values(1, Null);
insert into yi01(myid, mycol) values(2, Null);
insert into yi01(myid, mycol) values(3, Null);
insert into yi01(myid, mycol) values(4, 'a');
insert into yi01(myid, mycol) values(5, Null);
insert into yi01(myid, mycol) values(6, 'b');
insert into yi01(myid, mycol) values(7, Null);

insert into yi01(myid, mycol)
select rownum + 7, Null
from dual
connect by level <= 5010; 

commit;

exec dbms_stats.gather_table_stats(user,'YI01');

Benchmark
--------- 

SQL>

set linesize 120
set pagesize 0
set serveroutput off

COLUMN rid NEW_VALUE l_rid
COLUMN myid NEW_VALUE l_id

select rowid rid, myid from yi01
where rownum <= 1;  

*) index range/unique scan

update /*+ gather_plan_statistics index(a) */ YI01 a
set MYCOL = 'F'
WHERE rowid = '&l_rid' and MYID = &l_id;

SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

-----------------------------------------------------------------
| Id  | Operation         | Name    | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------
|   1 | UPDATE            |         |        |      1 |       2 |
|*  2 |  INDEX UNIQUE SCAN| YI01_PK |      1 |      1 |       2 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MYID"=1165)
       filter(ROWID='AAAYH+AAEAAAAhoASM')

*) approach 1: NO_INDEX sql hint

update /*+ gather_plan_statistics no_index(a) */ YI01 a
set MYCOL = 'F'
WHERE rowid = 'AAA5znAG2AAAPm0AAF' and MYID = 7;

SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| Id  | Operation                  | Name | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
|   1 | UPDATE                     |      |        |      1 |       1 |
-----------------------------------------------------------------------
|*  2 |  TABLE ACCESS BY USER ROWID| YI01 |      1 |      1 |       1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("MYID"=:SYS_B_2)

rollback;

*) approach 2: Surpress index scan by add expression/function to predicate column.

update /*+ gather_plan_statistics */ YI01 a
set MYCOL = 'F'
WHERE rowid = '&l_rid' and MYID + 0 = &l_id;

SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

-----------------------------------------------------------------------
| Id  | Operation                  | Name | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
|   1 | UPDATE                     |      |        |      1 |       1 |
|*  2 |  TABLE ACCESS BY USER ROWID| YI01 |      1 |      1 |       1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("MYID"+0=1954)

Reference
---------

*) To get the current SQL execution plan in shared_pool.

Oracle 11g
SELECT * FROM table(dbms_xplan.display_cursor);

Oracle 10.1
SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

-- 10gR1
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last'));

-- 11g
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'iostats memstats last partition'));