Hi Jorge and all, As for me, obvious way to solve this - it's use temporary tables. But you can't. Why? I'm too think that answer to this question is out of scope of this list. But this is main thing, as for me.
I think it's not normal, when developer want - and not just want, but have to - have to use temporary tables, and can't. Why this would be normal? You just trying to use some tool to solve your task, and have restrictions to use it in full. This is like when you need to use hammer, but you have restriction to not touch it - then how you can use it at all? Or like you need to write script in Perl, but can't use hashes, arrays and ref's - great conditions, isn't? Obviously, you can't create temporary tables because you have restrictions from your DBAs. And obviously, they made such restrictions to acheive some goal. Most probably, they trying to save CPU and/or RAM on db server. Well, that's great goal. But if they so smart, then may be will be better to power off db server? - This will save lot of CPU and RAM, obviously. Or even better - then let them to solve your task, ok? Try to understand: what you trying to do it's like crooked nail. You can't use it forever and keep thinking that everything is fine. Or, if it's fine for you, then obviously you are handicapped person. And if it's so, then nobody can't ask you to do something that can do any non-handicapped person. I'm sorry if I was too direct and may be looks careless in terms. I don't wanted to offend anybody, and this is only because my level of English knowledge. Hope you can re-think your situation and solve this puzzle successfully. Good luck! 2011/10/22 Jorge Gonzalez <[email protected]> > 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. > > -- > > *Jorge González Villalonga* > Director Técnico > > *DAIKON Integración y Desarrollo S.L.* > Telf: (+34) 91 188 08 28 > Fax: (+34) 91 632 65 42 > *www.daikon.es* > > _______________________________________________ > 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] > -- Sincerely yours, Oleg Kostyuk (CUB-UANIC)
<<logo-daikon-email.png>>
_______________________________________________ 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]
