- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Substr Question???
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 06:16 AM
тАО11-04-2004 06:16 AM
Substr Question???
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 06:39 AM
тАО11-04-2004 06:39 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 07:16 AM
тАО11-04-2004 07:16 AM
Re: Substr Question???
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 07:18 AM
тАО11-04-2004 07:18 AM
Re: Substr Question???
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-04-2004 07:20 AM
тАО11-04-2004 07:20 AM
Re: Substr Question???
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-08-2004 03:59 PM
тАО11-08-2004 03:59 PM
Re: Substr Question???
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