Oracle Tips and Tricks — David Fitzjarrell

March 21, 2016

Repeat After Me

Filed under: General — dfitzjarrell @ 08:44

“every time you decide something without having a good reason, you jump to Conclusions whether you like it or not.” 
-- Norton Juster, The Phantom Tollbooth

In an Oracle forum recently the following question was posted:


I have a request for 3 indices as shown below. Does the 1st index suffice for 2 and 3?  Do I need all 3?
 
    CREATE INDEX IDX_ATM_EM_EFF_VER_CURRENT_DEL ON ATM_xxx_SALARY
    (EMPLOYEE_KEY, EFFECTIVE_DT, SALARY_VERSION_NUMBER, IS_CURRENT, IS_DELETED);

    CREATE INDEX IDX_ATM_EM_VER_CURRENT ON ATM_xxx_SALARY
    (EMPLOYEE_KEY, SALARY_VERSION_NUMBER, IS_CURRENT);

    CREATE INDEX .IDX_ATM_SAL_CURRENT_DEL ON ATM_xxx_SALARY
    (EMPLOYEE_KEY, IS_DELETED, IS_CURRENT);

Answering such a question in absence of any additional information isn’t easy, but looking at the columns it’s not unrealistic to make the following presumptions about the data:

        EMPLOYEE_KEY must be not null and unique
        IS_CURRENT is either a YES or NO value
        IS_DELETED is either a YES or NO value    

The following example, using queries tailored to the indexes to be created, was run in Oracle 11.2.0.4; notice that the first index is used in all three queries:


SQL> --
SQL> -- Create the table
SQL> --
SQL> create table atm_xxx_salary(
  2  employee_key            varchar2(20) not null,
  3  emp_fname               varchar2(20),
  4  emp_lname               varchar2(40),
  5  effective_dt            date,
  6  salary_version_number      number,
  7  is_current              varchar2(3),
  8  is_deleted              varchar2(3));
      
Table created.

SQL>
SQL> --
SQL> -- Load data
SQL> --
SQL> begin
  2          for i in 1..100 loop
  3                  insert into atm_xxx_salary
  4                  values('Employee_'||i, 'Blobbo', 'Fingnestle', sysdate + mod(i, 9), mod(i,11)+1, 'YES','NO');
  5  End loop;
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Create first index
SQL> --
SQL> CREATE INDEX IDX_ATM_EM_EFF_VER_CURRENT_DEL ON ATM_xxx_SALARY
  2  (EMPLOYEE_KEY, EFFECTIVE_DT, SALARY_VERSION_NUMBER, IS_CURRENT, IS_DELETED);
         
Index created.
         
SQL>
SQL> exec dbms_stats.gather_schema_stats(user);
      
PL/SQL procedure successfully completed.
    
SQL>
SQL> set autotrace on linesize 150
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and effective_dt between sysdate and sysdate+4
  5  and salary_version_number = 7
  6  and is_current = 'YES';

no rows selected

   
       
Execution Plan
----------------------------------------------------------
Plan hash value: 2729507590

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |     6 |   288 |     2   (0)| 00:00:01 |
|*  1 |  FILTER                      |                                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ATM_XXX_SALARY                 |     6 |   288 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_ATM_EM_EFF_VER_CURRENT_DEL |     6 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
       
Predicate Information (identified by operation id):
---------------------------------------------------
     
   1 - filter(SYSDATE@!+4>=SYSDATE@!)
   3 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "EFFECTIVE_DT">=SYSDATE@! AND
              "SALARY_VERSION_NUMBER"=7 AND "IS_CURRENT"='YES' AND "EFFECTIVE_DT"<=SYSDATE@!+4)
       filter("SALARY_VERSION_NUMBER"=7 AND "EFFECTIVE_DT"=SYSDATE@! AND "IS_CURRENT"='YES')

SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and is_deleted = 'NO'
  5  and is_current = 'YES';
        
EMPLOYEE_KEY         EMP_FNAME            EMP_LNAME                                EFFECTIVE SALARY_VERSION_NUMBER IS_ IS_
-------------------- -------------------- ---------------------------------------- --------- --------------------- --- ---
Employee_10          Blobbo               Fingnestle                               22-MAR-16                    11 YES NO
Employee_100         Blobbo               Fingnestle                               22-MAR-16                     2 YES NO


Execution Plan
----------------------------------------------------------
Plan hash value: 1328304448
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |    99 |  4752 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID |  ATM_XXX_SALARY                |    99 |  4752 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | IDX_ATM_EM_EFF_VER_CURRENT_DEL |    99 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
        

Predicate Information (identified by operation id):
---------------------------------------------------
         
   2 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_CURRENT"='YES' AND "IS_DELETED"='NO')
       filter("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')
        
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and is_deleted = 'NO'
  5  and is_current = 'YES';
       
EMPLOYEE_KEY         EMP_FNAME            EMP_LNAME                                EFFECTIVE SALARY_VERSION_NUMBER IS_ IS_
-------------------- -------------------- ---------------------------------------- --------- --------------------- --- ---
Employee_10          Blobbo               Fingnestle                               22-MAR-16                    11 YES NO
Employee_100         Blobbo               Fingnestle                               22-MAR-16                     2 YES NO


Execution Plan
----------------------------------------------------------
Plan hash value: 1328304448
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |    99 |  4752 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ATM_XXX_SALARY                 |    99 |  4752 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | IDX_ATM_EM_EFF_VER_CURRENT_DEL |    99 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
         
Predicate Information (identified by operation id):
---------------------------------------------------
        
   2 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_CURRENT"='YES' AND "IS_DELETED"='NO')
       filter("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')

        
         
SQL>
SQL> set autotrace off

The above appears to answer the question and prove the remaining two indexes are not necessary, but it can’t hurt to test this theory by creating the remaining indexes and running the same set of queries after each index creation. Creating the second index apparently does no good as it isn’t used in favor of the first:

       
SQL> --
SQL> -- Create second index
SQL> --
SQL> CREATE INDEX IDX_ATM_EM_VER_CURRENT ON ATM_xxx_SALARY
  2  (EMPLOYEE_KEY, SALARY_VERSION_NUMBER, IS_CURRENT);
         
Index created.
         
SQL>
SQL>
SQL> set autotrace on
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and effective_dt between sysdate and sysdate+4
  5  and salary_version_number = 7
  6  and is_current = 'YES';
         
no rows selected
        
         
Execution Plan
----------------------------------------------------------
Plan hash value: 2729507590
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |     6 |   288 |     2   (0)| 00:00:01 |
|*  1 |  FILTER                       |                                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | ATM_XXX_SALARY                 |     6 |   288 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | IDX_ATM_EM_EFF_VER_CURRENT_DEL |     6 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
         
Predicate Information (identified by operation id):
---------------------------------------------------
         
   1 - filter(SYSDATE@!+4>=SYSDATE@!)
   3 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "EFFECTIVE_DT">=SYSDATE@! AND
              "SALARY_VERSION_NUMBER"=7 AND "IS_CURRENT"='YES' AND "EFFECTIVE_DT"<=SYSDATE@!+4)
       filter("SALARY_VERSION_NUMBER"=7 AND "EFFECTIVE_DT"=SYSDATE@! AND "IS_CURRENT"='YES')
         
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and is_deleted = 'NO'
  5  and is_current = 'YES';
      
EMPLOYEE_KEY         EMP_FNAME            EMP_LNAME                                EFFECTIVE SALARY_VERSION_NUMBER IS_ IS_
-------------------- -------------------- ---------------------------------------- --------- --------------------- --- ---
Employee_10          Blobbo               Fingnestle                               22-MAR-16                    11 YES NO
Employee_100         Blobbo               Fingnestle                               22-MAR-16                     2 YES NO
         
         
Execution Plan
----------------------------------------------------------
Plan hash value: 1328304448
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |    99 |  4752 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ATM_XXX_SALARY                 |    99 |  4752 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | IDX_ATM_EM_EFF_VER_CURRENT_DEL |    99 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
         
Predicate Information (identified by operation id):
---------------------------------------------------
         
   2 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_CURRENT"='YES' AND "IS_DELETED"='NO')
       filter("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')
         
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and is_deleted = 'NO'
  5  and is_current = 'YES';
      
EMPLOYEE_KEY         EMP_FNAME            EMP_LNAME                                EFFECTIVE SALARY_VERSION_NUMBER IS_ IS_
-------------------- -------------------- ---------------------------------------- --------- --------------------- --- ---
Employee_10          Blobbo               Fingnestle                               22-MAR-16                    11 YES NO
Employee_100         Blobbo               Fingnestle                               22-MAR-16                     2 YES NO
         
         
Execution Plan
----------------------------------------------------------
Plan hash value: 1328304448
         
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |    99 |  4752 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ATM_XXX_SALARY                 |    99 |  4752 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | IDX_ATM_EM_EFF_VER_CURRENT_DEL |    99 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
         
Predicate Information (identified by operation id):
---------------------------------------------------
         
   2 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_CURRENT"='YES' AND "IS_DELETED"='NO')
       filter("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')
         
SQL>
SQL> set autotrace off

Let’s create the third index and see what Oracle does:

       
SQL> --
SQL> -- Create third index
SQL> --
SQL> CREATE INDEX IDX_ATM_SAL_CURRENT_DEL ON ATM_xxx_SALARY
  2  (EMPLOYEE_KEY, IS_DELETED, IS_CURRENT);
         
Index created.
         
SQL>
SQL> set autotrace on
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and effective_dt between sysdate and sysdate+4
  5  and salary_version_number = 7
  6  and is_current = 'YES';
         
no rows selected
         
         
Execution Plan
----------------------------------------------------------
Plan hash value: 2729507590
         
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |     6 |   288 |     2   (0)| 00:00:01 |
|*  1 |  FILTER                       |                                |       |       |            |       |
|   2 |   TABLE ACCESS BY INDEX ROWID | ATM_XXX_SALARY                 |     6 |   288 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | IDX_ATM_EM_EFF_VER_CURRENT_DEL |     6 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
         
Predicate Information (identified by operation id):
---------------------------------------------------
         
   1 - filter(SYSDATE@!+4>=SYSDATE@!)
   3 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "EFFECTIVE_DT">=SYSDATE@! AND
              "SALARY_VERSION_NUMBER"=7 AND "IS_CURRENT"='YES' AND "EFFECTIVE_DT"<=SYSDATE@!+4)
       filter("SALARY_VERSION_NUMBER"=7 AND "EFFECTIVE_DT"=SYSDATE@! AND "IS_CURRENT"='YES')
         
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and is_deleted = 'NO'
  5  and is_current = 'YES';
         
EMPLOYEE_KEY         EMP_FNAME            EMP_LNAME                                EFFECTIVE SALARY_VERSION_NUMBER IS_ IS_
-------------------- -------------------- ---------------------------------------- --------- --------------------- --- ---
Employee_10          Blobbo               Fingnestle                               22-MAR-16                    11 YES NO
Employee_100         Blobbo               Fingnestle                               22-MAR-16                     2 YES NO
         
         
Execution Plan
---------------------------------------------------------
Plan hash value: 1562453262
         
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |    99 |  4752 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ATM_XXX_SALARY          |    99 |  4752 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | IDX_ATM_SAL_CURRENT_DEL |    99 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
         
Predicate Information (identified by operation id):
---------------------------------------------------
         
    2 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')
        filter("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')
         
SQL>
SQL> select *
  2  from atm_xxx_salary
  3  where employee_key like 'Emp%10%'
  4  and is_deleted = 'NO'
  5  and is_current = 'YES';
         
EMPLOYEE_KEY         EMP_FNAME            EMP_LNAME                                EFFECTIVE SALARY_VERSION_NUMBER IS_ IS_
-------------------- -------------------- ---------------------------------------- --------- --------------------- --- ---
Employee_10          Blobbo               Fingnestle                               22-MAR-16                    11 YES NO
Employee_100         Blobbo               Fingnestle                               22-MAR-16                     2 YES NO
         
         
Execution Plan
----------------------------------------------------------
Plan hash value: 1562453262
         
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |    99 |  4752 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ATM_XXX_SALARY          |    99 |  4752 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | IDX_ATM_SAL_CURRENT_DEL |    99 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
         
Predicate Information (identified by operation id):
---------------------------------------------------
         
   2 - access("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')
       filter("EMPLOYEE_KEY" LIKE 'Emp%10%' AND "IS_DELETED"='NO' AND "IS_CURRENT"='YES')

SQL>
SQL>
SQL> set autotrace off

Notice that the first and third indexes are used, with the third index used on the second and third queries. Remember that the first index was used on all three queries so given the presumptions made on the nature of the data the second and third indexes appear to not be needed. Of course this is not rigorous testing and other queries and/or data distributions could produce differing results. You really need to test, test, test, test and test again before making a final decision. But, the indications are good that a single index would suffice to provide “speedy” data retrieval.

Please don’t make me repeat myself.

Create a free website or blog at WordPress.com.