|
|
|
|
|
|
| Author |
Message |
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
 Posted: Fri Feb 25, 2005 1:05 am Post subject:
Re: LIKE performance
|
|
|
lh wrote:
| Quote: | "GreyBeard" <Fuzzy.GreyBeard@gmail.com> wrote in message
news:pan.2005.02.25.00.48.10.865706@gmail.com...
On Fri, 25 Feb 2005 09:43:15 +1000, lh wrote:
Depending on the version of Oracle, and the frequency of the specific
query, you might consider a funcion based index.
However, you leave us with far too little info to go beyond smarmy
guesses.
FGB
My smarmy apologies your Greybeardness.
Sorry 'bout that. I never should type what I'm thinking <g
Oracle 8i, the query is executed in a loop, the function is reading
directories for files then checking the table to check that the file has
been logged. The function is run regularly and with 10s of thousands of
files it is taking way too long. This query is the bottleneck with a
few seconds per query the current time. So I need a faster way to check
for filenames in the table.
Whatcha doing with the files? IOW, is there a way that intermedia could
help you, or the content management SDK?
As a first shot, though, I'd try a fbi.
FGB
I did experiment with substr as an index, but it doesn't seem to use the
index after i add it. It just does a table scan.
|
Why is that a problem?
Is there a measured performance issue? If so what is it?
Are your statistics current? Created how?
What percentage of the rows are being retrieved?
What is the cardinality?
etc. etc.
The fact that an index is not being used might mean nothing more than
that not using the index is appropriate.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
 |
GreyBeard
*nix forums Guru Wannabe
Joined: 10 Mar 2005
Posts: 182
|
| Posted: Fri Feb 25, 2005 12:56 am Post subject:
Re: LIKE performance
|
|
|
On Fri, 25 Feb 2005 10:21:13 +1000, lh wrote:
| Quote: |
I did experiment with substr as an index, but it doesn't seem to use the
index after i add it. It just does a table scan.
|
Is the substr the only component in your where clause, or are there
additional predicates?
I'd also recommend reading "Practical Oracle8i" by Jonathan Lewis and
following some of the advice.
Finally, is there any way you could attempt a test with a more recent
version, say 9iR2 or (pref) 10g? There have been a good number of
advances that should help.
FGB |
|
| Back to top |
|
|
lh
*nix forums beginner
Joined: 18 Apr 2005
Posts: 7
|
| Posted: Thu Feb 24, 2005 11:21 pm Post subject:
Re: LIKE performance
|
|
|
"GreyBeard" <Fuzzy.GreyBeard@gmail.com> wrote in message
news:pan.2005.02.25.00.48.10.865706@gmail.com...
| Quote: | On Fri, 25 Feb 2005 09:43:15 +1000, lh wrote:
Depending on the version of Oracle, and the frequency of the specific
query, you might consider a funcion based index.
However, you leave us with far too little info to go beyond smarmy
guesses.
FGB
My smarmy apologies your Greybeardness.
Sorry 'bout that. I never should type what I'm thinking <g
Oracle 8i, the query is executed in a loop, the function is reading
directories for files then checking the table to check that the file has
been logged. The function is run regularly and with 10s of thousands of
files it is taking way too long. This query is the bottleneck with a
few seconds per query the current time. So I need a faster way to check
for filenames in the table.
Whatcha doing with the files? IOW, is there a way that intermedia could
help you, or the content management SDK?
As a first shot, though, I'd try a fbi.
FGB
|
I did experiment with substr as an index, but it doesn't seem to use the
index after i add it. It just does a table scan. |
|
| Back to top |
|
|
GreyBeard
*nix forums Guru Wannabe
Joined: 10 Mar 2005
Posts: 182
|
| Posted: Thu Feb 24, 2005 10:47 pm Post subject:
Re: LIKE performance
|
|
|
On Fri, 25 Feb 2005 09:43:15 +1000, lh wrote:
| Quote: |
Depending on the version of Oracle, and the frequency of the specific
query, you might consider a funcion based index.
However, you leave us with far too little info to go beyond smarmy
guesses.
FGB
My smarmy apologies your Greybeardness.
|
Sorry 'bout that. I never should type what I'm thinking <g>
| Quote: | Oracle 8i, the query is executed in a loop, the function is reading
directories for files then checking the table to check that the file has
been logged. The function is run regularly and with 10s of thousands of
files it is taking way too long. This query is the bottleneck with a
few seconds per query the current time. So I need a faster way to check
for filenames in the table.
|
Whatcha doing with the files? IOW, is there a way that intermedia could
help you, or the content management SDK?
As a first shot, though, I'd try a fbi.
FGB |
|
| Back to top |
|
|
lh
*nix forums beginner
Joined: 18 Apr 2005
Posts: 7
|
| Posted: Thu Feb 24, 2005 10:43 pm Post subject:
Re: LIKE performance
|
|
|
| Quote: |
Depending on the version of Oracle, and the frequency of the specific
query, you might consider a funcion based index.
However, you leave us with far too little info to go beyond smarmy
guesses.
FGB
|
My smarmy apologies your Greybeardness.
Oracle 8i, the query is executed in a loop, the function is reading
directories for files then checking the table to check that the file has
been logged. The function is run regularly and with 10s of thousands of
files it is taking way too long. This query is the bottleneck with a few
seconds per query the current time. So I need a faster way to check for
filenames in the table. |
|
| Back to top |
|
|
GreyBeard
*nix forums Guru Wannabe
Joined: 10 Mar 2005
Posts: 182
|
| Posted: Thu Feb 24, 2005 10:29 pm Post subject:
Re: LIKE performance
|
|
|
On Fri, 25 Feb 2005 09:25:28 +1000, lh wrote:
| Quote: | I have a table with about 30000 rows, one column is a varchar2(90). I'm
doing a LIKE 'XXX%' query and its not using an index.
I tried querying with substr(col, 1, 10) = 'XXXXXXXXXX' with an index on
substr(col, 1, 10). It doesn't use the index, even with a hint.
Whats the best way to get good performance out of this type of query.
TIA
LH
|
Depending on the version of Oracle, and the frequency of the specific
query, you might consider a funcion based index.
However, you leave us with far too little info to go beyond smarmy guesses.
FGB |
|
| Back to top |
|
|
lh
*nix forums beginner
Joined: 18 Apr 2005
Posts: 7
|
| Posted: Thu Feb 24, 2005 10:25 pm Post subject:
LIKE performance
|
|
|
I have a table with about 30000 rows, one column is a varchar2(90). I'm
doing a LIKE 'XXX%' query and its not using an index.
I tried querying with substr(col, 1, 10) = 'XXXXXXXXXX' with an index on
substr(col, 1, 10). It doesn't use the index, even with a hint.
Whats the best way to get good performance out of this type of query.
TIA
LH |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Wed Jan 07, 2009 8:38 pm | All times are GMT
|
|
Buy Anything On eBay | Problem Mortgage | Links of Movies | Ringtone | Myspace Backgrounds
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
