February 2015

RMOUG Training Days 2015

Yet again, it was a fantastic time at the RMOUG Training Days 2015 conference, as it has been every other year I have attended it. That is in no small measure due to the incredible work of the organizing committee, and in particular the Training Days Director, my colleague Kellyn Pot’Vin Gorman of dbakevlar.com fame. For me personally, the travel to get to Denver Colorado was somewhat more daunting than in previous years (see my earlier post for why!), but once I got there it all went relatively smoothly. I flew in on the Sunday before the conference started to allow me to get over any problems from the trip, but as it turned out everything was just fine.

I had three abstracts accepted for the conference:

PeopleTools 8.54: %SQLHint Meta-SQL

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
 
This new PeopleCode meta-SQL macro performs a search of SQL statement for the nth instance of SQL command keyword and inserts a string after it.

PeopleTools 8.54: Table/Index Partitioning

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

 Partitioning in Oracle

Partitioning of table (and index) segments involves breaking them into several smaller segments where certain data values only occur in certain segments.  Thus if a query is looking for a certain data value it may be able to eliminate some partitions without having to scan them because by definition those values cannot occur in those partitions.  Thus saving logical and physical read, and improving performance.  This is called partition elimination or pruning.  It is often the principal reason for partitioning a table.

Data Guard Logical Standby – what does it mean?

With Data Guard, you have the choice between Physical and Logical Standby databases. Let’s see the differences! My demo starts with a Physical Standby, that is then converted into a Logical Standby (therefore the name of the database):

Learning for free – UK User Group Meetings Coming Up

There are a few user group meetings coming up in the UK over the next week or two.

Note, you need to register to attend any of these, follow the links.

First is Club Oracle London, which are evening sessions held in London with 3 talks plus free beer and pizza. The next meeting is Thursday Feb 26th at 103a Oxford Street, kicking off at 18:30. You can register for this free event here and also see more details. In brief, Simon Haslam is talking about Oracle Database Appliance, Martin Bach on 12C new features the marketing guys don’t push and finally Phil Brown giving a virtualisation case study, how NOT to do it.

12c Parallel Execution New Features: Hybrid Hash Distribution - Part 2

In the second part of this post (go to part 1) I want to focus on the hybrid distribution for skewed join expressions.

2. Hybrid Distribution For Skewed Join Expressions

The HYBRID HASH distribution allows to some degree addressing data distribution skew in case of HASH distributions, which I've described in detail already in the past.

PeopleTools 8.54: Global Temporary Tables

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Database Feature Overview

Global Temporary tables were introduced in Oracle 8i.  They can be used where an application temporarily needs a working storage tables.  They are named

  • Global because the content is private
  • Temporary because the definition is permanent

Or if you prefer

255 columns

You all know that having more than 255 columns in a table is a Bad Thing ™ – and surprisingly you don’t even have to get to 255 to hit the first bad thing about wide tables. If you’ve ever wondered what sorts of problems you can have, here are a few:

PeopleTools 8.54: Materialized Views

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Materialized Views in the Database

Snapshots were introduced in Oracle 7.  They were a way of building and maintaining a physical table to hold the results of a SQL query.  We are well used to a view being the results of a query, but here the results are materialised into a physical table.  Hence the feature was renamed materialized views in Oracle 8i.

Today materialized views are one among many replication technologies.  They are available in standard edition Oracle, and there is no licensing implication in their use.

Materialized views can generally be put into two categories

Oracle 12c SQL – Invisible Columns

Invisible Columns

Beginning with Oracle 12c columns may be marked “INVISIBLE” in CREATE/ALTER TABLE statement.

  • Invisible columns do not appear in SQL*Plus DESCRIBE or SQL Developer column display (does show in SQL Developer table column list)
  • SQL*Plus SET COLINVISIBLE ON will cause invisible columns to appear when the table is DESCribed
  • Invisible columns do not appear in SELECT * queries
  • Invisible columns are not included in PL/SQL %ROWTYPE
  • Invisible columns may be inserted into (by name) or omitted from INSERT statements (if NULLable)

When made visible again, columns appear to move to the end of table. Why? (see below)

COL$ View

What happens when a column is marked invisible? The database changes the column number in COL$ to 0; if you have access (probably only if your are a DBA); you can see this with the following query: