Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 1

Concepts

What statistics are there on partitioned tables?  

Consider a partitioned table called SALES that has date-range partitions for financial quarters 1 and 2 (Q1 and Q2). The partition key is on the column QUARTER. The database gathers statistics for individual partitions so that cardinality can be estimated for queries that are pruned to a single partition. These are called partition-level statistics. To illustrate this with an example, I’m going to consider just a couple of statistics and ignore the others. The number of rows in Q1 and Q2 are 600 and 550 respectively. The number of distinct values (NDVs) for SALE_TYPE in Q1 is 30 and Q2 it’s 50:

When a query is compiled, if the Oracle Optimizer determines that it will need to access a single partition (using partition pruning, for example) then the statistics at the partition-level

will be enough to help determine the execution plan. Here’s a query that reads Q1 only:

SELECT SUM(amount) FROM sales WHERE quarter = 'Q1' AND sale_type = 'DIRECT';

If the Optimizer determines at compile-time that a query has the potential to access more than one partition, then individual partition statistics are not enough. In the next example, the query needs to access more than one partition:

SELECT SUM(amount) FROM sales 

WHERE  sale_type = 'DIRECT';

Continuar aqui

Que hay de nuevo en Oracle Database Cloud?

Aqui podemos ver la historia de novedades y mejoras en Oracle Database Cloud Services:

link: What's New for Oracle Database Cloud Service

Invisible Columns en Oracle Database 12C

In Oracle Database 12c, you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the “SELECT *” or “INSERT INTO VALUES” statements unless specifically selected – it can be reverted back to visible by using ALTER TABLE command.

When you change an invisible column in Oracle 12c database to visible, the COL# assigned will be the highest available, so the column becomes the last column in the table (not storage, only display). So, if you accidentally make a column invisible and correct this by changing it to visible, the column order changes. Therefore, if the application uses “SELECT *” or “INSERT” without column names, they might break!

Once a table’s column is set to invisible, the following key statements will not work for the invisible column:

• SELECT * FROM in SQL Statement
• DESCRIBE statement
• %ROWTYPE in PL/SQL variable declaration
• Invisible columns will still available for indexing and such indexes can be used for cost-based optimizer purposes. Don’t confuse an invisible index with an index on invisible column, as they are entirely different concepts.

  Continuar leyendo aqui

Fuente: http://allthingsoracle.com/invisible-columns-in-oracle-database-12c/

Optimizer with Oracle Database 12c Release 2 (White Paper)

Oracle White Paper
Optimizer with Oracle Database 12c Release 2

Introduction

The Oracle Optimizer is one of the most fascinating components of the Oracle Database, since it is essential to the processing of every SQL statement. The optimizer determines the most efficient execution plan for each SQL statement based on the structure of the given query, the available statistical information about the underlying objects, and all the relevant optimizer and execution features.
This paper introduces all of the new optimizer and statistics related features in Oracle Database 12c Release 21 and provides simple, reproducible examples to make it easier to get acquainted with them, especially when migrating from previous versions. It also outlines how existing functionality has been enhanced to improve both performance and manageability.
Some Oracle Optimizer features have been broken out of this paper and covered in their own. Specifically, they are:

» Optimizer Statistics and Optimizer Statistics Advisor
» SQL Plan Management
» Approximate Query Processing

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf