Search

Top 60 Oracle Blogs

Recent comments

Oracle Development

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:

Oracle 12c SQL – Approximate Count Distinct

Approximate Count Distinct

Oracle 12.1.0.2 documents a new aggregate function named APPROX_COUNT_DISTINCT (first added internally to Oracle 11g to speed table statistics gathering). APPROX_COUNT_DISTINCT provides an approximate value without actually processing all of the database rows (Oak Table Network member and Oracle Ace Director Christian Antognini has documented consistent accuracy of plus-or-minus 4% with considerable performance improvement).

Standard COUNT DISTINCT

 

select count(distinct cust_id) from sh.sales;

COUNT(DISTINCTCUST_ID)
———————-
7059

Elapsed: 00:00:00.614

 

APPROX_COUNT_DISTINCT

select approx_count_distinct(cust_id) from sh.sales;

Oracle 12c SQL – “Top-N” Queries and Pagination

“Top-N” Queries and Pagination

Oracle 12c adds “top-n” type queries and paginated queries; features that have been part of other products for years.

In fairness to Oracle; we’ve been able to answer “top-n” queries easily using nested queries (SELECT in FROM) and/or Analytic functions (RANK/DENSE_RANK) for years; the new syntax simply makes for faster coding (the optimizer uses analytics under the covers to make things work).

Two new clauses have been added to Oracle SQL’s SELECT:

  • FETCH            FIRST/LAST nn ROWS FIRST/LAST n PERCENT ROWS
  • OFFSET           nn ROWS

Here is a simple query from the “SCOTT” schema’s EMP table; please note the salary values and the “top 4” salaries:

select ename,sal from emp order by sal desc;

Mobile Front and Center at Oracle Open World 2014

Wow! Sorry I have not posted anything since Oracle Open World. Lot’s of great new stuff is coming from Oracle that you should look into including some pretty cool stuff for mobile development.

Oracle Open World 2014 Begins

Lot’s of new stuff at Oracle Open World; if you’re here:
– Developers should look for new Mobile Application Framework (MAF) features and tools
– DBAs will find lots of information on In-Memory database and other new features of 12.0.1.2
– Applications users will see some pretty-cool User Experience and ease-of-use improvements
– Everyone will learn lots about Oracle’s Cloud offerings
I’ll post more once the official announcements are over!

Book Review: Oracle PL/SQL Performance Tuning Tips & Techniques

“Oracle PL/SQL Performance Tuning Tips & Techniques” by Michael Rosenblum and Dr. Paul Dorsey is a book you should read soon. A quote from Oracle’s Bryn Llwellyn in the book’s forward says it all “Every professional PL/SQL programmer should study this book and act upon its teachings.”
Misha and Paul take the scientific route to performance, providing readers the how-and-why of effective performance. The first chapter sets the tone showing how performance is an aggregate typically of nine-steps from execution of code on the client side, through middleware, to server, and back again to the client. Learn how PL/SQL works from two of the best whose shared stories of actual issues underline the importance of performance planning and thinking.

AZORA Rises Like the Phoenix!

The Arizona Oracle User Group (AZORA) was reincarnated Tuesday July 29 by a group of six Oracle specialists interested in focusing the Oracle User Community in Arizona. A board was elected and began review of the organization bylaws (dated 1990) to be completed and presented at AZORA’s inaugural Educational Workshop tentatively scheduled for October. The new board includes: President, John King (King Training Resources); Vice-President Danny Carrizosa (OneNeck IT Solutions), Treasurer, Raj Chotalla (Intel), Secretary, Carlos Aquilar (GoNet USA), and Past-President Stephen Andert (IBM). Two of AZORA’s past presidents Steve Lemme (Oracle) and Stephen Andert (IBM)  are actively involved and lending their experiences and expertise. Visit the “AZORA – Arizona Oracle User Group” LinkedIn page to learn more.

Exciting Oracle Developments

The summer has brought us a new version of Oracle’s flagship database and the Introduction of Oracle Mobile Application Framework. Both, truly big news.

Oracle 12c 12.1.0.2 was released in July! This is a full release and includes many new features including:

  • In-Memory Database
  • Big Data SQL
  • JSON Support
  • RESTful Data Services
  • Improved Index Compression
  • Zone Maps
  • Attribute Clustering
  • Full Database Caching
  • Enhanced Multi-Tenant Features
  • Rapid Home Provisioning
  • Approximate Count Distinct

For complete documentation; see Oracle’s web site: http://docs.oracle.com/database/121/NEWFT/chapter12102.htm#NEWFT003

Oracle 12c SQL IDENTITY Columns and Default SEQUENCES

As promised, here is the first in a series of posts dealing with Oracle 12c new features.

Creating new table rows often requires assigning a key value. In the past, it has been common to use an Oracle SEQUENCE to generate key values using an Insert trigger. Oracle 12c provides two new options: IDENTITY columns and SEQUENCEs used as column default values.

IDENTITY Columns

IDENTITY columns are new to Oracle, but, not new to the database world. IDENTITY columns use an Oracle SEQUENCE “under the covers” and their creation is automatic rather than manual. Should a table be dropped and recreated, the IDENTITY value starts over again.

Oracle has had SEQUENCES for years; the IDENTITY column allows use of a SEQUENCE as part of a column definition (much like some competitor databases):

Oracle 12c Edition-Based Redefinition (EBR)

Oracle 12c has many great features for DBAs and Developers; I’ll be talking about more features 12c in future articles (watch this space).

Edition-Based Redefinition made its debut in Oracle 11g and provides an ability to significantly reduce downtime due to changes in PL/SQL and/or SQL. Oracle 12c removes some limitations present in 11gR2 implementation of EBR:

  • Materialized Views may use editioned resources
  • Virtual Columns may be used with editioned functions

Materialized Views are not-editionable; but, Oracle 12c allows them to depend upon editioned objects with a new clause and new syntax for ENABLE QUERY REWRITE. The EVALUATE USING clause indicates that a referenced object is editioned (editioned objects are invisible otherwise). ENABLE QUERY REWRITE may be limited to selected editions. The new Materialized View CREATE/ALTER syntax looks something like this: