I need to optimize queries so that I have better performance when searching for the data, it is taking me too long, I attach an example
This is the query I am using ORACLE database
SELECT
SM_ID,
ORIGINAL_ADDRESS,
DESTINATION_ADDRESS,
SUBMISSION_TIME,
FINAL_TIME,
SM_STATUS,
ERROR_CODE,
ORG_ACCOUNT,
DEST_ACCOUNT,
MO_MSC_ADDR,
MT_MSC_ADDR
FROM SMS_DATA_SMC
WHERE TRUNC(SUBMISSION_TIME) = TO_DATE('22/11/2018', 'DD-MM-YYYY')
AND MO_MSC_ADDR is not null
AND MO_MSC_ADDR NOT LIKE '569%' AND FINAL_TIME IS NOT NULL
ORDER BY SM_ID
Test
So it filters first to remove fields with a direct comparison, and then the part that involves function calls is already compared.
You may need to create indexes so that the entire table does not have to be read to filter it.
You will see oracle executes the where operations from the bottom to the top, so any statement that does not involve data conversion will be executed faster, therefore the oracle will not have to do n times, for example, the operation that is now in the date variable is also reduced. from trunc because it previously shrank rows by removing null values
Finally, once the query has been made, you only execute the order to the results of the query instead of ordering all the data and filtering, filtering and ordering
If possible you should create a column with the SUBMISSION_TIME trick and then create an index of this value and finally place it at the end of the where, this will greatly improve your query