Path: text.usenetserver.com!out01b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!x40g2000prg.googlegroups.com!not-for-mail From: Aravindh Newsgroups: comp.databases.oracle.tools Subject: Re: using single table costing more than using JOINS Date: Tue, 07 Aug 2007 23:53:43 -0700 Organization: http://groups.google.com Lines: 63 Message-ID: <1186556023.334360.238090@x40g2000prg.googlegroups.com> References: <1186505361.819211.150700@x35g2000prf.googlegroups.com> NNTP-Posting-Host: 203.99.195.64 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1186556024 31862 127.0.0.1 (8 Aug 2007 06:53:44 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 8 Aug 2007 06:53:44 +0000 (UTC) In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1),gzip(gfe),gzip(gfe) X-HTTP-Via: 1.0 CTSINTCOISA6, 1.1 CTSINTCOISA7 Complaints-To: groups-abuse@google.com Injection-Info: x40g2000prg.googlegroups.com; posting-host=203.99.195.64; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.tools:181352 X-Received-Date: Wed, 08 Aug 2007 02:53:44 EDT (text.usenetserver.com) On Aug 8, 1:59 am, sybra...@hccnet.nl wrote: > On Tue, 07 Aug 2007 09:49:21 -0700, Aravindh > wrote: > > > > > > >Hi, > > >I have a large table called as JP_HISTORY_TBL. This has about 10 > >million rows. > >I did a select * from jp_history_tbl where assigned_to_pg_new = > >'IS00000025' > > >It did a full table scan and the cost is 10085 > > >Now I joined the JP_HISTORY_TBL with another table called as > >ps_rf_provider_grp TABLE. This table when joined brings down the cost > >and the time of execution drastically > > >select * from jp_history_tbl a,ps_rf_provider_grp b where > >a.assigned_to_pg_new = 'IS00000025' and a.assigned_to_pg_new = > >b.provider_grp_id > > >It brings down the Cost drastically to just 83.. > > >Then I am totally CONFUSED of why we should create a flattened table > >to store all the data. We are in the process of creating a flattened > >table which will contain the description (which is there in the > >PS_RF_PROVIDER_GRP table).. Now looking at the above cost we feel that > >our approach itself it totally illogical and IRRELEVANT. > >I want to know how ppl are designing big big warehousses with immense > >amount of flattened data... > > Just curious: why do you think reposting will get you better answers? > This is the second time you post this in the wrong group and the > second time without version and WITHOUT EXPLAIN PLAN results!! > > Why do you insist on calling out for crystal balls? > > Next time: please either post with sufficient details, or (in the > event that is asked to much, as you don't have a problem with people > wasting their free time with helping you out for free) better still: > don't post at all, and go to Oracle Technical Support. > At least those monkeys are getting paid to address your query, and > having you submit evidence for months. > > -- > Sybrand Bakker > Senior Oracle DBA- Hide quoted text - > > - Show quoted text - Hi Sybrand, I am very sorry for not furnishing the correct details..Totally sorry about that.. I will send out a separate mail to you since I am not able to attach a word document in this forum. Totally sorry again. Best Regards KN Aravindh