How to relate Execution Plan and SQL Query

Question

Here is the query

UPDATE hrs.rns_recon_ho c SET c.refr_numb = 
(
SELECT seqn_numb FROM hrs.rns_recon_ho p WHERE p.narr_1 = c.narr_1 
AND p.seqn_numb = p.refr_numb AND p.prod_code = 0
)
Where c.prod_code = 0 And c.refr_numb = 0 
AND c.narr_1 = '3/13/201211013198693442091'

And here is its execution plan

Which part of the query is causing TABLE ACCESS(FULL)?

Edit

My mistake, i should have been more clearer. Let me clear myself now that i need to know how to relate parts of execution plan with parts of the query. For example

1. Which part of query is causing TABLE ACCESS(FULL)
2. Which part of query is causing TABLE ACCESS(BY GLOBAL INDEX ROWID)

However, it is clear that INDEX(...)...PROD_CODE is caused by PROD_CODE field.

Is there any rule of thumb, guidelines or just tricks to identify? Or, we can not know because the optimizer won't tell us why a certain path was picked.

Answer 1

Maybe force a Full Table Scan by hint on one table, e.g.

UPDATE hrs.rns_recon_ho c SET c.refr_numb =
(
SELECT /*+ FULL(p) */ seqn_numb FROM hrs.rns_recon_ho p WHERE p.narr_1 = c.narr_1
AND p.seqn_numb = p.refr_numb AND p.prod_code = 0
)
Where c.prod_code = 0 And c.refr_numb = 0
AND c.narr_1 = '3/13/201211013198693442091'

or

UPDATE /*+ FULL(c) */ hrs.rns_recon_ho c SET c.refr_numb =
(
SELECT seqn_numb FROM hrs.rns_recon_ho p WHERE p.narr_1 = c.narr_1
AND p.seqn_numb = p.refr_numb AND p.prod_code = 0
)
Where c.prod_code = 0 And c.refr_numb = 0
AND c.narr_1 = '3/13/201211013198693442091'

In case your execution plan shows two "Full-Scan" you know it was originally caused by the other one.

Answer 2

Let me recreate:

CREATE TABLE RNS_RECON_HO ( PROD_CODE NUMBER,
            REFR_NUMB NUMBER,
            NARR_1 VARCHAR2 ( 25 ),
            SEQN_NUMB NUMBER primary key    );

CREATE INDEX TESTTT ON RNS_RECON_HO (PROD_CODE);
CREATE INDEX TESTTT1 ON RNS_RECON_HO (REFR_NUMB);
CREATE INDEX TESTTT2 ON RNS_RECON_HO (NARR_1);

Now reproducing your plan:

SET AUTOTRACE ON

UPDATE
      RNS_RECON_HO C
SET
      C.REFR_NUMB    =
          ( SELECT
                 SEQN_NUMB
           FROM
                RNS_RECON_HO P
           WHERE
                    P.NARR_1 = C.NARR_1
                AND P.SEQN_NUMB = P.REFR_NUMB
                AND P.PROD_CODE = PROD_CODE )
WHERE
         C.PROD_CODE = 0
      AND C.REFR_NUMB = 0
      AND C.NARR_1 = '3/13/201211013198693442091';


0 rows updated.

Execution Plan
----------------------------------------------------------
   0       UPDATE STATEMENT Optimizer Mode=ALL_ROWS (Cost=2 Card=1 Bytes=78)
   1    0    UPDATE RNS_RECON_HO
   2    1      TABLE ACCESS FULL RNS_RECON_HO (Cost=2 Card=1 Bytes=78)
   3    1      TABLE ACCESS BY INDEX ROWID RNS_RECON_HO (Cost=5 Card=1 Bytes=91)
   4    3        INDEX RANGE SCAN TESTTT (Cost=1 Card=1)

Statistics
----------------------------------------------------------
        190  recursive calls
          0  spare statistic 3
          0  gcs messages sent
         25  db block gets from cache
          0  physical reads direct (lob)
          0  queue position update
          0  queue single row
          0  queue ocp pages
          0  HSC OLTP Compressed Blocks
          0  HSC IDL Compressed Blocks
          0  rows processed

Reason:

The optimizer will only choose to use an index if it is cheaper (fewer reads) than a table scan. This usually means WHERE clause criteria need to map to the leading (i.e. leftmost) columns of an index. So adding an index (PROD_CODE, REFR_NUMB, NARR_1) on your where clause columns will avoid this.

To confirm check this one

CREATE INDEX TESTTT3 ON RNS_RECON_HO (PROD_CODE, REFR_NUMB, NARR_1);

SET AUTOTRACE ON

UPDATE
      RNS_RECON_HO C
SET
      C.REFR_NUMB    =
          ( SELECT
                 SEQN_NUMB
           FROM
                RNS_RECON_HO P
           WHERE
                    P.NARR_1 = C.NARR_1
                AND P.SEQN_NUMB = P.REFR_NUMB
                AND P.PROD_CODE = PROD_CODE )
WHERE
         C.PROD_CODE = 0
      AND C.REFR_NUMB = 0
      AND C.NARR_1 = '3/13/201211013198693442091';

0 rows updated.

Execution Plan
----------------------------------------------------------
   0       UPDATE STATEMENT Optimizer Mode=ALL_ROWS (Cost=1 Card=1 Bytes=78)
   1    0    UPDATE RNS_RECON_HO
   2    1      INDEX RANGE SCAN TESTTT3 (Cost=1 Card=1 Bytes=78)
   3    1      TABLE ACCESS BY INDEX ROWID RNS_RECON_HO (Cost=27 Card=1 Bytes=91)
   4    3        INDEX FULL SCAN TESTTT3 (Cost=26 Card=1)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  spare statistic 3
          0  gcs messages sent
          0  db block gets from cache
          0  physical reads direct (lob)
          0  queue position update
          0  queue single row
          0  queue ocp pages
          0  HSC OLTP Compressed Blocks
          0  HSC IDL Compressed Blocks
          0  rows processed

But this is just to change the plan, but never promises any performance improvements, with the limited inputs you gave.

FOLLOW UP:

The outer query is going on FTS since it ignores the index. So when we place a composite key, the index is straight forward for the optimizer and the same is used. Sub query is using index since it is joined with the outer query on the indexed column.

How to identify exact plan cannot be achieved 100% though you can be near to possible execution plan by following then core rules.