Re: Understanding System Statistics - JL
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2004 11:41:52 -0000
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@xxxxxxxxxxx>
: 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
