Home » SQL & PL/SQL » SQL & PL/SQL » How to query across schemas (Oracle V10)
icon5.gif  How to query across schemas [message #293935] Tue, 15 January 2008 13:21 Go to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 #293938 is a reply to message #293935] Tue, 15 January 2008 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Whatever you will do it, you will have to query the 250 tables.
So the conclusion is that you design is not correct.

The only way I can see you may get out of this is to build a materialized view that precomputes the result and is refreshed at each commit.
Or you can build a Streams environment but it is not an easy one.

Regards
Michel
Re: How to query across schemas [message #293969 is a reply to message #293935] Tue, 15 January 2008 16:32 Go to previous messageGo to next message
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 #294001 is a reply to message #293969] Tue, 15 January 2008 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why all_tab_columns and not all_tables?

This only works if you use SQL*Plus and have a human being watching the result, I don't think this is OP wants.

This is Oracle7 PL/SQL programming, in the current days there are more efficients ways to do it.

This still queries all the tables and this is what OP tries to avoid.

Regards
Michel
Re: How to query across schemas [message #294004 is a reply to message #293938] Wed, 16 January 2008 00:17 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: umlaut when converting blob to varchar
Next Topic: Query Help - Group By or Other solution?
Goto Forum:
  


Current Time: Sun Dec 04 08:59:57 CST 2016

Total time taken to generate the page: 0.07437 seconds