Re: Retreiving multiple data sets
- From: Stéphane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 29 Jan 2015 12:40:49 -0600
Lou,
+1 to what Lothar said. I would add that, since you are returning
data to users, you are, presumably, either building a HTML page or
perhaps some JSON or XML to return via an Ajax call. In all cases,
nothing will be returned before you have retrieved the very last row in
your data set, and therefore the faster you get your set, the better.
Stéphane Faroult
On 29/01/15 12:34, Lothar Flatz wrote:
Hi Lou,
as my former group RWPG puts it: straight select is always the
fastest. I think this is correct 99% of the time. I come across some
exceptions where pipeline table functions are faster.
Straight sql and all in one go sounds like a great idea. Morphing
rows sounds dangerous to me. Any tricking is normally turning back on
you.
These is one of the requests where more background would be helpful.
Or, lets put it like that: a lot more background. Your question seems
to indicate a difficult design task.
I don't think we can discuss it adequately on high level. Put in your
position I would get a tuning and design specialist on site. It will
certainly pay off.
Thanks and good luck!
Lothar
On 29.01.2015 19:21, avramil@xxxxxxxxxxxxxxx wrote:
Hi folks,
A new project has come our way that will require a decent amount of
SQL testing and tuning. We thought we would ask the community for
some suggestions on best approaches/solutions.
Among other things, it’s looking like we will have to send result
sets of several thousand records every 30 seconds to user browsers.
Those few thousand records will be collected from multiple tables via
UNIONs and JOINs.
We are considering several approaches:
Straight SELECT statements
Using stored procedures to return result sets
PIPELINED functions
Bulk operations
A "regular view / views"
Materialized views
Possibly compressing the result set for network transmission (if this
isn’t already done automatically)
Other ideas…
Further background: It’s an 11.2.0.3 database with over 400 million
records spread out over several schemas. It receives 3.5 million
updates per day. Most records contain alarm data. The primary goal
will be to retrieve uncleared alarms, plus alarms that were updated
in the last 5 minutes. Right now, there are 41,000 uncleared alarms,
which are part of the 240,000 alarms that were updated in the last
five minutes. We would be returning subsets of this data.
The developers (and we) are looking for a straightforward way to
retrieve everything in one shot – though that can mean, on our side,
morphing records from one table to look like those in another.
Thanks,
Lou Avrami
--
Other related posts:
