0 Replies Latest reply: Nov 11, 2013 12:05 PM by 743f20bd-d2f7-4835-807e-d77e3b3f1588 RSS

    Similar table showing bad performance?

    743f20bd-d2f7-4835-807e-d77e3b3f1588

      Hi there,

      So I have a big table TableA for about 75 million rows with 4 indexes on it.

      1. one is primary key on 1 column

      2. Unique index on 3 Columns

      3. non-unique index on 1 column

      4. non-unique index on 2 columns.

       

      when i run explain plan for :

      SELECT COUNT(1) FROM TableA

      WHERE Col =1 ;

       

      (Col belongs to index 2 and 4 and in this plan index 2 is being used)

       

      cost is 4 (beautiful).

       

      I created TableB as select * from TableA. and same indexes and Stats are fresh too.

       

      when i run explain plan for :

      SELECT COUNT(1) FROM TableB

      WHERE Col =1 ;

       

      (in this plan index 4 is being used)

       

      cost is 63703 (horrible).

       

      what should I be doing now.

      Thanks in advance.