New Index enhancement in Oracle 12c Part 2 – Partial indexes on partitioned tables
In the article, you will have a look at the partial indexes available since Oracle 12c. The partial indexes enable us to index only certain partitions of a partitioned able and avoid indexing of the other partitions of the same table. The partial indexes can be either global or local indexes.
I will illustrate with examples the use of partial indexes. First, I will create full indexes and after that I will create partial indexes and we will compare the results.
New Index enhancement in Oracle 12c Part 1 – Multiple indexes on the same set of columns
New Index enhancement in Oracle 12c Part 1 – Multiple indexes on the same set of columns
In the article you will have a look at the following new index feature in oracle 12c ‘Multiple indexes on the same set of columns’. Staring with Oracle 12c you are enabled to create multiple indexes on the same set of columns. The following different indexes can be created on the same set of columns.
- Index of different type. Bitmap index and B-tree index.
- Partitioned index and not partitioned index.
- Locally partitioned index and globally partitioned index.
- Indexes differing in partitioning range and hash.
- Indexes that have different uniqueness properties. You can have both unique and non-unique index on the same set of columns.
In database row archiving in Oracle 12c
In the article you will have a look at the new Oracle 12c feature called in database row archiving available since Oracle 12c. In Oracle versions prior to 12c all table data was an active visible set of data, that is subject to DML performed by the application. In Oracle 12c the In database row archiving feature enable us to have two sets of table data an active visible set of data that coexists with archived and invisible set of data. The in database row archiving is a table feature that get enabled at table level with the ROW ARCHIVAL clause of create table or alter table. Once the feature enabled a new hidden table column ORA_ARCHIVE_STATE appears. This column can be used in SELECT list and can be updated but is not visible in describe issued from sqlplus prompt. It is the ORA_ARCHIVE_STATE column value that determines whether the row belongs to the active set of data or the archive invisible set of database. When rows get inserted into a table the ORA_ARCHIVE_STATE is 0. By modifying the ORA_ARCHIVE_STATE to 1 the row is transferred into the invisible archived set of table data. CTAS does create a new table but does not propagated the In database row archiving feature from the source table. The application can control the visibility to the active and invisible archived set of data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY [ALL/ACTIVE] statement at session level.
Using PL/SQL to run the SQL tuning Advisor in 11gR2
Using PL/SQL to run the SQL tuning Advisor in 11gR2
In the article we will have a look at the steps to invoke and run the SQL Tuning Advisor using the interface of the PL/SQL packages provided by Oracle. The SQL Tuning Advisor can be run in an alternative way using the OEM 12c Cloud Control interface. We will not cover the OEM 12c CC interface.
Star Transformation in 11gR2
Star Transformation in 11gR2
In the article we will review the prerequisites for the CBO to perform a star query transformation. We will use the SH samples schema. We will look at two cases with and without a bit map join index. Continue reading
Using the SQL ACCESS Advisor PL/SQL interface
Using the SQL ACCESS Advisor PL/SQL interface
In the article you will have a look at an example of using the SQL Access Advisor PL/SQL interface to obtain the access path and object structures change recommendations.
Configuring Fast Start Failover
Configuring Fast Start Failover
In the article you will have a look at an example of configuring Fast Start Failover (FSFO). Oracle 11gR2 has been used. The configuration is based on server zeus running the PRA database and server Apollo running the STBY2 database. The STBY2 is the standby database.
Session failover using Data Guard 11gR2
Session failover using Data Guard 11gR2
In the article we will see the session failover using an Oracle 11gR2 Data Guard. The feature is available since Oracle 11gr2 when the services become a CRS resource. The software used is Oracle RDBMS 11gR2. There are two nodes zeus running the PRA primary database and a STBY1 standby database and apollo running the STBY” standby instance. We will demonstrate a connection failover of a connection to a service on the primary while transitioning the STBY2 standby database open as an ADG as a new primary.
Packt’s $5 eBonanza returns
Following the success of last year’s festive offer, Packt Publishing will be celebrating the holiday season with an even bigger $5 offer.
From Thursday 18th December, every eBook and video will be available on the publisher’s website for just $5. Customers are invited to purchase as many as they like before the offer ends on Tuesday January 6th, making it the perfect opportunity to try something new or to take your skills to the next level as 2015 begins.
With all $5 products available in a range of formats and DRM-free, customers will find great value content delivered exactly how they want it across Packt’s website this Xmas and New Year.
Find out more at www.packtpub.com/packt5dollar
2014 in review
The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.
Here’s an excerpt:
The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 120,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 5 days for that many people to see it.
-
OraNA
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Guenadi N Jilevski's Oracle BLOG 