Home » RDBMS Server » Performance Tuning » Query tuning - index suggestion (Oracle,10g)
Query tuning - index suggestion [message #550041] Thu, 05 April 2012 08:07 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

The below query is taking more time to complete. I am planning to create an index to avoid the full table scan on EDX_RPT_SERVICE_DIM table.

  16 - filter(NVL("B"."SERVICE_CATEGORY_X",'TEL')='TEL' AND "B"."CUST_GROUP_ID_X"=TO_NUMBER('|| p_cust_grp_id || ') AND
              "B"."SERVICE_TYPE_X"='U')


I need your help to identify the best way of indexing these columns.

I have given the couple of method below whatever I know. I am not sure which will work best here. Pls suggest on this.

Method 1:

Create 2 indexes. One is a function based index on SERVICE_CATEGORY_X column. and another one composite index on (CUST_GROUP_ID_X,SERVICE_TYPE_X).

Method 2:

Create one composite index to cover all these 3 columns.




SQL> select table_name,last_analyzed,num_rows,partitioned from user_tables where table_name ='EDX_RPT_SERVICE_DIM';

TABLE_NAME                     LAST_ANAL   NUM_ROWS PAR
------------------------------ --------- ---------- ---
EDX_RPT_SERVICE_DIM            30-MAR-12   93296688 YES


Existing indexes on this table:
=============================

INDEX_NAME                     COLUMN_NAME
------------------------------ ---------------------------------------------
NK_ERSD_SERVICENUM_X           CUST_GROUP_ID_X
NK_ERSD_SERVICENUM_X           ACCOUNT_KEY
NK_ERSD_SERVICENUM_X           SERVICE_NUM
NK_ERSD_SERVICENUM_X           SERVICE_TYPE_X
NK_ERSD_MASTERSERVICEKEY_X     CUST_GROUP_ID_X
NK_ERSD_MASTERSERVICEKEY_X     ACCOUNT_KEY
NK_ERSD_MASTERSERVICEKEY_X     MASTER_SERVICE_KEY_X
ERSD_RL                        CUST_GROUP_ID_X
ERSD_RL                        ACCOUNT_KEY
ERSD_RL                        SERVICE_NUM
ERSD_RL                        SERVICE_TYPE_X
ERSD_RL                        SERVICE_KEY
PK_EDX_RPT_SERVICE_DIM         CUST_GROUP_ID_X
PK_EDX_RPT_SERVICE_DIM         SERVICE_KEY
PK_EDX_RPT_SERVICE_DIM         ACCOUNT_KEY



Query:
======

SELECT
     "EXT_SOURCE_DATA_CU12"."ACCOUNT_KEY" "ACCOUNT_KEY",
     "EXT_SOURCE_DATA_CU12"."REPLACED_SERVICE_KEY" "SERVICE_KEY",
     "EDX_RPT_TARIFF_DIM"."TARIFF_KEY",
     TO_DATE( "EXT_SOURCE_DATA_CU12"."FIELD5"||"EXT_SOURCE_DATA_CU12"."FIELD6" ,'YYYYMMDDHH24MISS') "CALLED_DATE",
     TO_TIMESTAMP( "EXT_SOURCE_DATA_CU12"."FIELD5"||"EXT_SOURCE_DATA_CU12"."FIELD6" ,'YYYYMMDDHH24MISS') "CALLED_TIME",
     "EXT_SOURCE_DATA_CU12"."FIELD7",
     "EXT_SOURCE_DATA_CU12"."FIELD8" CALLED_NUM,
     SUBSTR( "EXT_SOURCE_DATA_CU12"."FIELD9" ,1,2)*3600 + SUBSTR( "EXT_SOURCE_DATA_CU12"."FIELD9" ,4,2)*60 + SUBSTR( "EXT_SOURCE_DATA_CU12"."FIELD9" ,7,2) "DURATION",
     "EXT_SOURCE_DATA_CU12"."FIELD10",
     "EXT_SOURCE_DATA_CU12"."FIELD11",
     "EXT_SOURCE_DATA_CU12"."FIELD12",
     "EXT_SOURCE_DATA_CU12"."FIELD13",
     "EXT_SOURCE_DATA_CU12"."FIELD14",
     "EXT_SOURCE_DATA_CU12"."FIELD15",
     "EXT_SOURCE_DATA_CU12"."FIELD16",
     "EXT_SOURCE_DATA_CU12"."FIELD17",
     "EDX_RPT_USAGE_TYPE_DIM"."USAGE_TYPE_KEY",
      TO_NUMBER ('|| etl_key || ') "ETL_KEY",
     '|| p_cust_grp_id ||' "P_CUST_GROUP_ID",
     TO_DATE( "EXT_SOURCE_DATA_CU12"."FIELD32" , 'YYYYMMDD') "LOAD_DATE",
      "EXT_SOURCE_DATA_CU12"."FIELD19",
     "EXT_SOURCE_DATA_CU12"."FIELD20",
     "EXT_SOURCE_DATA_CU12"."FIELD21",
     "EXT_SOURCE_DATA_CU12"."FIELD22",
     "EXT_SOURCE_DATA_CU12"."FIELD23",
     "EXT_SOURCE_DATA_CU12"."FIELD24",
     "EXT_SOURCE_DATA_CU12"."FIELD25",
     "EXT_SOURCE_DATA_CU12"."FIELD26",
     "EXT_SOURCE_DATA_CU12"."FIELD27",
     "EXT_SOURCE_DATA_CU12"."FIELD28",
     TO_DATE( "EXT_SOURCE_DATA_CU12"."FIELD29" , 'YYYYMMDD') "FLEX_FIELD_DATE1",
     TO_DATE( "EXT_SOURCE_DATA_CU12"."FIELD30" , 'YYYYMMDD') "FLEX_FIELD_DATE2",
     -2 "CONST_MINUS_2" /*,
     ROW_NUMBER() over (PARTITION BY
     "EDX_RPT_ACCOUNT_DIM"."ACCOUNT_KEY",
     "EDX_RPT_SERVICE_DIM"."SERVICE_KEY",
     "EXT_SOURCE_DATA_CU12"."FIELD5",
     "EXT_SOURCE_DATA_CU12"."FIELD6",
     "EXT_SOURCE_DATA_CU12"."FIELD31",
     "EXT_SOURCE_DATA_CU12"."FIELD8",
     "EXT_SOURCE_DATA_CU12"."FIELD12",
     "EXT_SOURCE_DATA_CU12"."FIELD9" ORDER BY "FIELD10" desc) As "RANK_POS"*/
     FROM
     "OLAP"."EDX_RPT_ACCOUNT_DIM"  "EDX_RPT_ACCOUNT_DIM",
     "OLAP"."EDX_RPT_SERVICE_DIM"  "EDX_RPT_SERVICE_DIM",
     "OLAP"."EDX_RPT_USAGE_TYPE_DIM"  "EDX_RPT_USAGE_TYPE_DIM",
     "OLAP"."EDX_RPT_TARIFF_DIM"  "EDX_RPT_TARIFF_DIM",
     (SELECT "EXT_SOURCE_DATA_CU12".* , "TAB_B"."ACCOUNT_KEY", "TAB_B"."REPLACED_SERVICE_KEY"
   FROM
     "OLAP"."EXT_SOURCE_DATA_CU14" "EXT_SOURCE_DATA_CU12",
        (SELECT "TAB_A"."ACCOUNT_NUM", "TAB_A"."SERVICE_NOT_PRESENT",
                "TAB_A"."ACCOUNT_KEY", "TAB_A"."SERVICE_NUM" "REPLACED_SERVICE_NUMBER",
                "TAB_A"."SERVICE_KEY" "REPLACED_SERVICE_KEY"
           FROM (SELECT "EXT_ACCT_NUM"."ACCOUNT_NUM", "SERVICE_NOT_PRESENT",
                        "SD"."ACCOUNT_KEY", "SD"."SERVICE_NUM", "SD"."SERVICE_KEY",
                        RANK () OVER (PARTITION BY "SD"."ACCOUNT_KEY" ORDER BY "SERVICE_NAME" ASC)
                                                                  SERVICE_RANK
                   FROM (SELECT DISTINCT "FIELD2" "ACCOUNT_NUM",
                                         "FIELD3" "SERVICE_NOT_PRESENT"
                                    FROM "OLAP"."EXT_SOURCE_DATA_CU14" "A"
                                   WHERE "A"."FIELD1" = '4100'
                                     AND NOT EXISTS (
                                            SELECT 1
                                              FROM "OLAP"."EDX_RPT_SERVICE_DIM" "B"
                                             WHERE "B"."CUST_GROUP_ID_X" = '|| p_cust_grp_id || '  --  1
                                               AND "B"."SERVICE_TYPE_X" = 'U'
                                               AND NVL("B"."SERVICE_CATEGORY_X", 'TEL') = 'TEL'
                                               AND "B"."SERVICE_NUM" = "A"."FIELD3")) "EXT_ACCT_NUM",
                        "OLAP"."EDX_RPT_ACCOUNT_DIM" "AD",
                        "OLAP"."EDX_RPT_SERVICE_DIM" "SD"
                  WHERE ("EXT_ACCT_NUM"."ACCOUNT_NUM" = "AD"."ACCOUNT_NUM")
                    AND ("AD"."ACCOUNT_KEY" = "SD"."ACCOUNT_KEY" )
                    AND ("AD"."CUST_GROUP_ID_X" = "SD"."CUST_GROUP_ID_X" )
                    AND ("AD"."CUST_GROUP_ID_X" = '|| p_cust_grp_id || ' )  -- 1
                    AND ("SD"."CUST_GROUP_ID_X" = '|| p_cust_grp_id || ' ) -- 1
                    AND (NVL("AD"."BILLER_ID",'CSS') = 'CSS' )
                    AND ("SD"."SERVICE_TYPE_X" = 'U' )
                    AND (NVL("SD"."SERVICE_CATEGORY_X" , 'TEL') = 'TEL' )
                    AND ("SD"."END_DATE" IS NULL)) "TAB_A"
          WHERE "TAB_A"."SERVICE_RANK" = 1) "TAB_B"
  WHERE "TAB_B"."ACCOUNT_NUM" = "EXT_SOURCE_DATA_CU12"."FIELD2"
    AND "TAB_B"."SERVICE_NOT_PRESENT" = "EXT_SOURCE_DATA_CU12"."FIELD3") "EXT_SOURCE_DATA_CU12"
     WHERE
     ( "EXT_SOURCE_DATA_CU12"."FIELD1"  ='4100') AND
     ( "EDX_RPT_ACCOUNT_DIM"."ACCOUNT_NUM" = "EXT_SOURCE_DATA_CU12"."FIELD2" ) AND
     ( "EDX_RPT_ACCOUNT_DIM"."ACCOUNT_KEY" = "EDX_RPT_SERVICE_DIM"."ACCOUNT_KEY" ) AND
     ( "EXT_SOURCE_DATA_CU12"."ACCOUNT_KEY" =  "EDX_RPT_ACCOUNT_DIM"."ACCOUNT_KEY") AND
     ( "EXT_SOURCE_DATA_CU12"."REPLACED_SERVICE_KEY" = "EDX_RPT_SERVICE_DIM"."SERVICE_KEY" ) AND
     ( "EDX_RPT_ACCOUNT_DIM"."CUST_GROUP_ID_X" = "EXT_SOURCE_DATA_CU12"."FIELD31" ) AND
     ( "EDX_RPT_SERVICE_DIM"."CUST_GROUP_ID_X"  = "EXT_SOURCE_DATA_CU12"."FIELD31" ) AND
     ( "EDX_RPT_ACCOUNT_DIM"."CUST_GROUP_ID_X" = '|| p_cust_grp_id || ' ) AND
     ( "EDX_RPT_SERVICE_DIM"."CUST_GROUP_ID_X"  = '|| p_cust_grp_id || ' ) AND
      ( "EDX_RPT_ACCOUNT_DIM"."END_DT" IS NULL ) AND
      ( "EDX_RPT_SERVICE_DIM"."SERVICE_TYPE_X" = 'U' ) AND
      ( NVL(TRIM( "EXT_SOURCE_DATA_CU12"."FIELD4") ,'UNK') = "EDX_RPT_TARIFF_DIM"."TARIFF_CD" ) AND
      ( UPPER(NVL(TRIM( "EXT_SOURCE_DATA_CU12"."FIELD4") ,'UNK')) = 'EIM9060' ) AND
      ( ("EDX_RPT_TARIFF_DIM"."FLEX_FIELD1" = "EDX_RPT_USAGE_TYPE_DIM"."USAGE_TYPE_CD"));




---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                            |     1 |  1253 |  7800   (3)| 00:01:34 |       |       |
|   1 |  NESTED LOOPS                                  |                            |     1 |  1253 |  7800   (3)| 00:01:34 |       |       |
|   2 |   NESTED LOOPS                                 |                            |     1 |  1242 |  7799   (3)| 00:01:34 |       |       |
|   3 |    NESTED LOOPS                                |                            |     1 |  1225 |  7798   (3)| 00:01:34 |       |       |
|   4 |     NESTED LOOPS                               |                            |     1 |  1204 |  7796   (3)| 00:01:34 |       |       |
|*  5 |      HASH JOIN                                 |                            |     1 |  1179 |  7794   (3)| 00:01:34 |       |       |
|*  6 |       VIEW                                     |                            |     1 |  1063 |  7791   (3)| 00:01:34 |       |       |
|*  7 |        WINDOW SORT PUSHED RANK                 |                            |     1 |  1344 |  7791   (3)| 00:01:34 |       |       |
|   8 |         VIEW                                   |                            |     1 |  1344 |  7790   (3)| 00:01:34 |       |       |
|   9 |          HASH UNIQUE                           |                            |     1 |   187 |  7790   (3)| 00:01:34 |       |       |
|* 10 |           TABLE ACCESS BY LOCAL INDEX ROWID    | EDX_RPT_SERVICE_DIM        |     1 |    84 |     3   (0)| 00:00:01 |       |       |
|  11 |            NESTED LOOPS                        |                            |     1 |   187 |  7789   (3)| 00:01:34 |       |       |
|  12 |             NESTED LOOPS                       |                            |     1 |   103 |  7786   (3)| 00:01:34 |       |       |
|* 13 |              HASH JOIN ANTI                    |                            |   112 |  5824 |  7450   (3)| 00:01:30 |       |       |
|* 14 |               EXTERNAL TABLE ACCESS FULL       | EXT_SOURCE_DATA_CU18       |   307 |  8903 |     3   (0)| 00:00:01 |       |       |
|  15 |               PARTITION RANGE SINGLE           |                            |   368K|  8266K|  7444   (3)| 00:01:30 |   KEY |   KEY |
|* 16 |                TABLE ACCESS FULL               | EDX_RPT_SERVICE_DIM        |   368K|  8266K|  7444   (3)| 00:01:30 |   KEY |   KEY |
|  17 |              PARTITION RANGE SINGLE            |                            |     1 |    51 |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 18 |               TABLE ACCESS BY LOCAL INDEX ROWID| EDX_RPT_ACCOUNT_DIM        |     1 |    51 |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 19 |                INDEX RANGE SCAN                | ERAD_RL                    |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  20 |             PARTITION RANGE SINGLE             |                            |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 21 |              INDEX RANGE SCAN                  | NK_ERSD_MASTERSERVICEKEY_X |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 22 |       EXTERNAL TABLE ACCESS FULL               | EXT_SOURCE_DATA_CU18       |     3 |   348 |     3   (0)| 00:00:01 |       |       |
|  23 |      PARTITION RANGE SINGLE                    |                            |     1 |    25 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 24 |       TABLE ACCESS BY LOCAL INDEX ROWID        | EDX_RPT_ACCOUNT_DIM        |     1 |    25 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 25 |        INDEX UNIQUE SCAN                       | PK_EDX_RPT_ACCOUNT_DIM     |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  26 |     PARTITION RANGE SINGLE                     |                            |     1 |    21 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 27 |      INDEX RANGE SCAN                          | ERSD_RL                    |     1 |    21 |     2   (0)| 00:00:01 |   KEY |   KEY |
|  28 |    TABLE ACCESS BY INDEX ROWID                 | EDX_RPT_TARIFF_DIM         |     1 |    17 |     1   (0)| 00:00:01 |       |       |
|* 29 |     INDEX UNIQUE SCAN                          | UK_EDX_RPT_TARIFF_DIM      |     1 |       |     0   (0)| 00:00:01 |       |       |
|  30 |   TABLE ACCESS BY INDEX ROWID                  | EDX_RPT_USAGE_TYPE_DIM     |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|* 31 |    INDEX UNIQUE SCAN                           | UK_EDX_RPT_USAGE_TYPE_DIM  |     1 |       |     0   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("TAB_A"."ACCOUNT_NUM"="EXT_SOURCE_DATA_CU12"."FIELD2" AND
              "TAB_A"."SERVICE_NOT_PRESENT"="EXT_SOURCE_DATA_CU12"."FIELD3")
   6 - filter("TAB_A"."SERVICE_RANK"=1)
   7 - filter(RANK() OVER ( PARTITION BY "$vm_col_8" ORDER BY "$vm_col_7")<=1)
  10 - filter(NVL("SD"."SERVICE_CATEGORY_X",'TEL')='TEL' AND "SD"."END_DATE" IS NULL AND "SD"."SERVICE_TYPE_X"='U')
  13 - access("B"."SERVICE_NUM"="A"."FIELD3")
  14 - filter("A"."FIELD1"='4100')
  16 - filter(NVL("B"."SERVICE_CATEGORY_X",'TEL')='TEL' AND "B"."CUST_GROUP_ID_X"=TO_NUMBER('|| p_cust_grp_id || ') AND
              "B"."SERVICE_TYPE_X"='U')
  18 - filter(NVL("AD"."BILLER_ID",'CSS')='CSS')
  19 - access("AD"."CUST_GROUP_ID_X"=TO_NUMBER('|| p_cust_grp_id || ') AND "FIELD2"="AD"."ACCOUNT_NUM")
  21 - access("SD"."CUST_GROUP_ID_X"=TO_NUMBER('|| p_cust_grp_id || ') AND "AD"."ACCOUNT_KEY"="SD"."ACCOUNT_KEY")
       filter("AD"."CUST_GROUP_ID_X"="SD"."CUST_GROUP_ID_X")
  22 - filter("EXT_SOURCE_DATA_CU12"."FIELD1"='4100' AND UPPER(NVL(TRIM("EXT_SOURCE_DATA_CU12"."FIELD4"),'UNK'))='EIM9060' AND
              TO_NUMBER("EXT_SOURCE_DATA_CU12"."FIELD31")=TO_NUMBER('|| p_cust_grp_id || '))
  24 - filter("EDX_RPT_ACCOUNT_DIM"."END_DT" IS NULL AND "EDX_RPT_ACCOUNT_DIM"."ACCOUNT_NUM"="EXT_SOURCE_DATA_CU12"."FIELD2")
  25 - access("EDX_RPT_ACCOUNT_DIM"."CUST_GROUP_ID_X"=TO_NUMBER('|| p_cust_grp_id || ') AND
              "TAB_A"."ACCOUNT_KEY"="EDX_RPT_ACCOUNT_DIM"."ACCOUNT_KEY")
       filter("EDX_RPT_ACCOUNT_DIM"."CUST_GROUP_ID_X"=TO_NUMBER("EXT_SOURCE_DATA_CU12"."FIELD31"))
  27 - access("EDX_RPT_SERVICE_DIM"."CUST_GROUP_ID_X"=TO_NUMBER('|| p_cust_grp_id || ') AND
              "EDX_RPT_ACCOUNT_DIM"."ACCOUNT_KEY"="EDX_RPT_SERVICE_DIM"."ACCOUNT_KEY" AND "EDX_RPT_SERVICE_DIM"."SERVICE_TYPE_X"='U' AND
              "TAB_A"."SERVICE_KEY"="EDX_RPT_SERVICE_DIM"."SERVICE_KEY")
       filter("EDX_RPT_SERVICE_DIM"."SERVICE_TYPE_X"='U' AND "TAB_A"."SERVICE_KEY"="EDX_RPT_SERVICE_DIM"."SERVICE_KEY" AND
              "EDX_RPT_SERVICE_DIM"."CUST_GROUP_ID_X"=TO_NUMBER("EXT_SOURCE_DATA_CU12"."FIELD31"))
  29 - access("EDX_RPT_TARIFF_DIM"."TARIFF_CD"=NVL(TRIM("EXT_SOURCE_DATA_CU12"."FIELD4"),'UNK'))
  31 - access("EDX_RPT_TARIFF_DIM"."FLEX_FIELD1"="EDX_RPT_USAGE_TYPE_DIM"."USAGE_TYPE_CD")
Re: Query tuning - index suggestion [message #550043 is a reply to message #550041] Thu, 05 April 2012 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
The first thing you should do is fix your query to use bind variables instead concatenating the id in.
Re: Query tuning - index suggestion [message #550049 is a reply to message #550043] Thu, 05 April 2012 09:07 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Thanks for your suggestion. will do it definitely. Any suggestion on the index part?
Re: Query tuning - index suggestion [message #550051 is a reply to message #550049] Thu, 05 April 2012 09:18 Go to previous message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
> Any suggestion on the index part?
Try both ways & compare EXPLAIN PLANS
Previous Topic: Oracle 11g alternates between two execution plans
Next Topic: functional index null values
Goto Forum:
  


Current Time: Mon Apr 21 03:51:44 CDT 2014

Total time taken to generate the page: 0.10558 seconds