In this post I am going to write some of quick read tips that is going to help you in 1Z0-051 certification.
These are tips referred from Oracle Database 11g: SQL Fundamentals I book.
The Oracle Server Architecture
Access to the database is through the Oracle instance. The instance is a set of processes and memory structures:it exists on the CPU(s) and in the memory of the server node, and this existence is temporary. An instance can be started and stopped.
The client tier consists of two components:
- the users
- user processes
The server tier has three components:
- the server processes that execute the SQL
- the instance
- the database itself
Each user interacts with a user process. Each user process interacts with a server process, usually across a local area network. The server processes interact with the instance, and the instance with the database.
A session is a user process in communication with a server process.There will usually be one user process per user and one server process per user process.The user and server processes that make up sessions are launched on demand by users and terminated when no longer required; this is the log-on and log-off cycle.
The instance processes and memory structures are launched by the database administrator and persist until the administrator deliberately terminates them; this is the database start-up and shut-down cycle
The simplest form of the database server is one instance connected to one database, but in a more complex environment one database can be opened by many instances. This is known as a RAC (Real Application Cluster).
RAC can bring many potential benefits, which may include scalability, performance, and zero downtime. The ability to add dynamically more instances running on more nodes to a database is a major part of the database’s contribution to the Grid.
The Oracle Application Server is a platform for developing, deploying, and managing web applications.
A web application can be defined as any application with which users communicate with HTTP.
Web applications usually run in at least three tiers:
- a database tier manages access to the data
- the client tier (often implemented as a web browser) handles the local window management for communications with the users.
- an application tier in the middle executes the program logic that generates the user interface and the SQL calls to the database.
The process of modeling data into relational tables is known as normalization.
A widely used standard is as follows:
■ Primary key columns identified with a hash (#)
■ Foreign key columns identified with a back slash (\)
■ Mandatory columns (those that cannot be left empty) with an asterisk (*)
■ Optional columns with a lowercase “o”
The Data Manipulation Language (DML) commands:
■ SELECT
■ INSERT
■ UPDATE
■ DELETE
■ MERGE
The Data Definition Language (DDL) commands:
■ CREATE
■ ALTER
■ DROP
■ RENAME
■ TRUNCATE
■ COMMENT
The Data Control Language (DCL) commands:
■ GRANT
■ REVOKE
The Transaction Control Language (TCL) commands:
■ COMMIT
■ ROLLBACK
■ SAVEPOINT
1.
DUAL table belongs to SYS.
This can be structurally described as
DESC SYS.DUAL;
2.
Capability of SELECT:
selection means you are using where clause
projection means you are selecing columns
joining interaction of tables with each other
3.
MetaData about different database objects is stored by defualt in UPPERCASE in the data dictionary.
SQL statements may be submitted to db in either UPPERCASE or LOWERCASE.You may pay attention when interacting with character literal.
Example:
- select * from HR where name='javalatte';
- select * from HR where name='JavaLatte';
Character literal data is case sensitive.
if there was entry in HR table with 'javalatte' .then 2nd query will not return any row.
4.
Quote Operator(q)
SELECT 'Plural's has two many quotes' from DUAL;
You will get the error :
ORA-01756: quoted string not properly terminated.
One solution is :
SELECT 'Plural''s has two many quotes' from DUAL;
You have to put 2 quotes , but this will become confusing.
if there are so many quotes in a string.
Quote Operator is the rescue.
Select q'('Plural's has two many quotes)' from dual;
5.
Any arithmetic calculation with a NULL values always return NULL.The character Concatenation operators ignore null.
6.
Select * from employe where salary=1000;
Select * from employe where salary='1000';
Both format are acceptable to oracle since an implicit data type conversion is performed.
7.
Default date format in Oracle DD-MON-RR
8.
Comparison of character:
Numeric inequality is naturally intuitive.
like where salary<1000;
The comparison of character and Date, however is more complex.like where last_name<'King'
String being compared on either side of inequality operator are converted to a numeric representation of its character.
K+i+n+g=75+105+110+103=393.similary last_name column is converted into numeric value.
value less than 393 are selected.
Inequality comparisons operating on date values follow a similar process to character data.
9.
ORDER BY
default is ASC i.e for number is lowest to highest,earliest to latest for date,alphabetically for characters.
Optional keyword NULLS LAST|FIRST
NULLS LAST keywords, specify that if sort column contain nulls values, then these rows are to be listed last after sorting the remaining rows bases on there NOT NULL values.
NULLS FIRST ,null values are displayed first.
Example : order by name NULLS FIRST
If null value occur in the sort column,the default sort
order is assumed to be NULLS LAST for ascending sort & NULLS FIRST for descending sorts.
10.
WHERE BETWEEN 3400 and 4000 is equivalent to salary>=3400 and salary<=4000
where salary in(1000,2000,3000) is equivalent to salary=1000 or salary=2000 or salary=3000
11.
where first_name like '%'
same as
where first_name is not null
12.
ESCAPE identifier
Scenario when you are searching for a literal that contains % or _ character.
Oracle provide a way to temporarily disable their special meaning & regard them as regular character using the ESCAPE identifier.
Suppose there are two value in job_id columns
SA_MAN,SA%MAN
We want to extract data like SA%MAN
when we do: where job_id like 'SA%MAN' it give
SA_MAN
SA%MAN
Oracle interprets the % as wild card
To obtain row with job_id : SA%MAN using like operator ,% may be escaped using:
Select * from job where job_id like 'SA\%MAN' escape '\';
The backslash is defined as the ESCAPE character that instruct the oracle server to ignore the wildcard properties of the symbol occurring immediately after the backslash.
13.
Substitution Variable:
Single ampersand substitution
When oracle server process the statement,notices a substitution variable and attempt to resolve this variable in one of 2 ways:
check whether this variable is defined in user session.(DEFINE command)
If variable is not define, the user process prompts for value that will be substituted in place of variable.
Once a value is submitted, the statement is complete and is executed by the oracle server.
The ampersand substitution variable is resolved at run time and also called run time binding.
Example :
select * from employe where last_name=&lastname
Double ampersand substitution
When substitution variable is reference multiple time, then we used &&.
When oracle encounter &&, a session variable is created and you are not prompted to enter value for same substitution variable.
Example:
select * from employe where last_name like '&&SEARCH' and first_name like '&SEARCH';
You can also substitute the column name with &.
14.
DEFINE command
it can be used to retrieve the variable defined in your session.
It can be used to explicitly define a value for variable referenced as substitution variable by one or more statements;
DEFINE;
DEFINE variable=value;
15.
VERIFY command
It controls whether substitution variable submitted is display on screen or not so that you can verify the correct substitution.
SET VERIFY ON|OFF;
16.
select initcap('init cap or init_cap or init%cap') from dual;
Init Cap Or Init_Cap Or Init%Cap
17.
Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function.
18.
Blank spaces, tabs, and special characters are all counted by the LENGTH function.
19.
INSTR(source string, search string, [search startposition], [nth occurrence]).
20.
SUBSTR(source string, start position, [number of charactersto extract]).
If the start position is larger than the length of the source string, null is returned.
select substr(sysdate,4,3) from dual.
17-DEC-07. The search for the substring begins at position 4 and the three characters from that position onward are extracted, yielding the substring DEC.
21.
select substr('1#3#5#7#9#',-3,2) from dual;
The negative start position parameter instructs Oracle to commence searching 3 characters from the end of the string. Therefore start position is three characters from the end of the string, which is position 8. The third parameter is 2, which results in the substring #9 being returned.
22.
REPLACE(source string, search item, [replacement term]).
with the first two being mandatory.if the replacement term parameter is omitted, each occurrence of the search item is removed from the source string.
23.
ROUND(source number,decimal precision).
The decimal precision parameter specifies the degree of rounding and is optional. If the decimal precision parameter is absent, the default degree of rounding is zero, which means the source is rounded to the nearest whole number.
If the specified decimal precision is n, the digit significant to the rounding is found (n + 1) places to the RIGHT of the decimal point. If it is negative, the digit significant to the rounding is found n places to the LEFT of the decimal point.
select round(1601.916718,-3) from dual;
Since it is negative, the digit significant for rounding is found 3 places to the left of the decimal point, at the hundreds digit, which is 6. Since the hundreds unit is 6, rounding up occurs and the number returned is 2000.
24.
TRUNC (sourcenumber, decimal precision).
if the decimal precision specified (n) is negative, the input value is zeroed down from the nth decimal position.
If the decimal precision parameter is absent, the default degree of rounding is zero, which means the source number is truncated to the nearest whole number.
select trunc(1601.916718,-3) from dual
Query 3 specifies a negative number (−3) as its decimal precision.
Three places to the left of the decimal point implies that the truncation happens at the hundreds digit
Therefore, the source number is zeroed down from its hundreds digit (6) and the number returned is 1000.
25.
select mod(5.2,3) from dual ; 2.2
select mod(7,35) from dual ; 7
26.
adding, multiplying,or dividing two date items is not permitted
27.
1 represents Sunday, 2 represents Monday, and so on.
28.
ROUND(source date, [date precision format]).
The date precision formats include century (CC), year (YYYY),quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI).
Rounding up to century is equivalent to adding one to the current century. Rounding up to the next month occurs if the day component is greater than 16, else rounding down to the beginning of the current month occurs. If the month falls between one and six, then rounding to year returns the date at the beginning of the current year, else it returns the date at the beginning of the following year.
29. chapter 5
The character equivalents of both number and date information can be stored in a VARCHAR2 field.
30.
When numeric values are supplied as input to functions expecting character parameters, implicit data type conversion ensures that they are treated as character values.
When numeric values are supplied as input to functions expecting character parameters, implicit data type conversion ensures that they are treated as character values.
There are some cases when it does not work as expected. for example :
Consider limiting data from a table T based on a character column C, which contains the string '100'.The condition clause WHERE C='100' works as you might expect,but the condition WHERE C=100 returns an invalid number error.
31.
Numbers to Characters
TO_CHAR(number1, [format], [nls_parameter]),
number1 parameter is mandatory.
When a format mask is smaller than the number being converted, a string of hash symbols is returned instead.
TO_CHAR(date1, [format], [nls_parameter])
Only the date1 parameter is mandatory.
The names of days and months are automatically padded with spaces. These may be removed using a modifier to the format mask called the fill mode (fm) operator.
32.
TH example : DDth "of " Month , Output : 12TH of September
SP example : MmSP Month Yyyysp' , Output : Nine September Two Thousand Eight
33.
TO_DATE(string1, [format], [nls_parameter])
TO_NUMBER(string1, [format], [nls_parameter]),
The TO_DATE function has an fx modifier which is similar to fm used with the TO_CHAR function
Be careful not to confuse TO_NUMBER conversions with TO_CHAR. For example,
TO_NUMBER(123.56,'999.9') returns an error, while TO_CHAR(123.56,'999.9') returns 123.6.
34.
NVL(original, ifnull)
The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter.
35.
NVL2(original,ifnotnull, ifnull)
The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG.
select nvl2(1234,1,'a string') from dual;
Since there is a data type incompatibility between them, an “ORA-01722: invalid number” error is returned.
36.
NULLIF(ifunequal, comparison_term)
The NULLIF function takes two mandatory parameters of any data type.If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.
37.
COALESCE(expr1, expr2,…,exprn)
The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned.
takes two mandatory parameters.
COALESCE is a general form of the NVL function :
COALESCE(expr1,expr2) = NVL(expr1,expr2)
COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))38.
DECODE(expr1,comp1, iftrue1,[comp2,iftrue2...[ compN,iftrueN]], [iffalse])
takes at least three mandatory parameters.
DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not.
Decode considers two nulls to be equivalent
39.
CASE search_expr
WHEN comparison_expr1 THEN iftrue1
[WHEN comparison_expr2 THEN iftrue2
…
WHEN comparison_exprN THEN iftrueN
ELSE iffalse]
END
CASE expression takes at least three mandatory parameters but can take many more.
40.
COUNT({*|[DISTINCT|ALL] expr})
When COUNT(*) is invoked, all rows in the group, including those with nulls or duplicate values are counted.
41.
COUNT(DISTINCT expr) is executed unique occurrences of expr are counted for each group.
42.
AVG([DISTINCT|ALL] expr)
When AVG(DISTINCT expr) is invoked, the distinct values of expr are summed and divided by the number of unique occurrences of expr.
43.
SUM([DISTINCT|ALL] expr)
SUM(DISTINCT expr) provides a total by adding all the unique values returned after expr is evaluated for each row in the group
44.
Group functions may only be nested two levels deep
G1(group_item) = result
G1(G2(group_item ) = result
G1(G2(G3(group_item))) is NOT allowed
Single-row functions may be nested to any level.
45.
The HAVING clause can occur before the GROUP BY clause in the SELECT statement.
46.
When you encounter this :
employees.department_id (+) = departments.department_id;
it means it is RIGHT OUTER JOIN
47.
ANSI SQL:1999
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 ON (table1.column_name = table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)] |
[CROSS JOIN table2];
Oracle-proprietary syntax
SELECT table1.column, table2.column
FROM table1, table2
[WHERE (table1.column_name = table2.column_name)] |
[WHERE (table1.column_name(+)= table2.column_name)] |
[WHERE (table1.column_name)= table2.column_name) (+)] ;
48.
NATURAL JOIN
SELECT table1.column, table2.column
FROM table1
NATURAL JOIN table2;
If coulmn is same in both table & data type is not match, then ORA-01722: invalid number is occur.
If there is no same column in both table , cross product will occur.
49.
SELECT table1.column, table2.column
FROM table1
JOIN table2 USING (join_column1, join_column2…);
The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword.
The join columns cannot be qualified using table names or aliases when they are referenced
50.
An error is raised if the keywords NATURAL and USING occur in the same join clause.
The ON and NATURAL keywords cannot appear together in a join clause.
51.
SELECT table1.column, table2.column
FROM table1
JOIN table2 ON (table1.column_name = table2.column_name);
The JOIN…ON clause allows the explicit specification of join columns, regardless of their column names
52.
nonequijoin
SELECT table1.column, table2.column
FROM table1
[JOIN table2 ON (table1.column_name < table2.column_name)]|
[JOIN table2 ON (table1.column_name > table2.column_name)]|
[JOIN table2 ON (table1.column_name <= table2.column_name)]|
[JOIN table2 ON (table1.column_name >= table2.column_name)]|
[JOIN table2 ON (table1.column BETWEEN table2.col1 AND table2.col2)]|
53.
A left outer join between the source and target tables returns the results of an inner join as well as rows from the source table excluded by that inner join.
54.
A rightouter join between the source and target tables returns the results of an inner join as well as rows from the target table excluded by that inner join.
55.
If a join returns the results of an inner join as well as rows from both the source and target tables excluded by that inner join, then a full outer join has been performed.
56.
LEFT OUTER JOIN
SELECT table1.column, table2.column
FROM table1
LEFT OUTER JOIN table2
ON (table1.column = table2.column);
Any rows from the table on the left of the JOIN keyword excluded for not fulfilling the join condition are also returned.
57.
RIGHT OUTER JOIN
SELECT table1.column, table2.column
FROM table1
RIGHT OUTER JOIN table2
ON (table1.column = table2.column);
Rows from the table to the right of the JOIN keyword, excluded by the join condition, are also returned
58.
FULL OUTER JOIN
SELECT table1.column, table2.column
FROM table1
FULL OUTER JOIN table2
ON (table1.column = table2.column);
A full outer join returns the combined results of a left and right outer join.
59.
The traditional Oracle join syntax does not support a full outer join, which is typically performed by combining the results from a left and right outer join using the UNION set operator
60.
CROSS JOIN
SELECT table1.column, table2.column
FROM table1
CROSS JOIN table2;
A Cartesian product freely associates the rows from table1 with every row in table2.
61.
Cartesian products are created when there are insufficient join conditions in a statement.
62.
A pure natural join between two tables sharing no identically named columns results in a Cartesian join since two tables are joined but less than one condition is available.
63. chapter 8
The places in a query where a subquery may be used are as follows:
SELECT
FROM
WHERE
HAVING
GROUP BY
64.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
-subquery can contain group by and order by
-main query and subquery can get data from different table.
-A public & private synonym can exist with the same name for the same table.
65.
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the reeults of the subquery can be passed to the parent.
66.
ANY returns rows that match any value on a list
ALL returns rows that match all the values in a list
67.
a correlated sub query must be evaluated once for every row in the outer query.
68.
these two statements, which retrieve all employees whose salary is above that of anyone in department 80, will return identical result sets:
select last_name from employees where salary > all (select salary from employees where department_id=80);
select last_name from employees where salary > (select max(salary) from employees where department_id=80)
69.
UNION
Returns the combined rows from two queries, sorting them and removing duplicates.
UNION ALL
Returns the combined rows from two queries without sorting or removing duplicates.
INTERSECT
Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates.
MINUS
Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.
70.
It is not possible to use ORDER BY in the individual queries that make a compound query.
71.
A MERGE operation does nothing that could not be done with INSERT, UPDATE, and DELETE statements—but with one pass through the source data, it can do all three.
72.
TRUNCATE it is DDL command
DDL commands, such as TRUNCATE, will fail if there is any DML command active on the table. A transaction will block the DDL command until the DML command is terminated with a COMMIT or a ROLLBACK.
73.
UPDATE table
SET column=[subquery] [,column=subquery...]
WHERE column = (subquery) [AND column=subquery...] ;
the subquery must return a scalar value.
74.
deletion (DELETE) can be rolled back whereas a truncation(TRUNCATE) cannot be.
75.
ACID
A is for Atomicity
The principle of atomicity states that all parts of a transaction must complete or none of them.
C is for Consistency
The principle of consistency states that the results of a query must be consistent with the state of the database at the time the query started
I is for Isolation
The principle of isolation states that an incomplete (that is, uncommitted) transaction must be invisible to the rest of the world.
While the transaction is in progress, only the one session that is executing the transaction is allowed to see the changes; all other sessions must see the unchanged data, not the new values.
D is for Durable
The principle of durability states that once a transaction completes, it must be impossible for the database to lose it
76.
If a user issues a DDL or DCL(GRANT or REVOKE) command, the transaction he has in progress (if any) will be committed:
77.
ROLLBACK [TO SAVEPOINT savepoint] ;
78.
SAVEPOINT
The use of savepoints is to allow a programmer to set a marker in a transaction that can be used to control the effect of the ROLLBACK command.
-Savepoint may be used to rollback
-Savepoint can be used for only DML statements.
79.
SELECT FOR UPDATE
Oracle, by default, provides the highest possible level of concurrency: readers do not block writers, and writers do not block readers
here is no problem with one session querying data that another session is updating, or one session updating data that another session is querying. However, there are times when you may wish to change this behavior and prevent changes to data that is being queried.
Example :
select * from regions for update;
The FOR UPDATE clause will place a lock on all the rows retrieved. No changes can be made to them by any session other than that which issued the command, and therefore the subsequent updates will succeed: it is not possible for the rows to have been changed. This means that one session will have a consistent view of the data (it won’t change), but the price to be paid is that other sessions will hang if they try to update any of the locked rows (they can, of course, query them).
The locks placed by a FOR UPDATE clause will be held until the session issuing the command issues a COMMIT or ROLLBACK.
80.
A namespace defines a group of object types, within which all names must be uniquely identified.
These object types all share the same namespace;
- Tables
- Views
- Sequences
- Private synonyms
81.
it is impossible to create a view with the same name as a table.
it is possible for an index to have the same name as a table, even within the same schema.
82.
LONG
All the functionality of LONG (and more) is provided by CLOB;
One LONG column in a table is allowed.
Long columne name can not be included in order by clause.
83.
Tables can be stored in the database in several ways. The simplest is the heap table.A heap is variable length rows in random order.
More advanced table structures, such as the following,may impose ordering and grouping on the rows or force a random distribution:
- Index organized tables
- Index Clusters
- hash Clusters
- partitioned tables
84.
CREATE TABLE [schema.]table [ORGANIZATION HEAP]
(column datatype [DEFAULT expression]
[,column datatype [DEFAULT expression]...);
85.
CREATE TABLE [schema.]table AS subquery;
Any not null and check constraints on the columns will also be applied to the new table, but any primary-key, unique, or foreign-key constraints will not be.
86.
Types of Constraints
UNIQUE
Unique constraints are enforced by an index.When a unique constraint is defined, Oracle will look for an index on the key column(s), and if one does not exist it will be created.
The structure of these indexes (known as B*Tree indexes) does not include NULL values, which is why many rows with NULL are permitted: they simply do not exist in the index.
selecting WHERE key_column IS NULL cannot use the index because it doesn’t include the NULLs.
NOT NULL
you cannot define one not null constraint for the whole group.
PRIMARY KEY
union of a unique constraint and a not null constraint.
FOREIGN KEY
the many-to-one relationships that connect the table, in their third normal form.
ON DELETE CASCADEif a row in the parent table is deleted, Oracle will search the child table for all the matching rows and delete them too.
ON DELETE SET NULLif a row in the parent table is deleted, Oracle will search the child table for all the matching rows and set the foreign key columns to null.If the columns in the child table also have a not null constraint, then the deletion from the parent table will fail.
87.
It is not possible to drop or truncate the parent table in a foreign key relationship.
This still applies if the ON DELETE SET NULL or ON DELETE CASCADE clauses were used.
88.
A table can, however, have any number of unique constraints and not null columns.
89.
it is impossible to define a foreign key constraint that refers to two columns, or a check constraint that refers to any column other than that being constrained if the constraint is defined in line, but both of these are possible if the constraint is defined at the end of the table.
90.
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
[schema.]viewname [(alias [,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;
The FORCE keyword will create the view even if the detail table(s) in the subquery does not exist.
91.
A synonym is an alternative name for an object.
Private synonyms are schema objects.Private synonyms must be a unique name within their schema.
--A public & private synonym can exist with the same name for the same table.
92.
If the object is recreated, the synonym must be recompiled before use.
ALTER SYNONYM synonym COMPILE;
93.
CREATE SEQUENCE [schema.]sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER] ;
It can’t be rolled back
94.
Sequence updates occur independently of the transaction management system.
For this reason, there will always be gaps in the series. The gaps will be larger if the database has been restarted and the CACHE clause was used.
All numbers that have been generated and cached but not yet issued will be lost when the database is shut down. At the next restart, the current value of the sequence will be the last number generated, not the last issued.
So, with the default CACHE of 20, every shutdown/startup will lose up to 20 numbers.
95.
If you want to reset the sequence to its starting value, the only way is to drop it:
96.
Types of Index
- B*Tree : these can be either unique or nonunique
- bitmap
97.
B*Tree
B*Tree indexes are a very efficient way of retrieving rows if the number of rows needed is low in proportion to the total number of rows in the table and if the table is large.
It is often said that if the query is going to retrieve more than 2 to 4 percent of the rows, then a full table scan will be quicker.
A major exception to this is if the value specified in the WHERE clause is NULL. NULLs do not go into B*Tree indexes, so a query such as this:
select * from employees where last_name is null;
will always result in a full table scan.
In general, B*Tree indexes should be used if:
- The cardinality (the number of distinct values) in the column is high.
- The number of rows in the table is high.
- The column is used in WHERE clauses or JOIN conditions
98.
bitmap
A particular advantage that bitmap indexes have over B*Tree indexes is that they include NULLs.
In general, bitmap indexes should be used if:
The cardinality (the number of distinct values) in the column is low (such asmale/female).
The number of rows in the table is high.
The column is used in Boolean algebra (AND/OR/NOT) operations.
99.
CREATE [UNIQUE | BITMAP] INDEX [ schema.]indexname
ON [schema.]tablename (column [, column...] ) ;
100.
When a table is dropped, all the indexes and constraints defined for the table are dropped as well.
If an index was created implicitly by creating a constraint, then dropping the constraint will also drop the index.
If the index had been created explicitly and the constraint created later, then if the constraint is dropped the index will survive.
101.
when you a create a composite primary key,you can add index on composite primary key column.
but not when it is single primary key
102.
The minimum column width that can be specified for a VARCHAR2 data type column
is one.
103.
DROP table tablename;
data along structure is deleted.
view & synonym remain but get invalid.
pending transaction in session is committed.
104.
USING
can be used to restrict the no of columns used in Joins
used to join column with same name & compatible data types.
if you find this information useful, please comment.
