cancel
Showing results for 
Search instead for 
Did you mean: 

Substr Question???

Chris Frangandonis
Regular Advisor

Substr Question???

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
5 REPLIES
Hein van den Heuvel
Honored Contributor

Re: Substr Question???


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
Regular Advisor

Re: Substr Question???

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
Honored Contributor

Re: Substr Question???

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.
Remember, wherever you go, there you are...
James A. Donovan
Honored Contributor

Re: Substr Question???

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.
Remember, wherever you go, there you are...
Sanjay Kumar Suri
Honored Contributor

Re: Substr Question???

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.