Say hello to everybody, and seeking for help

Hi: everybody

just say hello to you guys. of course I need some help from your suggesstion, I am a oracle DB developer, recently join a project, one query I am currently working with is to join 9 tables together, the largest fact table have around 27 million records, and other tables have no more than 1000 records, all the join are equal joins and join criteria are indexed, the query was designed to perform the most restricted selection first, more indexs are added to speed up the searching process.

one select key I am querying has around 280,000 out of 27 million, when I run that query, it never returns (10 hours and still return nothing), however the key for smaller amount of data (3000 out of 27 million) can returns the result to me.

another thing I observed, when running against larget data set (280,000 one), under enterprise manager => session, I exam my running session, under SQL part, using query plan view, I noticed that oracle splitted my query by 15 level of nested loops. to my understanding, the nested loops happens when there is not enough space to do the join, is that correct? can any one suggested any possible solutions using their knowlege?

I don't know anything about DBA job, what I know is to design the table, query, optimiz the sql script. forget to add, the oracle server is pretty good, dual 2.4 cpu, 4G ram, but it not only host my tasks, it also used by other developers, but the cpu usage is under 30% during normal development process, and seems always got heaps of rams there.

I never have this sort of question before, I thought, the DBA side put some limitations on the resource that can be used by those tables. can anyone give me an idea?

Thanks!!! Received on Mon Nov 21 2005 - 07:56:22 CST