performance - PL/SQL : Should i use a collection or do multiple querys? -

i'm working on project need create big html table each table row 15 minute interval , every table column day. goal show users want reserve rooms when room available , not. webpages pl/sql packages print out proper html.

example of table room :

(don't mind different colors)

all room definitions stored oracle database containing start date, end date, starting hour, ending hour , days of week. example definition be:

(07-31-2013 - 07-31-2014, 00:00 - 23:59, mondays,tuesdays , fridays)

my concern build every square of html table, need make query check if specific period defined. (for example if user wants see full week 10:00 14:00 room, 112 querys made build table 15 minute intervals) not mention user can see 4 weeks , time interval (could 00:00 23:59). problem need make check room definitions have make check many times see if interval taken reservation... make 224 querys see week.

my solution room definitions , reservations affect period user wants see , put them in collection(1 collection definitions , 1 reservations). after that, run tests on collection without doing query. main procedure calls function sending both collections parameter , function return true or false.

i wondering if oracle handle many small requests or did right thing collections ?

i read many posts saying how collections bad memory , i'm scared passing them through function parameter might not either... altough might no apply situation considering collection never have more 1-5 records in them.

my query check if available or not :

select distinct 'y'  room_defenition start_date <= user_date ,  end_date >= user_date , --f_to_day converts date coresponding code regexp_instr(mon||tue||wed||thu||fri||sat||sun,f_to_day(user_date)) <> 0 , start_time <= user_start_time ,  end_time >= user_end_time; 

user_date current column i'm testing on , user_start_time , user_end_time 2 border of current interval. (example 07-31-2013 - 10:00 - 10:15).

i have 2 subquerys in clause check if user allowed make reservations on room or not...

one solution kind of stuff create tables called dimensions. array has 2 dimensions create 2 tables, 1 called quarters example, has 1 row each 15 min in day (so 96 rows)

id    timestart   timeend ----------------- 1      00:00  00:15 2      00:15  00:30 ... 

and 1 called calendar holds days whole year (or next 10 years, doesn't matter)

id    dateofday --------------- 1     01/01/2013 2     02/01/2013 ... 

it's easy build query gives 1 row each cell of array, no matter if there or not available rooms.

select timestart, timeend, dateofday quarters cross join calendar -- legitimate use cross join timestart >= user_time_start ,       timeend   <= user_time_end ,       dateofday between user_date_start , user_date_end  order quarters.id, calendar.id 

result ordered rows of array, column. can read results of query 1 row @ time , build array on fly (no need collection or complex algorithm)

now need add subquery tells if room available or not, :

select timestart, timeend, dateofday,     (*subquery on room_definition *) available     quarters cross join calendar -- legitimate use cross join     timestart >= user_time_start ,           timeend   <= user_time_end ,           dateofday between user_date_start , user_date_end      order quarters.id, calendar.id