Re: Navigation question

On Feb 18, 2:27 pm, "Walt" <wami..._at_verizon.net> wrote:
> "dawn" <dawnwolth..._at_gmail.com> wrote in message
>
> news:1171655147.355068.254690_at_p10g2000cwp.googlegroups.com...
>
>
>
> > On Feb 16, 11:16 am, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> > > On Feb 16, 8:34 am, "dawn" <dawnwolth..._at_gmail.com> wrote:
>
> > > > On Feb 16, 9:48 am, "Marshall" <marshall.spi..._at_gmail.com> wrote:
>
> > > > They
> > > > have the same number of queries. How many times do I have to say that
> > > > I am not, not, not, absolutely not suggesting that we do more round
> > > > trips or split queries.
>
> > > You split a query. You had tin and you needed contacts, and you
> > > did it with two queries. I did it with one. Navigation splits queries.
> > > The performance of navigational approaches relative to declarative
> > > approaches ranges from as-good-as to unboundedly worse. Yes,
> > > you kept the total query count the same. However you introduced
> > > an explicit ordering between the two queries that wasn't there
> > > before, and that isn't free.
>
> > > Here's your approach:
>
> > > select name, companyid from companies where tin = XXX;
> > > mycompany = companyid;
> > > select emailaddress from contacts where companyid = mycompany;
>
> > > vs.
>
> > > select name from companies where tin = XXX;
> > > select emailaddress from contacts natural join companies where tin =
> > > XXX;
>
> > > Here's a possible execution scenario:
>
> > > no index on tin
> > > no index on contacts.companyid
> > > system has contacts fully loaded in cache
>
> > > your approach:
>
> > > select name, companyid from companies where tin = XXX;
>
> > > 1) requires a full scan of companies because no index on tin
> > > 2) loads companies from disk, pushing contacts out of cache
> > > 3) full scan of companies
>
> > > select emailaddress from contacts where companyid = mycompany;
>
> > > 3) loads contacts from disk
> > > 3) full scan of contacts
>
> > > my approach:
>
> > > I send these two queries to the system:
>
> > > q1: select name from companies where tin = XXX;
> > > q2: select emailaddress from contacts natural join companies where tin
> > > = XXX;
>
> > OK, now we are getting somewhere. My knowledge must be dated and/or
> > with the wrong tools. I was using ODBC against a DBMS where SQL was
> > not its only language. You are saying that you can pass in two
> > queries together and the tools would not execute them sequentially,
> > but figure them out together. That is definitely a piece I was
> > missing. I only played around with jdbc and mysql a little bit a few
> > years back, but perhaps if I read the current jdbc and mysql
> > information I would understand this. Are you passing both SQL
> > statements in the same jdbc statement?
>
> I thought you lead several large projects where Oracle was the environment.

I've been directly involved in a couple with Oracle, one was OLTP read/ write and the other BI (using OLAP tools, for example). If I said "several" were specifically Oracle (did I?), I exaggerated, which I would not have done on purpose. Additionally, Oracle was only one target environment so the software was written in such a way as to permit persistence in both UniData (Pick) and Oracle. I was not a programmer (but had employees who were), but did do some R&D and some training ("those who can't do, teach" perhaps), in addition to high level design. I have done a small amount of programming with Oracle in an R&D effort that was not production stuff (maybe I clumped that in there to get the several?)

My intro to Oracle was around 1987, when a colleague did a feasibility study with it. I was not a programmer on that project either. I first touched a product that declared itself to be a relational database when I "played with" RBase, likely late 80's. Most of my heads-down programming years ('77-88) were spent with IMS (with COBOL/ CICS) and indexed sequential files on Pr1mes and IBM iron. I became a manager at the end of '88 and since that time have had various projects with hands-on work, but most of my hands-on effort for the past almost two decades has been in the off-hours or on Friday afternoons (which I consider my "hands-on" time) just to keep at least a little bit current. I am not currently doing any project including any primarily SQL-DBMS, although I work with a s/w product that has Oracle as one of its target DBMS's and occasionally I am involved in such, but not as a developer.

> The above seems to deny that.
> Am I misreading you?

Yes, or I misspoke. Feel free to point me to a statement you think is incorrect. The above is as accurate as my memory is right now. --dawn Received on Mon Feb 19 2007 - 03:59:19 CET