We help IT Professionals succeed at work.
Get Started

Oracle query tuning help required

Eddie Shipman
on
237 Views
Last Modified: 2016-08-30
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

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
)

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

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

Open in new window

Comment
Watch Question
Database Administrator, retired
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE