more efficient Select statement

[wgechter]

The following is my select statement. How can I make this more effecient? My temp datafile is growing out of control and was curious how I can get this query to run better. Thanks, Wendi

SELECT DISTINCT A.PAT_EXT_ID||'|'||A.PAT_FIRST_NAME||'|'|| A.PAT_LAST_NAME||'|'||B.RX_MED_NAME||'|'||B.RX_SIZE||'|'||B.RX_AMT||'|'||(ascii(B.RX_TYPE))||'|'||B.RX_ROUTE||'|'||B.D_RX||'|'||B.D_DC||'|'||C.PROBLEM_NAME||'|'||C.D_PROBLEM||'|'||C.PROBLEM_CODE1||'|'||C.PROBLEM_TYPE
FROM MRPA99 A, MRRX99 B, MRPL99 C, MRSC99 D
WHERE A.PTID(+)=B.PTID AND B.PTID(+)=C.PTID AND C.PTID(+)=D.PTID and A.PAT_EXT_ID = '69910.0';

 

[Dagon]

Why do you need to do a "select distinct" ? I presume the reason is that the joins between A, B, C and D are non-unique. If this is the case, you are probably getting a series of many-to-many joins, which could blow up the result set massively.

This result set would have to be put into the temporary tablespace and de-duplicated by your database. That is probably why you are seeing your temporary tablespace going out of control.

You should also check whether there are indexes on the joining columns of the 4 tables. If not, the whole query would probably have to be done by a series of hash or merge joins, which again would take up a lot of temporary tablespace.

I would also question why you are outer joining to table A when you also have the condition A.PAT_EXT_ID = '69910.0'. For rows which have no match in A, a null value of PAT_EXT_ID would be generated and this clearly can never equal '69910.0'. As a result, the outer join to A will effectively be suppressed.

 

[wgechter]

Dagon
Thanks for the response. I am fairly new to SQL and don't know a whole lot. It might help if I send a sample of my output to show you why I did SELECT DISTINCT. But my use of select distinct may in fact be wrong too. This is the data without using DISTINCT:

xxxxx.x|GAYLA|SL$$$$|CEPHALEXIN HCL |500MG |30 |69|ORAL |14-APR-05|24-APR-05|STOOL GUAIAC|15-MAR-04|82270|3

xxxxx.x|GAYLA|SL$$$$|NORETHINDRONE ACETATE |5MG |40 |96| |15-MAR-04||STOOL GUAIAC|15-MAR-04|82270|3

xxxxx.x|GAYLA|SL$$$$|MEDROXYPROGESTERONE ACETATE |2.5MG |40 |69|ORAL |06-FEB-03|04-MAR-04|STOOL GUAIAC|15-MAR-04|82270|3

xxxxx.x|GAYLA|SL$$$$|AMOXICILLIN |500 | |69| |13-APR-94|23-APR-94|STOOL GUAIAC|15-MAR-04|82270|3

xxxxx.x|GAYLA|SL$$$$|AMOXICILLIN |500 MG |40 |69|PO |30-NOV-00|10-DEC-00|STOOL GUAIAC|15-MAR-04|82270|3

Notice that the same Problem_name shows up numerous times with same dates but different meds, etc So it seems, yes this is a many to one relationship?????? This happens with a lot of the fields. I just don't know how to make it show the pat_ext_id, pat_last_name and Pat_first_name and the one instance of her problem, med, etc.

PTID are the indexes on all tables. Not knowing SQL I used the pat_ext_id = '69910.0' to try to make it smaller and easier. Obviously didn't work. Thanks for the help.

 

[Dagon]

I think you need to start by working out what the primary keys on each of your tables are. Is table D uniquely keyed on ptid, for example ? Is A ptid + something else ?
You may be able to find out by looking at primary key constraints or unique indexes on the tables.

 

[wgechter]

Thank you for helping on this. Before we get too far I want to say that I'm querying an Oracle 8i Database. I was told from a user in a forum, oracle-reports to publish my question here. That is what I'm doing.

This is exactly what is in the data dictionary:
MRPA99 has 4 indexes
ptid
pat_ext_id
pat_name_key
d_birth pat_name_key

MRRX99 has 2 indexes
PTID rx_med_name d_rx rx_seq
PTID rx_type d_rx rx_med_name rx_seq

MRPL99 has 3 indexes
ptid problem_type n_problem problem_name
ptid problem_name
ptid problem_type d_problem problem_name

MRSC99 has 4 indexes
opid d_char_open t_chart_open ptid
ptid
d_chart_open t_chart_open
ptid session_id d_chart_open t_chart_open

I have mistaken the indexes for primary key. The primary key is ptid on all but I am not sure about mrsc99. Thanks again!

 

[Dagon]

I doubt if what you say is true since having 4 tables with exactly the same primary key would be pointless. You may as well combine them into a single table. If you run the following query, it should prove whether all the tables are uniquely keyed on ptid:

select ptid, count(*)
from MRPA99 A
group by ptid
having count(*) > 1

Then the same for MRRX99, MRPL99 and MRSC99.