CreatingOtherSchemaObjectsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:CreatesimpleandcomplexviewsRetrievedatafromviewsCreate,maintain,andusesequencesCreateandmaintainindexesCreateprivateandpublicsynonymsLessonAgendaOverviewofviews:Creating,modifying,andretrievingdatafromaviewDatamanipulationlanguage(DML)operationsonaviewDroppingaviewOverviewofsequences:Creating,using,andmodifyingasequenceCachesequencevaluesNEXTVALandCURRVALpseudocolumnsOverviewofindexesCreating,droppingindexesOverviewofsynonymsCreating,droppingsynonymsDatabaseObjectsLogicallyrepresentssubsetsofdatafromoneormoretablesViewGeneratesnumericvaluesSequenceBasicunitofstorage;composedofrowsTableGivesalternativenamestoobjectsSynonymImprovestheperformanceofdataretrievalqueriesIndexDescriptionObjectWhatIsaView?EMPLOYEEStableAdvantagesofViewsTorestrictdataaccessTomakecomplexquerieseasyToprovidedataindependenceTopresentdifferentviewsofthesamedataSimpleViewsandComplexViewsYesNoNoOneSimpleViewsYesContainfunctionsYesContaingroupsofdataOneormoreNumberoftablesNotalwaysDMLoperationsthroughaviewComplexViewsFeatureCreatingaViewYouembedasubqueryintheCREATE

VIEWstatement:ThesubquerycancontaincomplexSELECTsyntax.CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY[CONSTRAINTconstraint]];CreatingaViewCreatetheEMPVU80view,whichcontainsdetailsoftheemployeesindepartment80:DescribethestructureoftheviewbyusingtheiSQL*PlusDESCRIBEcommand:DESCRIBEempvu80CREATEVIEW empvu80ASSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=80;CreatingaViewCreateaviewbyusingcolumnaliasesinthesubquery:Selectthecolumnsfromthisviewbythegivenaliasnames.CREATEVIEW salvu50ASSELECTemployee_idID_NUMBER,last_nameNAME,salary*12ANN_SALARYFROMemployeesWHEREdepartment_id=50;SELECT*FROMsalvu50;RetrievingDatafromaViewModifyingaViewModifytheEMPVU80viewbyusingaCREATE

OR

REPLACE

VIEWclause.Addanaliasforeachcolumnname:ColumnaliasesintheCREATE

OR

REPLACE

VIEWclausearelistedinthesameorderasthecolumnsinthesubquery.CREATEORREPLACEVIEWempvu80(id_number,name,sal,department_id)ASSELECTemployee_id,first_name||''||last_name,salary,department_idFROMemployeesWHEREdepartment_id=80;CreatingaComplexViewCreateacomplexviewthatcontainsgroupfunctionstodisplayvaluesfromtwotables:CREATEORREPLACEVIEWdept_sum_vu(name,minsal,maxsal,avgsal)ASSELECTd.department_name,MIN(e.salary),MAX(e.salary),AVG(e.salary)FROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id)GROUPBYd.department_name;RulesforPerforming

DMLOperationsonaViewYoucanusuallyperformDMLoperationson

simpleviews.Youcannotremovearowiftheviewcontainsthefollowing:GroupfunctionsAGROUP

BYclauseTheDISTINCTkeywordThepseudocolumnROWNUMkeywordRulesforPerforming

DMLOperationsonaViewYoucannotmodifydatainaviewifitcontains:GroupfunctionsAGROUP

BYclauseTheDISTINCTkeywordThepseudocolumnROWNUMkeywordColumnsdefinedbyexpressionsRulesforPerforming

DMLOperationsonaViewYoucannotadddatathroughaviewiftheviewincludes:GroupfunctionsAGROUP

BYclauseTheDISTINCTkeywordThepseudocolumnROWNUMkeywordColumnsdefinedbyexpressionsNOT

NULLcolumnsinthebasetablesthatarenotselectedbytheviewUsingtheWITH

CHECK

OPTIONClauseYoucanensurethatDMLoperationsperformedontheviewstayinthedomainoftheviewbyusingtheWITH

CHECK

OPTIONclause:

AnyattempttoINSERTarowwithadepartment_idotherthan20,ortoUPDATEthedepartmentnumberforanyrowintheviewfailsbecauseitviolatestheWITH

CHECK

OPTIONconstraint.CREATEORREPLACEVIEWempvu20ASSELECT *FROMemployeesWHEREdepartment_id=20WITHCHECKOPTIONCONSTRAINTempvu20_ck;DenyingDMLOperationsYoucanensurethatnoDMLoperationsoccurbyaddingtheWITH

READ

ONLYoptiontoyourviewdefinition.AnyattempttoperformaDMLoperationonanyrowintheviewresultsinanOracleservererror.CREATEORREPLACEVIEWempvu10(employee_number,employee_name,job_title)ASSELECT employee_id,last_name,job_idFROMemployeesWHEREdepartment_id=10WITHREADONLY;DenyingDMLOperationsRemovingaViewYoucanremoveaviewwithoutlosingdatabecauseaviewisbasedonunderlyingtablesinthedatabase.DROPVIEWview;DROPVIEWempvu80;Practice11:OverviewofPart1Thispracticecoversthefollowingtopics:CreatingasimpleviewCreatingacomplexviewCreatingaviewwithacheckconstraintAttemptingtomodifydataintheviewRemovingviewsLessonAgendaOverviewofviews:Creating,modifying,andretrievingdatafromaviewDMLoperationsonaviewDroppingaviewOverviewofsequences:Creating,using,andmodifyingasequenceCachesequencevaluesNEXTVALandCURRVALpseudocolumnsOverviewofindexesCreating,droppingindexesOverviewofsynonymsCreating,droppingsynonymsSequencesLogicallyrepresentssubsetsofdatafromoneormoretablesViewGeneratesnumericvaluesSequenceBasicunitofstorage;composedofrowsTableGivesalternativenamestoobjectsSynonymImprovestheperformanceofsomequeriesIndexDescriptionObjectSequencesAsequence:CanautomaticallygenerateuniquenumbersIsashareableobjectCanbeusedtocreateaprimarykeyvalueReplacesapplicationcodeSpeedsuptheefficiencyofaccessingsequencevalueswhencachedinmemory12435687109CREATE

SEQUENCEStatement:

SyntaxDefineasequencetogeneratesequentialnumbersautomatically:CREATESEQUENCEsequence[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}];CreatingaSequenceCreateasequencenamedDEPT_DEPTID_SEQtobeusedfortheprimarykeyoftheDEPARTMENTStable.DonotusetheCYCLEoption.CREATESEQUENCEdept_deptid_seqINCREMENTBY10STARTWITH120MAXVALUE9999NOCACHENOCYCLE;NEXTVALandCURRVALPseudocolumnsNEXTVALreturnsthenextavailablesequencevalue.Itreturnsauniquevalueeverytimeitisreferenced,evenfordifferentusers.CURRVALobtainsthecurrentsequencevalue.NEXTVALmustbeissuedforthatsequencebeforeCURRVALcontainsavalue.

UsingaSequenceInsertanewdepartmentnamed“Support”inlocationID2500:ViewthecurrentvaluefortheDEPT_DEPTID_SEQsequence:INSERTINTOdepartments(department_id,department_name,location_id)VALUES(dept_deptid_seq.NEXTVAL,'Support',2500);SELECT dept_deptid_seq.CURRVALFROM dual;CachingSequenceValuesCachingsequencevaluesinmemorygivesfasteraccesstothosevalues.Gapsinsequencevaluescanoccurwhen:ArollbackoccursThesystemcrashesAsequenceisusedinanothertableModifyingaSequenceChangetheincrementvalue,maximumvalue,minimumvalue,cycleoption,orcacheoption:ALTERSEQUENCEdept_deptid_seqINCREMENTBY20MAXVALUE999999NOCACHENOCYCLE;GuidelinesforModifying

aSequenceYoumustbetheownerorhavetheALTERprivilegeforthesequence.Onlyfuturesequencenumbersareaffected.Thesequencemustbedroppedandre-createdtorestartthesequenceatadifferentnumber.Somevalidationisperformed.Toremoveasequence,usetheDROPstatement:DROPSEQUENCEdept_deptid_seq;LessonAgendaOverviewofviews:Creating,modifying,andretrievingdatafromaviewDMLoperationsonaviewDroppingaviewOverviewofsequences:Creating,using,andmodifyingasequenceCachesequencevaluesNEXTVALandCURRVALpseudocolumnsOverviewofindexesCreating,droppingindexesOverviewofsynonymsCreating,droppingsynonymsIndexesLogicallyrepresentssubsetsofdatafromoneormoretablesViewGeneratesnumericvaluesSequenceBasicunitofstorage;composedofrowsTableGivesalternativenamestoobjectsSynonymImprovestheperformanceofsomequeriesIndexDescriptionObjectIndexesAnindex:IsaschemaobjectCanbeusedbytheOracleservertospeeduptheretrievalofrowsbyusingapointerCanreducediskinput/output(I/O)byusingarapidpathaccessmethodtolocatedataquicklyIsindependentofthetablethatitindexesIsusedandmaintainedautomaticallybytheOracleserverHowAreIndexesCreated?Automatically:AuniqueindexiscreatedautomaticallywhenyoudefineaPRIMARY

KEYorUNIQUEconstraintinatabledefinition.Manually:Userscancreatenonuniqueindexesoncolumnstospeedupaccesstotherows.CreatinganIndexCreateanindexononeormorecolumns:ImprovethespeedofqueryaccesstotheLAST_NAMEcolumnintheEMPLOYEEStable:CREATEINDEX emp_last_name_idxON employees(last_name);CREATE[UNIQUE][BITMAP]INDEXindexONtable(column[,column]...);IndexCreationGuidelinesDonotcreateanindexwhen:ThecolumnsarenotoftenusedasaconditioninthequeryThetableissmallormostqueriesareexpectedtoretrievemorethan2%to4%oftherowsinthetableThetableisupdatedfrequentlyAcolumncontainsalargenumberofnullvaluesOneormorecolumnsarefrequentlyusedtogetherinaWHEREclauseorajoinconditionAcolumncontainsawiderangeofvaluesTheindexedcolumnsarereferencedaspartofanexpressionThetableislargeandmostqueriesareexpectedtoretrievelessthan2%to4%oftherowsinthetableCreateanindexwhen:RemovinganIndexRemoveanindexfromthedatadictionarybyusingtheDROP

INDEXcommand:Removetheemp_last_name_idxindexfromthedatadictionary:Todropanindex,youmustbetheowneroftheindexorhavetheDROP

ANY

INDEXprivilege.DROPINDEXemp_last_name_idx;DROPINDEXindex;LessonAgendaOverviewofviews:Creating,modifying,andretrievingdatafromaviewDMLoperationsonaviewDroppingaviewOverviewofsequences:Creating,using,andmodifyingasequenceCachesequencevaluesNEXTVALandCURRVALpseudocolumnsOverviewofindexes