|
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. 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. 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. 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. --
|
_______________________________________________ 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]
