How to query across schemas [message #293935] |
Tue, 15 January 2008 13:21  |
DaveyBob
Messages: 1 Registered: January 2008 Location: Atlanta, GA
|
Junior Member |
|
|
Our database holds each of our client's data in its own schema. But we have an application that needs to look for some pending work to do across those schemas.
To find that work in a single schema, I would do something like SELECT COUNT(*) FROM MSGWORK WHERE STATUS='PENDING' . If the result > 0, I have work to do in that schema.
But, I need a fast way (performance is very important) to query the MSGWORK table across every client schema in the database to find out which schemas have work. There may ultimately be 250 schemas in the database, so trying all 250 one after another will not work.
Likewise, any sort of query that joins across all the schemas doesn't seem like it will perform well either.
Help me out...
|
|
|
Re: How to query across schemas [message #293937 is a reply to message #293935] |
Tue, 15 January 2008 13:27   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
No way I can think of other than to make a single schema with a VIEW based on all the tables via a UNION ALL. Add a column to this view for schema owner. But this will probably break your app.
|
|
|
|
Re: How to query across schemas [message #293969 is a reply to message #293935] |
Tue, 15 January 2008 16:32   |
TimJF
Messages: 11 Registered: August 2007
|
Junior Member |
|
|
Hey mate!
I know you said you didn't want to check all tables, but perhaps there a way to do it without having huge performance issues?
I know for me, getting my DBA to add a view would be next to impossible. So I get creative and make things like the code below.
Provided you've got access to SYS.ALL_TAB_COLUMNS I think this might help...
Tim.
DECLARE
-- Options
-- Make sure you review v_search_value datatype mate.
v_search_value CONSTANT CHAR(10) := 'Pending';
v_column_name CONSTANT varchar2(100) := 'STATUS';
v_SQL varchar2(200);
TYPE cur_typ IS REF CURSOR;
-- Define a cursor variable
v_emp_cursor cur_typ;
v_counter number;
BEGIN
FOR L_CUR IN
(
SELECT DISTINCT atc.OWNER||'.'||TABLE_NAME AS OWNER_TABLENAME
FROM SYS.ALL_TAB_COLUMNS atc
WHERE upper(atc.TABLE_NAME) = 'MSGWORK'
)
LOOP
--dbms_output.put_line(l_cur.table_name);
v_sql := 'SELECT count(*) as counter FROM '
||l_cur.OWNER_TABLENAME
||' WHERE '
||v_column_name
||' = :search_value';
OPEN v_emp_cursor FOR v_SQL using v_search_value;
LOOP
FETCH v_emp_cursor INTO v_counter;
EXIT WHEN v_emp_cursor%NOTFOUND;
IF v_counter > 0 THEN
dbms_output.put_line(l_cur.OWNER_TABLENAME);
END IF;
END LOOP;
CLOSE v_emp_cursor;
END LOOP;
END;
|
|
|
|
Re: How to query across schemas [message #294004 is a reply to message #293938] |
Wed, 16 January 2008 00:17  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Depending on the business rules that define the need for "work" and the necessity to run them at exactly the right moment etc, you could think about having a central table in which you insert records from all other schemas. The inserted record would indicate the need for that schema to do "work".
The insertion could take place in a trigger.
As stated, the workability of this is highly dependant on the (complexity and frequency of) the business rules defining the need for work.
|
|
|