Re: Performance on complex database

50 tables isn't that many. Don't assume that just because it is complex Oracle can't handle it. Give it a try.
Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Titou" <idl7_at_yahoo.fr> wrote in message
news:ef2c494.0301030856.5c7516bd_at_posting.google.com...


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