Do You Know What Is SQL Tuning Advisor Internals?

Oracle course over here shares about the latest topic of Oracle in database archiving and invisible columns.

In-Database preserving is new to Oracle 12c. The concept behind it is for making series unseen. This is quite identical to the unseen range concept.

The two functions, mixed together, can create big information in the table look not that big to the program.

The really exciting is David Watson’s opinion and example at the very end of the orafaq content. He reveals the need for filtration that cannot be achieved with accessibility. David recommend that “all indices need to add the ora_archive_state range, perhaps appended to main and exclusive key indices and prepended to other indices. If you don’t do this, you will find that many functions that could formerly be finished without in contact with the table will need table scans”.

The AMIS Techonology weblog focuses on the very same thing:

The Oracle Information says the following: “In-Database Archiving allows you to database series within a table by tagging them as non-active. These non-active series are in the information source and can be enhanced using pressure, but are not noticeable to a software. The information in these series is available for conformity reasons when necessary by establishing an interval parameter.

With In-Database Archiving you can shop more information for a many years within an individual database, without limiting program efficiency. Stored information can be compacted to enhance back-up efficiency, and up-dates to archived information can be postponed during program improvements to enhance the efficiency of improvements.”

The AMIS Technological innovation Blog says: Observe the focus on ‘without limiting performance’. It would seem that such in range archived information are missed in an intelligent way during complete table tests and other information recovery functions.

Yong Woo, reveals in his piece 12c: In-Database Archival …good, but negative with an example how a ‘TABLE ACCESS BY INDEX ROWID’ can be introduced to the performance plan because of the new pseudo range.

What I would like to demonstrate, is in a way also the other, that efficiency can be even better with Oracle in-database preserving. I have a table known as customers and exposure is set to ACTIVE. Almost all series of the table are “invisible”. Examine how bytes and cost improve, once I set the exposure to ALL. And of course, the complete table scan!

SQL> select * from clients;


SQL> alter session set row archival visibility = all;

Session altered.

SQL> select * from clients;


It is interesting to note that virtual columns can be made invisible:

SQL> alter table clients add (vip_status number

     generated always as (power(status,status)) virtual);

Table altered.

SQL> alter table clients modify (vip_status invisible);

Table altered.

INVISIBLE content are not reinforced in exterior platforms, group platforms, or short-term platforms and you cannot create a system-generated invisible range noticeable.

You can not yet create a desk invisible. If you begin tagging all content from the desk as invisible, at the very end you will get:

SQL> modify desk customers change (client_id invisible);

alter desk customers change (client_id invisible)


ERROR at range 1:

ORA-54039: desk must have at least one range that is not invisible

For more information please join the Oracle training course in Pune to make your career in this field as an Oracle certified Professional.