Friday, 30 March 2018

Getting started with Oracle Database In-Memory Part II - In-Memory Population

In part two of this series on getting started with Oracle Database In-Memory I’ll explain how and when objects are populated into the In-Memory column store (IM column store) and exactly how you can control it.

Which objects are populated into the IM column store?

As I mentioned last week, only objects with the INMEMORY attribute are populated into the IM column store. The INMEMORY attribute can be specified on a tablespace, table, (sub)partition, or materialized view. In this example the INMEMORY attribute is specified on the customers.

Wednesday, 28 March 2018

Getting started with Oracle Database In-Memory Part I - Installing & Enabling

Now that Oracle Database has been officially released, I can finally start sharing more technical details on how Oracle Database In-Memory (Database In-Memory) works.

I thought we should start this series of posts right at the very beginning by answering the most fundamental question, how and when is Database In-Memory installed and enabled.

Let’s start by doing a clean install of and allowing the installer to create a typical single instance database.

Monday, 26 March 2018

Monitor Query Performance Using Query Store in SQL Server


There are various methods to monitor query performance in SQL Server. One of the best ways is to analyze the query execution plan and identify the queries that can cause performance bottlenecks. Sometimes, query execution plans get changed during the query execution due to certain factors of SQL Server. Then there is a need to understand this behavior, so you can review the query execution plans to identify the change and understand the overall performance. This can be done only if your system is persisting the required information for later analysis. The Query Store feature in SQL Server 2016, SQL Server 2017 and Azure SQL Database version provides the capability to store query execution plan information and gives you the opportunity to analyze it.

Friday, 23 March 2018

Inline External Tables in Oracle Database 18c

Oracle Database 18c allows you to access data in flat files using an inline external table defined in a SELECT statement.

Oracle Database 18c, Oracle Database Certifications, Oracle Database Tutorials and Materials

Wednesday, 21 March 2018

Private Temporary Tables in Oracle Database 18c

Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.

If you've come to Oracle from a different database engine, like SQL Server, you might be confused by Oracle's idea of temporary tables. In SQL Server developers will regularly create a temporary table to do some work and drop it. In Oracle a Global Temporary Table (GTT) is a permanent metadata object that holds rows in temporary segments on a transaction-specfic or session-specific basis. It is not considered normal to create and drop GTTs on the fly. With the introduction of private temporary tables, Oracle has an option similar to that seen in other engines, where the table object itself is temporary, not just the data.

Monday, 19 March 2018

Qualified Expressions in PL/SQL in Oracle Database 18c

Qualified expressions provide and alternative way to define the value of complex objects, which in some cases can make the code look neater.

◈ Syntax

The basic syntax for a qualified expression is as follows

Friday, 16 March 2018

Data Pump (expdp, impdp) Enhancements in Oracle Database 12c Release 2 (12.2)

This article provides an overview of the main Data Pump enhancements in Oracle Database 12c Release 2 (12.2).

Some of these features are not listed in the expdp -help or impdp -help usage text.

1. Parallel Export/Import of Metadata

In previous releases the PARALLEL parameter was only relevant to the export/import of data, with all metadata processed serially. In Oracle 12.2 both metadata and data can be exported in parallel provided it is not a transportable tablespace operation.

Wednesday, 14 March 2018

External Tables and XMLTAG to Load XML Documents in Oracle Database 12c Release 2 (12.2)

This article describes how to use the XMLTAG clause of an external table to load XML fragments from XML documents in Oracle Database 12c Release 2 (12.2).

Monday, 12 March 2018

Partitioned External Tables in Oracle Database 12c Realease 2 (12.2)

Partitioned external tables were introduced in Oracle Database 12c Release 2 (12.2), allowing external tables to benefit from partition pruning and partition-wise joins. With the exception of hash partitioning, many partitioning and subpartitioning strategies are supported with some restrictions.

Friday, 9 March 2018

Efficient Function Calls From SQL

This article presents a number of solutions for making calls to PL/SQL functions from SQL more efficient.

◈ The Problem

Sometimes we require functions in the select list of a query. These may be built in functions, or functions we've coded ourselves. The default action is to call the function for each row returned by the query. If the function is deterministic, for the same input parameter signature it gives the same output, and the number of unique parameter signatures used is low compared to the number of rows processed, this can represent a lot of wasted effort.

Wednesday, 7 March 2018

How to Send a TDE Encrypted Backup to Someone Outside Your Organization

Once you enable your database to be encrypted with Transparent Data Encryption (TDE), the physical database files, and the database backups are encrypted.  If your database and database backup are encrypted, then how can you send the encrypted backup to a person outside your organization?