运行时执行计划与explain plan(三)

第一部分：http://space.itpub.net/16689238/viewspace-551803

第二部分：http://space.itpub.net/16689238/viewspace-551805

Explain plan doesn't see your datatype...

The last bit about explain plan I'll look at is the fact that explain plan doesn't see your bind datatype. It presumes all binds are varchar2's regardless of how the developer is binding.  Consider:

ops$tkyte%ORA10GR2> create table t

  2  ( x varchar2(10) primary key,

  3    y date

  4  );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1, sysdate );

1 row created.

ops$tkyte%ORA10GR2> variable x number

ops$tkyte%ORA10GR2> exec :x := 1

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA10GR2> select * from t where x = :x;

X          Y

---------- ---------

1          06-APR-07

ops$tkyte%ORA10GR2> alter session set sql_trace=false;

Session altered.

So, we have a table with a varchar2 datatype for the primary key - but we only stuff numbers in there.  End users and developers know it is always a number and then presume the type is a number (makes sense) - but someone used the wrong datatype (just in case maybe....).  When we look at the TKPROF we'll see the explain plan mismatch:

select * from t where x = :x

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          1          0           0

Fetch        2      0.00       0.00          0          7          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.00       0.00          0          8          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 120  (OPS$TKYTE)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS FULL T (cr=7 pr=0 pw=0 time=76 us)

 

Rows     Execution Plan

-------  ---------------------------------------------------

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE)

      0    INDEX (UNIQUE SCAN) OF 'SYS_C0013586' (INDEX (UNIQUE))

Explain plan - the 'execution plan' shows an index unique scan, but reality (the row source operation) shows we full scanned.  DBMS_XPLAN (autotrace in 10gr2 uses these new package introduced in 9ir2, you can use it directly if you like) shows us why we are full scanning:

ops$tkyte%ORA10GR2> select * from t where x = to_number(:x);

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    16 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    16 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_NUMBER("X")=TO_NUMBER(:X))

Note

-----

   - dynamic sampling used for this statement

So, when I told explain plan "hey, we have a NUMBER here" using to_number(), we can see what happened. In the predicate information, we see that when you compare a number to a string, Oracle will TO_NUMBER(the string). That of course makes using the index on the string not possible!

So, this example shows two things. Firstly, that explain plan assumes varchar2 (so use to_number or literals!! and to_date to get the right type conveyed to explain plan).  Secondly, that implicit conversions are evil and should always be avoided.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16689238/viewspace-551806/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16689238/viewspace-551806/