1

First som basics.

Java 6 OJDBC6 Oracle 10.2.0.4 (also the same result in 11g version)

I am experiencing that a sql statement is behaving differently when executed from Java with the OJDBC6 client and using the tool SQL Gate that probably uses a native/OCI driver. For som reason the optimizer chooses to use hash join for the executed statement in Java but not for the other.

Here is the table:

CREATE TABLE DPOWNERA.XXX_CHIP (
   xxxCH_ID        NUMBER(22)       NOT NULL, 
   xxxCHP_ID       NUMBER(22)       NOT NULL, 
   xxxSP_ID        NUMBER(22)           NULL, 
   xxxCU_ID        NUMBER(22)           NULL, 
   xxxFT_ID        NUMBER(22)           NULL, 
   UEMTE_ID        NUMBER(38)           NULL, 
   xxxCH_CHIPID    VARCHAR2(30)     NOT NULL
)

The index:

ALTER TABLE DPOWNERA.XXX_CHIP ADD
  (
     CONSTRAINT IX_AK1_XXX_CHIPV2
     UNIQUE ( XXXCH_CHIPID )
     USING INDEX
     TABLESPACE DP_DATA01 
     PCTFREE 10
     INITRANS 2
     MAXTRANS 255
     STORAGE (
        INITIAL 128 K
        NEXT 128 K
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
    )
);

Here is the SQL i used:

SELECT *
    FROM   (SELECT m2.*,
            rownum rnum
            FROM   (SELECT m_chip.xxxch_id,
                      m_chip.xxxch_chipid
                      FROM   xxx_chip m_chip
                      ORDER  BY m_chip.xxxch_chipid) m2
            WHERE  rownum < 101)
WHERE  rnum >= 1;

And finally excerpts from the explain plan:

SQL Tool Query:

OPERATION        OBJECT_NAME         COST  CARDINALITY CPU_COST
---------------- ------------------- ----- ----------- ----------
SELECT STATEMENT NULL                    2          10      11740
VIEW             NULL                    2          10      11740
COUNT            NULL                 NULL        NULL       NULL
VIEW             NULL                    2          10      11740
NESTED LOOPS     NULL                    2          10      11740
TABLE ACCESS     XXX_CHIP                1     1000000       3319
INDEX            IX_AK1_XXX_CHIPV2       1          10       2336
TABLE ACCESS     XXX_CUSTOMER            1           1        842
INDEX            IX_PK_XXX_CUSTOMER      1           1        105

QQL Java Query OJDBC Thin client:

**OPERATION        OBJECT_NAME         COST  CARDINALITY CPU_COST**
SELECT STATEMENT NULL                15100         100 1538329415
VIEW             NULL                15100         100 1538329415
COUNT            NULL                 NULL        NULL       NULL
VIEW             NULL                15100     1000000 1538329415
SORT             NULL                15100     1000000 1538329415
HASH JOIN        NULL                 1639     1000000  424719850
VIEW             index$_join$_004        3           3    2268646
HASH JOIN        NULL                 NULL        NULL       NULL
INDEX            IX_AK1_XXX_CUSTOMER     1           3        965
INDEX            IX_PK_XXX_CUSTOMER      1           3        965
TABLE ACCESS     xxx_CHIP             1614     1000000  320184788

So, i am lost to why the hash join is chosen by the optimizer? My guess is that the varchar2 is treated differently.

jdsthlm
  • 51
  • 5
  • @jdssthlm - Why do the query plans appear to show the query hitting the `XXX_CUSTOMER` table (and/ or what appear to be indexes on that table) when the query doesn't reference that table? Are you sure the query you posted matches the plans you posted? Is your Java code using bind variables somewhere to pick a particular key value? Are you sure that the Java code is passing the correct data type when it does the bind? – Justin Cave Nov 18 '11 at 15:33
  • Are you using a PreparedStatement in Java? –  Nov 18 '11 at 16:11

1 Answers1

2

I found an answer and it was simpler than i thought. It all has to do with the VARCHAR2 datatype of the index column. My database was set to language and country "en", "US" but locally i have another language and region. Therfore the optimizer rightly discarded the index since it wasn't configured with the same language and country as the client.

So what i did to test it was to start my eclipse with some extra -D parameters entered in my eclipse.ini file. 

-Duser.language=en
-Duser.country=US
-Duser.region=US

Then in the data source explorer in Eclipse i had created a connection and ran my statement and it worked like a charm.

So lesson learned is to always see to that the client and database are compatible language wise. Probably we will change so we use UTF-8 in the database so it is the same for every installation. Otherwise you will have to configure it for every installation depending on country and language.

Hope this will help someone. If the answer was unclear please post a comment.

jdsthlm
  • 51
  • 5