Have a tricky one:
Our support team is running a function through a UI that performs a list lookup against our production database.
It runs fine, EXCEPT when they try to perform the lookup against a specific customer. This customer just happens to have 100k+ entries..
Here is the query:
select * from TPDetails
where SndrUserNo in (4006541,10001662)
and RcvrUserNo in (4006541,10001662);
So, if I run this against the Production database, I do see it take along time (over 10m)…just as they are seeing on the app.
However, TPDetails is just a simple view:
SELECT text from dba_views
WHERE owner='AUTHDBA'
AND VIEW_NAME='TPDETAILS';
TEXT
--------------------------------------------------------------------------------
SELECT a.sndraddrref AS sndraddrref, a.rcvraddrref AS rcvraddrref,
a.admtimestmp AS admtimestmp, b.userno AS sndruserno,
d.userno AS rcvruserno, a.servicelevel AS servicelevel,
a.tpstatus AS tpstatus, a.sndcharges AS sndcharges,
a.rcvcharges AS rcvcharges, a.storcharges AS storcharges,
a.dialcharges AS dialcharges, a.initiator AS initiator,
a.created AS created, a.autoforward AS autoforward,
a.modified AS modified, a.elemsep AS elemsep,
a.subelemsep AS subelemsep, a.approved AS approved,
a.established AS established, b.address AS sndraddress,
b.qualifier AS sndrqualifier, b.companyname AS sndrarefcompanyname,
c.companyname AS sndrusrcompanyname, c.mbservice AS sndrmbservice,
c.interconnectcode AS sndrinterconnectcode,
c.ediclient AS sndrediclient, c.thirdpartyusr AS sndrthirdpartyusr,
c.redirectnodeid AS sndrredirectnodeid, d.address AS rcvraddress,
d.qualifier AS rcvrqualifier, d.companyname AS rcvrarefcompanyname,
e.companyname AS rcvrusrcompanyname, e.mbservice AS rcvrmbservice,
e.interconnectcode AS rcvrinterconnectcode,
e.ediclient AS rcvrediclient, e.thirdpartyusr AS rcvrthirdpartyusr,
e.redirectnodeid AS rcvrredirectnodeid,
a.segterm as segterm,
a.use_tplvl_separators_for_rcvr as use_tplvl_separators_for_rcvr,
a.timezone as timezone
FROM tp a, addrref b, users c, addrref d, users e
WHERE a.sndraddrref = b.addrref
AND b.userno = c.userno
AND a.rcvraddrref = d.addrref
AND d.userno = e.userno
If, I replace the view SQL in the query and add the extra where clause filters, all of a sudden I can get the results (only 30 rows!) to return in less than a sec:
SELECT a.sndraddrref AS sndraddrref, a.rcvraddrref AS rcvraddrref,
a.admtimestmp AS admtimestmp, b.userno AS sndruserno,
d.userno AS rcvruserno, a.servicelevel AS servicelevel,
a.tpstatus AS tpstatus, a.sndcharges AS sndcharges,
a.rcvcharges AS rcvcharges, a.storcharges AS storcharges,
a.dialcharges AS dialcharges, a.initiator AS initiator,
a.created AS created, a.autoforward AS autoforward,
a.modified AS modified, a.elemsep AS elemsep,
a.subelemsep AS subelemsep, a.approved AS approved,
a.established AS established, b.address AS sndraddress,
b.qualifier AS sndrqualifier, b.companyname AS sndrarefcompanyname,
c.companyname AS sndrusrcompanyname, c.mbservice AS sndrmbservice,
c.interconnectcode AS sndrinterconnectcode,
c.ediclient AS sndrediclient, c.thirdpartyusr AS sndrthirdpartyusr,
c.redirectnodeid AS sndrredirectnodeid, d.address AS rcvraddress,
d.qualifier AS rcvrqualifier, d.companyname AS rcvrarefcompanyname,
e.companyname AS rcvrusrcompanyname, e.mbservice AS rcvrmbservice,
e.interconnectcode AS rcvrinterconnectcode,
e.ediclient AS rcvrediclient, e.thirdpartyusr AS rcvrthirdpartyusr,
e.redirectnodeid AS rcvrredirectnodeid,
a.segterm as segterm,
a.use_tplvl_separators_for_rcvr as use_tplvl_separators_for_rcvr,
a.timezone as timezone
FROM authdba.tp a, authdba.addrref b, authdba.users c, authdba.addrref d, authdba.users e
WHERE a.sndraddrref = b.addrref
AND b.userno = c.userno
AND a.rcvraddrref = d.addrref
AND d.userno = e.userno
AND SndrUserNo in (4006541,10001662)
and RcvrUserNo in (4006541,10001662)
30 rows selected.
Elapsed: 00:00:00.46
So, the question is:
Why do I not get the same performance using the VIEW as I do when I replace the view text with equivalent SQL?
I have not re-creating the view because it is Production…..but maybe it is corrupt somehow?
Thanks for any help!
jd