PL/SQL Tuning (merged 2 threads) [message #274093] |
Sat, 13 October 2007 13:35  |
ravichandran_123
Messages: 13 Registered: October 2007 Location: Bangalore
|
Junior Member |
|
|
Hi,
I need to divide the records in a table into different sets based on the number_of_sets argument passed by the user. If user wants to distribute the transactions in a table to 5 and number of records in the table is 5 million, then the records need to be equally (almost) divided. But the tricky thing here is, if I have 10 transactions for one customer, I need to make sure to process that customer in in set. The bottom line is that the same customer transactions should not be spread accross different sets and need to be processed under on set. So that's the reason I said almost equal distribution.
I tried using while loop with range min(customer_nbr) and max(customer_nbr).. But it distributed randomly which is very odd.. 5k in one set and 50k in other set.
So I'm using the cursor for this purpose which gives me the result but is time consuming.. Does anyone know of any other approach of distributing without using cursor?
Thanks in advance,
Ravi
|
|
|
Re: PL/SQL Tuning [message #274094 is a reply to message #274093] |
Sat, 13 October 2007 13:45   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Here is what we know, you claim to have a problem.
Here is what we don't know:
What the data looks like
What the table looks like
What your code looks like
What the result set is supposed to look like.
How to determine if/when the result set is "correct" (or not).
Please read & follow posting guidelines as found in #1 STICKY post at top of this forum.
Please clarify problem & desired solution so some body besides you understand the requirements.
[Updated on: Sat, 13 October 2007 13:45] by Moderator Report message to a moderator
|
|
|
PL/SQL Tuning [message #274095 is a reply to message #274093] |
Sat, 13 October 2007 13:46   |
ravichandran_123
Messages: 13 Registered: October 2007 Location: Bangalore
|
Junior Member |
|
|
Hi,
I need to divide the records in a table into different sets based on the number_of_sets argument passed by the user. If user wants to distribute the transactions in a table to 5 and number of records in the table is 5 million, then the records need to be equally (almost) divided. But the tricky thing here is, if I have 10 transactions for one customer, I need to make sure to process that customer in in set. The bottom line is that the same customer transactions should not be spread accross different sets and need to be processed under on set. So that's the reason I said almost equal distribution.
I tried using while loop with range min(customer_nbr) and max(customer_nbr).. But it distributed randomly which is very odd.. 5k in one set and 50k in other set.
So I'm using the cursor for this purpose which gives me the result but is time consuming.. Does anyone know of any other approach of distributing without using cursor?
Thanks in advance,
Ravi
|
|
|
|
|
Re: PL/SQL Tuning [message #274099 is a reply to message #274096] |
Sat, 13 October 2007 17:51   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
Have a look at NTILE function.
Regards
Michel
[Updated on: Sat, 13 October 2007 17:52] Report message to a moderator
|
|
|
Re: PL/SQL Tuning [message #274107 is a reply to message #274099] |
Sat, 13 October 2007 20:38   |
ravichandran_123
Messages: 13 Registered: October 2007 Location: Bangalore
|
Junior Member |
|
|
Thanks a lot.. NTILE function worked. Here is the query which ditributed the records equally by making sure that the same customer is not splitted accross the sets.
select min(to_number(customer_no)), max(to_number(customer_no)), count(1) cnt, nt
from
(select customer_no, ntile(8) over (order by customer_no) nt from customer_trans)
group by nt
Thanks,
Ravi
|
|
|
|
|
Re: PL/SQL Tuning [message #274122 is a reply to message #274120] |
Sun, 14 October 2007 02:16   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Yes, this is the trick on big numbers:
SQL> select bucket, count(*) nb, round(100*ratio_to_report(count(*)) over ()) percent
2 from (select ora_hash(empno,5) bucket from emp)
3 group by bucket order by 2;
BUCKET NB PERCENT
---------- ---------- ----------
1 1 7
2 1 7
5 1 7
0 2 14
3 4 29
4 5 36
6 rows selected.
SQL> select bucket, count(*) nb, round(100*ratio_to_report(count(*)) over ()) percent
2 from (select ora_hash(object_id,5) bucket from dba_objects)
3 group by bucket order by 2;
BUCKET NB PERCENT
---------- ---------- ----------
1 0
0 7857 16
5 7886 16
3 7903 17
1 7954 17
4 8054 17
2 8155 17
7 rows selected.
Take care of NULL (see above) and very skewed data:
SQL> select bucket, count(*) nb, round(100*ratio_to_report(count(*)) over ()) percent
2 from (select ora_hash(owner,5) bucket from dba_objects)
3 group by bucket order by 2;
BUCKET NB PERCENT
---------- ---------- ----------
3 244 1
5 735 2
2 806 2
4 1974 4
1 2024 4
0 42027 88
6 rows selected.
Regards
Michel
|
|
|
Re: PL/SQL Tuning [message #274139 is a reply to message #274119] |
Sun, 14 October 2007 10:04   |
ravichandran_123
Messages: 13 Registered: October 2007 Location: Bangalore
|
Junior Member |
|
|
>Actually, you have two contradictory conditions:
- 5 sets of same size
- a customer cannot be spread over more than 1 set
I just gave an example.. But generally we would be processing millions of transactions and would expect at max of 10 or 15 transactions for a single customer. If the user wants to run the process by distributing the records to 5, it has to be done making sure that the customer is not spread accross. As I already said, it doesn't need to be equal like 100/5 = 20. It could be 22, 18, 20, 24, 16, 20 something like this. But I was getting random results..
I believe ntile function would help me getting what I expected.
Thanks,
Ravi
|
|
|
|
|
|