Learning is not a spectator sport

Complete listing of posts

  • Attribute clustering (part 2)

    Attribute clustering (part 2)

    In the previous post, I demonstrated attribute clustering by creating a table of source data which contained data in randomised order, via SQL> create table source_data as 2 select d.* 3 from dba_objects d 4 where object_id is not null 5 order by dbms_random.random; and then it loading into a table with attribute clustering defined… Read more

  • Attribute clustering (part 1)

    Attribute clustering (part 1)

    One of the nice facilities in 12c is the ability to instruct the database to co-locate data during loading in order to facilitate reduced resource consumption for subsequent queries. Here’s an example of it in use. First I’ll create table SOURCE_DATA to mimic some randomised incoming data feed SQL> exec dbms_random.seed(0) PL/SQL procedure successfully completed.… Read more

  • Performing a large correlated update

    We had a question on AskTom recently asking for the best way to update a new column on a very large table.  Four in-place update options are at our disposal: Serial update SQL Parallel DML update SQL DBMS_PARALLEL_EXECUTE PL/SQL batching (as long as sensible restart-after-error logic is readily available) all of which will achieve the… Read more

  • OTN tour 2016 APAC

    The OTN tour came to APAC this year, so it’s been a pleasure and privilege to be able to participate in some of the legs.  Being Perth born and bred, I know all too well that any travel to Australia from … well… anywhere except Australia, is a long haul, so I’m very grateful to… Read more

  • Code enhancements without changing code

    An interesting suggestion came through on AskTom this week, which prompted the following exploration. Let us assume you are populating a table with INSERT INTO EMP SELECT * FROM SOURCE_EMPS and it falls over with ORA-12899: value too large for column To capture the data that caused that error is not trivial. Of course, we… Read more

  • BULK COLLECT into nested table

    I had an observation come to me last week about PL/SQL and populating nested tables. “The BULK COLLECT into statement cannot be used repeatedly to append results into a table. Instead, it silently truncates the target table each time. “ This is true.  However, if you need to use BULK COLLECT to append results into… Read more

  • Truncating a timestamp to the second

    We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value.  At first glance, TRUNC seems fine. SQL> select trunc(localtimestamp,’MM’) to_the_month from dual; TO_THE_MO ——— 01-OCT-16 1 row selected. SQL> select trunc(localtimestamp,’YYYY’) to_the_year from dual; TO_THE_YE ——— 01-JAN-16 1 row selected. SQL> select trunc(localtimestamp,’DD’)… Read more

  • Taking a peek at SYS_CONTEXT

    There was Twitter discussion about using context variables, accessed via SYS_CONTEXT within queries. It’s an easy means of passing parameters into a view. The question that got asked was – does a sys_context variable act exactly like a bind variable. Let’s take a look at a simple demo to explore that. We’ll create a table… Read more

  • Translating SQL (a migration tool)

    When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology.  But there are not many “perfect worlds” out there  in the IT landscape   So… Read more

  • Locked rows and lunch breaks ? A simple fix

    Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem.  That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling back the transaction.  The metaphor commonly used was the “someone locks… Read more

  • Generating rowids

    We have several posts on AskTom where it is described how to “carve up” a table into equi-sized chunks in order to (say) perform a task in parallel on that table.  Here is an example of one. Much of this has nowadays been obsoleted by the DBMS_PARALLEL_EXECUTE package, but in either instance, one key point… Read more

  • Running external programs from the scheduler

    Although I normally use the job or scheduler facility to run database-centric style processes, most commonly PL/SQL programs, there is nothing to stop you from using the scheduler to gain control over tasks that might normally need to be done outside of the database.  And of course, in the world of virtualization, cloud and other… Read more