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.


Ativan Ultram Vicodin Soma Celexa Buy Paxil Buy Darvocet Carisoprodol Norvasc Buy Vicodin Buy Cialis Online Buy Ambien Buy Lortab Prozac Valium Bontril Buy Line Xanax Buy Tramadol Buy Celexa Effexor Buy Zyrtec Buy Norvasc Paxil Acyclovir Viagra Online Buy Lortab Buy Valium Tramadol Online Buy Zocor Buy Zyprexa Buy Diflucan Buy Cheap Phentermine Buy Cheap Phentermine Buy Vicodin Buy Zyban Propecia Cialis Xanax Buy Percocet Vicodin Buy Levitra Buy Viagra Buy Phentermine Bupropion Buy Butalbital Buy Percocet Carisoprodol Buy Tenuate Buy Celexa Generic Viagra Lorazepam Zyban Buy Adipex Buy Lortab Cheap Phentermine Buy Didrex Buy Zyrtec Buy Codeine Alprazolam Online Xanax Buy Tramadol Online Buy Butalbital Buy Propecia Buy Generic Viagra Buy Alprazolam Buy Hydrocodone Ultram Valium Online Buy Cialis Online Buy Meridia Fioricet Buy Valium Online Diazepam Buy Percocet Buy Butalbital Buy Carisoprodol Buy Zocor Buy Glucophage Buy Meridia Buy Adipex Codeine