28. januar 2008 - 14:27Der er
8 kommentarer og 1 løsning
How is this qury executed
Hello! I just got an job/issue on my desk. "Why does this query take so long to execute?"
To give you the complete info, we got at database Oracle 9i with a table a_tbl with over 10 000 000 rows. Columns in a_tbl looks for eg: (id, name, mark)
We got a cronjob that updates some of these rows each night and insert a int in the column "mark".
The sql to fetch these rows looks like
select name from a_tbl where name not in ( "very complex select sql-query" )
If I just execute the "very complex select sql-query" it returns after 2 minutes.
But if executed within the the total query it doesn't return until after 5 hours, acctually it has not returned yet.
So my question is if Oracle, or the total question is executed like
select name from a_tbl for eache row, execute "very complex select sql-query"
or does it: execute "very complex select sql-query" only once and with this list above only select those "name" that is not in that list.
My guess is that it executes the "very complex select sql-query" for each name.
Predicates formed using NOT are Stage 1 But they are not indexable For Subquery - when using negation logic: Use NOT Exists DB2 tests non-existence Instead of NOT IN DB2 must materialize the complete result set
Avoid NOT IN with SubSelects Using sub-selects and NOT IN is poorly optimized. Converting to nested queries or OUTER JOINs are more efficient. The following example finds customers without orders:
Less efficient: SELECT Customers.* FROM Customers WHERE Customers.[Customer ID] NOT IN (SELECT [Customer ID] FROM Orders);
More efficient: SELECT Customers.* FROM Customers LEFT JOIN Orders ON Customers.[Customer ID] = Orders.[Customer ID] WHERE ((Orders.[Customer ID] Is Null));
I will try to get a chance to test your suggetstions in the begining of next week. Right now I'm participating in 3 different projects unfortenately. But If everything goes my way I will have 4 hours tommorow afternoon. I get back to you as quick as I can.
BTW I spoke to a DBA in this project and he suggested to create a couple of extra index. That would speed up the "very complex select sql-query" according to him. To me it sounds better to fetch all rows first for the prestanda. But I guess he is right if I need to be sure that the "very complex select sql-query" get evaluated for each row.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
