Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 9i issue (PL/SQL)
Oracle 9i issue [message #399171] Tue, 21 April 2009 00:32 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

I have 100 master tables in oracle.In each master there is a column name ID,code.There is a sequence in each master table which refers to ID Column.If I wanted to find out max(id) of one master table.I am writing the script as
sql> select max(id) from mst_stddes;
max(id)
---------
100

1 row selected.

I wanted to find out the max(id) of every master table.Is there any data dictionary view provided by Oracle which would help us to get the max(id) of every master table?.

Appreciate your help on the above.?

Regards
Re: Oracle 9i issue [message #399178 is a reply to message #399171] Tue, 21 April 2009 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No there is none.
And why is this an "Oracle 9i issue"? Can't you find a more meaningful title?

Regards
Michel
Re: Max(ID) of every table. [message #399238 is a reply to message #399178] Tue, 21 April 2009 04:33 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

What is the possible way of retrieving the max(id) of 100 master table in a query?
Is there any way we can retrieve the max(id) of every record

One method is
e.g:-
select 'table_name',max(id) from table_name
union
select 'table_name1',max(id) from table_name1
union
select 'table_name2',max(id) from table_name2
union
select 'table_name3',max(id) from table_name3;

Here table_name,table_name1,table_name2,table_name3 are the master tables


Can you please suggest me the optimal way to find the max(id) of 100 master tables in a query?

Appreciate your help on the above.?

Regards
Re: Max(ID) of every table. [message #399242 is a reply to message #399238] Tue, 21 April 2009 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no other way than to select all tables.

Regards
Michel
Re: Max(ID) of every table. [message #399245 is a reply to message #399242] Tue, 21 April 2009 04:58 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Thank you very much

Regards
Re: Max(ID) of every table. [message #399246 is a reply to message #399245] Tue, 21 April 2009 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But if all tables have an index on its id column this should be very fast (less than 1s).

Regards
Michel
Re: Max(ID) of every table. [message #399248 is a reply to message #399246] Tue, 21 April 2009 05:12 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I would also suggest to use Union All instead of Union in your query to avoid the uneccessary Distinct set operator on the result set.
Re: Oracle 9i issue [message #399249 is a reply to message #399171] Tue, 21 April 2009 05:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use User_Tables and User_Tab_Columns to find out all the tables that have a column called ID in them, and automatically generate the SQL to be run from this.
Previous Topic: External Table
Next Topic: Tree View Query
Goto Forum:
  


Current Time: Sun Dec 04 18:53:46 CST 2016

Total time taken to generate the page: 0.08247 seconds