Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

elapsed_time in apex_workspace_activity_log

Keegan_WFeb 20 2018 — edited Feb 22 2018

Trying to get a better understanding of what elapsed_time is to use it in custom charts to show our highest-used APEX apps.

The description in the apex_dictionary is "Elapsed time to generate page source"

So, if you were to sum the elapsed_time for a certain app and time period, this would be the sum of all the times any page in the app was fully loaded (in seconds)?

elapsed time.JPG

Thank you!

Comments

Boneist
Hint: use a correlated subquery in the set part of the update statement, and then use decode/case within the subquery to decide what the output of the column should be, based on whether the the row exists in table1 or not for the linked id from table2.

Edited by: Boneist on 28-Jul-2010 10:05
Amended hint so it's for update, not select (d'oh!)
737905
Answer
Something like:
SQL> update tab_2 x set indi = NVL((SELECT 'MMM' FROM tab_1 WHERE id = x.id),'OOO');

6 rows updated.

SQL> commit;

Commit complete.

SQL> select * from tab_2;

ID         INDI
---------- ----------
A123       MMM
A456       MMM
A678       MMM
A890       MMM
B130       OOO
C180       OOO

6 rows selected.

SQL> select * from tab_1;

ID
----------
A123
A456
A678
A890

SQL> select * from tab_2;

ID         INDI
---------- ----------
A123       MMM
A456       MMM
A678       MMM
A890       MMM
B130       OOO
C180       OOO

6 rows selected.

SQL> 
Marked as Answer by AceNovice · Sep 27 2020
781735
See the solution below
SQL> select * from table1;

ID
----------
A123
A456
A678
A890

SQL> select * from table2;

ID         INDI
---------- -----
A678       
A456       
A123       
A666       
A898       
A890       

6 rows selected

SQL> 
SQL> update table2 t2
  2   set INDI =  (
  3                select decode((select 1 from table1 t1 where t1.id = t2.id),1,'MMM','OOO') from dual
  4               );

6 rows updated

SQL> commit;

Commit complete

SQL> select * from table2;

ID         INDI
---------- -----
A678       MMM
A456       MMM
A123       MMM
A666       OOO
A898       OOO
A890       MMM

6 rows selected
Thanks,
Andy
732249
TABLE1
======
ID
-----
A123
A456
A678
A890


TABLE2
======
ID INDI
----- ----
A123
A456
A678
A890
A666
A898


If ID of TABLE2 matches with ID of TABLE1 then update INDI with 'MMM' else update INDI with 'OOO'. How can i achive this?

FInal result will be....

TABLE2
======
ID INDI
----- ----
A123 MMM
A456 MMM
A678 MMM
A890 MMM
A666 OOO
A898 OOO

One more option for the above solution is u can go with NVL2 as welll....

UPDATE TABLE2 T
SET INDI =
NVL2((SELECT 1 FROM TAB1 WHERE TAB1.ID = T.ID),'MMM','OOO')
AceNovice
Hi All, Thanks for your help, problem is solved.

Now i have 10 milion records to be updated. If i ran this stand alon query, it will cause a memory problem.

How can i write PL/SQL block, which can commint after every 10,000-20,000 records?
781735
Try this
declare           
type t_type is table of rowid index by pls_integer;
l_type t_type;
cursor c1 is select rowid from table2;             
begin
open c1;
loop
fetch c1 bulk collect into l_type limit 10000;
exit when c1%notfound;
forall i in l_type.FIRST .. l_type.LAST
update table2 t2
     set INDI =  (
                  select decode((select 1 from table1 t1 where t1.id = t2.id),1,'MMM','OOO') from dual
                 )
     where t2.rowid = l_type(i);
commit;
end loop;
close c1;
end;  
I have not written any exception block but should write one.

PS: I still think you should use the simple update query.
Thanks,
Andy
Aketi Jyuuzou
I like case expression :D
update TABLE2 a
set INDI = case when exists(select 1 from TABLE1 b
                             where b.ID = a.ID)
                then 'MMM' else 'OOO' end;
6363
ace_friends22 wrote:

Now i have 10 milion records to be updated. If i ran this stand alon query, it will cause a memory problem.
What do mean by memory problem and how do you know it is going to happen? When you measured the update on a few rows what resources did it consume?

>
How can i write PL/SQL block, which can commint after every 10,000-20,000 records?
However you write it it will use more undo, redo and everything else that can be measured and take about twenty times longer.

4339747
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 22 2018
Added on Feb 20 2018
5 comments
1,792 views