Solved

Best way to creat index on the column which is varchar2

Posted on 2013-02-06
20
640 Views
Last Modified: 2013-02-11

we have created an index for the above column but these column as data as follows  '000300000000000000000003026779'  that is the first 20 to 22 char is static, so the optimizer is not picking an index  and going for full table scan.

this particular table as millions of records, let me know which is the best way to implement and index so the optimizer can pick it up.

Note: we can't use Hints
0
Comment
Question by:praveencpk
  • 5
  • 4
  • 4
  • +4
20 Comments
 
LVL 20

Expert Comment

by:Amitkumar Panchal
ID: 38862894
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38862913
what is the query you are using and can you give the explain plan for the same ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38862927
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,
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38862938
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' ;
0
 
LVL 12

Author Comment

by:praveencpk
ID: 38862949
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'))))
 

Open in new window

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 38862957
I think first of all this is due to missing () in regards to the OR:
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' )
       ) 

Open in new window


which could be simplified then into:
AND ( contact_event.customer_id = '000900000000000000000000031946' 
        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' 
 

Open in new window


however, that OR will likely be the cause the full table scan.
what about starttime and prospect_id ? are they indexed?

I think the best here would be 2 indexes:
index 1 : customer_id + start_time
index 2 : prospect_id  + start_time

I will remove the db2 and sql server zones
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 50 total points
ID: 38862988
by looking at your query - i second the opinion to give a try to test the below options as already suggested above ?

index 1 : customer_id + start_time
index 2 : prospect_id  + start_time
0
 
LVL 12

Author Comment

by:praveencpk
ID: 38863137
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')
       )  

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38863145
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' 
 

Open 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' 

Open in new window


                                           
a UNION ALL query might give much better results ...
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 38863167
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' 

Open in new window

and it will be able to use the right index for each.
Regards,
Franck.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Author Comment

by:praveencpk
ID: 38863213
@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.
0
 
LVL 12

Author Comment

by:praveencpk
ID: 38864086
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'))

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38864148
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  )
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 38864962
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'))

Open 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.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 38865188
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38867546
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'

Open 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.
0
 
LVL 12

Author Comment

by:praveencpk
ID: 38867549
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38867594
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.
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 100 total points
ID: 38868249
If you can't change the query, then you have to play with the statistics.
1. Don't collect histograms for that column.
2. if it is not sufficient, you can set your own stats on that column using DBMS_STATS.SET_COLUMN_STATS Especially density and distinct count.

see about that 32 bytes issue here: here it's from the people that have coded the optimizer.

Regards,
Franck.
0
 
LVL 5

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 100 total points
ID: 38871667
Hi,

I see that you are mostly facing problem since your first 20 characters are static. Thus your index size might also be big. So, most of the times it is going for full table scan. Since you are sure that first 20 characters are static you can maybe ignore these in your index and create a function based index on the column customer_id i.e. substr(customer_id,20) means only consider the characters after the static 20 characters.

This will narrow down on the index and probably give you index scan. However, there is still possibility that if you are querying data for 2-3 years and the optimizer deems it fit to go for full scan instead then you might still get the full scan. Then you might have to think of restricting the search in other way or reduce the period.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now