Distinct keyword in SELECT statement is taking lot of time around 46 sec , but when removed the query is taking 17 sec .
The query finally returns only 10 records ( whithout or without DISTINCT ).
In the explain plan I can see that the EXECS ( may be no. of execution ) and IO Requests for each table is very high when compared to without DISTINCT keyword.
Sorry , I am not able to post the explain data or tables due to data sensitivity.
Can someone please help me understand the below
1) Why are EXECS and IO Requests very high when using DISTINCT.
2) Distinct is supposed to sort the data fetched in select statement and remove duplicates , here in this case there are only 10 records with no duplicates . But why is DISTINCT taking lot of time ?
3) By direclty reading the explain plan ( without having look at the query ) , Can we guess that the query is using DISTINCT ?
For generating the execution plan , i am using the below:
SELECT dbms_sqltune.report_sql_monitor(sql_id=>'XXXXXXXXXXXX', report_level=>'ALL', TYPE=>'HTML') rpt FROM dual ;
Thanks in advance