While using hash partition i am facing the problem that the data is not going into the proper partition table . Due to this, records are not visible in the main table when I am searching for a particular record, as it searches only that partition. Any suggestions on how to overcome this problem and why this happened.
For example, in my case, explain is searching for the record in the 10th child table, but the record is actually placed in the 5th child table. When I delete this record and insert it again, it goes to the 10th child table. I want to know how the records are shuffled.
And in the future, if i truncate the records from the main table and insert them back again, will the shuffling of records happen or not. Any ideas regarding why this happened?
Moreover, i have not changed anything from my side. Just add up the indexing.