WL#358: Selecting into multiple tables
Affects: Prototype Only — Status: Un-Assigned
Based on this e-mail from one user (excerpt only): Multitable SELECT (M-SELECT) is similar to the join operation. You select values from different tables, use WHERE clause to limit the rows returned and send the resulting single table back to the originator of the query. MOVIE(id, title) PERSON(id, name) MOVIE_PERSON(mid, pid, role) SELECT m.title, p.name FROM movie m, person p, movie_person mp WHERE m.id = mp.mid AND mp.pid = p.id AND m.title LIKE 'Adams Family%' The difference with M-SELECT is that it would return multiply tables as the result set. SELECT m.* INTO result.movie, mp.* INTO result.movie_person, p.* INTO result.person FROM movie m, person p, movie_person mp WHERE m.id = mp.mid AND mp.pid = p.id AND m.title LIKE 'Adams Family%' 1: The Addams Family movies from the MOVIE table 2: all cast members from the PERSON table 3: required linking rows from the MOVIE_PERSON table This has several advantages over the simple SELECT. 1: Client applications could be much more flexible in displaying or using the results set in different ways. A simple re-sorting for example would not require another query to be run. 2: Queries from multiply tables could still be updateable easily from client apps if they select the keys from all linked tables too. With a simple SELECT if you find an error in a name's spelling you have to form a different query to update that person's name. With the other approach you can provide update functioality automaticcaly in client app development components (for example in Delphi's TDataSet) 3: Stored procedures could return more tables too, and this is really helpful in some situations. 4: Communication is more compact too. A simple SELECT has to transfer each movies' title castmember-count times, with M-SELECT only once. I guess it would ease the load on the server too: less wainting for the net interface to eat all those repeated titles. I writed no SQL server in my life, but I guess as you scan the tables while processing a query and push the values from the base tables into the result set table, it wouldn't hurt that much to fill the apropriate value into the apropriate table. It could even make it easier to optimize queries, since the communication protocoll (and API) supporting the new functionality, would allow you to fill the tables in an order you choose, or from multiple threads. Or for example a linked dictionary table(media type of films [vhs, videodisc, dvd, etc.]) could be sent to the client before the main result data, and while doing the other joins, you could simply forget the dictionary table. I know tha latter example is not valid in all situations, but you have expirience in this field, and I have very little - you'll know how to do it.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.