RE: Statistics madness

From: Barr, Stephen <Stephen.Barr_at_BSkyB.com>
Date: Wed, 11 May 2005 14:17:33 +0100
Message-ID: <4127F81F6CAFC245A18BC49054EFB0630193374F@ssslexchusr6.sssl.bskyb.com>

You're right - doesn't seem to do this in 10.1.0.2 -

Uniform spread of dates from 2nd Mar 2004 to 24th Mar 2005.

Within the bounds...

  1 explain plan for
  2 select * from stin
  3 where last_modified_dt >= to_date('02-Mar-2005 00:00:00','DD-MON-YYYY HH24:MI:SS')
  4* and last_modified_dt <= to_date('02-Mar-2005 23:59:59','DD-MON-YYYY HH24:MI:SS')
PERF_TEST@>/ Explained.

Elapsed: 00:00:00.03
PERF_TEST@>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1086516732

| 0 | SELECT STATEMENT | | 2901K| 276M| 141K (5)| 00:33:05 | |* 1 | TABLE ACCESS FULL| STIN | 2901K| 276M| 141K (5)| 00:33:05 |

Predicate Information (identified by operation id):

1 - filter("LAST_MODIFIED_DT"<=TO_DATE('2005-03-02 23:59:59',

'yyyy-mm-dd hh24:mi:ss') AND "LAST_MODIFIED_DT">=TO_DATE('2005-03-02
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

15 rows selected.

Just outside the high date -

  1 explain plan for
  2 select * from stin
  3 where last_modified_dt >= to_date('25-Mar-2005 00:00:00','DD-MON-YYYY HH24:MI:SS')
  4* and last_modified_dt <= to_date('25-Mar-2005 23:59:59','DD-MON-YYYY HH24:MI:SS')
PERF_TEST@>/ Explained.

Elapsed: 00:00:00.02
PERF_TEST@>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1086516732

| 0 | SELECT STATEMENT | | 1418K| 135M| 141K (4)| 00:32:56 | |* 1 | TABLE ACCESS FULL| STIN | 1418K| 135M| 141K (4)| 00:32:56 |

Predicate Information (identified by operation id):

1 - filter("LAST_MODIFIED_DT">=TO_DATE('2005-03-25 00:00:00',

'yyyy-mm-dd hh24:mi:ss') AND "LAST_MODIFIED_DT"<=TO_DATE('2005-03-25

23:59:59', 'yyyy-mm-dd hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.06

...it has defaulted to the density...

And well outside the high date...

  1 explain plan for
  2 select * from stin
  3 where last_modified_dt >= to_date('24-Mar-9999 00:00:00','DD-MON-YYYY HH24:MI:SS')
  4* and last_modified_dt <= to_date('24-Mar-9999 23:59:59','DD-MON-YYYY HH24:MI:SS')
PERF_TEST@>/ Explained.

Elapsed: 00:00:00.03
PERF_TEST@>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1086516732

| 0 | SELECT STATEMENT | | 1418K| 135M| 141K (4)| 00:32:56 | |* 1 | TABLE ACCESS FULL| STIN | 1418K| 135M| 141K (4)| 00:32:56 |

Predicate Information (identified by operation id):

1 - filter("LAST_MODIFIED_DT">=TO_DATE('9999-03-24 00:00:00',

'yyyy-mm-dd hh24:mi:ss') AND "LAST_MODIFIED_DT"<=TO_DATE('9999-03-24

23:59:59', 'yyyy-mm-dd hh24:mi:ss'))

15 rows selected.

...again, defaulted to the density.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: 06 May 2005 17:58
To: oracle-l_at_freelists.org
Subject: Re: Statistics madness

Correct,

I have n1 with values from 0 to 9
100 rows of each.

select where n1 = 10        card = 89
select where n1 = 11        card = 78
select where n1 = 12        card = 67

select where n1 = 18        card = 1

10.1.0.4 - I don't think it did this on 10.1.0.2

I demo'd Oracle 11! with attitude on Tuesday Looks like we have 10g with existential angst

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 Public Appearances - schedule updated April 5th 2005

Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Friday, May 06, 2005 5:46 PM Subject: Re: Statistics madness

Let me see if I get this right. The optimizer now is saying "according to all I know, what you are asking for doesn't exist. But now let me figure out a degree of 'does not exist'"

Jonathan Lewis wrote:

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



-----------------------------------------

Information in this email may be privileged, confidential and is intended
exclusively for the addressee. The views expressed may not be official
policy, but the personal views of the originator. If you have received it
in error, please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit, use or
disclose its contents to anyone.     Please note we reserve the right to
monitor all e-mail communication through our internal and external
networks.

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

Received on Wed May 11 2005 - 09:22:35 CDT