Learning is not a spectator sport

Complete listing of posts

  • Exceeding 1 million partitions

    In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions.  As we saw, the partition numbers are preordained based on the low boundary and the interval size. That also creates an interesting scenario that… Read more

  • Pending statistics and partition queries

    This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I thought I would bring it to your attention.  The issue occurs… Read more

  • The non-existent partition

    Things get a little interesting in the data dictionary when it comes to interval partitions.  Consider the following example: SQL> create table t 2 partition by range(x) interval(1) 3 ( partition p0 values less than (1) ) 4 as 5 select rownum x from dual connect by level <= 5; Table created. SQL> select count(*)… Read more

  • Transaction subtleties

    This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out. Lets set the scene with a simple procedure that commences a transaction, but then always fails SQL> drop table test$tab purge; Table dropped. SQL> SQL> create table test$tab (val varchar2(1)); Table created.… Read more

  • Data denormalization … another take

    I read an interesting article the other day about data modelling and data denormalization. I’ll paraphrase the topic and requirement here (apologies to the original author for any omissions or excessive brevity). We have a fictional application with chat rooms, people subscribing to those chat rooms, and posting messages in the chat rooms.  To satisfy… Read more

  • Exadata Express – I’m in !

    Jumping into 12.2 on Exadata Express is made easy with the packaging up of the client configuration files.  Here’s what I had to do to get SQL Plus going into the database 1) Download the client credentials file from the Service Console 2) Unzip the file into a folder 3) And voila!  Just set TNS_ADMIN… Read more

  • Shirts of OpenWorld

    OpenWorld just isn’t OpenWorld without enjoying a bit of fun with one’s attire                   Read more

  • The simple fix to date queries

    We had question in the OpenWorld panel about why queries on date columns are “always slow”.  Well….they aren’t but here’s a common cause of that misconception. Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is  a date). SQL> create table t as 2 select * 3 from dba_objects;… Read more

  • Partitioning an existing index

    I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s).  Their database was 11g, but I thought it would worth showing off some 12c features that would… Read more

  • Securefile in 12c – part 2

    In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c.  Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile LOBs defined.  This blog post explores that. We’ll start with “PERMITTED”… Read more

  • Happy birthday to …. well … us 🙂

    Last year on September 1, the AskTom site was resurrected under Apex 5 with myself and Chris Saxon manning the fort to deliver as much value to the Oracle community as the esteemed previous custodian of the site did in the many years preceding us. In the last year, we have answered ~2,500 questions taken… Read more

  • After OpenWorld…

    Don’t forget that there’s lot of great content still to come your way throughout the rest of the year. I’ll be part of the OTN Tour in Bangalore, Sydney and Brisbane. And for a change of pace… I’ll be doing a keynote  at UKOUG !  (I’m the person in the list below who isn’t a… Read more