help needed for a better search

Are any of your joins between the tables in the different DBMS?  If so, you will never get good performance with tables 0f 10 lakhs without some clever staging, some good middleware, or an industrial-strength ETL/EAI tool.  

You definitely need indexes for tables that size.  Can you go into each of the source databases and build the indexes there?  This still leaves the problem of cross-dbms joins, which will be very slow if you need them.

The best solution I can think of is to create a staging area on the fastest and biggest dbms platform you have (Oracle would always be my first choice), and either stage all of the necessary source data in an indexed warehouse structure, or stage 'join tables' providing indexed joins between the tables in different dbms that you need to join - then you can query the indexed join tables and generate subset queries to apply to the source tables.  Staging brings up the ETL/mirroring problem of keeping the staging area updated with changes in the source tables.  How volatile is this source data?  If it is volatile, do you need instant updates (mirroring), or batch updates (ETL)?

I always recommend www.datamirror.com for data-mirroring and ETL because their tools do the best job I know, but they are not cheap.  You could alternatively write it in PL/SQL, or even java, but you will end up with a sizeable piece of code to maintain, and keep in step with the source meta-data - are the source applications still in development, with metadata that is likely to change?

What is your budget?  Can you afford ETL tools, the disk space and processing to do the staging, and some input from a data warehouse tuning consultant?

sorry for the delay,..
and thanks for the response..

as payperpage said,..i went thru each db's and created indexes on that,..

since i dont have any cross joins,..i somehow managed to get the data and dumped in one single database..Oracle.,..i just created index like this..

create table t1 (c1 varchar2(255),c2 number(14))

some 30 lakhs rows created in t1
then
create index t1_idx on t1(c1,c2) compress 1;

still its giving high delay in fetching..

i dont know whether indexing is right..and i'm not sure bout all oracle parameters for tuning up the datbase.

please do reply in detail...

my response will be never delayed like before...u can trust me..

thans..Jim

Hi payperpage nd Koka..

even if i indexed the columns,..in my case there is no effect.

for eg:-
if the table has a title field which contains some book title names...say .."The gulf war","Iraq and gulf war"..
Suppose i have indexed this column..
if i search for "gulf war" its giving high delay,..because like '%gulf war%' wont take index.

i heard about the intermedia text in oracle,..if u guys can provide little bit info on that, its very helpful.

regards Jim

Lacking timely response from jimaricat@idg or contributing experts, but seeing that there is some useful info here, I recommend:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer

Perhaps we could find a better advise if the question were reposted with clear indication in title that it concerns 'intermedia text in oracle'. Well, indeed that seems to be a toll designed specifically for the searches that jimaricat needs. Unfortunately I have never used it...