RE: (stupid) Question about indexing

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  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bill Ferguson Sent: 12 July 2007 13:35
To: oracle-l_at_freelists.org
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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 12 2007 - 08:11:18 CDT