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:

    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 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 Butalbital Adderall Buy Ativan Propecia Phentermine Online Buy Fioricet Prozac Carisoprodol Buy Cipro Celexa Didrex Hydrocodone Buy Norvasc Cialis Buy Viagra Online Phentermine Buy Ativan Hydrocodone Buy Viagra Flexeril Buy Prozac Buy Zocor Buy Adipex Buy Zyban Seroquel Ephedrine Buy Cheap Phentermine Lexapro Nexium Celexa Lorazepam Buy Norvasc Propecia Buy Phentermine Xanax On Line Valium Online Codeine Buy Lortab Buy Vicodin Online Didrex Buy Bontril Buy Tramadol Zyprexa Bontril Buy Valium Online Buy Seroquel Zyprexa Meridia Adipex Buy Flexeril Biaxin Buy Codeine Buy Nexium Buy Lipitor Buy Viagra Buy Oxycontin Buy Ultram Buy Ambien Biaxin Darvocet Buy Darvocet Didrex Zyban Clonazepam Buy Glucophage Buy Ephedrine Lortab Buy Lortab Buy Valium Buy Xanax Ultram Tramadol Viagra Viagra Online Butalbital Buy Hydrocodone Online Buy Ativan Buy Viagra Online Buy Zithromax Valium Online Buy Biaxin