We have two SQL statements that run at radically different speeds. It seems to be based on whether a column is placed in the WHERE or in the SELECT list. We would like this investigated and explained. Remote access to our test server can be arranged.
The following statement runs slowly - 2.125 seconds:
SELECT COUNT(*) FROM SBC WHERE [login to view URL] = 1 AND [login to view URL] = 40088609 AND [login to view URL] = 798120609;
Despite it using the new index completely (including PLACED):
CREATE INDEX "BTS"."SBC_PLACEDTARGETSPOTS" ON "BTS"."SALES_BREAKS_COMMS" ("CONTITEMPRGDETTARGETID", "CONTITEMPRGDETID", "PLACED") TABLESPACE "BTS_INDEXES";
Yet this statement, with the PLACED in the SELECT list and NOT the WHERE clause, runs quickly (0.238 seconds):
SELECT [login to view URL] FROM SBC WHERE [login to view URL] = 40088611 AND [login to view URL] = 798120611;
Note: The IDs in the SQLs are changed to prevent Oracle caching the results.
PLACED, CONTITEMPRGDETID and CONTITEMPRGDETTARGETID in the WHERE - slow
PLACED and CONTITEMPRGDETID but no CONTITEMPRGDETTARGETID in the WHERE - fast
CONTITEMPRGDETID and CONTITEMPRGDETID in the WHERE, PLACED in the SELECT list - fast
CONTITEMPRGDETID and CONTITEMPRGDETID in the WHERE, COUNT(*) in the SELECT list, no direct references to PLACED - fast
PLACED and CONTITEMPRGDETID in the WHERE, COUNT(*) in the SELECT list - fast
22 freelancers are bidding on average £21/hour for this job
I worked on performance tunning for more than 5 years in different MNCs. I can assure you to deliver project on time with good quality of work. Looking forward for your further discussion for this project.
Hi, It looks like the INDEX is the cause. I'm happy to take a look at it. Will need to further investigate what's happening. Please send me a message if you'd me to take it further.