Performance on complex database

Hi,

I have to develop an application which uses a complex database composed of more than 50 tables.

It describes cars.
Each car has a main record with general information like built date or brand and also technical
informations about the record like the operator who create or change the record and the corresponding date
and time.
The caracteristics of the car are stored in other tables.

For example the country of origine is stored with a table COUNTRY and the ID is stored in the main table.

Other caracteristics are multi-eval fields. For exempale the color of the car (a car can have multiple color). There is a table with the color
(Red, blue, green ...) and a table to make the join between the color
table and the main table.

Other informations are stored in full text fields, like the description of the car, or the technical informations. Each full text field (7 in total) is stored in its own table with the english and spanish version.

With all those informations, there are about 150 fields that the user can query and they are disseminated
in more than 50 tables.

With so much fields and so much possibilities, it was not possible to create stored proc, and so the query
is dynamically constructed by my application.

At the beginning I was thinking of using the query from the user (very simple: brand = 'gm' and color = 'red')
and adding the relations:
Ex: (brand = 'gm' and color = 'red') and (join for color)

It seems that it is too complex for Oracle and so I have to help him by using a complex mechanism which will
give me for each field a sub query:
Ex: SELECT ID FROM MAIN WHERE ID IN ( (SELECT ID FROM MAIN WHERE BRAND = 'gm') INTERSECT
(SELECT ID FROM MAIN_COLOR WHERE COLOR = RED AND join for color)

Do you think the first method is really too confusing for Oracle and that I will have poor performances ?
Do you think of a better method ?

Any suggestions ?

TIA. Received on Fri Jan 03 2003 - 17:56:36 CET