3

I have really interesting problem.

I have user-defined table type

create or replace TYPE "TABLE_OF_NUMBER" AS TABLE OF NUMBER

I have table with millions of rows - calls

I have pl/sql block which works okay

declare
  v_calls     TABLE_OF_NUMBER;
begin
  select id bulk collect
  into v_calls
  from calls;
end;

I have another pl/sql block which gives me an error

declare
  v_calls     TABLE_OF_NUMBER;
  t1 number;
begin
  select id bulk collect
  into v_calls
  from calls;

  select count(*) into t1 from table(v_calls);
end;

ORA-22813: Operand value exceeds system limits.

And I found an explanation of error:

Cause: Object or Collection value was too large. The size of the value might have exceeded 30k in a SORT context, or the size might be too big for available memory.

My actual goal is to get this:

declare
  v_calls     TABLE_OF_NUMBER;
  v_events TABLE_OF_NUMBER;

begin

  select id bulk collect
  into v_calls
  from calls;

  select id bulk collect
  into v_events 
  from events
  where call_id in (select * from table(v_calls));

end;

But I already find out that problem command is select * from table(v_calls)

I didn't find anything about restrictions for TABLE(user_defind_table) command.

Does anyone know what is the problem and how can I avoid it?

Tatiana
  • 1,489
  • 10
  • 19
  • As from this [link](http://www.dba-oracle.com/t_ora_22813_operand_value_exceeds_system_limits.htm) they are sugesting to `Action: Choose another value and retry the operation` .anyhow ill try your command at my pc and ill check if it will work – Moudiz Oct 23 '15 at 07:42
  • `v_calls` is already a table, so you should directly select with `select * from v_calls` – Troopers Oct 23 '15 at 07:45
  • @Moudiz yep, I found that as well but not actually understand - which `another value` can I choose :) – Tatiana Oct 23 '15 at 07:46
  • @Troopers no, that's not true. It is user defined, so I can use only table(v_calls) – Tatiana Oct 23 '15 at 07:47
  • Why you do you use nested table? Why don't you like this simple SQL query "select id from events where call_id in (select id from calls);"? – diziaq Oct 23 '15 at 07:57
  • @Tatiana I have a question why you didnt declare the type in such way DECLARE TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; – Moudiz Oct 23 '15 at 07:57
  • @diziaq it is only part of procedure. It takes values from v_events table next and so on and so on. Like next step is `select * from bills where event_id in (select * from table(v_events) )`. So it will be really huge code if I will use nested selects – Tatiana Oct 23 '15 at 07:59
  • @Moudiz according to [this](http://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement) prior to Oracle 12C I can select only from type which is defined at schema level (not at package or procedure level). And I have Oracle 11.2 – Tatiana Oct 23 '15 at 08:01
  • 1
    @Tatiana, it's a typical problem for SQL. Don't use nested selects - use joins. – diziaq Oct 23 '15 at 08:02
  • @Tatiana you are facing the error when you executing it right ? your not having the error on compiling time – Moudiz Oct 23 '15 at 08:07
  • @Moudiz when executing, yes – Tatiana Oct 23 '15 at 08:09
  • @Tatiana what happend did it work ? – Moudiz Oct 23 '15 at 11:51
  • @Moudiz what exactly? I tried `bulk collect limit ` bit it degrades performance. So I am thinking on some other ways... – Tatiana Oct 23 '15 at 11:54
  • @Tatiana the answer of Stawros , and yes you should have a good hardwares to benefit from bull collect , even using Jobs executing several queries needed a good cpu and rams – Moudiz Oct 23 '15 at 11:59
  • 1
    @Moudiz and I found an answer about restriction for `table(...)` [here](http://stackoverflow.com/questions/24191257/what-is-maximum-rows-count-in-oracles-nested-table) So at least I know the bottle neck... – Tatiana Oct 23 '15 at 12:05
  • @Moudiz if you still interested in - I tried option with temp tables, performance still really poor, so I have to rewrite whole procedure some how and change logic. Since it is package for backup, I will use `exchange partition` for faster backup data... – Tatiana Oct 27 '15 at 15:20
  • @Tatiana I have never used exchange partition before, I will do an example by tomorow for sure. I was intersted in your question because I am doing similar thing, I am searching for a fast way to archive huge data( 100 millions of data every week) for several tables. for now i am doing a loop and do a bulk delete of 1 million every loop. but I am doing a search if If I can use multithreading using java. Hope it works – Moudiz Oct 27 '15 at 15:35

2 Answers2

3

Possible solution:

declare
  v_calls     TABLE_OF_NUMBER;
  v_events TABLE_OF_NUMBER;
  v_events_tmp TABLE_OF_NUMBER;
  cursor cur_calls is
  select id
  from calls;
begin
  open cur_calls;
    loop
       fetch cur_calls 
        bulk collect 
        into v_calls 
       limit 1000;
       exit when v_calls.count = 0;

      select id bulk collect
      into v_events_tmp 
      from events
      where call_id in (select * from table(v_calls));

      v_events := v_events_tmp MULTISET UNION v_events;

    end loop;
  close cur_calls;
end;
Stawros
  • 918
  • 1
  • 10
  • 20
  • So I tried this solution, but performance degrades dramatically, so for my case it is not an option. But thank you for your answer – Tatiana Oct 27 '15 at 15:18
1

The restriction for select * from table(v_calls) could be found here

I find few solutions:

  1. As @Stawros said - use bulk collect ... limit In my case performance degrades dramatically, so it isn't solution for me

  2. Use temporary tables instead of user defined tables. It helps avoid error, but performance still really poor

  3. Change whole code (logic) - unfortunately, that is my way.

Community
  • 1
  • 1
Tatiana
  • 1,489
  • 10
  • 19