Re: Index scan turns into Full Table scan when use JOIN with another Table on 3 Indexed Fields

Hi Jusung Yang,

What you mentioned is completely correct and I also have tried nested loop, merge and hash join.
on Query 2 on table t3.
Following is total cost

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29281 Card=6002 Bytes=738246) (USE_HASH)
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=81568 Card=6002 Bytes=738246) (USE_NL)
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=74592 Card=6002 Bytes=738246) (USE_MERGE) None of these is better than Query 1 which is

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=19745 Card=5771 Bytes=709833)

"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0306040926.79aa7b16_at_posting.google.com...
> What are the definitions of all the indexes and primary key invloved?
> Looks like index CQ_VPS_USER_CALLS_PI1 is built on stat_date, yes? In
> this case, the plan looks OK.
>
> In the first query, you have a clear range for stat_date in the where
> clause, so a range scan was performed on a partitioned local index.
>
> In the 2nd query, the fact table is joined to another dimension table
> ( I figure this is a warehouse environment?). You can do nested loop,
> merge join or hash join. The optimizer opted for merge join. Range
> scan was performed on the unique index INCO_MODEL_DATES_1, this is
> because of the md.name = 'abc' clause looks like. To avoid sort on the
> row source from the fact table, full index scan was performed on the
> primary key. The two row sets were then merged.
>
> You probably can force a range scan on CQ_VPS_USER_CALLS_PI1 by using
> nested loop hint. You will have to see for yourself if it gives you
> better performance.
>
>
> - Jusung Yang
>
>
Received on Wed Jun 04 2003 - 17:27:11 CDT