Re: Understanding System Statistics - JL

Henry,

I'm just catching up on email after two consecutive conferences, and found this in the digest

I think your question arises from the fact that Oracle clearly seems to be doing redundant work. After all, since the index starts with D1, every row accessed after the initial index probe that gets to the first "d1 >= sysdate" will obviously still match the condition - so why does Oracle bother to check the condition on every single row thereafter.

The answer is: it just does.

The process seems to be:

    Use some predicates to identify a starting     entry in the index. Use some predicates     to identify a stopping entry in the index.     Use some predicates to test every single     entry between the start-key and the stop-key.

In this example, Oracle redundantly checks D1 on every single entry - hence the possibility that swapping the order of tests will affect the amount of work done.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated Sept 19th

: From: "Henry Poras" <hporas_at_comcast.net>
: Subject: Understanding System Statistics - JL
: Date: Fri, 29 Oct 2004 12:09:57 -0400
:
: 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.
:
: 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 numeric tests can come first,
followed by one 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.

--
http://www.freelists.org/webpage/oracle-l