I have one big table A who has PK (C1, C2, C3) and many other columns, to make the select faster, a smaller table B was created with PK (C1, C2). So we can do a select by joining the two tables to find a row in A.
But the problem now is that it can happen that if data is corrupted in B which results in a joint select returns nothing but we still have a row in A.
Am I doing something wrong with this design and how can I ensure the data in those two tables are consistent?
Thanks a lot.
Standard way - if those tables are in a master-detail relationship - is to create a foreign key constraint which will prevent deleting master if details exist.
If you can fix it now, do it - then create the constraint.
If you can't, then create foreign key constraint using INITIALLY DEFERRED DEFERRABLE option so that current values aren't checked, but future DML will be.
Finally, to fetch data although certain rows don't exist any more, use outer join.
"Am I doing something wrong with this design"
Well it's hard to be sure without more details about your scenario but probably you just needed a non-unique index on A(C1, C2).
Although I would like to see some benchmarking which proves an index-range scan on your primary key index was not up to the job. Especially as it seems likely the join on table B is using that access path.
Performance tuning an Oracle database is a matter of understanding and juggling many variables. It's not just a case of "bung on another index". We need to understand what the database is actually doing and why the optimiser made that choice. So, please read this post on asking Oracle tuning questions which will give you some insight into how to approach query optimisation.
