it just keeps running for a long time without returning any results.
I am pasting here the execution plan for this query,please let me know which part could tune ,
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 9720 | 182K (1)| 00:42:31 |
| 1 | SORT GROUP BY | | 27 | 9720 | 182K (1)| 00:42:31 |
| 2 | VIEW | | 27 | 9720 | 182K (1)| 00:42:31 |
| 3 | HASH GROUP BY | | 27 | 14283 | 182K (1)| 00:42:31 |
|* 4 | HASH JOIN | | 27 | 14283 | 182K (1)| 00:42:31 |
|* 5 | HASH JOIN | | 26 | 9568 | 166K (1)| 00:38:56 |
| 6 | NESTED LOOPS | | 26 | 9048 | 160K (1)| 00:37:29 |
| 7 | NESTED LOOPS | | 25 | 7950 | 160K (1)| 00:37:28 |
|* 8 | HASH JOIN | | 25 | 7800 | 160K (1)| 00:37:28 |
|* 9 | HASH JOIN | | 25 | 7050 | 134K (1)| 00:31:18 |
|* 10 | HASH JOIN | | 25 | 6600 | 107K (1)| 00:25:08 |
|* 11 | TABLE ACCESS FULL | WC_WRK_SPEC_F | 2288 | 84656 | 4405 (2)| 00:01:02 |
| 12 | NESTED LOOPS | | 16275 | 3607K| 103K (1)| 00:24:06 |
| 13 | NESTED LOOPS | | 592 | 121K| 102K (1)| 00:23:49 |
|* 14 | HASH JOIN | | 592 | 47952 | 99684 (1)| 00:23:16 |
|* 15 | TABLE ACCESS BY INDEX ROWID | W_PERSON_D | 14057 | 700K| 90700 (1)| 00:21:10 |
| 16 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 17 | BITMAP AND | | | | | |
| 18 | BITMAP OR | | | | | |
|* 19 | BITMAP INDEX SINGLE VALUE| IXDIM_W_PERSON_D_F27 | | | | |
|* 20 | BITMAP INDEX SINGLE VALUE| IXDIM_W_PERSON_D_F27 | | | | |
|* 21 | BITMAP INDEX SINGLE VALUE| IXDIM_W_PERSON_D_F27 | | | | |
|* 22 | BITMAP INDEX SINGLE VALUE| IXDIM_W_PERSON_D_F27 | | | | |
|* 23 | BITMAP INDEX SINGLE VALUE| IXDIM_W_PERSON_D_F27 | | | | |
|* 24 | BITMAP INDEX SINGLE VALUE | W_PERSON_D_M14 | | | | |
| 25 | NESTED LOOPS | | 62915 | 1843K| 8983 (1)| 00:02:06 |
|* 26 | TABLE ACCESS FULL | WC_WRK_SPEC_F | 2288 | 57200 | 4405 (2)| 00:01:02 |
| 27 | BITMAP CONVERSION TO ROWIDS | | 27 | 135 | 8983 (1)| 00:02:06 |
|* 28 | BITMAP INDEX SINGLE VALUE | W_PER_RANK_F_F7 | | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | W_PERSON_D | 1 | 129 | 4 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | W_PERSON_D_U1 | 1 | | 2 (0)| 00:00:01 |
| 31 | BITMAP CONVERSION TO ROWIDS | | 27 | 459 | 103K (1)| 00:24:06 |
|* 32 | BITMAP INDEX SINGLE VALUE | W_PER_RANK_F_F7 | | | | |
|* 33 | TABLE ACCESS FULL | WC_CUST_INST_F | 33386 | 586K| 26431 (2)| 00:06:11 |
|* 34 | TABLE ACCESS FULL | WC_CUST_INST_F | 33386 | 978K| 26431 (2)| 00:06:11 |
|* 35 | INDEX UNIQUE SCAN | W_PERSON_D_P1 | 1 | 6 | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | W_PERSON_D | 1 | 30 | 2 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | W_PERSON_D_P1 | 1 | | 1 (0)| 00:00:01 |
|* 38 | VIEW | index$_join$_003 | 287K| 5616K| 6215 (3)| 00:01:28 |
|* 39 | HASH JOIN | | | | | |
|* 40 | HASH JOIN | | | | | |
| 41 | BITMAP CONVERSION TO ROWIDS | | 287K| 5616K| 47 (0)| 00:00:01 |
|* 42 | BITMAP INDEX SINGLE VALUE | IXDIM_WC_CUST_ADDR_D_F07 | | | | |
| 43 | BITMAP CONVERSION TO ROWIDS | | 287K| 5616K| 85 (0)| 00:00:02 |
|* 44 | BITMAP INDEX SINGLE VALUE | IXDIM_WC_CUST_ADDR_D_F04 | | | | |
| 45 | BITMAP CONVERSION TO ROWIDS | | 287K| 5616K| 5895 (1)| 00:01:23 |
| 46 | BITMAP INDEX FULL SCAN | IXDIM_WC_CUST_ADDR_D_F02 | | | | |
| 47 | TABLE ACCESS BY INDEX ROWID | WC_CUST_ADDR_D | 152K| 23M| 15400 (1)| 00:03:36 |
| 48 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 49 | BITMAP AND | | | | | |
|* 50 | BITMAP INDEX SINGLE VALUE | IXDIM_WC_CUST_ADDR_D_F07 | | | | |
|* 51 | BITMAP INDEX SINGLE VALUE | IXDIM_WC_CUST_ADDR_D_F06 | | | | |
|* 52 | BITMAP INDEX SINGLE VALUE | IXDIM_WC_CUST_ADDR_D_F05 | | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T489839"."ROW_WID"="T555160"."CONTACT_WID")
5 - access("T489839"."ROW_WID"="T555160"."CONTACT_WID")
8 - access("T489839"."ROW_WID"="T565669"."CONTACT_WID")
9 - access("T489839"."ROW_WID"="T565669"."CONTACT_WID")
10 - access("T445248"."CONTACT_WID"="T565813"."CONTACT_WID")
11 - filter("T565813"."WRK_SPECIALITY"='01 Allm??Allm??edicin')
14 - access("T445248"."CONTACT_WID"="T489839"."ROW_WID")
15 - filter("T489839"."X_BU_NAME"='NNEU Sweden' AND ("T489839"."VIS_PR_BU_ID"='1-A30BR' OR
"T489839"."VIS_PR_BU_ID"='1-A37AL' OR "T489839"."VIS_PR_BU_ID"='1-B67SM' OR "T489839"."VIS_PR_BU_ID"='1-DEN82'
OR "T489839"."VIS_PR_BU_ID"='Unspecified') AND ("T489839"."X_CUST_STAT_CD"='Active' OR
"T489839"."X_CUST_STAT_CD"='New' OR "T489839"."X_CUST_STAT_CD"='Pending Validation' OR
"T489839"."X_CUST_STAT_CD"='Unspecified'))
19 - access("T489839"."VIS_PR_BU_ID"='1-A30BR')
20 - access("T489839"."VIS_PR_BU_ID"='1-A37AL')
21 - access("T489839"."VIS_PR_BU_ID"='1-B67SM')
22 - access("T489839"."VIS_PR_BU_ID"='1-DEN82')
23 - access("T489839"."VIS_PR_BU_ID"='Unspecified')
24 - access("T489839"."EMP_FLG"='N')
26 - filter("T565813"."WRK_SPECIALITY"='01 Allm??Allm??edicin')
28 - access("T445248"."CONTACT_WID"="T565813"."CONTACT_WID")
29 - filter("T489839"."X_BU_NAME"='NNEU Sweden' AND ("T489839"."VIS_PR_BU_ID"='1-A30BR' OR
"T489839"."VIS_PR_BU_ID"='1-A37AL' OR "T489839"."VIS_PR_BU_ID"='1-B67SM' OR "T489839"."VIS_PR_BU_ID"='1-DEN82'
OR "T489839"."VIS_PR_BU_ID"='Unspecified') AND "T489839"."EMP_FLG"='N')
30 - access("T489839"."INTEGRATION_ID"="T489839"."INTEGRATION_ID")
32 - access("T445248"."CONTACT_WID"="T489839"."ROW_WID")
33 - filter("T565669"."X_REL_TYPE_CD"='Ass Spe' OR "T565669"."X_REL_TYPE_CD"='AssDist Health C' OR
"T565669"."X_REL_TYPE_CD"='Dis Health C Ctr' OR "T565669"."X_REL_TYPE_CD"='DisHealth C Ctr' OR
"T565669"."X_REL_TYPE_CD"='DistHealth C' OR "T565669"."X_REL_TYPE_CD"='Distr Healt Ch' OR
"T565669"."X_REL_TYPE_CD"='District Health' OR "T565669"."X_REL_TYPE_CD"='Graduate physician' OR
"T565669"."X_REL_TYPE_CD"='Home Health Care Physician' OR "T565669"."X_REL_TYPE_CD"='Spe RecSenior' OR
"T565669"."X_REL_TYPE_CD"='SpeRecSen' OR "T565669"."X_REL_TYPE_CD"='SpeRecSpec' OR
"T565669"."X_REL_TYPE_CD"='SpeRecep')
34 - filter("T565669"."X_REL_TYPE_CD"='Ass Spe' OR "T565669"."X_REL_TYPE_CD"='AssDist Health C' OR
"T565669"."X_REL_TYPE_CD"='Dis Health C Ctr' OR "T565669"."X_REL_TYPE_CD"='DisHealth C Ctr' OR
"T565669"."X_REL_TYPE_CD"='DistHealth C' OR "T565669"."X_REL_TYPE_CD"='Distr Healt Ch' OR
"T565669"."X_REL_TYPE_CD"='District Health' OR "T565669"."X_REL_TYPE_CD"='Graduate physician' OR
"T565669"."X_REL_TYPE_CD"='Home Health Care Physician' OR "T565669"."X_REL_TYPE_CD"='Spe RecSenior' OR
"T565669"."X_REL_TYPE_CD"='SpeRecSen' OR "T565669"."X_REL_TYPE_CD"='SpeRecSpec' OR
"T565669"."X_REL_TYPE_CD"='SpeRecep')
35 - access("T36044"."ROW_WID"="T445248"."CONTACT_WID")
37 - access("T36044"."ROW_WID"="T445248"."CONTACT_WID")
38 - filter("T555160"."COUNTRY"='Sweden' AND "T555160"."NN_PRIMARY"='Y')
39 - access(ROWID=ROWID)
40 - access(ROWID=ROWID)
42 - access("T555160"."COUNTRY"='Sweden')
44 - access("T555160"."NN_PRIMARY"='Y')
50 - access("T555160"."COUNTRY"='Sweden')
51 - access("T555160"."MAIN_ADDR_FLG"='Y')
52 - access("T555160"."ACTIVE_FLG"='Y')