Home » RDBMS Server » Performance Tuning » Query tuning - index suggestion (Oracle,10g)
| Query tuning - index suggestion [message #550041] |
Thu, 05 April 2012 08:07  |
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") |
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 02:37:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
