Re: Say hello to everybody, and seeking for help

<mac.unsw_at_gmail.com> wrote in message
news:1132581539.642874.72180_at_o13g2000cwo.googlegroups.com...
> 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!!!
>

Implement a materialized view.

You can find a good explanation of what they are by viewing the Oracle Concepts manual:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref843

-- 
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer


"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup