-2

I have use the bulk collect in purchase_detail table , within this table have on 30-million records. I wand to maximum how much of value use in limit clause.

example : fetch cur_name bulk collect into pur_var limit 5000;/----Maximum how much LIMIT ?---/

William Robertson
  • 15,273
  • 4
  • 38
  • 44
sygops
  • 19
  • 2

1 Answers1

3

From a syntax point of view, the limit value can't exceed 2147483647 as it's a pls_integer.

However, whether your database server is physically able to hold 2 billion rows in PGA memory will depend on the resources available. It is unlikely to be a good idea.

From a performance perspective, the optimal value will depend on the size of the record and the likely number of users executing the code concurrently, and there may not be much improvement after the mid 100s.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • Can i set row_limit NUMBER := 10000; because in my explicit cursors with array fetching PROCEDURE took more than 13 hr to be completed Thanks in advance – Syan Dec 29 '21 at 14:13
  • Yes, although it may be worth some trial and error to find the optimal setting. – William Robertson Dec 29 '21 at 14:16