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.
SELECT COUNT(1) FROM TableB
(in this plan index 4 is being used)
cost is 63703 (horrible).
what should I be doing now.
Thanks in advance.