Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Brian.

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

Answered by: Tom Kyte - Last updated: July 08, 2002 - 7:50 pm UTC

Category: Database - 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?



and we 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> <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.

and you rated our response

  (2 ratings)

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


April 26, 2002 - 10:04 am UTC

Reviewer: A reader

what is "Oracle clinical " ?

July 08, 2002 - 3:56 pm UTC

Reviewer: A reader

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 ?


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