Index Advice from Oracle SQL Developer
Posted on 2015-02-12
I've been developer and DBA on IBM DB2 for the past two decades, and I'm just now jumping in to developing on Oracle. So, I apologize in advance if this is too-easy, "beginner stuff".
When I wanted to optimize a query in DB2, I could use their "Visual Explain" tool which gives loads of details on how the optimizer parses and runs a query. It outputted a graphical tree-structure showing the details of how each table was going to be parsed, including which index it would choose, why it chose it, its index key-list, estimated processing time, and numerous other pieces of information about the query. In fact, if the optimizer determined that it could have used an index that doesn't actually exist, it will report that back as "index advice". From there, I can even right-click on the icon to manually create the suggested index.
In Oracle SQL Developer, I've been able to successfully use "explain plan for <my SELECT statement>" and "select * from table(dbms_xplan.display);" to see a somewhat descriptive set of text-based information on the optimization plan of a query, but I don't see any sort of "Index Advice" like I did in the DB2 tool.
Again, I apologize for my ignorance, but does this kind of feature exist in Oracle? I'm using Oracle 11g, and my SQL Developer version is "Version 1.5.5 Build MAIN-5969".
Thanks in advance!