Introduction to Oracle9i: SQL (12)

Creating other database objects
##CONTINUE##

• TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
• To look on the view definition you need to query the USER_VIEWS data dictionary view. Can’t use DESC.
• USER_SOURCE data dictionary view is used to store system objects definitions only.
• USER_OBJECTS data dictionary view is used to find record about view
• The ROWID datatype stores information related to the disk location of table rows. They also uniquely identify the rows in your table. The ROWID datatype is stored as a hexadecimal string.
• Create index may require a temporary segment.
• Table is a Database object which has many key columns dependent on the key column of same or other object. Cursor is not a database object which forms a memory area independent of whether the query fetches data or not. A SEQUENCE is an automatic number generator and does not require a segment. View is a database object dependent on one or more tables or views.
• After create a sequence it is first necessary to issue the ‘Select seq.nextval from dual’
• Row Share, Row Exclusive, Share Row Exclusive are valid DML locks in ORACLE.
• ON DELETE CASCADE clause allows you to delete child records if parent records are deleted.
  DELETE FROM dept WHERE depe=10 ON DELETE CASCADE;
• Build_index(Btree=>’abc’,Bitmap=>’efg’) è Build_index(‘abc’,’efg’)
• Bitmaps work well in multiple indexes rather than single indexes. Bitmaps do not support row-level locking. Bitmap indexes are suitable for low-cardinality columns. Bitmap indexes are useful for datawarehousing. Updates to key columns in a bitmap index is very expensive.
• B-tree indexes work well as a single index. B-tree indexes are useful for OLTP. B-tree indexes are suitable for retrieval of a small number of rows.