KAMAL SQL Material

  CHAPTER 4-Index Ø   Index is scheme object, which is a pointer locates the physical address of data. Ø   Index is used by oracle server to speedup the retrieval and manipulation of rows. Ø   Index can created explicitly by user or automatically by server. Ø   Index is activated where index column is used in the where clause. Ø   Creation of index is two types these are 1.       Automatic : - Index is created automatically where we can define primary key or unique constraint in a table definition. 2.       Manual : - users can create non unique indexes on columns to speedup access of the rows. DML operations are committed on table with indexes means that indexes must be updated. Ø   Indexing involves forming a two-dimensional matrix, which is completely independent of a table on which the index is been created. The two-dimensional matrix will have single column and address field. Sin...

  Sequence Pseudo columns à Pseudo column behave like a table column, but it is not actually store in a table. à Upon pseudo column only select can be possible, DML operations can’t be possible to implement. à The available pseudo columns are 1.       CURRVAL 2.       NEXTVAL 3.       LEVEL 4.       ROWNUM 5.       ROWID à CURRVAL, NEXTVAL are applied upon sequence schema object. These are only in select statement, value clause of insert statement, set clause of update statement. à CURRVAL, NEXTVAL can’t be used in ·         A sub queries ·         A view, SNAPSHOT query ·         Select statement with group by or order by ·         Select statement with distinct operation. ·  ...