Using index and not full table scan

[WILLIEWANKA]

I have a date column that is indexed, and I want to query a range of dates.


Which will use the index and which will use a full table scan?


1. select * from table where column_date between '2004-06-01' and '2004-06-30';


2. select * from table where substr(column_date,1,7) = substr(date,1,7);



I think the first will use the index because it matches the entire column, whereas the second only uses part of it.


Any ideas? I've done explains on both, and can't see much difference (aside from expected rows returned)

 

[tdatgod]

What Version of Software? 4.1 or 5.0?

With a PPI table or with out.

If it was the optimizer in its infinate wisdom could determine

where column_date between '2004-06-01' and '2004-06-30';

really means

where column_date in (
'2004-06-01',
'2004-06-02',
.
.
.
'2004-06-30');

And could convert this query into 30 Secondary Index lookups and build a common spool for the answer set.

I don't think it is that smart, or it just figured doing the PK lookup and the compare on the Where clause was faster than the the secondary Index lookup.

you can try the 'IN' clause and see if the explain changes.

---

 

[WILLIEWANKA]

Basically, we have a table that has monthly statement data. It is partitioned on statement date.

I was trying to find this month's statement, as well as last month's statement. We were trying to avoid hard coding a date because we are automating a weekly process and don't want to have to change any dates.

I spoke with our NCR rep and he said that the only way to use the index is to hard code the dates.

"The first query will be far be the best performance wise because it will use partition elimination (i.e. query looks at only those partitions that contain the data required) in this case 1 partition.

The second query will result in a full table scan because the partition key - statement_date has been rendered unusable by the substr."

He also said that if we use derived dates (add_months(date,-1) and add_months(date,1), the optimizer will not know they are dates, and it will result in another full table scan.


I think we're going to have to result to unix variables.

 

[tdatgod]

V2r4 or v2r5?


V2r5 has DYNAMIC PARTITION elimintaion so something like....

sel * from tab
where column_date in
( sel calendar_date
from sys_calendar.calendar
where
month_of_year = 6
and
year_of_calendar = 2004
);

will use partition elimination.
;

 

[BillDHS]

I would not think that the second example with the substring clause would ever use an index on the column_date column.

 

[dnoeth]

Tdatgod, you probably mean V2R5.1, because V2R4 didn't have partitions and V2R5.0 didn't do any dynamic elimination ;-)

BillDHS, you're absolutely right, because functions on *both* sides of a calculation always result in a FTS.


Dieter