Query help

[Raj]

Hi all,

The following query does a table scan, it is only selecting 10% of
tab1's data. It has a clustered index on dt column (not-MDC) and
cluster ratio is 93%

Card of tab1 - 1175070690
Card of tab2 - 100000

Query: ( Tablescan)
select [10-columns] from tab1 where dt >= (select min(dt) from tab2)
Query:( uses the index on dt)
select [10-columns] from tab1 where dt >= (select char(min(dt)) from
tab2)

Actual query:
select .......
(
select .......
union all
select [10-columns] from tab1 where dt >= (select char(min(dt)) from
tab2)
union all
select .......
)
group by .....
Why does the optimizer use the index on the dt column in the second
case??? It also runs faster...

Thanks,
Raj

[Tonkuma]

What is the data type of dt?

[Raj]

Both are of datatype date...
Tonkuma wrote:

What is the data type of dt?