I have a document archive that is somewhat of a tuple table where we store keywords that pertain to the document in one table and the path to the document in another. This setup was created almost 15 yrs ago and I'd rather not make any monumental changes to it.
The lookups are extremely slow as you can see by the query and the table definitions below. I have added a numeric fd_cli_rid value on the filedirectory table which should make lookups much faster. So, if I switch to go through the filedirectory table to get the records required, how would I rewrite this query to return all rows?
The FILEDIRECTORY table is primarily used for versioning of files. It can poossiby point to a different path than the FILENAME keyword in FILEKEYPAIR.
select /*+INDEX(filekeypair filekeypair2)*/ FD_RID, FKP_KEYWORD, FKP_VALUE, FD_FILENAME, FD_LASTMODIFIED from filekeypair,( select distinct p.*, rownum rnum from (select /*+INDEX(filedirectory filedirattrib_fd_rid)*/ FD_LASTMODIFIED,FD_RID,FD_FILENAMEfrom filedirectory,(select * from filekeypair where FKP_KEYWORD='CLI_RID') z where z.FKP_FD_RID(+)=FD_RID and FD_RID in (select /*+INDEX(B FILEKEYPAIR3)*/ b.FKP_FD_RID from filekeypair b where (upper(b.FKP_KEYWORD) like upper('CLI_RID')||'%' and upper(b.FKP_VALUE) like upper('1026697')||'%') and (upper(b.FKP_KEYWORD) like upper('DISTRIBUTION')||'%' and upper(b.FKP_VALUE) not like upper('INTERNAL')||'%'))order by z.FKP_VALUE ASC ,FD_LASTMODIFIED ASC) p where rownum<=100000) where rnum>=1 and FKP_FD_RID=FD_RID order by rnum
1. The use of operators, like: "upper(b.FKP_KEYWORD)" on database columns in "where" clauses should always be avoided, since these prevent Oracle from using indexes on that column (unless you have function-based indexes that exactly match this syntax).
2. The nested queries (select ... from (select...(from select...))) can create performance problems, depending on the numbers of records returned by the inner queries and on the amount of memory in your server, since these intermediate result sets need to be buffered somewhere and evaluated without the benefit of indexes.
3. Outer joins, for example: "where z.FKP_FD_RID(+)=FD_RID", can add significant performance penalties.
4. Filters on rownum *ONLY* reduce the number of rows returned by the query, They do *NOT* reduce the number of rows that Oracle has to process internally before returning the requested number of rows to the application.
Basically, it looks to me like whoever designed this query (or this system?) was concerned about flexibility but either had no concern for, or was unaware of, the impact on performance when Oracle queries are written like this.