Good Morning All (well depending on when you read this):
Typically in the past, I've had people create the Oracle tables for me and I just interact with them. The current project I've had to, so I think therin lies the problem of my issue. Let me generically explain the setup:
t_request: id (pk), pers_id_requester (fk), pers_id_reqFor (fk), cur_title (fk), future_title (fk), status (fk), various other attributes for table
t_pers: id (pk), name
t_titles: id (pk), name
t_status: id (pk), name
All id's are NUMBERs and all Names are VARCHAR2, 256 BYTEs.
So the query below is taking 1.5 -2 seconds to run initially when t_request has 80 records, t_pers has 30, and the remaining have less than 15; prior to being cached obviously
SELECT r.title, r.request_date, requester.name, emp.name, cur.name, fut.name, stat.name
FROM t_request r, t_pers requester, t_pers emp, t_title cur, t_title fut, t_status stat
WHERE r.pers_id_requester = requester.id AND r.pers_id_reqFor = emp.id
AND r.cur_title = cur.id AND r.future_title = fut.id
AND r.status = stat.id AND r.name = 'Sample Name'
ORDER BY r.request_date;
So what that means is that anytime someone intially comes into view this information (it's the default query) they'll have at least a 1.5s wait time; that's way too high for less than 50 records being returned. I also have another query that takes less than .02s run that I'm joining this query; together they take 4-5s to run. So I split the queries down and saw this was the problem one. So actually once I join the two, it increases the wait time significantly when first run.
I'm assuming that the pk -> fk indexes are wokring as they should; however I turned on AUTOTRACE and I all the joins are hash joins with a cost varying from 10 -27 and I also see an identical amount of 'TABLE ACCES FULL' operations. That doesn't seem right.
So I'm looking at the query and it's pretty straight forward, and that's why I'm thinking it has to do with the way the tables are setup; help, guidance, tips are much appreciated. Or even links to some doco would be helpful as well.. I just can't seem to find any searching; it seems that all the good stuff is probably buried 15 pages deeps in a Google search once you get past the sites that want to train you...
Thanks in Advance
