Best way to creat index on the column which is varchar2

refer https://forums.oracle.com/forums/thread.jspa?threadID=2397854

what is the query you are using and can you give the explain plan for the same ?

I think that unless you get the data you really want to index for into a dedicated column (aka split up the column value into several colums), you cannot solve this.

as you posted in oracle, db2 and sql server, the solutions may vary.
in sql server, I would create computed columns
* either have the new computed column only the data that you want to index
* or have the data splitted into several normal columns, and have a computed column with the concatenated value ...

in any case you will need to change the query, eventually, to check against the column that has only the data you are looking for,

if you have created an index for that column and if you are running like the below query, then it should pick up.

select * from your_table
where your_indexed_column = '000300000000000000000003026779' ;

Here is the SQL and the explain plan

SELECT contact_history.contact_id, 
       contact_event.prospect_id, 
       contact_history.location_code_id, 
       contact_event.customer_id, 
       users.last_name, 
       users.user_name, 
       contact_event.end_time, 
       contact_event.account_number, 
       contact_event.start_time, 
       contact_event.customer_name, 
       users.first_name, 
       contact_history.contact_origin, 
       contact_history.note_text, 
       users.middle_initial, 
       contact_history.lob_id, 
       contact_event.prod_cat_name, 
       contact_subject_ml_info.is_visible, 
       contact_event.contact_name, 
       contact_event.folder_case_id, 
       contact_subject_ml_info.subject_description, 
       contact_event.subject_id, 
       contact_history.call_id, 
       contact_event.event_comment, 
       contact_event.referral_id, 
       contact_event.product_name, 
       contact_event.prospect_id 
FROM   contact_event, 
       contact_subject_ml_info, 
       contact_history, 
       users 
WHERE  contact_subject_ml_info.subject_id = contact_event.subject_id 
       AND contact_history.contact_id = contact_event.contact_id 
       AND users.user_id = contact_history.user_id 
       AND contact_subject_ml_info.language_id = 'en' 
       AND ( contact_event.customer_id = '000900000000000000000000031946' 
             AND contact_event.start_time >= To_date('2010-01-01 00:00:00', 
                                             'YYYY-MM-DD HH24:MIS') 
             AND contact_event.start_time <= To_date('2013-01-01 00:00:00', 
                                             'YYYY-MM-DD HH24:MIS') 
             AND contact_subject_ml_info.is_visible = 'Y' 
              OR contact_event.prospect_id = '5270179' 
                 AND contact_event.start_time >= To_date('2010-01-01 00:00:00', 
                                                 'YYYY-MM-DD HH24:MIS') 
                 AND contact_event.start_time <= To_date('2013-01-01 00:00:00', 
                                                 'YYYY-MM-DD HH24:MIS') 
                 AND contact_subject_ml_info.is_visible = 'Y' ) 
ORDER  BY contact_event.start_time, 
          contact_event.contact_event_id DESC  


Plan hash value: 3043090953

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |  1586K|   749M|       |   773K  (1)| 02:34:48 |
|   1 |  SORT ORDER BY                     |                     |  1586K|   749M|       |   773K  (1)| 02:34:48 |
|   2 |   CONCATENATION                    |                     |       |       |       |            |          |
|   3 |    NESTED LOOPS                    |                     |       |       |       |            |          |
|   4 |     NESTED LOOPS                   |                     |    10 |  4950 |       |    11   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                  |                     |    10 |  4280 |       |     9   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                     |    10 |  3130 |       |     7   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                     |    10 |  2420 |       |     5   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| CONTACT_EVENT       |    11 |  2585 |       |     3   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | I03_CONTACT_EVENT   |    11 |       |       |     1   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| CONTACT_SUBJECT     |     1 |     7 |       |     1   (0)| 00:00:01 |
|* 11 |          INDEX UNIQUE SCAN         | PKC_CONTACT_SUBJECT |     1 |       |       |     1   (0)| 00:00:01 |
|  12 |        TABLE ACCESS BY INDEX ROWID | CONTACT_SUBJECT_ML  |     1 |    71 |       |     1   (0)| 00:00:01 |
|* 13 |         INDEX UNIQUE SCAN          | PKC_CONTACT_SUB_ML  |     1 |       |       |     1   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID  | CONTACT_HISTORY     |     1 |   115 |       |     1   (0)| 00:00:01 |
|* 15 |        INDEX UNIQUE SCAN           | PKC_CONTACT_HISTORY |     1 |       |       |     1   (0)| 00:00:01 |
|* 16 |      INDEX UNIQUE SCAN             | PKC_USERS           |     1 |       |       |     1   (0)| 00:00:01 |
|  17 |     TABLE ACCESS BY INDEX ROWID    | USERS               |     1 |    67 |       |     1   (0)| 00:00:01 |
|  18 |    NESTED LOOPS                    |                     |       |       |       |            |          |
|  19 |     NESTED LOOPS                   |                     |     6 |  2970 |       |     8   (0)| 00:00:01 |
|  20 |      NESTED LOOPS                  |                     |     6 |  2568 |       |     7   (0)| 00:00:01 |
|  21 |       NESTED LOOPS                 |                     |     6 |  1878 |       |     5   (0)| 00:00:01 |
|  22 |        NESTED LOOPS                |                     |     6 |  1452 |       |     4   (0)| 00:00:01 |
|* 23 |         TABLE ACCESS BY INDEX ROWID| CONTACT_EVENT       |     6 |  1410 |       |     3   (0)| 00:00:01 |
|* 24 |          INDEX RANGE SCAN          | I03_CONTACT_EVENT   |    11 |       |       |     1   (0)| 00:00:01 |
|* 25 |         TABLE ACCESS BY INDEX ROWID| CONTACT_SUBJECT     |     1 |     7 |       |     1   (0)| 00:00:01 |
|* 26 |          INDEX UNIQUE SCAN         | PKC_CONTACT_SUBJECT |     1 |       |       |     1   (0)| 00:00:01 |
|  27 |        TABLE ACCESS BY INDEX ROWID | CONTACT_SUBJECT_ML  |     1 |    71 |       |     1   (0)| 00:00:01 |
|* 28 |         INDEX UNIQUE SCAN          | PKC_CONTACT_SUB_ML  |     1 |       |       |     1   (0)| 00:00:01 |
|  29 |       TABLE ACCESS BY INDEX ROWID  | CONTACT_HISTORY     |     1 |   115 |       |     1   (0)| 00:00:01 |
|* 30 |        INDEX UNIQUE SCAN           | PKC_CONTACT_HISTORY |     1 |       |       |     1   (0)| 00:00:01 |
|* 31 |      INDEX UNIQUE SCAN             | PKC_USERS           |     1 |       |       |     1   (0)| 00:00:01 |
|  32 |     TABLE ACCESS BY INDEX ROWID    | USERS               |     1 |    67 |       |     1   (0)| 00:00:01 |
|* 33 |    HASH JOIN                       |                     |  1586K|   749M|       |   211K  (1)| 00:42:20 |
|  34 |     TABLE ACCESS FULL              | USERS               |  7326 |   479K|       |    71   (0)| 00:00:01 |
|* 35 |     HASH JOIN                      |                     |  1586K|   647M|       |   211K  (1)| 00:42:19 |
|* 36 |      TABLE ACCESS FULL             | CONTACT_SUBJECT_ML  |   413 | 29323 |       |     4   (0)| 00:00:01 |
|* 37 |      HASH JOIN                     |                     |  1586K|   540M|       |   211K  (1)| 00:42:19 |
|* 38 |       TABLE ACCESS FULL            | CONTACT_SUBJECT     |   413 |  2891 |       |     4   (0)| 00:00:01 |
|* 39 |       HASH JOIN                    |                     |  1586K|   529M|   373M|   211K  (1)| 00:42:19 |
|* 40 |        TABLE ACCESS FULL           | CONTACT_EVENT       |  1586K|   355M|       | 53438   (1)| 00:10:42 |
|  41 |        TABLE ACCESS FULL           | CONTACT_HISTORY     |  9999K|  1096M|       | 79427   (1)| 00:15:54 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - filter("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   9 - access("CONTACT_EVENT"."PROSPECT_ID"=U'5270179')
  10 - filter("A"."IS_VISIBLE"=U'Y' AND ("CONTACT_EVENT"."CUSTOMER_ID"=U'000900000000000000000000031946' 
              AND "A"."IS_VISIBLE"=U'Y' AND "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
              OR "CONTACT_EVENT"."PROSPECT_ID"=U'5270179' AND "A"."IS_VISIBLE"=U'Y' AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  11 - access("A"."SUBJECT_ID"="CONTACT_EVENT"."SUBJECT_ID")
  13 - access("A"."SUBJECT_ID"="B"."SUBJECT_ID" AND "B"."LANGUAGE_ID"=U'en')
  15 - access("CONTACT_HISTORY"."CONTACT_ID"="CONTACT_EVENT"."CONTACT_ID")
  16 - access("USERS"."USER_ID"="CONTACT_HISTORY"."USER_ID")
  23 - filter("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."CUSTOMER_ID"=U'000900000000000000000000031946' AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              (LNNVL("CONTACT_EVENT"."PROSPECT_ID"=U'5270179') OR LNNVL("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 
              2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR LNNVL("CONTACT_EVENT"."START_TIME">=TO_DATE(' 
              2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
  24 - access("CONTACT_EVENT"."PROSPECT_ID"=U'5270179')
  25 - filter("A"."IS_VISIBLE"=U'Y' AND "A"."IS_VISIBLE"=U'Y')
  26 - access("A"."SUBJECT_ID"="CONTACT_EVENT"."SUBJECT_ID")
  28 - access("A"."SUBJECT_ID"="B"."SUBJECT_ID" AND "B"."LANGUAGE_ID"=U'en')
  30 - access("CONTACT_HISTORY"."CONTACT_ID"="CONTACT_EVENT"."CONTACT_ID")
  31 - access("USERS"."USER_ID"="CONTACT_HISTORY"."USER_ID")
  33 - access("USERS"."USER_ID"="CONTACT_HISTORY"."USER_ID")
  35 - access("A"."SUBJECT_ID"="B"."SUBJECT_ID")
  36 - filter("B"."LANGUAGE_ID"=U'en')
  37 - access("A"."SUBJECT_ID"="CONTACT_EVENT"."SUBJECT_ID")
       filter(LNNVL("CONTACT_EVENT"."PROSPECT_ID"=U'5270179') OR LNNVL("A"."IS_VISIBLE"=U'Y') OR 
              LNNVL("CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR 
              LNNVL("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  38 - filter("A"."IS_VISIBLE"=U'Y' AND "A"."IS_VISIBLE"=U'Y')
  39 - access("CONTACT_HISTORY"."CONTACT_ID"="CONTACT_EVENT"."CONTACT_ID")
  40 - filter("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."CUSTOMER_ID"=U'000900000000000000000000031946' AND 
              "CONTACT_EVENT"."CUSTOMER_ID"=U'000900000000000000000000031946' AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              (LNNVL("CONTACT_EVENT"."PROSPECT_ID"=U'5270179') OR LNNVL("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 
              2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR LNNVL("CONTACT_EVENT"."START_TIME">=TO_DATE(' 
              2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
 

Select allOpen in new window

This didn't hepl us still you are seeing full table access below is the explain plan for your refference.

Plan hash value: 3299804944
 
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |  5361K|  2500M|       |   254K  (1)| 00:50:51 |
|*  1 |  HASH JOIN            |                    |  5361K|  2500M|       |   254K  (1)| 00:50:51 |
|   2 |   TABLE ACCESS FULL   | USERS              |  7326 |   479K|       |    71   | 00:00:01 |
|*  3 |   HASH JOIN           |                    |  5361K|  2157M|       |   254K  (1)| 00:50:50 |
|*  4 |    TABLE ACCESS FULL  | CONTACT_SUBJECT_ML |   413 | 29323 |       |     4   | 00:00:01 |
|*  5 |    HASH JOIN          |                    |  5361K|  1794M|       |   254K  (1)| 00:50:50 |
|*  6 |     TABLE ACCESS FULL | CONTACT_SUBJECT    |   413 |  2891 |       |     4   | 00:00:01 |
|*  7 |     HASH JOIN         |                    |  5361K|  1758M|  1211M|   254K  (1)| 00:50:49 |
|   8 |      TABLE ACCESS FULL| CONTACT_HISTORY    |  9999K|  1096M|       | 79427   (1)| 00:15:54 |
|*  9 |      TABLE ACCESS FULL| CONTACT_EVENT      |  5361K|  1170M|       | 53399   (1)| 00:10:41 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("USERS"."USER_ID"="CONTACT_HISTORY"."USER_ID")
   3 - access("A"."SUBJECT_ID"="B"."SUBJECT_ID")
   4 - filter("B"."LANGUAGE_ID"=U'en')
   5 - access("A"."SUBJECT_ID"="CONTACT_EVENT"."SUBJECT_ID")
   6 - filter("A"."IS_VISIBLE"=U'Y')
   7 - access("CONTACT_HISTORY"."CONTACT_ID"="CONTACT_EVENT"."CONTACT_ID")
   9 - filter("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd 
              hh24:mis') AND ("CONTACT_EVENT"."CUSTOMER_ID"=U'000900000000000000000000031946' OR 
              "CONTACT_EVENT"."PROSPECT_ID"=U'5270179') 
       AND "CONTACT_EVENT"."START_TIME">=TO_DATE(' 
              2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mis')
       )  

Select allOpen in new window

did you modify the query as I suggested? as the OR without the brackets will presumably indeed do the full table scan anyhow.

also, how may records are in the table, and how many are returned?

what if you search for "either" :

AND contact_event.customer_id = '000900000000000000000000031946' 
  AND contact_event.start_time >= To_date('2010-01-01 00:00:00', 
                                             'YYYY-MM-DD HH24:MIS') 
 AND contact_event.start_time <= To_date('2013-01-01 00:00:00', 
                                             'YYYY-MM-DD HH24:MIS') 
 AND contact_subject_ml_info.is_visible = 'Y' 
 

Select allOpen in new window

and in a second query:

AND contact_event.prospect_id = '5270179' 
  AND contact_event.start_time >= To_date('2010-01-01 00:00:00', 
                                             'YYYY-MM-DD HH24:MIS') 
 AND contact_event.start_time <= To_date('2013-01-01 00:00:00', 
                                             'YYYY-MM-DD HH24:MIS') 
 AND contact_subject_ml_info.is_visible = 'Y' 

Select allOpen in new window

                                           
a UNION ALL query might give much better results ...

Hi,
The problem has nothing to do about the fact that the first 20 characters are static, but because of the OR. As you have no index to acces a specific prospect_id, a full table scan is mandatory.
With both indexes on customer_id  and on prospect_id  you can expect the optimizer to do an OR Expansion transformation, so that it can do something like:

... where contact_event.customer_id = '000900000000000000000000031946'
union all
... contact_event.prospect_id = '5270179' 

Select allOpen in new window

and it will be able to use the right index for each.
Regards,
Franck.

@angelIII
We have used your modified query from the post 38862957 with adding the compsoite index as mention in your post.

@angelIII  and @Franck
Yeah its agood catch we can try union all , let me try that and update you guys.

we checked  with the application team and removed the OR condition from the SQL, but still the query is going for full table scan. here is the SQL and explain plan.

Note: There is the index on customer_id +start_time

SELECT 
       contact_history.contact_id,
       contact_event.prospect_id,
       contact_history.location_code_id,
       contact_event.customer_id,
       users.last_name,
       users.user_name,
       contact_event.end_time,
       contact_event.account_number,
       contact_event.start_time,
       contact_event.customer_name,
       users.first_name,
       contact_history.contact_origin,
       contact_history.note_text,
       users.middle_initial,
       contact_history.lob_id,
       contact_event.prod_cat_name,
       contact_subject_ml_info.is_visible,
       contact_event.contact_name,
       contact_event.folder_case_id,
       contact_subject_ml_info.subject_description,
       contact_event.subject_id,
       contact_history.call_id,
       contact_event.event_comment,
       contact_event.referral_id,
       contact_event.product_name,
       contact_event.prospect_id
FROM   contact_event,
       contact_subject_ml_info,
       contact_history,
       users
WHERE  contact_subject_ml_info.subject_id = contact_event.subject_id
       AND contact_history.contact_id = contact_event.contact_id
       AND users.user_id = contact_history.user_id
       AND contact_subject_ml_info.language_id = 'en'
       AND ( contact_event.customer_id = '000900000000000000000000031947'  )
        AND (contact_event.start_time) >= To_date('2010-01-01 00:00:00',
                                            'YYYY-MM-DD HH24:MI:SS')
                                            
        AND (contact_event.start_time )<= To_date('2013-01-01 00:00:00',
                                             'YYYY-MM-DD HH24:MI:SS')
        AND contact_subject_ml_info.is_visible = 'Y'
             
          
ORDER  BY contact_event.start_time,
          contact_event.contact_event_id DESC  ;




Plan hash value: 1990217778

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |  2916K|  1376M|       |   533K  (1)| 01:46:38 |
|   1 |  SORT ORDER BY         |                    |  2916K|  1376M|  1519M|   533K  (1)| 01:46:38 |
|*  2 |   HASH JOIN            |                    |  2916K|  1376M|       |   227K  (1)| 00:45:26 |
|   3 |    TABLE ACCESS FULL   | USERS              |  7326 |   479K|       |    71   (0)| 00:00:01 |
|*  4 |    HASH JOIN           |                    |  2916K|  1190M|       |   227K  (1)| 00:45:25 |
|*  5 |     TABLE ACCESS FULL  | CONTACT_SUBJECT_ML |   413 | 29323 |       |     4   (0)| 00:00:01 |
|*  6 |     HASH JOIN          |                    |  2916K|   993M|       |   227K  (1)| 00:45:25 |
|*  7 |      TABLE ACCESS FULL | CONTACT_SUBJECT    |   413 |  2891 |       |     4   (0)| 00:00:01 |
|*  8 |      HASH JOIN         |                    |  2916K|   973M|   687M|   227K  (1)| 00:45:25 |
|*  9 |       TABLE ACCESS FULL| CONTACT_EVENT      |  2916K|   653M|       | 53392   (1)| 00:10:41 |
|  10 |       TABLE ACCESS FULL| CONTACT_HISTORY    |  9999K|  1096M|       | 79427   (1)| 00:15:54 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("USERS"."USER_ID"="CONTACT_HISTORY"."USER_ID")
   4 - access("A"."SUBJECT_ID"="B"."SUBJECT_ID")
   5 - filter("B"."LANGUAGE_ID"=U'en')
   6 - access("A"."SUBJECT_ID"="CONTACT_EVENT"."SUBJECT_ID")
   7 - filter("A"."IS_VISIBLE"=U'Y')
   8 - access("CONTACT_HISTORY"."CONTACT_ID"="CONTACT_EVENT"."CONTACT_ID")
   9 - filter("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "CONTACT_EVENT"."CUSTOMER_ID"=U'000900000000000000000000031947' AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Select allOpen in new window

give the fact that you retrieve 3 years of data even though for a single customer, oracle might indeed decide, based on the stats (btw, did you gather the stats on the table and indexes recently/regularly ? ) that the full table scan might be more efficient ...

you could check what happens if you query for 1 single day instead of 3 years of data.

this said ...  
I suggest (if you can) to add the column "reduced_customer_id", and populate if with only the "relevant" part, so  31947  instead of '000900000000000000000000031947'  (and also as numerical data type)
=> you may want to copy the table if you shall not modify the current (prod) table for testing ...
put the index reduced_customer_id + start_date , and query based on that field

AND ( contact_event.reduced_customer_id = 31947  )

Hi,
About the problem may come from the first 20 static values, I thought that histograms recognize the first 32 bytes.
-> What is your characterset ?
-> do you have histogram on that column ?

the CBO estimates that 2916K rows are coming from the predicate. Can you check the truth:

select count(*) from "CONTACT_EVENT" where
("CONTACT_EVENT"."START_TIME"<=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "CONTACT_EVENT"."CUSTOMER_ID"=U'000900000000000000000000031947' AND 
              "CONTACT_EVENT"."START_TIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Select allOpen in new window

Oh just when pasting that I see the  U'...' literal !
It's not documented in Oracle docs. Only N'...' for national characterset.
Can you try without that U ?

Regards,
Franck.

Hi,

Even if I did not find a documentation, my test shows that U'...' is show in the execution plan predicates because your column is a NVARCHAR2, using rhe national character set that is probably AL16UTF16. Your 20 'static' digits are 40 bytes, so the histograms looks like you have the same value in all rows, leading to a full table scan.

Solution:  
 - don't store that ID in a NVARCHAR as you should not expect non-ASCII characters in it. I'ts just a waste of space
 - store the id in two columns, the first one being the static part. Then you can optimize the index using key compression
 - don't store that static prefix at all if you know it is always the same

Workarounds:
 - try a reverse index (just an idea, I haven't check myself if it solve the 32 bytes histogram issue)
 - don't gather histogram on that column (to be tested as well, I don't know if the issue occurs with the high/low value column stat)

Regards,
Franck

I resisted temptation to get involved here, but I do NOT believe the issue relates to the index on the contact_event.customer_id, but you need to be sure this is true first.

explain plan for
select
/* choose a few fields to include, not many*/
FROM   contact_event,
       contact_subject_ml_info,
       contact_history,
       users
WHERE  contact_subject_ml_info.subject_id = contact_event.subject_id
       AND contact_history.contact_id = contact_event.contact_id
       AND users.user_id = contact_history.user_id
       AND contact_subject_ml_info.language_id = 'en'
       AND contact_event.customer_id = '000900000000000000000000031947'

Select allOpen in new window

Does this explain plan include a full table scan?

If not then you can be more comfortable with the other advice already given.

Thanks for all your comments

we already tried reverse key index but that didn't help us. But spliting of column customer_id cann't be done as of now since it is reffernce in many senarios.

Yes we have gathered the stats for tables and indexes.

>>>the CBO estimates that 2916K rows are coming from the predicate. Can you check the truth:
The truth was we got some 1000 records.

ooops we crossed-over...

I was going on to say, I haven't yet seen proof that the issue is an ignored index on contact_event.customer_id

Try the reduced query I suggested, please provide that explain plan.