(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_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).

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

--
http://www.freelists.org/webpage/oracle-l