AI in the DB?

Have you seen the Nintendo Wii? This new games console is the first mass market step towards Virtual Reality that I’ve seen; soon there will probably be add-ons for it that include visors and gloves. For some reason though, the words “Virtual Reality” and Nintendo Wii do not seem to be used together in marketing advertisements.

In the same way, Oracle does not market the software in its database as being “Artificial Intelligence”, but in fact it is, in the form of the Cost Based Optimizer. There is a famous problem in artificial intelligence called “The Travelling Salesman”. The computational problem is how to work out the shortest route between say 20 cities, when there are 20 factorial (20×19x18×17…x3×2) possible routes any one of which might be the shortest. i.e. there are 2,432,902,008,176,640,000 possible routes to consider – now that is a lot of routes, and in fact would take the largest supercomputer thousands of years to process by brute force of considering each and every combination. Now compare this problem with the problem of finding the best join method between 20 tables – not the same, I know, as each table cannot be joined to any of the others, but it is similar wouldn’t you say?

Since version 7 of Oracle, there have been 2 possible methods of query optimization that the database performs; rule-based and cost-based. So why did Oracle create the cost-based model to supercede the rule-based model? The rule-based model was a relatively simple, unsophisticated means of finding an effective access path through all the table joins to the data. It consisted of 20 some rules that were each considered and an execution plan was formulated. For the most part, it was reasonably consistent and satisfactory; its main problem was that it wasn’t very flexible and didn’t adapt itself to suite the type of data it was acting on. But, well, it was …ok.

Enter the cost-based model. After a poor start, it has improved steadily over time, to the point now where Oracle are happy to declare that the rule-based model is now deprecated in 10g (although it still is actually there in the background). Now this is supposed to be ‘clever’ software; taking in to account the performance stats of your disks and CPU, the indexes available, data distribution highlights in histograms and many other variables. Each of these variables are used to construct a series of plans that are costed in terms of how much time the optimizer thinks it would take to retrieve the data; after due consideration, the plan with the least cost and hence the least expected execution time is chosen and away we go. The main difference here, and this is the artificial intelligence aspect, is that it attempts to adapt to both its hardware environment and to the data it acts on to find the fastest path to the data as opposed to blindly following simple rules.

The reality is that ideas that initially appear far out, like Artificial Intelligence and Virtual Reality, are entering our world in simpler, downplayed ways that avoid conjuring up any negative or gimicky images based on the original ideas.

2 Responses to “AI in the DB?”

  1. Fernando Andrade Says:

    I got two comment.
    First Oracle DB 10g in my opinion is a DSS system mixed with a Expert System (AWR,ADDM,ADVISORS), this features also get into the AI field of study.
    Second, can you go a little deeper in this statement “Oracle are happy to declare that the rule-based model is now deprecated in 10g (although it still is actually there in the background)”. If I´m not wrong in 10g when not table/index statistics are present the database use Dynamic Sampling. I wonder if there´s a way to activate rule based behavior.

  2. AndyNoble Says:

    Fernando,
    Yes, I agree that your examples are also part of the field of AI, although strictly speaking DSS and Expert systems are different types of intelligent systems than AI as they have no feedback mechanism for self-learning; having said that, the CBO is a heuristic intelligent system that also doesn’t yet have performance stats fed back in to its optimization, but that is due in future versions. Either way intelligent systems are creeping in to the mainstream, and who knows, maybe as process management evolves more in the middle tier we’ll end up with complex business process models automatically evolving in the form of self learning neural networks…?

    Whilst the RBO is now officially deprecated, meaning that we shouldn’t use it as it probably won’t exist in later versions of Oracle, we can still activate it using

    alter session set optimizer_mode=rule
    or alter system set optimizer_mode=rule

    I tested this on Oracle 10.2.0.1.0. Also the RULE hint still works fine.

    But this is ill-advised, as we need to build solutions with some future proofing in it which means we need to understand how the CBO works and how to use it correctly.

Leave a Reply

You must be logged in to post a comment.


Buy Viagra Online Buy Alprazolam Tramadol Online Celexa Alprazolam Buy Prozac Buy Zyrtec Diazepam Flexeril Carisoprodol Ambien Zithromax Buy Celexa Buy Ephedrine Buy Zithromax Viagra Online Buy Zocor Paxil Buy Generic Viagra Buy Zocor Buy Fioricet Oxycontin Buy Seroquel Tramadol Online Bontril Buy Glucophage Fioricet Buy Ephedrine Buy Nexium Buy Viagra Online Xanax Darvocet Percocet Cheap Phentermine Norco Buy Viagra Buy Zyrtec Hydrocodone Meridia Glucophage Diazepam Buy Lorazepam Buy Acyclovir Propecia Buy Soma Buy Biaxin Didrex Lexapro Bontril Buy Valium Online Buy Lexapro Buy Adderall Buy Zyban Cheap Phentermine Didrex Buy Adipex Buy Codeine Prozac Buy Xanax Online Buy Butalbital Vicodin Buy Cialis Buy Levitra Didrex Buy Clonazepam Xanax On Line Buy Butalbital Buy Zocor Buy Norvasc Buy Oxycontin Effexor Butalbital Buy Diazepam Cheap Phentermine Clonazepam Buy Phentermine Online Buy Biaxin Buy Butalbital Buy Cipro Vicodin Buy Phentermine Online