Re: process long time query!

Cuong Hoang 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);"

Hello Cuong,
you could try a hint to use a hash anti join. Try this:

create table NO_BASE_DATE_PRICE as
  select ItemCode, IndexCode, ItemBaseDate   from CELL_MAP
  where (ItemCode, IndexCode, ItemBaseDate)   not in (select /*+ HASH_AJ */

             ItemCode, IndexCode, IndexRefMonth
          from CALC_PRICE);

Since hints do not force the database to do something but just give a hint, this *may* result in faster execution. You should have a look at the execution plan.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de