Home » SQL & PL/SQL » SQL & PL/SQL » Resource allocation problem (Oracle 12c- 12.2.0.1.0 )
Resource allocation problem [message #669084] Mon, 02 April 2018 12:14 Go to next message
suneeshvr
Messages: 3
Registered: May 2015
Junior Member
Hi Experts ,
I do have complex scenario to be solved using PL/SQL.I have n arrays of resource availability. Out of that I need to get best 5 resource allocation.

For example :

a[105]= [0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]

a[111]=[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]

a[119]=[1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]

a[105] represents the resource id 105.Each if this array represents resource availability . 0 - means resource is not available and 1 means resource available. Each node represents half an hour
duration . I need to get 5 best possibilities where resource(s) who can work on 6 hours.
- My first preference will be resources can work continues 6 hours.
- My second preference will be resource minimum number of resources to work 6 hours
- I need to get 5 possibilities to display .
Re: Resource allocation problem [message #669085 is a reply to message #669084] Mon, 02 April 2018 12:24 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

I don't understand what you are asking. You need to provide the CREATE TABLE statement and the three INSERT statements, so that people can set up the problem. Then you need to show the result you expect. You also need to show what SQL you have tried so far to get that result.
Re: Resource allocation problem [message #669087 is a reply to message #669085] Mon, 02 April 2018 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
Same lame post in Oracle's forum

https://community.oracle.com/thread/4134179
Re: Resource allocation problem [message #669088 is a reply to message #669087] Mon, 02 April 2018 19:43 Go to previous messageGo to next message
suneeshvr
Messages: 3
Registered: May 2015
Junior Member
Sorry not to explain the problem in details . Let me try to explain . Below is my table and insert queries

create table RESOURCE_ALLOC
(
cal_entry_id number,
resource_id number,
start_date TIMESTAMP WITH LOCAL TIME ZONE,
end_date TIMESTAMP WITH LOCAL TIME ZONE
);

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (2,119,to_timestamp('09-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('09-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (3,119,to_timestamp('10-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('10-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (4,119,to_timestamp('11-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('11-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (5,119,to_timestamp('12-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('12-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (6,119,to_timestamp('13-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('13-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (8,111,to_timestamp('09-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('09-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (9,111,to_timestamp('10-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('10-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (10,111,to_timestamp('11-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('11-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (11,111,to_timestamp('12-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('12-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (13,105,to_timestamp('09-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('09-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (14,105,to_timestamp('10-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('10-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (15,105,to_timestamp('11-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('11-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (16,105,to_timestamp('12-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('12-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

My requirement here is to find the best 5 possible path. For eg. The resource 119 is available from 09-APR-18 12.00.00.000000000 AM to 09-APR-18 12.00.00.000000000 PM . So I need to get 6 hours slot for resource 119.

He can start at 09-APR-18 12.00.00.000000000 AM and end at 09-APR-18 06.00.00.000000000 AM

He can start at 09-APR-18 00.30.00.000000000 AM and end at 09-APR-18 06.30.00.000000000 AM

He can start at 09-APR-18 01.00.00.000000000 AM and end at 09-APR-18 07.00.00.000000000 AM


Note each increment is 30 mints . Likewise I need to search on other rows for the same resource and other resource and get 5 best possible time slots.


- My first preference will be one single resource can work continues 6 hours.
- My second preference will be minimum number of resources to work 6 hours countinously
- I need to get 5 possibilities to display
Re: Resource allocation problem [message #669089 is a reply to message #669088] Mon, 02 April 2018 19:59 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
>find the best 5 possible path.
Which metric measures "best"?
If there are 10 possible paths, how to decide which 5 are best & which 5 do not get presented?

Why are column datatypes like below
>start_date TIMESTAMP WITH LOCAL TIME ZONE,
>end_date TIMESTAMP WITH LOCAL TIME ZONE
When no TIMEZONE detail is involved?

>Note each increment is 30 mints .
What is an increment?
Re: Resource allocation problem [message #669090 is a reply to message #669089] Mon, 02 April 2018 21:11 Go to previous message
suneeshvr
Messages: 3
Registered: May 2015
Junior Member
1) We want to make the performance of this query more efficient .
metrics for best
a) the resource should start as soon as possible
b) Minimum change of resource
c) if possible we should pick minimum lodaded resource

2) Timezone is used for different purpose for this usecase that many not applicable
3) Time increment : Say the resources are available from 00:00AM to 24:00 hours a day. And I need to search the resource availability from 6am. Frist I need to search for resource availability starting from 6am. If I am not finding any I need to start search again from 6.30 am and so on.
Hope this is clear.
Previous Topic: Administering queue
Next Topic: UNUSABLE INDEX on Partition table
Goto Forum:
  


Current Time: Wed Jan 29 03:00:17 CST 2020