so hello all of you welcome back to my YouTube channel so in this video we are going to learn about table partition in SQL so how this partition works and how to do the partition actually on the Oracle application we'll learn in this video so basically what is this partition so in this partition let's take an example of this table you can see in this table all data is grouped together now in this case table is divided into different parts so this is part one and this is part two then in this case if the table is having lots of data thousands or millions of data and to improve the query performance or to improve the performance of SQL application we are dividing the table into different parts so again you can see in the second case that table again it is divided into sub parts that is part one it is further divided into subp parts that is subpart one and subpart two similarly Again part two is further divided into subpart three and again it is divided into subpart four so likewise we can divide a main table into different parts so this helps us to improve the performance of this application or the particular table now let's move on to the second slide so let's see what is the introduction of the table partitioning so definition in this case table partitioning is a technique that divides large database tables into smaller more manageable segments so basically in the partitioning what we are doing is we are dividing large database tables into smaller ones or we can say those are manageable segments so which are more manageable segments next what are the benefits of doing partitioning so the partitioning improves the query performance as I stated earlier so it will improve the query performance of your table then it facilitates the data management and it provides flexibility for the archiving or purging the data next what are the different types of partitioning so the basically there are various types of partitioning methods so those can be range partition list partition hash partition and composite partitioning next different types of partitioning so we'll discuss in detail what actually is range partition what actually is list partition hash partition and composite partition so first is range partition so what is this range partition now the data will be divided into the partitions based on the specified range of the values now let's suppose we have salary so in a table we have salary of range 5,000 to 20,000 so suppose this is the range of 5,000 so all the salaries of below 5,000 will be grouped together then next salary is of 1,000 sorry 10,000 so all the 10,000 salaries will be grouped together or it will be in the one part and next suppose we have salaries less than 15,000 so 11 uh 10,000 1 to 15,000 will be the one group so 5,000 1 to uh 10,000 will be one group and Below 5,000 will be another group so likewise based on the Range we can divide table into different parts that is called as range partition now second we have list partition so it divides or the data is divided into the partitions based on specific values of the column now let's take an example we have departments numbers called as 10 then we have department number called as 20 then we have department number called as 30 so likewise the number of data is present in department number 10 number of data is present in department number 20 and number of data or records are present in department number 30 so likewise we can do the partitions based on department number that is 10 20 and 30 so this is called as list partition now third we have hash partition so hash partition this in this case data is divided into the partitions based on the specified number of partitions suppose we have all the data together and if I specify the number of partitions suppose in this case I am specifying the number of partitions is three based on particular uh column now in previous case we have grouped the data or we have made Parts based on the salary range and also based on the department number so likewise I can specify the particular column and I will specify the number of partitions I am I want So based on that the hash partition will be uh working next we have composite partition so in composite partition it is combination of multiple partition methods for added flexibility and efficiency so this is all about different types of partitioning so we'll work in detail about this different types in our Oracle application so next we have syntax and examples of the table partitioning so part uh partitioning function so create function to Define logic for splitting data into the different partitions then partitioning key choose an appropriate column or exess expression to the partition table next create partitions so execute alter table statement to add partitions based on the defined function next example query so select St from table name partition specific partition name so all this stuff we will will do in our Oracle application so for better understanding we will work in the Oracle application we will work on the specified data and we will try to execute all this stuff next considerations for the implementation implementing table partitioning so First Data distribution so partitioning should be based on the data distribution to ensure balanced partition for the Optimal Performance so the balanced partition should be there so all the data should be distributed evenly so that the partition will be balanced next partition maintenance so regular maintenance such as partition uh planning table recognization is a crucial to maintain the efficiency of the partition tables next we have data load and manipulation so consider the impact on data loading and manipulation operations when implementing the table partitioning next we have performance advantages of table partitioning so first is faster query execution so partitioning improves query performance by reducing the amount of data scanned during the query execution so once we are doing the partition so the query performance will be improved so all scanning whichever required in the traditional case it will be avoided and if you are doing specific part partition then the scan data will be optimized next we have efficient data storage so partitioning optimizes data storage and retrieval in the large scale data warehouses and first backups and restores second third we have so partitioning allows for faster backups and restores by selectively operating on individual partitions now last conclusion and key points so first scalability so in this the table partitioning enables horizontal scalability as it allows for Distributing data across multiple storage devices next query performance partitioning improves query performance by reducing the amount of data accessed and processed next we have data management partitioning facilitates efficient data management enabling faster data loading and purging as well as as archiving of historical data so these are the some conclusions and key points of this presentation now we will do the implementation of the partition table so in this Oracle application we will Implement all the range partition list partition and hash partitions so first we will discuss about range partition so what is this range partition so already we have covered in the slide so let's take an overview on the the partition range partition so if you have data in a specific range you can use range partition for example let's suppose we have employee table that is EMP table so partition will be only on the table and we will do the partition based on the salary so salary suppose salary range is 1,000 to 5,000 so this will be in the one partition that is one part that is P1 partition then if the salary range is 5,1 to 10,000 this will be the second partition that is P2 likewise again we have third partition the salary range will be 10,1 to 20,000 so this will be the partition P3 so likewise we can do the parts of table into P1 P2 P3 based on the salary so this will again improve the performance of your query so proper management of data will be done by using this partition now next if you have data of salary on of 1,000 employees suppose in a table we you have 1,000 records then and salary is from 1,000 to 3,000 if this is my lowest salary and this is my highest salary of the employees then you can use range of less than 1,000 less than 2,000 and less than 3,000 so likewise you can do the partition so this will be partition one whose salary is less than th000 this will be second partition whose salary is less than than 2,000 and third partition will be less than 3,000 so likewise the partition can be done so let's work or let's create a simple table and we will try to implement this list partition in this table so what I'm doing is see uh See the syntax so first during creation of the table we can do the partition so create table so I am naming the table as part employee number employee name job MGR higher date cell commission department number and email are my columns so I hope you know the table creation syntax so if you don't know how to create table you can watch my previous video I will add the link in the description box so this syntax is to create table and this is data types that is number Vare and this 20110 are the sizes now so I have specified the table columns that is employee number e name job MGR now next complete this bracket and outside this bracket write Partition by range so this is first type that is Partition by range so we have four different types that is list partition range partition hash partition and composite partition so in this we are discussing about range partition so we'll do this range partition on column called as salary that is cell column so on Cell column we are doing the Partition by range partition so next we have interval which is optional so in this case we are doing interval of 5,000 you can write or you can if you're not writing it's okay so it is optional next in the next bracket you have to specify the ranges that is partition P1 this is first partition partition P1 values less than so specify values in the bracket that is 5,000 second partition P2 values less than 10,000 third partition P3 value is less than 15,000 and last partition before Valu is less than 20,000 so these are the values of salary as we are doing the partition on column called as salary all the values are from the column salary so we will be adding all the values in this table of range 5,000 to 20,000 right so column again the bracket is completed and write enable row movement so again this is optional syntax so if you are writing it's okay if you're not writing again it will work so this is total syntax of adding partition during creation of the table so I repeat this part once again so this creation of the table I have added tables that is employe number e name job MGR all the columns I have added next bracket complete complete the bracket and write Partition by range specify the type of partition so I'm specifying the type of partition as range and I'm working on the column called as Sal that is salary interval I keeping 5 ,000 but I have commented it so I don't want to specify interval I will tell you what is disadvantage of specifying or specifying the interval that is 5,000 which is optional so again uh in the next bracket write the partitions range that is partition P1 this is the name of partition P1 P2 P3 P4 so the values less than specify the value the salary should be less than 5,000 10,000 again this values should be written in the brackets so this is all syntax of creating partition during the table so let's run this query so I am running this query and let's see the table will be created yes so the table part is created so keep in mind our table name is part so we will using we will be using this part in the further per now so uh drop table no need to drop table already we now we have created the table now what suppose let's suppose what what I want I want to add further partition after creation of the table so if you once you create a table and if you afterwards if you if there is an requirement of further partition or adding further partition also you can add the partition after creation the table so what is the syntax to add partition after creation of the table is Alter table part that is table name add Partition p file so in up to P4 we have partitions in our existing table so I interested to add next partition that is P5 values less than 25,000 so up to 20,000 we have created the partition so again I want to add one more partition that is P5 whose value should be less than 25,000 so if I run this query you will see the table will altered yes the table part is altered so this is how the partition or further partition can be added in the existing table now in which case it is possible see the syntax or see the next line when your table when you are using interval in the query so as I said you here that interval is optional so purposely I have made the comment for this interval why because when you are using interval in the query you are not allowed to add partition after creation of the table so if you are specifying this interval during creation of the table this is not possible means after creation of the table you are not allowed to add partition if you are specifying the interval so this is the disadvantage of specifying the interval in the query now next let's try to insert the 10 records in the part table so our table name is part so I am inserting all the values I hope you know the syntax how to create how to insert the value so I have created one block so as I want to add 10 values I have created one block that is begin block and end block in in that begin and end block I have written all the queries that is insert into table that is part employee number e name job MJ all the all the columns I have specified and values into bracket all the values of the above columns I have specified so likewise I have added 10 insert queries so it is added into the block and in this block I have added commit statement so that the all the records will be saved in the database so if I run this part or if I run this block so if I want to add multiple values and if I want to uh insert in the single query then you have to write this into the block that is begin and end block in between you have to write the commit statement so that so that all the records will be saved so at once I adding all the records so you can see the procedure successfully completed so all the values I have added into the table so let's view the data let's see the select star from part that is table name order by salary so I am ordering the data into the by salary so you can see this is the data that is 10 records I have inserted right so I have ordered the data based on salary in the ascending order see the lowest salary of the employee so the lowest salary is 2,500 and maximum salary is 20,000 so this is my range So based on this range I have done the partition already uh in the previous part we have done the partition which was in the range of less than 5,000 less than 10,000 15,000 20,000 and 25,000 so see you can see the range so I have added the values in the range of 2,500 to the 20,000 in between all the range all the ranges are present like 4,500 6,000 800 10,000 12,000 14,000 16,000 18,000 So based on these values I have done the partition now let's try to retrieve data from the specific partition as we have done five partitions let's try to retrieve data so if I want to see only the data from the partition one so select star from part that is table name and next line you have to write partition and name of the partition into the bracket that is P1 so if I run this query you will see the in partition only two records are present whose cell is less than 5,000 that is 4,500 and 2,500 values are present in this partition P1 now let's see the value or partition P2 again you will see again there are two records in Partition P2 that is salary is 8 8,000 and 6,000 as we specified in the partition two the values are less than 10,000 so this is these are the two records likewise you can see the partition that is P5 whose Sal salary is less than 25,000 so only one record is there whose salary is 20,000 so this is how you can view a specific partition data now next let's try to split data or split partition how to split partition so the syntax to split partition is Alter table table name that is part I want to split partition P1 so P1 is uh less values are less than 5,000 so 0 to 5,000 values are present in the Partition p so again I want to split the partition P1 at 2,500 so this is mid value so I want to split partition into two parts so I am splitting partition P1 at 2,500 into partition P1 a and partition let's write here p1b right so I splitting partition that is P1 whose values salary values are in the range of 0 to 5,000 so I'm splitting at Mid value that is 2,500 at partition P1 a and Partition p P1 B So Below 2 2500 will be under P1 a and above 2500 will be in the partition p1b so let's run this query so alter table part so again you will see that partition table part is altered now let's view the data in the splitted partitions that is Select star from part table name partition P1 a into bracket so if I run this query there is no record okay fine now let's see in the uh partition to that is p2b okay there is error because we have specified name as p1b so if I run this you will see yes so two records are there right that is five uh s is 4,5 and 2500 so this is how we can split the data into two parts now let's how to merge the data now in the previous case we have splitted the partition now let's see how to merge the same partitions so alter table syntax we have alter table table name part merge partitions specify the name of two partitions that is P1 a and p2b that is already we have used here p1b so P1 a and p1b we have splitted into two partitions that is P1 a and P1 B so let's merge these partitions so merge part partitions P1 a and p1b into partition P1 so we will merge into original partition so if I run this qu you will see the table is altered let's fire the query let's try to view the data in the main partition that is merged partition select star from part partition P1 so if I view the data all the original record will be there so this is how you can do the merge merging in the partition now next how to do the rame rame the partition so again we have synex alter table part table name rename partition P3 to p3a so if I run this query so already we have original partition name as P3 so it will be renamed to the P3 a now let's try to view the data in the uh new name that is Select star from new name that is P3 a so all records will be there in the the p3a okay it is p3a Select star from P3 it is not sh data let me check the syntax okay we have to specify table name uh select star from table name is part and partition is this is 3 a that is P3 a so if I run this you will see the partition p3a is showing all the records so if I use the uh previous name so if I if I'm using old name which was renamed to p3a if I fire this query you will see no records or it will say that partition does not exist after renaming it will show that this it is not exist already it is it has been renamed to P3 a okay now next let's see how to delete trunet drop records from the partition so you we can drop a specific partition we can delete or we can truncate a specific partition all records from that partition will be removed so if I want to delete the records of partition P5 or if I want to delete all partition P5 delete from part that is table name partition P5 so this is the syntax I run this so one row will be deleted so in P5 only one row was there so that's why one row was deleted if I run if I try to see the data in the partition P5 select star from part partition P5 it will show no data so this is no data already we have deleted the data now let's see how to truncate the data same syntax is there alter table part truncate partition P1 so I'm deleting part P1 so P1 is deleted so if I view the data in P1 it will show nothing yes so likewise we can do trunet delete or drop in the partitions so I hope this part part is pretty much clear about manipulations about manipulation of the partitions now let's see the second type of partition that is a list partition so how this list partition Works let's see so if you have data in the specific list you can use the list partition for example if you have the data of th000 employees of different department number then you can use the list partition Department wise so let's see how it works so what I am doing is I am creating the table as uh part one so let me drop the table first drop table part one because already I have created this table okay already it has been dropped so let us create this table then this create create table part one so name of the table is part one employee number a and Department only three column colums are there number whereare and number are the data types so Partition by list so I'm doing the Partition by list that is Department numbers so partition my department number so partition P1 values 10 so all the values of partition all the values of department number 10 will be grouped together that is P1 into P1 so the department number who is having 10 all the department all the records of department number 10 will be grouped together in the partition P1 next partition P2 values of department number 20 so we are specifying this list partition on department number so all the Records who are having 20 department number will be part together or it will be the different part that is P2 partition P3 of department number 30 will be together enable row movement so if I run this the table will be created yes part one is created so likewise the table is created so this is uh one example of list partition let's see second example now again let us create one another table name as I will write name as part two right so part two I writing the name employee number a name department number and month I'm specifying the month it may be a higher month or it may it may be a date of birth month so uh these are the columns and I'm doing Partition by list so on which column I working I working on this month column so month column all the values I will specify so partition P1 values are Jan Feb and March so all the employees who are in the month of Jan February and March it will be under part one that is partition P1 then in P2 all the month values like a April May and June will be in the P2 in P3 July August August September likewise P4 will be there so likewise we can do the list partition so if I run this query again the table will be created yes so part two table is created now at your end you can do all the stuffs that is insert delete update or you can do uh rename table drop table trunet table you can do all the steps which we done which we did in the previous example that is which was in the uh range partition you can do all the stuff you can add randomly vales and you can do the manipulation operations in this Stu or you can uh face the data based on your requirement or based on the partitions so you can do at your end so next last partition is uh hash partition so we have to specify number of partitions we want to create for the available data then Oracle will divide data into the number of partitions so in this case only we have to specify number of partitions so let's see how the syntax is so create table part three now I will take part three employe number e name department number all the columns are same Partition by hash so we have to specify the type of partition so I'm using hash partition and employee number so I'm specifying it on employee number which is unique or we can see it is a primary key then partitions are five so I am I have specified the number of partition so this is the syntax you can see the difference so partitions by hash and you can simply write partition and specify number of partitions you are interested to group or to part to group the data into different parts so I want to make five parts of this my of this table so enable row movement so if I run this query the table will be created and further you can go and add the records right so I hope this uh part is pretty much Clear till this so in this video we have covered all the different types of partitions those were hash partitions then it it was list partition and also we have covered about uh range partition so in range partition we have covered all the manipulation operations how to add insert delete rename trunet all the stuff we have uh discussed in the range partition at U you can do the practice for the list and hash partition you you can use the same method so I hope this part is pretty much clear about partitions so that's all for this video we'll meet in the next video so if you have not subscribe to my channel you can subscribe my channel and press the Bell icon so thank you all and uh have a wonderful day
