Oracle query tuning help required

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_FILENAME
from 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

Select all Open in new window

Definitions:

CREATE TABLE FILEKEYPAIR
(
  FKP_FD_RID   NUMBER,
  FKP_KEYWORD  VARCHAR2(100 BYTE),
  FKP_VALUE    VARCHAR2(110 BYTE),
  FKP_INSUSER  VARCHAR2(100 BYTE),
  FKP_INSDATE  DATE,
  FKP_DELUSER  VARCHAR2(100 BYTE),
  FKP_DELDATE  DATE
)

CREATE TABLE FILEDIRECTORY
(
  FD_RID           NUMBER(9),
  FD_FILENAME      VARCHAR2(1000 BYTE),
  FD_LASTMODIFIED  DATE,
  FD_EVE_RID       NUMBER(9),
  FD_DELETED       DATE,
  FD_OLD           CHAR(1 BYTE),
  FD_CLI_RID       NUMBER
)

Select all Open in new window

This would be a typical record retrieved using the above query:

FD_RID     FKP_KEYWORD  FKP_VALUE                    FD_FILENAME                 FD_LASTMODIFIED
---------- ------------ ---------------------------- --------------------------- -------------------
5741874    CLI_RID      1026697                      \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874    DESIG        CC                           \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874    PRINT_DATE   20150105                     \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874    FILENAME     \NewCCDesigMemo20150103.pdf  \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874    DOC_NAME     New CC Desig Memo            \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM

Select all Open in new window

It eventually comes out displayed like this in the UI:

DOC_NAME           FILENAME                    PRINT_DATE  DESIG   CLI_RID
------------------ --------------------------- ----------- ------- --------
New CC Desig Memo  \NewCCDesigMemo20150103.pdf 20150105    CC      1026697

Select all Open in new window