troubleshooting Question

Oracle query tuning help required

Avatar of Eddie Shipman
Eddie ShipmanFlag for United States of America asked on
Oracle Database
9 Comments2 Solutions241 ViewsLast Modified:
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
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
)
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
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
ASKER CERTIFIED SOLUTION
Mark Geerlings
Database Administrator, retired

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros