RE: Force implicit data conversion - REOPENED

From: Michael Rosenblum <mrosenblum_at_dulcian.com>
Date: Mon, 23 Mar 2015 17:35:45 +0000
Message-ID: <30fc2fb04512494e8b2b9efa2ddae573_at_Mail.dulcian.local>

Hi, all!

Decided to reopen this thread, because I’ve got a bit puzzled results today. Looks like Oracle SOMETIMES changes the datatype of a constant, and SOMETIMES changes the datatype of a column. Here is the example (environment: 11.2.0.3 64 bit EE on Windows Server 2008):

• Setup create table misha_bind as select * from all_objects where rownum < 10000; create index misha_bind_object_idx on misha_bind(object_id); create index misha_bind_created_idx on misha_bind(created);

begin

   dbms_stats.gather_table_stats(user,'MISHA_BIND'); end;

• Test #1 plus output of DBMS_XPLAN.DISPLAY_CURSOR declare v_tx varchar2(256):='50'; v_nr number; begin select /*+ gather_plan_statistics misha101 */ count(*) into v_nr from misha_bind where object_id = v_tx ; end;

  ---

  | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

  ---

  | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | |* 2 | INDEX RANGE SCAN| MISHA_BIND_OBJECT_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |

  ---

  Predicate Information (identified by operation id):

  ---

  2 - access("OBJECT_ID"=TO_NUMBER(:B1))
• Test #2 plus output of DBMS_XPLAN.DISPLAY_CURSOR declare v_nr number; v_dt timestamp := to_timestamp('12/9/2010 3:40:00','MM/DD/YYYY hh24:mi:ss'); begin select /*+ gather_plan_statistics misha110 */ count(*) into v_nr from misha_bind where created=v_dt; end;

  ---

| Id  | Operation             | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |


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


|   0 | SELECT STATEMENT      |                        |      1 |        |      1 |00:00:00.01 |      32 |
|   1 |  SORT AGGREGATE       |                        |      1 |      1 |      1 |00:00:00.01 |      32 |
|*  2 |   INDEX FAST FULL SCAN| MISHA_BIND_CREATED_IDX |      1 |    100 |     24 |00:00:00.01 |      32 |


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

Predicate Information (identified by operation id):

---

   2 - filter(INTERNAL_FUNCTION("CREATED")=:B1)

• Summary As you can see, in the first case I’ve got TO_NUMBER applied to the bind, while in the second case I have datatype transformation applied to the column. Strange, isn’t it? Any ideas/explanations?

Regards,
Michael Rosenblum
Dulcian Inc

P.s. If I change the datatype of V_DT back to DATE in Test #2, I get back my INDEX RANGE SCAN on MISHA_BIND_CREATED_IDX, so it doesn’t seem to be a problem of created indexes.
--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 23 2015 - 18:35:45 CET