I pointed out some time ago a few of the things in SQL Server that I would like to see in Oracle. Here’s a couple more:
Insert, update, and delete execution plans show the indexed access paths used to check the side effects of referential integrity constraints. (I haven’t checked to see what happens if you define a foreign key as ’on delete cascade’ so I don’t know if SQL Server follows all the way down through the delete tree – but I wouldn’t be surprised if it did.)
SQL Server also tells you about indexes that have to be modified even if your DML doesn’t require any referential integrity checks. Here’s one line extracted from a plan that demonstrates the point. I have a statement that is updating a column in the transactions table but, as you can see, two other objects are referenced at the same time – the two indexes that have to be updated:
|--Table Update(OBJECT:([testdata].[dbo].[transactions]), OBJECT:([testdata].[dbo].[transactions].[tx_err]), OBJECT:([testdata].[dbo].[transactions].[tx_double]), SET:([testdata].[dbo].[transactions].[error] = raiseIfNullUpdate([Expr1004])))
If nothing else, this will remind people that when they insert a row there may be a lot more work going on than just finding a block with a bit of free space somewhere in the table.
This one is more “nice to have” than “extremely useful”, and might not be worth the return on investment to the Oracle developers, but SQL Server has a lovely little trick for handling concurrent tablescans.
If a process starts to do a tablescan and a second process starts to scan the same table a little while later the second (and Nth) process will deliberately synchronise with the first process by starting its tablescan at the point that the first process has just reached – allowing both processes to take advantage of the same block reads at the same time.
When the processes get to the end of the table, the second process will then go back to the start of the table and read the blocks it originally skipped. The benefit is that you don’t have several processes doing physical multiblock reads in different parts of the table at the same time and causing unnecessary contention and queues for disks as they do so. (Of course, in many cases in Oracle, the second process may benefit from visiting some blocks that were buffered by the first process – so you often see the second process “catch up” the first process and then spend its time waiting on wait event “read by other session” (one of the “buffer busy waits” class of wait events in earlier versions of Oracle) – which has the same effect as SQL Server, but it’s not by design and it’s not guaranteed.
This little trick has earned the name “merry-go-round” tablescan, and you can understand why if you imagine several processes all starting to scan the same table one after the other – you could cause a continuous cyclic scan to take place as one process drops out and another takes over.
The reason why I rate this as “nice to have” is that (a) you wouldn’t normally expect to be doing many concurrent tablescans like this in Oracle and (b) if you were you’d probably be doing it with parallel queries in a data warehouse anyway – which might make the merry-go-round very hard to implement safely.