1 / 8

SQL/PL SQL

SQL/PL SQL. Performance Tuning. Indexes ROWID View Sequences. 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.

alaric
Download Presentation

SQL/PL SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related