asked on

Need hint help in existing sql

Hello Experts,

I have the below SQL :

EXPLAIN PLAN FOR 
SELECT  QST_RESPONSE ,
  QST_RESPONSE_ATTACHMENT ,
  QST_RESPONSE_DATE ,
  QST_ALL_AREAS ,
  QST_APPLE_DEDICATED_AREAS ,
  QST_NON_PRODUCTION_UTILITIES ,
  QST_PRODUCTION_AREAS_DEDICATED ,
  QST_COMMON_PRODUCTION_USED
FROM MS_SRA_VISIT_QUESTIONS
WHERE QUESTION_ID                          = :B2
AND REGEXP_SUBSTR(VISIT_ID ,'[^-]+' ,1 ,2) = :B1
AND QST_TYPE                              <> '1'
AND DD_CREATED_ON                          =
  (SELECT /*+ index_ss(QS MS_SRA_VISIT_QUESTIONS_PK) */ MAX(qs.DD_CREATED_ON)
  FROM MS_SRA_VISIT_QUESTIONS QS
  WHERE QS.QUESTION_ID                          = :B2
  AND REGEXP_SUBSTR(QS.VISIT_ID ,'[^-]+' ,1 ,2) = :B1
  AND qs.QST_TYPE                              <> '1'
  ) ;

Open in new window

Plan hash value: 283024080
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |     1 |  1290 |    80   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID  | MS_SRA_VISIT_QUESTIONS    |     1 |  1290 |    40   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN             | MS_SRA_VISIT_QUESTIONS_PK |     1 |       |    40   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE              |                           |     1 |    32 |            |          |
|*  4 |    TABLE ACCESS BY INDEX ROWID| MS_SRA_VISIT_QUESTIONS    |     1 |    32 |    40   (0)| 00:00:01 |
|*  5 |     INDEX SKIP SCAN           | MS_SRA_VISIT_QUESTIONS_PK |     1 |       |    40   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_SS(@"SEL$2" "QS"@"SEL$2" ("MS_SRA_VISIT_QUESTIONS"."VISIT_ID" 
              "MS_SRA_VISIT_QUESTIONS"."QUESTION_ID" "MS_SRA_VISIT_QUESTIONS"."QST_SOURCE_OBJECT_TYPE"))
      PUSH_SUBQ(@"SEL$2")
      INDEX_SS(@"SEL$1" "MS_SRA_VISIT_QUESTIONS"@"SEL$1" ("MS_SRA_VISIT_QUESTIONS"."VISIT_ID" 
              "MS_SRA_VISIT_QUESTIONS"."QUESTION_ID" "MS_SRA_VISIT_QUESTIONS"."QST_SOURCE_OBJECT_TYPE"))
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      OPT_PARAM('optimizer_index_cost_adj' 40)
      OPT_PARAM('star_transformation_enabled' 'true')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_index_join_enabled' 'false')
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPT_PARAM('_complex_view_merging' 'false')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("QST_TYPE"<>'1' AND "DD_CREATED_ON"= (SELECT /*+ INDEX_SS ("QS" 
              "MS_SRA_VISIT_QUESTIONS_PK") */ MAX("QS"."DD_CREATED_ON") FROM "MS_SRA_VISIT_QUESTIONS" "QS" WHERE 
              "QS"."QST_TYPE"<>'1' AND "QS"."QUESTION_ID"=TO_NUMBER(:B2) AND  REGEXP_SUBSTR 
              ("QS"."VISIT_ID",'[^-]+',1,2)=:B1))
   2 - access("QUESTION_ID"=TO_NUMBER(:B2))
       filter("QUESTION_ID"=TO_NUMBER(:B2) AND  REGEXP_SUBSTR ("VISIT_ID",'[^-]+',1,2)=:B1)
   4 - filter("QS"."QST_TYPE"<>'1')
   5 - access("QS"."QUESTION_ID"=TO_NUMBER(:B2))
       filter("QS"."QUESTION_ID"=TO_NUMBER(:B2) AND  REGEXP_SUBSTR 
              ("QS"."VISIT_ID",'[^-]+',1,2)=:B1)

Open in new window

Want to change in such a way that the indexes should not be skiped and also the way IN condition are been used.

johnsone

The index is not being skipped. The index is still being used. The index being used is a composite index (it has more than one column). The first column in the index (VISIT_ID) cannot be used because of the REGEXPR_SUBSTR call on it. However, QUESTION_ID (the second column in the index) can be used. Oracle is smart enough now to be able to used that index to search for QUESTION_ID.

If you want an index range scan instead of an index skip scan, then create an index on QUESTION_ID. If you do that, I would probably add DD_CREATED_ON as a second column in that index. You may already have that indexed, but with the hint you have, you are forcing an index to be used.

You may also consider a function based index on the REGEXP_SUBSTR. Not sure if you would get good cardinality there or not.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Swadhin Ray

ASKER

thanks a lot