Dropping Table and Index Partition

Dropping Table partition or Index partition is a part of routine maintenance job of DBA. When to drop or add partition ? Well, it depends on the requirement . For instance , in range partitioned tables whenever new range comes in the picture we need to add new partition for the table if we have […]

Read More

Partition Pruning with Example

In previous article we saw the benefits of partition pruning. Partition Pruning in Oracle Here we will see how partition pruning takes place when you run the query accessing your partitioned table. For instance , you have transaction table from year 2011 to 2019 with different partition for each year.  And you want statement of […]

Read More

Partition Pruning in Oracle

Pruning itself means to cut the dead parts and work on the pieces which are indeed important one. In Oracle partition pruning works in the same manner. Oracle can recognize which partitions and sub-partitions need to be eliminated and which partitions should be accessed for the query to be processed. When you have created some […]

Read More

Types of Index Partitions

In previous article we have seen overview of index partitioning and advantages of it. In this article we will see example of different types of indexes on partitioned tables. 1.Non-partitioned Index: We can create normal non-partitioned index on partitioned table. Example : TRANSACTIONS table is partitioned table in my database. 2.Global Range Partitioned Indexes : […]

Read More

Composite Partitioning in Oracle

In this article we will see examples of various types of composite partitions 1.RANGE-HASH composite partitioning : 2.LIST-HASH composite partitioned table 3.LIST-RANGE Partitioned table : 4.LIST-LIST partitioned table 5.RANGE-LIST Partitioned table : 6.RANGE-RANGE Partitioned table : Thank you for giving your valuable time to read the above information. If you want to be updated with […]

Read More

Types of Table Partitions in Oracle

In this article we will see types of partitions available in Oracle. Range Partitioning : Range Partitioning works with range of column values. It sets lower boundaries and upper boundaries to the partition. It is widely used with DATE data types. Lets create one range partition to understand it better.It is generally used with DATE […]

Read More

Index Partitioning

In previous article we saw what is partitioning in oracle and the benefits it brings . http://oracle-help.com/oracle-database/oracle-partitioning/         In this article we will see Index partitioning. Indexes are the essential part of any SELECT and DML operations which we run on the database. So , when we tune the table we must consider […]

Read More

Oracle Partitioning

As a DBA we must have heard about Partitioned tables or Partitioned index in our tenure or may be we have used it at some extent.Here in this post we will see what is Partitioning and what benefits it brings. The first question that arises is What is Partitioning ? Partitioning means dividing your large […]

Read More

How Select Statement Works

When Oracle receives a sql query, it requires to run some pre-tasks before actually being able to really run the query. Combination of these tasks is called parsing. During parsing the below operations used to perform. Database validate the syntax of the statement whether the query is valid or not.For example, the following statement fails […]

Read More

Explore the Automatic Diagnostic Repository (ADR)

ADR – Automatic Diagnostic Repository is a file-based directory structure formed in a hierarchical way to store diagnostic information of the database. For each database instance, ADR directories are created and maintained. In 12c release two, it is not changed. Hence we are having one instance for root container only. ADR directories are managed for […]

Read More