| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.theory -> Re: Navigation question
Walt wrote:
> "dawn" <dawnwolthuis_at_gmail.com> wrote in message
> news:1171853959.171262.207070_at_h3g2000cwc.googlegroups.com...
>
>>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
>>>>>>I am not, not, not, absolutely not suggesting that we do more
>>>>>>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
>>>>>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
>>>>>= 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
>>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.
We have identified another cause for the oh, so apparent brain damage!
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
In her role as manager, the chance she will ever encounter a well run project is somewhere between nil and next to none.
> Once you get enough "anectodal" evidence to contradict your long held
> illusions, you may no longer need the "empirical evidence" you have been
> demanding in order to see the merits of the tools you have been deriding.
> But I don't think that's going to happen.
I know it won't. Learning first requires intellectual honesty. Received on Mon Feb 19 2007 - 07:07:55 CST
 |
 |