AnDOUC TechCast 2023-11-02 New Features in Oracle Database 23C
- December 15, 2023
- Posted by: MainInstructor
- Category: C Go Oracle Database SQL
Video Title: AnDOUC TechCast 2023-11-02 New Features in Oracle Database 23C
Okay well hi Kathy and Gerald thanks for joining me today I’m looking forward to hearing what you got to share we’ll just give it a minute or two I can see a whole lot of participants arriving and we’ll give a give them a minute or so to
For the trickle to slow down and then we’ll uh we’ll get moving um just as people are joining um it’s worth pointing out that there are some useful links um that will be posted by Carrie are amazing facilitator into chat that people can use um there will also be links uh to
How you can join our community in terms of joining the slack Channel and things like that so uh those things will all go into the into the chat all right wow it’s um looks like it’s going to be a festive time today all right let’s go ahead and make
A start so I’m joined by Kathy and Gerald we’re going to be talking about what’s new in 23c um and uh in the meantime we’ve just got a little bit of housekeeping that I need to do up front can we go to the next slide please so there are some helpful links
These are what I mentioned will be popped into the um the chat screen while we’re talking about this just to make uh people aware if you have a question which I hope you do for for Gerald and for Cathy then please do put them in the
Q&A area do not put them in the chat area I’ll be monitoring the Q&A and uh we’ll collect them up and do them at the end unless one of the speakers while they’re not speaking has a chance to answer them in the chat uh of in the Q&A chat um next
Please so um this Tech cast is is one of many we have a lovely Archive of very interesting topics um in on the website you can go to our.org Tech casts um and what we’re also interested in seeing is um abstracts from your good selves anybody who’s got interest in information to
Share with the community um in respect of Oracle database analytics graph um spatial we really want to have a look at them and uh please do submit them you can also get to that through this page just note what’s coming up we’ve got uh on November the 15th we’ve got uh graph
Analytics for SQL developers the new match Clause um with Abby Charles hag and Jim chinsky two really fabulous speakers um we’ve had um recently our favorite features of OAC um which um was done uh on October 19th you’ll find those in the archives so as you can see from the
Slide it’s it’s a very active Community it’s a place where you get nonstandard technical knowledge anyway next slide so we’ve got merchandise it’s again this was all designed by our own Abby Jes havy and um there’s some great merchandise it’s mainly available in the US so uh otherwise just come over and next
Slide um we’ll we’ll see you at The Summit in in next year which I’ll talk about a little bit in a minute but um sorry it’s my fault Kathy no no sorry no no you’re doing fine um you’re meant to know these thoughts by osmosis you know
That we have a lot of ways of connecting um if you uh the website um we’ve already talked about the slack channel has become very active and there’s lots of people including Oracle product management who are on there if you have a question or or um want to be part of a
An active Community slack as a great way of communicating with us um we’ve got obviously the YouTube channel which also has archives of all of these recordings this today’s session will be recorded um obviously our our Twitter sign at analytics and data um Facebook and Linkedin so um we do have a separate
Sort of subgroup for spatial and graph um please note these these uh links will all be pasted into the um into the chat if they haven’t already been next slide so this is what I alluded to just now and um we are um we are meeting in
Person again from April the 9th to the 11th and that’s at Redwood Shores in that um 300 building 300 area we would really love to see you in person um we’ve got uh a massive amount of abstracts that have been submitted which we’re now evaluating um some really incredible um
Presentations it looks like people have prepared for this um but there’s nothing quite like being in person and um it’s a very focused few days lots of knowledge sharing um we have lots of Oracle product management especially because of the location it’s right next to uh you
Know it’s in Redwood Shores itself right there yeah right there yes in the picture behind Gerald so yeah all we we we really do have access to a great bunch of people and um you get to rub shoulders with all those people that are sort of just in your imagination a lot
Of the year so right next slide yep right so it’s your agenda I just reminds me to properly introduced Kathy and Gerald and um I’ll start with Kathy because it’s ladies first she’s the customer she’s more important sorry Geral um so Kathy’s business intelligence program manager rosenon and she has 25 years of working experience with Oracle business intelligence and analytics Technologies so she is uh she’s part of the furniture you know she’s been around that long but I I wanted to introduce you to to her in a
Slightly different way uh we have a fun fact about Kathy and I think it’s quite lovely I’ll leave her to talk more about it when she starts the presentation but she was in uh fairly poor Straits when she first got married and a 10-year-old cousin um overheard is saying that they didn’t
Have much money and she he contributed and gave her some Christmas decorations and that’s now become an annual tradition for her and her family uh where they make uh Christmas ornaments I leave that to her to go to expand on and then Gerald who insisted that he had no
Interesting facts about him is actually a very interesting man um but first of all he’s a senior director product management for Oracle um and he’s come for a a very technical background and still has a very technical mind but he’s also um really uh advocating on how to
Build systems that provide Flex ability to meet users needs um he’s focusing on improving the developer experience of the Oracle database and the Oracle Cloud so um and he’s also very instrumental in um helping Drive the community so um uh he’s he’s actually for me the top fun
Fact is that he’s a Star Trek afficionado he knows anything and everything about Star Trek so when you come to question time I expect at least one question about Star Trek and the other thing is that he bakes bread and this sort of started uh three years ago and he has a
Three-year-old starter souro um kit to which he bakes bread from so that is our presenters today oh and I I neglected to say who I was although hopefully most of you do know I’m Roger cresy I’m uh representing uh braner in the USA now as an associate director but my love and my
Passion is for this community um analytics and data Oracle user Community work with a bunch of incredible people including Carrie who’s facilitating this you don’t get to see her on picture because she’s too shy but with that I’m gonna hand over to you two H Kathy and
Gerald thanks uh for joining us and thanks for sharing today I appreciate it thank thank you very much uh let’s go ahead and I want to go ahead and get started with the agenda that we have and basically what we’re going to be going over today first off you’re coming from
Me when when I’m you’re getting it from both the user Community uh as well as from product management which which I hopefully you’ll find that is a good mix I actually went to I think one of Gerald’s presentations at global leaders about the 23 new features the new
Features in the database 23 um and I got excited about it I thought okay I’m going to take this back to my team and I back in the summer did a presentation internally to my team on some of the new features I went and researched them so I
Got really excited about it um and offered to then do a presentation as well a tech cast and Gerald volunteered uh to help me with it so we’re going to be going over this we’re going to be talking about some more key features that we have we’ll go over some analytic
Features as well and then just some what I think are important for the everyday person using uh when they’re they’re using um SQL the everyday the databases some of the nice to knows and some of the time Savers that you may see coming out in the um 23 C release also the last
On the last slide that we’ll have is some information on how to actually download a free copy of the Oracle database 23 see for developers it’s something that you can go out in and actually test some of these key features that are coming out this is also kind of
Hand inand with another presentation that we’re doing on the 15th that’s going to talk about more the operational property graphs that aby’s doing uh that Roger mentioned earlier so if you want to go more into the property graphs and things like that Abby will be doing that
Later uh in a couple of weeks okay with that I will turn it over to Gerald okay cool thank you Kathy so yeah hopefully we can make this interactive between Kathy and me uh put some fun in there as well so as Kathy said we’re going to go
Through some of these Marquee features but we deliberately did not cover property graphs because in detail because you know Abby will be talking about that so instead we’re trying to give you a well-rounded idea of just what’s in 23c and the various different area so that when you see customers or
You have um any of these projects yourself you go like a GE If I Only Could do this you will find that as well so let’s Jump Right In we don’t have too much time uh I’ll start with Jason relational Duality View and let’s just
Hop to the next slide Kathy this will be a quick walk through this is something this is probably one it’s definitely one of the three more key features of 23c it might be actually the more key feature of 23c before we knew that the world is
Going AI uh but what we’re trying to do here is that we allow users to specify by business objects if you like in the database and these business objects being backed by Jason so specifically every database obviously has relational tables every database these days can do
Many things like Jason XML and so forth as well but the relational model is still the general purpose model right the cult gave us and has a lot of benefits like all the other data models have a lot of benefits as well but fast forward to the 2020s every application
Wants to consume Chason structures wants to send Jason structures so we have always this impedance mismatch of mapping Jason to tables and the said like the databases started to add Jason support in the database so storing Chason directly but what Chason relational Duality does this gives you
This new concept of fuse that allow you to have the database from relational tables that you see on the left hand side generate Jason directly and if you send Jason to the database it will go through the Jason and basically transforms it back into those tables and
Rows so let’s just see how this looks like if we go to the next slide here we will have the syntax so um there’s a new construct a new database uh object called a Jason relational Duality view or Jason Duality view you see here on the top left it says create Chason
Duality View and that view itself contains the structure of essentially the Chason document that you want to deal with uh so you see here on the right hand side we have the student schedule right have student Jill and her major and the schedule for the classes
And you see on the left hand side this is graph ql syntax that reflects that uh structure that you have on the right hand side uh now if you go to the next slide we’ll see what actually happens here it’s like you see these uh names
That are here in Orange those are the actual relational tables that we’re referring to so we have a a structure that starts archical structure it starts with the student table and then traverses down to students courses courses teachers ET and there’s a lot of little functionality that you can do there we
Have like a teacher at the bottom there you see like an at un Nest so that basically means don’t create a sub object of that and the Jon but pull out the teacher name into the higher level and many more little things that we haven’t plased on the slides but that
Allow you control including um modification control so updatability delet ability and so forth if we go to the next slide here you will see that what you have in each of these hierarchy ke are the actual column name so you know we started with a student
That’s the student table and then we say okay we want a student attribute and now Json and then colon the column name in the table is actually sdu ID so if the column was the same that you wanted in the Json as in the in the database then
You wouldn’t need this colon mapping then you would just say student ID for example uh but if the names are different in the table then what you would expect this attribute in the Jason use this column ation to rename them so you see here uh we mapped all these SD
Uid s name major uh to student name at Major and technically for major we wouldn’t need it because it’s actually the very same name in the column as well as in the Jason uh structure that we want to have if we go to the next slide here uh the important thing about Jason
Duality views is that they can be retrieved and modified via well non document API so I said before like every application wants to use rest uh well I said every application wants to use Chas and documents consume Chas and documents s Chason documents and what’s usually
Happening is like all of this goes via rest so HTTP calls you know put gets Etc so like an application just wants to send off an HP called the payload is the Jason document and that’s really the essence of what these applications try to do keep everything stateless everything organized in these
Hierarchical format so for Jason Duality used to be also um attractive to these applications and style of communication it’s imperative to have such a document API as well and so this is exactly what we’re doing we actually allow you to have these rest API calls to the
Database directly oh sorry if you just go one once back Kathy to slide before yeah no so you see here on the left hand side you have a get call to the student schedule which will be the Chase and relational Duality View and then basically use what’s known as query by
Example to retrieve that but on top of that we of course give you SQL access so you can’t just select them by SQL as well for the more traditional applications or you know for the users the end users and you can also use it by
The mongod the B API now if we go to the next slide which we alluded to so getting the documents is simple that’s actually something you could do for a long time even by just using SQL Json to generate Json out of of SQL where a magic really happens is when you update
These documents so sending them back and this is where kind of like you know other databases basically say just store the Chason as is or you know the application has to deal with that but what we do is actually we also tell you okay you can either you SQL among V API
Orr rest to send us this Chason document because the database has the mapping this Chason Duality view the database will look through this Chason document and say okay uh I have to update the students table I have to insert the new row in the in the course table and so
Forth and the important thing here is like you the the simplest approach of doing that would be like we just update everything everything gets locked and you just write everything from scratch new that’s sort of like what the kind of uh you know simplistic databases would
Do there Oracle does not do that we actually detect the changes on Cell level so literally on attribute per row uh and also of course throughout the different tables and then only update these particular parts of the document uh and you get this with full asset consistency you get this with full
Durability and you get this with this performance of what any other SQL update would look like or insert would look like in an Oracle database rather than having to rewrite the whole structure so this is super super powerful and that’s sort of a secret source that neither ORS
Can give you or document stores can give you uh and then if you go to the next slide see sorry there we go yeah not on words so just to round this up it’s like this has been extensively covered by the way in oneway as Keynote so if you want
To hear more about that just rewatch that from cloud world one Louis’s keynote but the important thing about Chason Duality views is that they these Chas and documents that are generated that come out of the Oracle database because it is an Oracle database they can include any data so not only just
You know straightforward relational columns they can have spatial data in them they can have graph data in them anything you can store in oral database you can essentially also expose VI this uh this Jason relational Duality views into Json documents and the important thing here is that unlike the
Traditional document stores that just store Jason after Json after Json you see here in the middle we have two times the schedule for math 2011 right so they look duplicated so we have a student schedule for chill we have a student schedule for Lucas so it looks like okay
We store the data twice and in a document store even if you were to store this Jason an Oracle database natively which Oracle can do as well that data would be duplicated so if you ever had to change you know the teacher for the math class you would have to go through
Every document and update that but if you use Chason relational Duality views because it’s backed by relational tables that data is actually only stored once right in a fully normalized version and if you were to change the teacher you only would have to do an update on one
Schedule row but for the documents they get the information right away and again full asset and full consistency so huge benefit for any app over any kind of uh document store if we hop to the next slide so just to round up uh the other important thing that people tend to to
Forget it’s like we always talk about one Jason Duality view but you can have many Jason Duality views on the same data right it’s like if you think microservices or different applications students want to have their student schedule a teacher also want to have his or her schedule but they probably don’t
Care much about the students they care more about which class to give and when right and then there’s maybe a curriculum team that actually schedules these classes so all of these want to have their structures without necessarily having all the information so you can have multiple Jason Duality
Views on the same table they really become like business objects on the same tables uh and again they never duplicate data and they always fully consistent because they’re backed on the same relational tables okay next slide please so that was Chase relational Duality View and I said if you want to
Know more about it you can hit me up later on and Phil ask whether the rest API reside in ORS we do use ORS for that so that’s the Gateway through uh um talking to the database but actually the rest API automatically generated so you
Don’t have to go in ORS and uh create them yourself they actually aut to registered with our AES capabilities switching GE slightly we still stay on Jason we’re talking now uh about Jason schema so we’re going now into the native Jason storage I have a Jason document I would like to store this
Jason in the Oracle database or do something with it um Jason came out of the you know we want flexible schema schema less Etc all this good stuff that we talked about 10 years ago or the industry as a whole right basically it’s like oh we want no schema flexible
Schema we just want to be able to store something well fast forward 10 years later probably everybody you on this call it’s like is like me I get us was bound to happen people go like well it would actually be nice to have some sort
Of a schema or know what Jason is in there not just arbitrary stuff uh and so the need for Jason schema came in Jason schema is a well-known construct is literally you can Google Jason schema I think it’s Chason schema.org uh that we have a aded so what that allows you to
Do is allows you to defin Define a schema of your Chason structure in Json very similar to XML without all the actual overhead the nice thing about Oracle database is we give you a couple of different ways how to validate these schemas so you can either on the left
Hand side what you see there put the schema right on the on the Jason data type column that you would want it to be validated against so you store the schema essentially on the column it becomes somewhat like of a check constraint you think like similar as you
Would have any kind of check constraint they not null constraint or you could also do this at runtime you see in the middle here can actually select from a table which happens to have a Jason in this column called doc uh and you will tell the database okay it’s Jason to
Make sure that it’s well from Jason and then validate this new validate keyword with the Jason schema inside so you can do Jason schema validation at runtime as well then on the right hand you see we also have a new package called dbms Jason schema that among other things
Allow you to give a validation report or get the validation report so here we just say validate report the column is St that’s the you know where the Chason data resides the schema is another in this case a column where we stored the schema so you get this flexibility this
Could be a bind variable this could be just a l string literal or you could store the schema in any table anywhere else as you like and then the report will tell you hey actually you know the validity of this document is not given valid is false there’s an error and in
This case it says hey actually your ID attribute was supposed to be a number but actually what you gave us was a string so super powerful and lot flexibility there for you we didn’t kind of do the same thing that we did in the good old XML DB days where we put this
Uh repository in place rather we give you this capability at runtime and in any kind of way you want to want to validate if we go to the next slide another Marquee feature that has comeing into Oracle our domains so some people call them SQL domains because we
Have domain names and service names Etc but domains are a well-known construct in a SQL standard for a long time but they’re actually very limited from what’s in there so domain in the SQL standard today what it kind of gives you like a named check constraint that you
Could then apply across multiple tables but the idea of domains is very simple it’s like you know databases have data types so I say we have a watcher we have a date we have a number we have a time stamp what have you um but a database actually doesn’t really know what the
Data means so it’s like if you have a credit card number or a social security number uh or a zip code these are all stored as just number right the database tells you well this is a number right I know that but it doesn’t know that it’s
A zip code or a credit card number or a social security number all of these have characteristics in their own right zip codes can be only four five digits long if you go for the uh uh main sip codes right and they have all special meanings what the digits actually mean as well
And so forth or same with phone numbers and so forth um domains allow you to store and enfor for that knowledge in the database so it gives you this new type called create domain give it a name can tell out which data type it has to
Be and then allows you to Define certain check constraints or certain conditions on top of that so if you look in the bottom of the of these two white bubbles you see in this case we create a domain for email which is a watch 2 it has to
Be not null if you use this domain you know there has to be an email or string that you provide and then email has a very well-known semantic it has this at symbol in the middle has actually a lot of other things like it needs a top
Level domain at the end and so forth but let’s just focus it needs this add symbol right if you don’t have this add sign in there it cannot be a valid email address and in this case we say okay we enforce that we put this this domain
Knowledge on top as a check constraint in this case and then there’s a couple of other things that you may want to say how what do you want to do with this data so for example if you want to display that domain maybe you don’t want to show anything that’s before the ad
Sign right and if you order that by that domain uh you want to probably order by the domain sorry yeah order by that domain if you want to order the email addresses you want to probably order by the email domain name first right so that you have everybody from a company
Name together like everybody from Target is under Target everybody from AT&T is AT&T and not just order by first name right and kind of get it completely random so that is exactly what we do here on the bottom and if you go to the next slide what that allows you to do is
You can then create tables and you can use these domains so you see here in red we have a customer table and we have actually two email addresses right we have a contact email and we have an invoice email could be the same doesn’t have to be now there’s two ways of how
You can define a domain if you kind of like just trust the domain definition you know this is what the data should look like somebody put this domain in place you could do what we see in the bottom here the invoice email space email the domain essentially the domain
Name becomes a short end for the data type definition alongside with all the constraints and other stuff but if you say Hey you know like uh I would like to have my email address enforced I need this add sign you know whatever the business rules are for the data but I
Don’t actually agree with the data type definition with Watcher 2255 that may actually not be so long you may actually have a longer email address then you can add that or overwrite that if you like uh with the definition that you have here on top and contact email where you
Say Watcher to 1,000 so actually would like to use 1,000 and apply the domain email so what will happen in this case is the database will enforce that the data type is correct you cannot give us a number you cannot give us a date because neither of those can contain an
Ad symbol but it’s like whether an email is only 10 characters long or 4,000 characters long actually does not matter for the semantics of whether it’s a valid email address or not so in this case here we overwrite it so it’s it gives you the flexibility basically for
Somebody who goes like G we thought that you know emails will never belong as X or you know whatever it may be and you guys again notice better than than many of people out there it’s like as things go on you go like oh we got that wrong
You can actually use this to basically okay make this wider Etc and then yeah at the bottom here we see if we were to insert just random strings right no add symbol so we have a two times ABC here we will basically get this check constraint violation that comes from the
Domain uh so that’s super powerful there’s a lot of things that come in domains and that you can do very smart things of also it it allows you to actually query the data dictionary and ask okay which of my columns in my entire scheme of a thousand tables
Perhaps are actually email addresses or social security numbers or zip codes and now it starts to become really interesting for analytics as well but yeah so it’s a long topic we could talk a whole hour on that let’s go to the next slide super powerful concept
Though let’s see what we got on the next one there we go oh yeah we got the domain display there yeah this was just an example of we said before the display in order so let’s just go to the next slide and um to save some time uh so you
Know I mentioned once keynote so we we put it in here again so the future of data and APPA one loisa at Cloud World about a month and a half ago so it talks about Chason relational Duality talks about AI talks about property graphs and
A lot more things so if you want to get a high level view of where we’re going and you haven’t seen this yet definitely check out the keynote um set it’s called the future of data and appd let’s go to the next slide one other aspect we say
We touch very briefly on as we said we have Abby doing a deep di on that it’s also my key feature though is the operational property graphs right it’s like actually having property graphs in the database and notice this perfect you see Cathy and I rehearsed this so it’s
Like perfect C stay on the next slide so it’s like everybody of you probably knows property graphs right or it’s graph concept so what it allows you to do is essentially this right uh you can uh write a lot less SQL to it to accomplish the same output so what we
Have here is like you know you want to find indirect uh bank account transfers from source to destination right so with middleman in between you could do this in SQL for a long long time but it’s like it looks like what you see on the
Right hand side a lot of SQL right it’s like you could do this in a graph fashion with graph queries well it’s four lines so super super super powerful as well but again we have more to come there the graph thing is really something to watch out for and I know
That this user group you will he a lot more about SQL property graph queries so let’s go to the next slide and I think this is yes we’re hand it over to Kathy so there we go so I’m just going to go over a couple of analytic functions that are out there uh
Definitely there’s more so be sure and and go out and check it and I’ll I’ll go over these fairly quickly because I can see where we are on time and make sure we get through this get through everything else but one of the things we have is the aggregation over the
Interval data type I don’t know if you guys are familiar with the interval data you can go from day to second or you can go from for the time or from from dates you can go from year to months interval type um which is a a
Data type and a table and before you could always do the Max and the men on that but if you try to do an aggregate or a sum uh an interval data type was produced the arrow so now all this is saying is right here you can now do this
And this is just all this little sections here is we’re doing the average duration and you can see over here average duration is 9 hours and 15 seconds for all four of those so it is coming out it’s giving us what we want and you’re not getting errors you could
Do the same with the sum so just know that that’s working for the anybody out there using that data type and was running into issues that’s one thing that’s fixed and then this is something that I had not researched much in the past but I did do some research over the weekend
I was was kind of playing with it working with with my daughter who’s um taking some analytic classes in college so we started looking at this string matching SQL function so you have your Fanatic in code which basically takes code um takes takes a code um algorithms
And puts builds the code based on how it sounds uh and then it it gives you you can have it give from a maximum from one to 12 codes it’ll give it’ll go as few as it needs to go but you can also say hey I don’t want to go past five codes
And if it does it will cut it off at that point um and then you have your fuzzy match and that’s where you’re comparing using different algorithms to compare one text string to next and it may be come as a number like uh from a number of how many changes is going to
Be to make them correct or more like into a percentage of of how close is it to being correct um so let’s start with the phonetic en code here’s just an example of it you can see here what I wanted to show is you have the double
Meta and the and the double metaphone alt so if there is an ALT text for it it will provide that alt text if not it will just provide it will put what’s in the um double metaphone and then you can see here I have one that’s 12 length 12 in
Length and one that’s three in link so what I did was um you can see here for night and night while they’re spelled differently they mean different things they phonetically sound the same so you can see that in two codes int that’s to bringing back for night for Peter Pan if
You see here I have 12 in the first two and coming back with with giving me five codes for Peter Pan because they’re thinking that’s that’s that’s the best to do it but if I if I go and say three it’s only going to bring me three back
Here so it’s G to say phonetically but you said no more than three so we’re going to Max it out at three even though five may be better may be more descriptive and then the Hulk is one that kind of brings up and says hey
Um just know we have a alt as well so it’s T tlk versus olk so th those are um just kind of showing the different things and Barbie thr in there because my granddaughter was there and goes well why can’t we do Barbie so that one’s just in there to make her happy
Okay then when we’re talking about matching with the fuzzy match I included this in here I’m not going to go through all of these because I think this could be a session within itself understanding these different things but just know that there are different algorithms to check one um text to
Another and if you see here um this is a select statement that that that we did and it’s just kind of comparing these different text strings um and have things from the percentage of how’s the longest string that that that they have in common the longest common string
Here it’s sitting at I would say kind of like at 44 which is kind of roughly 44% of that is the length of that’s correct or you also have things like how much how many many what’s how many steps is it going to take to make this change and
For like this kitten versus sitting you just all you have to do is make one change change the S to K and between these two you’d have to make the 347 to 170 there’s three changes there and then as you get to the bottom we have an
Extra eye in oil over here so that your four changes so you can kind of see how you can pull this and get this information when you’re matching strings using the different algorithms and like I said we could this could be a separate class within itself a separate session
Within itself kind of going through all of this with that we’ll get on to some nice to knows and Time Savers and I’ll turn this over to back over to Gerald all right thank you so yeah nice to know so a couple of things that have come to 23c
That uh are probably nice to know is like oh gee yeah great we can do this finally so let’s Jump Right In if we go to the next slide here schema level privileges um my security guys and I always kind of argue whether it’s a security feature or an aptive feature I
Mean it’s in here so I claim it’s an aptive feature but really what it allows you to do is specifying privileges on schema level so before 23c what you had if you wanted to Grant access to your data you would have one of two choices
That you see there on the left right you either do Grant select any table to the user HR and therefore the user HR could access any table that’s in a database or you could say Grant select on these uh five tables that we have in here in
Production to HR uh the trouble with that was always like well you never really want to Grant select any table because again it’s any table in the database um but if you know your production schema were to have uh a new table added you would have to go back
And say okay which users have access to the schema read access to the schema okay and now do Grant select on this new table to that schema now you could already for many many years just use rols to do that right that would sort of
Be the correct way if you were to ship an application you would have a role that would have the three privileges on the schema uh but more often not it would just be nice to be able to actually say especially when it’s not an application that you package up or
Whatever to say hey look these analytics guys need read access to that schema okay and that schema is maybe maintained by a couple of different teams right not there’s not one team or whatever or one application in place so I just want to make sure that I can say Hey you know
For any table in the schema the analytics guys or let’s say the HR guys here have access to that have read access to these tables and you see you have now this new way of saying GR select any table on schema production to HR and therefore whenever you add
Another table to the schema production HR will automatically have select privileges on that schema so nice to know little thing it’s like it makes especially also up there for lot simpler because you don’t basically have to worry about maintaining a role updating a role having forgotten to update a role
Or you know like uh having to go back and see hey which users actually should have we access to the table so so if we go to the next slide another nice little thing this is actually something that’s in a SQL standard for quite a while so I was like
Why do we not have that it’s the capability to insert multiple rows in one insert statement so what you see here on the left hand side you have inserted into bookings and then you have a values clause and then you have actually three parenthesis items with a
Comma at the end and that will insert three different rows for you in one go rather than having to do insert into bookings now for this example it doesn’t look that cumbersome but I’m sure many of you have SE have had tables with like 200 columns whatever and copy pasting
Just always this insert into statement all to this entirety again just to add another row can be quite cumbersome so this is actually called table value Constructor because in the SQL standard the values clause in itself is an operator it’s called the value table a table value Constructor and so you can
Use this also to generate rows so you see this on the right hand side so you can actually say select star from values and you say hey give me quickly three rows right one two three with James Scott James Chan and then you have to obviously tell us what what should we
Name these three these two columns of these three rows so this is where you have afterwards this parenthesis EMP and first name and then boom here you have your data generated super handy I know you can do a connect by for many many years but this would actually be the NC
SQL or ISO SQL equivalent of generating rows so nice to have if we are nice to know if we go to the next slide there’s a couple of other ways how you could use this so again you could also use this in a with clause for example
For recursive queries and the one thing is we will actually tell you when this value Clause has been used so under the covers this is something that basically just executes a select Union all sort of the same code path the same C code is used but we still didn’t want to confuse
You guys by kind of clustering their Union all in the explain plan so therefore you have this new execution plan operation called value scan and also you will see on the botom then of what has actually been used right we have three tupal with three elements in
This case here and that tells you hey you know this values Clause has been used and that select statement we go to the next slide The annotation so this is something that um I’m very fond of personally so an annotation is essentially what you can do now is you
Can annotate your data model with these annotations and this is just a SQL syntax to use if we go straight to the next slide I think this is a little bit more clearer or get gets clearer so like for any object that you have in a database of for almost any object that
You have in a database certainly like tables and fuse indexes Etc you can specify annotations and these annotations are key value pairs with an optional value they’re just strings it’s literally like basically Park the metadata next to the data so have a table customers here and then the
Application or you as the user or DBA or all three of you can decide okay in this case I have three annotations there okay there’s a sensitivity well you know have like low medium high so sensitivity space High departments which departments does this table belong to or should have
Access to or own it or whatever well department sales comma deliveries a comma separated list again it’s just a string this could be a Chas an object this could be anything else or often you actually don’t need necessarily a value for it so for example you say for whom
Is this for well for the front office well okay for the key itself is deterministic or is a unique in that case that that uh the application can look at me all the tables that are for front office they don’t need to necessarily have a value now if you go
To the next slide you can also repeat this on columns right so you can also say Hey you know look here we have in this employee table the ID we have annotations in there and for there as well the first thing is well this is an
It gets an annotation of identity I just need to know that this is an identity I don’t need any value for that so this is good enough for me to query or which are my columns and my entire schema of a thousand tables that are classified as
Identity you can do that now because you annotated it now s if you just stay on that slide for a little bit longer sorry and then no worries uh and then you know again you can do key value pairs so like display is a common thing right it’s
Like okay it’s called ID name salary but we want this actually as an employee ID employee name salary the application can start looking for these things and then interpret these things right and so you see also on the salary at the bottom you have another one that says UI hidden now
The important thing about annotations is it’s like so we had domains before right where you put the sematics into the database right and tell it exactly what to do or not um annotations are sort of like a parking lot for metadata for the user database does nothing with it right
They’re just key value pair strings whether the value is so key has to be a string a name basically an identifier but whether the value is a comma separated list ad Chason object or whatever else is completely up to you and the idea here is that users and
Applications start sharing a common set of annotations and they start interpreting these so that you can say you are hidden and any application that goes into that schema goes like hey you know this is an annotation on UI hidden so it will not display so this can become very very powerful by sharing
Metadata alongside with your data uh by storing it directly next to it okay if you up to next slide now to switch gears here bit uh Boolean data type um probably don’t have to say much more other than yes it’s finally here you can have Boolean now so all good next
Slide select without from something that is kind of a little nice to know time saer ET wouldn’t believe how many times people get a little bit like well I select syate from Dual from Dual from Dual there’s technically no reason that you would have to specify table if you
Just have an expression that evaluates in itself meaning that there’s no colum name from the table that you select from right so sstate is a function you have another function why have this from Dual there all the time uh now oddly enough in the SQL standard you do need the from
Clause but for type saving purposes we made the from duel optional and literally what will happen if we don’t find a from Clause we will just attach from duel that’s literally all we happen will happen so if you kind of were to have a comma there at the end and then a
Semicolon right it will give you a random Arrow saying hey there’s a missing expression Etc we will just attach from Dual and then run the query for you and whatever whether the query is valid or not that’s what what you get but it’s a little nice to know you know
Like especially when you type a lot of SQL ad hop next one yeah 4,096 columns so table can now have 4,096 columns uh if you wish to do so there’s a new parameter that guides this Max columns you will have to set it to Extended uh and of course for that
Parameter to be set your compatibility paramet compatibility parameter has to be set to 23c so that helps if you have Ultra wide tables denormalized tables Etc next Slide the developer role we have packed everything that an application developer needs uh to build an application into a new role called DB developer role that
Has been sanctioned by our security guys so it’s a complete secure role the idea here being like there’s way too many developers that could just grant me theba or you know the any privileges so that I can create a table Etc we had resource for a while but there’s a
Couple of other privileges that are involved or that as a developer you may want to use such as you know being able to run explain plans get your stats Etc so those are kind of some V dollar views that you need to select and and end uh
So all of that is packed into this TB developer role you can have a look in what’s actually in there but the idea here being if you have a new developer coming on board and it’s like hey I need privileges to do something with my schema create a table insert some rows
Etc that’s the one role that you grant them now so Grant theb developer role to the user and they’re good to go obviously never really Grant this in production because it’s a developer role all right next slide and then oh yeah so here’s a list
Of all the stuff that’s in there I said you can look in the documentation as well so let’s just skip this in interest of time this the other resources that we have so hop to the next slide and then the returning Clause so the returning
Clause got now old and new so by default or by default prior to 23c the returning Clause only gave you the new values back so you know you did an insert into an update whatever you got a new the new value back but actually sometimes it
Would be quite nice to also know what the change happened what the change was that happened because if you look at these update statement here on the bottom left hand side you know you kind of say Hey you know employees that live in Austria they all get like twice the
Salary right but you actually never knew what the actual salary was to begin with right it’s it’s like so the application to know to answer this question they would have to select all this first right give me all the salaries from employees in Austria right so that store
My old salaries and then do the update and then perhaps do another select you know salary from from all emploees in Austria to get the new salary and actually know the differences while now you no longer have to do this you can just execute your update as you always
Would and say returning old salary and new salary into bind variables and with this one operation and one network ground trip you will get the information back of what was the previous value and the new value right and that can be very very very helpful for applications that
Are for example curious in what was the Delta of the change right or what will the change look like if we were to go through there because we haven’t committed this either yet right we could always roll back okay next slide that’s where we hand it back to Kathy so take
Us home Kathy and so we just have a few that are more some of the time Savers although I will say some of the ones that Gerald mentioned are also I think times Savers as well so um so I’m gonna go by this fairly quickly because I know
I think we’re over on time a little bit but uh one of the things that I think is is helpful is you can now Group by uh Alias and position so uh we’re in the past when you did a group by and come talk about Alias Group by and having
Anytime you did that you’d have to put in group by and the long expression this one isn’t that long but just can imagine when you have you have longer column column calculations oops sorry longer column calculations and then when you do that and then you have um you have to change
Them and if you make a change to one you have to change it in three places now that’s the way we had to do it in the past now you can just use higher deer so put higher deer here higher deer there if you make a change here you only make
It one place and you don’t have something really long and in addition to that you can also like order by the column number the column position so if you have a column one sort by column one uh you can now Group by that column position as well so you could just do a
Group by Group by one here so that’s something that I think is going to be very helpful going forward again you can now do direct joins and updates and deletes so you can see here where we’re doing an update and we’re going to make this the employee salaries we’re going to make them
Employee salaries times two where the department is development so we’re having to pull from the dep Department table in the past you had to do a select cause where you would would do this and you say where select you know Department you know ID is in select Department you
Know the long long long select cause on the fa it’s harder to read this is I think much easier to read much easier to code so that’s helpful and it’s a very similar thing as well as to the delete you can delete from employees table um
If you have if you do from departments D where the department equals Department again on the where Clause we’re not having to put a long uh select statement in there um so this is these are again things I think are going to save us some time
When we’re doing our coding or at least save me time um if not exist uh you know before before you’d have to go check and see if it existed and put this if it doesn’t exist then you can go ahead and create the table now you can just put in the
Create table just put it if not exist in there which again I think this is going to save us some time very very it’s also nice to know but it’s also going to save us some time when we’re coding that we don’t have to check and see if if it exists before we
Create our drop tables okay and this one uh this is something that I wasn’t as familiar with because I always use the pipes I’m fairly I came in from a olap background so I started using SQL and everybody always said just do those double pipes to when you can
Catenate stuff and I thought really they don’t have you know didn’t know why we didn’t do can cenate just ose this function I didn’t realize that you had to Nest them before and this to me is something like oh wow like something simple so now we you used to have to
Could only concatenate two strings and then you have to create another concatenation to concatenate the two strings you just did get that nested you know that can get very lengthy now you can just do it it can c canat multiple strings so you can see here now
You can do that to me that’s really uh something that’s probably going to save time cre errors and not have to worry about counting your open and Clos prints um you can set a default on updat or insert statement so this is where I
Know I I would have used this a lot when we’re creating a field can’t be null and we may want to give it a value of a default value that’s going to be doesn’t exist or whatever for hierarchies or whatever you may want to do but before
You’d write the code in the ETL to do that when you loaded the data now you can just say here we have this description and the default this will be if you have an insert or update the default will be in this case we’ll make it banana you can
Do it for insert you can do it for update you can do it for both so you can see here you’re putting all values in there and there we go it’s going to come up and display as s okay so now this is the this is something that I want to challenge you
Guys with this is Oracle has a Oracle 23c free um database out there it’s not large it’s got 12 gigabytes of user data storage and two gigs of RAM is not meant for production it has a sample schema and tables in there so it’s ready to use
You can go in I will tell you I took the steps I downloaded the image I installed the image I follow the instructions and within an hour because they had the schema login everything there I was writing SQL statements in this new environment so it’s not a lot of time to
Invest in it but it gives you an opportunity to not only check these functions uh check what we’ve we’ve talked about today but also go in and look and see what else is out there with that I think done here’s some other free resources and we would open
It up for any questions that we have well I’m happy to say that we have no oh oh we’ve got one left since this is for developers it would be great if you used Oracle Linux Cloud it’s more of a comment oracal Linux Cloud developer image so that was from Phil
Canata so um I think we we are at at top but um Testament to the interest of your presentation the brunt of our attendees are still on and um I just want to thank you again it’s been very informative um 23c is going to be a real
Step up I mean well is a real step up and um thank you very much for presenting to us today Kathy and Gerald um we’ll see you on again soon I think yeah I think you’re right there are a number of different topics that could spawn separate presentations from within
This so um anyway thank you once again we’ll there’s one more question about the PDF sorry oh there we are yeah carry on yes the PDF will be uh put on the archive as well um the PDF and the recording will all be made available on the archive the recording will be made
Available not just on our techcast um uh area of the.org website but also on YouTube so uh yeah look out for that um you can get a get hold get your fingers on all of it so thank you very much everybody and thank you Kathy and Gerald appreciate
You coming very much thank you guys good rest of your day and week thank you bye bye
Video Keywords: Oracle Database, [vid_tags]
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99