I've been reading Jonathan Lewis' article on TechNet ( http://www.oracle.com/technology/pub/articles/lewis_cbo.html ) with the above name. One part of the example at the end of the article confuses me and I was wondering if anyone could help explain what is happening.
JL creates a table as
create table t1 as
select
trunc(sysdate-1) + rownum/1440 d1,
rownum n1,
rpad('x',100) padding
from
all_objects
where
rownum <= 3000
;
alter table t1
add constraint t1_pk primary key (d1,n1)
using index (create index t1_pk on t1(d1,n1))
;
execute dbms_stats.gather_table_stats(user,'t1',cascade=>true)
Two queries are then executed:
select /*+ index(t1) */
padding
from t1
where n1 = 2800
and d1 >= trunc(sysdate)
;
select /*+ index(t1) */
padding
from t1
where d1 >= trunc(sysdate)
and n1 = 2800
;Jonathan then goes on to state "The difference in run time depends on the fact that I engineered the data set and the query to force Oracle to check both columns (d1 and n1) for every index entry covered by the index range scan, and the specific predicate values required Oracle to check 1,560 rows. Of those 1,560 rows, every single one will pass the date test, but only one will pass the numeric test. So, by switching the order of the predicates manually, I was actually choosing between executing: 1,560 date tests that pass, followed by 1,560 numeric tests and 1,560 numeric tests of which one passes, and one subsequent date test."I understand that the range scan d1 >= trunc(sysdate) will be true for 1560 rows. Since this is the leading column in the index, there are 1560 date tests that pass.These rows then go through the numeric tests (1560 numeric tests, also using the index), of which one passes. What I don't understand is how the 1560 numerictests can come first, follow
ed by on date test.This is an interesting article and the timing tests show there is a definite difference between these two queries. I just don't follow the explanation. Any clarification would help me rest better at night.Thanks.Henry
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 29 2004 - 11:06:01 CDT