Home » RDBMS Server » Performance Tuning » Slow query with full table scan (ORACLE 11)
Slow query with full table scan [message #563933] Sat, 18 August 2012 08:57 Go to next message
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 #563934 is a reply to message #563933] Sat, 18 August 2012 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the explain plan shows its doing a full table scan on emplyees. I need it to use the index.


Why? As there is a FK on department between the 2 tables an index would be a bad choice, a FTS is the good choice as you will return ALL employess rows.

As this is not the real case, we cannot help you.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Re: Slow query with full table scan [message #563935 is a reply to message #563934] Sat, 18 August 2012 09:03 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
Thanks for responding...

The first select above does an FTS and takes 25 seconds. The second select uses the index and takes 0.04 seconds, I need the first query to use the index and work like the second query. Only 250 rows of 16 million are returned.
Re: Slow query with full table scan [message #563936 is a reply to message #563933] Sat, 18 August 2012 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

please post CREATE TABLE statements for both tables EMPLOYEES & DEPARTMENTS



WHY MY INDEX IS NOT BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html
Re: Slow query with full table scan [message #563943 is a reply to message #563935] Sat, 18 August 2012 12:28 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #563945 is a reply to message #563933] Sat, 18 August 2012 13:02 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Try rewriting your query using the following syntax and see if Oracle is more inclined to use the index for the JOIN.

WITH dept as
  (SELECT department 
   FROM   departments 
   WHERE  location = 'UK'
  )
SELECT e.*
FROM   employees e
       INNER JOIN dept  d
       ON e.department = d.department;
Re: Slow query with full table scan [message #563949 is a reply to message #563945] Sat, 18 August 2012 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If 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.

Regards
Michel
Re: Slow query with full table scan [message #563953 is a reply to message #563949] Sat, 18 August 2012 14:22 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

You lost me there, Michel. I'd really like to know what you mean though.

My take on the rewrite is that the WITH query will be executed, and the results stored in a temporary table. From what the OP said, this should be about five rows. I don't know the specific structure of his 'employees' table, but he has indicated that there is an index on the departments column. When the 'employees' table is joined to the 'dept' temporary table, the optimizer should be smart enough to recognize that using the index would be valuable. This at least is the logic I'm working under. Generally when I'm trying to tune a query like this, I'll test a few SQL variants to see how they stack up.

Why do you say that the presence of a foreign key will require a FTS? I also don't understand why you indicate that the departments table won't be accessed. It has to be for the WITH subquery. After that, it won't be simply because the DEPT temporary table will be used.
Re: Slow query with full table scan [message #563956 is a reply to message #563949] Sat, 18 August 2012 15:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Sat, 18 August 2012 19:58
If 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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 #563958 is a reply to message #563956] Sat, 18 August 2012 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>It has to access deptartments.
It only needs to access the FK index & never bother with the departments table which is a different object.
Re: Slow query with full table scan [message #563960 is a reply to message #563957] Sat, 18 August 2012 16:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #563969 is a reply to message #563961] Sun, 19 August 2012 06:04 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
Thanks for responding...

I just used these table names as an example of my problem, employees has a foreign key on department to the departments table and also an index on this column. Departments has an index on departments. Employees has 16 million rows and departments a few thousand. The select returns 250 rows from 16 million.

The two queries I posted are the same, one has the departments hard coded and the other looks them up in the subselect. They return the same results but only one of them is using the index.

I ran an explain plan and the first select I posted does a FTS and takes 25 seconds.

The second select (with the 5 department numbers hard coded) uses the index and takes 0.04 seconds, I need the first query to use the index and work like the second query.

I'll try your suggestions.
Re: Slow query with full table scan [message #564078 is a reply to message #563969] Tue, 21 August 2012 05:28 Go to previous messageGo to next message
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:
  1. your subquery is estimated to bring back way more than 5 rows (SELECT department from departments where location = 'UK')
  2. 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 #564116 is a reply to message #564078] Tue, 21 August 2012 08:20 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

>...because someone was lazy in posting a wide piece of text.

A more accurate adjective would be 'clueless', not 'lazy'. Hadn't pasted plans from SQLDev in this forum before. I don't know why SQLDev adds the massive amount of training spaces to output. Since I haven't been back in the thread since then I didn't realize the plans had caused a problem. My apologies.
Re: Slow query with full table scan [message #564128 is a reply to message #564116] Tue, 21 August 2012 09:36 Go to previous message
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
Previous Topic: Performance Issue while truncating table partition. (3 Merged)
Next Topic: SQL Statement taking too long (merged 3)
Goto Forum:
  


Current Time: Fri Mar 29 04:17:28 CDT 2024