We have seen creation of partitions , its benefits , different methods etc. Today we will see method to check the records we are inserting in the partitioning table are placed properly in intended partitions. Let’s create one table using range partitioning method. Let us insert some random records in table. Now lets query the […]
All posts by Jagruti Jasleniya
Jagruti Jasleniya
Drop Table Partition with Index Maintenance
We can simply add and drop partitions from table as we are dropping and creating a new table. While dropping and adding table partition , we need to take care of the index too. In previous post we saw simply removing partitions makes index unusable. For Reference Drop Table Partition without Index Maintenance and SKIP_UNUSABLE_INDEXES […]
Drop Table Partition without Index Maintenance and SKIP_UNUSABLE_INDEXES parameter
In this article we will see how we can drop the PARTITION without any index maintenance at run time and doing it later. I have following partitions available for my table TRANSACTIONS_NEW. I have one index on it on date_of_transaction column : Let us drop partition Y_2014 Let us check index status : We can […]
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 […]
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 […]
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 […]
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 : […]
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 […]
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 […]
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 […]