Wednesday, April 01, 2009

A unique opportunity...

Learn SQL from one of the original SQL'ers.  Chris Date is doing some a seminar in conjunction with Cary Millsap's company Method-R.  Unfortunately I cannot make it that week (I'd be there if I could) - but it is a rather unique and uncommon offering.  I've often said that if you want to be able to "tune" SQL, you need to understand what is happening underneath the covers, what is available.  You don't want a 10 step checklist, you want knowledge.  That is what this is all about.

This article by Cary sums up the seminar as well as why it will be good (in his humble opinion)...  Give it a look see...

POST A COMMENT

16 Comments:

Blogger Doug Cowles said....

Man does that sound awesome - but last day to sign up is today and I can't wrangle funding that quickly.. if at all in the current environment.. bummer.. Nice to see Chris Date on the trail however. His book "An Intro.." is a classic. I hope there is a Chris Date SQL seminar redux someday. This sounds like a great subject for another tome as well.

Thu Apr 02, 03:11:00 AM EDT  

OpenID craigc said....

Chris Date seems to be wearing the pragmatist's hat (or the smart strategist's), in the sense that although he explains why SQL databases fundamentally do not implement the relational model and therefore are a disservice to end users he accepts their deep rooted existence in practice. In a recent book, SQL and Relational Theory, aimed at practitioners, and in teaching how to bypass SQL shortfalls he stretches us to apply the principles of the model in our database applications. Wish I could attend!

Thu Apr 02, 03:55:00 PM EDT  

OpenID irishdba said....

Whenever I read of the shortcomings of SQL I wonder why are we stuck with it? In the rest of the computing world, when the shortcomings of a language have become apparent, new languages have sprung up. Look at the hundreds of them out there, whole families of languages. Yet in databases, we have just one commonly used query language.

If I can plug storage engines into into MySQL, why not plug in different query language parsers? I can run whole different languages on top of a Java VM, why not different query languages on top of a database?

Things like Tutorial D are out there but hardly anyone has ever even heard of it. And it is standalone, I can't run it in my existing environment.

I am surprised that new query languages have not sprung up in the open source world. Maybe it needs a bigger player - can Oracle do better than SQL?

Tue Apr 07, 11:26:00 PM EDT  

Blogger Thomas Kyte said....

@irishdba

I don't necessarily think the language is 'deficient' or has severe shortcomings personally.

I do think people don't learn the language - and all of the features - and how it works. That is a shortcoming, but not of the language itself.

And the fact is, you do have
sql,
plsql,
triggers,
xquery,
xpath,
regular expressions,
the ability to add your own operators,
create you own indexing schemes (eg: it is and has been since 1997 'extensible'),

spatial capabilities,
text (gisting, searching, marking up),
audio/ video/ graphic capabilities - etc.


How about you mention two or three 'shortcomings' and describe how a 'language plugin' would rectify them - because I don't see how a language plugin would rectify a deficiency in the underlying *model*.

Wed Apr 08, 03:43:00 AM EDT  

Anonymous RussellH said....

One way in which SQL seems deficient to me is that there seems to be no way two efficiently re-use a SQL fragment or sub-query. Sometimes exactly the same business rule needs to be used in dozens of different queries in a large system. Even a small change to the rule means a change to dozens of queries.

I know you can use a user defined function to encapsulate the rule, but that can only hurt performance. I wish there were some way to do an "inline" SQL function.

Fri Apr 10, 03:59:00 PM EDT  

Anonymous Jan said....

Sorry for this off-topic.

Asktom is down for few days. Looks you are taking well deserved holidays :-)

I would be interested if/what monitoring is in place there ... knowing it's a "pet" project.

Sun Apr 12, 10:33:00 AM EDT  

Blogger Thomas Kyte said....

@Jan

thanks for the heads up, I would have discovered that today myself once I got moving..

I was in Greece for the week - seminar and customer events - and spent most of the last two days traveling back.

Unfortunately, I am the "monitor", it is a pet project - no real DBA beyond "me". We are moving it to apex.oracle.com (in the middle of that process) at which point it'll be running in a virtual machine managed by our global IT staff with their tools to monitor and watch it.

So, availability will hopefully increase from "pretty darn available" to "very available" (he says confidently...)

Sun Apr 12, 10:41:00 AM EDT  

Blogger sphealey said....

> I know you can use a user defined
> function to encapsulate the rule,
> but that can only hurt
> performance. I wish there were
> some way to do an "inline" SQL
> function.

I believe that is known as a "view" ;-)

sPh

Tue Apr 21, 11:17:00 AM EDT  

Blogger Seedy said....

So, has anyone read the book: SQL and Relational Theory: How to Write Accurate SQL Code
Comments? it seems relatively reasonably priced.

Tue Apr 21, 07:08:00 PM EDT  

OpenID irishdba said....

Getting back to SQL...

Think in terms of the programming languages C and C++. C is an elegant programming language, it has been one of my favourites for two decades. However careless programmers can hang themselves quite easily with pointers and memory and it isn't object oriented. Skillful practitioners can avoid the pitfalls and can, with some work, even implement objects with C but most practitioners aren't up to that. Hence we have C++ which is object oriented and addresses some of the major pitfalls. The Java language can be seen as taking that language family further down that road. It is not that C is a bad language but that C++ and Java add more capability to the language family.

In SQL, for example, let's say we want a language that more closely follows the relational model as Chris Date has many times described. Could we not have a "SQL++" that was more relational? I know that skillful SQL practitioners can write relational SQL code but many others apparently aren't up to that task just as many C programmers weren't up to the task either. Why not have the option of a tool or language that provides more support for that?

"SQL++" would not negate SQL just as C++ does not negate C. Rather it would just provide more options to the user.

Another example might be that there is a huge gap between the way an object-oriented programming system references data and the way SQL does. An OOPS finds a record with something like:
object.find_by_id(my_id)
whereas SQL does
select * from table where id = my_id

Why not have a query language that acts more like the OOPS? It would make an easier transition between code and data for the programmer and would make the ORM layer simpler. Again, the underlying data model would be the same, SQL would still work. Another query language would just give the developer the option of a different tool that might work better in some situations.

Tue Apr 21, 11:01:00 PM EDT  

Blogger Doug Cowles said....

@Seedy I bought it because of this thread. Plan on cracking it open this weekend.

Wed Apr 22, 02:49:00 AM EDT  

Blogger Thomas Kyte said....

@irishdba


I don't see the connection between C and C++ and "building another language to sit on top of an existing data store"

C - just a language, compile down to object code and the OS executes it.

C++ - ditto


SQL - the language used to access and transactionally process data. C and C++ - they have no underlying data store, they are not a special purpose language built to manipulate and access data, they are general purpose programming languages.


And - I would put forth (again) that the extensibility you are asking for already exists. I listed quite a few above.


You wrote:

Another example might be that there is a huge gap between the way an object-oriented programming system references data and the way SQL does. An OOPS finds a record with something like:
object.find_by_id(my_id)
whereas SQL does
select * from table where id = my_id
I disagree on the "huge gap", I do think developers in love with their programming language and in fear of the database (lack of knowledge, they frankly do not care to understand what it is, how it works) leads to some really bad designs. However, you cannot tell me that in Oracle you cannot implement:



object.find_by_id(my_id)


You can (since 1997 you can). You can create complex types (collections of objects, objects themselves, pointers (refs), etc) and you can easily have an interface that says "object.find_thyself(your_id)". You can even have utilities like jpublisher publish an interface class in your language java in order to receive this object. That most certainly exists.


You need to describe this

Could we not have a "SQL++" that was more relational? in more 'detail', tell us - what does it mean to be 'more relational'. Most of that means "do not use this feature of the existing SQL, this truly useful feature of SQL (which is already sql++) technically goes against the relational model). It is not that SQL needs to be added to to be more relational - rather, the opposite, it needs to be taken away from.


C and C++ are not useful analogies, they have *no data store*, they do not access the same things. They are just languages - SQL is a non-procedural language plus a datastore that must provide ACID properties.

And you can write your own 'languages' to interact with it - on top of SQL.

Wed Apr 22, 04:49:00 AM EDT  

Blogger Joel Garry said....

ACM portal has some free books, like this Codd guy who explains why one might want a declarative language. Wow, a system that figures out how to get what data you ask for without all these optimizer and index performance issues, what a great idea. Next, we can get rid of all these programmers and analysts.

word: averink

Fri Apr 24, 07:07:00 PM EDT  

Blogger Glasses said....

Unfortunately, I am the "monitor", it is a pet project - no real DBA beyond "me". We are moving it to apex.oracle.com (in the middle of that process) at which reading glasses point it'll be running in a virtual machine managed by our global IT staff with their tools to monitor and watch it.

Wed Jul 29, 03:41:00 AM EDT  

Blogger Purvesh said....

Hi Tom,

Quite weird of me to leave a comment on this blog(i tried on asktom.oracle but it was not accepting questions), but i desperately need a favor. Can you please suggest me some books/links that would explain the Undercover working of Oracle. Some very core basics that one needs to know to master a DBMS. I have been working on Oracle since past 1 year but have no firm idea of how its working behind the scenes. I would be glad with your reply. Will it be possible for you to drop me mail at purveshk@gmail.com. Eagerly awaiting your reply in this regard.

Thu Oct 08, 04:45:00 AM EDT  

Blogger Thomas Kyte said....

@Purvesh

the concepts guide is excellent


Also, my book Expert Oracle Database Architecture does that.

Thu Oct 08, 06:24:00 AM EDT  

POST A COMMENT

<< Home