I will be honest. I don't use views all the much. But it is good to know a little about about them. They are SELECT statements that have a name. You access a view just like you would a table. For example, you can DESCRIBE it. There are some scenarios where you cannot do all insert/update/delete operations on a view:
- If a required field in a table is not contained in the view
- SQL to create view has GROUP BY
- SQL to create view has aggregation
- SQL to create view uses DISTINCT
- SQL to create view has more than one table (through a join)
I think I have mentioned before that an inline view is when the table section of a FROM clause is replaced by a SELECT statement. Now let's move on to sequences. They are mainly to create values for a primary key. They have properties such as INCREMENT BY and START WITH. You use the pseudo columns NEXTVAL and CURRVAL to get sequence values. Note that you cannot access CURRVAL in a session without first getting the NEXTVAL.
Indexes are objects to make queries run faster. The WHERE clause and ORDER BY clause will specifically benefit from some indexes. They cannot be placed on columns of type LOB or RAW. You automatically get an index for a primary key or a unique column. The optimizer might use an index for almost everything except for NOT EQUALS comparisons. A composite index has two or more columns in it.
Finally synonyms are aliases for database objects. They can be for tables, views, sequences, or even other synonyms. The object referenced does not even have to exist when you create the synonym. But it must by the itme you use it. There are two types of synoyms:
- PUBLIC
- PRIVATE
Private synonyms are those created without the PUBLIC keyword. They are owned by the user that creates them. They get precedence over a PUBLIC synonym with the same name. Public synonyms are owned by a used called PUBLIC. The are visible to all users. You cannot ALTER a synonym. But you can CREATE OR REPLACE one.
