Bill, not stupid questions at all
Firstly, how many rows in the table. Do you believe 3 -4
minutes response is unacceptable. Do the users think that (probably not if the
application has been running a long time like this). The standard question on
here is 'what version of Oracle' but that is probably not too relevant here
anyway.
Secondly the original DBA makes a valid
point in stating that coordinates may not be used for querying.
You have an ideal set up to build a test case here and
try out your assumptions. Create table test as select * from original (where
rownum < 50001, if you are short of space). Enable tracing with set autotrace
on and build the indexes as they currently exist and then make changes , looking
at each explain plan and the cost figure from the
autotrace.
Your individual points can then be proved to your own
satisfaction.
WRT the point about a composite index, the index
organised tablespace is exactly that. A table with no data but an index that
holds it all. Beware that it is not suitable for a system that has a lot of
updates. Of course that is a major issue with adding many indexes, how is the
application used, will it impact the main function - to add data to the table
quickly and unobtrusively.
John
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_indexing.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).
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
