RE: (stupid) Question about indexing
- From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
- To: wbfergus@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 12 Jul 2007 08:33:05 -0500
If the longitude and latitude are actually numbers, then all retrievals
on the arithmetic conditions (<,>,=,between, etc) can be indexed
retrievals.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bill Ferguson
Sent: Thursday, July 12, 2007 7:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: (stupid) Question about indexing
Okay, I'm feeling rather stupid about these questions, but I figured I'd
better hear from the experts.
I've been tasked to work on another database project who's DBA has
retired, but he still works on it part-time when he wants. Anyway, I
know very little about this database other then it's an accumulation of
data from over 40 years, and some of the data fields that are now
required (through another front-end interface) are null. So that's
problem #1.
There are two common queries that are used almost exclusivley:
SELECT job_id, s.lab_id, j.submitter, field_id, country, state,
to_char(latitude, '99.999999'), to_char(longitude,'999.999999'),
primary_class, secondary_class, specific_name, sample_source,
method_collected, addl_attr
FROM ngdb_code_work2 s LEFT OUTER JOIN ngdb_job j USING (job_id)
WHERE &P1_WHERE_CLAUSE.
and:
select job_id, lab_id, species, data_value, qualifier, units,
technique, digestion, latitude, longitude
from all_chemistry c LEFT OUTER JOIN ngdb_code_work2 s1 USING
(job_id,lab_id)
where job_id in (select job_id
from (ngdb_job j LEFT OUTER JOIN ngdb_code_work2 s USING (job_id))
WHERE &P1_WHERE_CLAUSE.)
The &P1_WHERE_CLAUSE. is built during an Application Express (Apex)
session at run time based on user supplied criteria. With just indexes
on the job_id, lab_id fields, queries are taking around 3-4 minutes. I
asked the other DBA to also create indexes on the fields that are on the
search screen, basically the fields in the queries above. However, I
asked for individual indexes on (almost) each of the fields, even the
three numeric fields (data_value, latitude and longitude).
He replied back with :
I have created your requested indexes except for 3.
latitude and longitude:
1. nobody has ever queried on a single point
2. if you were to try, how would you know the exact value and precision?
-109.92638889 or -109.92639
3. I don't believe that indexes are used when > or < symbols are used in
sql, am I correct?
data_value
1. again, nobody has ever queried for a single value, only by a range of
values. i.e. why query for ICP Ti=.014 instead of .015
At this point, I do not see any advantage for indexing these fields. Am
I missing something?
So, starting with the numeric indexes, the application allows the user
to specify a "box" (N, S, E, W) of latitude/longitude coordinates. I'm
thinking that having these indexed will improve performance, as the
where clause does a between comparison. With the data_value, the query
will do either a ">=" or a "<=" depending on the user selection, and
again I think an index helps here as well. I also asked for a couple
function based indexes for a couple fields, where the data exists as
upper, lower or mixed, so I don't have to do a case conversion during
the query and negate the benefit of having the field indexed. The
submitter field is one example. It can either be all upper case, all
lower case, or both.
But, after re-reading the 10.2 documentation on indexes at
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_ind
exing.htm#ADFNS005 , I got wondering if instead of the multiple indexes
for each field, which evidently aren't going to help anything since I
didn't include the job_id, lab_id fields, if it would be better to have
a composite index consisting of all the columns returned by each query.
This would allow Oracle to only retrieve data from the index and not the
table itself, but any of the columns can be null (though I'm trying to
"remove" the records with null job_id and lab_id's).
However, the documentation at
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.
htm#sthref1298 also leads me to think that maybe the multiple index
approach would work as well as long as I remember to include the job_id,
lab_id in the index.
I really haven't bothered with (or had the time to be bothered with)
this aspect of Oracle since back in the v.5 days, and Oracle has changed
quite a bit since then. What are the opinions of the "experts" on this
subject, based on your previous experiences and knowledge? I think both
the other DBA and myself are missing something, but I can't quite put my
finger on it. I guess I'm also going to need to read up on using hints,
as I've never used them before, so any pointers for the above examples
would be appreciated.
Thanks a bunch.
--
-- Bill Ferguson
------------------------------------------------------------------------------
NOTICE: This electronic mail message and any attached files are confidential.
The information is exclusively for the use of the individual or entity intended
as the recipient. If you are not the intended recipient, any use, copying,
printing, reviewing, retention, disclosure, distribution or forwarding of the
message or any attached file is not authorized and is strictly prohibited. If
you have received this electronic mail message in error, please advise the
sender by reply electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your computer
system. Thank you.
==============================================================================
Other related posts:
