Oracle Effective Schema Design

ORACLESeriesTIGHT/EffectiveOraclebyDesign/Kyte/223065-7/Chapter7BlindFolio7:367CHAPTER7EffectiveSchemaDesignP:\010Comp\Oracle8\065-7\ch07.vpThursday,July31,20034:24:59PMColorprofile:GenericCMYKprinterprofileCompositeDefaultscreen368EffectiveOraclebyDesignORACLESeriesTIGHT/EffectiveOraclebyDesign/Kyte/223065-7/Chapter7BlindFolio7:368Yourapplicationwillliveanddiebasedonitsphysicalimplementation.Choosethewrongdatastructures,andperformancewillbecrippledandflexibilitylimited.Choosethecorrectdatastructures,andyoucouldhavegreatperformance.Inthischapter,wewilllookatsomeofthethingsyouneedtoconsiderwhendesigningyourschema.Thenwewillfocusontabletypes,someusefulindextypes,andfinally,compression.FundamentalSchemaDesignPrinciplesInthissection,we’llconsidersomeofthefundamentalapproachesthatyoushouldadoptwhendesigningyourschema.We’llcoverintegritychecking,datatypeselection,andoptimizationforqueries.LettheDatabaseEnforceDataIntegrityWetouchedonthissubjectwaybackinthe“It’saDatabase,NotaDataDump”sectioninChapter1.There,Irelayedthestoryoftheconsultantwhowantedtoremoveallreferentialintegrityfromthedatabaseanddoitintheapplication.Iexplainedthatthiswasareallybadidea,forthefollowingreasons:■Yourswillnotbethelastapplicationtowanttousethisdata.Iftherulesgoverningthedataintegrityarehiddenawayinclientapplications,theywillnotbeenforceduniformly,andtheywillbeveryhardtochange.■Itisslowertomanagedataintegrityontheclient.Itrequiresadditionalround-tripstotheserver,anditmustbedoneontheserveraswell.■Ittakesordersofmagnitudemorecodetomanageintegrityontheclient.Ihaveatheoryastowhydevelopersaresometimesinclinedtodothisontheclient:IthastodowiththeDBA-versus-developerphilosophyinsteadoftheDBA-and-developers-working-togetherapproach.Iftheintegrityofthedataismanagedinthedatabase,thedevelopersfeeltheyhavelostcontrol.Iftheintegrityofthedataismanagedintheapplication,thedevelopersfeeltheyhaveregainedthatcontrol.Thisisashortsightedperspective.NeitherthedevelopernortheDBAownthedata.Thedataisthepropertyofsomeoneelse:theendusers.Itiscertainlynotinthebestinterestsoftheenduserstoobfuscatethebusinessrulesandhidethemintheclientapplication.WhyYouWanttoKeepReferentialIntegrityintheDatabaseData-integrityrulesmustbeplacedintothedatabasetoensurethattheyareconsistentlyimplementedandenforced.Application-specificrules—rulesthatapplyonlytothedatainthecontextoftheapplication’suseofit—maybeintheapplication.Herearethereasonsyouwantdataintegrityenforcedbythedatabasewheneverpossible:■Featuressuchasqueryrewritearethwarted.Thesefeaturesarerenderedless-than-usefulifyoudonottellthedatabaseabouttherelationshipsbetweenobjects,unlessyoudeclarativelyspellouttherulesregardingthedata.InthesectiononQUERY_P:\010Comp\Oracle8\065-7\ch07.vpThursday,July31,20034:24:59PMColorprofile:GenericCMYKprinterprofileCompositeDefaultscreenChapter7:EffectiveSchemaDesign369ORACLESeriesTIGHT/EffectiveOraclebyDesign/Kyte/223065-7/Chapter7BlindFolio7:369REWRITE_ENABLEDinChapter6,yousawanexampleofhowamaterializedviewcouldnotbeuseduntilthedatabasewastoldabouttherelationshipbetweenthetables,theNOTNULLcolumn,andtheprimarykeys.■Thedataintegritywillbecompromisedatsomepointintime.Virtuallyeverydevelopedsystemthatchoosestoenforceforeignkeyconstraintsoutsidethedatabasehasorphanedchildrows(childrowswithoutparents).Ifyouhavesuchasystem,justrunthisquery:selectforeign_key_columnsfromchild_tableMINUSselectprimary_key_columnsfromparent.Youmaybesurprisedtofindsomeinyourowndatabase!Runacoupleofchecksonyourdata,andyoumightfindNULLvalueswherenoneshouldbeanddatathatdoesnotconformtoyourbusinessrules(out-of-rangedata,forexample).Thesearisefromaninconsistentapplicationofthebusinessrules.■Server-enforcedintegrityisblindinglyfast.Isitslowerthannotusingdataintegrity?Yes,ofcourseitis.Itaddsanextrastep,anditmeansthatmorecodeisexecutedintheserver.Isitfasterthanyoucancodeityourself?Yes,itis.Isitappliedconsistentlyregardlessoftheapplication?Yes,itis.■Thedatabaseprovidesmoreinformation.Bykeepingtheintegrityrulesinthedatabase,yoursystemisinfinitelymoreself-documenting.Asimplequerytellsyouwhatrelatestowhatandhow.Asalastreasonforusingserver-enforcedintegrity,let’sconsiderwhathappenswhenyoutrytodoityourself.TheProblemwithDo-It-YourselfIntegrityChecksClient-sideenforcementofconstraintsisproblematicatbest,ifyoucandoitatall.Here,we’lllookatanexampleoftheproblemsthatcanarise.“Ihopesomeonecanhelpmeand,therefore,thankyouinadvanceifyoucan.IamusingOracle9andtheEMPtable.Ineedtoensurethatnodepartmentistohavemorethaneightemployeesandfewerthanthree,exceptwhenatransactionreducesthenumberofemployeesto0.”Interestingly,thisuserposedthisquestiontomanyindividualsindifferentforums,andthemostfrequentresponsewassomethingalongthelinesofthefollowingtrigger:SQLcreateorreplacetriggerxxx2afterdeleteorupdateorinsertonemp3declare4begin56forirecin(selectdeptno,count(*)emps7fromemp8groupbydeptno9havingcount(*)310orcount(*)8)P:\010Comp\Oracle8\065-7\ch07.vpThursday,July31,20034:24:59PMColorprofile:GenericCMYKprinterprofileCompositeDefaultscreen11loop12RAISE_APPLICATION