I am going to write the following query
select answer, answer_id from surveys s join answers a on s.survey_id = a.survey_seq_id
where question_uid = 206400374 and insertdate = to_date('31/12/2012') and answer > 0;
However, when I look at the execution plan, I see that the last predicate (to_number(answer) > 0) has been executed the first. Henceforth, it checks many rows first. Normally, 75 rows belong to 31/12/2012 as you see from the following. Can I specify the execution order?
select count(*) from surveys s join answers a on s.survey_id = a.survey_seq_id
where question_uid = 206400374 and insertdate = to_date('31/12/2012');
COUNT(*)
--------------
75
If so, how? Because, the type of answer is varchar2 and some of answers contain text characters such as 'Yes' or 'No'. Therefore, before 31/12/2012 some answers contain 'Yes' or 'No'. However in 31/12/2012 all answers are numeric.
Plan hash value: 3217836037
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 68 | 9401 (1)| 00:01:53 |
|* 1 | HASH JOIN | | 2 | 68 | 9401 (1)| 00:01:53 |
|* 2 | TABLE ACCESS FULL| SURVEYS | 2 | 26 | 239 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| ANSWERS | 337 | 7077 | 9162 (1)| 00:01:50 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."SURVEY_ID"="A"."SURVEY_SEQ_ID")
2 - filter("S"."INSERTDATE"=TO_DATE(' 2012-12-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
3 - filter("A"."QUESTION_UID"=206400374 AND
TO_NUMBER("A"."ANSWER")>0)
select distinct answer from surveys s join answers a on s.survey_id = a.survey_seq_id
where question_uid = 206400374 and insertdate = to_date('31/12/2012');
ANSWER
------------
1
3
0
2
And, also I can execute the following query without any error
select to_number(answer) from surveys s join answers a on s.survey_id = a.survey_seq_id
where question_uid = 206400374 and insertdate = to_date('31/12/2012');