Home » SQL & PL/SQL » SQL & PL/SQL » Diff between 2 sql's
Diff between 2 sql's [message #196866] Sun, 08 October 2006 18:27 Go to next message
kirso491
Messages: 8
Registered: October 2006
Junior Member
I have the sql

SELECT COUNT(*) FROM PS_ER_INTVW_SRCH2 WHERE
(ORIGINATOR_ID = '5995' OR AUTHORIZATION_ID = '5995'OR
RECRUITER_ID = '5995' OR EXISTS (SELECT 'X' FROM PS_ER_INTVW_SCHED A
WHERE A.INTERVIEWER = '5995' AND PS_ER_INTVW_SRCH2.JOB_REQ_NBR = A.JOB_REQ_NBR))

for which the execution time is 2.734 secs

Total rows in PS_ER_INTVW_SRCH2 -- 43369
PS_ER_INTVW_SCHED -- 78211

and the sql

SELECT COUNT(*) FROM PS_ER_SS_REQ_SRCH2 WHERE
(ORIGINATOR_ID = '5995' OR AUTHORIZATION_ID = '5995' OR
RECRUITER_ID = '5995' OR EXISTS (SELECT 'X' FROM PS_APPLICATN_ROUTE A
WHERE A.ROUTE_TO_ID = '5995' AND PS_ER_SS_REQ_SRCH2.JOB_REQ_NBR = A.JOB_REQ_NBR))

for which the execution time is 48.680 secs

Total Rows in PS_ER_SS_REQ_SRCH2 -- 1269
PS_APPLICATN_ROUTE -- 52394

Even though the sql's are the same, the execution time has huge differences.
What could be the different reasons
Re: Diff between 2 sql's [message #196867 is a reply to message #196866] Sun, 08 October 2006 19:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Run EXPLAIN_PLAN on both & see for yourself.
The difference could be for any number of reasons (missing index, lack of current statistics, etc) but you did not provide enough meaningful details.
Re: Diff between 2 sql's [message #196897 is a reply to message #196866] Mon, 09 October 2006 01:35 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

How many rows in both tables.
Re: Diff between 2 sql's [message #196980 is a reply to message #196897] Mon, 09 October 2006 07:45 Go to previous messageGo to next message
kirso491
Messages: 8
Registered: October 2006
Junior Member
In my message I have given the number of rows they contain.

Re: Diff between 2 sql's [message #196981 is a reply to message #196980] Mon, 09 October 2006 07:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But it's the explain plans that will tell you why they are running differently.
Re: Diff between 2 sql's [message #196984 is a reply to message #196866] Mon, 09 October 2006 08:06 Go to previous messageGo to next message
kirso491
Messages: 8
Registered: October 2006
Junior Member
For the first sql



Query Plan
SELECT STATEMENT Cost = 219
SORT AGGREGATE
FILTER
NESTED LOOPS
TABLE ACCESS FULL SYSADM .PS_JOB_REQUISITION
INDEX UNIQUE SCAN SYSADM .PS_ER_STATUS_TBL
INDEX RANGE SCAN SYSADM .PS_ER_INTVW_SCHED
INDEX RANGE SCAN SYSADM .PS_ER_INTVW_SCHED
Note: PLAN_TABLE is older version: Using simple plan output.




For the second sql


Query Plan
SELECT STATEMENT Cost = 660
SORT AGGREGATE
FILTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
VIEW SYS .VW_NSO_1
SORT UNIQUE
FILTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
HASH JOIN
TABLE ACCESS FULL SYSADM .PS_ER_STATUS_TBL
TABLE ACCESS FULL SYSADM .PS_POSN_APPLIEDFOR
TABLE ACCESS BY INDEX ROWID SYSADM .PS_APPLICANT
INDEX UNIQUE SCAN SYSADM .PS_APPLICANT
INDEX UNIQUE SCAN SYSADM .PS_APPLICANT_DATA
TABLE ACCESS BY INDEX ROWID SYSADM .PS_ER_STATUS_TBL
INDEX UNIQUE SCAN SYSADM .PS_ER_STATUS_TBL
INDEX RANGE SCAN SYSADM .PS_APPL_DATA_EFFDT
SORT AGGREGATE
FIRST ROW
INDEX RANGE SCAN (MIN/MAX) SYSADM .PS_APPL_DATA_EFFDT
INDEX UNIQUE SCAN SYSADM .PS_APPLICANT_DATA
SORT AGGREGATE
FIRST ROW
INDEX RANGE SCAN (MIN/MAX) SYSADM .PSAPOSN_APPLIEDFOR
TABLE ACCESS BY INDEX ROWID SYSADM .PS_JOB_REQUISITION
INDEX UNIQUE SCAN SYSADM .PS_JOB_REQUISITION
INDEX UNIQUE SCAN SYSADM .PS_ER_STATUS_TBL
TABLE ACCESS BY INDEX ROWID SYSADM .PS_SET_CNTRL_GROUP
INDEX UNIQUE SCAN SYSADM .PS_SET_CNTRL_GROUP
INDEX FULL SCAN SYSADM .PS_REC_GROUP_REC
INDEX RANGE SCAN SYSADM .PSBJOBCODE_TBL
SORT AGGREGATE
FIRST ROW
INDEX RANGE SCAN (MIN/MAX) SYSADM .PSBJOBCODE_TBL
INDEX RANGE SCAN SYSADM .PS_DEPT_TBL
SORT AGGREGATE
FIRST ROW
INDEX RANGE SCAN (MIN/MAX) SYSADM .PS_DEPT_TBL
INDEX FULL SCAN SYSADM .PS_APPLICATN_ROUTE
Note: PLAN_TABLE is older version: Using simple plan output.
Re: Diff between 2 sql's [message #196997 is a reply to message #196984] Mon, 09 October 2006 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, there's your answer.
One of those SQLS is using what looks like a reasonably complex view where the other one uses a table.
Re: Diff between 2 sql's [message #197002 is a reply to message #196997] Mon, 09 October 2006 08:45 Go to previous message
kirso491
Messages: 8
Registered: October 2006
Junior Member
Thank you. Now i got it
Previous Topic: Create output that represents ranges based on broken consecutive list
Next Topic: Repeated Sequence generation in Table
Goto Forum:
  


Current Time: Sun Dec 11 00:38:52 CST 2016

Total time taken to generate the page: 0.04826 seconds