Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Understanding System Statistics - JL

Understanding System Statistics - JL

From: Henry Poras <hporas_at_comcast.net>
Date: Fri, 29 Oct 2004 12:09:57 -0400
Message-ID: <018d01c4bdd1$bc845460$6600a8c0@GROUCHO>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US