Hi,
I am having this query with distinct clause --
This query returns me 10,146 rows and time to execute is almost 135 secs
| SELECT | -- | /*+ first_rows */ |
| distinct | a.sub_id, b.status, |
| | pkg_sp_subbrief.get_sub_typ (a.sub_id) sub_type, |
| | c.svc_provider_nm, fn_sp_get_svc_plan (a.sub_id) svc_plan, |
| | pkg_sp_subbrief.get_sub_contact_parm |
| | (a.sub_id, |
| | 'contact.name.salutation' |
| | ) salutation, |
| | pkg_sp_subbrief.get_sub_contact_parm |
| | (a.sub_id, |
| | 'first_name' |
| | ) first_name, |
| | pkg_sp_subbrief.get_sub_contact_parm |
| | (a.sub_id, |
| | 'contact.name.middle' |
| | ) middle_name, |
| | pkg_sp_subbrief.get_sub_contact_parm |
| | (a.sub_id, |
| | 'last_name' |
| | ) last_name, |
| | pkg_sp_subbrief.get_sub_parm (a.sub_id, 'company_name') company_name, |
| | pkg_sp_subbrief.get_sub_parm (a.sub_id, 'itc_account') itc_accout, |
| | pkg_sp_subbrief.get_sub_contact_parm |
| | (a.sub_id, |
| | 'phones.home.number' |
| | ) phone_number, |
| | pkg_sp_subbrief.get_location_parm |
| | (a.sub_id, |
| | 'address_1' |
| | ) addr_home_address, |
| | pkg_sp_subbrief.get_location_parm |
| | (a.sub_id, |
| | 'city' |
| | ) addr_home_city, |
| | pkg_sp_subbrief.get_location_parm |
| | (a.sub_id, |
| | 'prov' |
| | ) addr_home_prov, |
| | pkg_sp_subbrief.get_location_parm |
| | (a.sub_id, |
| | 'postal_cd' |
| | ) addr_home_postal_code, |
| | pkg_sp_subbrief.get_location_parm |
| | (a.sub_id, |
| | 'country' |
| | ) addr_home_country, |
| | e.val cm_mta_mac, ' ' telephone_number, |
| | pkg_sp_subbrief.get_subsvc_parm_first (a.sub_id, |
| | 3374 |
| | ) alpha_tag, |
| | NVL (j.voipdn1, ' ') voip_dn1, |
| | NVL (j.mtavoiceport, ' ') mta_voice_port |
| | FROM svc_provider c, |
| | ref_status b, |
| | sub a, |
| | sub_svc d, |
| | sub_svc_parm e, |
| | (SELECT f.sub_id, g.val voipdn1, i.val mtavoiceport |
| | FROM sub_svc f, sub_svc_parm g, sub_svc_parm i |
| | WHERE f.svc_id = SvcId('smp_dial_tone_access') |
| | AND f.sub_svc_status_id ! = 29 |
| | AND f.sub_svc_id = g.sub_svc_id |
| | AND g.parm_id = get_cgo_parm_id('voip_dn1', GET_CLASS_ID('SubSvcSpec'), SvcID('smp_dial_tone_access')) |
| | AND f.sub_svc_id = i.sub_svc_id |
| | AND i.parm_id = 20410) j |
| | WHERE a.svc_provider_id = c.svc_provider_id |
| | AND a.sub_status_id = b.status_id |
| | AND a.sub_id = d.sub_id |
| | AND d.sub_svc_id = e.sub_svc_id |
| | AND (e.parm_id = 1254 OR e.parm_id = 20249) |
| | AND d.sub_svc_status_id != 29 |
| | AND a.sub_status_id != 9 |
| | AND a.sub_id = j.sub_id(+); |
This query returns me 10,146 rows and time to execute is almost 135 secs
explain plan for this is -
Execution Plan
----------------------------------------------------------
Plan hash value: 2622307916
--------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 10409 | 1118K| | 2540 (4)| 00:00:31 |
| 1 | HASH UNIQUE | | 10409 | 1118K| 1240K| 2540 (4)| 00:00:31 |
|* 2 | HASH JOIN RIGHT OUTER | | 10409 | 1118K| | 2279 (4)| 00:00:28 |
| 3 | VIEW | | 899 | 23374 | | 1340 (6)| 00:00:17 |
|* 4 | HASH JOIN | | 899 | 53940 | | 1340 (6)| 00:00:17 |
|* 5 | HASH JOIN | | 885 | 35400 | | 918 (8)| 00:00:12 |
|* 6 | TABLE ACCESS BY INDEX ROWID| SUB_SVC | 877 | 17540 | | 434 (0)| 00:00:06 |
|* 7 | INDEX RANGE SCAN | SUB_SVC_IX2 | 951 | | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | SUB_SVC_PARM | 1770 | 35400 | | 483 (14)| 00:00:06 |
|* 9 | TABLE ACCESS FULL | SUB_SVC_PARM | 2632 | 52640 | | 422 (2)| 00:00:06 |
|* 10 | HASH JOIN | | 10409 | 853K| | 939 (2)| 00:00:12 |
|* 11 | TABLE ACCESS FULL | REF_STATUS | 95 | 2280 | | 3 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 10409 | 609K| | 935 (2)| 00:00:12 |
| 13 | VIEW | index$_join$_001 | 49 | 588 | | 3 (34)| 00:00:01 |
|* 14 | HASH JOIN | | | | | | |
| 15 | INDEX FAST FULL SCAN | SVC_PROVIDER_PK | 49 | 588 | | 1 (0)| 00:00:01 |
| 16 | INDEX FAST FULL SCAN | SVC_PROVIDER_UK1 | 49 | 588 | | 1 (0)| 00:00:01 |
|* 17 | HASH JOIN | | 10409 | 487K| | 932 (2)| 00:00:12 |
|* 18 | TABLE ACCESS FULL | SUB | 8777 | 111K| | 53 (0)| 00:00:01 |
|* 19 | HASH JOIN | | 10607 | 362K| | 878 (2)| 00:00:11 |
|* 20 | TABLE ACCESS FULL | SUB_SVC_PARM | 10607 | 207K| | 423 (2)| 00:00:06 |
|* 21 | TABLE ACCESS FULL | SUB_SVC | 90284 | 1322K| | 454 (1)| 00:00:06 |
--------------------------------------------------------------------------------
-----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SUB_ID"="J"."SUB_ID"(+))
4 - access("F"."SUB_SVC_ID"="I"."SUB_SVC_ID")
5 - access("F"."SUB_SVC_ID"="G"."SUB_SVC_ID")
6 - filter("F"."SUB_SVC_STATUS_ID"<>29)
7 - access("F"."SVC_ID"="SVCID"('smp_dial_tone_access'))
8 - filter("G"."PARM_ID"="GET_CGO_PARM_ID"('voip_dn1',"GET_CLASS_ID"('SubSvcS
pec'),"SVCID"('smp_di
al_tone_access')))
9 - filter("I"."PARM_ID"=20410)
10 - access("A"."SUB_STATUS_ID"="B"."STATUS_ID")
11 - filter("B"."STATUS_ID"<>9)
12 - access("A"."SVC_PROVIDER_ID"="C"."SVC_PROVIDER_ID")
14 - access(ROWID=ROWID)
17 - access("A"."SUB_ID"="D"."SUB_ID")
18 - filter("A"."SUB_STATUS_ID"<>9)
19 - access("D"."SUB_SVC_ID"="E"."SUB_SVC_ID")
20 - filter("E"."PARM_ID"=1254 OR "E"."PARM_ID"=20249)
21 - filter("D"."SUB_SVC_STATUS_ID"<>29)
Statistics
----------------------------------------------------------
470461 recursive calls
0 db block gets
13591783 consistent gets
0 physical reads
0 redo size
1272441 bytes sent via SQL*Net to client
7960 bytes received via SQL*Net from client
678 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10146 rows processed
while I remove the distinct clause from the query it execute in 4 secs BUT QUERY RETURNS 10163 rows which means it returns duplicate rows as well.
so i am looking for any alternative to distinct clause to tune this query.
my DB version is --
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production