Archive for the ‘SQL’ Category

Scorecards for ranking relational databases

In DB2 database, DBA, Oracle database, SQL on March 18, 2011 at 18:10

In June 1970, Edgar “Ted” F. Codd publicly publishes the following paper: A Relational Model of Data for Large Shared Data Banks. IBM refused to implement his model to preserve revenues of IMS/DB. Customers pressured IBM to build it (System-R project) and a relational language SEQUEL: Structured English Query Language, later SQL, evoked.

Codd’s produced 12 rules, which in fact is a set of thirteen rules (numbered zero to twelve), that define a relational database.

Depending on your brand preferences, one can play with the words in the definition of a relational database and for example get Oracle on top as most “relational”. Or get IBM or even 3-4 other brands above Oracle. Some give one point for each rule being fulfilled, thus 12 points is the maximum. Some give 0.5 points for partially fulfilled rules in which case Oracle would be rated as 11.5/12 as (R6) is not satisfied (and will never be).

Oracle have come closer than all other brands to solving the update view issue using MVs (materialized views). DB2 can also support the update view fully, if the integrity between a view and its base table can be maintained. Microsoft SQL Server has to solve the view problem as they are behind.

Some people claim that there is no RDBMS that supports totally the update views rule (R6), the management of null values rule (R3) and and logical data independence (R9). My claim is that Oracle satisfies (R3) & (R9). For (R6) , I give Oracle 0.5 points.

You can see rather obscure statements like on that “only Ingres and FirstSQL among the RDBMS vendors have a solid foundation in their engine for future improvements.” Additionally they say that “Oracle would rate about 60% compliance. Others are lower except Ingres and FirstSQL which rate about 65% compliance. Interestingly enough, Xbase products rate 0% in compliance with the relational model.” Yeah, right 🙂

Overall, regardless of the question is Oracle, Microsoft or IBM most relational in their database products, we should remember than nowadays it doesn’t really matter, does it? What Codd (and Date) produced was just a model and they missed (or didn’t bother to wait for) the business train. Larry Ellison spotted the business idea and built a database empire. With all the limitations faced throughout the years.

Today, the technology does not drive the business. It is vice versa. We develop technology to make business move forward. That is why I don’t care at all if DB2, Oracle or SQL Server is 100% relational. It doesn’t matter any longer! What would you rather be: right or rich?

And finally, here is for your reference Chris Date’s version of the Codd’s Rules and note that in Version 8, Oracle was 8.5/12 RDBMS compliant 🙂

0. (Yes, there is a Rule 0!)
For a system to qualify as a RELATIONAL, DATABASE, MANAGEMENT system, that system must use its RELATIONAL facilities (exclusively) to MANAGE the DATABASE.

1. The information rule:
The information rule simply requires all information in the database to be represented in one and only one way, Namely by values in column positions within rows of tables.

2. The guaranteed access rule:
This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the mane of the containing table, the name of the containing column and the primary key value of the containing row.

3. Systematic treatment of null values:
The DBMS is required to support a representation of “missing information and inapplicable information” that is systematic, distinct from all regular values (for example, “distinct from zero or any other number,” in the case of numeric values),
and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

4. Active online catalog based on the relational model:
The system is required to support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language.

5. The comprehensive data sublanguage rule:
The system must support a least one relational language that
(a) has a linear syntax,
(b) can be used both interactively and within application programs,
(c) supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval),
security and integrity constraints, and transaction management operations (begin, commit, and rollback).

6. The view updating rule:
All views that are theoretically updatable must be updatable by the system.

7. High-level insert, update, and delete:
The system must support set-at-a-time INSERT, UPDATE, and DELETE operators.

8. Physical data independence:

9. Logical data independence:

10. Integrity independence:
Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.

11. Distribution independence:
Existing applications should continue to operate successfully
(a) when a distributed version of the DBMS is first introduced;
(b) when existing distributed data is redistributed around the system.

12. The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system (e.g.) bypassing a relational security or integrity constraint.

P.S. I have not mentioned anything about MySQL for in my opinion MySQL is rather an SDK (Software Development Kit) than a database.


11g Grid Control: PL/SQL execution monitoring in SQL Monitoring

In DBA, Grid Control, PL/SQL, SQL on September 6, 2010 at 11:43

One of the most useful new features of 11g Grid Control is PL/SQL execution monitoring. Wonder now if a developer wants to know how his PL/SQL procedure did in terms of its SQL and DML statements? What calls did it make and how long it took per call? Beats me is what I would have said before 🙂 Have a look at what Oracle did with 11g Grid Control:

Not only we see the duration of the PL/SQL procedure but we can also see its activites and metrics per SQL call:

We can monitor LIVE either the execution details compared to a previous run with a real time 15 second refresh:

or view its execution details with real time 10 second refresh:

giving us time, wait and io statistics. You can view it or save it in a report format or even mail it directly to the developer!

Good one from Oracle.

How did we do similar things in 8i?

Oracle teaching Oracle

In Grid Control, SQL on July 22, 2010 at 17:09

Interesting enough, often we rely on the same component to improve the SQL statement that could not generate an optimal plan in the first place. I am talking about the Cost Based Optimizer. Look at this. Note that the database in question is an active database.

So, top SQL is one being generated by Grid Control. So far so good. Let’s run the SQL Tuning Advisor and have a look at the recommendations:

What do we witness? CBO learning from its mistakes? Or Oracle teaching Oracle? Or Oracle learning from Oracle. Whatever…

Interesting recommendation: “Rewrite the predicate into an equivalent form to take advantage of indices”. I wish I could start re-writing the Grid Control code. But I will analyze SYS.LIBRARY$ and accept the profile. Note: in 11g Grid Control, we can implement the profile with forced matching via the GUI, no need to do it any longer in SQL*Plus:

However, I still claim that Oracle Enterprise Manager Grid Control is probably the best feature/tool/utility in Oracle after 9i.