sql pl sql n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL/PL SQL PowerPoint Presentation


137 Vues Download Presentation
Télécharger la présentation


- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. SQL/PL SQL Oracle By Rana Umer

  2. Performance Tuning • Indexes • ROWID • View • Sequences Oracle By Rana Umer

  3. Performance Tuning • Indexing a table is an access strategy, that is a way to sort and search records in the table. Indexes are essential to improve the speed with which records can be located and retrieved from a table. • An index is an ordered list of the contents of column or columns of a table. • When index is created, it is assigned a ROWID for each row before it sorts.( Data Value in the index ) • Records in the index are sorted in the ascending order of the index column. Oracle By Rana Umer

  4. Performance Tuning • CreateIndex<Index Name> On<table Name> (<Column,Column…..>) • SelectROWID, Column-Name fromTabke-Name; • CreateUnique Index <Index Name> On<table Name> (<Column,Column…..>) • CreateIndex<Index Name> On<table Name> (<Column> Reverse • Alter INDEX <Index Name> ReBuild NOREVERSE; • Drop index <Index NAme> • Alter Table <Table Name > index <Index Name> Oracle By Rana Umer

  5. Performance Tuning ROWID Take Space in Table Data Dictionary In Bytes AAAHeeAABAAAMWCAAA Data Object Number AAAHee Datafile Number AAB Data Block AAAMWC Row Number AAA Oracle By Rana Umer

  6. View Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables. Create VIEW <View Name> AS Select * from Table; Insert into VIEW Values (Values , Values …) Delete From ViewName where Name =‘Ali’; Update ViewNameSet column=values Where condition Oracle By Rana Umer

  7. Sequences The Oracle SEQUENCE function allows you to create auto-numbering fields by using sequences. An Oracle sequence is an object that is used to generate incrementing or decrementing numbers. CREATESEQUENCE <sequence_name>MINVALUE valueMAXVALUE valueSTART WITH valueINCREMENT BY valueCACHE value; Oracle By Rana Umer

  8. Sequences CREATE SEQUENCE user_seqMINVALUE 1MAXVALUE 20000START WITH 1INCREMENT BY 1CACHE 100; Drop Sequence <Sequence Name> Alter Sequence <Sequence Name > Maxvalue=50000; Insert into TableNameValuse ( user_seq, ‘Ali’,’MCS’,’Evening’); Oracle By Rana Umer