Oracle uses object statistics to estimate the amount of data that a query will probably return. This is used to find the best execution plan for the specific case. Having stale statistics is perhaps a major reason for bad execution plans. But sometimes the data changes so frequently that the generation of up to date statistics is not a feasible goal.Continue reading "Faking Oracle statistics for TIMESTAMP columns"
A co-worker tried to drop a user in an Oracle database and got a strange error:
ORA-00600: [ktssdrp_segment: NonNullDba]. Unfortunately My Oracle Support did not have any useful information on this error and so I started to find a solution for this myself. In the end the solution was easy but not obvious.
This article outlines my findings of using
ROWNUM with the
MERGE statement in Oracle.
The pseudo column
ROWNUM can be used to limit the result set to a specific number of rows. I have used this often when I had to update a lot of rows but needed to keep the size of the transaction fairly small. Systems using data replication like Materialized Views or Streams get easily out of sync when they have to cope with huge transactions.
MERGE statement can be used to reconcile two data sets. A new row is inserted if the key is not available in the target table and it gets updated if it already exists.
Have you ever tried to use both at the same time?Continue reading "No MERGE with ROWNUM"