"Oddities" might be overstating it, but I remember a number of times a light went off and I though "oh, so _that's_ why Oracle does X". It was the same kind of information you might deduce from looking at a "query plan" from `EXPLAIN` or similar, and the kind of thing you might deduce from a careful review of topics like transaction isolation levels.
It's been a very long time so I'm not sure I can name many specific examples, but IIRC some of the topics that became very clear were things like:
* the importance of the order in which JOINs and WHEREs are applied (to limit the number of rows being accessed per step)
* the relationship between columns that are selected and those that appear in WHERE and ORDER BY clauses.
* the value of tables with a small number of columns (limiting data that must be read per row)
* the cost/complexity of variable-width columns (VARCHAR vs a fixed-length string), again because of the time and complexity required to do something like "skip ahead three rows" in a data file
* the behavior of CLOB/BLOB types (stored external to the "main" table content)
* the various types of transaction isolation levels and the conditions in which it becomes hard or impossible to guarantee isolation without table or row locking
* etc.
The references at http://axion.tigris.org/readings.html describe some of the theoretical concepts we seemed to think were important or useful at the time.
Also, there's a post at http://heyrod.com/articles/radio-blog/pleasures-of-profiling... that describes a session of performance-tuning on the index implementation that gets into some of the nitty-gritty implementation topics. (But a lot of the issues addressed there were an artifact of Java's primitive vs. object representation, which has been reduced by things like generics.)
It's been a very long time so I'm not sure I can name many specific examples, but IIRC some of the topics that became very clear were things like:
* the importance of the order in which JOINs and WHEREs are applied (to limit the number of rows being accessed per step)
* the relationship between columns that are selected and those that appear in WHERE and ORDER BY clauses.
* the value of tables with a small number of columns (limiting data that must be read per row)
* the cost/complexity of variable-width columns (VARCHAR vs a fixed-length string), again because of the time and complexity required to do something like "skip ahead three rows" in a data file
* the behavior of CLOB/BLOB types (stored external to the "main" table content)
* the various types of transaction isolation levels and the conditions in which it becomes hard or impossible to guarantee isolation without table or row locking
The references at http://axion.tigris.org/readings.html describe some of the theoretical concepts we seemed to think were important or useful at the time.Also, there's a post at http://heyrod.com/articles/radio-blog/pleasures-of-profiling... that describes a session of performance-tuning on the index implementation that gets into some of the nitty-gritty implementation topics. (But a lot of the issues addressed there were an artifact of Java's primitive vs. object representation, which has been reduced by things like generics.)