Major Differences between Views and Materialized Views!. Views Views are the virtual projection of an output query or the dynamic view v iew of the data in a database that is presented to the user whenever requested. Materialized views on the other hand are a nonvirtual schema which is a common part of database warehousing, primarily used for summarizing, pre-computing, replicating and distributing data etc. Now having said that, lets move on to some basic differences d ifferences between Views Views and Materialized views in order to gain a better understanding.
Access to the data !he operations performed using Views directly affects affects the data in the base b ase table. !herefore they are subjected to the integrity constraints and triggers of the b ase table. Materialized views provide an indirect access to the data of the base table. !his is due to the fact that they create a separate schema to store the results of a query.
Storage space "ne of the common differences between views and materialized views is the way they ta#e up storage space. $ince Views are Views are the virtual projection of a query result hence they do not ta#e up any storage area. !his helps in reduction of memory usage and encourages the use of $hared $%&. 'hereas the schema created by Materialized by Materialized Views Views ta#e ta#e up some storage space as it is saved in either the same database as its base table or in a different database.
Usage of Views and Materialized Views (ifferences between views and materialized views can also be understood in terms of their usages. Views have Views have a more restrictive behavior towards a base table. ). *t could could be used to isolat isolatee an application application in order order to prohibit prohibit any any change in in base table table definition. +. *t can be be used in in order to to simplify simplify $%& $%& statements statements for for the user user.. . Views Views could be used used to enhance security security by restric restricting ting access access to a predetermine predetermined d set of columns and rows
istence of Materialized of Materialized Views Views is is transparent to the $%& ecept when used for query rewrites. ). %uery rewrit rewrites es are said said to improve improve the perform performance ance of $%& eecution eecution and are useful useful in in a data warehouse environment. +. /sers can can insert, insert, delete delete and update update the data by means means of updatabl updatablee materialize materialized d views. 0part from these differences between views and materialized views, some other important points to #eep in mind which further clears c lears the concept of views and materialized views are1 ). Views can Views can be based on each other in addition to operating on base tables. 0 view can 2"*N another view with a table using 3 45"/6 78 or /N*"N /N*"N clause. +. Materialized views can views can be defined on a base table, partitioned table or Views whereas indees are defined on Materialized on Materialized views. views. . /ltimately /ltimately a materia materialized lized view view log is a schema schema object object which which records records changes changes to a master master table9s data so that the materialized view defined on that master table can be refreshed incrementally.
Sysdate. $ysdate is a function in oracle database which returns system current date and time. $o lets move ahead and see how we can retrieve system current date and time using $ysdate function of "racle (atabase. 1. How to retrieve system current date using Sysdate function By default, Sysdate function returns system current date in oracle default date format which is DD-MON-RR
SELE! Sysdate "ROM dual#
2. How to retrieve current date and time using Sysdate function $sin% Sysdate function you can also retrie&e system current time alon% with the date. Let's see how
SELE! !O()*R+Sysdate, !O()*R+S ysdate, DDMON ))/01M21SS34 "ROM dual# 2n the a5o&e 6uery, in order to 7rint the system current time alon% with the date we used !O()*R+4 con&ersion function where we 7assed Sysdate as the 8rst 7arameter of the function and a date format. *s you can see in the date format, we ha&e e97licitly s7eci8ed that alon% with the date +DDMON4 we also want time of the system +))/01M21SS4. !his 6uery u7on e9ecution will %i&e you system current date alon% with the system current time.
3. How to retrieve system current time using Sysdate function 2t3s also 7ossi5le to 8nd out :ust the current time of your system usin% Sysdate function of oracle data5ase.
SELE! !O()*R+Sysdate, !O( )*R+Sysdate, ))/01M21SS34 "ROM dual# ;uery is 7retty similar to the 7re&ious one, the only di
COALESCE ( ). $imilar to NV& to NV& : ; and ; and NV&+ NV&+ : ; functions ; functions which we have seen in previous tutorial, <"0&$< : ; is also a function which will help you in handling null values stored. =owever unli#e NV& : ; and NV&+ : ; functions, <"0&$< : ; function does not restrict you with the number o f parameters you pass to the function.
, e97r/? e97rn4# Examples of COALESCE ( ) Function In Oracle Database
2n order to 5etter e97lain you the wor=in% of O*LESE + 4 function let3s start with some sim7le e9am7les. Example 1: SELE! O*LESE +null, >, /, @4 "ROM dual#
*s you can see in the a5o&e e9am7le we ha&e 7assed 0 7arameters for the O*LESE + 4 function from which the 8rst 7arameter is N$LL and as mentioned 5efore, a O*LESE + 4 function returns the 8rst Not Null3 &alue from the 7arameter list. !hus on e9ecution the result will 5e > which is the 8rst Not Null3 &alue in the ar%ument list. Example 2: SELE! O*LESE +'Re5ellion', 'R2DER', null4 "ROM dual#
2n this e9am7le 2 ha&e 7assed three 7arameters to the O*LESE + 4 function in which the 8rst two 7arameters are character strin% and the third 7arameter is a N$LL. On successful e9ecution this 6uery will return the 8rst Not Null3 &alue from
the ar%ument list, which is the strin% Re5ellion3 in our case. !he core 7ur7ose of a5o&e a5o&e two e9am7les e9am7les is to ma=e you understand understand )ow O*LESE O*LESE + 4 function wor=s. Now let3s ta=e a ste7 ahead and see a sli%htly more com7le9 yet easy to understand e9am7le. Example 3: "or the demonstration of this e9am7le 2 ha&e created a ta5le with the name *AENERS which has four columns1
>. "irst irst name name /. Last na name @. Mo5i Mo5ile le num5 num5er er 0. Landli ndline ne..
!a 5le1 *&en%ers
>or better understanding of the concepts * have also inserted some rows into this table. Now suppose you have to write a query or say ma#e a report to list the name of all the avengers along with their contact numbers. =owever what if * say that some of the avengers only have
mobile number or some only have landline number? *n this case the possible queries that may stri#e your mind will be1
SELE! 8rst(name, last(name, mo5ile "ROM a&en%ers# Or SELE! 8rst(name, last(name, landline "ROM a&en%ers# Both the 6ueries are correct 5ut the only 7ro5lem with them is that you will not 5e a5le to %et the contact of all those a&en%ers who don3t ha&e either mo5ile num5er 6uery> or landline num5er 6uery /. !here are two solutions to this problem. >irst is to write a query which will return the data from both landline and mobile columns.
SELE! 8rst(name, last(name, mo5ile, landline "ROM a&en%ers# !he second and more eCcient solution of this 7ro5lem is that you can use O*LESE function on the columns landline and mo5ile. !hat will ma=e your re7ort clearer. SELE! 8rst(name, last(name, O*LESE +mo5ile, landline4 *S contact "ROM a&en%ers# !hats all about unction *n "racle (atabae. =ope it helped you in understanding this concept better. 6lease share it with your friends on social networ#ing and help me reach out to more people. !han#s and have a great day@
NVL2. NV&+ is the second null function in the series of Null function. 'e can say its a null function li#e NV& but on steroids as NV&+ epands the functionality of NV& function. NV&+ function lets you substitute the values when a null value or a non-null value is encountered. $ynta NV&+ :pression, value), value+; /nli#e NV& null function NV&+ function has parameters. pression, value) an d value +. >irst parameter pression can either be column name or an arithmetic epression whose result will be evaluated, whereas the second parameter is the value which will be returned if the first parameter value is Not Null. >urthermore if the first parameter value is Null the NV&+ returns the third parameter.
*n $imple words if the data returned from first parameter is Not Null then va lue from the second parameter will be displayed otherwise value from third parameter will be returned.
Example
$&5"M employees '=5 salaryC)BBBD "n eecution this query will return values in two columns where first column will display the actual values from commission pct column and second will display the string Not Null where some actual values eist in commission pct otherwise it will display B in place of Null. Now if you want to display the actual value of commission pct column instead of Not Null string then you can simply write the column name which is commission pct at the place of this string. &et me show you how $&5"M employees '=5 salaryC)BBBD Now you have to comment on my 8ou!ube or tweet me on my twitter and tell me. 0ccording to you what are the applications or say uses of this particular function NV&+? 'here and how you can use this function? 0nd if you have a crazy query with this N/&& function NV&+ you can also comment that in the comment section below.
NVL(). 0s we all #now that unli#e other programming languages N/&& in oracle database does not signify zero or a blan# space rather it is a value that is unavailable, unassigned, un#nown, or inapplicable. !hus N/&& value can a problem and requires intensive handling for driving an accurate result. 6roblems@@@ 'hat #ind of problems? 4lad you as#ed. ). 5esult is always N/&& *f you are performing any arithmetic epression containing Null as an operand then oracle will evaluate that epression as null. 0s you can see here. +. =ard to distinguish !hough some graphic user tools such as $%& (eveloper will show you N/&& value stored in a column eplicitly but in case you are wor#ing on your database using command prompt, &inu terminal or $%&E6lus interface then in most of the cases these tools show nothing in place of null.
Now the question arises here is how to overcome these problems? !o handle N/&& in oracle database we have a few pre-defined functions which come as freebies in the pac#age. !hese functions are NV&, NV&+, N/&&*>, <"0&$< and many more. $o lets dig in and see what are these functions for and what are they capable of doing. d oing. 'e will start with the first function NV&.
NVL /sing NV& function you can substitute a value in the place of N/&& values. !he substituted value then temporarily replaces the N/&& values in your calculations or e pression. 5emember that the substituted value only replaces the N/&& value temporarily for the session and does not affect the value stored in the table. =ere is the synta of NV& function. NV& :ep, replacement-ep; 0s you can see NV& function ta#es two parameters ep an d replacement ep. >irst parameter ep can be a column name of a table or an arithmetic epression and the second parameter replacement epression will be the value which you want to substitute when a N/&& value is encountered. 0lways remember the data type of both the parameters must match otherwise the compiler will raise an error.
Example $&5"M employees '=5 salaryC)BBBD "n eecution all the null values va lues in the result set will get replaced by B. $imilarly we can use NV& null function while performing performing arithmetic epression. 0gain 0gain lets ta#e the same arithmetic epression which we used in the previous query where we added )BB to the values of commission pct column. $&5"M employees '=5 salaryC)BBBD
L*D R*D.
!he story isnt finished yet and * am bac# to complete it@@@ &ast year * had done some tutorials on $%& functions and this one is in the continuations of those. $o lets get started. started. !hese two widely used functions return an epression padded with special characters to a specific length. !he epression can be a value stored in a column or any user specific epression. LPAD
L*D stands for Left ad. !his function returns an e97ression 7added with the s7ecial character to the left side of that e97ression returned. RPAD
R*D stands for Ri%ht ad. R*D function returns an e97ression 7added with the s7ecial character to the ri%ht side of that e97ression returned. *7art from the name 5oth the functions share similar synta9. L*D +e97ressioncolumn name, len%th, 7addin%-e97ression4
*nd
R*D +e97ressioncolumn name, len%th, 7addin%-e97ression4 Expressioncolumn name! "irst in7ut is the e97ression which you want to 7ad. !his can 5e 5e any mathematics mathematics e97ression, e97ression, character character strin%s strin%s or a column column name. Len"t#! Len%th is the second 7arameter, usin% which you can set the ma9imum len%th of the return &alue as it is dis7layed on your screen. Pa$$in"%expression! !he last 7arameter is 7addin% e97ression which is a te9t e97ression that s7eci8es the 7addin% characters. !he default &alue of 7addin% e97ression is a sin%le 5lan=. )ere also as a 7addin% e97ression you can either s7ecify a s7ecial character such as asteris= +F4 si%n, )ash +G4 or dollar +H4 si%n etc. or a mathematics e97ression or for that matter strin%. But %enerally we use s7ecial characters.
Examples >. L*D and R*D with haracter Strin% as E97ression.
SELECT LPAD ('RebellionRider', 20, '#') FROM dual;
2n this 6uery Re5ellionRider is our e97ression which has a len%th of >0 characters, and hash si%n +G4 is our 7addin% character which will %et added to the left side of the strin% Re5ellionRider, and /I will 5e the total len%th of the strin%. By !otal 2 mean 7addin% e97ression J the e97ression which is a strin% i.e. Re5ellionRider in our case. Let3s try R*D function in the same 6uery SELECT RPAD ('RebellionRider',
20,
'#') FROM dual;
+. &60( and 560( with
)ere in this 6uery we are usin% &alues stored in salary column of em7loyees ta5le as the 8rst 7arameter of L*D function. Ke set the total len%th of the result strin% on /I and we are usin% asteris= si%n as our 7added character. Let3s e9ecute &ets eecute the same query with 560( function SELECT RPAD (alar!, 20, '"') FROM e$lo!ee;
ntersect and Mins *n the previous the previous tutorial we tutorial we learnt about /nion and /nion 0ll set operators, the differences between them and their usage. Now in this tutorial we will learn the remaining $et operators in in oracle database which includes *ntersect and Minus.
ntersect "et #perator !he *N!5$or this eample * will be using the same sa me tables from the previous tutorial that are ootball. $&5"M cric#et *N!5$5"M footballD "n eecution the resulting set will consists of only those rows that are common to both these tables.
Mins "et #perator.
!he M*N/$ operator returns rows in the first query that are not present in the second query. !he definition is self-eplanatory. !herefore if we eecute M*N/$ set operator on cric#et and football tables then all the rows from cric#et table which are not present in the second table tab le i.e. football will get returned and vice versa. $&5"M cric#et M*N/$ $&5"M footballD "n eecution the result set of this query will contain two rows from the cric#et table which were not present in the football table.
$nion and $nion %ll "et #perators $et operators allow us to combine rows returned from two or more queries. *n $%& we have four set operators. ). /nion +. /nion 0ll . *ntersect ect G. Minus *n this tutorial we will learn the concept of /nion and /nion 0ll set operator. >or >or the demonstration purposes * have created and populated two tables ootball. 7oth of these have two columns first name and last name with the same data type which is Var Varchar+. char+. * have also inserted some rows into the tables, h ere are the screenshots of the data in these tables. 7oth these tables have rows of o f data and if you will loo# carefully c arefully you will notice that there is a duplicate row in both of them. !his is row ) where first name is
$nion "et #perator
!he /N*"N set operator returns results from all the participating queries after eliminating duplications. &ets see the eample
$&5"M cric#et /N*"N $&5"M footballD =ere we have + separate queries. *n the first query we are selecting rows from first name and last name column of ootball table and both these queries are bound using union set operator. "n eecuting this query all the rows of both the columns of both the tables will be returned ecept the duplicate one.
$nion %ll "et #perator !he /N*"N 0&& operator returns results from all the participating queries, including the duplicate rows. !his means that if we will eecute the same query then we will get all the rows returned from both the tables along with the duplicate one. &et me demonstrate this to you. >or that * will copy this query and modify it accordingly. $&5"M cric#et /N*"N 0&& $&5"M footballD
&estrictions of "et #perators in #racle Database
•
!he epressions in the $&
•
6arentheses can be used to alter the sequence of eecution. !he "5(5 78 clause1
• • •
Du7licate rows are automatically eliminated e9ce7t in $N2ON *LL. olumn names from the 8rst 6uery a77ear in the result. !he out7ut is sorted in ascendin% order 5y default e9ce7t in $N2ON *LL.
'ablespace 'e ntrodction * promised this tutorial a while ago but never had a chance to do it since then. =owever here * am today, finally starting a new series on !ablespaces in "racle database. "racle database stores schema objects such as tables, indees, Views etc. logically into the tablespace and physically in datafiles associated with corresponding tablespace. $o we can say !ablespace are logical storage units made up of one or more datafiles. * #now this whole deal with datafiles and tablespaces is slightly confusing but dont worry. &et me try to eplain it to you in detail.
Datafiles (atafiles are physical files stored on your dis# created by oracle database and has .dbf etension. !hese files are not only capable for storing tables but also indees, synonyms, views and other schema objects created by you. !hese are physical files because they have eistence on your "$s file system and you can see them. !hese files are written by database writer :(7'5; processes and used by oracle database for proper functioning of your database system. (o not try to modify these files manually. 0 datafile cannot be shared between multiple tablespaces which means that every datafile belongs to a specific tablespace.
'ablespaces !hen comes the !ablespace. !ablespaces are logical entity in your database and logically organized data which is physically stored in datafiles. !hey are called logical storage units because they are not visible in the "$s file system. 0 tablespace belongs to only one database, and has at least one datafile that is used to store data for the associated tablespace. 8ou can also define tablespaces as logical storage units made up of one or more datafiles. "ne tablespace can have up to )B++ datafiles this number also depends upon your "$.
'*pes of 'ablespaces 'e can differentiate tablespace on the basis of two factors ). !ype of (ata +. $ize of (ata
'*pe of data consists #inds of tablespace including
). 6ermanent !ablespace +. !emporary !ablespace . /ndo !ablespace
0nd on the basis of "ize of Data we have + #inds of tablespace ). 7ig file tablespace +. $mall file tablespace
+ermanent 'ablespace *t is the tablespace which contains persistent schema object which means the data stored in the permanent tablespace persists beyond the duration of a session or transaction. "bjects in permanent tablespaces are stored in datafiles.
'emporar* tablespace "n !he contrary temporary tablespace are the tablespaces which contain schema objects only for the duration of a session which means that data stored in the temporary tablespace eists only for the duration of a session or a transaction. "bjects in temporary tablespaces are stored in tempfiles.
$ndo 'ablespace !hen there comes /ndo tablespace. /ndo tablespace is a special type of tablespace used by oracle database to manage undo data if you are running your database in automatic undo management mode. /ndo tablespace stores data permanently which means that undo tablespace are permanent in nature. /ndo tablespace play a vital role in providing •
5ead consistency for $&
•
!he ability to rollbac# a transaction that has failed to commit.
Net we have 7ig-file tablespace and small file tablespace.
,i-ile tablespace !he new concept started from "racle )Bg. 7ig file tablespace is best suited for storing large amounts of data. 7ig file tablespace can have maimum ) datafile which means bigfile tablespaces are built on single data files which can be as many as ++ data bloc#s in size. $o, a bigfile tablespace that uses IH7 data bloc#s can be as much as +!7 in size.
"mallile tablespace !his is the default type of tablespace in oracle database. $mall file tablespace can have multiple datafiles and each datafile can be as many as +++ data bloc#s in size. 0 small file tablespace can have maimum up to )B++ data files but this number depends on your "perating system also. nfo ,*te/ 8ou can create 6ermanent, temporary or undo tablespace either as big-file tablespace or small-file tablespace but by default they are alwa ys small-file tablespace.
nter0iew 1estions
1/ 'hat are the default tablespaces in "racle (atabase? %/ !he $8$!M and $8$0/J tablespaces are always created when the database is created. "ne or more temporary tablespaces are usually created in a database along with an undo tablespace and several application tablespaces. 7ecause $8$!M and $8$0/J are the only tablespaces always created with the database, the y are the default tablespaces. 1/ 'hat type of tablespace does $8$!M and $8$0/J belong to? %/ $8$!M and $8$0/J are always created as $M0&&>*& tablespace.
!hats all about the introduction of !ablespaces. * hope you found this article on !ablespaces in "racle (atabase helpful. Hindly please share it on your social networ# and help me reach out to more people. !han#s K have a great day@
ermanent !a5les7ace 6ermanent tablespace contains persistent schema object which means that the data stored in the permanent tablespace persists beyond the duration of a session or transaction. "bjects in permanent tablespaces are stored in data files. *n this web article * will eplain to you how to create a small file as well as a big file permanent tablespace in oracle database. $o lets start.
Su%%ested Readin%1 !a5les7ace 2ntroduction Database Connection.
!he 8rst ste7 in the creation of any ty7e of ta5les7ace is to connect to your data5ase. ou can do this usin% sys user or any other user which either has sysd5a 7ri&ile%es or RE*!E !*BLES*E system 7ri&ile%e. 2 will connect to my data5ase usin% SS user with sysd5a 7ri&ile%es. 1 s6l7lus as sysd5a
&o' o create smallle permanent tablespace
!o create a ta5les7ace we use RE*!E !*BLES*E ddl statement RE*!E SM*LL"2LE !*BLES*E re5ellionrider D*!*"2LE 1a77!BS(DEMORe5ell>.d5f3 S2E >IIM, 1a77!BS(DEMORe5ell/.d5f3 S2E >IIM LO2N
E!EN! M*N*EMEN! LO*L $N2"ORM S2E >IIM SEMEN! S*E M*N*EMEN! *$!O# !his ddl statement starts with <50! !07&$60< where both of which are oracle reserved #eywords. Net, since we are creating a small file tablespace thus we can eplicitly specify this by writing #eyword smallfile in between <50! and !07&$60<. CRE!E S"##$%#E !&#ES'CE !hou%h this ste7 is o7tional since oracle, 5y default, creates all the ta5les7aces as small 8le ta5les7ace yet it is a %ood 7ractice as it ma=es your code more reada5le. *fter RE*!E SM*LL"2LE !*BLES*E you ha&e to %i&e the name of your ta5les7ace. !a5les7ace name is com7letely user de8ned which means that you can %i&e whate&er name you want to your ta5les7ace. 2n my case 2 will name my ta5les7ace RebellionRider . (!$%#E: $sin% this clause we can add one or more data8les to our ta5les7ace. *s 2 mentioned in my 7re&ious tutorial that !a5les7aces are nothin% 5ut lo%ical stora%e units made u7 of one or more data8les.
!o add the data8le you :ust ha&e to write the name of the clause which is D*!*"2LE and then inside the sin%le 6uote you either ha&e to s7ecify the name of data8le the directory 7ath +where you want to 7ut your data8le4 as 2 did here or you can sim7ly write the name of your data8le. 2n latter case, oracle en%ine will create your data8le and 7laced it in the default directory. *fter writin% the name of your data8le you ha&e to s7ecify the siPe for your data8le usin% S2E clause which is mandatory, if you for%et to do so then oracle en%ine will %i&e you an error. Let3s say 2 want to s7ecify >IIMB as the siPe for my data8le re5el>. "or that 2 will write. #)**%+* C#,SE: !he lo%%in% clause lets you s7ecify whether creation of a data5ase o5:ect will 5e lo%%ed in the redo lo% 8le +LO2N4 or not +NOLO2N4. Lo%%in% clause has two o7tions1
>. Lo%%in% and /. Nolo%%in% 2f you s7ecify Lo%%in% then oracle en%ine will enerate redo lo%s for creation of ta5les, inde9es and 7artitions, and for su5se6uent inserts. But in case you do not want to %enerate redo lo%s for all those o7erations which 2 :ust mentioned then sim7ly write QNOLO2N. )owe&er lo%%in% is always 5etter as it ma=es reco&ery much easier.
E-!E+! "+*E"E+!: E9tents are the %rou7 of 89ed num5er of 5ytes of dis= s7ace called data 5loc=s or we can also say that e9tents are a %rou7 of data 5loc=s.
ou can use ta5les7aces either with local e9tent mana%ement or the older techni6ue of dictionary e9tent mana%ement. !he local e9tent mana%ement clause lets you s7ecify how the e9tents of the ta5les7ace will 5e mana%ed. Oracle %i&es you two ways to mana%e e9tents1 >. $niform /. *utoallocate $niform o7tion tells the data5ase to allocate and de-allocate e9tents in the ta5les7ace with the same un&aryin% siPe that you can s7ecify or let e9tents default to 5e >MB. $N2"ORM is the default for tem7orary ta5les7aces and cannot 5e s7eci8ed for undo ta5les7aces. On the other hand, *$!O*LLO*!E s7eci8es that the ta5les7ace is system mana%ed. $sers cannot s7ecify an e9tent siPe. !he a5o&e 6uery demonstrates the $niform E9tent Mana%ement with siPe >IIMB. 2f you want to switch e9tent mana%ement from uniform to auto-allocate then you :ust ha&e to sim7ly write auto allocate at the 7lace of siPe 5ecause in this mode the oracle en%ine decides the siPes for the e9tents of your ta5les7ace. E!EN! M*N*EMEN! LO*L *$!O*LLO*!E#
SE*"E+! S'CE "+*E"E+! Similar to the e9tents, Se%ments are the collection of one or more e9tents. "or ta5les7aces that ha&e local e9tent mana%ement, you can use either manual or automatic se%ment s7ace mana%ement. $nli=e e9tent mana%ement here we do not ha&e to s7ecify the siPe. But Oracle stron%ly recommends *$!OM*!2 se%ment s7ace mana%ement for 7ermanent and locally mana%ed ta5les7aces. 2n the a5o&e 6uery 2 ha&e set the Se%ment s7ace mana%ement as *$!O. 2f you want to switch se%ment s7ace mana%ement from auto to manual then sim7ly re7lace the SEEMEN! S*E M*N*EMEN! clause with this one
SEMEN! S*E M*N*EMEN! M*N$*L# E9ecution of the a5o&e 6uery will create you a small 8le 7ermanent ta5les7ace &o' o Create *i" le Permanent ablespace.
Khile creatin% a 5i% 8le ta5les7ace you :ust ha&e to ta=e care of a few thin%s such as a 5i% 8le ta5les7ace can ha&e only > data 8le unli=e the small 8le ta5les7ace where we ha&e to add se&eral data 8les. *lso while creatin% a 5i% 8le ta5les7ace it3s mandatory to write =eyword B2"2LE in 5etween reate and ta5les7ace =eywords otherwise oracle en%ine will create a small 8le. Info *+te! *lways remem5er, no two ta5les7aces and data 8les in a data5ase can ha&e the same name. !hus don3t for%et to chan%e the name of your ta5les7ace and its data 8les. !he query for creating a big file permanent tablespace will be1
RE*!E B2"2LE !*BLES*E TRe5ellionRider/T D*!*"2LE '1*M*NNOR*D*!*ORLBi%(Re5ell.d5f' S2E > LO2N E!EN! M*N*EMEN! LO*L SEMEN! S*E M*N*EMEN! *$!O# 2n this 6uery 2 am creatin% a 5i% 8le 7ermanent ta5les7ace with the name of Re5ellionRider/ which has only > data8le Bi%(Re5ell.d5f a7art from this rest of the clauses are the same as in a5o&e 6uery.
reate $ndo 'ablespace /ndo tablespace is a #ind of permanent tablespace used by oracle database engine to manage undo data if you are running your database in automatic undo management mode. !his undo data or say undo records are generally used to1 •
5oll bac# transactions when a 5"&&70
•
5ecover the database
•
6rovide read consistency
•
0nalyze data from an earlier point in time by using "racle >lashbac# %uery
•
5ecover from logical corruptions using "racle >lashbac# features
3ow to reate "mall file $ndo 'ablespace $imilar to other two tablespaces i.e. 6ermanent and !emporary tablespace which we have discussed in the previous two tutorials, undo tablespace can also be created as small file as well
as big file tablespace. $ince we are creating an undo tablespace thus we will use <50! /N(" !07&$60< clause. <50! $M0&&>*& /N(" !07&$60< LtbspAundoL (0!0>*& 9<1066!7$6A(M"tbspAundo.dbf9 $* )BBM 0/!"J!N( "N NJ! OBBM M0J$* /N&*M*!( 5!N!*"N N"4/050N!D !he create undo statement is slightly different than the others which we have done so far. $o lets delve deeper into this statement and see what we have done here. !he first line is self-eplanatory, here we are creating a small file undo tablespace by the name of tbspAundo. *n the second line we have our data file clause using which * have added a data file tbspAunod.dbf to our undo tablespace which has initial size )BBM7. 0s its a small file tablespace therefore we can add multiple data files into it which is not true in case of big file tablespace which can have maimum of ) data file. >or the simplicity of the demonstration here * have added only one data file. *n the third line we have auto etend clause. /sing this clause we are ensuring that as soon as our data file tbspAundo.dbf which is of )BBM7 size gets filled with undo segments, oracle engine will etend it automatically and increase it by OB B Megabytes. *n the fourth line we have clause M0J$*. /sing this clause we can set the maimum size to which the data file of undo tablespace can etend or say grow. =ere * have set the maimum size of our data file tbspAundo.dbf to /N&*M*!(. 8ou can also limit the ma size of your data file by simply writing the size. >or eample say you want to set the ma size of your data file to OBM7 in that case simply write OBM at the place of /N&*M*!(. *n the Oth and last line we have a 5etention clause which is right now set on No 4uarantee. 8ou can set 5etention clause either on 4uarantee or No 4uarantee. •
•
5!N!*"N 4/050N! specifies that "racle (atabase should preserve unepired undo data in all undo segments of tablespace. !his setting is useful if you need to issue an "racle >lashbac# %uery or an "racle >lashbac# !ransaction %uery to diagnose and correct a problem with the data. 5!N!*"N N"4/050N! returns the undo behavior to normal. $pace occupied by unepired undo data in undo segments can be consumed if necessary by ongoing transactions. !his is also the default setting.
*f you do not specify this clause then oracle engine will by default set the retention o n No 4uarantee.
3ow to Drop $ndo 'ablespace !o drop a tablespace from the database, we use the (5"6 !07&$60< statement. !he optional clause *N<&/(*N4 <"N!N!$ recursively removes any segments :tables, indees, and so on; in the tablespace, li#e this1 (5"6 !07&$60< dbaAsandbo *N<&/(*N4 <"N!N!$D (ropping a tablespace does not automatically remove the datafiles from the file system. /se the additional clause *N<&/(*N4 <"N!N!$ 0N( (0!0>*&$ to remove the underlying datafiles as well as the stored objects, li#e this1 (5"6 !07&$60< hrAdata *N<&/(*N4 <"N!N!$ 0N( (0!0>*&$D
,#at is Se-uence In Oracle Database 0 $equence is a database object which generates integer seque nce. 'e generally use it for populating numeric 6rimary Hey columns. *n order to create a sequence we use create sequence ((& statement. &ets ta#e a loo# at <50! $%/N< ((& $ynta
RE*!E SE;$ENE se6uence(name US!*R! K2!) start(numV U2NREMEN! B increment(numV UM*A*L$E ma9imum(num W NOM*A*L$EV UM2NA*L$E minimum(num W NOM2NA*L$EV U*)E cache(num W NO*)EV ULE W NOLEV UORDER W NOORDERV# irst attribute is
S!R! %!H )ere you ha&e to s7ecify a numeric &alue from which you want your se6uence to start. Khate&er num5er you s7ecify will 5e the 8rst num5er %enerated 5y your se6uence. %+CRE"E+! &/ !his attri5ute also ta=es a numeric &alue, to increment the se6uence 5y. !he num5er that you s7ecify here will ser&e as the inter&al 5etween se6uence num5ers. !he &alue for 2NREMEN!ED B cannot 5e I 5ut it can 5e any 7ositi&e or ne%ati&e &alue. 2f this &alue is ne%ati&e, then the se6uence descends. 2f the &alue is 7ositi&e, then the se6uence ascends. 2f you omit this clause, then the inter&al defaults to >. "-0#,E +)"-0#,E Ne9t attri5ute is M*A*L$E or NOM*A*L$E $sin% these attri5utes you can set the ma9imum u77er 5ound for your se6uence. *lways remem5er M*A*L$E must 5e e6ual to or %reater than S!*R! K2!) and must 5e %reater than M2NA*L$E attri5ute. 2n case you don3t want to set the M*A*L$E for your se6uence then you can use NOM*A*L$E attri5ute. "%+0#,E +)"%+0#,E Similar to M*A*L$E we use M2NA*L$E attri5ute to set the lower 5ound of our se6uence. *s a &alue this attri5ute also acce7ts the numeric &alue and should 5e less than or e6ual to S!*R! K2!) as well as less than M*A*L$E. 2n case you don3t want to set the lower 5ound for your se6uence then you can use NOM2NA*L$E attri5ute instead. CCHE +)CCHE *s &alue of cache attri5ute you s7ecify the num5er of inte%ers to =ee7 in memory. !he default num5er of inte%ers to cache is /I. !he minimum num5er of inte%ers that may 5e cached is /. !he ma9imum inte%ers that may 5e cached is determined 5y the formula1
E2L+ma9imum(num -minimum(num4*BS+increment(num4. S7ecify NO*)E to indicate that &alues of the se6uence are not 7re-allocated. 2f you omit 5oth *)E and NO*)E, the data5ase caches /I se6uence num5ers 5y default. C/C#E+)C/C#E LE and NOLE are two Xa%s which you ha&e to set. 2f you set the Xa% on cycle then your se6uence continues to %enerate &alues after reachin% either its ma9imum or minimum &alue. ou s7ecify NOLE Xa% when you do not want your se6uence to %enerate more &alues after reachin% its ma9imum or minimum &alue. 2f in case you omit 5oth these Xa%s then 5y default oracle en%ine will set the Xa% on NOLE.
)R(ER +))R(ER *t last we ha&e two more Xa%s which are ORDER and NOORDER. ORDER "la% %uarantees that se6uence num5ers are %enerated in order of re6uest. !his clause is useful if you are usin% the se6uence num5ers as timestam7s. uaranteein% order is usually not im7ortant for se6uences that are used to %enerate 7rimary =eys.
Set the Xa% on NOORDER if you do not want to %uarantee that the se6uence num5ers are %enerated in order of re6uest. NOORDER is the default Xa% in case you omit either of them. How to Create a Seuence Let3s create a se6uence 5y the name of s6(demo
RE*!E SE;$ENE s6(demo> S!*R! K2!) > 2NREMEN! B / M*A*L$E >I M2NA*L$E > *)E @ LE ORDER# !his is a sim7le se6uence 5y the name of s6(demo which is startin% with > and the inter&al 5etween e&ery se6uence is /. !he Ma9 &alue for this se6uence is >I and Min &alue is > and this se6uence will cache @ inte%ers at a time and will cycle after reachin% the ma9imum &alue which is >I. *lso 2 ha&e set ORDER "L* which means the num5ers will 5e %enerated in the %uaranteed order. "ew thin%s which you should =now1 >. ou can s7ecify any of these attri5utes and Xa%s in any order. Means order of these Xa%s is not 89ed. /. *ll these attri5utes and Xa%s are o7tional. 2f you omit all of them then oracle en%ine will create a default se6uence for you. Let3s see an e9am7le RE*!E SE;$ENE s6(demoI/# How !o ,se Seuence !o use a se6uence we use NE!A*L and $RRA*L. Both these are 7seudo columns of a se6uence usin% which we can retrie&e ne9t &alue and current &alue of a se6uence. NE!A*L column returns the ne9t &alue of the se6uence as well as initialiPes the se6uence whereas $RRA*L column will return the current &alue of the se6uence.
SELE! s6(demo.NE!A*L "ROM dual#
!his 6uery will initialiPe and return the 8rst &alue of your newly created se6uence. !o %et the current &alue of your se6uence you use $RRA*L 7seudo column of a Se6uence let3s see how1 SELE! s6(demo.$RRA*L "ROM dual# !his 6uery will %i&e you the current &alue stored in your se6uence. oints to 5e remem5ered1 Before usin% any se6uence it3s mandatory to initialiPe it 8rst. 2f you will try to retrie&e current &alue without initialiPin% it then it will %i&e you an error and we use NE!A*L 7seudo column to initialiPe a se6uence as well as to retrie&e ne9t &alue of the se6uence. !his means after creatin% a se6uence you ha&e to e9ecute the NE!A*L 6uery 5efore the $RRA*L one. How !o 'opulate +umeric 'rimary ey column using a Seuence Su77ose there is a ta5le 5y the name of demo which has two columns demo(id and demo(name where column demo(id is a numeric 7rimary =ey column and column demo(name is ca7a5le of holdin% &aria5le character strin% as its data ty7e is A*R)*R/. ou can use se6uence to 7o7ulate 7rimary =ey column. Let see how1
2NSER! 2N!O demo A*L$ES +s6(demo.ne9t&al, 9yP34# How !o "odify Seuence ou can modify a se6uence usin% the *L!ER SE;$ENE statement. !here are some limitations on what you can modify in a se6uence such as1
>. ou cannot chan%e the start &alue of a se6uence. /. !he minimum &alue cannot 5e more than the current &alue of the se6uence. @. !he ma9imum &alue cannot 5e less than the current &alue of the se6uence. Su77ose you want to modify the &alue of 2NREMEN! B attri5ute from / to 0, so for that *L!ER SE;$ENE command will 5e1 S;L*L!ER SE;$ENE s6(demo 2NREMEN! B 0# !his &ery sim7le statement starts with *L!ER SE;$ENE followed 5y the name of the se6uence which is s6(demo ne9t is the 2NREMEN! B attri5ute which we want to modify. How !o (rop Seuence !o delete any se6uence from the schema we use DRO DDL statement. Say you want to delete the Se6uence s6(demo which we :ust created, so for that the statement will 5e
S;LDRO SE;$ENE s6(demo#
Khat is S;L Aiew 2n Oracle Data5ase $%& view is nothing but a logical table or a virtual table stored in a database. 'e can also define a V*' as a $&
>. Security /. Reducin% com7le9ity of a 6uery. Security: $sin% a &iew you can mas= columns of a ta5le and restrict any user to use the data from that column. "or e9am7le,
Su77ose you ha&e a lar%e ta5le containin% a mi9ture of 5oth sensiti&e as well as %eneral interest information. 2n this case it will 5e &ery handy for you to create a &iew which only 6ueries the %eneral interest columns of the ori%inal ta5le and in turn %rants 7ri&ile%es on this &iew to the %eneral users. 2n this case the 7o7ulation of %eneral users can 6uery the &iew and ha&e direct access only to the %eneral information omittin% the sensiti&e information 7resent in the underlyin% ta5le. !hus in this way &iews can 5e used to %i&e access to selecti&e data in a ta5le. Reducing t4e complexity of a uery *nother reason for usin% a &iew is to reduce the com7le9ity of a 6uery which is made 5y :oinin% &arious ta5les. "or e9am7le,
* &iew 5uilt on a com7le9 :oin can 5e created in order to include the com7le9ity in the &iew itself. !he result of this is a re%ular &iew o5:ect that loo=s to 5e a sin%le ta5le which could 5e 6ueried 5y you as any re%ular ta5le. !he &iew can 5e :oined with other &iews and ta5les as well. !hus here in this way, &iew can 5e used to reduce the com7le9ity of a common Yoin. S+ntax of SL /IE, in Oracle Database
RE*!E A2EK &iew(name *S SELE! column(name+s4 "ROM ta5le(name K)ERE condition#
!his is a synta9 of a &ery sim7le &iew which starts with the RE*!E and A2EK =eyword followed 5y the name of the Aiew, you can assi%n any name of your desire to your &iew. Ne9t we ha&e Q*S which is a%ain an oracle reser&ed =eyword and followed this we ha&e our Select statement. Su%%ested !utorial UAideoV1 )ow to retrie&e data usin% SELE! DML statement in Oracle Data5ase Prere-uisites !o create a &iew in your own schema, you must ha&e the RE*!E A2EK system 7ri&ile%e. !o create a &iew in another user's schema, you must ha&e the RE*!E *N A2EK system 7ri&ile%e. Create a Simple /ie'
2n this e9am7le 2 will create a &iew 5y the name of &w(re5ellion on Em7loyees ta5le of )R schema. ou can %i&e whate&er name you want to your &iew. RE*!E A2EK &w(re5ellion *S SELE! 8rst(name, email, 7hone(num5er "ROM em7loyees# !his is a &ery sim7le &iew created on QEm7loyees ta5le of )R user and it will hold all the rows of "irst name, email and 7hone num5er column of the 5ase ta5le em7loyees. Once a &iew is created, it can 5e referred to in your select statement as if it were a ta5le. &o' to recreate a /ie' (OR REPLACE)
Ke use OR REL*E to recreate the de8nition of a &iew after creatin% it. Su77ose in the a5o&e &iew &w(re5ellion you realiPe that alon% with the 8rst name column you also wanted to ha&e the last name column. !here are two o7tions to do this tas= either dro7 the &iew and recreate it or :ust recreate the &iew without dro77in% it. Let3s see how RE*!E OR REPLACE A2EK &w(re5ellion *S SELE! 8rst(name, last0name, email, 7hone(num5er "ROM em7loyees# *7art from the OR REL*E and the column L*S! N*ME nothin% is di
ou can use Descri5e DES dml command to see the de8nition of your &iew e.%.
DES &w(re5ellion# 2 =now QDescri5e is a DDL statement not DML 5ut 2 thou%ht this ti7 is worth sharin%. &o' o retrie2e Ro's from a /IE, in Oracle Database (SELEC $$l)
ou can use SELE! ddl command to retrie&e rows from a A2EK similar to the way we do with ta5les in oracle data5ase. SELE! F "ROM &w(re5ellion ou can also use ORDER B clause to sort the result. SELE! F "ROM &w(re5ellion ORDER B 8rst(name# "urthermore you can e&en use K)ERE clause in your SELE! statement to 8lter the result. SELE! F "ROM &w(re5ellion K)ERE 8rst(name Z 'Nancy'#
Info *+te! 2n case you want to use K)ERE and ORDER B clause in a sin%le SELE! statement then always remem5er K)ERE clause will come 5efore the ORDER B clause in the 6uery. "or E9am7le SELEC 4 FRO5 2'0rebellion rst0name:
,&ERE rst0name 6 78anc+7 ORDER *9
&o' to ;PDAE ro's in a /IE, (;PDAE $$l)
Similar to the SELE! ddl we can also e9ecute $D*!E ddl on a A2EK. Su77ose you want to u7date the email address of em7loyee Nancy then 6uery for this will 5e1 $D*!E &w(re5ellion SE! email Z'nancy[e9am7le.com' K)ERE 8rst(name Z'Nancy'# &o' to $elete ro's in a /IE, (DELEE $$l) ou can e9ecute DELE!E DML command to delete one or more rows from the ta5le usin% A2EKS. DELE!E "ROM &w(re5ellion K)ERE 8rst(name Z 'Nancy'# &o' to insert Ro' in a /IE, in Oracle Database (I8SER $$l) !o insert a row, a &iew has to satisfy all the constraints on any column of underlyin% ta5le o&er which it3s created. 2nsert DML is su5:ect to se&eral restrictions when e9ecuted on a Aiew in Oracle data5ase such as1
>. * &iew must contain the entire Not Null column from underlyin% ta5le. !he failure to include any columns that ha&e NO! N$LL constraints from the underlyin% ta5le in the &iew will result the non-e9ecution of the 2NSER! statement. )owe&er an $D*!E or DELE!E statement can 5e issued. /. 2f there is any column with referential =ey constraint in underlyin% ta5le then &iew must contain that column and a 7ro7er &alue must 5e 7ro&ided to that column while 7erformin% 2nsert on the Aiew and many more. !hus to 7erform an 2NSER! dml on a &iew, it has to include all the columns which either ha&e NO! N$LL constraint or Referential constraint or any other mandatory constraint from the 5ase ta5le em7loyees. E.%. 2NSER! 2N!O &w(su7erheroes A*L$ES +'Ste&e', 'Ro%ers', \]0^\]4# Khere &w(su7ereheroes is another &iew created o&er Su7erheroes ta5le which has ] columns 8rst name, last name, and real name, hone num5er and SSN. !his ta5le has no constraint on any column. &o' to create a complex 2ie' (/ie' b+
om7le9ity of a &iew de7ends u7on the com7le9ity of its SELE! statement. ou can increase the com7le9ity of a &iew 5y increasin% the com7le9ity of the SELE! statement. RE*!E OR REL*E A2EK &w(:oin *S SELE! 8rst(name, de7artment(name "ROM em7loyees em7 "$LL O$!ER YO2N de7artments de7t ON +em7.DE*R!MEN!(2D Z de7t.DE*R!MEN!(2D4# *s 2 told you a5o&e that 5y usin% a Aiew you can reduce the com7le9ity of a 6uery. * &iew 5uilt on a com7le9 :oin can 5e created in order to include the com7le9ity in the &iew itself. !he result of this is a re%ular &iew o5:ect that loo=s to 5e a sin%le ta5le which could 5e 6ueried 5y you as any re%ular ta5le. !he &iew can 5e :oined with other &iews and ta5les as well. !hus here in this way, &iew can 5e used to reduce the com7le9ity of a common Yoin.
"imple ase Expression.
/sing -!=N-&$ logic in "racle $%& that too without invo#ing any #ind of $%& procedures. <0$ epression can do all the wor# of a (<"( epression. $everal boo#s recommend using <0$ over (<"( because •
<0$ epressions are easier to read in comparison to (<"( epression and
•
<0$ epression is 0N$*-compliant and forms part of the $%&PQ+ standard.
*n "racle there are two types of <0$ epressions •
$imple case epressions
•
$earched case epressions
"imple ase Expression 0s the name suggests it is the simplest version of <0$ epression in other words we can say that it is a fleible and easiest version of (<"( epression. !he synta of “Simple Case Expression” is <0$ searchAepression '=N inputAepression ) !=N outputAresult ) '=N inputAepression + !=N outputAresult + ... '=N inputAepression N !=N outputAresult N &$ lseAresult N( 'here •
$earchApression is the epression to be evaluated.
•
*nputApressions are the epressions to be evaluated a gainst searchAepression.
•
"utputA5esults are the returned results :one for each possible epression;. *f epression) evaluates to searchAepression, result) is returned, and similarly for the other epressions.
•
lseA5esult is returned when no matching epression is found.
!he $imple urthermore the
lse statement is optional so in case you omit it then Null is displayed when the simple case epression cannot find a match. 6oints to remember •
!he data type of all the input epressions must match with the data type of search epression. Means data type of search epression and input epression must be the same.
•
!he datatype of all the output results must match with lse result means the data type of output result along with else result must be the same.
!he maimum number of arguments in a <0$ epression is +OO. 0ll epressions count toward this limit, including the initial epression of a simple <0$ epression and the optional &$ epression. ach '=N ... !=N pair counts as two arguments. !o avoid eceeding this limit, you can nest <0$ epressions so that the outputAresult itself is a <0$ epression.
1er* 4/ olmn Name as "earc Expression. $&5"M employees '=5 departmentAid 7!'N )B 0N( OB "5(5 78 +D &ets ta#e a loo# at the case bloc# of this query. 'e are using column departmentAid of employees table as our search epression and the values from department id column :)B, +B, B, GB and OB; are serving as input epressions of '=N-!=N pairs. 'henever we specify a column name of a table as search epression the oracle engine treats all the data from this column as an array and matches all the inputAepressions with every element of this array and if a match is found, it returns the corresponding result values, "therwise the else statement gets return.
1er* 2/ "trin- as "earc Expression. *n case you specify a string instead of a column name as a search epression then oracle will find the first best fit match and then return the corresponding result value. $&
'=N 9lase9 '=N 9(og9 !=N 9+ !rue9 '=N 9alse9 '=N 9(og9 !=N 9G !rue9 &$ 9$orry9 N( >5"M dualD =ere in this simple case, the search epression is R(og along with G 'hen-and-!hen pairs. !he input epression of +nd and Gth pair is a string S(ogT which is a perfect match for our search epression but on eecuting this query, oracle engine will return the result value from the first best match which in our case is the +nd pair.
"1L Delete and 'rncate ommand. !ill so far we have learned how to insert data into a table using *N$5! statement, $%& (eveloper and from some other table so the only thing which is left is how to delete data from a table. 0mong several different ways of deleting data from a table the two most used ways are $%& (elete and !runcate command. 0lthough both perform the same tas# yet they are still different. *n this blog you will learn how to use $%& (elete and truncate command as well as the various differences between them.
"1L DELE'E command $%& (elete is a (M& command which removes entire rows of data from a specified table or view. "*ntax
(&! >5"M table_name '=5 conditionD 0s its a (&! query thus it starts with (&! #e yword followed by >5"M #eyword. 0fter that you have to specify the table name from which you want to delete the data and at the end we have our '=5 clause. '=5 clause enables you to choose rows from the references table or view that need to be deleted conditionally. !hat is only those rows that meet these search conditions are deleted. *f you omit the '=5 clause, all rows in the table or view are deleted. or example
1er* 4 / &ets say you want to delete only those rows from employees table of =5 schema where job id is $!A<&5H
(&! >5"M employees '=5 jobAid U 9$!A<&5H9D !his query will delete all the rows from employees table wh ere job id is st cler#.
1er* 2 / Now, what if, you want to delete all the rows from the employees table? >or that we just need to do a slight modification in the above query.
(&! >5"M employeesD 0s * mentioned above *f we use (&! command without the '=5 clause, all the rows in the table or view are deleted. "n eecuting this query all the rows from employee table will get deleted.
!here are few things which you should #now about $%& (&! command ). (&! is a (M& command. +. 'henever you perform (&! operation all the triggers associated with (&! command gets eecuted. . (&! chec#s all the constraints on all the columns of the rows which are getting deleted before deleting the rows and accordingly set the *nde. G. *f you accidently deleted some data then there are still chances that you can get it bac# by performing 5"&&70
"1L 'rncate ommand
'rncate command deletes all te rows from a table. >or eample say you want to delete all the rows from countries table of =5 sche ma.
!5/N<0! !07& countriesD !hings to remember ). !runcate is a ((& command. +. 8ou cannot roll bac# a !5/N<0! statement. . 8ou cannot use '=5 clause with !5/N<0! statement. !his means that unli#e $%& (&! there are no options for conditional delete.
G. /nli#e $%& delete when you perform !runcate no trigger gets eecuted. O. !runcate doesnt use much /N(" space as $%& (elete. . !runcate is significantly faster than the $%& (elete. !his is because when you use (&!, all the data first gets copied onto the /ndo !ablespace after which the delete operation gets performed. !hats why when you type 5"&&70
Natural Yoins. Denition!
* N*!$R*L YO2N is a YO2N o7eration that creates an im7licit :oin clause for you 5ased on the common columns in the two ta5les 5ein% :oined. ommon columns are columns that ha&e the same name in 5oth ta5les. * N*!$R*L YO2N can 5e an 2NNER :oin, a LE"! O$!ER :oin, or a R2)! O$!ER :oin. !he default is 2NNER :oin. Source1 Oracle 'e will start with Natural 2oin. 7ut before jumping over to natural join let me tell you a few terminologies "orce table 1 !able which comes after >5"M clause in the select statement 'ar-et table1 0ll the tables which come after 2"*N clause in the query.
'hen the join query is eecuted then oracle starts matching data from the source table to the target table. *f there is a hit for a matching source table data in the target table then the value is returned.
Examples of 8atural =oins in Oracle Database Scenario 1: 4en t4ere is only one identical name column 5etween source and target ta5les.
Let3s use De7artments ta5le and locations ta5le for the demonstration.
De7artment ta5le has 0 columns DE*R!MEN!(2D, DE*R!MEN!(N*ME, M*N*ER(2D, LO*!2ON(2D *nd Locations ta5le has \ columns Location id, street address, 7ostal code, city, state 7ro&ince and country id $7on minutely o5ser&in% you will notice that location id is a common column 5etween our source and tar%et ta5les. 2t is the forei%n =ey and thus has same data ty7e and column width. Let3s write the 6uery for natural :oin. Say you want to 8nd the ity for all your De7artments SELE! de7artment(name, city "ROM de7artments
N*!$R*L YO2N locations#
2n this 6uery we are selectin% de7artment name from de7artments ta5le and city from locations ta5le. ou can select other columns too De7artments is our source ta5le as its comin% ri%ht after "ROM =eyword and locations is our !ar%et ta5le as its comin% after YO2N clause. !he 5est 7art of usin% Natural :oin is that you do not need to s7ecify the :oin column 5ecause column with the same name in source and tar%et ta5le are automatically associated with each other. On e9ecutin% this 6uery oracle returns all the rows from de7artment column of de7artments ta5le and from city column of locations ta5le, where location id of de7artments ta5le is e6ual to the location id of location ta5le. Scenario 2: What if when our source and target tables have more than one identical name column.
!o demonstrate this scenario we will use employees and departments table these two tables shares two common columns which are department id and manager id. =ere if we put Natural join on these two tables then oracle engine will use these two common columns to return the result. $ay we want to see the name of employee and the name of department in which he or she is wor#ing. >or that we will select first name from employee table which is our source table and department name from departments table.
SELE! 8rst(name, de7artment(name "ROM em7loyees de7artments#
N*!$R*L YO2N
*s 2 ha&e already mentioned that when we use natural :oin, there is no need to s7ecify :oin columns e97licitly. *ll the columns which are common in 5oth source and tar%et ta5le %et associated automatically. !he 6ueries in which all the common columns of source and tar%et ta5le %et associated automatically are =nown as ure Natural Yoin. Khich means in this 6uery, oracle has automatically associated our common columns which are de7artment id and mana%er id. !o help you understand more clearly lets write same query but this time using "N clause.
SELE! 8rst(name,de7artment(name "ROM em7loyees YO2N de7artments ON +em7loyees.mana%er(id Z de7artments.mana%er(id *ND em7loyees.de7artment(id Z de7artments. de7artment(id4#
)ere is a sli%ht chan%e in the natural :oin synta9 as we are usin% ON clause this time, instead of writin% natural :oin we will only write YO2N. Both these 6ueries 7roduce the same results as 5oth are same 6uery written in di
=ere "nce again we will use our natural join query.
SELE! 8rst(name, de7artment(name "ROM em7loyees de7artments#
N*!$R*L YO2N
*s we can see here in this 6uery that when we ha&e more than one common column oracle en%ine uses all of them to 7roduce the result Now, here in this 6uery we are usin% em7loyees and de7artment ta5les which ha&e / common columns de7artment id and mana%er id.
Khat if you want to use only de7artment id in your natural :oin instead of 5oth mana%er and de7artment id_ 2n this situation we use natural :oin with $S2N clause. Say we want to select all the 8rst name and de7artment name from source ta5le em7loyees and tar%et ta5le de7artments from where we ha&e e6ual &alues of mana%er ids in 5oth the em7loyees ta5le and the de7artments ta5le. Let3s write the 6uery SELE! 8rst(name, de7artment(name "ROM em7loyees YO2N de7artments $S2N+mana%er(id4#
&i-t #ter 5oin n "1L. 0s the name suggests 5ight "uter 2oin is a form of "uter 2oin that returns each and every record from the source table and returns only those values from the target table that fulfil the 2oin condition. 6Note/ 'e "orce table is the one situated on the right side of the 5ight "uter 2oin
S+ntax ! SELE! columns "ROM target_table R2)! O$!ER YO2Nsource_table ON+source(ta5le.column Z tar%et(ta5le.column4 K)ERE condition ORDER B column(names#
amples1 !o demonstrate the wor#ing of 5ight "uter 2oin, * have created two tables by the name of emp and dept and have also inserted some data in them.
$tructure of emp table
$tructure of dept table
(ata *n emp table
(ata *n dept table *f you have noticed that in dept table *! and mar#eting department dont have any employees.
1er* 4/ &i-t #ter 5oin wit 7#N8 clase Now when should we use "N 2oin 5"M dept 5*4=! "/!5 2"*N "N :emp.M6A*( U dept.M6A*(;D
emp
*n the above query we are selecting emp9name from emp table and dept9name from dept table. %mp table is the source table as its on the right hand side of join clause thus automatically ma#ing dept table as the target table. &olumn emp'id is a primar( )e( in emp table and foreign )e( in dept table thus column empAid is the column which is establishing a relationship in between these two tables. 0s column empAid is common in both tables thus its a best fit for 2oin condition :"N clause;.
*f you eecute this query you will get all the rows of empAname column of emp table as emp is the source table and only those rows of deptAname column of dept table which satisfy the join condition :condition in "N clause;.
1er* 2/ &i-t #ter 5oin wit :3E&E clase 8ou can use '=5 clause with any type of 2"*N and limit or >ilter the result. *n case of 2"*N$, '=5 clause always comes after 2oin 5"M dept 5*4=! "/!5 2"*N emp "N :emp.M6A*( U dept.M6A*(; '=5
empAsalary W OBBBBD
1er* ;/ &i-t #ter 5oin wit $"N< clase /sing clause can be used on the place of "N clause when ). 'e are performing qui 2oin. +. 'e want to put join on that column of source and target table which are common i.e. have same name and data type. Most of the joins youll perform will be equijoins, and if you always use the same name as the primary #ey for your foreign #eys, then1 $&5"M dept 5*4=! "/!5 2"*N emp /$*N4 :empAid;D
"ull Outer Yoin 2n S;L. !odays $%& article is all about >ull "uter 2oin. 7efore moving ahead just want to say that in order to better understand the concept of full outer 2oin please read my last two $%& tutorial article on right and left outer join. =ere once again we will be using same table which we have used so far in this "uter 2oin series. !hese are the emp and the dept tables. &ets have a quic# loo# of the structure and the data of these two tables.
Desc em7#
Our ta5le em7 has @ columns em7 id, em7 name and em7 salary. )ere column em7 id is a 7rimary =ey. Desc de7t#
!a5le de7t also has @ columns de7t id, de7t name and em7 id. )ere in this ta5le column de7t id is a 7rimary =ey where column em7 id is a forei%n =ey reference from the em7 ta5le. Now lets have a loo# at the data of these two tables.
SELE! F "ROM em7#
Em7 ta5le has ] rows where column one has em7 id and column / has em7 name and column @ consist the salary of these em7loyees. Now data of dept table
SELE! F "ROM de7t#
!a5le De7t also has ] columns. olumn one hold dept id where column two hasdept name and column three emp id which is the forei%n =ey has only @ records corres7ondin% to de7artment name sales account and 8nance. 'e can interpret this data easily. >or eample the employee with employee id ) is na med $teve in our emp table and wor#s in department of $ales. $imilarly employee with employee id + is Nancy who wor#s in 0ccounts department and employee with employee id is 4uru who wor#s in finance. 0lso we have not assigned any employee id for department of *! and Mar#eting which signifies that no one wor#s in these dep artments. &ets jump over to full outer join. ll oter join is =ind of a combination of bot ri-t oter join and left oter join becase it retrns all te rows from left as well as te ri-t side table. &ets have loo# of >/&& "/!5 2"*N synta. S+ntax SELE! column names "ROM
ta5le> "$LL O$!ER YO2N "$LL YO2N ta5le / ON+e97ression4 or $S2N+column(name4 K)ERE+e97ression4 ORDER B column names# Synta9 is 7retty similar to our left or ri%ht :oin as you can see. Ke ha&e our SELE! statement where you can s7ecify the name of the columns from 5oth the 7artici7atin% ta5les followed 5y "ROM clause. *nd our YO2N clause which is full outer :oin. )ere you can either write full outer :oin or :ust outer :oin since 5oth are 7ermissi5le and 7erform the same tas=. *nd then we ha&e our Yoin condition ON and $S2N followed 5y K)ERE and ORDER B clause. 6uery 1 : $ull )uter 7oin it4 )+ clause SELE! em7(name, de7t(name "ROM em7 "$LL O$!ER YO2N de7t ON +em7.em7(id Z de7t.em7(id4#
)ere in this 6uery we are selectin% em7 name column from em7 ta5le and de7t name column from de7t ta5le. 2n our full outer :oin clause we ha&e em7 ta5le on left side and de7t ta5le on ri%ht side and then we ha&e Our ON clause where we are com7arin% the &alues of em7 id columns from the 5oth the ta5les. "n eecuting this query the result will be.
*f you will observe minutely then you can see that the result till row O is similar to that of right outer join, 0s all the records from right side table is here and only those records from left side
table which satisfy the join condition are here in the result. >ollowed by all the remaining records from the tables thus the last row and X contains the remaining emp names. =ere in this "N join clause we used columns which have same name and data type. Now lets use columns which have different name and same data type for eample column dept id.
SELE! em7(name, de7t(name +em7.em7(id Z de7t.de7t(id4#
"ROM em7 "$LL O$!ER YO2N de7t ON
and the result of this 6uery is
6uery 2 : $ull )uter 7oin it4 ,S%+* clause Ke use $S2N :oin condition when
!he column in :oin condition share the same name and same data ty7e and are com7ared only usin% Z com7arison o7erator and no other com7arison o7erator such as %reater than, less than etc. *s you can see, in the :oin condition +ON clause4 of ;uery > we ha&e used em7 id column of 5oth the ta5les. !his column shares the same name and same data ty7e hence we can easily re7lace this ON clause with $S2N clause. Let3s do it. SELE! em7(name, de7t(name (emp0i$) #
"ROM em7 "$LL O$!ER YO2N de7t ;SI8>
!he result of this 6uery is
2f you com7are this result with the result of our 8rst 6uery then you will 8nd them as e9actly the same. 6uery 3 : $ull )uter 7oin it4 HERE clause ou can also limit the result 5y usin% K)ERE clause. Say you want to see the name of only those em7loyees and their de7artments who ha&e a salary of less than ]IIII
"or that we can modify our 6uery / and add a where clause to it. SELE! em7(name, de7t(name, em7(salary "ROM em7 "$LL O$!ER YO2N de7t $S2N +em7(id4 ,&ERE emp.emp0salar+ ? @ # On e9ecutin% this 6uery you will %et only those em7loyee and their de7artments who has salary less than ]I,III
6uery 8 : $ull )uter 7oin it4 )R(ER &/ clause Similarly you can sort the result usin% ORDER B clause. Say you want to sort the result of 6uery @ in ascendin% order accordin% to the em7loyee name +em7(name column4 "or that :ust add the ORDER B clause followed 5y the column name which is em7(name in our case.
SELE! em7(name, de7t(name "ROM em7 "$LL O$!ER YO2N de7t $S2N +em7(id4 K)ERE em7.em7(salary ` ]IIII ORDER *9 emp0name # 2f you e9ecute this 6uery then 5y default the result will 5e sorted in ascendin% order.
2f you write DES ri%ht after the column, your result will 5e sorted in descendin% order. "or E9am7le SELE! em7(name, de7t(name "ROM em7 "$LL O$!ER YO2N de7t $S2N +em7(id4 K)ERE em7.em7(salary ` ]IIII ORDER B em7(name DESC#
2nner Yoin 2n S;L. !his is the fifth tutorial in the 2oin series and till now we h ave seen Natural 2oin, 5ight "uter 2oin, >ull "uter 2oin and left "uter 2oin. 0nd in this tutorial we will cover the concepts of *NN5 2oin. Denition 2nner Yoin is the :oin which returns all those rows from 5oth the 7artici7atin% ta5les that satisfy the Yoin condition or for that matter e97ression of ON$S2N clause. S+ntax
SELE! column names "ROM ta5le> 2NNER YO2N YO2N ta5le / ON+e97ression4 or $S2N+column(name4 K)ERE+e97ression4 ORDER B column names#
*n the first line of our synta we have our "ELE' statement where you specify all those columns from both the participating table :table) and table+; whose data you want to fetch in your result set. !he $&ollowed by our join clause we have our 5oin condition . 7asically we have two types of join condition that we can use one at a time but never together. !hese two join conditions are #N and $"N<. /sage of each join condition depends on certain conditional parameters. 0nd at the end of the synta we have our :3E&E and #&DE& ,> clause. 48ote #ere if +ou are usin" ORDER *9 clause t#en it must be t#e last statement of +our -uer+. ,#en to use O8 an$ ;SI8> clause
)o7e synta9 is clear. &ets do some practical eercise. *ll be using the same table which we have been using in all our 2"*N tutorial emp and dept. :for tables you can refer to !able for $%& 2oins tutorial; 6,ER/ 1: %++ER 7)%+ wit4 )+ clause
SELE! em7(name,de7t(name "ROM em7 2NNER YO2N de7t ON+em7.EM(2D Z de7t.EM(2D4# )ere in this 6uery we are selectin% em7 name from em7 ta5le and de7t name from de7t ta5le while in YO2N condition which in this case is the ON clause we are com7arin% em7(id column of 5oth the ta5les. *s here in this 6uery we are usin% ON :oin condition thus we can use any column as e97ression of ON clause as lon% as columns share same data ty7es. !he name of
the column doesn3t matter here. "or e9am7le we ha&e de7t(id column in de7t ta5le which shares same data ty7e as of em7(id column of em7 ta5le which is a N$MBER thus here in our ON clause we can use this de7t(id column too. SELE! em7(name,de7t(name "ROM em7 2NNER YO2N de7t ON+em7.EM(2D Z de7t.DEP0ID4#
6,ER/ 2: %++ER 7)%+ wit4 ,S%+* clause.
Ke already =now that we can use $S2N clause when the column in :oin condition share same name and same data ty7e and are com7ared only usin% e6ual to + Z 4 com7arison o7erator and no other com7arison o7erator such as %reater than, less than etc. SELE! em7(name,de7t(name "ROM em7 2NNER YO2N de7t $S2N+EM(2D4# )ere we used em7(id column in $S2N clause 5ecause it3s common in 5oth ta5les +forei%n =ey relation in em7 and de7t ta5le4 and shares same name as well as data ty7e. Ke can easily com7are its &alue usin% e6ual to o7erator in 5oth the ta5les as we did in our 8rst 6uery where we used em7(id column of 5oth the ta5les in the e97ression of ON clause.
6,ER/ 3: %++ER 7)%+ wit4 HERE clause.
Ke use K)ERE clause to limit the result of a ;uery, similarly you can use K)ERE clause here with 2nner :oin to do the same. Say you want to see the name and de7artments of only those em7loyees who ha&e a salary of less than ]IIII "or that you :ust ha&e to add the where clause ri%ht after the YO2N condition in the 6uery. "or e9am7le SELE! em7(name,de7t(name "ROM em7 2NNER YO2N de7t $S2N+EM(2D4 K)ERE em7.em7(salary ` ]IIII#
6,ER/ 8: %++ER 7)%+ wit4 )R(ER &/ clause.
"eel free to use ORDER B clause if you want to sort the result returned 5y your
6uery in *scendin% or Descendin% order. 8OE #ere ORDER *9 clause must be t#e last statement of a ;ER9.
ORDER B clause 5y default sorts the result in ascendin% order. But if you want to arran%e the result in Descendin% order then you ha&e to s7ecify it 5y usin% DESEND2N or DES =eyword with the ORDER B clause. "or e9am7le >. Sort the result in ascendin% order accordin% to em7(name column SELE! em7(name,de7t(name "ROM em7 2NNER YO2N de7t $S2N+EM(2D4 ORDER B em7(name# /. Sort the result in descendin% order accordin% to em7(name column. SELE! em7(name,de7t(name "ROM em7 2NNER YO2N de7t $S2N+EM(2D4 ORDER B em7(name DES#
ross Yoin 2n S;L. =ey guys hope you are enjoying my articles. !his one is all about cross outer join Denition
ross Yoin 7roducesartesian 7roduct of the ta5les which are 7artici7atin% in the Yoin 6ueries that3s why it3s also =nown 5y the name of artesian Yoin.
enerally we use ROSS YO2N when there is no relationshi7 5etween 7artici7atin% ta5les. S+ntax
SELE! column names "ROM ta5le> ROSS YO2N ta5le / K)ERE +e97ression4 ORDER B column names# Or SELE! column names "ROM ta5le>, ta5le / K)ERE +e97ression4 ORDER B column names *n the first line we have our $elect statement where you can specify the list of columns from both the participating tables.
*n the second line we have our tables and the <5"$$ 2"*N clause. =ere you can write either clase ten ma=e sre it mst be te last statement of *or "1L ?er*.
Example: 23ll 5e usin% the same ta5le which we ha&e 5een usin% in all our YO2N tutorial so far. !hese are the em7 and de7t ta5les. +"or ta5les you can refer to !a5le for S;L Yoins tutorial4 6uery 1: CR)SS 7)%+ SELE! em7(name, de7t(name "ROM em7 ROSS YO2N de7t#
!his 6uery is fairly sim7le# we are selectin% em7 name and de7t name from em7 and de7t ta5les res7ecti&ely. On e9ecutin% this 6uery, the 8rst record of em7 name column of em7 ta5le that is Ste&e, %ets 7aired with all the rows of the second ta5le de7t. Similarly second record Nancy %ets 7aired with all the rows of de7t ta5le and so on. $ince this cross join produces a
Say you want to see only those records where de7t name is 2!. SELE! em7(name,de7t(name "ROM em7 ROSS YO2N de7t K)ERE de7t(name Z '2!'# On e9ecution this 6uery will return all the em7 name which are corres7ondin% to De7artment name 2! 6uery 3 : Cross 7oin wit4 )rder &y clause
Similarly you can use ORDER B clause if you want to sort the result returned 5y your 6uery in *scendin% or Descendin% order.
8OE! &ere ORDER *9 clause must be t#e last statement of a ;ER9.
ORDER B clause 5y default sorts the result in ascendin% order. But if you want to arran%e the result in Descendin% order then you ha&e to s7ecify it 5y usin% DESEND2N or DES =eyword with the ORDER B clause. SELE! em7(name,de7t(name "ROM em7 ROSS YO2N de7t K)ERE de7t(name Z '2!' ORDER B em7(name# *nd if you want to re&erse the order meanin% if you want to sort the result in Descendin% order, then you :ust need to add DES or DESEND2N =eyword after the column name of ORDER B clause SELE! em7(name,de7t(name "ROM em7 ROSS YO2N de7t K)ERE de7t(name Z '2!' ORDER B em7(name DES# &o' o Dene Primar+ Be+ Constraint
'henever we tal# about database constraint the first thing which comes into the mind is the primary #ey constraint. $o today in this tutorial we will see what is a 6rimary #ey constraint and the different ways of defining a primary #ey constraint on a table. ,#at is a Primar+ Be+ Constraint
rimary =ey constraint is the com5ination of NO! N$LL and $N2;$E constraints. 2n more 7ro7er manners you can de8ne a 7rimary =ey constraint as an 2n7utout7ut Data constraint which ser&es the 7ur7ose of uni6uely identifyin% the rows in a ta5le. (e9nition rimary =ey constraint is an 2n7utout7ut Data constraint which ser&es the 7ur7ose of uni6uely identifyin% the rows in a ta5le. +pes of Primar+ Be+
!here are two ty7es of rimary =eys1 >. Sim7le rimary =ey and /. om7osite rimary ey.
Simple 'rimary ey * 7rimary =ey constraint that is de8ned usin% only one column of a ta5le in the data5ase is called Sim7le rimary ey onstraint. Composite 'rimary ey * 7rimary =ey constraint that is de8ned usin% more than one column of a ta5le in the data5ase is called om7osite rimary =ey constraint.
rimary =ey has a sin%le function of identifyin% a uni-ue ro' in t#e table. 2n case the sim7le 7rimary =ey fails to identify that uni6ue row, the user must then de8ne a com7osite 7rimary =ey. !here are some restrictions on com7osite 7rimary =ey such as. * com7osite 7rimary =ey can 5e de8ned usin% u7 to @/ columns a ta5le in oracle data5ase. 6uic !ip )ow to ensure the uni6ueness of a 7rimary =ey constraint_ Oracle automatically creates a uni6ue inde9 so that the re6uirement of the uni6ueness of the R2M*R E constraint is ful8lled.
&o' to Create a Primar+ Be+ Constraint in Oracle Database
rimary =ey constraint can 5e de8ned 5y two ways1 >. $sin% RE*!E !*BLE statement and /. $sin% *L!ER !*BLE statement ou can de8ne a 7rimary =ey constraint either durin% the creation of a ta5le usin% RE*!E !*BLE statement or after creatin% a ta5le usin% *L!ER !*BLE statement. 2f you choose to de8ne a 7rimary =ey usin% RE*!E !*BLE statement then you ha&e two di. *t olumn Le&el /. *t !a5le Le&el.
&ets learn all the above mentioned way of defining a 6rimary Hey constraint one by one. Dene Primar+ Be+ ;sin" CREAE A*LE.
*s mentioned a5o&e we can de8ne 7rimary =ey in two ways usin% create ta5le. Let3s start with the 8rst way which is De8ne rimary =ey at olumn Le&el. $uggested 5eading1 =ow to
(e9ne 'rimary ey at Column #evel. 2n create ta5le statement you de8ne any column a 7rimary =ey column :ust 5y 7uttin% reser&ed 7hrase Qrimary =ey ri%ht after de8nin% the column. !his means you :ust ha&e to 7ut reser&ed 7hrase Qrimary ey after data ty7e and siPe in column de8nition. "or e9am7le
E9am7le >1 rimary ey at olumn Le&el RE*!E !*BLE 7roduct(master + Pro$uct0i$ 8;5*ER() PRI5AR9 BE9 roduct(name A*R)*R/+@I4, roduct(7rice N$MBER+]4 4#
*s you can see in the a5o&e code the column 7roduct(id is a 7rimary =ey column. But it is always a %ood 7ractice to %i&e a uni6ue and meanin%ful name to your 7rimary =ey constraint e&ery time you create it. !his will ma=e mana%in% of your constraint much easier. &o' to 8ame 9our Constraint in Oracle Database
2n case you do not 7ro&ide a meanin%ful name to your constraint then the oracle data5ase ser&er %i&es a default name to the constraint automatically. 2n case if you ha&e se&eral constraints on your ta5le then usin% this default name ma=es it &ery diCcult to 8nd a s7eci8c constraint. ou use =eyword QONS!R*2N! to %i&e your constraint a meanin%ful name. Let3s modify the a5o&e code and %i&e our constraint a name. E9am7le /1 )ow to name a 7rimary =ey constraint. RE*!E !*BLE 7roduct(master + Pro$uct0i$ 8;5*ER() CO8SRAI8 PRI5AR9 BE9 roduct(name A*R)*R/+@I4, roduct(7rice N$MBER+]4 4#
promstr0col0pi$0p1
!hat is how we de8ne a 7rimary =ey constraint at column le&el usin% reate !a5le Statement in Oracle data5ase. Now let3s see the second way of creatin% a 7rimary =ey. Dene Primar+ 1e+ at able Le2el.
De8nin% the 7rimary =ey constraint at ta5le le&el is one of my fa&orite ways as it hel7s me to mana%e all my constraints s7ecially when dealin% with a hu%e line of code. De8nin% a constraint at ta5le le&el se7arates the column de8nition from the constraint de8nition. 2n this way you 8rst de8ne all the columns of a ta5le and then you de8ne all your constraints in the reate !a5le Statement. "or E9am7le E9am7le @1 rimary ey onstraint at !a5le Le&el Let3s a%ain ta=e the a5o&e e9am7le and see how we can de8ne the 7rimary =ey constraint 7romstr(col>(7id(7= at ta5le le&el1 RE*!E !*BLE 7roduct(master + roduct(id N$MBER+@4, roduct(name A*R)*R/+@I4, roduct(7rice N$MBER+]4, CO8SRAI8 promstr0col0pi$0p1 PRI5AR9 BE9 (pro$uct0i$) 4# lease watch the &ideo tutorial on the same to7ic for line 5y line e97lanation of the a5o&e code. &o' to Dene Primar+ Be+ ;sin" ALER A*LE statement
ou can use *L!ER !*BLE statement to add the constraint in an already created ta5le or to chan%e the de8nition of already de8ned constraint in the ta5le. E9am7le 01 Let3s say we ha&e a ta5le Qustomer with @ columns ust(id, cust(name, 7hone(no and we don3t ha&e any rimary ey constraint on any column and now we want to add rimary ey constraint on cust(id column. !o do this tas= we will use *L!ER !*BLE statement1 *L!ER !*BLE customer *DD ONS!R*2N! cust(cid(7= R2M*R E +cust(id4# !hat is how we use *L!ER !*BLE statement to add a 7rimary =ey constraint in an already created ta5le.
&o' to Dene a Composite Primar+ Be+ Constraint
*s said a5o&e that the 7rimary =ey de8ned usin% more than one column is called a com7osite 7rimary =ey. Let3s see how to de8ne a com7osite 7rimary =ey E9am7le ]1 om7osite rimary ey RE*!E !*BLE customer + cust(id N$MBER+@4, cust(name A*R)*R/+@4, 7hone(no N$MBER+>I4, CO8SRAI8 cust0ci$0p1 4#
PRI5AR9 BE9 ( cust0i$ p#one0no)
2n the a5o&e code we create a ta5le with the name ustomer3 which has @ columns and 7rimary =ey constraint is de8ned usin% two columns cust(id and 7hone(no.
E9am7le \1 om7osite rimary ey $sin% *L!ER !*BLE Similar to sim7le 7rimary =ey you can add a com7osite 7rimary =ey to an already created ta5le usin% *L!ER !*BLE statement. *L!ER !*BLE customer *DD ONS!R*2N! cust(cid(7= R2M*R E +cust(id,7hone(num5er4#
&o' o Enable an$ Disable a Primar+ Be+ Constraint
2f you were wonderin% what is the re6uirement of %i&in% a name to a constraint then here is the answer. Modifyin% a constraint 5ecomes easier if your constraint has a uni6ue and meanin%ful name. 2f you do not 7ro&ide a name to your constraint then oracle ser&er %i&es a default name to it which is not &ery meanin%ful and it 5ecomes diCcult to 8nd a s7eci8c constraint usin% that name. So let3s modify the 7rimary =ey constraint and see how to ena5le or disa5le it usin% the name of the constraint. E9am7le ^1 Ena5le or Disa5le 7rimary =ey. "or e9am7le let3s say you want to disa5le cust(cid(7= constraint which we earlier de8ned on the ustomer ta5le. Disa5le rimary =ey constraint *L!ER !*BLE customer D2S*BLE ONS!R*2N!
cust(cid(7=#
2f you want to ena5le this constraint then sim7ly write
Ena5le rimary =ey constraint *L!ER !*BLE customer EN*BLE ONS!R*2N!
cust(cid(7=#
&o' to C#ec1 Constraint on A able
*s we =now that DES statement shows nothin% a5out constraints on a ta5le. But Oracle Data5ase 7ro&ides us se&eral D*!* D2!2ON*R2ES for chec=in% or descri5in% all the constraints which we ha&e de8ned on our ta5le. !hese Data Dictionaries are •
$SER(ONS!R*2N!S
•
$SER(ONS(OL$MNS
Khere $SER(ONS!R*2N!S %i&es a 5rief a5out constraint on ta5le. $SER(ONS(OL$MNS is a Data Dictionary which holds detailed information a5out columns of a ta5le. Let3s see how to use them to %et information a5out the 7rimary =ey constraint. >or eample lets say you want to see constraint details on customer table ,SER;C)+S!R%+!S SELE! ONS!R*2N!(N*ME, ONS!R*2N!(!E, !*BLE(N*ME, S!*!$S, 2NDE(N*ME "ROM user(ONS!R*2N!S
K)ERE
ta5le(name Z '$S!OMER'#
,SER;C)+S;C)#,"+S SELE! ONS!R*2N!(N*ME, !*BLE(N*ME, OL$MN(N*ME, OS2!2ON "ROM user(cons(columns
K)ERE
ta5le(name Z '$S!OMER'#
Restrictions on a primar+ 1e+ constraint
>. ou cannot delete a rimary =ey if it is referenced 5y a forei%n =ey in some other ta5le. /. * ta5le can ha&e only One rimary =ey no matter whether its Sim7le rimary ey Or om7osite rimary ey. @. olumns which are 7artici7atin% in rimary ey cannot ha&e N$LL &alues. !his means you cannot lea&e them unattended or you cannot 7ut N$LL &alue into them. 0. *s 7rimary =ey is all a5out Row or Record3s uni6ueness thus it will not allow du7licate &alues. ]. Khen a rimary ey constraint has 5een de8ned on multi7le columns then its &alues can 5e du7licated 7ro&ided the du7lication is ha77enin% within one sin%le column. )owe&er the com5ination of &alues of all the columns de8nin% each 7rimary =ey constraint should 5e uni6ue. \. Data-ty7es such as LOB, LON, LON R*K, A*RR*, NES!ED !*BLE, B"2LE, RE", !2MES!*M K2!) !2ME ONE, or user-de8ned ty7e are not allowed with the columns which are 7art of rimary =ey. *ny attem7t of creatin% a 7rimary =ey with the column of these data-ty7es will raise S;L Error1 OR*-I//\b. ^. !he siPe of the 7rimary =ey cannot e9ceed a77ro9imately one data5ase 5loc=. . *s 2 ha&e already mentioned a5o&e that a com7osite 7rimary =ey can ha&e @/ columns ma9imum. b. !he rimary =ey and $ni6ue =ey should ne&er 5e desi%nated as the same column or com5ination of columns. >I.ou cannot s7ecify a 7rimary =ey when creatin% a su5 &iew in an inheritance hierarchy. !he 7rimary =ey can 5e s7eci8ed only for the to7-le&el +root4 &iew. >>.$ni6ue cluster 2nde9 %ets created automatically on the time creatin% rimary =ey. >/.*lthou%h it is not necessary for you to de8ne a 7rimary =ey yet it is always recommended to do so. $o thats all about 65*M058 H8. =ope it gives you a detailed insight into the concept. 8ou can visit my 8ou!ube channel for tutorials on 6rimary Hey. (o li#e and $ubscribe to my videos. !han#s for reading K have a great day@
"orei%n ey. Oracle Database orei!n "e# Explained in Detail *n the last tutorial we learnt about 6rimary #ey constraint and today 0nother topic in oracle database that * would li#e to elaborate is referential integrity constraint or >oreign Hey. orei-n =e* is an npt@otpt data constraint wic is also =nown as referential inte-rit* constraint . >oreign #ey represent a lin# or say a relationship between columns of tables. $imilar to primary #ey constraint >oreign Hey constraint is also of two types.
>. Sim7le "orei%n =ey constraint and /. om7osite "orei%n =ey constraint. onstraint which in&ol&es only one column in forei%n =ey in child ta5le and one column in reference =ey in 7arent ta5le is called Simple Forei"n Be+. Khile the constraint which in&ol&es more than one column in forei%n =ey in child ta5le and more than one column in reference =ey in the 7arent ta5le is called Composite Forei"n Be+. !here are few things which you should #now about foreign #ey : >eatures of >oreign Hey; >. ou cannot de8ne a forei%n =ey constraint in a RE*!E !*BLE statement that contains an *S su5 6uery clause. 2nstead, you must create the ta5le without the constraint and then add it later with an *L!ER !*BLE statement /. None of the columns in the forei%n =ey can 5e of LOB, LON, LON R*K, A*RR*, NES!ED !*BLE, B"2LE, RE", !2MES!*M K2!) !2ME ONE, or userde8ned ty7e. )owe&er, the 7rimary =ey can contain a column of !2MES!*M K2!) LO*L !2ME ONE. @. * com7osite forei%n =ey cannot ha&e more than @/ columns. 0. Referenced =ey in 7arent ta5le must either 5e rimary ey or $ni6ue ey. ]. Records in 7arent ta5le cannot 5e u7dated if child record e9ist. \. "orei%n ey onstraint can 5e s7eci8ed on child ta5le not on 7arent ta5le >oreign #ey involves two different tables. >irst one is *A+%!, ,A-% or referenced !able and second one is &/#0 ,A-% or foreign table.
>. !he column of 7arent ta5le which will %et referenced 5y forei%n =ey must 5e either rimary ey or $ni6ue ey.
/. olumn+s4 in child ta5le can contain N$LL or Du7licate &alues while &ice &ersa is not true and. @. olumn+s4 of 7arent ta5le column+s4 of child ta5le which are 7artici7atin% in forei%n =ey should 5e of same data-ty7e and siPe +column width4.
&o' to create Dene Forei"n 1e+ ou can de8ne forei%n =ey constraint either 5y usin% RE*!E !*BLE statement or 5y usin% *L!ER !*BLE statement.
>. De8nin% "orei%n ey $sin% reate ta5le at olumn Le&el !his way of de8nin% constraint is called column le&el 5ecause we de8ne constraint with column de8nition while creatin% ta5le. S+ntax olumn(name Dataty7e+siPe4 7arent(ta5le(name +7arent(column(name4
RE"ERENES
"or e9am7le !o demonstrate this we will use two ta5les 7arent ta5le with the name of ut4ors and child ta5le with the name of &oos arent ta5le authors is a sim7le ta5le with / Author_name. Khere *uthor(id is a rimary =ey column.ou columns Author_id and can add as many column as you want. Read how to create ta5le and how to de8ne7rimary =ey on a ta5le. RE*!E !*BLE author + author(id N$MBER+@4 ONS!R*2N! athr(aid(7= R2M*R E, author(name A*R)*R/+@I4 4# Now lets create our child table 7""H$. !he structure of this table contain columns boo#Aid which will be the primary #ey for this table, boo#Atitle and 7oo#Aprice and the Gth column will be boo#AauthorAid this column will be the foreign #ey which will reference the authorAid column of author table you can give whatever name to this column but data-type and the size :column width; of this column must be the same as of authorAid column in author table. RE*!E !*BLE 5oo=s + 5oo=(id N$MBER+@4, 5oo=(title A*R)*R/+@I4, 5oo=(7rice N$MBER+@4, boo10aut#or0i$ 8;5*ER() REFERE8CES aut#or(aut#or0i$) 4#
CO8SRAI8
bo10ai0f1
/.)ow to de8ne forei%n =ey usin% RE*!E !*BLE at ta5le Le&el. !o de8ne forei%n =ey $sin% create ta5le at ta5le le&el. ou ha&e to de8ne all the column of your child ta5le 8rst then you ha&e to de8ne forei%n =ey at the end of the ta5le. Let3s see the syntax 8rst ONS!R*2N! RE"ERENES
constraint(name "ORE2N E+child(ta5le(column4 arent(ta5le(name+7arent(ta5le(column4
!o demonstrate how to define foreign #ey using create table at table level *ll recreate our child table $OO"S .
RE*!E !*BLE 5oo=s + 5oo=(id N$MBER+@4 ONS!R*2N! 5o=(5i(7= R2M*R E, 5oo=(title A*R)*R/+@I4, 5oo=(7rice N$MBER+@4, 5oo=(author(id N$MBER+@4, CO8SRAI8 bo10ai0f1 FOREI>8 BE9 (boo10aut#or0i$) REFERE8CES aut#or(aut#or0i$) 4#
*s you can see 2 de8ned all the column 8rst and then 2 de8ne forei%n ey constraint in the last statement of reate ta5le.
Dene Forei"n Be+ ;sin" ALER A*LE statement. Ke de8ne "orei%n ey throu%h *L!ER !*BLE statement when we already ha&e a ta5le and then we want to em7hasiPe the constraint o&er that Su77ose we ha&e a sim7le ta5le with 7rimary =ey 5y the name of BOOS Ulease watch my )ow to create ta5le tutorialV and now you ha&e em7hasiPe the "ORE2N E constraint on this ta5le. 2n this scenario we will use *L!ER !*BLE statement. Let3s how "irst see the synta9 *L!ER !*BLE RE"ERENES
child(ta5le(name *DD "ORE2N E 7arent(ta5le(name+7arent(column4
+child(column4
"or E9am7le *L!ER !*BLE 5oo=s *DD ONS!R*2N! 5o=(ai(f= +5oo=(author(id4 RE"ERENES author+author(id4#
"ORE2N E
!hat3s all you ha&e to do. f *o tr* to delete parent table wic is a0in- primar* or ni?e =e* wic was referenced b* cild table ten oracle will -i0e *o a "1L Error/ 1+A32445.,t *o can still drop cild table witot an* error.
On Delete Set Null.
On Delete Set %ull clause of orei!n "e# Explained in Detail 0s we discussed in our last tutorial that >oreign #ey constraint establishes a lin# P relation between &'(E%) and C*+,D table. 7ecause of this lin# we cannot pdate or delete the rows of parent table. >oreign #ey is defined in the child table and parent table contains the reference column. 7y default "racle engine is set to #N DELE'E N# %'#N clause when you define a simple foreign #ey. !his means that you are allowed to update the rows in parent table however you cannot delete rows from parent table.
*cti&ity
o !o my 7re&ious tutorial on forei%n =ey and create 7arent +authors4 and child +5oo=s4 ta5le with forei%n =ey and after creatin% them try to dro7 the 7arent +authors4 ta5le and see what will ha77en. +!o dro7 7arent ta5le *$!)ORS e9ecute DROP A*LE A;&ORS: ddl4 5ead how to define foreign #ey.
!his default behavior is called (estrict rule. !his 5estrict rule doesnt allow user to delete or update reference data in parent table. 7ut we can change this default behavior of oracle, either to "E' N$LL or to DEL'E %"%DE by
>. ON DELE!E SE! N$LL or /. ON DELE!E *S*DE Referential action. Let3s see how "irst we will see O8 DELEE SE 8;LL. +emember parent table holds +%6%+%!&% &1UM! and &hild table holds 61+%#$! 7%8 &1UM!.
'hen you change default restrict rule :"N (&! N" 0
)ow to set ON DELE!E SE! N$LL clause with forei%n =ey. arent ta5le1 ut4ors Note1 arent ta5le is sim7ly a ta5le with 7rimary =ey constraint on *uthor id column which in turn is the reference column. RE*!E !*BLE author + author(id N$MBER+@4 ONS!R*2N! author(name A*R)*R/+@I4 4#
athr(aid(7=
R2M*R E,
hild ta5le1 &oos RE*!E !*BLE 5oo=s + 5oo=(id N$MBER+@4, 5oo=(title A*R)*R/+@I4, 5oo=(7rice N$MBER+@4, 5oo=(author(id N$MBER+@4 ONS!R*2N! 5o=(ai(f= RE"ERENES author+author(id4 ON DELE!E SE! N$LL 4# !his is how you de8ne forei%n =ey with O8 DELEE SE 8;LL with create ta5le in column le&el.
8ou can chec# this constraint by eecuting the query - $"E&9#N"'&%N'" data dictionary
SELE! constraint(name, delete(rule "ROM user(constraints K)ERE ta5le(name Z 'BOOS' #
On Delete ascade. On Delete cascade clause of orei!n "e# Explained in Detail 7efore jumping ahead *ll suggest you to read and watch my tutorials on foreign #ey and "n (elete $et Null clause for a better understanding of this topic.
"n (elete oreign Hey for more information. &ets create parent table 0uthors
RE*!E !*BLE author + author(id N$MBER+@4 ONS!R*2N! author(name A*R)*R/+@I4 4#
athr(aid(7=
R2M*R E,
5ead =ow !o (efine 6rimary Hey /sing
RE*!E !*BLE 5oo=s + 5oo=(id N$MBER+@4, 5oo=(title A*R)*R/+@I4, 5oo=(7rice N$MBER+@4, 5oo=(author(id N$MBER+@4 ONS!R*2N! author+author(id4 ON DELE!E *S*DE 4#
5o=(ai(f=
RE"ERENES
&ename 'able. (ename table usin! S-, alter table statement Explained in Detail $ometimes we need to rename our table after we have already created it. *n such cases there are two ways in which you can rename your table1
). (rop the whole table altogether and recreate it with the new name. !his is a logical choice if you are a student using your table for practice purposes only and can afford to lose the valuable data that your table contains. *n practical environment there is a 7*4 N" for this method as (ata held by database in tables is valuable and we cannot afford to lose it. *f you opt for this method then for you * have a disclaimer 3 A do not recommend tis metod of renamin- a table. f *o opt tis metod ten *o will be responsible for *or own loss.B
+. $econd way of renaming a table is by using 0&!5 !07& data manipulation language :((&; statement. !his is the recommended way of renaming a table. &ets see how. *n order to demonstrate how to rename a table using $%& 0&!5 !07& ((& statement we will create a very simple table by the name of ,%S, !est table will have only one column test'name . 8ou can define as many columns as you want but since we are concentrating over the renaming of the table therefore * am ta#ing into account only one column. <50! !07& test : testAname V05<=05+:)O; ;D
&ets say we want to change the name of this table from ,%S, to %9AM*% for that we have to eecute an A,%+ ,A-% statement. >irst lets see the synta of 0&!5 !07& statement "*ntax
0&!5 !07&
old_name_of_table
5N0M !"
new_name_of_tableD
Now lets change the name of our table from !$! to J0M6& according to above synta. 0&!5 !07&
test
5N0M !"
exampleD
*dd column to an e9istin% !a5le. 'dd column to an existin! table usin! S-, alter table statement Explained in Detail !his is the second tutorial in the alter table series. *n the last tutorial we saw =ow to rename a table using 0&!5 !07& statement. =as it ever happened with you that after creating a table, you realized that you need to add
another column to it? *t generally happens with me. !he first thing which comes into the mind in this scenario is to (rop the table and recreate it with that etra column. 7ut what if your table has a data which you cannot compromise. Means dropping a table is not the solution here. (ont worry there is a solution for this problem. *t is possible to add a column to an eisting table using %L'E& '%,LE statement. 7y /sing 0&!5 !07& statement you can add one or more than one columns to an eisting table. &ets see how. *n the previous tutorial we have created a very simple table by the name of )est . )est table has only one column test_name.
DES test Name -------- !ES!(N*ME
Null ----
!y7e -----------A*R)*R/+>]4
Say you want to add another column test;id to this ta5le. !est(id column will 5e of 8;5*ER data ty7e and column width +siPe of column4 will 5e @. &ets see how to add test_id column to )est table using alter table statement.
2n order to add test(id column to our e9istin% ta5le !est usin% alter ta5le we ha&e to 8rst understand the synta9 of the alter ta5le statement. S+ntax *L!ER !*BLE
e9istin%(ta5le(name
ADD
new(column(name
data(ty7e +siPe4#
8ote #ere 'e $i$nGt 'rite
Do not write *DD OL$MN after e9istin% ta5le name otherwise Oracle will raise ORA%H! ! in2ali$ i$entier S;L error. =ope synta is clear. $o now lets write a $%& query for adding test'id column to ,est table.
*L!ER !*BLE
test
*DD
test(id
N$MBER+@4#
On e9ecutin% the a5o&e 6uery you will %et a test(id column in the already created ta5le !est. )ow !o add Multi7le olumns 2n a !a5le.
Similarly you can add multi7le columns to an e9istin% column. "or that you :ust ha&e to 7ut your columns de8nitions in a 7arenthesis after eyword *DD. Synta9 for addin% multi7le columns to an e9istin% ta5le *L!ER !*BLE e9istin%(ta5le(name *DD + column(name> Data!y7e+siPe4, column(name/ Data!y7e+siPe4, ?? column(name n Data!y7e+siPe4 4#
)ow to dro7 a column in a ta5le. Similarly you can dro7 a column from your e9istin% ta5le usin% alter ta5le command. Say you want to dro7 test(name column from Test ta5le. Synta9 *L!ER !*BLE
e9istin%(ta5le(name
DRO OL$MN
column(name#
e.%. *L!ER !*BLE
test
DRO OL$MN
test(name#
Rename and Modify column of a ta5le. (ename and Modif# column of a table usin! S-, alter table statement Explained in Detail !his is the third tutorial in 0&!5 table series. !ill now in previous tutorials we have seen
>. )ow to rename ta5le usin% *L!ER !*BLE and /. )ow !o add delete a column from an e9istin% ta5le usin% *L!ER !*BLE. 2n this tutorial we will see how to rename a columns of a ta5le and how to modify one or more columns of a ta5le. )ow to rename a column of a ta5le. S+ntax *L!ER !*BLE ta5le(name new(name(of(column#
REN*ME OL$MN
old(name(of(column
!O
"or e9am7le 2n the 7re&ious tutorial we ha&e created a sim7le ta5le 5y the name of !ES! so we
will 5e usin% that for this e9am7le. !a5le test has only one column test(name. Structure of ta5le !est is as follows1 DES test Name Null !y7e ----------------- !ES!(2D N$MBER+@4 Say you want to rename the column test(id to col> *L!ER !*BLE test REN*ME OL$MN test(id !O col># On e9ecutin% the a5o&e 6uery the column test(id of ta5le test will 5e renamed to col>. ou can chec= it 5y descri5in% the structure of !ES! ta5le. DES test Name Null !y7e -----------------OL> N$MBER+@4 )ow !o modify the column de8nition usin% *L!ER !*BLE. *5o&e we saw how to rename a column of a ta5le. Now we will see how to modify the column de8nition of a ta5le. By modifyin% the column de8nition, what 2 mean is to chan%e the Data ty7e and column width of the column. S+ntax *L!ER !*BLE ta5le(name MOD2" colun(name data(ty7e +siPe4# ample1 0bove we renamed column testAid to col).
*L!ER !*BLE
test
MOD2"
col>
A*R)*R/+@I4#
ou can &erify the chan%e 5y descri5in% the structure of the test ta5le 5y DESR2BE command DES test Name Null !y7e -----------------OL> A*R)*R/+@I4 haracter Mani7ulation "unctions S;L Su5str "unction.
0fter $%&
S+ntax! Substr ( source_string, start_pos, Substr_length )
*s we can see S;L su5str function ta=es @ 7arameters. First one isSource strin" from which you want to e9tract the se%ment.Secon$ 7arameter is Startin" position for su5 strin% from the Source strin%. *nd thet#ir$ 7arameter is Substr0len"t# which is the len%th for the su5strin%. First two parameters are MANDATR! to speci"# while third one is $T%NA& .
So we can say. S;L Su5str function will return a su5 strin% of a s7eci8ed len%th from the source strin% 5e%innin% at a %i&en 7osition. "irst 7arameter source strin% can 5e of any data ty7e )*R, A*R)*R/, N)*R, NA*R)*R/, LOB, or NLOB and 5oth start(7os, Su5str(len%th 7arameters must 5e num5er data ty7e. !he returnin% result of S;L Su5str function is of same data ty7e of source strin%. Let3s see an e9am7le of S;L Su5str function. S;L SELE! su5str+ 'www.Re5ellionRider.com', ], >0 4 "ROM dual# )ere in this 6uery url of my we5site www.Re5ellionRider.com is our source strin% with the total len%th of // characters. 2 want to e9tract the name of my we5site which is Re5ellionRider. So if you count the total len%th of the name of the we5site, it is >0 !hat3s why 2 ha&e s7eci8ed >0 as my third 7arameter of S;L Su5str function which is su5str(len%th. *lso the name of the we5site Re5ellionRider is startin% from 7osition ]th therefore 2 ha&e s7eci8ed ] at second 7arameter of S;L su5str function which is strt(7os startin% 7osition. On E9ecution the out7ut of this e9am7le will 5e the name of the Ke5site 7RebellionRi$er7.
Scenarios !here are few scenarios related to S;L Su5str function such as -
"irst scenario
,#en startin" position is lar"er t#an t#e len"t# of source strin".
2n this case S;L Su5str function will return +,## as a result. Let3s do an e9am7le. SELE! su5str+ 'www.Re5ellionRider.com', /@, >0 4 "ROM dual# *s you can see here 2 ha&e s7eci8ed /@ at startin% 7osition and the total len%th of our source strin% is // characters. On e9ecutin% this 6uery we will %et N$LL in return.
Second scenario ,#en t#e Substr0len"t# is "reater t#an source strin"
2n this case the se%ment return is the su5strin% from startin% 7osition to the end of the Strin%. "or e9am7le SELE! su5str+ 'www.Re5ellionRider.com', ], /@ 4 "ROM dual# Our startin% 7osition is at ] means at the 8rst R ofRebellionRider and len%th of su5strin% is set to /@ which is %reather than the len%th of source strin% that is //. On e9ecutin% this 6uery will %et a su5strin% from 8rst R ofRebellionRider till the end of the source Strin%. !hird scenario ,#en +ou suppl+ numeric or arit#metic expression or a Data as Source strin" to SL Substr function 2n this scenario 2f you ha&e su77lied a numeric strin% instead of character as source strin%, the oracle en%ine casts them as a character when they occur as 7arameter to S;L Su5str function. *nd if you ha&e su77lied Arithmetic e'pression or aDAT then !he Oracle en%ine 8rst sol&es or e&aluates the Arithmetic e'pression or the DAT . it casts them as a character. Means if you ha&e arithmetic e97ression in your source strin% then oracle will 8rst sol&e it and then chan%e or say cast the &alue of its result into character. Let3s see some e9am7le.
SELE! su5str+ ]IIII-^, /, 0 4 "ROM dual# Oracle 8rst e&aluates the arithmetic e97ression that is ]IIII-^ e6ual to 0bbb@.*nd then oracle en%ine casts this result 0bbb@ into a character strin%. Means 0bbb@ will 5e a ] characters strin%. Startin% 7osition of su5strin% is /, that means from the 8rst b of 0bbb@ Ke s7eci8ed the len%th of su5strin% is 0 so we must %et bbb@ as our result. Let3s chec= e9ecute )ere is our result Similarly if you %i&e a data as your source strin% it 8rst %ets e&aluated and then %ets casted Let3s do an e9am7le
SELE! Su5str + sysdate, 0, @4 "ROM dual# *5o&e 6uery 8rst e&aluates the SSD*!E function and then con&erts the date returned into a character strin%. *ssume that the current system date is I/-*R->0. !he search for the su5strin% 5e%ins at 7osition 0 and the three characters from that 7osition onward are e9tracted, yieldin% the su5strin% *R.
"ourth scenario ,#en Startin" position (start0pos) is set o (Jero) 2n this scenario when user sets the startin% 7osition which is the second ar%ument of S;L Su5str function to I +ero4 then the oracle en%ine treats this Pero as One and starts searchin% or say e9tractin% su5 strin% from the startin% of Source strin%. "or e9am7le
SELE! su5str+ 'www.Re5ellionRider.com', I, >0 4 "ROM dual# 2n the a5o&e 6uery we set the startin% 7osition for our su5 strin% on ero and the Len%th of su5strin% on >0. !his means !he search for the su5strin% 5e%ins at 7osition > that is from the 8rst 3w3 of www3 and the >0 characters from that 7osition onward are e9tracted, yieldin% the su5strin% www.Re5ellionR3 "ifth scenario ,#en Startin" position (start0pos) is set o a 8e"ati2e number 2n this scenario when user sets startin% 7osition of the second ar%ument of S;L Su5str function to a ne%ati&e num5er say -@ then oracle en%ine counts 5ac=ward from the end of the source strin% "or e9am7le
SELE! su5str+ 'www.Re5ellionRider.com', -b, ] 4 "ROM dual# )ere searchin% of su5 strin% will start from the character R of QRider as character R is at the bth 7osition from the end of the source strin%. !hen 8&e characters from that 7osition onward are e9tracted, yieldin% the su5strin% R2DER3. Si9th scenario '#en t#e len"t# of substrin" (substr0len"t#)is omitte$ 2 ha&e already mentioned a5o&e that the !hird ar%ument of S;L Su5str function is o7tional, which means that it3s not mandatory for you to s7ecify the len%th of su5strin%. 2n case the user does not s7ecify len%th for Su5 strin% then Oracle en%ine returns all characters to the end of source strin%. "or e9am7le
SELE! su5str+ 'www.Re5ellionRider.com', ] 4 "ROM dual#
*s you can see 2 didn3t s7ecify the third ar%ument of S;L Su5str function which is Su5str(len%th +su5 strin% len%th4 in the a5o&e 6uery. 2n this case oracle en%ine will start searchin% su5strin% from the s7eci8ed 7osition which is ] in our 6uery and all the characters till the end from that 7osition onward are e9tracted, ieldin% the su5strin% QRe5ellionRider.com .
haracter Mani7ulation "unctions S;L oncat "unction. 0s we have already seen the hierarchy of $%& functions in my previous article $%&
2n oracle, the S;L oncat +4 function ta=es only two ar%uments. !hese ar%uments can 5e of any data-ty7es )*R, A*R)*R/, N)*R, NA*R)*R, and LOB or NLOB. ou can e&en s7ecify the columns of the ta5le here. For example
Say if you want to retrie&e full name of em7loyee from em7loyees ta5le then you can use S;L oncat +4 function as S;L SELE! oncat +8rst(name, last(name4 "ROM em7loyees# )ere rst_name andlast_name are two columns ofhr- emplo#ees ta5le. !he out7ut of this 6uery will 5e the full name of em7loyee without any s7ace 5etween 8rst name and last name. *nd if you want to concatenate two strin%s then you can s7ecify two se7arate character strin%s as ar%uments of S;L oncat function. "or e9am7le S;L SELE! concat +'hello ','world'4"ROM dual# *s you can see we ha&e used two se7arate character strin%s ./ello0 and.1orld20 here as ar%uments to our SL concat function . On e9ecution the out7ut of this 6uery will 5e Hello orld= So from the a5o&e two e9am7les it3s clear that S;L concat function returns a single string which is a com5ined strin% of 7arameter Strin%(> and Strin%(/.
SL concat function is e-ui2alent to SL concat operator es, it is true that 5oth S;L concat function and S;L concat o7erator 7erform the same tas=. !hen 6uestion arises here that '#at is t#e $iKerence bet'een SL concat function an$ SL concatenation operator Both S;L concat function and S;L concatenation o7erator ha&e their ad&anta%es and disad&anta%es. ,#ile SL concat function ta1es onl+ t'o parameters SL concat operator can be repeate$ as often as is necessar+. 0s we have seen above that we can only provide two arguments to $%& concat function neither more than two nor less than two but with $%& concatenation operator scenario is totally different. 'ith $%& concatenation operator you can combine as man y strings as you want
&o' to combine more t#an t'o strin"s usin" SL concat function Now you must 5e thin=in% that &o' to combine more t#an t'o strin"s usin" SL concat function t#at can #a2e onl+ t'o parameters *lthou%h S;L concat ta=es only two 7arameters you can still com5ine more than two strin%s. But for that you will ha&e to use the conce7t of Nestin% S;L concat function. )ow_ Let me e97lain. Say you want to concatenate the strin% - >Hi= How are you?@ usin% S;L concat function. "or that we will 5e nesting concat function. Let3s see how. SELE! ON*!+ON*!+ON*!+ON*!+ ON*!+ON*!+ON*! +')i',''4,')ow'4,' '4,'are'4,' '4,'ou'4,'_'4 "ROM dual# *s you can see here that :ust to concat a sin%le strin% AHi= How are you?B Ke ha&e to use se&en concat functions. !han=s to S;L de&elo7er who hel7s us in remem5erin% all the 7arenthesis
7hewww 2ncidentally this tas= can 5e achie&ed muc# easil+ if you useS3& concatenation operator . Let3s see how SELE! ')i'WW' 'WW')ow'WW' 'WW'are'WW' 'WW'you'WW'_' "ROM dual#
%rci0in- in #racle Database explained. :at is arci0in- in oracle databaseC !he configuration of a database at the time of its creation is necessary in order to easily recover your data in case of any occurrence of system failure. "nline redo log files are the ones that contain the complete record of any changes that were made to the primary data files. !hese online redo log files are stored in online redo log groups. *t is important to have at least two online redo log groups for your database. "nce an online redo log files belonging to a group have been filled, the log writer process :&4'5; switches to a new group for continuing the writing process of redo log records. !he inactive group of online redo log files can be automatically saved by the "racle database at one or multiple offline destinations. !hese destinations are collectively addressed as archived redo log or the archive log. !his process of converting online redo log files into archived log is called 'rc.ivin! . !he process of archiving can only be performed when the database is running in the 05<=*V&"4 mode. *t is important to note that unless the group of online redo log files is archived, they cannot be reused by the log writer :&4'5;. *n case the &4'5 switches to a new group and leaves the previous one inactive for some time then that inactive group becomes readily available for immediate reuse by the &4'5 process provided the database is running in N"05<=*V&"4 mode.
0 database instance failure could be avoided in N"05<=*V&"4 mode however this mode does not protects the database from media failure. !he most recent changes made to the database that is stored in the online redo log files are only ones available for instance recovery. !herefore in order to restore the database operating in N"05<=*V&"4 mode, you can only use the
