Improve SQL query
Hi Experts,
Database-11.2.0.3. Any suggestions to improve this query.
select distinct(ap.applicant_id),a.firstname,a.surname,ap.sender,nvl( ap.offline_app_flag, 'N') as offline_app_flag from application ap inner join applicant a on ap.applicant_id = a.applicant_id where ap.agency_id = :agency_id and (lower(ap.appformq_surname) like :surname or lower(a.surname) like :surname) order by a.firstname,a.surname,ap.sender,offline_app_flag;
Plan
SELECT STATEMENT ALL_ROWS Cost: 14,793 Bytes: 52,206 Cardinality: 791
5 SORT UNIQUE Cost: 14,792 Bytes: 52,206 Cardinality: 791
4 HASH JOIN Cost: 14,790 Bytes: 52,206 Cardinality: 791
2 TABLE ACCESS BY INDEX ROWID TABLE NHS.APPLICATION Cost: 7,954 Bytes: 400,320 Cardinality: 8,340
Database-11.2.0.3. Any suggestions to improve this query.
select distinct(ap.applicant_id),a.firstname,a.surname,ap.sender,nvl( ap.offline_app_flag, 'N') as offline_app_flag from application ap inner join applicant a on ap.applicant_id = a.applicant_id where ap.agency_id = :agency_id and (lower(ap.appformq_surname) like :surname or lower(a.surname) like :surname) order by a.firstname,a.surname,ap.sender,offline_app_flag;
Plan
SELECT STATEMENT ALL_ROWS Cost: 14,793 Bytes: 52,206 Cardinality: 791
5 SORT UNIQUE Cost: 14,792 Bytes: 52,206 Cardinality: 791
4 HASH JOIN Cost: 14,790 Bytes: 52,206 Cardinality: 791
2 TABLE ACCESS BY INDEX ROWID TABLE NHS.APPLICATION Cost: 7,954 Bytes: 400,320 Cardinality: 8,340
0