1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with slow query doing full table scan

Discussion in 'SQL PL/SQL' started by brown_zzz, Aug 18, 2012.

  1. brown_zzz

    brown_zzz Guest

    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.
     
  2. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    It depends on data of tables and fetching data of table. Index picking only available when you are fetching less rows and index value is distinct (means depends on clustering_factor). Oracle CBO is very smart and index scanning always not fast. If fetching data spread on maximum blocks of tables then Oracle uses full table scan instead of index scan.

    You can forcibaly perform index scan using select/*+index (table_name index_name)*/ in your query.
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi brown,

    Please post the execution plan. By that we can explain whats going on there.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The execution plan won't help nearly as much as a 10053 trace to show what choices the optimizer has made. The optimizer is clever but it's not always smart. Are your statistics current on the employee and department tables? Is department a nullable column? If so then the original query may not use the index with the original query as NULL values may be returned from the subquery and may need to be fetched from the table. Have you tried modifying your query to eliminate the possibility of NULLs? See the example below:

    Code (SQL):
    SELECT *
    FROM employees
    WHERE department IN (SELECT department FROM departments WHERE location = 'UK')
    AND department IS NOT NULL;
     
    Read here:

    http://dfitzjarrell.wordpress.com/2008/04/09/a-tale-of-two-indexes/

    to see why NULLable columns that are indexed may cause the optimizer to choose a full table scan.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It only partially depends on the data; it also depends on the table definition. If I define the employee table as:

    Code (SQL):
    CREATE TABLE employee(
         employee_id            NUMBER NOT NULL,
         employee_name       varchar2(50) NOT NULL,
         department             NUMBER,
         manager                 NUMBER,
         salary                    NUMBER,
         ssn                       varchar2(11) NOT NULL
    );
     
    and create an index on department Oracle may or may not use that index as entirely NULL keys will not be present; this happens whether or not NULL values are actually in the department column as the column CAN contain NULLs and Oracle will expect that possibility. If, on the other hand, employee is defined in this manner:

    Code (SQL):
    CREATE TABLE employee(
         employee_id            NUMBER NOT NULL,
         employee_name       varchar2(50) NOT NULL,
         department             NUMBER NOT NULL,
         manager                 NUMBER,
         salary                    NUMBER,
         ssn                       varchar2(11) NOT NULL
    );
     
    then an index on the department column WILL be used as no NULL values can exist in the table and all possible column values will be contained in the index.

    In this case clustering factor has very little to do with the decision to use or not use the index.

    I suspect the original table has department defined as nullable and that is why the index is not being used with the subquery.