> > From: "Stephane Faroult" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon AM 09:59:39 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: interesting sql question > > > > >----- ------- Original Message ------- ----- > >From: <[EMAIL PROTECTED]> > >To: Multiple recipients of list ORACLE-L > ><[EMAIL PROTECTED]> > >Sent: Mon, 29 Sep 2003 05:19:39 > > > >Im taking a database theory class(no I dont need > >help with my homework). There is an interesting > >query in the book that I have never seen posed > >before. The solution would be hideously slow if > >there was even a moderate amount of data in the > >tables. How would you write it? > > > >Given 3 tables: and columns in the tables: > > > >TABLE: Person > >Primary Key: SID > >COLUMN: NAME > > > >TABLE: BIDS > >Primary Key: BID > >Foreign Key: SID > >FOREIGN KEYT: BOAT_ID > >Column: Date > > > >Boat: > >Primary Key: BOAT_ID > >Column: Color > > > >Find any person who has reserved all the boats. The > > > > > >I dont have the solution with me, but there is a > >'NOT EXISTS', then in the subquery there is a minus > >and a correlated 'where' clause.'. That query > >wouldnt move. > > > >How would you solve this? > > > >Also, according to the 'SQL Standard', SQL is > >supposed to support op codes such as 'ALL' or 'ANY' > >So you can say: > > > >Find all people who are older than any person with > >blue eyes. Or find all the people who are older > >than 'ALL' the people with blue eyes. > > > >Just to reiterate. Not looking for help with my > >homework. My professor isnt an Oracle guy so he > >doesnt know. > > > > I would run an uncorrelated subquery on BOATS to count how many of them we have (mot > likely to be a multimillion row table, and it's just a PK scan), which you can feed > into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and > supposing (which is often the case) that your FK is indexed it doesn't require > anything but another index scan. Which can of course take *some* time if BIDS is > really big but I don't see how to escape a group by here (or anything worse).
Bitmap scan would be the fastest. Ive noticed that counts on those are incredibly fast. So your saying something like: how would you write the query? I dont quite see it. > > Regards, > > Stephane Faroult > Oriole > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephane Faroult > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
