Hi,
I am trying to generate XML by joining two of my tables and the following is used in my where clause
where (((o.type = 302
**and**
**( o.type\_pk in (select cr.asset\_id from TABLE(V\_REP\_ARRAY\_UPD) CR)**
**or**
**o.type\_pk in (select ct.asset\_id from TABLE(V\_TASK\_ARRAY\_UPD) CT)))**
**or**
**(o.type = 105**
**and**
**(o.type\_pk in (select ct.task\_id from TABLE(V\_TASK\_ARRAY\_UPD) CT)))**
**) or**
**(((o.type = 302**
**and**
**( o.type\_pk in (select cr.asset\_id from TABLE(V\_REP\_ARRAY) CR)**
**or**
**o.type\_pk in (select ct.asset\_id from TABLE(V\_TASK\_ARRAY) CT)))**
**or**
**(o.type = 105**
**and**
**(o.type\_pk in (select ct.task\_id from TABLE(V\_TASK\_ARRAY) CT)))**
**)**
**and (o.created\_on > V\_UPDATED\_ON or o.updated\_on > V\_UPDATED\_ON)))**
**and o.enabled=1 and u.user\_id=o.user\_id;**
Due to o.created_on > V_UPDATED_ON or o.updated_on > V_UPDATED_ON, it is taking 23 seconds for just getting 26 records from tables. I have two tables named as Owners which has 170000 records and other users table which has more than 700000 records. I have tried creating an index on created_on and updated_on but it doesn't improve any performance. Even I have an index on type and type_pk. Can someone please suggest what can be wrong and how to improve the performance of my query.
Update
I have added my owner's table structure and indexes and also added explain plan for better understanding
Table
Owner table indexes
Explain plan from SQL Developer
Sample query with hardcoded values
select xmlelement("Owners", xmlagg(xmlelement("Owner", xmlforest(
o.owner\_id "owner\_id",
o.type "type",
o.type\_pk "type\_pk",
itv\_util.getFullName(u.user\_title,u.user\_first\_name,u.user\_last\_name) "owner\_name",
u.email "email",
u.user\_mobile\_number "mobile",
(select r.role from itv\_owner\_role r where r.role\_id=o.role\_id) "role",
o.description "description",
lower(o.created\_by) "created\_by",
to\_char(o.created\_on, 'DD-MM-YYYY HH24:MI:SS TZH:TZM') "created\_on",
lower(o.updated\_by) "updated\_by",
to\_char(o.updated\_on, 'DD-MM-YYYY HH24:MI:SS TZH:TZM') "updated\_on",
nvl(u.photo\_thumb\_url,'[https://iweb.itouchvision.com/i/MCS/img/no-profile-image.jpg](https://iweb.itouchvision.com/i/MCS/img/no-profile-image.jpg)') "photo",
o.delete\_flag "delete\_flag",
(case when o.contact\_type=1 then 'Person' when o.contact\_type=2 then 'Organisation' end) "contact\_type",
o.organisation\_id "organisation\_id",
(select org.organisation\_name from itv\_organisations org where org.organisation\_id=o.organisation\_id) "organisation\_name"
))))as owner\_xml
from itv\_owners o, itv\_user\_data u
where o.enabled=1 and u.user\_id=o.user\_id
AND
(((o.type = 302
and
( o.type\_pk in (NULL)
or
o.type\_pk in (NULL)))
or
(o.type = 105
and
(o.type\_pk in (833959, 833052, 831807, 833046, 827474, 921343, 1270135, 1269204, 74493, 74620, 75905, 76055, 75911, 75919, 75949, 74454, 74809, 74812, 76133, 74316, 74320, 74321, 74322, 76328, 74257, 74328, 74330, 74462, 74513, 74388, 74389, 74579, 74861, 74864, 74901, 74908, 74912, 74899, 75149, 74599, 74600, 74922, 76529, 74602, 75088, 75037, 74961, 75119, 76930, 76958, 75072, 75174, 76946, 75644, 75650, 75598, 75605, 77055, 75831, 75613, 74485, 75660, 77240, 75709, 74842, 75973, 75983, 74406, 74428, 75800, 75999, 74671, 74674, 74675, 74698, 76037, 75296, 83196, 81530, 81531, 82141, 82253, 83115, 81282, 83201, 83295, 83372, 83399, 80853, 80864, 83403, 80878, 83443, 83466, 81735, 83530, 81443, 84876, 84898, 84962, 85142, 84986, 85139, 85146, 85804, 85278, 84046, 85320, 85619, 85668, 85749, 85957, 83960, 85826, 90373, 109797, 706627, 706628, 706629, 706630, 706631, 706632, 702873, 704426, 818785, 812194, 896950, 896951, 893425, 896043, 898685, 898686, 900843, 899911, 898645, 895900, 900824, 900837, 896947, 80402, 78788, 78977, 78987, 78995, 78996, 78997, 78998, 79003, 79004, 78297, 78301, 78240, 77353, 79665, 77498, 78014, 77366, 77651, 78583, 78666, 81948, 82101, 82256, 80790, 80745, 81060, 83090, 82735, 81151, 86874, 87190, 87132, 87230, 87603, 87326, 89063, 87765, 88159, 87624, 87372, 87696, 87999, 88001, 88613, 113113, 117006, 128776, 128777, 128782, 128778, 128771, 699017, 778499, 784979, 884847, 886952, 886954, 869625, 74684, 74689, 74693, 74694, 76109, 74623, 74774, 74940, 76274, 74667, 74819, 74400, 74511, 74553, 75434, 76726, 77135, 75472, 75489, 75491, 74382, 75577, 74469, 75700, 76793, 76795, 76798, 76801, 76805, 75666, 75726, 75727, 75728, 74494, 78318, 78323, 78604, 78608, 78769, 80369, 78877, 80381, 78766, 79010, 79011, 79340, 77663, 77460, 79632, 78047, 79835, 80014, 78116, 80114, 86082, 84458, 86252, 84816, 86462, 86474, 86002, 85180, 86009, 86361, 84057, 86608, 86618, 85949, 84618, 83781, 84518, 85387, 86770, 88695, 86901, 87331, 87382, 87384, 87387, 87010, 87020, 87022, 86932, 109996, 112759, 113112, 749944, 865304, 846399, 865303, 865301, 1314022)))
) or
(((o.type = 302
and
( o.type\_pk in (NULL)
or
o.type\_pk in (833959, 833052, 831807, 833046, 827474, 921343, 1270135, 1269204, 74493, 74620, 75905, 76055, 75911, 75919, 75949, 74454, 74809, 74812, 76133, 74316, 74320, 74321, 74322, 76328, 74257, 74328, 74330, 74462, 74513, 74388, 74389, 74579, 74861, 74864, 74901, 74908, 74912, 74899, 75149, 74599, 74600, 74922, 76529, 74602, 75088, 75037, 74961, 75119, 76930, 76958, 75072, 75174, 76946, 75644, 75650, 75598, 75605, 77055, 75831, 75613, 74485, 75660, 77240, 75709, 74842, 75973, 75983, 74406, 74428, 75800, 75999, 74671, 74674, 74675, 74698, 76037, 75296, 83196, 81530, 81531, 82141, 82253, 83115, 81282, 83201, 83295, 83372, 83399, 80853, 80864, 83403, 80878, 83443, 83466, 81735, 83530, 81443, 84876, 84898, 84962, 85142, 84986, 85139, 85146, 85804, 85278, 84046, 85320, 85619, 85668, 85749, 85957, 83960, 85826, 90373, 109797, 706627, 706628, 706629, 706630, 706631, 706632, 702873, 704426, 818785, 812194, 896950, 896951, 893425, 896043, 898685, 898686, 900843, 899911, 898645, 895900, 900824, 900837, 896947, 80402, 78788, 78977, 78987, 78995, 78996, 78997, 78998, 79003, 79004, 78297, 78301, 78240, 77353, 79665, 77498, 78014, 77366, 77651, 78583, 78666, 81948, 82101, 82256, 80790, 80745, 81060, 83090, 82735, 81151, 86874, 87190, 87132, 87230, 87603, 87326, 89063, 87765, 88159, 87624, 87372, 87696, 87999, 88001, 88613, 113113, 117006, 128776, 128777, 128782, 128778, 128771, 699017, 778499, 784979, 884847, 886952, 886954, 869625, 74684, 74689, 74693, 74694, 76109, 74623, 74774, 74940, 76274, 74667, 74819, 74400, 74511, 74553, 75434, 76726, 77135, 75472, 75489, 75491, 74382, 75577, 74469, 75700, 76793, 76795, 76798, 76801, 76805, 75666, 75726, 75727, 75728, 74494, 78318, 78323, 78604, 78608, 78769, 80369, 78877, 80381, 78766, 79010, 79011, 79340, 77663, 77460, 79632, 78047, 79835, 80014, 78116, 80114, 86082, 84458, 86252, 84816, 86462, 86474, 86002, 85180, 86009, 86361, 84057, 86608, 86618, 85949, 84618, 83781, 84518, 85387, 86770, 88695, 86901, 87331, 87382, 87384, 87387, 87010, 87020, 87022, 86932, 109996, 112759, 113112, 749944, 865304, 846399, 865303, 865301, 1314022))
or
(o.type = 105
and
(o.type\_pk in (833959, 833052, 831807, 833046, 827474, 921343, 1270135, 1269204, 74493, 74620, 75905, 76055, 75911, 75919, 75949, 74454, 74809, 74812, 76133, 74316, 74320, 74321, 74322, 76328, 74257, 74328, 74330, 74462, 74513, 74388, 74389, 74579, 74861, 74864, 74901, 74908, 74912, 74899, 75149, 74599, 74600, 74922, 76529, 74602, 75088, 75037, 74961, 75119, 76930, 76958, 75072, 75174, 76946, 75644, 75650, 75598, 75605, 77055, 75831, 75613, 74485, 75660, 77240, 75709, 74842, 75973, 75983, 74406, 74428, 75800, 75999, 74671, 74674, 74675, 74698, 76037, 75296, 83196, 81530, 81531, 82141, 82253, 83115, 81282, 83201, 83295, 83372, 83399, 80853, 80864, 83403, 80878, 83443, 83466, 81735, 83530, 81443, 84876, 84898, 84962, 85142, 84986, 85139, 85146, 85804, 85278, 84046, 85320, 85619, 85668, 85749, 85957, 83960, 85826, 90373, 109797, 706627, 706628, 706629, 706630, 706631, 706632, 702873, 704426, 818785, 812194, 896950, 896951, 893425, 896043, 898685, 898686, 900843, 899911, 898645, 895900, 900824, 900837, 896947, 80402, 78788, 78977, 78987, 78995, 78996, 78997, 78998, 79003, 79004, 78297, 78301, 78240, 77353, 79665, 77498, 78014, 77366, 77651, 78583, 78666, 81948, 82101, 82256, 80790, 80745, 81060, 83090, 82735, 81151, 86874, 87190, 87132, 87230, 87603, 87326, 89063, 87765, 88159, 87624, 87372, 87696, 87999, 88001, 88613, 113113, 117006, 128776, 128777, 128782, 128778, 128771, 699017, 778499, 784979, 884847, 886952, 886954, 869625, 74684, 74689, 74693, 74694, 76109, 74623, 74774, 74940, 76274, 74667, 74819, 74400, 74511, 74553, 75434, 76726, 77135, 75472, 75489, 75491, 74382, 75577, 74469, 75700, 76793, 76795, 76798, 76801, 76805, 75666, 75726, 75727, 75728, 74494, 78318, 78323, 78604, 78608, 78769, 80369, 78877, 80381, 78766, 79010, 79011, 79340, 77663, 77460, 79632, 78047, 79835, 80014, 78116, 80114, 86082, 84458, 86252, 84816, 86462, 86474, 86002, 85180, 86009, 86361, 84057, 86608, 86618, 85949, 84618, 83781, 84518, 85387, 86770, 88695, 86901, 87331, 87382, 87384, 87387, 87010, 87020, 87022, 86932, 109996, 112759, 113112, 749944, 865304, 846399, 865303, 865301, 1314022)))
)
and (o.created\_on > :V\_UPDATED\_ON or o.updated\_on > :V\_UPDATED\_ON))));
Message was edited by: Kinjan Bhavsar Added all details as requested by other community members.