Re: Dreaming About Redesigning SQL

Jan Hidders wrote:
>
> Lee Fesperman wrote:
> >
> > But what do I know? Chris Date tells me I'm completely wrong about nulls.
>
> Really? I actually believe he has some pretty good arguments there. What
> is it that you don't agree with?

Read the papers on www.firstsql.com.

--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Jan Hidders wrote:
>
> Lee Fesperman wrote:
> >
> > But what do I know? Chris Date tells me I'm completely wrong about nulls.
>
> Really? I actually believe he has some pretty good arguments there. What
> is it that you don't agree with?

Read the papers on www.firstsql.com.

--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Lee Fesperman writes:

> Jan Hidders wrote:
> >
> > Lee Fesperman wrote:
> > > Jan Hidders wrote:
> > >>
> > >>Correct. There's absolutely no reason to believe that you cannot
> > >>have data independence with logical pointer or references. I
> > >>would however argue that allowing entities without representable
> > >>keys is not a good idea.
> > >
> > > Incorrect. Even though you call them 'logical' pointers, they
> > > are still physical artifacts and have no place in a truly
> > > logical view of the database.
> >
> > Logical pointers can be defined at the logical level and
> > implemented in various ways. They are just as much physical
> > artifacts as, say, relations are.
> >
> > > Databases are about
> > > data, and pointers are not data (or meta-data).
> >
> > They carry information. That makes them data. I see no good reason
> > to use a more restrictive definition.
>
> Because the information they carry is physical, which doesn't belong
> in a logical model.
>
> Let me try again:
>
> In the relational model, tables (relations) are completely
> independent except through the action of inter-table constraints
> (like referential integrity). The constraints are declarative and
> truly logical. They can be removed without changing the contents of
> a single column in the participating tables.
>
> Pointers form a rigid bond between table. To my mind, this makes
> them physical --- if it walks like a duck, etc.

But references (as opposed to pointers) are not that far off from
surrogate keys, at least in terms of how they can be used to enforce
referential integrity. But don't take that as trolling, I'm just
setting the stage for a few questions below.

> Pointers don't have the flexibility associated with a logical
> entity. You must change table contents to remove that bond.

I agree with that, and the same is true of references - they do
represent a bond between objects, which makes them inflexible. But if
you compared an RDBMS that used surrogate keys to establish RI between
two tables, you've got a certain inflexibility there as well. If
there is no longer a relationship between the two tables, the FK
should be dropped along with the RI constraint, right?

> It's been argued here that pointers (references) could be mapped to
> pk/fk constructs. Besides throwing away other benefits of pk/fk, it
> doesn't work in the general case, for instance:

The only benefit I've come up with is the case where something other
than a surrogate key is used as the fk (like state postal code, for
example), eliminating the need to do a join to get a meaningful
value. What other benefits do you consider to be lost when references
are used instead of values?

> + When the fk is part of the pk of the referencing table,

If I understand you correctly, I disagree with this. I understand you
to say that if a fk makes up part of the pk of the referencing table,
and the fk was a reference, instead of a value, the dbms would not be
able to enforce the uniqueness constraint on the pk of the referencing
table. That is not true about my ODBMS implementation (PyPerSyst).
Did I understand you correctly?

> + When the fk columns are shared with other fk's.

I don't understand this point. Can you provide a bit more detail
about what you mean, or maybe an example? Thanks.

--
Patrick K. O'Brien
Orbtech http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

Lee Fesperman writes:

> Jan Hidders wrote:
> >
> > Lee Fesperman wrote:
> > > Jan Hidders wrote:
> > >>
> > >>Correct. There's absolutely no reason to believe that you cannot
> > >>have data independence with logical pointer or references. I
> > >>would however argue that allowing entities without representable
> > >>keys is not a good idea.
> > >
> > > Incorrect. Even though you call them 'logical' pointers, they
> > > are still physical artifacts and have no place in a truly
> > > logical view of the database.
> >
> > Logical pointers can be defined at the logical level and
> > implemented in various ways. They are just as much physical
> > artifacts as, say, relations are.
> >
> > > Databases are about
> > > data, and pointers are not data (or meta-data).
> >
> > They carry information. That makes them data. I see no good reason
> > to use a more restrictive definition.
>
> Because the information they carry is physical, which doesn't belong
> in a logical model.
>
> Let me try again:
>
> In the relational model, tables (relations) are completely
> independent except through the action of inter-table constraints
> (like referential integrity). The constraints are declarative and
> truly logical. They can be removed without changing the contents of
> a single column in the participating tables.
>
> Pointers form a rigid bond between table. To my mind, this makes
> them physical --- if it walks like a duck, etc.

But references (as opposed to pointers) are not that far off from
surrogate keys, at least in terms of how they can be used to enforce
referential integrity. But don't take that as trolling, I'm just
setting the stage for a few questions below.

> Pointers don't have the flexibility associated with a logical
> entity. You must change table contents to remove that bond.

I agree with that, and the same is true of references - they do
represent a bond between objects, which makes them inflexible. But if
you compared an RDBMS that used surrogate keys to establish RI between
two tables, you've got a certain inflexibility there as well. If
there is no longer a relationship between the two tables, the FK
should be dropped along with the RI constraint, right?

> It's been argued here that pointers (references) could be mapped to
> pk/fk constructs. Besides throwing away other benefits of pk/fk, it
> doesn't work in the general case, for instance:

The only benefit I've come up with is the case where something other
than a surrogate key is used as the fk (like state postal code, for
example), eliminating the need to do a join to get a meaningful
value. What other benefits do you consider to be lost when references
are used instead of values?

> + When the fk is part of the pk of the referencing table,

If I understand you correctly, I disagree with this. I understand you
to say that if a fk makes up part of the pk of the referencing table,
and the fk was a reference, instead of a value, the dbms would not be
able to enforce the uniqueness constraint on the pk of the referencing
table. That is not true about my ODBMS implementation (PyPerSyst).
Did I understand you correctly?

> + When the fk columns are shared with other fk's.

I don't understand this point. Can you provide a bit more detail
about what you mean, or maybe an example? Thanks.

--
Patrick K. O'Brien
Orbtech http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

Lee Fesperman writes:

> Jan Hidders wrote:
> >
> > Lee Fesperman wrote:
> > > Jan Hidders wrote:
> > >>
> > >>Correct. There's absolutely no reason to believe that you cannot
> > >>have data independence with logical pointer or references. I
> > >>would however argue that allowing entities without representable
> > >>keys is not a good idea.
> > >
> > > Incorrect. Even though you call them 'logical' pointers, they
> > > are still physical artifacts and have no place in a truly
> > > logical view of the database.
> >
> > Logical pointers can be defined at the logical level and
> > implemented in various ways. They are just as much physical
> > artifacts as, say, relations are.
> >
> > > Databases are about
> > > data, and pointers are not data (or meta-data).
> >
> > They carry information. That makes them data. I see no good reason
> > to use a more restrictive definition.
>
> Because the information they carry is physical, which doesn't belong
> in a logical model.
>
> Let me try again:
>
> In the relational model, tables (relations) are completely
> independent except through the action of inter-table constraints
> (like referential integrity). The constraints are declarative and
> truly logical. They can be removed without changing the contents of
> a single column in the participating tables.
>
> Pointers form a rigid bond between table. To my mind, this makes
> them physical --- if it walks like a duck, etc.

But references (as opposed to pointers) are not that far off from
surrogate keys, at least in terms of how they can be used to enforce
referential integrity. But don't take that as trolling, I'm just
setting the stage for a few questions below.

> Pointers don't have the flexibility associated with a logical
> entity. You must change table contents to remove that bond.

I agree with that, and the same is true of references - they do
represent a bond between objects, which makes them inflexible. But if
you compared an RDBMS that used surrogate keys to establish RI between
two tables, you've got a certain inflexibility there as well. If
there is no longer a relationship between the two tables, the FK
should be dropped along with the RI constraint, right?

> It's been argued here that pointers (references) could be mapped to
> pk/fk constructs. Besides throwing away other benefits of pk/fk, it
> doesn't work in the general case, for instance:

The only benefit I've come up with is the case where something other
than a surrogate key is used as the fk (like state postal code, for
example), eliminating the need to do a join to get a meaningful
value. What other benefits do you consider to be lost when references
are used instead of values?

> + When the fk is part of the pk of the referencing table,

If I understand you correctly, I disagree with this. I understand you
to say that if a fk makes up part of the pk of the referencing table,
and the fk was a reference, instead of a value, the dbms would not be
able to enforce the uniqueness constraint on the pk of the referencing
table. That is not true about my ODBMS implementation (PyPerSyst).
Did I understand you correctly?

> + When the fk columns are shared with other fk's.

I don't understand this point. Can you provide a bit more detail
about what you mean, or maybe an example? Thanks.

--
Patrick K. O'Brien
Orbtech http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

lauri.pietarinen@atbusiness.com (Lauri Pietarinen) writes:

Please allow me to take exception to a few of your points.

> If you take a standard SQL-query with sorting and grouping and some
> joins and compare it with hand written "navigational" code you will
> notice that what can be expressed in SQL in 5-10 lines of code will
> require several pages of hand written code for equivalent result.

That really depends on two things: the specific SQL query, the
"navigational" programming language, and the capabilities of the
ODBMS. In general, I would agree that a declarative language is going
to be able to express an operation more concisely than a procedural or
object-oriented language. But a language like Python is very powerful
and expressive. I can do the equivalent of a simple SQL query in
about as many lines of code in Python using the PyPerSyst ODBMS. It
would take an incredibly complex SQL query before I'd get to "several
pages of hand written code". PyPerSyst itself is only several pages
of hand written code. ;-)

> The third issue is that the SQL query can be optimised to a much
> higher degree than procedural code. Say you add a new index. The
> DBMS will/can immediately start using it without any user
> intervention (if it decides to). In the procedural alternative you
> would have to recode your query.

You wouldn't necessarily *have to*. PyPerSyst does not yet have a
declarative query capability. But if you add an index, the same
procedural code will now make use of that index (or rather, the
procedural code is calling methods of objects that are smart enough to
look for indexes to optimize themselves without requiring any changes
in application code).

I only point this out becase critics of object databases really need
to improve their understanding of the capabilities of object languages
and object databases. They just aren't as inflexible as some pundits
would like to claim.

> The optimiser can also take into account the cardinality of tables
> (=number of rows) so that it will produce a different plan for a
> small database and a big database.

PyPerSyst doesn't do this, but it is completely within the realm of
possibilities. All we are really talking about is levels of
abstraction. Nobody should be writing procedural code at such a low
level of abstraction that their code can't be optimized at runtime
based on the presence or absence of indexes, or the size of the data
involved. There is nothing that precludes an ODBMS, or procedural
code, from being able to be optimized at runtime without changing
code.

> An optimiser can even be sensitive to input from users at run time,
> say you have
>
> select name, salary
> from nurses
> where sex = ?
>
> If the parameter given at run time is 'M' then it could be
> advantageous to use an index, but not if parameter is 'F'. This
> decision could be made by the optimiser "on the fly". I hope you get
> the picture...

I hope you get the picture that every example you have given can be
true about procedural code. Here is a very simple example of the
source code for the "find" method of the PyPerSyst Extent class:

def find(self, **criteria):
"""Return list of instances exactly matching all criteria."""
instances = []
names = criteria.keys()
spec = self._makekeyspec(names)
if spec in self._altkeys:
# Use alternate keys as a shortcut.
key = self._makekey(criteria, spec)
d = self._altkeys[spec]
if key in d:
instance = d[key]
instances.append(instance)
else:
# Scan all instances for a match.
for instance in self._instances.values():
match = True
for name, value in criteria.items():
if getattr(instance, name) != value:
match = False
break
if match:
instances.append(instance)
return instances

This find method returns a list of instances matching some criteria
supplied by the application code. A specific example of its use would
look like this:

people = db.root['Person'].find(name='Lauri Pietarinen')

If there is an alternate key index on the name attribute, the find
method will use that as an internal optimization. If one does not
exist, then it will scan all instance of the Person class looking for
a match. Let's assume that today there is no index, and all instances
are scanned. If tomorrow the schema is changed to declare that there
should be an alternate key enforced on the name attribute, then
PyPerSyst will build and maintain an index that will be used to
enforce that constraint. As a side benefit, the find method will also
use that index to optimize its performance. The application code will
not have changed.

> > Other than that, what's wrong with the network (or similar)
> > models? I have not found a good link to such a discussion though
> > I hear it repeated that there are certain classical arguments
> > against them.
>
> In 1974 there was the BIG DEBATE where Codd defended the RM against
> network guys. I wonder if there is a transcript available?
>
> I think one of Codds arguments was that the number of manipulative
> operators needed in the RM (i.e. insert, update, delete) was much
> lower than in CODASYL (connect, disconnect, etc etc...)

Any arguments that are true about CODASYL are not necessarily true
about current ODBMSs, in spite of the fact that they both could be
described as implementing a network model. I have also tried to find
information about the weaknesses of the network model and have not
found much of value. Some of the obvious weaknesses of network
products, such as the difficulty in changing your schema because
pointers were static and tied to the physical location of information
on disk, are easily avoided in modern object databases. For example,
PyPerSyst uses references, but has no problem evolving schemas and
migrating instances from the old schema to the new schema. IMO,
references in modern OO languages aren't as evil as pointers in old
CODASYL products. But I'm still looking for information to confirm or
dispute that opinion.

Thanks for listening. :-)

--
Patrick K. O'Brien
Orbtech http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

lauri.pietarinen@atbusiness.com (Lauri Pietarinen) writes:

Please allow me to take exception to a few of your points.

> If you take a standard SQL-query with sorting and grouping and some
> joins and compare it with hand written "navigational" code you will
> notice that what can be expressed in SQL in 5-10 lines of code will
> require several pages of hand written code for equivalent result.

That really depends on two things: the specific SQL query, the
"navigational" programming language, and the capabilities of the
ODBMS. In general, I would agree that a declarative language is going
to be able to express an operation more concisely than a procedural or
object-oriented language. But a language like Python is very powerful
and expressive. I can do the equivalent of a simple SQL query in
about as many lines of code in Python using the PyPerSyst ODBMS. It
would take an incredibly complex SQL query before I'd get to "several
pages of hand written code". PyPerSyst itself is only several pages
of hand written code. ;-)

> The third issue is that the SQL query can be optimised to a much
> higher degree than procedural code. Say you add a new index. The
> DBMS will/can immediately start using it without any user
> intervention (if it decides to). In the procedural alternative you
> would have to recode your query.

You wouldn't necessarily *have to*. PyPerSyst does not yet have a
declarative query capability. But if you add an index, the same
procedural code will now make use of that index (or rather, the
procedural code is calling methods of objects that are smart enough to
look for indexes to optimize themselves without requiring any changes
in application code).

I only point this out becase critics of object databases really need
to improve their understanding of the capabilities of object languages
and object databases. They just aren't as inflexible as some pundits
would like to claim.

> The optimiser can also take into account the cardinality of tables
> (=number of rows) so that it will produce a different plan for a
> small database and a big database.

PyPerSyst doesn't do this, but it is completely within the realm of
possibilities. All we are really talking about is levels of
abstraction. Nobody should be writing procedural code at such a low
level of abstraction that their code can't be optimized at runtime
based on the presence or absence of indexes, or the size of the data
involved. There is nothing that precludes an ODBMS, or procedural
code, from being able to be optimized at runtime without changing
code.

> An optimiser can even be sensitive to input from users at run time,
> say you have
>
> select name, salary
> from nurses
> where sex = ?
>
> If the parameter given at run time is 'M' then it could be
> advantageous to use an index, but not if parameter is 'F'. This
> decision could be made by the optimiser "on the fly". I hope you get
> the picture...

I hope you get the picture that every example you have given can be
true about procedural code. Here is a very simple example of the
source code for the "find" method of the PyPerSyst Extent class:

def find(self, **criteria):
"""Return list of instances exactly matching all criteria."""
instances = []
names = criteria.keys()
spec = self._makekeyspec(names)
if spec in self._altkeys:
# Use alternate keys as a shortcut.
key = self._makekey(criteria, spec)
d = self._altkeys[spec]
if key in d:
instance = d[key]
instances.append(instance)
else:
# Scan all instances for a match.
for instance in self._instances.values():
match = True
for name, value in criteria.items():
if getattr(instance, name) != value:
match = False
break
if match:
instances.append(instance)
return instances

This find method returns a list of instances matching some criteria
supplied by the application code. A specific example of its use would
look like this:

people = db.root['Person'].find(name='Lauri Pietarinen')

If there is an alternate key index on the name attribute, the find
method will use that as an internal optimization. If one does not
exist, then it will scan all instance of the Person class looking for
a match. Let's assume that today there is no index, and all instances
are scanned. If tomorrow the schema is changed to declare that there
should be an alternate key enforced on the name attribute, then
PyPerSyst will build and maintain an index that will be used to
enforce that constraint. As a side benefit, the find method will also
use that index to optimize its performance. The application code will
not have changed.

> > Other than that, what's wrong with the network (or similar)
> > models? I have not found a good link to such a discussion though
> > I hear it repeated that there are certain classical arguments
> > against them.
>
> In 1974 there was the BIG DEBATE where Codd defended the RM against
> network guys. I wonder if there is a transcript available?
>
> I think one of Codds arguments was that the number of manipulative
> operators needed in the RM (i.e. insert, update, delete) was much
> lower than in CODASYL (connect, disconnect, etc etc...)

Any arguments that are true about CODASYL are not necessarily true
about current ODBMSs, in spite of the fact that they both could be
described as implementing a network model. I have also tried to find
information about the weaknesses of the network model and have not
found much of value. Some of the obvious weaknesses of network
products, such as the difficulty in changing your schema because
pointers were static and tied to the physical location of information
on disk, are easily avoided in modern object databases. For example,
PyPerSyst uses references, but has no problem evolving schemas and
migrating instances from the old schema to the new schema. IMO,
references in modern OO languages aren't as evil as pointers in old
CODASYL products. But I'm still looking for information to confirm or
dispute that opinion.

Thanks for listening. :-)

--
Patrick K. O'Brien
Orbtech http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

lauri.pietarinen@atbusiness.com (Lauri Pietarinen) writes:

Please allow me to take exception to a few of your points.

> If you take a standard SQL-query with sorting and grouping and some
> joins and compare it with hand written "navigational" code you will
> notice that what can be expressed in SQL in 5-10 lines of code will
> require several pages of hand written code for equivalent result.

That really depends on two things: the specific SQL query, the
"navigational" programming language, and the capabilities of the
ODBMS. In general, I would agree that a declarative language is going
to be able to express an operation more concisely than a procedural or
object-oriented language. But a language like Python is very powerful
and expressive. I can do the equivalent of a simple SQL query in
about as many lines of code in Python using the PyPerSyst ODBMS. It
would take an incredibly complex SQL query before I'd get to "several
pages of hand written code". PyPerSyst itself is only several pages
of hand written code. ;-)

> The third issue is that the SQL query can be optimised to a much
> higher degree than procedural code. Say you add a new index. The
> DBMS will/can immediately start using it without any user
> intervention (if it decides to). In the procedural alternative you
> would have to recode your query.

You wouldn't necessarily *have to*. PyPerSyst does not yet have a
declarative query capability. But if you add an index, the same
procedural code will now make use of that index (or rather, the
procedural code is calling methods of objects that are smart enough to
look for indexes to optimize themselves without requiring any changes
in application code).

I only point this out becase critics of object databases really need
to improve their understanding of the capabilities of object languages
and object databases. They just aren't as inflexible as some pundits
would like to claim.

> The optimiser can also take into account the cardinality of tables
> (=number of rows) so that it will produce a different plan for a
> small database and a big database.

PyPerSyst doesn't do this, but it is completely within the realm of
possibilities. All we are really talking about is levels of
abstraction. Nobody should be writing procedural code at such a low
level of abstraction that their code can't be optimized at runtime
based on the presence or absence of indexes, or the size of the data
involved. There is nothing that precludes an ODBMS, or procedural
code, from being able to be optimized at runtime without changing
code.

> An optimiser can even be sensitive to input from users at run time,
> say you have
>
> select name, salary
> from nurses
> where sex = ?
>
> If the parameter given at run time is 'M' then it could be
> advantageous to use an index, but not if parameter is 'F'. This
> decision could be made by the optimiser "on the fly". I hope you get
> the picture...

I hope you get the picture that every example you have given can be
true about procedural code. Here is a very simple example of the
source code for the "find" method of the PyPerSyst Extent class:

def find(self, **criteria):
"""Return list of instances exactly matching all criteria."""
instances = []
names = criteria.keys()
spec = self._makekeyspec(names)
if spec in self._altkeys:
# Use alternate keys as a shortcut.
key = self._makekey(criteria, spec)
d = self._altkeys[spec]
if key in d:
instance = d[key]
instances.append(instance)
else:
# Scan all instances for a match.
for instance in self._instances.values():
match = True
for name, value in criteria.items():
if getattr(instance, name) != value:
match = False
break
if match:
instances.append(instance)
return instances

This find method returns a list of instances matching some criteria
supplied by the application code. A specific example of its use would
look like this:

people = db.root['Person'].find(name='Lauri Pietarinen')

If there is an alternate key index on the name attribute, the find
method will use that as an internal optimization. If one does not
exist, then it will scan all instance of the Person class looking for
a match. Let's assume that today there is no index, and all instances
are scanned. If tomorrow the schema is changed to declare that there
should be an alternate key enforced on the name attribute, then
PyPerSyst will build and maintain an index that will be used to
enforce that constraint. As a side benefit, the find method will also
use that index to optimize its performance. The application code will
not have changed.

> > Other than that, what's wrong with the network (or similar)
> > models? I have not found a good link to such a discussion though
> > I hear it repeated that there are certain classical arguments
> > against them.
>
> In 1974 there was the BIG DEBATE where Codd defended the RM against
> network guys. I wonder if there is a transcript available?
>
> I think one of Codds arguments was that the number of manipulative
> operators needed in the RM (i.e. insert, update, delete) was much
> lower than in CODASYL (connect, disconnect, etc etc...)

Any arguments that are true about CODASYL are not necessarily true
about current ODBMSs, in spite of the fact that they both could be
described as implementing a network model. I have also tried to find
information about the weaknesses of the network model and have not
found much of value. Some of the obvious weaknesses of network
products, such as the difficulty in changing your schema because
pointers were static and tied to the physical location of information
on disk, are easily avoided in modern object databases. For example,
PyPerSyst uses references, but has no problem evolving schemas and
migrating instances from the old schema to the new schema. IMO,
references in modern OO languages aren't as evil as pointers in old
CODASYL products. But I'm still looking for information to confirm or
dispute that opinion.

Thanks for listening. :-)

--
Patrick K. O'Brien
Orbtech http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

In article , Costin Cozianu
writes
>> select --+leading(d) use_nl(e) index(e deptref_ix)
>> E.Name FROM Emp E, Dept D
>> WHERE E.Deptref = REF(d) AND D.Name = 'shoe';
>>
>> did follow the access path that I wanted to. Of course, it still remains to
>> see the more challenging task solved with the hints removed.
>>
>> It is optimizer that is always a problem:-)
>>
>>
>
>Well, I'd be least concerned about the optimizer. If you're using Oracle , I'd
>lookup the documentation for the cute name
>
> IS_DANGLING
>
>Cool stuff.

I'd just use a database that doesn't NEED an optimiser :-)

Cheers,
Wol
--
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
Witches are curious by definition and inquisitive by nature. She moved in. "Let
me through. I'm a nosey person.", she said, employing both elbows.
Maskerade : (c) 1995 Terry Pratchett