On 22/10/2011 11:36, Jorge Gonzalez wrote:
Hi all,
first of all, I think this question is probably offtopic in this list
since it's not DBIx::Class specific, but since I do all my DB
development with DBIx::Class I'd like to ask here first. The question
is probably of interest for others anyway. This is it:
I have read-only access to an Oracle schema. The user is limited, it
can't create temporary tables, views, etc. Just access to some tables.
I have a big table BIGTABLE in this schema (~millions of rows) which I
want to filter by values in a specific field. The number of values to
filter is also big (~40.000), and can't be obtained from other tables,
i.e. I take them out of another data source which is not the Oracle
schema.
Obviously if you could pick the values from somewhere else in the
database you could do
select * from table where column in (select column from another_table)
but it sounds like you cannot do this.
For this I tried the following query: SELECT * FROM BIGTABLE WHERE
FIELD IN (...~40.000 values...). Oracle, though, has a 1000 element
limit for IN clauses, so I have to break the query in some 40 smaller
queries and then process the results afterwards (I call this the
"slice technique" since I break the list in slices with splice :-)
Appart from having to do N/1000 queries insted of 1 (and now N is
40.000 but it will surely grow, so more queries), I can't offload data
processing to the SQL server (which of course would be good) and my
app has to process the data itself.
It would be a long piece of SQL but you could get all the results in one
piece of SQL with:
select * from table where column in (1000 values) or column in (another
1000 values) ...
My question is: has anyone managed to get past this limitation in any way?
One approach could be to load some temporary table with the filter
data and then join BIGTABLE with the temp table, but as I said, my
Oracle user is very limited and can't create temporary tables, much
less load them with data.
Which is probably what I would have done - shame.
I'd very much appreciate any idea for solving this. And again, accept
my apologies if you feel this question is very offtopic.
Thanks in advance.
Regards
J.
Martin
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
