| Text versus numeric indexing... [message #9127] |
Mon, 20 October 2003 16:54  |
Dan Skousen
Messages: 1
Registered: October 2003
|
Junior Member |
|
|
Okay, so for reasons we won't go into right now, I am desigining a table in which I have two choices:
1. Define a range of numbers in which I will be able to issue a query like the following:
SELECT * FROM MY_TABLE
WHERE INDEX_MIN > 2341
AND INDEX_MAX <= 6523
2. Define a string that defines the same type of relationship, i.e.:
SELECT * FROM MY_TABLE
WHERE STR_INDEX LIKE "A.D.B.F.%"
Obviously I would be indexing INDEX_MIN and INDEX_MAX in the first case, and STR_INDEX in the second. The question then becomes: which is faster?
As a programmer, my instinct is to say that mathematical operations are always quicker than string operations. Using a string (as in solution number two) seems to be more of a hit. But after I index it, is this still the case, i.e. it is hashed together in some fashion so that my LIKe statement really becomes a mathematical operation.
Any help out there from you Oracle gurus? It would be nice to get an idea about what the cost for each method is so I only have to write the best one.
Thanks,
Dan Skousen
iCentris, Inc.
|
|
|
|
| Re: Text versus numeric indexing... [message #9128 is a reply to message #9127] |
Mon, 20 October 2003 17:20  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Since your numeric approach involves two columns and the string approach only one column, I'm not sure it is really fair to compare the two here.
One factor would be the data distribution. What percentage of the total table would these queries be returning?
I would suggest benchmarking (TKPROF) the two approaches - with representative and analyzed (dbms_stats) data - and and go with the one that has the lowest logical I/Os (consistent gets).
|
|
|
|
| Re: Text versus numeric indexing... [message #9129 is a reply to message #9127] |
Mon, 20 October 2003 18:33 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Maybe I am missing something, but indexes on strings are as effective as indexes on numbers , becos basically they just store the key values and the corresponding ROWIDs and Oracle is going to scan the table blocks using the ROWIDs ...there are no mathematical operations involved(maybe in hash clusters),unless again I am missing something.
In your case, both of them are going to go for Index range scans and the number of blocks scanned depends on your data & cardinality. Again depending upon if you are using histograms , Oracle may decide to employ Index scans or Full table scans depending upon the data distribution, whether its number or a string.
But if you want to find out %ACB% , then you will need TEXT Search Indexes (context) and not conventional indexes and use where CONTAINS , instead of LIKE.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
