Oracle10g, accidental discovery, after the restriction on the same field twice in the where cost is lower, perform faster, such as a query
Where in_date & gt;=sysdate - 300,
Cost more than 1000, if use two
Where in_date & gt;=sysdate - 300
And in_date & gt;=sysdate - 310
It cost only a few hundred, query time is less than the above, this is what reason ah (ps, this field has join index)

CodePudding user response:

Look at the execution plan

CodePudding user response:

There is only one in_date & gt; Don't go=sysdate - 300, the index, a full table scan, add a and in_date & gt;=go after sysdate - 310 index

CodePudding user response:

Just write in_date & gt;=sysdate - 310, to see what execution plan,

CodePudding user response:

Look at the in_date & gt;=sysdate - 300 and in_date & gt;=sysdate - 310 record of how much is the percent of total record number respectively?

refer to the second floor lookups_me response:

only one in_date & gt; Don't go=sysdate - 300, the index, a full table scan, add a and in_date & gt;=go after sysdate - 310 index

CodePudding user response:

And after the intersection in_date & gt;=sysdate - 300
And in_date & gt;=sysdate - 310 is in_date & gt;=sysdate - 310, don't walk index may be in_date & gt;=sysdate - 300 more than 20% of the total amount of or 25 (remember),

CodePudding user response:

Agree with the upstairs, accounting for more than a certain amount of time, ORACLE will choose a full table scan, because is more efficient

CodePudding user response:

reference learning turn _ 5 floor response:

and after the intersection in_date & gt;=sysdate - 300
And in_date & gt;=sysdate - 310 is in_date & gt;=sysdate - 310, don't walk index may be in_date & gt;=sysdate - 300 more than 20% of the total amount of or 25 (remember),

Are separate in_date & gt;=sysdate - 300, does not walk the index, and go after two conditions index
After just and intersection should still in_date & gt;=sysdate - 300, and an original condition is the actual filter is the same

CodePudding user response:

reference 4 floor yaiger response:

see in_date & gt;=sysdate - 300 and in_date & gt;=sysdate - 310 record of how much is the percent of total record number respectively?

Accounted for about the same, more than 300, with sysdate - 400 with sysdate - 600400 without index, 600 - plus two conditions index

CodePudding user response:

Look again after take a table analysis

CodePudding user response:

references 9 f yaiger response:

do table analysis after see

Analyzed, or a date condition index don't go, go to add another date index