Hi all,
Obviating the uncheerful response (I'm not a handicapped person; my
oracle username is a handicapped one), I'll explain the situation so
that everybody can understand why I can't create temporary tables:
1. My customer is a SMALL company (=little negotiation power).
2. They have and agreement with a HUGE company (=big pressure
power), which offers to SMALL company a detailed access to their
data warehouse. This is the Oracle access I'm using. It's a readonly
user which can't do anything on the DB except query _some_ tables.
3. The SMALL company wants to cross information from their systems
(which we manage and to which we have full access) with the HUGE
company's data warehouse.
4. The HUGE company has a stated policy regarding access to their
data warehouse, which forbids creating temporary tables.
5. The HUGE company can't change their policy easily without
breaking their internal quality audits. And they will NOT change it
for the SMALL company. And ther's NO way I can challenge those
quality audits, since I'm an external person for them.
6. As a corollary to the former points, it's pretty clear that the
DB permissions are completely out of my control and my customer's.
They fully depend on a third party which won't allow us to do it.
Full stop.
So:
A. I can't create temporary tables
B. I can't ask the DBAs to allow me to do it (well I can, but
they'll ignore me)
C. I don't have the power to negotiate a change in the HUGE
company's policy.
Regarding the comment "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?" - Well, the concept of "normality"
is pretty dependent on everyone's experience. What is not normal for
you is just one of my daily situations. Sometimes you can bend
things so that you can work better, and some times you can't and
it's you who has to bend to the circumstances to get the work done.
I apologize if my language seems a bit harsh.
Regards
J.
El 22/10/11 13:49, Oleg Kostyuk escribió:
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.
_______________________________________________
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)
_______________________________________________
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]
|
