Tag: sql

AskTOM TV–episode 11

Just a quick note to give the supporting collateral to the latest episode of AskTOM TV. The question I tackled is this one: https://asktom.oracle.com/pls/apex/asktom.search?tag=want-to-retrive-numbers-in-words which was a fun one to answer because it showcases several useful SQL techniques: Using CONNECT to synthesize rows, Using…

Partition-wise join

So just what is a “partition-wise” join ?  We will use a metaphor to hopefully explain the benefit. Let’s say two people, Logan and Shannon, decide to move in together.  If each of them already have an existing residence, they will both have a…

Oracle Code … Not for database people ?

Jump over to the Oracle Code home page and you will see the “mission statement” of the Oracle Code conference series: “Learn from technical experts in sessions for developing software in Java, Node.js, and other languages and frameworks.” You might hence be thinking that…

AskTOM TV episode 8

On AskTOM episode 8, I’ve taken a look at locating the SQL Plan Directives used for a particular query.  Here is the script output from the video if you want to use this for your own exploration SQL> SQL> create table t as 2…

SUM is better than DISTINCT

There is a good chance that (based on this blog post title) that you’re expecting a post on SQL, and that’s understandable. But I’ll come clean nice and early – that was just to lure you in The post is about SUM and DISTINCT,…

Pi Day, March 14

Geeks around the world will be celebrating Pi day, a shameless excuse to put the month before the day like the Americans do so we can talk Mathematics on “3.14” day So what better way to show how cool SQL can be with some…

Partition count for interval partitioned tables

When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below SQL> create table SALES 2 ( cal_year date, 3 txn_id int,…

Multi-table insert

An interesting question came through on AskTom recently.  The requirement was to perform a single pass through a source table, and load the data into three target tables. Now that’s trivially achieved with a multi-table insert, but there was a subtle “twist” on this…

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…

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…

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…

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…