| Slow query with full table scan [message #563933] |
Sat, 18 August 2012 08:57  |
 |
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
|
Member |
|
|
I'm Using Oracle 11.
I desperately need help with a query that is running too slow - it's a very simple problem. I have a table with 16 million rows and an index (let's call it the employee table with an index on department). I need to select all the employees whose departments are located in the uk. I achieve this by selecting all the department numbers from departments where location = 'UK' in a sub select then plug this into the main query as follows:
SELECT *
FROM employees
WHERE department IN (SELECT department from departments where location = 'UK');
It takes ages, 25 seconds or more, the explain plan shows its doing a full table scan on emplyees. I need it to use the index. The sub query is instant and returns only 5 rows. If I explicitly put the 5 numbers in the IN clause the query uses the index and executes in 0.04 seconds. See below:
SELECT *
FROM employees
WHERE department IN (1,2,3,4,5);
This is so frustrating, please can anyone help, I need it to use the subquery once and then use the index on the main table.
Many thanks.
|
|
|
|
|
|
|
|
|
|
| Re: Slow query with full table scan [message #563943 is a reply to message #563935] |
Sat, 18 August 2012 12:28  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
The hacker's way to force use of indexes is to revert to the rule based optimizer by hinting the statement with /*+ rule */ and then, if you really like the result, you can nail it down with a stored outline or with dbms_advanced_rewrite.
|
|
|
|
|
|
|
|
|
|
| Re: Slow query with full table scan [message #563956 is a reply to message #563949] |
Sat, 18 August 2012 15:18 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 18 August 2012 19:58If there is a FK as in the demo schemas then any way you rewrite the query Oracle will NEVER use the index as the result is the WHOLE employees table and Oracle knows it and it will even NOT access the departments table.
Are you reading a different question to the rest of us?
There's a restriction in the query on deptartments.
Unless every dept statisfies that criteria the chance of the result being the whole of employees is basically zero.
This is obvious.
It has to access deptartments.
|
|
|
|
| Re: Slow query with full table scan [message #563957 is a reply to message #563953] |
Sat, 18 August 2012 15:19 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Here's what I mean (with old SCOTT schema):
SQL> select * from emp where deptno in (select deptno from dept);
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO" IS NOT NULL)
As you can see the optimizer knows the foreign key and so it knows that all deptno in emp are deptno in dept and so it is useless to access it.
Quote:My take on the rewrite is that the WITH query will be executed, and the results stored in a temporary table.
Oracle makes it a temporary table ONLY if it useful (based on the statistics and constraints):
SQL> with d as (select deptno from dept)
2 select * from emp where deptno in (select deptno from d);
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO" IS NOT NULL)
Now you can force the materialization (it is seldomly useful), see the VIEW below:
SQL> with d as (select deptno from dept where rownum > 0)
2 select * from emp where deptno in (select deptno from d);
Execution Plan
----------------------------------------------------------
Plan hash value: 2469779214
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 574 | 5 (20)| 00:00:01 |
| 2 | VIEW | | 4 | 12 | 1 (0)| 00:00:01 |
| 3 | COUNT | | | | | |
|* 4 | FILTER | | | | | |
| 5 | INDEX FULL SCAN| DEPT_PK | 4 | 12 | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
4 - filter(ROWNUM>0)
Regards
Michel
[Updated on: Sat, 18 August 2012 15:20] Report message to a moderator |
|
|
|
|
|
| Re: Slow query with full table scan [message #563960 is a reply to message #563957] |
Sat, 18 August 2012 16:13 |
 |
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
|
Senior Member |
|
|
Michel
Your example is against a 14-row table. The optimizer would have to be lobotomized to use an index. The same is not necessarily true of a 6-million row table. Even if that weren't the case, your example forces a FTS because 'select deptno from dept' returns *all* departments and therefore every employee that has a department (i.e. most if not all of the table).
I don't have the SCOTT schema on my DB, but the following query using the new HR schema does use an index range scan rather than a FTS of EMPLOYEES:
SELECT *
FROM HR.employees
WHERE department_id IN (SELECT department_id
FROM hr.departments
WHERE department_id = 70);
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Using a WITH clause also makes use of an index range scan:
WITH dept AS
(SELECT department_id
FROM hr.departments
WHERE department_id = 70)
SELECT e.*
FROM HR.employees e
INNER JOIN dept d
ON e.department_id =d.department_id;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
So let's make this closer to what the OP has by including several departments:
WITH dept AS
(SELECT department_id
FROM hr.departments
WHERE department_id IN (70, 80, 90))
SELECT e.*
FROM HR.employees e
INNER JOIN dept d
ON e.department_id =d.department_id;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38 | 2622 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 38 | 2622 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
OK -- we've changed to a FTS. However, it that because of the IN-list or because now we're selecting enough of the employees table that a FTS is more effective? The next query picks three departments that have only one person in each:
WITH dept AS
(SELECT department_id
FROM hr.departments
WHERE department_id IN (10, 40, 70))
SELECT e.*
FROM HR.employees e
INNER JOIN dept d
ON e.department_id =d.department_id;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 207 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 207 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 3 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
OK -- now it uses the index. The CBO is doing what it is supposed to -- picking an index only when it is cheaper than a FTS. Will it do the same for the OP? Heck if I know -- I don't have his data. However, unless you can point out where I've done something stupid (always a possibility), the mere presence of a foreign key does not guarantee that a FTS will be performed.
[Updated on: Tue, 21 August 2012 05:32] by Moderator Report message to a moderator |
|
|
|
| Re: Slow query with full table scan [message #563961 is a reply to message #563958] |
Sat, 18 August 2012 17:14 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Sat, 18 August 2012 21:21>It has to access deptartments.
It only needs to access the FK index & never bother with the departments table which is a different object.
Congratulations - you haven't read the question properly either.
The query is supposed get all employees that belong to departments in the UK.
I very much doubt location is on the fk index.
So the query has to access departments
Ignore the query with the depts hard coded. That's just there to show that accessing the employees table via the index on dept is fast.
And it's that location that makes Michel's examples completely irrelevant.
EDIT: typo
[Updated on: Sat, 18 August 2012 17:15] Report message to a moderator |
|
|
|
|
|
| Re: Slow query with full table scan [message #564078 is a reply to message #563969] |
Tue, 21 August 2012 05:28 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Get back to basics. Have you collected statistics on your data? Do you have a FK between the tables that is enabled or RELY'd?
Though Michel did post a bad example (sorry buddy), his post does bring us to the basic point. Oracle FOR SOME REASON believes that the 5 departments will return enough rows from EMP that it will be faster to FTS the EMP table instead of using an index. Of course the OP has shown that this is not the case.
First thing to do is get a query plan with rowcount estimates and compare this to the actuals returned by different query steps. If you find that the estimated rows returned are way more than the actuals then you have a scenario where Oracle Optimizer is miscalculating its cardinality estimates and that is why you are getting FTS. The best solution for this is to make sure Oracle estimates correctly. How this is done however is sometimes hard to achieve. The first thing to check though is stats. See if stats are up-to-date. Then check to see if constraints exist and are enabled. Then check your datatypes to makes sure they are correct for reach column and that the join columns are the same datatype.
After that you are in a bit of trouble and need to resort to the types of tricks mentioned above.
To explain better what I mean consider this. What should happen if 99% of your employees were assigned to those 5 departements? What should happen if 1% of your employees are assigned to these 5 departments? What is happening in your query? At what % should you switch from index to FTS?
I am just guessing here, but there are likely one of two things happening:
- your subquery is estimated to bring back way more than 5 rows (SELECT department from departments where location = 'UK')
- since the subquery does not say which specific 5 rows, oracle is using a default value in order to estimate the join cardinality against emp. That may be why explicitely stating the 5 values changes things.
Lastly, this post is a pain in the butt. I am having to type off screen because someone was lazy in posting a wide piece of text. That would be you matthewmorris68. You posted some plans that have lots of trailing spaces. Don't do that. I have edited your offending post to remove the spaces so we can all read this thing now.
Kevin
[Updated on: Tue, 21 August 2012 05:49] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Slow query with full table scan [message #564128 is a reply to message #564116] |
Tue, 21 August 2012 09:36 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
The only reason I knew was because I was clueless at one point too and did the same things a few years back. Was not trying to be mean. Thanks for responding. Hope my info was useful.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
