Part three in a blog series about SQL optimization, index tuning and yes … the quiz answers!
Optimizing indexes? It’s hard. Really, really hard.
At the end of my previous blog post, I gave you a pop-quiz. Remember the scenario? You have five small tables, and each table has only three columns. That’s it. For simplicity’s sake, I’ll keep the answers short here, but see the forum thread discussion here, where you’ll see others’ quiz responses, and where I get into more details regarding calculations and practical considerations. What were the questions again?
- What is the total number of possible indexes on just one of the tables? On all five tables?
15 indexes per table, assuming you can define one-, two-, or three-column indexes.
75 total possible indexes on the 5 tables. - What is the total number of different combination sets of these indexes, considering a max of three indexes at a time?
70,375 index sets. - What is the total number of index sets, considering up to 10 indexes for each set?
Almost one trillion such index sets (973,602,516,870 to be exact). - (Extra Credit) What is the number of all possible combinations of all the indexes?
2 to the power of 75,
which is beyond enormous, by the way. It is so large that it would take the fastest computer close to 1,198 YEARS of continuous operation to define all the index sets here, assuming it could generate them at one trillion sets per second!
What’s the real point of the quiz? Simply this: most of us don’t realize how super difficult it really is to identify an effective set of indexes for the way your applications access database tables.
Develop your own applications in-house? Purchase vendor-supplied solutions? Either way, you’re sure to have more than five small database tables, and you’re going to need some super-duper uber-intelligent assistance to find the ultimate set of indexes to make your applications hum. Quest® SQL Optimizer, has that uber-intelligence. Plain and simple.
Overcoming SQL / index optimization challenges
Remember Alex from my previous post? Here were his challenges. And here’s how Quest SQL Optimizer overcomes each of these.
Overcoming challenge #1: Finding the SQL
Alex confessed that he has a hard time “seeing” all the SQL that hits his databases.
Quest SQL Optimizer has parsers that can search for SQL in:
-
-
- Database code (SQL embedded in stored procedures, packages, view definitions, etc.)
- Source code off the database (like Java source code, or shell scripts, for example).
- Database SQL memory (for SQL built dynamically at runtime or submitted by ad hoc users).
-
Overcoming challenge #2: Finding feasible index combinations
How does Alex identify and keep track of index candidates as the number of objects accessed in his SQL workload gets bigger?
You took the quiz. You’ve seen the numbers. This task is humanly impossible. But when you feed your workload to SQL Optimizer’s Index Analyzer, the product employs AI techniques and smart genetic algorithms to rapidly find feasible sets of candidate indexes. And it does this exponentially faster—literally!—than the best consultants your money can afford.
The screen capture below shows how SQL Optimizer finds several index sets only mere seconds after the search starts. Other index sets are discovered at other points in time. In minutes!
Overcoming challenge #3: Testing the impact of index combinations
Even if Alex *could* find a small fraction of the index possibilities, does he have the right tools to measure the performance impact of new indexes introduced to his workload? Ideally, BEFORE they impact Production?
SQL Optimizer can use virtual indexes to assess the performance impact of proposed indexes on your SQL workload, as the screen capture below shows.
Bottom line
So there you have it, a one-two-three punch. Plain and simple. Quest SQL Optimizer can:
- Find various SQL-based workloads
- Analyze the object access patterns to recommend candidate indexes
- Assess the impact of suggested indexes
Try SQL Optimizer for FreeSQL Optimizer is included in many of our Toad elite editions like Xpert, Dev and DBA, and not just for Oracle, but also for SAP and Microsoft SQL Server. If you’re already using a Toad product, check to see which edition you have and consider upgrading. Or, try SQL Optimizer out for free. Add on data modeling, code analysis, and code testing in this free trialBesides SQL Optimizer, do you need more features likedata modeling, code analysis, and code testing? Compare our top three Toad for Oracle editions and try Toad free for 30 days. Are you already in a trial and ready to purchase? Talk to sales or buy now online. |
More resources:
Webcast: How can you simplify and automate SQL optimization for developers in Toad® for Oracle?—Ask Toad!
Blog: SQL Optimizer saves $40,000
Blog: SQL Optimizer for Oracle – another success story
Blog: Quick and Easy SQL Optimization with Toad for Oracle
Blog: Understand the optimization process used in Quest SQL Optimizer
Blog: Optimize SQL (Plan Control
Blog: SQL in COBOL? Find and tune your SQL wherever it is.
Need help managing data?
Quest Software is here to help you simplify complexity, reduce cost and risk, and drive performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate.
Toad® database management tools are cross-platform solutions from Quest® that can help new and veteran DBAs, and even “accidental” DBAs manage data in Oracle, SQL Server, DB2, SAP, MySQL, and Postgres environments.
Learn more about how Toad database management tools from Quest can help with database development, performance monitoring and database DevOps.
Share on social media
If you think your colleagues would benefit from this article, share it now on social media with the buttons located at the top of this blog post. Thanks!
