| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to analyze a odd column distribution
sorry -- the timestamp literal needs a time component too -- see correction below...
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Lex de Haan
Sent: Friday, January 21, 2005 19:25
To: jonathan_at_jlcomp.demon.co.uk; aleon68_at_gmail.com; 'Christian Antognini'
Cc: oracle-l_at_freelists.org
Subject: RE: How to analyze a odd column distribution
just to add to what Jonathan suspects, I think it is better to specify real
datetime literals in cases like this anyway. that is:
replace "FHASTA = TO_TIMESTAMP('31-DEC-2999','DD-MON-YYYY')"
with "FHASTA = TIMESTAMP '2999-12-31 00:00:00'"
it reads much better, and Oracle for sure will interpret this as a literal,
not a function call.
hope this helps,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Friday, January 21, 2005 17:19
To: aleon68_at_gmail.com; Christian Antognini
Cc: oracle-l_at_freelists.org
Subject: Re: How to analyze a odd column distribution
If you check the 'wrong' plan, it is doing a KEY-KEY partition start/stop. This means Oracle can see that a single partition will be visited, but cannot identify which one, at parse time. Consequently it is probably using the global level density to estimate the row count. I suspect you have a problem with the to_timestamp() call, that is making Oracle treat the result as 'unknown' rather than 'constant'.
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 Dec 23rd 2004
This is an example
first there is the statistics and histograms and at the end the explain plan for the queries, as you can see if I specify the partitions then I get the real number, but that is not the case when I don't specify the partitions.
SQL> select num_distinct,num_buckets,sample_size from
USER_tab_col_statistics
2 where table_name='TCUENTA'
3 and column_name='FHASTA'
4 /
NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
------------ ----------- -----------
1333281 28 3668140
SQL> select endpoint_number,endpoint_value from user_tab_histograms
2 where table_name='TCUENTA'
3 AND COLUMN_NAME='FHASTA'
4 /
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 2453157,36
1 2453168,08
2 2453181,13
3 2453194,03
4 2453225
5 2453231,64
6 2453231,68
7 2453231,72
8 2453231,75
9 2453266,01
10 2453316,15
11 2453321,86
12 2453322,83
13 2453323,06
14 2453323,08
15 2453323,1
16 2453323,13
17 2453323,15
18 2453323,18
19 2453323,2
20 2453323,22
21 2453323,24
22 2453323,26
23 2453329,59
24 2453336,67
25 2453374,59
26 2453385,65
27 2453385,67
75 2816787
29 filas seleccionadas.
SQL> select partition_name,num_distinct,num_buckets,sample_size from
USER_part_col_statistics
2 where table_name='TCUENTA'
3 and column_name='FHASTA'
4 /
PARTITION_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------ ----------- -----------
P1 1333280 75 50720 P2 1 1 65174
SQL> select Partition_name,bucket_number,endpoint_value from
user_PART_histograms
2 where table_name='TCUENTA'
3 AND COLUMN_NAME='FHASTA'
4 /
PARTITION_NAME BUCKET_NUMBER ENDPOINT_VALUE
------------------------------ ------------- --------------
P1 0 2453157,42 P1 1 2453167,81 P1 2 2453167,86 P1 3 2453169,02 P1 4 2453178,56 P1 5 2453181,12 P1 6 2453181,15 P1 7 2453189,08 P1 8 2453194,03 P1 9 2453194,06 P1 10 2453194,09 P1 11 2453231,6 P1 12 2453231,61 P1 13 2453231,63 P1 14 2453231,65 P1 15 2453231,66 P1 16 2453231,68 P1 17 2453231,69 P1 18 2453231,7 P1 19 2453231,72 P1 20 2453231,73 P1 21 2453231,74 P1 22 2453231,76 P1 23 2453231,77 P1 24 2453242,67 P1 25 2453278 P1 26 2453292,84 P1 27 2453305,74 P1 28 2453321,81 P1 29 2453321,83 P1 30 2453321,86 P1 31 2453321,87 P1 32 2453322,82 P1 33 2453322,83 P1 34 2453323,05 P1 35 2453323,06 P1 36 2453323,06 P1 37 2453323,07 P1 38 2453323,08 P1 39 2453323,09 P1 40 2453323,1 P1 41 2453323,1 P1 42 2453323,11 P1 43 2453323,12 P1 44 2453323,13 P1 45 2453323,13 P1 46 2453323,14 P1 47 2453323,16 P1 48 2453323,17 P1 49 2453323,18 P1 50 2453323,18 P1 51 2453323,19 P1 52 2453323,2 P1 53 2453323,21 P1 54 2453323,21 P1 55 2453323,22 P1 56 2453323,23 P1 57 2453323,24 P1 58 2453323,25 P1 59 2453323,25 P1 60 2453323,26 P1 61 2453323,27 P1 62 2453323,27 P1 63 2453323,32 P1 64 2453329,8 P1 65 2453329,82 P1 66 2453336,67 P1 67 2453344,2 P1 68 2453368,54 P1 69 2453374,64 P1 70 2453385,64 P1 71 2453385,65 P1 72 2453385,66 P1 73 2453385,66 P1 74 2453385,67 P1 75 2453390,36 P2 6498 2816787
77 filas seleccionadas.
SQL> explain plan for
2 SELECT * FROM TCUENTA
3 WHERE FHASTA =TO_TIMESTAMP('31-DEC-2999','DD-MON-YYYY')
4 /
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
| 0 | SELECT STATEMENT | | 3 | 399 | 5 (20)| | | | 1 | PARTITION RANGE SINGLE | | | | | KEY | KEY | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TCUENTA | 3 | 399 | 5 (20)| KEY | KEY | |* 3 | INDEX RANGE SCAN | ICTAFHASFDESPROSUB | 3 | | 4 (25)| KEY | KEY |
----------------------------------------------------------------------------
-----------------------------------
SQL> explain plan for
2 SELECT * FROM TCUENTA PARTITION (P1)
3 WHERE FHASTA =TO_TIMESTAMP('31-DEC-2999','DD-MON-YYYY')
4 /
Explicado.
| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 129 | 5 (20)| | | | 1 | TABLE ACCESS BY LOCAL INDEX ROWID| TCUENTA | 1 | 129 | 5 (20)| 1 | 1 | |* 2 | INDEX RANGE SCAN | ICTAFHASFDESPROSUB | 1 | | 4 (25)| 1 | 1 |
----------------------------------------------------------------------------
----------------------------------
SQL> explain plan for
2 SELECT * FROM TCUENTA PARTITION (P2)
3 WHERE FHASTA =TO_TIMESTAMP('31-DEC-2999','DD-MON-YYYY')
4 /
Explicado.
| 0 | SELECT STATEMENT | | 2334K| 300M| 14642 (55)| | | |* 1 | TABLE ACCESS FULL | TCUENTA | 2334K| 300M| 14642 (55)| 2 | 2 |
----------------------------------------------------------------------------
On Wed, 19 Jan 2005 20:19:17 +0100, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
> Hi Alfonso > > >I have a table where I have a date column with historic information > >in the same table, if date = '31-dec-2999' then is actual record if > >the date is different of 31-dec-2999 the is an historic record. Now > >there are about 5 million records and half of them are historic so > >there are > >2.5 million records with 31-dec-2999 and it is confusing the > >optimizer. > > Exactly for this reason such thing should not be implemented. > > >Can anybody give me any suggestion to tell the optimizer this odd > >distribution? I already try using histograms and didn't get a good > >execution plan and I would not like to use hints. > > How did you create the histograms? Can you give us an example of a > query that doesn't work? > > Chris > >
-- Alfonso Leon -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 21 2005 - 13:58:48 CST
 |
 |