Print Story Any Oracle DBAs know how to...
Help!
By anonimouse (Thu Aug 30, 2012 at 07:23:19 AM EST) fuck oracle, damn oracle, stupid bloody database (all tags)
... get a count of the number of results contained in a subquery to an outer query?


Ok, so I have this system which returns a number of results in a subquery, which I then want to further filter by looking for search terms, so I have

select * from (
  select * from joined tables where complex_query
  ) where query.column like '%text%'

I'd really like to avoid running a second query to get the number of results from the inner query
so I don't want to do:

  select count(1) into total_results from joined_tables where complex_query

So is there any way of getting the total number of results from the inner query without doing the statement twice?
< this summer still sucks | I'll put a jihad on you, too >
Any Oracle DBAs know how to... | 9 comments (9 topical, 0 hidden) | Trackback
A bit more of a clear example would be better by ks1178 (2.00 / 0) #1 Thu Aug 30, 2012 at 07:48:25 AM EST
But I think what you're asking to do, is that you need to do an query that will return results that you want to have outputed.

Along with that output, you want to also return a count of the returned data, but on a subset based on some additional filter criteria. But you don't want to have to run the inner query in two separate statements (one to get the initial results, then the second to get just the count of the subset) Correct?

Sounds about right by anonimouse (2.00 / 0) #2 Thu Aug 30, 2012 at 07:52:28 AM EST
I want to output  a statement saying this is
"results 1..30 (120 filtered) from 5,320 entries"

Its the last value (5,320 in the above) I want to retrieve along the filtered results.


Girls come and go but a mortgage is for 25 years -- JtL
[ Parent ]
before I try to come up with something aweful by ks1178 (4.00 / 1) #3 Thu Aug 30, 2012 at 08:18:16 AM EST
I assume, the main goal that you have, is that you don't want to execute complex query twice.

On the other hand, to get away from doing that, the easiest (and most straight forward way to do this) is to break up the execution in 2 parts.

First do complex query (without the filter) and put it in a temp table (or maybe a materialized view).

Then you can do 2 separate queries, one doing a select count on the temp table, and then the next doing the select * with the filter.

If you don't have the capability to create temp tables, or materialized views, let me know, and I'll see if I can think something up that's not super ugly.

[ Parent ]
Don't do anything too hard by anonimouse (2.00 / 0) #4 Thu Aug 30, 2012 at 01:57:52 PM EST
I was asking the question because I was hoping that someone would have an instant answer in their head.

You're probably right that creating temporary tables is a way forward; however unless it's simple there's not much point in doing it, as its simply to prettify the results statement.
I've solved the real problem which was to get the search down from 40 secs to less than a second, so this was just icing on the cake.

I was sorta hoping there was a clever way of using an analytic function to transfer the number of hits into the outer query, even if it added an extra column to the results.


Girls come and go but a mortgage is for 25 years -- JtL
[ Parent ]
It's stupid by Driusan (4.00 / 1) #5 Thu Aug 30, 2012 at 03:58:14 PM EST
But you could run something along the lines of:

select a., (select count() from joined tables where complex_query) as NumRows from (select * from joined tables where complex_query) a where query.column like '%text%';

But you're probably better off with a temporary table and 2 queries.

--
Vive le Montréal libre.

Two solutions I can think of by gazbo (4.00 / 1) #6 Fri Aug 31, 2012 at 04:37:31 AM EST
(Don't use Oracle so I can't be too specific)
  1. Add a window function to the inner query so that each row carries with it the total row count.  I assume Oracle has a window function to do that? If not, it can probably be cobbled together using existing window functions and duct tape.
  2. Create a pl/sql function to run the inner query, then loop over the results to build the actual result set, adding an extra column which is the total row count and keep a count.  The looping isn't inefficient if you order the query by the outer search clause because then you can just terminate at the first non-match.
However you do it, I'd have thought you'd have to end up with an extra column in the final results with the total count duplicated on all rows.

I recommend always assuming 7th normal form where items in a text column are not allowed to rhyme.

I think you sortof nailed it by anonimouse (2.00 / 0) #7 Tue Sep 04, 2012 at 07:57:55 AM EST
I don't think you can use count(*) directly but you can use
count(1) over() ct
as a column

so I can have a ct0 count for the inner query results and a ct1 for a filtered outer query count.

I think the only flaw is if zero rows are returned....


Girls come and go but a mortgage is for 25 years -- JtL
[ Parent ]
Urgh by gazbo (4.00 / 1) #8 Tue Sep 04, 2012 at 10:18:26 AM EST
I hadn't thought about that case - there's pretty much by definition no clean way to do it, because in order to get the data you MUST return a row, and therefore the result set no longer necessarily matches the search condition - which is semantically a bit screwy.

I can think of a way of returning all matches followed by 1 non-match (with a bool field saying if each row is a match or not), so that all of the data is there but the application layer would have to do a small amount of processing.  I have no idea if that's possible in your usage, but at least as it's only one extra row you don't need to worry about transferring too many rows - assuming that's an issue.


I recommend always assuming 7th normal form where items in a text column are not allowed to rhyme.

[ Parent ]
Indeedy by anonimouse (2.00 / 0) #9 Tue Sep 04, 2012 at 10:36:10 AM EST
I'm thinking along the lines of modifying the above to return a single statistics row, but the above solution is most of it.
I may also be happy to run the query for the stats if no rows are returned.


Girls come and go but a mortgage is for 25 years -- JtL
[ Parent ]
Any Oracle DBAs know how to... | 9 comments (9 topical, 0 hidden) | Trackback