Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Tuning (merged 2 threads)
PL/SQL Tuning (merged 2 threads) [message #274093] Sat, 13 October 2007 13:35 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #274096 is a reply to message #274094] Sat, 13 October 2007 14:26 Go to previous messageGo to next message
ravichandran_123
Messages: 13
Registered: October 2007
Location: Bangalore
Junior Member
Please see how the table and the data looks like

Before

customer_nbr activity set_id
100 act null
100 addon null
100 deact null
101 act null
102 act null
103 act null
104 act null
104 addon null
105 act null
106 act null

After
customer_nbr activity set_id
100 act 1
100 addon 1
100 deact 1
101 act 2
102 act 2
103 act 3
104 act 3
104 addon 4
105 act 4
106 act 5

I have 10 records and need to divide into 5 sets. This is basically for running the process in multi-threading mode.
But the thing is I don’t want to distribute one customer data in 2 different threads
My requirement is to divide the transactions equally satisfying the condition explained above.

Please find the code attached. Hope I made it clear.

Thanks,
Ravi
  • Attachment: approach.txt
    (Size: 2.28KB, Downloaded 575 times)
Re: PL/SQL Tuning [message #274097 is a reply to message #274095] Sat, 13 October 2007 14:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/90914/74940/
Do not cross/multi-post
Re: PL/SQL Tuning [message #274099 is a reply to message #274096] Sat, 13 October 2007 17:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #274119 is a reply to message #274107] Sun, 14 October 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for the feedback but I'm not sure your query will work with any data.
Actually, you have two contradictory conditions:
- 5 sets of same size
- a customer cannot be spread over more than 1 set

Imagine there are 10 customers with number 100 in your example.
But if you have few number of sets (5) and big number of customers, this is possible but I think only using MODEL clause.

Regards
Michel
Re: PL/SQL Tuning [message #274120 is a reply to message #274119] Sun, 14 October 2007 02:03 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT ora_hash(cust_num, 5)
FROM   my_tab


Ross Leishman
Re: PL/SQL Tuning [message #274122 is a reply to message #274120] Sun, 14 October 2007 02:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: PL/SQL Tuning [message #274141 is a reply to message #274139] Sun, 14 October 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ora_hash if the one you need.

Regards
Michel
Re: PL/SQL Tuning (merged 2 threads) [message #275246 is a reply to message #274093] Thu, 18 October 2007 23:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
very cool Michel. Looks like I need to take a trip back to the 10g sql reference manual to bone up on my functions.

Ntile, also very cool.

Thanks a bunch, Kevin

Re: PL/SQL Tuning (merged 2 threads) [message #275261 is a reply to message #275246] Fri, 19 October 2007 01:51 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ora_hash idea was from Ross and it is indeed cool.

Regards
Michel

Previous Topic: add decode in a where clause
Next Topic: DB link
Goto Forum:
  


Current Time: Fri Feb 07 14:48:54 CST 2025