Re: process long time query!

"Cuong Hoang" <hoang_c_at_bls.gov> wrote:
> I tried to run query but it takes forever (each table has 1 miillon
> rows). Do you know any way to rewrite this query to make faster?
>
> "create table NO_BASE_DATE_PRICE as
> select ItemCode, IndexCode, ItemBaseDate
> from CELL_MAP
> where (ItemCode, IndexCode, ItemBaseDate)
> not in (select ItemCode, IndexCode, IndexRefMonth
> from CALC_PRICE);"

That would very much depend on what indices are present and what the current execution plan is, among other things.

Assuming CALC_PRICE has a highly selective index on some combination of the three columns, I'd try:

create table NO_BASE_DATE_PRICE as
  select ItemCode, IndexCode, ItemBaseDate   from CELL_MAP a
  where not exists (select * from CALC_PRICE b where

      b.ItemCode=a.ItemCode and b.IndexCode=a.IndexCode and
      a.ItemBaseDate=b.IndexRefMonth)

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB