4 Replies Latest reply: Aug 13, 2014 10:17 AM by brunovroman RSS

    Index is not used while running the SQL query

    S_27

      All,

       

      i have two tables with a common key field. For example:

       

      Table A:

      -----------

      KeyField VARCHAR2(10),

      NAME VARCHAR2(50),

      ADDRESS VARCHAR2(100)

       

      KeyFieldNAMEADDRESS

       

      Table B:

      -----------

      KeyField VARCHAR2(10),

      FLAG VARCHAR2(1)

      KeyFieldFLAG

       

       

      Both of these tables have same amount of data (around 8 Million).

       

      On Table A, I have an regular index on KeyField.

      on Table B, I have an regular index on KeyField and a function based index on FLAG field ("FLAG", 1)

       

      I have an update statement which updates the FLAG field for 50K records on TABLE B to 1. All of the remaining fields will have null value for FLAG field.

      Then I have this insert statement which inserts TABLE A values into TABLE C based on TABLE B's FLAG field.

       

      ie,

       

      INSERT INTO TABLE_C

      (NAME, ADDRESS, KEYFIELD)

      VALUES

      (SELECT A.KEYFIELD,A.NAME,A.ADDRESS FROM TABLE_A A JOIN TABLE_B B ON A.KEYFIELD=B.KEYFIELD AND B.FLAG='1');

       

      Now, I have noticed two things:

      a) When I use above SQL query runs the function based index on FLAG field is not used and does a full table scan

      b) When I use above SQL query and modify the condition to B.FLAG=1 then the keyfield Index on TABLE A is not used and does full table scan of TABLE A

      c) I tried adding INDEX HINTS to use both indexes but it does a FULL on the keyfield index on TABLE A

       

      Any help here is much appreciated.

       

      Thanks,

      S