Say hello to everybody, and seeking for help

Giganews Newsgroups
Subject: Say hello to everybody, and seeking for help
Posted by:  mac.un… (mac.un…
Date: 21 Nov 2005

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

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?