Jump to content

skywalker

Members
  • Content count

    2
  • Joined

  • Last visited

Community Reputation

0 Neutral

About skywalker

  • Rank
    Newbie
  1. Hi, I am using VARRAY in my stored procedure. To use it in select statement I have to TABLE function as shown below. SELECT * FROM TABLE(bulk_coll_arr) I am using this TABLE function with bulk_coll_arr varray at two places. So this TABLE function is being used invoked two times. Is there any way to store the output of this TABLE(bulk_coll_arr) in some pl/sql variable so that I can using pl/sql variable in select statement and TABLE function will invoked only once? I am looking for something like this... temp_tab = TABLE(bulk_coll_arr); select * form temp_tab; Can you please explain what TABLE function does internally to convert varray to virtual or in memmory table? Will it cause a performance issue if used with an array of size 100k?
  2. skywalker

    Delete query very slow

    Hi, I am executing following sql from stored procedure and it is taking more than 20 minutes to delete 5 million records from table. Can some one please advise? DELETE FROM AGMS_HOST_ASSET_GROUP WHERE HOST_ID IN (SELECT ID FROM AGMS_USER_HOST UH WHERE UH.IP_INT IN (SELECT * FROM TABLE(:B2 ) ) AND UH.SUBSCRIPTION_ID = :B1 ) AND ASSET_GROUP_ID IN (SELECT * FROM TABLE(:B3 ) ) The binding (:B2) is an oracle array which has 10_000 ids and I am collecting these ids in batch on 10_000 only. The binding (:B3) is an oracle array but it has only 5-10 ids. The table AGMS_HOST_ASSET_GROUP has 12 million rows where as AGMS_USER_HOST has 70 million rows. Execution plan of this sql is attached below. After gather stats: EXEC dbms_stats.gather_table_stats('GRC','AGMS_USER_HOST',cascade=>TRUE); EXEC dbms_stats.gather_table_stats('GRC','AGMS_HOST_ASSET_GROUP',cascade=>TRUE);
×