Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Brian.

Asked: April 21, 2002 - 7:56 pm UTC

Last updated: July 08, 2002 - 7:50 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi, Tom:
Thanks for you answer my questions.
This is a question about clinical trial database design and performance.
Clinical patient records needs to store many possible facts, for examples, (patient XYZ 1/5/2001, 12:00AM 12.5 gm/dl, 4.9 Meq/L, 80 IU....)

EAV design represents a column to row transformation, like this:
Patient date time lab_test_name result
(patient XYZ 1/5/2001, 12:00AM 'Hemoglobin", 12.5gm/dl)
(patient XYZ 1/5/2001, 12:00AM 'Potassium" 4.9 Meq/L)
..................

But it raises a problem: Query performance of a big clinical report. Because each attribute-value pair for a patient is stored as separate row in a table, when performing a similar complex Boolean query on a single EAV table, the conceptual AND, OR and NOT operations must be translated into the row-based operations of set union, set intersection and set differences...., and select statements involved in distinct, max, full outer join (missing data problem), self join, nested subqueries etc, which are more complexes, inefficient.

From your experience, what is the best model to store this kind of clinical data from database design and performance point of view? How does oracle handle this kind of issues?

Thanks


Brian


and Tom said...

While the above is flexible, it is not scalable if you need to perform queries like "find the patients that have had test X and test Y or something_else"


If at all possible, I would use a model like:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3361148209692 <code>

The problem there may well be that you have more then say 900 observations -- we run out of columns.

What you might also consider would be XML (especially in 9i with some of the new features therein).

There you could create an XML document for the patient with their history and using Oracle Text (in 9i) or Intermedia (8i) you can index the XML document. So, instead of inserting a new observation, you would update an XML tagged document. Then you can do very efficient CONTAINS searching on that using the tagged information.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 26, 2002 - 10:04 am UTC


what is "Oracle clinical " ?

A reader, July 08, 2002 - 3:56 pm UTC

Hi tom,

I don't know if this is relevent question in the above contex, but I don't know what is Oracle clinical ?
is it possible to down load that software ? if yes can you plase provide the link ?

Thanks,

Tom Kyte
July 08, 2002 - 7:50 pm UTC

It is an application (packaged off the shelf application) like HR or Fin Apps. It is used primarily in health care related industries for performing clinical trials of medications.

I do not believe it is available for download.

I did find some docs in Metalink. Under Top Tech Docs, click on E-Business Suite: ERP. If you hit ctrl-f and search on "Clinical" you can hit the documentation link. There are some install guides, user guides