Substr Question???

[Chris Frangandonis]

Hi All

Is there any explanation as to why the SQL1 statement (substr) takes longer than the SQL2 statement?
They are both index the same.

SQL1
select count(*) from CE.CDR_DRMS
where substr(FE_F,9,8) >= '20041001' and substr(FE_F,9,8) <= '20041001'
and STAT_CODE like 'r%';

SQL2
select count(*) from CE.CDR_DRMS
where R_DT >= '20041001 00:00:00' and R_DT <= '20041001 23:59:59'
and STAT_CODE like 'r%';

Many Thanks
Chris

[Hein van den Heuvel]

Go try EXPLAIN PLAN for you query.

I'm sure you'll see that in the first case you 'decoupled' the target from the index and that oracle is either doing an fullindex scan or full tablescan versus 'index range scan' for the second case.

The oracle optimizer is not going to look at the actual substr arguments and realize that the sorting order HAPPENS to match the index sort order. In the second case it knows.

hth,
Hein.

[Chris Frangandonis]

Hi Hein

Thanks for the input. I am using Sybase and not oracle as my D/B. To use SQL1 with substr it gives me a more accurate output to what I am requesting than SQL2. If I need to improve this how/what should my SQL look like?

Thanks Again
Chris

[James A. Donovan]

By applying the substr function you immediately preclude the use of any standard index you may have on the FE_F column. As a result, query #1 runs slower than you would think it should.

If you have function-based index on FE_F then that index could be used to help query #1

e.g.

create index myindex on CE.CDR_DRMS(substr(FE_F,9,8))

will create a function based (substr) index on the FE_F column of the CDR_DRMS table.

[James A. Donovan]

doh...just saw your followup...not sure if Sybase supports function-based indexes or not, but query #1 does indeed preclude the use of a standard index even in Sybase.

[Sanjay Kumar Suri]

Check if the following input from Net helps:

http://sybooks.sybase.com/onlinebooks/group-asarc/srg1100e/sqlug

substring
(expression, start, length)
Returns part of a character or binary string. start specifies the character position at which the substring begins. length specifies the number of characters in the substring.

http://www.sybase.com/detail?id=2602

Pre-System 11 Syntax: forceindex
Place the indid of the index you wish to force in parentheses immediately following the table name in the from clause of the query:

select colA,colB,colC
from table_name(indid)
where.....

There are a couple of ways to get the indid for an index:

To get the indid and name of all indexes on a table, run the following query:

1> select indid,sysindexes.name
2> from sysindexes, sysobjects
3> where sysindexes.id=sysobjects.id
4> and sysobjects.name="table_name"
5> go

To get the name of an index using its indid, run the following query:

1> select indid,sysindexes.name
2> from sysindexes, sysobjects
3> where sysindexes.id=sysobjects.id
4> and sysobjects.name="table_name"
5> and sysindexes.indid=index_id
6> go

System 11 Syntax: Specifying an Index
In System 11, forceindex is now fully supported. Functionally the same as in previous SQL Server versions, the new syntax allows the use of an index name rather than the index ID:

select colA,colB,colC
from table_name (index index_name)
where.....

sks