I just came back from RMOUG Training Days conference. It was my first time in Colorado (and obviously my first RMOUG training day) and it was really great (I wrote about it in another post).
During my second session (From 4 Minutes to 8 Seconds – about a real SQL tuning case I had quite a few years ago), I mentioned that one thing that I usually do when I see a query and need to analyze it, is to take a piece of paper and draw the tables and relations between them. When I later look at the execution plan and try to understand what Oracle does, it helps a lot if I know the structure of the tables. There is a big difference between queries built like a “star” (a single table in the middle, while the others are joined to it) or a “line” (each table is joined to the next one), or any other structure.
Apparently, I’m not the only one who does that. During lunch, Andy Haack approached me and said that he does the same when he analyzes SQL statements and I’m sure Andy and I are not the only ones who do that. Andy asked me if I know of any tool that can perform basic parsing on a statement and build this simple “drawing”. I don’t know about anything like that (I’m familiar with some tools that analyze the schema and foreign keys and draw the tables structure and relations, but not tools that parse SQL statements).
I know that building such a tool is very complicated. It’s not too bad for simple statements, but if you add inline views, ANSI joins as well as Oracle syntax, WITH clauses and more it can be really tough.
So here is my question to you, do you know of any tool that does something similar? Would you be willing to write something (even a basic one that can be improved later)?