Tuesday, December 11, 2007

Ok, so what happened up there on stage...

I gave an 11g for DBA's "new feature" talk at UKOUG.  It was going pretty good - until...  Well, let me back up and start at the beginning.  I began my talk with:

You are going to see for the first time ever on screen a series of scripts.  I have not used these before in public - live.  Fingers crossed, let's hope for the best.

I shouldn't have done that - I jinxed myself :)

Everything was going great - until I got to some partitioning examples, interval partitioning in particular.  The script goes like this...

Ok, there is this new feature with partitioning called interval partitioning.  If you use this - then Oracle will automatically create new partitions based on your template, your description of how the partitions should be created.  This means you do not have to preallocate your partitions for a rolling window anymore.  For example, suppose you have an audit trail range partitioned by month - at the end of each month, you have to make sure you create the partition for NEXT month to avoid any errors.  With interval partitioning you can tell us to create a new partition on the fly as needed - without DBA intervention.  For example:

ops$tkyte%ORA11GR1> create table audit_trail
2 ( ts timestamp,
3 data varchar2(30)
4 )
5 partition by range(ts)
6 interval (numtodsinterval(1,'day'))
7 store in (users, example )
8 (partition p0 values less than
9 (to_date('23-feb-2007','dd-mon-yyyy'))
10 )
11 /
Table created.

 That creates a range partitioned table with everything having a TS attribute value less than 23-Feb-2007 stored in partition P0.  Further, we desire each new partition automatically added by Oracle to contain 1 days worth of data (numtodsinterval is a function that turns a number into a day to second interval). 


In order to demonstrate that Oracle does "logical things" here, I will insert a date in the future with regards to 23-Feb-2007, please note how Oracle did not create dozens of partitions for all of the interior values - it creates the MINIMAL set of partitions needed to contain our data:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '02-jun-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201 EXAMPLE TIMESTAMP' 2020-06-03 00:00:00'

 and then another date further out in the future, we'll use this to show that - even though it looks like all of the data between the 3rd of June and 15th of Sept will go into it - it won't:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '16-sep-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

two PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201 EXAMPLE TIMESTAMP' 2020-06-03 00:00:00'
SYS_P202 EXAMPLE TIMESTAMP' 2020-09-17 00:00:00'

Right now, it might appear that anything between 3-jun and 16-sep would go into SYS_P202, but if we insert a date in between the existing dates:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '11-sep-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201 EXAMPLE TIMESTAMP' 2020-06-03 00:00:00'
SYS_P203 USERS TIMESTAMP' 2020-09-12 00:00:00'
SYS_P202 EXAMPLE TIMESTAMP' 2020-09-17 00:00:00'

We can see that Oracle created SYS_P203 - it will fill in the interior partitions as needed...


It was at this point I received a question from the audience.  It was simply:


Why are your range high values in the year 2020?


Indeed.  I had not noticed that.  I pointed out right there and then "Hey, this is the first time after all...", but I did not see immediately what I had done.  I knew what I changed in my scripts (I used to use SYSDATE in the example, but as time marches on - I'd prefer to have static dates so I can have a predicable experience).  But it was not immediately obvious - so I said "Watch my blog next week, I'll write about it"


Which brings us here - what happened?


A few people pointed it out for me after the session - it was the use of the default Oracle DATE format to insert a string into a TIMESTAMP!  On my system, the NLS settings were:

ops$tkyte%ORA11GR1> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

 See that NLS_TIMESTAMP_FORMAT - when applied to 11-sep-2007 for example, it becomes:



  • 11 for the DAY
  • -
  • sep for the MONTH
  • -
  • 20 for the RR year - which is 2020
  • (space is ignored in the format, unless you use fx - for Format eXact)
  • 07 for the HOUR

We can see this easily:

ops$tkyte%ORA11GR1> create table t ( x timestamp, y timestamp );
Table created.

ops$tkyte%ORA11GR1> insert into t values ( '23-feb-2007', to_date( '23-feb-2007' ) );
1 row created.

ops$tkyte%ORA11GR1> select * from t;

X Y
----------------------------------- ------------------------------
23-FEB-20 07.00.00.000000 AM 23-FEB-07 12.00.00.000000 AM

 When we convert a string directly into the timestamp - we get 23-feb-2020, hour 7am.  If we use a DATE - which has a different default mask of dd-mon-rr - which works also with dd-mon-rrrr dates since the time is NOT included - we get the expected value.


So, the problem I encountered was an implicit conversion - introduced by me right before the session.  (Note to self: stop changing stuff before sessions, it is a bad idea).


The fix would be to use to_timestamp:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( to_timestamp('11-sep-2007','dd-mon-yyyy'), 'xx' );

1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P204 USERS TIMESTAMP' 2007-06-03 00:00:00'
SYS_P206 EXAMPLE TIMESTAMP' 2007-09-12 00:00:00'
SYS_P205 USERS TIMESTAMP' 2007-09-17 00:00:00'

So, now the demo works as expected and no (evil) implicit conversions happen... 

POST A COMMENT

10 Comments:

Anonymous Anonymous said....

thats why I call it demostruction

Wed Dec 12, 12:33:00 AM EST  

Anonymous Sokrates said....

implicit conversions !

I didn't think you yourself would stumble across them once

Wed Dec 12, 04:40:00 AM EST  

Anonymous John Scott said....

Tom,

Clearly Hall 1 was jinxed....

I was presenting in there on the wednesday and my VMWare machine (which I've used hundreds of times) refused to boot up with an obscure error and also the projector wouldn't hold the resolution of my laptop (never had a problem with that before) leading to what I can only describe as a 60 foot wide epilepsy-inducing image of a VMWare core-dump.

Luckily with about 5 minutes to go before I had to start, the techs fixed the resolution problem and my VMWare machine suddenly started to 'play nice'.

Live Demos...always good for the resting heart rate ;)

http://jes.blogs.shellprompt.net/2007/12/10/ukoug-day-3-or-sharing-a-stage-with-tom-kyte/

John.

Wed Dec 12, 07:56:00 AM EST  

Anonymous Anonymous said....

how funny that I learnt from Tom's writtings to always explicitly convert to the required data type in oracle!

Wed Dec 12, 05:13:00 PM EST  

Blogger Thomas Kyte said....

I would change the last two words from "in oracle" to "in any programming environment" in the above comment actually.

avoid implicit conversions - they'll get you every time, some time - maybe not today - maybe tomorrow - but they will GET you.

Wed Dec 12, 05:43:00 PM EST  

Anonymous Anonymous said....

these days I use things like:

select date '2007-10-11' from dual;
select timestamp '2007-10-11 12:00:00' from dual;

which are very compact, and don't depend on the locale...

Thu Dec 13, 07:22:00 AM EST  

Anonymous Pete Taylor said....

Hi Tom, I was at your jinxed talk, and enjoyed it very much, thanks. Can I suggest an additional new feature to cover? 11g seems to be using xpath version 2 whereas 10g r2 seems to be using xpath 1. Is there a roadmap which says when xmltransform will move from xslt 1 to xslt 2?

Tue Dec 18, 03:08:00 PM EST  

Blogger Thomas Kyte said....

Pete -

I don't follow the XML stuff to closely myself - suggest you query that on the XML forum on otn.oracle.com - they are pretty responsive over there.

Tue Dec 18, 03:11:00 PM EST  

Blogger Kiran said....

May I know How to post question in tom kyte site.

Sun Jan 20, 11:34:00 PM EST  

Blogger George Joseph said....

Tom,

Though i have been working with oracle database for about sometime, its only recently that i have started taking a real interest in db designing. I Think the ask tom site is brilliant. It gives me a chance to understand why people say "count(*) is slower compared with count(1)" and i used to nod and agree earlier but now its "show me facts and not your opinions".

In the current system that i am designing one of the comments i got from a senior developer is to store the dates as number itself. Since the reporting application never uses dates day comparison (rather it uses year(ie 2005 < 2004 comparison)though the data obtained from the flat file is in a timestamp format(mm/dd/yyyy hh24:mi:ss). His logic is instead of implict converstion storing dates as a number would be better? My gut feeling is to say that DATES are to be stored as DATES because oracle didnt create the datatype "DATE" for the heck of it. I wanted to check if there are any "facts" that i can use in this regard.

Sun Jul 13, 04:06:00 AM EDT  

POST A COMMENT

<< Home