You Asked
1. Some Oracle-supplied functions, like GREATEST, LEAST, COALESCE, allow an arbitrary number of parameters to be passed in. Is there some convenient means of creating user-defined functions with an arbitrary number of arguments? (If I put the function in a package, I know I could overload the function with increasing number of arguments, but that's rather verbose).
2. When partitioning a table, is there a minimum recommended # of records per partition? I just finished reading
</code>
https://asktom.oracle.com/Misc/oramag/on-rownum-and-limiting-results.html <code>
and it doesn't seem to address the granularity of partitions. In your original book Expert 1-on-1, you says say that "Partitioning is designed to facilitate the management of very large tables and indexes" a "100 GB table for example, [if] it is partitioned into fifty 2 GB partitions.".
What is the threshold of "very large"?
For example, we have a partitioned table with 2.8 million records, about 2G in size. It's partitioned by month: the largest partition has just 17,000 records. I've never seen any query invoke the partition key so I see no obvious performance benefit (generally the queries use a global index, then get the data). I can't imagine partitioning improves managability either. I'd be inclined to unpartition such tables until some far future time.
As I recall, a DBA at a previous company of mine experimented, and reported that one million was the minimum size of a partition that contributed positively to performance. The fact table he experimented upon was only a couple of million records in size (like the one I mentioned). He said that for full table scans across all the data, which were the most common query, paritions slowed the scans. He de-partitioned the table.
Thank you!
and Tom said...
1) well, they all have limits too...
ops$tkyte%ORA10GR2> declare
2 l_cursor sys_refcursor;
3 l_query long := 'select greatest(1';
4 l_output number;
5 begin
6 for i in 1 .. 254
7 loop
8 l_query := l_query || ','||i;
9 end loop;
10 l_query := l_query || ') x from dual';
11
12 execute immediate l_query into l_output;
13 dbms_output.put_line( l_output );
14 end;
15 /
254
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> declare
2 l_cursor sys_refcursor;
3 l_query long := 'select greatest(1';
4 l_output number;
5 begin
6 for i in 1 .. 2000
7 loop
8 l_query := l_query || ','||i;
9 end loop;
10 l_query := l_query || ') x from dual';
11
12 execute immediate l_query into l_output;
13 dbms_output.put_line( l_output );
14 end;
15 /
declare
*
ERROR at line 1:
ORA-00939: too many arguments for function
ORA-06512: at line 12
all of them do, so you can simply have a fixed number of arguments (as many as you like) and just default then, the following function could be called as:
f()
f(1)
f(1,2)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function f( p_arg1 in number default null,
2 p_arg2 in number default null )
3 return number
4 as
5 begin
6 return null;
7 end;
8 /
Function created.
Using a collection, you can do it even "easier" if you ask me, an array is a good way to pass as many things as you like
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type varargs as table of varchar2(4000)
2 /
Type created.
ops$tkyte%ORA10GR2> create or replace function f( p_args in varargs default varargs() ) return number
2 as
3 begin
4 for i in 1 .. p_args.count
5 loop
6 dbms_output.put_line( 'varargs('||i||') = ' || p_args(i) );
7 end loop;
8 return null;
9 end;
10 /
Function created.
ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2> exec :x := f( varargs(1,2,3) );
varargs(1) = 1
varargs(2) = 2
varargs(3) = 3
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec :x := f( varargs(1,2,3,4,5,6) );
varargs(1) = 1
varargs(2) = 2
varargs(3) = 3
varargs(4) = 4
varargs(5) = 5
varargs(6) = 6
PL/SQL procedure successfully completed.
2) absolutely... NOT
partitioning isn't not necessarily about size (although if my segments where nearing say the 10gig size, I'd think about using partitioning for EASE OF ADMINISTRATION).
Very large is very subjective. What is your threshold for pain?
the number of records - not relevant.
Your goal for partitioning (ease of admin OR partition elmination in a warehouse OR increased availability - pick one, and typically only one - and then use partitioning to achieve it) - priceless.
I'm laughing at that one million number. sorry, but that is just funny.
First, partitioning is not entirely about performance. Many times you have to be really careful not to NEGATIVELY impact performance (eg: it can be done so that performance is not affected at all - BUT it is easier to administer or more available).
I doubt he tested very well. For a table of any size, the partitions are just not going to affect full scan performance if you have to full scan all of the data. It is like the "single extent myth".
I do not concur with the findings at all.
But - you do need a reason to use partitioning, it is NOT fast=true, it is a tool, nothing more, nothing less.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment