Oracle-OCP-SQL-12-Other-Database-Objects

Copyright©OracleCorporation,2001.Allrightsreserved.OtherDatabaseObjects12-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Create,maintain,andusesequences•Createandmaintainindexes•Createprivateandpublicsynonyms12-3Copyright©OracleCorporation,2001.Allrightsreserved.DatabaseObjectsDescriptionBasicunitofstorage;composedofrowsandcolumnsLogicallyrepresentssubsetsofdatafromoneormoretablesGeneratesprimarykeyvaluesImprovestheperformanceofsomequeriesAlternativenameforanobjectObjectTableViewSequenceIndexSynonym12-4Copyright©OracleCorporation,2001.Allrightsreserved.WhatIsaSequence?Asequence:•Automaticallygeneratesuniquenumbers•Isasharableobject•Istypicallyusedtocreateaprimarykeyvalue•Replacesapplicationcode•Speedsuptheefficiencyofaccessingsequencevalueswhencachedinmemory12-5Copyright©OracleCorporation,2001.Allrightsreserved.TheCREATESEQUENCEStatementSyntaxDefineasequencetogeneratesequentialnumbersautomatically:CREATESEQUENCEsequence[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}];12-6Copyright©OracleCorporation,2001.Allrightsreserved.CreatingaSequence•CreateasequencenamedDEPT_DEPTID_SEQtobeusedfortheprimarykeyoftheDEPARTMENTStable.•DonotusetheCYCLEoption.CREATESEQUENCEdept_deptid_seqINCREMENTBY10STARTWITH120MAXVALUE9999NOCACHENOCYCLE;Sequencecreated.12-7Copyright©OracleCorporation,2001.Allrightsreserved.ConfirmingSequences•VerifyyoursequencevaluesintheUSER_SEQUENCESdatadictionarytable.•TheLAST_NUMBERcolumndisplaysthenextavailablesequencenumberifNOCACHEisspecified.SELECTsequence_name,min_value,max_value,increment_by,last_numberFROMuser_sequences;12-8Copyright©OracleCorporation,2001.Allrightsreserved.NEXTVALandCURRVALPseudocolumns•NEXTVALreturnsthenextavailablesequencevalue.Itreturnsauniquevalueeverytimeitisreferenced,evenfordifferentusers.•CURRVALobtainsthecurrentsequencevalue.•NEXTVALmustbeissuedforthatsequencebeforeCURRVALcontainsavalue.12-10Copyright©OracleCorporation,2001.Allrightsreserved.UsingaSequence•Insertanewdepartmentnamed“Support”inlocationID2500.•ViewthecurrentvaluefortheDEPT_DEPTID_SEQsequence.INSERTINTOdepartments(department_id,department_name,location_id)VALUES(dept_deptid_seq.NEXTVAL,'Support',2500);1rowcreated.SELECTdept_deptid_seq.CURRVALFROMdual;12-11Copyright©OracleCorporation,2001.Allrightsreserved.UsingaSequence•Cachingsequencevaluesinmemorygivesfasteraccesstothosevalues.•Gapsinsequencevaluescanoccurwhen:–Arollbackoccurs–Thesystemcrashes–Asequenceisusedinanothertable•IfthesequencewascreatedwithNOCACHE,viewthenextavailablevalue,byqueryingtheUSER_SEQUENCEStable.12-12Copyright©OracleCorporation,2001.Allrightsreserved.ModifyingaSequenceChangetheincrementvalue,maximumvalue,minimumvalue,cycleoption,orcacheoption.ALTERSEQUENCEdept_deptid_seqINCREMENTBY20MAXVALUE999999NOCACHENOCYCLE;Sequencealtered.12-13Copyright©OracleCorporation,2001.Allrightsreserved.GuidelinesforModifyingaSequence•YoumustbetheownerorhavetheALTERprivilegeforthesequence.•Onlyfuturesequencenumbersareaffected.•Thesequencemustbedroppedandre-createdtorestartthesequenceatadifferentnumber.•Somevalidationisperformed.12-14Copyright©OracleCorporation,2001.Allrightsreserved.RemovingaSequence•RemoveasequencefromthedatadictionarybyusingtheDROPSEQUENCEstatement.•Onceremoved,thesequencecannolongerbereferenced.DROPSEQUENCEdept_deptid_seq;Sequencedropped.12-15Copyright©OracleCorporation,2001.Allrightsreserved.WhatisanIndex?Anindex:•Isaschemaobject•IsusedbytheOracleservertospeeduptheretrievalofrowsbyusingapointer•CanreducediskI/Obyusingarapidpathaccessmethodtolocatedataquickly•Isindependentofthetableitindexes•IsusedandmaintainedautomaticallybytheOracleserver12-16Copyright©OracleCorporation,2001.Allrightsreserved.HowAreIndexesCreated?•Automatically:AuniqueindexiscreatedautomaticallywhenyoudefineaPRIMARYKEYorUNIQUEconstraintinatabledefinition.•Manually:Userscancreatenonuniqueindexesoncolumnstospeedupaccesstotherows.12-17Copyright©OracleCorporation,2001.Allrightsreserved.CreatinganIndex•Createanindexononeormorecolumns.•ImprovethespeedofqueryaccesstotheLAST_NAMEcolumnintheEMPLOYEEStable.CREATEINDEXemp_last_name_idxONemployees(last_name);Indexcreated.CREATEINDEXindexONtable(column[,column]...);12-18Copyright©OracleCorporation,2001.Allrightsreserved.WhentoCreateanIndexYoushouldcreateanindexif:•Acolumncontainsawiderangeofvalues•Acolumncontainsalargenumberofnullvalues•OneormorecolumnsarefrequentlyusedtogetherinaWHEREclauseorajoincondition•Thetableislargeandmostqueriesareexpectedtoretrievelessthan2to4percentoftherows12-19Copyright©OracleCorporation,2001.Allrightsreserved.WhenNottoCreateanIndexItisusuallynotworthcreatinganindexif:•Thetableissmall•Thecolumnsarenotoftenusedasaconditioninthequery•Mostqueriesareexpectedtoretrievemorethan2to4percentoftherowsinthetable•Thetableisupdatedfrequently•Theindexedcolumnsarereferencedaspartofanexpression12-20Copyright©OracleCorporation,2001.Allrightsreserved.SELECTic.index_name,ic.column_name,ic