Partitioning in Oracle Explained with Real project Examples : Introduction

This Video series will explain partitioning and its use cases referencing real project examples from different domain.
It will explain what when and why of partitioning in a simple but elaborate manner.
This is the 1st video which explains why partitioning is required and what are the advantages we gain from it.
The following videos will explain when should we do it and how.
Part 1 : Partitioning in Oracle : • Partitioning in Oracle...
Part 2 : Partitioning in Oracle - Performance Basics : • Partitioning in Oracle...
Part 3 : List Partitioning in Oracle : • List partitioning in O...
Part 4 : Range partitioning in Oracle : • Range Partitioning in ...
Part 5 : Hash Partitioning in Oracle : • Hash Partitioning in O...
Part 6 : Composite Partitioning in Oracle : • Composite Partitioning...
Part 7 : Reference Partitioning in Oracle : • Reference partitioning...
Part 8 : Local Vs Global Partitioned Index in Oracle 11g : • Local Vs Global Partit...
Part 9 : Partitioning enhancement in 12 c : • Partitioning Enhanceme...
#Oracle #Partitioning #Techcoach

[@TechCoach]

Here is a complete list of partitioning videos. Part 1 : Partitioning in Oracle : kzbin.info/www/bejne/o2TUZZ-oeptqZ7s Part 2 : Partitioning in Oracle - Performance Basics :kzbin.info/www/bejne/hYKbfYuYrbF8a8k Part 3 : List Partitioning in Oracle : kzbin.info/www/bejne/rWfOc2BvZdV5l5Y Part 4 : Range partitioning in Oracle : kzbin.info/www/bejne/jHPOoXxqgM5nb7M Part 5 : Hash Partitioning in Oracle : kzbin.info/www/bejne/aIrbgHmVqK-Agqs Part 6 : Composite Partitioning in Oracle :kzbin.info/www/bejne/p5DIanyhg9mGjcU Part 7 : Reference Partitioning in Oracle :kzbin.info/www/bejne/pmnKf6qoa7enmtE Part 8 : Local Vs Global Partitioned Index in Oracle 11g : kzbin.info/www/bejne/qmial6yLqZ2Lhtk Part 9 : Partitioning enhancement in 12 c :kzbin.info/www/bejne/pZC6m5Smmb2ch8k

[@mangeshmankar1046]

Very Crisp ,clear and excellent explanation.Perfectly paced.Keep up the great work.

[@TechCoach]

Thanks a lot for the kind words, Please support me in my new channel by watching subscribing and sharing with your friends kzbin.info

[@andersericsson4654]

Thx for giving me the basics I needed - very pedagogical.

[@TechCoach]

Hi Anders, Thanks a lot for the kind words I have a complete series on partitioning. It may interest you. kzbin.info/aero/PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA

[@raghavendranramachari488]

Very very useful and clear explanation...it matches exactly with my speed of learning...

[@TechCoach]

Thanks a lot buddy for the wonderful comment, I am glad it helped.

[@purnachandranayak7400]

Just awesome ....Nice Explanation.... Easy to Understand with proper Explanation

[@TechCoach]

Thanks for the kind words. I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@vinzy147]

Very neatly explained.Looking forward for your upcoming videos. Keep up the good work ahead

[@TechCoach]

+vinit naik Thanks a lot for your kind words, I shall the upload the following videos soon

[@davindertagore4225]

I feel partitioning is a complex topic and tough to understand, but you explained it very nicely. Very well put with real time scenarios. Thank you and looking forward to your next videos.

[@TechCoach]

There is a complete video series on partitioning, I hope you enjoyed all of those videos

[@TechCoach]

I have a small request, I am working on this new youtube channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@mohanvilla0789]

Awesome.. perfect..:)

[@rohansharma141]

great job, well explained. Thanks!!

[@TechCoach]

Thanks a lot Rohan for the kind words, I have created an entire series of videos on partitioning they may interest you as well :)

[@alifia276]

Thank you for sharing! You have explained really well:)

[@TechCoach]

Thanks buddy for the kind words :)

[@padmajarani9392]

Very Useful vedios thank you brother

[@seetamajhi9934]

Thank you for explaining it so clearly.

[@TechCoach]

Thanks seeta for the kind words, Your support is my motivation

[@santoshkumar-pk6xt]

Terrific

[@lucadrogon]

Please create videos like this. These are very easy to follow. Thank you.

[@TechCoach]

Thanks buddy :) I am glad I could help

[@amol013]

Thank you for such excellent explanation. Helps a lot. Please keep uploading more videos. Thanks.

[@TechCoach]

Thanks a lot for the kind words Amol. Sure I will upload more soon :)

[@TechCoach]

Hi Amol, I have a small request, I am working on this new youtube channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@phanmemchamcongHRADEnterprise]

Helpfull

[@mallikarjunpatil7553]

Great Explanation!

[@TechCoach]

Thanks buddy for the kind words:)

[@aniket8328]

Good content! Thanks!

[@TechCoach]

Thanks Aniket for the kind words I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@aneksingh4496]

Best as always

[@TechCoach]

Thanks a lot buddy for the kind words

[@atul.thorat]

Very helpful , thank you.

[@TechCoach]

Thanks Atul for the kind words, I am glad I could help

[@uperwala]

Very well explained Vivek. Thank You.

[@TechCoach]

I am glad you liked it, Thanks for the kind words :)

[@pateldewangana]

Excellent session..

[@TechCoach]

Thanks a lot for the kind words. I would really appreciate if you help me in my new Initiative "An Indian Abroad" by subscribing viewing and sharing. kzbin.info

[@mohammedhusenkhatib5752]

Very informative and well explained

[@TechCoach]

Thanks for the kind words, Apologies for the late reply

[@andishez5359]

TO EVERY ONE : Suggesting you to continue up to end patiently and succeed for your purpose of searching this topic. They are very useful, create deep understanding and clear all doubts and fill all gaps in your partition knowledge you had up to today. For sure you will confirm me in middle of this journey. ENJOY

[@MrRajanSrivastava]

very very informative and easy to understand, thanks for being a good mentor...I have one query, what are the static and dynamic pruning , which one is default and how they work.

[@TechCoach]

Thanks for the kind words Rajan I will answer your question shortly meanwhile I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@MrRajanSrivastava]

Subscribed :)

[@VijayNaramalli]

Excellent

[@TechCoach]

Thanks Vijay for your kind words. Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@23yaganti]

Very good clear. Thank you very much

[@AnIndianAbroadd]

Thanks a lot Rama Yaganti for the kind words. I need a small help I am working on this new youtube channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@sundarrajk4380]

@@AnIndianAbroaddtry to take it re re re r re r see rr re r a re re re re 44 re r re are the r r re re r re r see see see rr see rr t re re re re 44 rest rest to you to do the needful r see rrrrrr err rrrrrr try to rr4r4 r rrrr r rrr r rr4 Dr stay r re the same T6 buy hi by buy bunny buy huh but day:0:0 do see be be busy p my voice mail by p be by my car bc my to me hi to her in cup fun cd guess the my best to rest 🌲,zz so

[@sujaa1000]

Hi Vivek, Thank you so much, this is so clear to understand, and it is so very helpful. Regards, Sujaa

[@TechCoach]

Thanks sujaa, Please share it with people who may benefit from it. Your support is my motivation:)

[@sujaa1000]

@@TechCoach Ofcourse, I recommend you channel to all my colleagues. Regards, Sujaa

[@coolviveka]

My name also Vivek, great bro keep it up ...!!

[@TechCoach]

Thanks Vivek for the kind words,I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@snehadubey2442]

Thank you so much

[@TechCoach]

You're most welcome! I am glad I could help

[@shravankumar.v8244]

Good explanation

[@TechCoach]

shravan kumar. v Thanks a lot :)

[@subhashisoracle3944]

outstanding bro

[@TechCoach]

Thanks a lot for the kind words bro :)

[@praveenganesh3782]

Thank u 😊

[@TechCoach]

I am glad I could help Praveen :)

[@srikanthgandhi7289]

Super Explanation

[@TechCoach]

Thanks Srikanth for the kind words I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@naveenpusa9015]

Very well explained sir 🙂

[@TechCoach]

Thanks naveen for the kidn words :)

[@sagarbodala9590]

Good session

[@TechCoach]

Thanks Sagar Bodala,I have a small request, I am working on this new youtube channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@sitaramsahoo811]

Hi, While making a series of video on a topic, please mention the sequence either on the beginning of the slide or in the title itself. Thnx

[@TechCoach]

Sure Sitaram, I will do that for my upcoming videos. I have a small request I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@swapnilsabale6401]

Hello, Thank you so much this series of video, I have 1 question if we delete the partitioned then what will happened with data of base table?

[@suchitasinha4808]

Why your concepts r crystal clear..envy!!! Your videos were really helpful..just a request can we get any help in snowflake db

[@TechCoach]

Thanks Suchita for the kind words :) Snowflake, Unfortnately not at this moment. But I will look into it soon.

[@shaliniguha1822]

Is it always necessary that the NA partition will be on a storage rack different from AP? I mean it’s possible that both are unavailable right? Also what if I have a process that requires both the NA and AP data at the same time? Will it impact the process?

[@sreekeshnatht.r7242]

is it possible to partition an existing table? if, yes please write the syntax. I m using 19c

[@modernphysco7436]

I have partition in my table say by year(2011,2012...2018). And i need to update an single row in 2011 partition,and insert a row in 2011 partition , how can i do this.

[@TechCoach]

Your DML code does not change for a partitioned table, you can update and insert the way you have done for a normal table.Oracle will take care of the rest

[@TechCoach]

I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad". kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@Chitikireddyramesh]

How to partitioned existing table with huse data in Oracle 10g in production please provide steps

[@vineshchauhan2282]

Super

[@TechCoach]

Thanks a lot buddy

[@TechCoach]

@Vinesh. Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@jaisinghprasad6544]

Good explanation , can you share the PPT for partition topic

[@TechCoach]

Thanks Jaisingh, My apologies I don't have this PPT now

[@prashantmhatre9225]

Hello , when will update new videos , the videos are very good and much informative, we are waiting for your new videos. Thanks in advance

[@TechCoach]

Thanks Prashant for the kind words, Apologies for the late reply

[@prateekpamecha7854]

How to perform split or fast split partioning?

[@codetourist6996]

can we use two columns for partioning, like in second example , data partioned by year , still furthuer partion by region.

[@TechCoach]

Sure please watch this video it should answer your question. kzbin.info/www/bejne/p5DIanyhg9mGjcU I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@codetourist6996]

Tech Coach watched the order videos and subscribed.

[@TechCoach]

Thanks a lot mate, I really appreciate it.

[@surabhisood4089]

How do we decide , how many partitions are needed?

[@TechCoach]

It depends on our dataset.

[@nishaphd]

Crystal clear explanation, but if you use very simple English, you would get more followers.

[@TechCoach]

Thanks a lot for the kind words. I will try to keep it simple in my next videos. Please help me build my new channel by watching, Subscribing and sharing with friends kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@pankajkharade6936]

Can we create a partition on the table which already have a large amount of data

[@sujis9263]

Yeah we can by using dbms_redefinition and interim tables

[@sujis9263]

In 12c, it's being overcome by using alter table tablename modify...

[@ashish13007singh]

how to see names of partitions in a table

[@TechCoach]

You can query the metadata table shown in the video series to view the name of the partitions

[@sukritsen9530]

Hello, I have a question, how can we partition on a column which has timestamp data, how can I partition that column for year or month granularity??

[@TechCoach]

Good question, there are multiple ways to do it. You can look at range or interval partitioning. If you want to use list partitioning, you can derive columns from timestamp column and use partitioning on the derived column. Please let me know if it helps.

[@sukritsen9530]

Thank You Tech Coach for your prompt response, I have gone through the videos and concepts of Partitioning, Materialised Views and Indexes. I am confused as to which one has to be used in which situation if we have a billion records. All of them seem to be inter-usable. Can you please explain the conditions in which each has to be used?

[@TechCoach]

Sukrit there are 2 basic things we should understand. If we want to access a major chunk of data frequently from a big table like more than 10% then partitioning will help. ( partition pruning is the reason). If you want to access very minor chunk from a table ( indexing will help). Now you can leverage indexing and partitioning together to have high performance systems. For example, a telecom company would access monthly data frequently to generate bills. So it makes more sense to partition on month column. In extension to that you might want to query individual customer transactions frequently for a month. So you can create an index on top of partitioning. I hope this clarifies your doubts.

[@TechCoach]

If I have answered your question to your satisfaction.kindly consider subscribing to my new channel. Your support is my motivation. kzbin.info/www/bejne/gKDFgIGfi7x0b7M

[@sukritsen9530]

Tech Coach 👍👍👍

[@KRaghavendar]

Can you provide access to these ppts and share the scripts too please.

[@sanjaykrkundu]

how can i partition based on a uuid in oracle?

[@AnIndianAbroadd]

What kind of partitioning you want sanjay, I am guessing you want to divide the data in equal partitions using the key. I would recommend hash partitoning for that.

[@sanjaykrkundu]

@@AnIndianAbroadd thanks :) i'm looking for something like this. data => 123e4567-e89b-12d3-a456-426655440000 | col1 | col2 .... 00112233-4455-6677-8899-aabbccddeeff | aaa | zzz... 123e4567-e89b-12d3-a456-426655440000 | abc | yyy .... 00112233-4455-6677-8899-aabbccddeeff | acn | zyc ... i want partition as below. partition 1 --------------------------------------------- 123e4567-e89b-12d3-a456-426655440000 | col1 | col2 .... 123e4567-e89b-12d3-a456-426655440000 | abc | yyy .... partition 2 -------------------------------------------- 00112233-4455-6677-8899-aabbccddeeff | acn | zyc ... 00112233-4455-6677-8899-aabbccddeeff | aaa | zzz... 123e4567-e89b-12d3-a456-426655440000 and 123e4567-e89b-12d3-a456-426655440000 are dynamic so i cant use list.. will hash partitioning be the best choice?

[@user-ib4lf5yn6n]

I have question which I will post on Monday

[@TechCoach]

Sure Please take your time. I have a small request , I am working on this new youtube channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@MrAchesh]

Did the Monday come?

[@shrijeetshivdekar9076]

When we define by region, clear differentiation through segments If we have to retrieve days for a emea, it scans segment of region, full partition scan done rather than full table scan This simpler handling of huge amount of data Transaction table for billions of rows:

[@shrijeetshivdekar9076]

When we want rolling 3 years of data, we care only about data of last 3 years, and we don't care about days before it Deleting billions of rows will take forever 2nd option :partitioning based on year! This deleting is simpler

[@shrijeetshivdekar9076]

Partitioning allows tables to be subdivided into smaller pieces, we get better granularity

[@shrijeetshivdekar9076]

January February match April and may Only new data from may is editable We cannot go back in time and edit For previous months, data is read only This partitioning on basis of month will make our job very easy. We No need to backup pe restore for previous months! This reduces time by a huge margin. The backup is going to take very less time. This better manageability of data

[@shrijeetshivdekar9076]

Na partition unavailable If non partitioned, then we would have not been able to work However, because it is available, thus we can work despite power failure

[@shrijeetshivdekar9076]

Fir database larger than terabytes, we need partitions.

[@karmetpsg1986]

content is good ...but too much of ad's :(

[@chaithanyamannem5040]

May be minimize the duration of vidoes

[@TechCoach]

Sure Chaithanya , Thanks for the feedback.

[@user-ib4lf5yn6n]

how to handle ORA-14074 ? in package

[@TechCoach]

Can you please post the exact code with the issue?

[@syedanoorumar1419]

Excellent

[@TechCoach]

Thanks a lot for the kind words :)