List partitioning in Oracle : Part 3

This video explains list partitioning in detail with 2 real project examples.
I recommend watching these 2 videos on partitioning prior to watching the current one.
1. • Partitioning in Oracle...
2. • Partitioning in Oracle...
#Listpartitioning #Oraclepartitioning #TechCoach

[@TechCoach]

List of all 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

[@ladysay3]

Lovely presentation. I’m actually enjoying your videos. Thank you.

[@TechCoach]

I am glad I could help :)

[@SreeramReddyB]

In oracle 12c , there is option of ‘Automatic’ in partition by list i.e., for any new value for partition key a new partition will be created.

[@samirmomin203]

Slowly I am interesting in Partitioning due to your video series. Thanks a Lot, Vivek

[@TechCoach]

Thanks Samir, I am glad they are helping you out:)

[@khanaasif5606]

Hi Vivek, your videos on Table Partitioning helped me in my recent interview, thanks for the videos.

[@TechCoach]

Wow that's awesome aasif, I am glad I could help.

[@girishpaliwal6178]

awesome 100/100 Please keep coming with new vedio based on different topic of DB

[@aadhithanradhakrishnan6166]

Well explained , my sincere appreciation to your efforts 👏

[@TechCoach]

Thanks a lot buddy for your kind words :)

[@aadhithanradhakrishnan6166]

@@TechCoach i gained much knowledge from your videos, thumbs up for your efforts 👍

[@deepikaaggarwal9930]

Hi Vivek, Thanks for sharing the videos. These are really helpful to understand the concepts clearly. Using composite partitioning, can also be one of the possible solutions for the problem statement. Like if we use list-range partitioning, in that case, we can create 3 partitions for 3 countries and for each partition we can further create 14 sub-partitions.

[@TechCoach]

Hey Deepika, Yes we can further subpartition based on data and requirement. Though you want to be careful as creating way too many partitions will be difficult to maintain.

[@praveenkumarb5155]

Excellent explanation..very useful

[@TechCoach]

Thanks a lot buddy for the kind words, I have a small request I am working on this new youtube channel "An Indian Abroad" and would really appreciate if you watch and subscribe to it :) kzbin.info/www/bejne/p2XIiayFhdVggtU

[@truptimane1371]

Hi Vivek, I would like to have video on Automatic list and Multi-column list partition

[@TechCoach]

Sure Trupti it's fairly straightforward, the automatic keyword should do the job here. I will make a video on it soon

[@mprasad3661]

What If we use check constraint for region column, is that be good practice sir?

[@AnIndianAbroadd]

It is not a good practice, in future if you decide to add a new region you will have to perform a ddl operation to alter the column. I hope this clarifies. I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@ragalasaandeep4940]

I am Ganesh. Your explanations very good. Could you please make a one video for parsing , Adaptive cursor sharing and Sql profile.

[@rajasekhar5702]

what if i want to add another partition called 'SA' to the existing list partition?

[@shaliniguha1822]

Can we change the values for one partition later?

[@andishez5359]

Thank you so much again for your very useful video

[@TechCoach]

Thanks I am happy I could help:)

[@phanmemchamcongHRADEnterprise]

number of staff is dynamic. how create partition every staff

[@TechCoach]

You should not create partition on staff it will increase the number of objects significantly. I will create a video on dynamic list partitioning next week.

[@phanmemchamcongHRADEnterprise]

@@TechCoach cause my data is attendance checktime logs, insert, update or delete record on staff, reports usually calculate on each of staff

[@gvmahesh5499]

Thanks for the explanation!! How we can create partitions dynamically?

[@AbhishekKumar-yt9wk]

I have same question. By the way excellent explanation. Keep it up.

[@kallurufamily6465]

By using EXECUTE IMMEDIATE’ alter table..’ statement

[@ankursrivastava4164]

Hi Vivek..As you suggest if any list partition will drop global index will be unusable so what should we do to make it usable again ?

[@sandipjena]

Once again very useful video. But as I can see out of these 9 tutorial video, I think sub-partition is not covered. I haven't seen rest of the videos. It will be very helpful if you add all the topics related to partitioning. Thank you.

[@vipulbansal8022]

Hi Vivek, Thanks for these tutorials, these are very helpful. One request, if its ok, can you please share PPT of these videos, for quick revision.

[@TechCoach]

Hi Vipul, Thank you, Unfortunately I don't have the PPT handy at the moment.

[@vipulbansal8022]

@@TechCoach its ok, no problem, can u please share it later on.

[@ahmedgomaa5893]

what will happen if the partition key value got updated ? will the physical location of the updated rows get changed ?

[@TechCoach]

Yes it will move to the other partition.

[@sankaranarayanan138]

Sir one doubt, if I'm not using the partition key in my select Sql will degrade my query performance

[@TechCoach]

It will have to do a full table scan instead of a full partition scan unless you have created a global index, in which case the index will be accessed 1st.

[@ankursrivastava4164]

What is staging table and how it is different from normal table ? When we need to use staging table and advantage over it on normal table? Please advise Vivek?

[@TechCoach]

Usually staging area or landing area is where you load the data from source without many/any changes. Then you load your final tables from these stage table after the ETL process. I hope this helps Ankur

[@ankursrivastava4164]

@@TechCoach Thanks Vivek for your input.Your videos are very informative!!

[@sj9753]

Hi.Thanks for the video. I have a doubt regarding global index.you said after deleting partition entire global index will b unusable.If there are 4 partitions,i will drop 3 partitions.That time for the remaining 1 partition how the global index will behave?? Wil it b unusable ??

[@TechCoach]

I haven't tried this scenario personally , but I think it will still be unusable.

[@sj9753]

@@TechCoach thank u

[@shrijeetshivdekar9076]

List partitioning should be used only when the data to be inserted belongs to a specific key! Else you can use default to map unknown values When and why . Data above 2 gb . When predefined set of discrete values to arrange unorganized data in an organized way

[@TechCoach]

Great job shrijeet at summarizing videos :)

[@shrijeetshivdekar9076]

This saved significant amount of time, because only related partitions are used

[@dollyphilip20]

thank you,..

[@TechCoach]

Thanks for the comment. I am working on this new youtube channel and would really appreciate if you watch and subscribe to it :) kzbin.info/www/bejne/eWbTkoCsnN9nhNE

[@SreeramReddyB]

Good, but you need to speak a little faster.

[@komalpatel6960]

You can increase playback speed to 1.5x or 2x accordingly from settings options at the bottom of the video.

[@jagneethi]

who is watching in 2021?

[@TechCoach]

Never thought someone would post this comment on a tech video ;)

[@jagneethi]

@@TechCoach great vid btw!