No need and no such thing as "dynamic index", all indexes are dynamic by nature, they are updated when you have information that affects you.
What you need to do is change the table, the way you described it, there is no miracle, there is a law of physics that prevents things from changing on their own. You could even have some resource in the database that you would do alone, but the updates in the table would occur in the same way, and precisely because there would be no real gain and would provoke an illusion, it is good that there is something like this. If your problem is changing the data you will have to change the data.
Why is it impossible to change thousands of lines? It says you have a table with 10,000 lines, this is one of the smallest tables I’ve ever seen, especially if you only have those 3 columns. Even to find the data will do almost the same thing you have to do to change this data, of course writing costs more, but it is not the end of the world.
Michel Simões' answer gives the SQL that you would probably already do and changes thousands of lines, IE, it does not really answer what you asked.
Without understanding the exact problem nor can we offer an alternative solution, we do not even know why there is a column index and a posicao, maybe I shouldn’t even have both, yes, to have one id stable, but we don’t know.
If you can rephrase this could have a column posicao and, I don’t know, a column desempate, then you don’t touch posicao and by default the desempate always enters as value 0, And if you enter one right after it 1, and if there’s another one after that posicao remains the same and the desempate enters as 2, and so on.
Of course, if you have too much of that, ultimately, you could end up having them all posicao equal and the desempate turn what was the posicao, but then you can have a routine that normalizes that and when you spin it it will redo only once renumbering all posicao and zeroing out the desempate, thus resolves.
If you have to enter in the middle of this tie-breaker list you will have to change the values of desempate, but, if it doesn’t happen what I said in the previous paragraph, it will be a much smaller amount of lines to change and the normalization of positions that I quoted will make it not necessary, it is one of the most famous techniques of computing, the divide to conquer.
But this is too complicated and would only be interesting if this insertion occurs several times a second, which I doubt. Make it simple, I doubt there’s a real problem there, there’s just something you don’t want to do because you think it’s bad, without any foundation. Otherwise the question does not give enough information to help you better. If you do not know how to do it right it will be worse than the update pure and simple.
If you have a real performance problem I would do a series of alternative tests to solve, has a plethora of possibilities, but for that you need to have two characteristics: understand very well the functioning of a database and general aspects of computing, including mathematical aspects, something that few people want to study; and at the same time understand very well the problem in detail. I don’t know about one of them (and if you knew it would take hours or days to come up with a result, which may even be that the best way is what you’re already doing) and you don’t know the other, so it’s hard to find a solution.
Take a look at Elastic Search https://www.elastic.co/pt/products/elastic-stack
– Diogo Santos Almeida
"how could I make sure that each record knew its proper position without having to update each one individually, "for it is, how could he know his proper position without you informing? See your example, why the record has to be in position 3 and not in the seventh (if it is a simple classification by name) or tenth (if it is the last one inserted or the one sorted by the file number)?
– anonimo
Have any answers solved what was in doubt? Do you need something else to be improved? Do you think it is possible to accept it now?
– Maniero