using single table costing more than using JOINS

From: Aravindh <>
Date: Tue, 07 Aug 2007 09:49:21 -0700
Message-ID: <>


[Quoted] 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... Received on Tue Aug 07 2007 - 18:49:21 CEST

Original text of this message