3) How many types of SQL Statements are there in Oracle
3) There are basically 6 types of SQL statements. They are
a) Data Definition Language (DDL):
The DDL statements define and maintain objects and drop objects.
b) Data Manipulation Language (DML):
The DML statements manipulate database data.
c) Transaction Control Statements:
Manage change by DML
d) Data Query Language(DQL):
Select Statement
e) Session Control:
Used to control the properties of current session enabling and disabling roles and changing. e.g. Alter Statements, Set Role
f) System Control Statements:
Change Properties of Oracle Instance. e.g.:: Alter System
g) Embedded SQL:
Incorporate DDL, DML and T.C.S in Programming Language. e.g.:: Using the SQL Statements in languages such as 'C', Open, Fetch, executes and close
4) What is a Transaction in Oracle?
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.
5) Key Words Used in Oracle
The Key words that are used in Oracle are:
a) Committing: A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) Save Point: For long transactions that contain many SQL statements, intermediate markers or save points are declared. Save points can be used to divide a transaction into smaller points.
d) Rolling Forward: Process of applying redo log during recovery is called rolling forward.
e) Cursor: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f) System Global Area (SGA): The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA):: The PGA is a memory buffer that contains data and control information for server process.
h) Database Buffer Cache: Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.
i) Redo log Buffer: Redo log Buffer of SGA stores all the redo log entries.
j) Redo Log Files: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
k) Process: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.
6) What are Procedure, functions and Packages?
Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
Procedures do not return values while Functions return one Value
Packages: Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
7) What are Database Triggers and Stored Procedures
Database Triggers: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT is useful for implementing complex business rules, which cannot be enforced using the integrity rules. We can have the trigger as before trigger or After Trigger and at Statement or Row level.
e.g.:: operations insert, update ,and delete 3
Before, after 3*2 A total of 6 combinations
At statement level (once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures: Stored Procedures are Procedures that are stored in Compiled form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
8) How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows:
a) Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
9) What are the Different Optimization Techniques?
The Various Optimization techniques are
a) Execute Plan: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint:
Set_item_property ('DeptBlock', OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno, Dname, Loc, Rowid from dept
Where (Deptno > 25)
c) Optimize_Sql:
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements. This slow downs the processing because for every time the SQL must be parsed whenever they are executed.
F45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp:
By setting the Optimize_Tp= No, Oracle Forms assigns separate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
F45run module = my_firstform userid = scott/tiger optimize_Tp = No
10) How do u implement the If statement in the Select Statement
We can implement the if statement in the select statement by using the Decode statement.
e.g. select DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done.
11) How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions
E.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
E.g. My_exception exception
When My_exception then
12) What are the inline and the precompiler directives?
The inline and precompiler directives detect the values directly
13) What are snap shots and views?
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated
14) What are the OOPS concepts in Oracle.
Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorize the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects
15) What is the difference between candidate key, unique key and primary key?
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.
16) What is concurrency?
Concurrency is allowing simultaneous access of same data by different users. Locks useful for accessing the database are
a) Exclusive: The exclusive lock is useful for locking the row when an insert, update or delete is being done. This lock should not be applied when we do only select from the row.
b) Share lock: We can do the table as Share Lock as many share locks can be put on the same resource.
17) Privileges and Grants
Privileges are the right to execute a particular type of SQL statements.
e.g. Right to Connect, Right to create, Right to resource
Grants are given to the objects so that the object might be accessed accordingly. The grant has to be given by the owner of the object.
18) Table Space, Data Files, Parameter File, Control Files
Table Space: The table space is useful for storing the data in the database. When a database is created two table spaces are created.
a) System Table space: This data file stores all the tables related to the system and DBA tables
b) User Table space: This data file stores all the user related tables
We should have separate table spaces for storing the tables and indexes so that the access is fast.
Data Files: Every Oracle Data Base has one or more physical data files. They store the data for the database. Every data file is associated with only one database. Once the Data file is created the size cannot change. To increase the size of the database to store more data we have to add data file.
Parameter Files: Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files: Control files record the physical structure of the data files and redo log files. They contain the Db name, name and location of Database, data files, redo log files and time stamp.
19) Physical Storage of the Data
The finest level of granularity of the database is the data blocks.
Data Block: One Data Block correspond to specific number of physical database space
Extent: Extent is the number of specific number of contagious data blocks.
Segments: Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment: Non Clustered Table has data segment data of every table is stored in cluster data segment
b) Index Segment: Each Index has index segment that stores data
c) Roll Back Segment: Temporarily store 'undo' information
d) Temp. Segment: It Used in sort order.
20) What are the PCT Free and PCT Used?
PCT Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly PCT Used is used to denote the percentage of the used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40
21) What is Row Chaining?
The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks.
22) What is a 2 Phase Commit?
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2-phase commit.
a) Prepare Phase: Global coordinator asks participants to prepare
b) Commit Phase: Commit all participants to coordinator to Prepared, Read only or abort Reply
23) What is the difference between deleting and truncating of tables?
Delete
At the simplest level, delete scans the table and removes any rows that match the given criteria in the (optional) where clause. It generates rollback information so that the deletions can be undone should it be necessary. Index entries for the deleted rows are removed from the indexes. You must commit to make the deletions permanent.
When deleting rows from a table, extents are not deallocated, so if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. In addition the High Water Mark is not moved down, so it remains where it was before the deletion began. This means that any subsequent full table scans may (still) take a long time to complete - because a full table scans always scans up to the HWM. So, by example, if a select count(*) from very_large_table; took 15 minutes to complete before all the rows were deleted, you will find that it still takes about 15 mins after the deletion - because Oracle is still scanning every single block up to the HWM - even though some (or all) of the blocks may have no data in them.
In delete database trigger fires, if any.
Truncate
Truncate, on the other hand, simply moves the high water mark on the table right down to the beginning. It does this very quickly, and does not need to be committed. Once you truncate a table, there is no going back. Indexes are also truncated. There is no facility to be able to specify which rows to 'delete' as you can with the where clause on the delete command.
When a table is truncated, all its extents are deallocated leaving only the extents specified when the table was originally created. So if the table was originally created with minextents 3, there will be 3 extents remaining when the tables is truncated.
If you specify the reuse storage clause, then the extents are notdeallocated. This saves time in the recursive SQL department if you intend to reload the table with data from an export for example, and can reduce the time it takes to do the import as there is no need to dynamically allocate any new extents.
Truncate do not fires database trigger.
24) What are mutating tables?
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
25) What are Codd Rules?
Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only RDBMS to satisfy the maximum number of rules.
26) What is Normalisation?
Normalisation is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalisation rules.
a) 1 Normal Form: A table is said to be in 1st Normal Form when the attributes are atomic
b) 2 Normal Form: A table is said to be in 2nd Normal Form when all the candidate keys are dependent on the primary key and it is in 1st normal form.
c) 3rd Normal Form: A table is said to be third Normal form when it is not dependent transitively or all the non-columns are mutually independent. And it is to be in 2nd normal form.
27) Can U disable database trigger? How?
Yes. With respect to table
ALTER TABLE TABLE_NAME
[DISABLE all_trigger]
28) What is a pseudo column? Name them?
A pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:
* CURRVAL
* NEXTVAL
* LEVEL
* ROWID
* ROWNUM
* SYSDATE
* User
29) How many columns can table have?
The number of columns in a table can range from 1 to 1000.
30) Is space acquired in blocks or extents?
In extents.
31) What is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values. Cannot apply for HASH.
32) What are the data types supported by oracle (INTERNAL)?
Varchar2, Number, Char, MLSLABEL.
39) What are attributes of cursor?
%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT
40) Can you use select in FROM clause of SQL select?
Yes.
41) Types of Snapshot Refresh
To ensure that a snapshot is consistent with its master table, you need to refresh the
snapshot periodically. Oracle provides the following three methods to refresh snapshots:
a) Fast refresh uses snapshot logs to update only the rows that have changed since the last refresh.
b) Complete refresh updates the entire snapshot.
c) Force refresh performs a fast refresh when possible. When a fast refresh is not possible, force refresh performs a complete refresh.
When it is important for snapshots to be transactional consistent with each other, you can organize them into refresh groups. By refreshing the refresh group, you can ensure that the data in all of the snapshots in the refresh group correspond to the same transactional consistent point in time. A snapshot in a refresh group still can be refreshed individually, but doing so nullifies the benefits of the refresh group because refreshing the snapshot individually does not refresh the other snapshots in the refresh group.
Database:
- Explain Database Structure.
When Oracle server starts Instance gets initiated. Instance is the creation of SGA in server RAM and initiation of the background process. SGA is divided into shared pool, database buffer; redo log buffer and Java pool. Shared pool consists of data dictionary, where the entire table structures, stored procedures, functions, packages, privileges are stored.
When query fires, it gets parsed in shared pool area where all the syntaxes are checked with proper positions. Verification of tables, columns and grants will be taken place in data dictionary. Search for the required data will start first in shared pool, otherwise data will be fetch from the data files.
2. What is the Back ground processes in Oracle and what are they.
2. This is one of the most frequently asked questions. There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer (DBWR): Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.
b) Log Writer (LGWR): Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transactions commit and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor (SMON): The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure
d) Process Monitor (PMON): The Process Monitor performs process recovery when user Process fails. PMON Clears and Frees resources that process was using.
e) Check Point (CKPT): At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f) Achieves (ARCH): The Archiver copies online redo log files to archival storage when they are busy.
g) Recoveror (RECO): The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn): The Dispatcher is useful in Multi Threaded Architecture
i) Lckn: We can have up to 10 lock processes for inter instance locking in parallel SQL.
- How to find out duplicate rows?
Select * from my_table t1 where exists ( select 'x' from my_table t2 where t2.key_value1 = t1.key_value1 and t2.key_value2 = t1.key_value2 and t2.rowid > t1.rowid );
- How to delete duplicate rows with rowid and without rowid?
delete from my_table t1 where exists ( select 'x' from my_table t2 where t2.key_value1 = t1.key_value1 and t2.key_value2 = t1.key_value2 and t2.rowid > t1.rowid );
- What are types of joins? Where the + (Plus) should be given in outer join. Explain with example. Emp and Dept table.
- what is a schema?
It is an logical grouping of database objects based on the user that owns the object.
- I want to calculate the percentage EUROSALES against PLANNEDEUROSALES on WEEK basis, only for customers where the PLANNEDEUROSALES IS NOT NULL
There is one table SDM_CPLAN_INVOICES_W with the fields
WEEK, CUSTOMER_NUMBER, EUROSALES_TY, PLANEUROSALES
SELECT X.WEEK,
count(Y.perc),
count(X.percentage),
(count(Y.perc)/count(X.percentage))*100 PERC_CUST_REALIZED
FROM (SELECT CPLAN.WEEK WEEK,
FROM (SELECT CPLAN.WEEK WEEK,
CPLAN.CUSTOMER_NUMBER,
CPLAN.EUROSALES_TY,
CPLAN.PLAN_EUROSALES, round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) percentage
FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE CPLAN.BRAND_IND = 'AV'
WHERE CPLAN.BRAND_IND = 'AV'
and cplan.PLAN_EUROSALES != 0
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER, CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES )X ,
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER, CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES )X ,
(SELECT CPLAN.WEEK WEEK,
CPLAN.CUSTOMER_NUMBER,
CPLAN.EUROSALES_TY,
CPLAN.PLAN_EUROSALES, round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) perc
FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE CPLAN.BRAND_IND = 'AV'
WHERE CPLAN.BRAND_IND = 'AV'
and cplan.PLAN_EUROSALES != 0
HAVING round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) >= 100
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER, CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES
)Y
WHERE X.WEEK = Y.WEEK
GROUP BY X.WEEK
I get the following results
WEEK COUNT(Y.PERC) COUNT(X.PERCENTAGE) PERC_CUST_REALIZED
1 32 32 100
2 16 16 100
3 80 80 100
4 48 48 100
5 48 48 100
etc.
this is wrong.
The Good results shoulb something like beneath
HAVING round((CPLAN.EUROSALES_TY/CPLAN.PLAN_EUROSALES)*100) >= 100
GROUP BY CPLAN.WEEK, CPLAN.CUSTOMER_NUMBER, CPLAN.EUROSALES_TY , CPLAN.PLAN_EUROSALES
)Y
WHERE X.WEEK = Y.WEEK
GROUP BY X.WEEK
I get the following results
WEEK COUNT(Y.PERC) COUNT(X.PERCENTAGE) PERC_CUST_REALIZED
1 32 32 100
2 16 16 100
3 80 80 100
4 48 48 100
5 48 48 100
etc.
this is wrong.
The Good results shoulb something like beneath
WEEK COUNT(Y.PERC) COUNT(X.PERCENTAGE) PERC_CUST_REALIZED
1 2 16 12.5
2 1 16 6.25
3 5 16 31.25
4 3 16 18.75
5 3 16 18.75
1 2 16 12.5
2 1 16 6.25
3 5 16 31.25
4 3 16 18.75
5 3 16 18.75
SELECT X.WEEK,
SUM(X.realised) num_realised,
count(*) num_percs,
SUM(X.realised)/count(*)*100 PERC_CUST_REALIZED
FROM
(SELECT CPLAN.WEEK WEEK, DECODE(SIGN(CPLAN.EUROSALES_TY-CPLAN.PLAN_EUROSALES),-1,0,1) realised
FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE CPLAN.BRAND_IND = 'AV' and cplan.PLAN_EUROSALES != 0
)X
GROUP BY X.WEEK;
SUM(X.realised) num_realised,
count(*) num_percs,
SUM(X.realised)/count(*)*100 PERC_CUST_REALIZED
FROM
(SELECT CPLAN.WEEK WEEK, DECODE(SIGN(CPLAN.EUROSALES_TY-CPLAN.PLAN_EUROSALES),-1,0,1) realised
FROM SDM_CPLAN_INVOICES_W CPLAN
WHERE CPLAN.BRAND_IND = 'AV' and cplan.PLAN_EUROSALES != 0
)X
GROUP BY X.WEEK;
- If you want to find the top 10 earners in your company then,
select * from (
select empno,sal,rownum
from emp
order by sal desc
)
where rownum < 11
- Is it possible to run another query if the results of a first query are null?
For example, query one is:
select max(date) from table1 where status = 'B' and account = '123'
Query 2 is:
select max(date) from table 2 where curr_status in('a','b','c') and account = '123'
If the results for query 1 are null, I need the results of query 2, but I don't want to have to test the results of query 1 in my program and then run query 2 if I can get oracle to do the test for me.
Select nvl((select max(date) from table1 where status = 'B' and account = '123'), (select max(date) from table 2 where curr_status in('a','b','c') and account = '123'))
from dual;
from dual;
Select decode(x, 0, date2, date1)
from ( select max(date) date1,
from ( select max(date) date1,
count(*) x
from table1
where status = 'B' and account = '123'
),
( select max(date) date2
( select max(date) date2
from table 2
where curr_status in('a','b','c') and account = '123'
);
I also have tried this type of statement with thousands of resultant records...not only one.-
using the same example we have:
using the same example we have:
SELECT table3.field_a,
nvl(
(select max(date) from table1 A where A.field2 = 'B' and a.field1 = c.field1),
(select max(date) from table2 B where curr_status in('a','b','c') and b.field1 = c.field1)
)
FROM table3 C
WHERE
c.field1='XXX'
.
.
. ;
nvl(
(select max(date) from table1 A where A.field2 = 'B' and a.field1 = c.field1),
(select max(date) from table2 B where curr_status in('a','b','c') and b.field1 = c.field1)
)
FROM table3 C
WHERE
c.field1='XXX'
.
.
. ;
- I have 2 tables...
STUDENT
student_id (pk) Std_name
sjones Sam Jones
jdoe Jane Doe
ENROLLMENT
Student_id (pk) Course#(pk) Grade
---------------------------------------------------------------------
sjones csc211 A
sjones csc212 A-
jdoe ect555 B-
jdoe is404 B
jdoe is421 B+
For each student name, list the course number or numbers the student took where the student obtained the lowest grade.
OK, this is what I have so far...
SELECT student.std_name, enrollment.[course#], enrollment.grade
FROM enrollment INNER JOIN student ON enrollment.student_id = student.student_id;
This lists all of the students and the course #'s and grades. Now, I only want to show the lowest grade and the course # for each student. How can I sort them so B+ is greater than B, but B- is less than B.
sjones csc212 A-
jdoe ect555 B-
jdoe is404 B
jdoe is421 B+
For each student name, list the course number or numbers the student took where the student obtained the lowest grade.
OK, this is what I have so far...
SELECT student.std_name, enrollment.[course#], enrollment.grade
FROM enrollment INNER JOIN student ON enrollment.student_id = student.student_id;
This lists all of the students and the course #'s and grades. Now, I only want to show the lowest grade and the course # for each student. How can I sort them so B+ is greater than B, but B- is less than B.
Answer : you can use the decode function...
select STUDENT_ID, decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, ... -1) from ENROLLMENT
it basically translates the GRADE into a number value ... like a bunch of if statements... where the first parameter is the value to check on... and the last is the default value if no other value is met...
Other way1
select STUDENT_ID, COURSE#, GRADE from ENROLLMENT E1 where
decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1) =
(select min(decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1))
from ENROLLMENT E2 where E1.STUDENT_ID=E2.STUDENT_ID);
STUDENT_ID
---------------
COURSE#
---------------
GRADE
---------------
sjones
csc212
A-
jdoe
etc555
B-
decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1) =
(select min(decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1))
from ENROLLMENT E2 where E1.STUDENT_ID=E2.STUDENT_ID);
STUDENT_ID
---------------
COURSE#
---------------
GRADE
---------------
sjones
csc212
A-
jdoe
etc555
B-
Other Way 2
select a.std_name, a.course#, a.grade from enrollment a, (SELECT student.std_name,min(decode(grade, 'A+', 13, 'A', 12, 'A-', 11, 'B+', 10, 'B', 9, 'B-', 8, 'C+', 7, 'C', 6, 'C-',5,'D+', 4, 'D', 3, 'D-', 2, 'F', 1)) min_grade
FROM enrollment INNER JOIN student ON enrollment.student_id= student.std_name
group by student.std_name) b
where a.std_name=b.std_name
AND a.grade=decode(b.min_grade,13, 'A+', 12, 'A', 11, 'A-', 10, 'B+', 9, 'B', 8, 'B-', 7, 'C+', 6, 'C', 5, 'C-',4,'D+', 3, 'D', 2, 'D-', 1, 'F');
FROM enrollment INNER JOIN student ON enrollment.student_id= student.std_name
group by student.std_name) b
where a.std_name=b.std_name
AND a.grade=decode(b.min_grade,13, 'A+', 12, 'A', 11, 'A-', 10, 'B+', 9, 'B', 8, 'B-', 7, 'C+', 6, 'C', 5, 'C-',4,'D+', 3, 'D', 2, 'D-', 1, 'F');
- I wish to display the value that occurs most frequently. So for record 11 2 appears twice, and 3 only once. Therefore I wish to display 2.
Select id, status
from
(
select id, status, rank() over (partition by id order by stat_count desc, status) as rnk
from
(
select id, status, count(*) as stat_count
from my_table
group by id, status
)
)
where rnk=1
from
(
select id, status, rank() over (partition by id order by stat_count desc, status) as rnk
from
(
select id, status, count(*) as stat_count
from my_table
group by id, status
)
)
where rnk=1
- How many rows of table A in table B:
select count(*) from (select * from <table a> INTERSECT select * from <table b>);
How many rows of tablea NOT in table B
select count(*) from (select * from <table a> MINUS select * from <table b>);
- How will you find no of columns in a table
select count(column_name) from all_tab_columns where table_name = 'EVALUATION9CODE';
- One question on self join query. How to find out the emp_no doing same job but working in different departments.
Select a.emp_no from emp a ,emp b where a.job = b.job and a.dept_no <> b.dept_no;
- Find out the count of three tables in one query. The output should be in the same line.
Select count1, count2 from (select count(*)count1 from tab1), (select count(*)count2 from tab2);
- What are cursors? Difference in implicit and explicit cursors. Cursors attributes?
Cursors (PL/SQL)
=======
Oracle uses work areas to execute SQL statements and store processing
information. A PL/SQL construct called a "cursor" lets you name a work
area and access its stored information. There are two kinds of cursors:
implicit and explicit.
PL/SQL implicitly declares a cursor for all SQL data manipulation
statements, including queries that return only one row. For queries
that return more than one row, you can explicitly declare a cursor
to process the rows individually.
Explicit Cursors
----------------
The set of rows returned by a multi-row query is called the "active
set." Its size is the number of rows that meet your search criteria.
An explicit cursor points to the current row in the active set. This
allows your program to process the rows one at a time.
Multi-row query processing is somewhat like file processing. For
example, a COBOL program opens a file to process records, then closes
the file. Likewise, a PL/SQL program opens a cursor to process rows
returned by a query, then closes the cursor. Just as a file pointer
marks the current position in an open file, a cursor marks the current
position in an active set.
You use three commands to control the cursor: OPEN, FETCH, and CLOSE.
First, you initialize the cursor with the OPEN statement, which
identifies the active set. Then, you use the FETCH statement to
retrieve the first row. You can execute FETCH repeatedly until all
rows have been retrieved. When the last row has been processed, you
release the cursor with the CLOSE statement.
Forward references are not allowed in PL/SQL. So, you must declare a
cursor before referencing it in other statements. You define a cursor
in the declarative part of a PL/SQL block, subprogram, or package by
naming it and specifying a query. In the following example, you declare
a cursor named "c1":
DECLARE
CURSOR c1 IS SELECT ename, deptno FROM emp WHERE sal > 2000;
...
The cursor name is an undeclared identifier, not the name of a PL/SQL
variable. You cannot assign values to a cursor name or use it in an
expression.
Explicit cursors can take parameters, as the example below shows.
A cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
To declare formal cursor parameters, you use the syntax
CURSOR cursor_name [(parameter [, parameter, ...])] IS SELECT ...
where "parameter" stands for the following syntax:
parameter_name [IN] datatype [{:= | DEFAULT} expr]
The formal parameters of a cursor must be IN parameters. Therefore, they
cannot return values to actual parameters.
As the example below shows, you can initialize cursor parameters to default
values. That way, you can pass different numbers of actual parameters to a
cursor, accepting or overriding the default values as you please. Moreover,
you can add new formal parameters without having to change every reference
to the cursor.
DECLARE
CURSOR c1
(low INTEGER DEFAULT 0,
high INTEGER DEFAULT 99) IS SELECT ...
The scope of cursor parameters is local to the cursor, meaning that they
can be referenced only within the query used in the cursor declaration.
The values of cursor parameters are used by the associated query when
the cursor is opened.
Implicit Cursors
----------------
Oracle implicitly opens a cursor to process each SQL statement not
associated with an explicitly declared cursor. PL/SQL lets you refer
to the most recent implicit cursor as the "SQL" cursor. So, although
you cannot use the OPEN, FETCH, and CLOSE statements to control an
implicit cursor, you can still use cursor attributes to get information
about the most recently executed SQL statement.
The values of cursor attributes always refer to the most recently
executed SQL statement, wherever that statement appears. It might be
in a different scope (for example, in a sub-block). So, if you want to
save an attribute value for later use, assign it to a Boolean variable
immediately. The following example shows how failing to save an
attribute value can result in a logic bug:
UPDATE parts SET qty = qty - 1 WHERE partno = part_id;
check_parts; -- procedure call
IF SQL%NOTFOUND THEN -- dangerous!
...
END IF;
In this example, it is dangerous to rely on the IF condition because the
procedure "check_parts" might have changed the value of %NOTFOUND. You
can debug the code as follows:
UPDATE parts SET qty = qty - 1 WHERE partno = part_id;
sql_notfound := SQL%NOTFOUND;
check_parts;
IF sql_notfound THEN
...
END IF;
Before Oracle opens the SQL cursor, the implicit cursor attributes
yields NULL.
Cursor Attributes
%FOUND Attribute (PL/SQL)
================
When appended to the name of a cursor or cursor variable, the %FOUND
attribute returns useful information about the execution of a multi-row
query. When appended to the name of the SQL implicit cursor (SQL),
%FOUND returns useful information about the most recently executed
INSERT, UPDATE, DELETE, or SELECT INTO statement.
Explicit Cursors
----------------
%FOUND is the logical opposite of %NOTFOUND. After a cursor or cursor
variable is opened but before the first fetch, %FOUND yields NULL.
Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if
the last fetch failed to return a row.
In the following example, you use %FOUND to select an action:
LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%FOUND THEN -- fetch succeeded
...
ELSE -- fetch failed, so exit loop
EXIT;
END IF;
...
...
END LOOP;
If a cursor or cursor variable is not open, referencing it with %FOUND
raises the predefined exception INVALID_CURSOR.
Implicit Cursors
----------------
%FOUND is the logical opposite of %NOTFOUND. Until a SQL data
manipulation statement is executed, %FOUND yields NULL. Thereafter,
%FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected
one or more rows or a SELECT INTO statement returned one or more rows.
Otherwise, %FOUND yields FALSE. In the following example, you use %FOUND
to insert a row if a deletion succeeds:
DELETE FROM emp WHERE empno = my_empno;
IF SQL%FOUND THEN -- delete succeeded
INSERT INTO new_emp VALUES (my_empno, my_ename, ...);
...
END IF;
When appended to the name of a cursor or cursor variable, the %NOTFOUND
attribute returns useful information about the execution of a multi-row
query. When appended to the name of the SQL implicit cursor (SQL),
%NOTFOUND returns useful information about the most recently executed
INSERT, UPDATE, DELETE, or SELECT INTO statement.
Explicit Cursors
----------------
When a cursor or cursor variable is opened, the rows that satisfy the
associated query are identified and form the active set. Before the
first fetch, %NOTFOUND yields NULL. Rows are fetched from the active set
one at a time. If the last fetch returned a row, %NOTFOUND yields FALSE.
If the last fetch failed to return a row, %NOTFOUND yields TRUE.
In the following example, you use %NOTFOUND to exit a loop when the
FETCH statement fails to return a row:
LOOP
FETCH c1 INTO my_ename, my_deptno;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
If a cursor or cursor variable is not open, referencing it with
%NOTFOUND raises the predefined exception INVALID_CURSOR.
Implicit Cursors
----------------
%NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected
no rows or a SELECT INTO statement returned no rows. Otherwise, %NOTFOUND
yields FALSE. In the following example, you use %NOTFOUND to insert a new
row if an update fails:
UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;
IF SQL%NOTFOUND THEN -- update failed
INSERT INTO errors VALUES (...);
END IF;
%ISOPEN Attribute (PL/SQL)
=================
When appended to the name of a cursor or cursor variable, the %ISOPEN
attribute returns the status of the cursor or cursor variable.
Explicit Cursors
----------------
%ISOPEN yields TRUE if its cursor or cursor variable is open; otherwise,
%ISOPEN yields FALSE. In the following example, you use %ISOPEN to select
an action:
IF c1%ISOPEN THEN -- cursor is open
...
ELSE -- cursor is closed, so open it
OPEN c1;
END IF;
Implicit Cursors
----------------
Oracle closes the SQL cursor automatically after executing its
associated SQL statement. As a result, %ISOPEN always yields FALSE.
%ROWCOUNT Attribute (PL/SQL)
===================
When appended to the name of a cursor or cursor variable, the %ROWCOUNT
attribute returns useful information about the execution of a multi-row
query. When appended to the name of the SQL implicit cursor (SQL),
%ROWCOUNT returns useful information about the most recently executed
INSERT, UPDATE, DELETE, or SELECT INTO statement.
Explicit Cursors
----------------
When you open its cursor or cursor variable, %ROWCOUNT is zeroed. Before
the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of
rows fetched so far. The number is incremented if the last fetch returned
a row. In the next example, you use %ROWCOUNT to take action if more than
ten rows have been fetched:
LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;
If a cursor or cursor variable is not open, referencing it with
%ROWCOUNT raises the predefined exception INVALID_CURSOR.
Implicit Cursors
----------------
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or
DELETE statement or returned by a SELECT INTO statement. %ROWCOUNT
yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows or
a SELECT INTO statement returned no rows. In the following example, you
use %ROWCOUNT to take action if more than ten rows have been deleted:
DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN
...
END IF;
If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
In any DML statement if we want to know how many records are processed then SQL%ROWCOUNT attribute can be used.
In implicit cursor SQL%ROWCOUNT will give the total count of records.
In explicit cursor CUR%ROWCOUNT will give the incremental record count.
In Implicit cursor %ISOPEN is always FALSE.
- What are exceptions? Different types of exceptions? Pragma exception in detail.
Three types of Exceptions are there
a) System Defined Exception When
b) User Defined Exception
c) Pragma Exception
Predefined Exceptions (PL/SQL)
=====================
An internal exception is raised implicitly whenever a PL/SQL program
violates an Oracle rule or exceeds a system-dependent limit. Every
Oracle error has a number, but exceptions must be handled by name.
So, PL/SQL predefines some common Oracle errors as exceptions.
For example, the predefined exception NO_DATA_FOUND is raised if a
SELECT INTO statement returns no rows.
PL/SQL declares predefined exceptions globally in package STANDARD,
which defines the PL/SQL environment. So, you need not declare them
yourself. You can write handlers for predefined exceptions using the
names shown in the table below. Also shown are the corresponding
Oracle error codes.
Exception Error Raised if ...
----------------------------------------------------------------
CURSOR_ALREADY_OPEN ORA-06511 you try to OPEN an already open
cursor; you must CLOSE a cursor
before you can reOPEN it
DUP_VAL_ON_INDEX ORA-00001 you try to INSERT or UPDATE
duplicate values in a UNIQUE
database column
INVALID_CURSOR ORA-01001 you try an illegal cursor
operation such as closing an
unopened cursor
INVALID_NUMBER ORA-01722 the conversion of a character
string to a number fails in a SQL statement
LOGIN_DENIED ORA-01017 you log on to Oracle with an
invalid username/password
NO_DATA_FOUND ORA-01403 a SELECT INTO returns no rows, or you refer to
an uninitialized row in a PL/SQL table
NOT_LOGGED_ON ORA-01012 your PL/SQL program issues a
database call without being logged on to Oracle
PROGRAM_ERROR ORA-06501 PL/SQL has an internal problem such as exiting
a function that has no RETURN statement
ROWTYPE_MISMATCH ORA-06504 the host cursor variable and PL/SQL cursor
variable involved in an assignment have incompatible return types
STORAGE_ERROR ORA-06500 PL/SQL runs out of memory or memory is
corrupted
TIMEOUT_ON_RESOURCE ORA-00051 a timeout occurs while Oracle is waiting for a
resource
TOO_MANY_ROWS ORA-01422 a SELECT INTO returns more than one row
VALUE_ERROR ORA-06502 the conversion of a character string to a number
fails in a procedural statement, or an arithmetic, conversion, truncation, or constraint error occurs
ZERO_DIVIDE ORA-01476 you try to divide a number by zero
Redeclaring Predefined Exceptions
---------------------------------
Remember, PL/SQL declares predefined exceptions globally in package
STANDARD, so you need not declare them yourself. Redeclaring predefined
exceptions is error-prone because your local declaration overrides the
global declaration. For example, if you declare an exception named
"invalid_number" and then PL/SQL raises the predefined exception
INVALID_NUMBER internally, a handler written for INVALID_NUMBER will
not catch the internal exception. In such cases, you must use dot
notation to specify the predefined exception, as follows:
...
EXCEPTION
WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
-- handle the error
...
END;
EXCEPTION_INIT Pragma (PL/SQL)
=====================
To handle unnamed internal exceptions, you must use the OTHERS handler
or the pragma EXCEPTION_INIT. A "pragma" is a compiler directive, which
can be thought of as a parenthetical remark to the compiler. Pragmas
(also called "pseudoinstructions") are processed at compile time, not at
run time. They do not affect the meaning of a program; they simply
convey information to the compiler.
The predefined pragma EXCEPTION_INIT tells the PL/SQL compiler to
associate an exception name with an Oracle error number. That allows
you to refer to any internal exception by name and to write a specific
handler for it. You code the pragma EXCEPTION_INIT in the declarative
part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);
where "exception_name" is the name of a previously declared exception.
The pragma must appear somewhere after the exception declaration in the
same declarative part, as shown in the following example:
DECLARE
insufficient_privileges EXCEPTION;
PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
-----------------------------------------------------
-- Oracle returns error number -1031 if, for example,
-- you try to UPDATE a table for which you have
only SELECT privileges
BEGIN
...
EXCEPTION
WHEN insufficient_privileges THEN
-- handle the error
...
END;
- Hierarchy of exception? (when others should be last)
Scope Rules
-----------
You cannot declare an exception twice in the same block. You can,
however, declare the same exception in two different blocks.
Exceptions declared in a block are considered local to that block and
global to all its sub-blocks. Because a block can reference only local
or global exceptions, enclosing blocks cannot reference exceptions
declared in a sub-block.
If you redeclare a global exception in a sub-block, the local
declaration prevails. So, the sub-block cannot reference the global
exception unless it was declared in a labeled block, in which case the
following syntax is valid:
block_label.exception_name
The next example illustrates the scope rules:
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
...
---------------- beginning of sub-block ----------------
DECLARE
past_due EXCEPTION; -- this declaration prevails
acct_num NUMBER;
BEGIN
...
IF ... THEN
RAISE past_due; -- this is not handled
END IF;
...
END;
------------------- end of sub-block -------------------
EXCEPTION
WHEN past_due THEN -- does not handle RAISEd exception
...
END;
The enclosing block does not handle the RAISEd exception because the
declaration of "past_due" in the sub-block prevails. Though they share
the same name, the two "past_due" exceptions are different, just as
the two "acct_num" variables share the same name but are different
variables. Therefore, the RAISE statement and the WHEN clause refer
to different exceptions. To have the enclosing block handle the RAISEd
exception, you must remove its declaration from the sub-block or define
an OTHERS handler.
- In emp table the fields are empno,sex,deptno. Find out the department no wise total count of employee, count of male, count of female.
select dept, sum(decode(sex,'M',1,0)) MALE, sum(decode(sex,'F',1,0)) FEMALE, count(decode(sex,'M',1,'F',1)) TOTAL from my_emp_table group by dept;- Find out the 3rd max salary in emp table.
a) SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC) WHERE ROWNUM < 10; b) SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT(DISTINCT maxcol) FROM my_table b WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC;- Find out the record between 70 and 80.
a) SELECT * FROM ( SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 ) WHERE RN between 91 and 100 ; b) SELECT rownum, f1 FROM t1 GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;- Update the salary of emp table as: salary between 1000-1999 1.2%sal, 2000- 2999 1.5% otherwise don’t update. In a single update statement.
a) select f2, sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100", sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59", sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29" from my_table group by f2; b) select ename "Name", sal "Salary", decode( trunc(f2/1000, 0), 0, 0.0, 1, 0.1, 2, 0.2, 3, 0.31) "Tax rate" from my_table;- What is Public synonym? What’s the use?
CREATE SYNONYM command
PURPOSE:
To create a synonym. A synonym is an alternative name for a table,
view, sequence, procedure, stored function, package, snapshot, or
another synonym.
SYNTAX:
CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object[@dblink]
where:
PUBLIC
creates a public synonym. Public synonyms are accessible to all
users. If you omit this option, the synonym is private and is
accessible only within its schema.
schema
is the schema to contain the synonym. If you omit schema, Oracle
creates the synonym in your own schema.
synonym
is the name of the synonym to be created.
FOR
identifies the object for which the synonym is created. If you do
not qualify object with schema, Oracle assumes that the object is in
your own schema. The object can be of these types:
* table
* view
* sequence
* stored procedure, function, or package
* snapshot
* synonym
The object cannot be contained in a package. Note that the object
need not currently exist and you need not have privileges to access
the object.
You can use a complete or partial dblink to create a synonym for an
object on a remote database where the object is located. If you
specify dblink and omit schema, the synonym refers to an object in
the schema specified by the database link. Oracle Corporation
recommends that you specify the schema containing the object in the
remote database.
If you omit dblink, Oracle assumes the object is located on the
local database.
PREREQUISITES:
To create a private synonym in your own schema, you must have CREATE
SYNONYM system privilege.
To create a private synonym in another user's schema, you must have
CREATE ANY SYNONYM system privilege. If you are using Trusted
Oracle in DBMS MAC mode, your DBMS label must dominate the creation
label of the owner of schema to contain the synonym.
To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM
system privilege.
- How to tune a query? Explain Plan and TKPROF.
Oracle's Explain Plan
Whenever you read or write data in Oracle, you do so by issuing an SQL Statement. One of Oracle's task when it receives such a statement is to build a statement execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.
If one wants to explore such an execution plan, Oracle provides the SQL Statement EXPLAIN PLAN to determine this.
The general syntax of EXPLAIN PLAN is
EXPLAIN PLAN [SET STATEMENT_ID=<id>; [INTO <table>] FOR <here goes the sql statement>. If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the INTO <table> part of the EXPLAIN PLAN command. If you omit the INTO <table> clause, Oracle fills a default table: PLAN_TABLE. The Plan Table
The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL Statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.
The fields (attributes) within the plan table
Arguably, the most important fields within the plan table are operation, option, object_name, id, and parent_id. The pair operation and object_name define what operation would be done on (or with) object_name. If an operation has an id which other operations have as parent_id, it means the other operations feed their result to the parent.
Possible values for operation are: DELETE STATEMENT, INSERT STATEMENT, SELECT STATEMENT, UPDATE STATEMENT, AND-EQUAL, CONNECT BY, CONCATENATION, COUNT, DOMAIN INDEX, FILTER, FIRST ROW, FOR UPDATE, HASH JOIN, INDEX, INLIST ITERATOR, INTERSECTION, MERGE JOIN, MINUS, NESTED LOOPS, PARTITION, REMOTE, SEQUENCE, SORT, TABLE ACCESS, UNION and VIEW.
Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others. Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.
dbms_xplan
As if 9i, dbms_xplancan be used to format the plan table.
Operations
TABLE ACCESS
The following table is used to demonstrate EXPLAIN PLAN:
CREATE TABLE test_for_ep (a number, b varchar2(100));Now, let's explain the plan for selecting everything on that table:
delete plan_table;explain plan for select /*+ rule */ * from test_for_ep where a = 5;Displaying the execution plan
In order to view the explained plan, we have to query the plan table:
select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object"from plan_table start with id = 0 connect by prior id=parent_id; Here's the output of the explain plan:
SELECT STATEMENT () TABLE ACCESS (FULL) TEST_FOR_EPFirst, take a look at the indention: TABLE ACCESS is indented right. In an explain plan output, the more indented an operation is, the earlier it is executed. And the result of this operation (or operations, if more than one have are equally indented AND have the same parent) is then feeded to the parent operation. In this case, TABLE ACCESS is made first, and its result feeded to SELECT STATEMENT (which is not an actual operation). Note the FULL in paranthesis in TABLE ACCESS: this means that the entire table is accessed.
Now, let's create an index on that table:
create index test_for_ep_ix on test_for_ep (a);And do the same select statement again:
delete plan_table;explain plan for select /*+ rule */ * from test_for_ep where a = 5;The plan is now:
SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP INDEX (RANGE SCAN) TEST_FOR_EP_IXObviously, the index (TEST_FOR_EP_IX) is used first (most indented) then used for a TABLE ACCESS, second most indented, then the result is returned. The table access is not done by a FULL table scan but rather by using the data's rowid.
INDEX
In the last example, Oracle employed an INDEX (RANGE SCAN). The RANGE SCAN basically means, that the index was used, but that it can return more than one row. Now, we create a unique index to see how this alters the explain plan:
create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a)); delete plan_table;explain plan for select /*+ rule */ * from test_for_ep where a = 5;The explained plan is:
SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP INDEX (UNIQUE SCAN) UQ_TPINDEX (UNIQUE SCAN) means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid. What happens, if we query the field not for equality but for greater than (a>5)?
explain plan for select /*+ rule */ * from test_for_ep where a > 5;Here, we see that the index is used, but for a RANGE SCAN:
SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP INDEX (RANGE SCAN) UQ_TPIf we only query fields of a table that are already in an index, Oracle doesn't have to read the data blocks because it can get the relevant data from the index:
create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a)); delete plan_table;explain plan for select /*+ rule */ a from test_for_ep where a > 5 and a < 50;Here's the execution plan. No table access anymore!
SELECT STATEMENT () INDEX (RANGE SCAN) UQ_TPMERGE JOIN
Usually, you want to select data across multiple table which is referred to as to join the tables. MERGE JOIN is just one possible method of joining the data (the others are HASH JOIN, NESTED LOOPS and CLUSTER JOIN). A Sort Merge Join basically sorts all relevant rows in the first table by the join key (ba), and also sorts the relevant rows in the second table by the join key (aa), and then merges these sorted rows. Take an example! At a garage sale you can buy 400 books. The deal is to take all or none. You take all. Now, you have to find the books that you already have at home. How would you go about it? Probably, you'd do a merge join: first, you sort your books by the primary key (author, title), then you sort the 400 books by their primary key (auther, title). Now, you start at the top of both piles. If the value of the left piles primary key is higher, then you take a book from the right pile and vice versa. When both values are equal, then you have found a dublicate. To demonstrate a MERGE JOIN, two tables need to be created:
create table test_for_ep_a (aa number, ab varchar2(100));create table test_for_ep_b (ba number, bb varchar2(100));Note, there are no indexes on both of the tables. Now, we join the tables on aa and ba:
explain plan for select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where a.aa=b.ba and a.aa > 5;As there are no indexes, both tables must be TABLE ACCESSed (FULL). After these accesses, their results are sorted.
SELECT STATEMENT () MERGE JOIN () SORT (JOIN) TABLE ACCESS (FULL) TEST_FOR_EP_B SORT (JOIN) TABLE ACCESS (FULL) TEST_FOR_EP_AThe behaviour of MERGE JOINS is influenced by the init.ora parameters sort_area_size and db_file_mutliblock_read_count.
Note MERGE JOINs can only be used for equi joins, as is demonstrated in NESTED LOOPS
NESTED LOOPS
For each relevant row in the first table (driving table), find all matching rows in the other table (probed table).
create table test_for_ep_a (aa number, ab varchar2(100));create table test_for_ep_b (ba number, bb varchar2(100));explain plan for select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where a.aa > b.ba and a.aa > 5;Note, there is no equi join to join test_for_ep_a and test_for_ep_b, (a.aa > b.ba)
SELECT STATEMENT () NESTED LOOPS () TABLE ACCESS (FULL) TEST_FOR_EP_B TABLE ACCESS (FULL) TEST_FOR_EP_ANow, we put an index on TEST_FOR_EP_B and see how that influences our nested loop:
create table test_for_ep_a (aa number, ab varchar2(100));create table test_for_ep_b (ba number, bb varchar2(100), constraint uq_ba unique(ba)); delete plan_table;explain plan for select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where a.aa > b.ba; The plan is:
SELECT STATEMENT () NESTED LOOPS () TABLE ACCESS (FULL) TEST_FOR_EP_A INDEX (RANGE SCAN) UQ_BAInterpreted, this means: TEST_FOR_EP_A is fully accessed and for each row, TEST_FOR_EP_B (or more accurately, its index UQ_BA) is probed. Thinking about it, this makes sense, doing the costly TABLE ACCESS once and use the index for each row. Then again, thinking about it, if TEST_FOR_EP_A is very small nad TEST_FOR_EP_B is large, this doesn't make sense anymore. This is when the Cost Based Optimizer comes into play.
Sorts
Aggregate Sorts
Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE. Consider the following example:
create table t_ep ( w date, v number, x varchar2(40)); delete plan_table;explain plan for select /*+ rule */ max(w) from t_ep where v=4;SELECT STATEMENT () SORT (AGGREGATE) TABLE ACCESS (FULL) T_EPNow: creating an index:
alter table t_ep add constraint uq_t_ep unique(v);delete plan_table;explain plan for select /*+ rule */ max(w) from t_ep where v=4;SELECT STATEMENT () SORT (AGGREGATE) TABLE ACCESS (BY INDEX ROWID) T_EP INDEX (UNIQUE SCAN) UQ_T_EP EXPLAIN PLAN command
PURPOSE:
To determine the execution plan Oracle follows to execute a
specified SQL statement. This command inserts a row describing each
step of the execution plan into a specified table. If you are using
cost-based optimization, this command also determines the cost of
executing the statement.
SYNTAX:
EXPLAIN PLAN
[SET STATEMENT ID = 'text']
[INTO [schema.]table[@dblink]]
FOR statement
where:
SET
specifies the value of the STATEMENT_ID column for the rows of the
execution plan in the output table. If you omit this clause, the
STATEMENT_ID value defaults to null.
INTO
specifies the schema, name, and database containing the output
table. This table must exist before you use the EXPLAIN PLAN
command. If you omit schema, Oracle assumes the table is in your
own schema.
The dblink can be a complete or partial name of a database link to a
remote Oracle7 database where the output table is located. You can
only specify a remote output table if you are using Oracle with the
distributed option. If you omit dblink, Oracle assumes the table is
on your local database.
If you omit the INTO clause altogether, Oracle assumes an output
table named PLAN_TABLE in your own schema on your local database.
FOR
specifies a SELECT, INSERT, UPDATE, or DELETE statement for which
the execution plan is generated.
PREREQUISITES:
To issue an EXPLAIN PLAN statement, you must have the privileges
necessary to insert rows into an existing output table that you
specify to hold the execution plan. For information on these
privileges, see the INSERT command.
You must also have the privileges necessary to execute the SQL
statement for which you are find the execution plan. If the SQL
statement accesses a view, you must have privileges to access any
tables and views on which the view is based. If the view is based
on another view that is based on a table, you must have privileges
to access both the other view and its underlying table.
To examine the execution plan produced by an EXPLAIN PLAN statement,
you must have the privileges necessary to query the output table.
For more information on these privileges, see the SELECT command.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must dominate the output table's creation label or you must satisfy
one of these criteria:
* If the output table's creation label is higher than your DBMS
label, you must have READUP and WRITEUP system privileges.
* If the output table's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
EXPLAIN PLAN Usage
When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. Once you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing. Interpretation of the execution plan is beyond the scope of this article.
Plan Table
The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows:
SQL> @%ORACLE_HOME%\rdbms\admin\utlxplan.sqlSQL> GRANT select, insert, update, delete ON plan_table TO public;SQL> CREATE PUBLIC SYNONYM plan_table FOR .plan_table;AUTOTRACE - The Easy Option?
Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.
SQL> SET AUTOTRACE ONSQL> SELECT * FROM dual; D-X 1 row selected. Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics---------------------------------------------------------- 0 recursive calls 4 db block gets 1 consistent gets 0 physical reads 0 redo size 363 bytes sent via SQL*Net to client 429 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time.
EXPLAIN PLAN
The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained:
SQL> EXPLAIN PLAN FOR 2 SELECT * FROM dual; Explained. SQL>Then the execution plan displayed:
SQL> @%ORACLE_HOME%\rdbms\admin\utlxpls.sql Plan Table----------------------------------------------------------------------------------------------------| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |-----------------------------------------------------------------------------------------------------| SELECT STATEMENT | | | | | | || TABLE ACCESS FULL |DUAL | | | | | |----------------------------------------------------------------------------------------------------- 5 rows selected. SQL>For parallel queries use the utlxplp.sql script instead of utlxpls.sql.
Statement ID
If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.
SQL> EXPLAIN PLAN STATEMENT_ID = 'TIM' FOR 2 SELECT * FROM dual; Explained. SQL>By default the Oracle scripts to not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.
Oracle's TKPROF
Enabling SQL TRACE
To be able to use TKPROF, you must first enable sql trace. This can be done for either the instance or the session. If you want to change it for the entire instance, set sql_trace=trueinto the init.orafile and restart the instance. However, usually, you'll want to turn on sql trace for a particular session only. This can be done like so:
or, from another session with a
sys.dbms_system.set_sql_trace_in_session(session's id,serial number)Timed Statistics
You can have Oracle include timing information into the sql trace files as well. Either set the timed_statisticsparameter to truo or or issue an
or
Finding the SQL Trace file
The trace file will be written into the directory pointed to by the parameter user_dump_dest. You can query for the value with select value from v$parameterwhere name = 'user_dump_dest'.
See find the trace file to see where the tk prof file goes.
Maximum size of the trace file
The init parameter max_dump_file_sizeallows to set the maximum size of the trace file. Valid values for this parameter are: unlimited, a number followed by a K or a M or a number. If the value is a number only, it indicates how many OS Blocks the file can grow to. K or M set the file's maximum size in kilo or mega bytes.
Using tkprof
Follow the following links:
The meaning of count, cpu and elapsed in tkprof
Be sure to also take a look at Basics of tkprof.
In order to demonstrate the meaning of count, cpu and elapsed in tkprof, three different and simple SQL statements are executed. One of these SQL Statements is executed three times, one is executed twice and one is executed once. The only thing these statements actually do is call dbms_lock.sleep.
set feedback off select spid from v,v where audsid= sys_context('userenv','sessionid') and addr=paddr; alter session set sql_trace=true; begin /* three times */ dbms_lock.sleep(1);end;/ begin /* once */ dbms_lock.sleep(1);end;/ begin /* three times */ dbms_lock.sleep(1);end;/ begin /* twice */ dbms_lock.sleep(2);end;/begin /* twice */ dbms_lock.sleep(2);end;/ begin /* three times */ dbms_lock.sleep(1);end;/alter session set sql_trace=false;If these statements are run and the trace file is tkprof'ed, it produces the following output:
begin /* three times */ dbms_lock.sleep(1);end; call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 3 0.01 0.01 0 0 0 0Execute 3 0.00 3.10 0 0 0 3Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 6 0.01 3.11 0 0 0 3 Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 206 -------------------- begin /* twice */ dbms_lock.sleep(2);end; call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.01 0 0 0 0Execute 2 0.00 4.09 0 0 0 2Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 4.10 0 0 0 2 Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 206 -------------------- begin /* once */ dbms_lock.sleep(1);end; call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.01 0.01 0 0 0 0Execute 1 0.00 1.03 0 0 0 1Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.01 1.04 0 0 0 1 Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 206 Query and current in tkprof
Be sure to also take a look at Basics of tkprof.
Setting up a demonstration environment
A table is created to demonstrate the use of TKPROF. This PL/SQL Script will take care of this.
Ok, now let's give it a try:
alter session set sql_trace=true;alter session set timed_statistics=true;select id, ob from test_for_tk_1 where nm='elit magna wisi nulla praesent possim';When the statement returns, we go to the directory pointed to by user_dump_dest and do a:
tkprof ora01676.trc perfThen, we open the file perf.prf which was created by tkprof and search for the statement:
select id, ob from test_for_tk_1 where nm='elit magna wisi nulla praesent possim' call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 1 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 2.49 30.56 37355 75405 4 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 2.49 30.56 37355 75405 5 1 Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 18 Rows Row Source Operation------- --------------------------------------------------- 1 TABLE ACCESS FULL TEST_FOR_TK_1How is this table to be interpreted? First, the statement was parsedand executed once.
query
Query: also known as consistent gets
The TKPROF output includes in his header the following explanation:
query = number of buffers gotten for consistent read
query = number of buffers gotten for consistent read
query means: blocks gotten in consistent mode. That could possibly entail reading the rollback segment to reconstruct the data.
Most often, query gets
current
current: also known as db block gets.
The TKPROF output includes in his header the following explanation:
current = number of buffers gotten in current mode (usually for update)
current = number of buffers gotten in current mode (usually for update)
current mode means: the blocks are read as they are (either in the datafileor still in the buffer cache). Current gets might entail waiting for some data.
- A column varchar having numbers and characters mixed. How will get only numbers from it. For e.g. 123AB4CD to 1234.
Select replace(translate(‘123AB4CD’,’ABCD’,’A’),’A’) from dual;
- How will get the reverse of Ename column in employee. Suppose you have ename as vidhya output should be ayhdiv. Without using reverse funtion.
- Can a package have only specification without body?
Yes. But you can’t have Body without Specification.
- Can a package body be compiled separately?
Yes.
- What is the difference between EXIST and IN?
Exist gives the value as True and False depends upon the query gives records or not.
You can give multiple value in the predicate ‘IN’ which will be true for the every value given as in the quotes.
- Diiference between rownum and rowid? Why only < is allowed in rownum?
Rowid stores the physical address of the records as a hexadecimal number.
Rownum is psuedo column getting the number while query is executing.
- Find out employee number and names from emp having salary greater than Jones salary.
Use of subquery.
- How to convert an amount in number into amount in words?
select to_char(to_date('123','J'),'JSP') from dual;
- What are purity levels in functions?
1.15 Calling PL/SQL Functions in SQL
Stored functions can be called from SQL statements in a manner similar to built-in functions like DECODE, NVL, or RTRIM. This is a powerful technique for incorporating business rules into SQL in a simple and elegant way. Unfortunately, there are a number of caveats and restrictions.
The most notable caveat is that stored functions executed from SQL are not guaranteed to follow the read consistency model of the database. Unless the SQL statement and any stored functions in that statement are in the same read-consistent transaction (even if they are read-only), each execution of the stored function will look at a different time-consistent set of data. To avoid this potential problem, you need to ensure read consistency programmatically by issuing the SET TRANSACTION READ ONLY or SET TRANSACTION SERIALIZABLE statement before executing your SQL statement containing the stored function. A COMMIT or ROLLBACK then needs to follow the SQL statement to end this read-consistent transaction.
1.15.1 Syntax for Calling Stored Functions in SQL
The syntax for calling a stored function from SQL is the same as referencing it from PL/SQL:
[schema_name.][pkg_name.]func_name[@db_link] [parm_list]schema_name is optional and refers to the user/owner of the function or package. pkg_name is optional and refers to the package containing the called function. func_name is mandatory and is the function name. db_link is optional and refers to the database link name to the remote database containing the function. parm_list is optional, as are the parameters passed to the function.
The following are example calls to the GetTimestamp function in the time_pkg example seen earlier in the Section 1.14.1, "Overview of Package Structure " section:
-- Capture system events. INSERT INTO v_sys_event (timestamp ,event ,qty_waits) SELECT time_pkg.GetTimestamp ,event ,total_waits FROM v$system_event -- Capture system statistics.INSERT INTO v_sys_stat (timestamp,stat#,value) SELECT time_pkg.GetTimestamp ,statistic# ,value FROM v$sysstat;1.15.2 Requirements and Restrictions on Stored Functions in SQL
There are a number of requirements for calling stored functions in SQL:
· The function must be a single-row function -- not one that operates on a column or group function.
· All parameters must be IN; no IN OUT or OUT parameters are allowed.
· The datatypes of the function's parameters and RETURN must be compatible with RDBMS datatypes. You cannot have arguments or RETURN types like BOOLEAN, programmer-defined record, index-by table, etc.
· The parameters passed to the function must use positional notation; named notation is not supported.
· Functions defined in packages must have a RESTRICT_REFERENCES pragma in the specification (Oracle8.0 and earlier).
· The function must be stored in the database, not a local program, Developer/2000 PL/SQL library, or Form.
1.15.3 Calling Packaged Functions in SQL
Prior to Oracle8i Release 8.1, it was necessary to assert the purity level of a packaged procedure or function when using it directly or indirectly in a SQL statement. Beginning with Oracle8i Release 8.1, the PL/SQL runtime engine determines a program's purity level automatically if no assertion exists.
The RESTRICT_REFERENCES pragma asserts a purity level. The syntax for the RESTRICT_REFERENCES pragma is:
PRAGMA RESTRICT_REFERENCES (program_name | DEFAULT, purity_level);The keyword DEFAULT applies to all methods of an object type or all programs in a package. There can be from one to five purity levels, in any order, in a comma-delimited list. The purity level describes to what extent the program or method is free of side effects. Side effects are listed in the following table with the purity levels they address.
| Purity Level | Description | Restriction |
| WNDS | Write No Database State | Executes no INSERT, UPDATE, or DELETE statements. |
| RNDS | Read No Database State | Executes no SELECT statements. |
| WNPS | Write No Package State | Does not modify any package variables. |
| RNPS | Read No Package State | Does not read any package variables. |
| TRUST (Oracle8i) | | Does not enforce the restrictions declared but allows the compiler to trust they are true. |
The purity level requirements for packaged functions are different depending on where in the SQL statement the stored functions are used:
· To be called from SQL, all stored functions must assert WNDS.
· All functions not used in a SELECT, VALUES, or SET clause must assert WNPS.
· To be executed remotely, the function must assert WNPS and RNPS.
· To be executed in parallel, the function must assert all four purity levels or, in Oracle8i, use PARALLEL_ENABLED in the declaration.
· These functions must not call any other program that does not also assert the minimum purity level.
· If a package has an initialization section, it too must assert purity in Oracle7.
· If a function is overloaded, each overloading must assert its own purity level, and the levels don't have to be the same. To do this, place the pragma immediately after each overloaded declaration.
Many of the built-in packages, including DBMS_OUTPUT, DBMS_PIPE, and DBMS_SQL, do not assert WNPS or RNPS, so their use in SQL stored functions is necessarily limited.
1.15.3.1 Column/function name precedence
If your function has the same name as a table column in your SELECT statement and the function has no parameter, then the column takes precedence over the function. To force the RDBMS to resolve the name to your function, prepend the schema name to it:
CREATE TABLE emp(new_sal NUMBER ...);CREATE FUNCTION new_sal RETURN NUMBER IS ...; SELECT new_sal FROM emp; -- Resolves to column.SELECT scott.new_sal FROM emp;-- Resolves to function.- What are the minimum arguments required in Decode?
Three.
- A column has a constraint of value not exceeding 100. If try to insert value greater than 100 when will the constraint fire at insert statement or at commit.
By default the constraints are checked immediately after the insert (before commit) but you can make it to check after all the inserts and at the time of commit.
- A column varchar having combination of number like 123ABC, 234XYZ. Want to have the max number.
Use of substring and to_number
- Explain Materialized view in detail
A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.
A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.
In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.
In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.
Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.
SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/48 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db; Materialized view created.Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:
SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.Rowid Materialized Views
The following statement creates the rowid-materialized view on table emp located on a remote database:
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * FROM emp@remote_db; Materialized view log created.Subquery Materialized Views
The following statement creates a subquery-materialized view based on the emp and dept tables located on the remote database:
SQL> CREATE MATERIALIZED VIEW mv_empdeptAS SELECT * FROM emp@remote_db eWHERE EXISTS (SELECT * FROM dept@remote_db d WHERE e.dept_no = d.dept_no)REFRESH CLAUSE
[refresh [fast|complete|force] [on demand | commit] [start with date] [next date] [with {primary key|rowid}]]The refresh option specifies:
- The refresh method used by Oracle to refresh data in materialized view
- Whether the view is primary key based or row-id based
- The time and interval at which the view is to be refreshed
Refresh Method - FAST Clause
The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created.Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
Refresh Method - COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.
Refresh Method - FORCE Clause
When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
PRIMARY KEY and ROWID Clause
WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.
Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
Rowid materialized views should have a single master table and cannot contain any of the following:
- Distinct or aggregate functions
- GROUP BY Subqueries , Joins & Set operations
Timing the refresh
The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 2 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db; Materialized view created.In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.
Summary
Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes. Materialized Views can be refresh on commit or on request on base table using package for that M. View. (DBMS_MVIEW)
32. How does one escape special characters when building SQL queries?
The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:
a) SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';Use two quotes for every one displayed. Example:
b) SELECT 'Franks''s Oracle site' FROM DUAL; c) SELECT 'A ''quoted'' word.' FROM DUAL; d) SELECT 'A ''''double quoted'''' word.' FROM DUAL; 33. How does one get the time difference between two date columns?
Look at this example query:
select floor(((date1-date2)*24*60*60)/3600) || ' HOURS ' || floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600 - (floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60))) || ' SECS ' time_difference from ...If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):
select to_char(to_date('00:00:00','HH24:MI:SS') + (date1 - date2), 'HH24:MI:SS') time_difference from ...Note that this query only uses the time portion of the date and ignores the date itself. It will thus never return a value bigger than 23:59:59.
34. How does one add a day/hour/minute/second to a date value?
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual; SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400 -------------------- -------------------- -------------------- -------------------- 03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13 The following format is frequently used with Oracle Replication:
select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual; NOW NOW_PLUS_30_SECS -------------------- -------------------- 03-JUL-2002 16:47:23 03-JUL-2002 16:47:5335. Can one retrieve only the Nth row from a table?
Rupak Mohan provided this solution to select the Nth row from a table:
SELECT * FROM t1 a WHERE n = (SELECT COUNT(rowid) FROM t1 b WHERE a.rowid >= b.rowid);Shaik Khaleel provided this solution:
SELECT * FROM ( SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 ) WHERE RN = 100;Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.
Ravi Pachalla provided these solutions:
SELECT f1 FROM t1 WHERE rowid = ( SELECT rowid FROM t1 WHERE rownum <= 10 MINUS SELECT rowid FROM t1 WHERE rownum < 10); SELECT rownum,empno FROM scott.emp a GROUP BY rownum,empno HAVING rownum = 4; Alternatively...
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN (SELECT rowid FROM emp WHERE rownum < 10);Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
36. How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
SELECT * FROM emp WHERE (ROWID,0) IN ( SELECT ROWID, MOD(ROWNUM,4) FROM emp); Method 2: Use dynamic views (available from Oracle7.2):
SELECT * FROM ( SELECT rownum rn, empno, ename FROM emp ) temp WHERE MOD(temp.ROWNUM,4) = 0; Method 3: Using GROUP BY and HAVING - provided by Ravi Pachalla
SELECT rownum, f1 FROM t1 GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-nPlease note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.
37. How does one select the TOP N rows from a table?
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC) WHERE ROWNUM < 10;Use this workaround with prior releases:
SELECT * FROM my_table a WHERE 10 >= ( SELECT COUNT(DISTINCT maxcol) FROM my_table b WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC;38. How does one code a tree-structured query?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
select LEVEL, EMPNO, ENAME, MGR from EMP connect by prior EMPNO = MGR start with MGR is NULL;One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
select lpad(' ', LEVEL * 2) || ENAME ........One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
39. How does one code a matrix report in SQL?
Look at this example query with sample output:
SELECT * FROM (SELECT job, sum(decode(deptno,10,sal)) DEPT10, sum(decode(deptno,20,sal)) DEPT20, sum(decode(deptno,30,sal)) DEPT30, sum(decode(deptno,40,sal)) DEPT40 FROM scott.emp GROUP BY job) ORDER BY 1; JOB DEPT10 DEPT20 DEPT30 DEPT40 --------- ---------- ---------- ---------- ---------- ANALYST 6000 CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 560040. How does one implement IF-THEN-ELSE in a select statement?
The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or columns based on the values of other columns in the select statement.
Some examples:
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees; select a, b, decode( abs(a-b), a-b, 'a > b', 0, 'a = b', 'a < b') from tableX; select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 'B is greater than A')... select decode( GREATEST(A,B), A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'), 'A NOT GREATER THAN B')...Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.
From Oracle 8i one can also use CASE statements in SQL. Look at this example:
SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END FROM emp;41. How can one dump/ examine the exact content of a database column?
SELECT DUMP(col1) FROM tab1 WHERE cond1 = val1; DUMP(COL1) ------------------------------------- Typ=96 Len=4: 65,66,67,32For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
42. Workaround for snapshots on tables with LONG columns
You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg:
COPY TO SCOTT/TIGER@REMOTE - CREATE IMAGE_TABLE USING - SELECT IMAGE_NO, IMAGE - FROM IMAGES;Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.
43. Describe Create Table syntax
CREATE TABLE command
PURPOSE:
To create a table, the basic structure to hold user data, specifying this information:
* column definitions
* integrity constraints
* the table's tablespace
* storage characteristics
* an optional cluster
* data from an arbitrary query
SYNTAX:
CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ...
| table_constraint}
[, { column datatype [DEFAULT expr] [column_constraint] ...
| table_constraint} ]...)
[ [PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[ RECOVERABLE | UNRECOVERABLE ]
[ PARALLEL ( [ DEGREE { integer | DEFAULT } ]
[ INSTANCES { integer | DEFAULT } ]
)
| NOPARALLEL ]
[ CACHE | NOCACHE ]
| [CLUSTER cluster (column [, column]...)] ]
[ ENABLE enable_clause
| DISABLE disable_clause ] ...
[AS subquery]
where:
schema
is the schema to contain the table. If you omit schema, Oracle
creates the table in your own schema.
table
is the name of the table to be created.
column
specifies the name of a column of the table. The number of columns
in a table can range from 1 to 254.
datatype
is the datatype of a column.
DEFAULT
specifies a value to be assigned to the column if a subsequent
INSERT statement omits a value for the column. The datatype of the
expression must match the datatype of the column. A DEFAULT
expression cannot contain references to other columns, the
pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants
that are not fully specified.
column_constraint
defines an integrity constraint as part of the column definition.
table_constraint defines an integrity constraint as part of the table definition.
PCTFREE
specifies the percentage of space in each of the table's data blocks
reserved for future updates to the table's rows. The value of
PCTFREE must be a positive integer from 1 to 99. A value of 0
allows the entire block to be filled by inserts of new rows. The
default value is 10. This value reserves 10% of each block for
updates to existing rows and allows inserts of new rows to fill a
maximum of 90% of each block.
PCTFREE has the same function in the commands that create and alter
clusters, indexes, snapshots, and snapshot logs. The combination of
PCTFREE and PCTUSED determines whether inserted rows will go into
existing data blocks or into new blocks.
PCTUSED
specifies the minimum percentage of used space that Oracle maintains
for each data block of the table. A block becomes a candidate for
row insertion when its used space falls below PCTUSED. PCTUSED is
specified as a positive integer from 1 to 99 and defaults to 40.
PCTUSED has the same function in the commands that create and alter
clusters, snapshots, and snapshot logs.
The sum of PCTFREE and PCTUSED must be less than 100. You can use
PCTFREE and PCTUSED together use space within a table more
efficiently.
INITRANS
specifies the initial number of transaction entries allocated within
each data block allocated to the table. This value can range from 1
to 255 and defaults to 1. In general, you should not change the
INITRANS value from its default.
Each transaction that updates a block requires a transaction entry
in the block. The size of a transaction entry depends on your
operating system.
This parameter ensures that a minimum number of concurrent
transactions can update the block and helps avoid the overhead of
dynamically allocating a transaction entry.
The INITRANS parameter serves the same purpose in clusters, indexes,
snapshots, and snapshot logs as in tables. The minimum and default
INITRANS value for a cluster or index is 2, rather than 1.
MAXTRANS
specifies the maximum number of concurrent transactions that can
update a data block allocated to the table. This limit does not
apply to queries. This value can range from 1 to 255 and the
default is a function of the data block size. You should not change
the MAXTRANS value from its default.
If the number concurrent transactions updating a block exceeds the
INITRANS value, Oracle dynamically allocates transaction entries in
the block until either the MAXTRANS value is exceeded or the block
has no more free space.
The MAXTRANS parameter serves the same purpose in clusters,
snapshots, and snapshot logs as in tables.
TABLESPACE
specifies the tablespace in which Oracle creates the table. If you
omit this option, then Oracle creates the table in the default
tablespace of the owner of the schema containing the table.
STORAGE
specifies the storage characteristics for the table. This clause
has performance ramifications for large tables. Storage should be
allocated to minimize dynamic allocation of additional space.
RECOVERABLE
specifies that the index will be re-created as part of a database
recovery. This is the default in ARCHIVELOG mode. You cannot specify
RECOVERABLE when using NOARCHIVELOG mode.
UNRECOVERABLE
specifies that the index will not be re-created during recovery. For
more information on recoverable and unrecoverable objects, see
Oracle7 Server Administrator's Guide.
PARALLEL
DEGREE specifies the number of query server processes that can scan
the table in parallel. Either specify a positive integer or DEFAULT
which signifies to use the initialization parameter
INSTANCES specifies the minimum number of instances that need to be
of a Parallel Server. A positive integer specifies the number of
caches.
NOPARALLEL
specifies that queries on this table are not performed in parallel
by default. A hint in the query still causes the query to be
performed in parallel.
CACHE
specifies that blocks of this table are placed on the most recently
is performed.
This option is useful for small lookup tables.
NOCACHE
specifies that blocks of the table in the buffer cache follow the
standard LRU algorithm when a full table scan is performed.
CLUSTER
specifies that the table is to be part of the cluster. The columns
listed in this clause are the table columns that correspond to the
cluster's columns. Generally, the cluster columns of a table are
the column or columns that comprise its primary key or a portion of
its primary key.
Specify one column from the table for each column in the cluster
key. The columns are matched by position, not by name. Since a
clustered table uses the cluster's space allocation, do not use the
PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE
option, or the STORAGE clause in conjunction with the CLUSTER
option.
ENABLE
enables an integrity constraint.
DISABLE
disables an integrity constraint.
Constraints specified in the ENABLE and DISABLE clauses of a CREATE
TABLE statement must be defined in the statement. You can also
enable and disable constraints with the ENABLE and DISABLE keywords
of the CONSTRAINT clause. If you define a constraint but do not
explicitly enable or disable it, Oracle enables it by default.
You cannot use the ENABLE and DISABLE clauses in a CREATE TABLE
statement to enable and disable triggers.
AS subquery
inserts the rows returned by the subquery into the table upon its
creation.
If you include this clause, the column definitions can only specify
column names, default values, and integrity constraints, not
datatypes. Oracle derives column datatypes and lengths from the
subquery. Oracle also automatically defines NOT NULL constraints on
columns in the new table if they existed on the corresponding
columns of the selected table and the subquery does not modify the
column value with a SQL function or operator. A CREATE TABLE
statement cannot contain both the AS clause and a referential
integrity constraint definition.
The number of columns must equal the number of expressions in the
subquery. If all expressions in the subquery are columns, you can
omit the columns from the table definition entirely. In this case,
the names of the columns of table are the same as the columns in the
subquery.
PREREQUISITES:
To create a table in your own schema, you must have CREATE TABLE
system privilege. To create a table in another user's schema, you
must have CREATE ANY TABLE system privilege. Also, the owner of the
schema to contain the table must have either space quota on the
tablespace to contain the table or UNLIMITED TABLESPACE system
privilege.
SEE:
ALTER TABLE, CONSTRAINT, CREATE CLUSTER, CREATE INDEX, CREATE
TABLESPACE, DISABLE, DROP TABLE, ENABLE, STORAGE
CREATE TABLESPACE command
PURPOSE:
To create a tablespace. A tablespace is an allocation of space in
the database that can contain objects.
SYNTAX:
CREATE TABLESPACE tablespace DATAFILE filespec [ AUTOEXTEND { OFF | ON [NEXT integer [K|M]] [MAXSIZE [UNLIMITED | integer [K|M]] } ] [, filespec ...] [DEFAULT STORAGE storage_clause] [ONLINE | OFFLINE] where: tablespace is the name of the tablespace to be created. DATAFILE specifies the data file or files to comprise the tablespace. AUTOEXTEND enables or disables the autoextending of the size of the datafile in the tablespace. OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND commands. ON enable autoextend. NEXT the size in bytes of the next increment of disk space to be automatically allocated to the datafile when more extents are required. You can also use K or M to specify this size in in kilobytes or megabytes. The default is one data block. MAXSIZE maximum disk space allowed for automatic extension of the datafile. UNLIMITED set no limit on allocating disk space to the datafile. DEFAULT STORAGE specifies the default storage parameters for all objects created in the tablespace. ONLINE makes the tablespace available immediately after creation to users who have been granted access to the tablespace. OFFLINE makes the tablespace unavailable after immediately after creation. If you omit both the ONLINE and OFFLINE options, Oracle creates the tablespace online by default. The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline. PREREQUISITES: You must have CREATE TABLESPACE system privilege. Also, the SYSTEM tablespace must contain at least two rollback segments including the SYSTEM rollback segment. 44. Explain Snapshots CREATE SNAPSHOT command PURPOSE: To create a snapshot. A snapshot is a table that contains the results of a query of one or more tables or views, often located on a remote database. SYNTAX: CREATE SNAPSHOT [schema.]snapshot [ [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace] [STORAGE storage_clause] [ USING INDEX [ PCTFREE integer | TABLESPACE tablespace | INITTRANS integer | MAXTRANS integer | STORAGE storage_clause ] ... | [CLUSTER cluster (column [, column]...)] ] [ REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXT date]] AS subquery where: schema is the schema to contain the snapshot. If you omit schema, Oracle creates the snapshot in your schema.snapshot is the name of the snapshot to be created. Oracle chooses names for the table, views, and index used to maintain the snapshot by prefixing the snapshot name. To limit these names to 30 bytes and allow them to contain the entire snapshot name, Oracle Corporation recommends that you limit your snapshot names to 23 bytes. PCTFREEPCTUSEDINITRANSMAXTRANS establishes values for these parameters for the internal table Oracle uses to maintain the snapshot's data. TABLESPACE specifies the tablespace in which the snapshot is to be created. If you omit this option, Oracle creates the snapshot in the default tablespace of the owner of the snapshot's schema. STORAGE establishes storage characteristics for the table Oracle uses to maintain the snapshot's data. USING INDEX specifies the storage characteristics for the index on a simple snapshot. If the USING INDEX clause not specified, the index is create with the same tablespace and storage parameters as the snapshot. CLUSTER creates the snapshot as part of the specified cluster. Since a clustered snapshot uses the cluster's space allocation, do not use the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE option, or the STORAGE clause in conjunction with the CLUSTER option. REFRESH specifies how and when Oracle automatically refreshes the snapshot: FAST specifies a fast refresh, or a refresh using only the updated data stored in the snapshot log associated with the master table. COMPLETE specifies a complete refresh, or a refresh that re- executes the snapshot's query. FORCE specifies a fast refresh if one is possible or complete refresh if a fast refresh is not possible. Oracle decides whether a fast refresh is possible at refresh time. If you omit the FAST, COMPLETE, and FORCE options, Oracle uses FORCE by default. START WITH specifies a date expression for the first automatic refresh time. NEXT specifies a date expression for calculating the interval between automatic refreshes. Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle determines the first automatic refresh time by evaluating the NEXT expression when you create the snapshot. If you specify a START WITH value but omit the NEXT value, Oracle refreshes the snapshot only once. If you omit both the START WITH and NEXT values or if you omit the REFRESH clause entirely, Oracle does not automatically refresh the snapshot. AS subquery specifies the snapshot query. When you create the snapshot, Oracle executes this query and places the results in the snapshot. The select list can contain up to 253 expressions. A snapshot query is subject to the same restrictions as a view query. PREREQUISITES: To create a snapshot in your own schema, you must have CREATE SNAPSHOT system privilege. To create a snapshot in another user's schema, you must have CREATE ANY SNAPSHOT system privilege. Before a snapshot can be created, the user SYS must run the SQL script DBMSSNAP.SQL on both the database to contain the snapshot and the database(s) containing the tables and views of the snapshot's query. This script creates the package SNAPSHOT which contains both public and private stored procedures used for refreshing the snapshot and purging the snapshot log. The exact name and location of this script may vary depending on your operating system. When you create a snapshot, Oracle creates a table, two views, and an index in the schema of the snapshot. Oracle uses these objects to maintain the snapshot's data. You must have the privileges necessary to create these objects. For information on these privileges, see the CREATE TABLE, CREATE VIEW, and CREATE INDEX commands. The owner of the schema containing the snapshot must have either space quota on the tablespace to contain the snapshot or UNLIMITED TABLESPACE system privilege. Also, both you (the creator) and the owner must also have the privileges necessary to issue the snapshot's query. To create a snapshot, you must be using Oracle with the procedural option. To create a snapshot on a remote table or view, you must also be using the distributed option. SEE: ALTER SNAPSHOT, CREATE SNAPSHOT LOG, DROP SNAPSHOT CREATE SNAPSHOT LOG command PURPOSE: To create a snapshot log. A snapshot log is a table associated with the master table of a snapshot. Oracle stores changes to the master table's data in the snapshot log and then uses the snapshot log to refresh the master table's snapshots. SYNTAX:CREATE SNAPSHOT LOG ON [schema.]table [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace] [STORAGE storage_clause] where: schema is the schema containing the snapshot log's master table. If you omit schema, Oracle assumes the master table is contained in your own schema. Oracle creates the snapshot log in the schema of its master table. You cannot create a snapshot log for a table in the schema of the user SYS. table is the name of the master table for which the snapshot log is to be created. You cannot create a snapshot log for a view. Oracle chooses names for the table and trigger used to maintain the snapshot log by prefixing the master table name. To limit these names to 30 bytes and allow them to contain the entire master table name, Oracle Corporation recommends that you limit master table names to 24 bytes. PCTFREEPCTUSEDINITRANSMAXTRANS establishes values for these parameters for the snapshot log. TABLESPACE specifies the tablespace in which the snapshot log is to be created. If you omit this option, Oracle creates the snapshot log in the default tablespace the owner of the snapshot log's schema. STORAGE establishes storage characteristics for the snapshot log. PREREQUISITES: You must have the privileges necessary to create a table in the schema of the master table. For information on these privileges, see the CREATE TABLE command. Before a snapshot log can be created, the user SYS must run the SQL script DBMSSNAP.SQL on the database containing the master table. This script creates the package SNAPSHOT which contains both public and private stored procedures used for refreshing the snapshot and urging the snapshot log. The exact name and location of this script may vary depending on your operating system. You must also have the privileges to create a trigger on the master table. For information on these privileges, see the CREATE TRIGGER command. If you are using Trusted Oracle in DBMS MAC mode, your DBMS label must dominate the label of the tablespace in which the snapshot log is to be stored. 45. What are the Different types Parameters you can pass in Procedure and Function? CREATE PROCEDURE commandPURPOSE:
To create a stand-alone stored procedure. A procedure is a group
of PL/SQL statements that you can call by name.
SYNTAX:
CREATE [OR REPLACE] PROCEDURE [schema.]procedure
[ (argument [IN | OUT | IN OUT] datatype
[, argument [IN | OUT | IN OUT] datatype] ...)]
{IS | AS} pl/sql_subprogram_body
where:
OR REPLACE
recreates the procedure if it already exists. You can use this
option to change the definition of an existing procedure without
dropping, recreating, and regranting object privileges previously
granted on it. If you redefine a procedure, Oracle recompiles it.
For information on recompiling procedures, see the ALTER PROCEDURE
command.
Users who had previously been granted privileges on a redefined
procedure can still access the procedure without being regranted the
privileges.
CREATE FUNCTION command
PURPOSE:
To create a stand-alone stored function. A stored function is a set
of PL/SQL statements you can call by name. Stored functions are
very similar to procedures, except that a function returns a value
to the environment in which it is called.
SYNTAX:
CREATE [OR REPLACE] FUNCTION [schema.]function
[ (argument [IN] datatype
[, argument [IN] datatype] ...)]
RETURN datatype
{IS | AS} pl/sql_subprogram_body
where:
OR REPLACE
recreates the function if it already exists. You can use this
option to change the definition of an existing function without
dropping, recreating, and regranting object privileges previously
granted on the function. If you redefine a function, Oracle
recompiles it. For information on recompiling functions, see the
ALTER FUNCTION command.
Users who had previously been granted privileges on a redefined
function can still access the function without being regranted the
privileges.
schema
is the schema to contain the function. If you omit schema, Oracle
creates the function in your current schema.
function
is the name of the function to be created.
argument
is the name of an argument to the function. If the function does
not accept arguments, you can omit the parentheses following the
function name.
IN
specifies that you must supply a value for the argument when calling
the function. This is always true for function arguments, so this
keyword is entirely optional.
A procedure, rather than a stored function, can accept arguments for
which the procedure passes a value back to the calling environment
after execution.
datatype
is the datatype of an argument. An argument can have any datatype
supported by PL/SQL.
The datatype cannot specify a length, precision, or scale. Oracle
derives the length, precision, or scale of an argument from the
environment from which the function is called.
RETURN datatype
specifies the datatype of the function's return value. Because
every function must return a value, this clause is required. The
return value can have any datatype supported by PL/SQL.
The datatype cannot specify a length, precision, or scale. Oracle
derives the length, precision, or scale of the return value from the
environment from which the function is called.
pl/sql_subprogram_body
is the definition of the function. Function definitions are written
in PL/SQL.
To embed a CREATE FUNCTION statement inside an Oracle Precompiler
program, you must terminate the statement with the keyword END-EXEC
followed by the embedded SQL statement terminator for the specific
language.
PREREQUISITES:
Before a stored function can be created, the user SYS must run the
SQL script DBMSSTDX.SQL. The exact name and location of this script
may vary depending on your operating system.
To create a function in your own schema, you must have CREATE
PROCEDURE system privilege. To create a function in another user's
schema, you must have CREATE ANY PROCEDURE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, you can create a
function in another user's schema if your DBMS label dominates the
creation label of the other user.
46. Explain Packages
CREATE PACKAGE commandPURPOSE:
To create the specification for a stored package. A package is an
encapsulated collection of related procedures, functions, and other
program objects stored together in the database. The specification
declares these objects.
SYNTAX:
CREATE [OR REPLACE] PACKAGE [schema.]package
{IS | AS} pl/sql_package_spec
where:
OR REPLACE
recreates the package specification if it already exists. You can
use this option to change the specification of an existing package
without dropping, recreating, and regranting object privileges
previously granted on the package. If you change a package
specification, Oracle recompiles it. For information on recompiling
package specifications, see the ALTER PROCEDURE command.
Users who had previously been granted privileges on a redefined
package can still access the package without being regranted the
privileges.
Schema
is the schema to contain the package. If you omit schema, Oracle
creates the package in your own schema.
package
is the name of the package to be created.
pl/sql_package_spec
is the package specification. The package specification can declare
program objects. Package specifications are written in PL/SQL.
To embed a CREATE PACKAGE statement inside an Oracle Precompiler
program, you must terminate the statement with the keyword END-EXEC
followed by the embedded SQL statement terminator for the specific
language.
PREREQUISITES:
Before a package can be created, the user SYS must run the SQL
script DBMSSTDX.SQL. The exact name and location of this script may
vary depending on your operating system.
To create a package in your own schema, you must have CREATE
PROCEDURE system privilege. To create a package in another user's
schema, you must have CREATE ANY PROCEDURE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, you can only
create a package in another user's schema if your DBMS label
dominates the creation label of the other user.
To create a package, you must be using Oracle with the procedural
option.
SEE:
ALTER PACKAGE, CREATE FUNCTION, CREATE PACKAGE BODY, CREATE
PROCEDURE, DROP PACKAGE
CREATE PACKAGE BODY command
PURPOSE:
To create the body of a stored package. A package is an
encapsulated collection of related procedures, stored functions, and
other program objects stored together in the database. The body
defines these objects.
SYNTAX:
CREATE [OR REPLACE] PACKAGE BODY [schema.]package
{IS | AS} pl/sql_package_body
where:
OR REPLACE
recreates the package body if it already exists. You can use this
option to change the body of an existing package without dropping,
recreating, and regranting object privileges previously granted on
it. If you change a package body, Oracle recompiles it. For
information on recompiling package bodies, see the ALTER PACKAGE
BODY command.
Users who had previously been granted privileges on a redefined
package can still access the package without being regranted the
privileges.
schema
is the schema to contain the package. If you omit schema, Oracle
creates the package in your current schema.
package
is the name of the package to be created.
pl/sql_package_body
is the package body. The package body can declare and define
program objects. Package bodies are written in PL/SQL.
To embed a CREATE PACKAGE BODY statement inside an Oracle
Precompiler program, you must terminate the statement with the
keyword END-EXEC followed by the embedded SQL statement terminator
for the specific language.
PREREQUISITES:
Before a package can be created, the user SYS must run the SQL
script DBMSSTDX.SQL. The exact name and location of this script may
vary depending on your operating system.
To create a package in your own schema, you must have CREATE
PROCEDURE system privilege. To create a package in another user's
schema, you must have CREATE ANY PROCEDURE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, you can only
create a package in another user's schema if your DBMS label
dominates the creation label of the other user.
To create a package, you must be using Oracle with the procedural
option.
Summary:
There are two types of functions/Procedure/Variable in packages
a) Public Function:- Functions define at package specification is called as Public Function.
b) Private Function:- Functions define at package Body is called as Private Function.
Function / Procedure Overloading:
A Function Name can be use within a same package with different signature.
47. Oracle Database Objects.
Table, Index, Views, Sequences, Stored Procedure, Functions, Packages, Snapshot, Synonyms, Types, Clusters, Role, Database Links, Jobs, Triggers, Partition.
48. Explain Indexes.
Types Of Indexes.
a) B-Tree Index: - Its Oracle default Index type.
A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.
b) Bitmap Index: -
Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform. Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).
c) Reverse Key: -
d) Function based Index: -
One of the largest problems with indexes is that the indexes are often suppressed by developers. Developers using the UPPER function can suppress an index on a column for a given query. In Oracle8i, there is now a way to combat this problem. Function-based indexes allow you to create an index based on a function or expression. The value of the function or expression is specified by the person creating the index and is stored in the index. Function-based indexes can involve multiple columns, arithmetic expressions or may be a PL/SQL function or C callout. The following example shows an example of a function based index.
e) #key Index: -
f) Cluster Index: -
49. Is there a limit to the number of indexes that we can have on a table?
If most of the columns are selected often from a table is it advisable to define indexes on all the columns Besides what other performance issues can be created by indexes other than the overhead in insert operations.
If most of the columns are selected often from a table is it advisable to define indexes on all the columns Besides what other performance issues can be created by indexes other than the overhead in insert operations.
No limit.
The number of columns is not so important. Analyze the query and the where clauses there to decide how menu indexes you need.
You must be careful when writing queries or you may unintentional suppress (turn off) an index that you may have intended on using. Any function that modifies the column name in a WHERE clause will suppress the corresponding index. In Oracle8I, there are function-based indexes that allow indexes to be built on functions like UPPER, SUBSTR and DECODE Many common functions that are used to suppress a standard index are listed below
· NOT / IS NULL / != or <>
· Comparing a number field to a character field
· Any modification to the Indexed Column Name
· (TO_CHAR, TO_DATE, +0, || '', SUBSTR, DECODE...)
Suppression Example; despite the intended hint to use the index, the SUBSTR function will suppress the index on the CUST_NO column below:
select /*+ index(customer custidx) */ CUST_NO, ZIP_CODE
from CUSTOMER
where SUBSTR(CUST_NO,1,4) = '2502';
Execution Time - 280 seconds
The SUBSTR function was re-written with a LIKE instead and part of the index is used and the performance is substantially increased:
select CUST_NO, ZIP_CODE
from CUSTOMER
where CUST_NO LIKE '2502%';
Execution Time - 3 seconds
Tip: Prior to Oracle 8.1, if a column is modified in anyway in the WHERE clause, the index on the column will not be used (it will be internally suppressed).
Comparing wrong datatypes
If you compare the wrong datatypes, your index may be suppressed internally. This is because Oracle will re-write the query so that the comparison is correct. This problem is at times difficult to track down.
Comparing Characters to Numbers:
where char_data = 123
could be rewritten to:
where To_Number(char_data) = 123
Comparing Numbers to Characters:
where num_data = ‘123’
could be rewritten lik:e
where To_Char(num_data) = ‘123’
Tip: Comparing mismatched datatypes could cause an internal index suppression that is difficult to track down. Oracle will often place a function on the column that fixes the mismatch, but suppresses the index.
You must be careful when writing queries or you may unintentional suppress (turn off) an index that you may have intended on using. Any function that modifies the column name in a WHERE clause will suppress the corresponding index. In Oracle8I, there are function-based indexes that allow indexes to be built on functions like UPPER, SUBSTR and DECODE Many common functions that are used to suppress a standard index are listed below
· NOT / IS NULL / != or <>
· Comparing a number field to a character field
· Any modification to the Indexed Column Name
· (TO_CHAR, TO_DATE, +0, || '', SUBSTR, DECODE...)
Suppression Example; despite the intended hint to use the index, the SUBSTR function will suppress the index on the CUST_NO column below:
select /*+ index(customer custidx) */ CUST_NO, ZIP_CODE
from CUSTOMER
where SUBSTR(CUST_NO,1,4) = '2502';
Execution Time - 280 seconds
The SUBSTR function was re-written with a LIKE instead and part of the index is used and the performance is substantially increased:
select CUST_NO, ZIP_CODE
from CUSTOMER
where CUST_NO LIKE '2502%';
Execution Time - 3 seconds
Tip: Prior to Oracle 8.1, if a column is modified in anyway in the WHERE clause, the index on the column will not be used (it will be internally suppressed).
Comparing wrong datatypes
If you compare the wrong datatypes, your index may be suppressed internally. This is because Oracle will re-write the query so that the comparison is correct. This problem is at times difficult to track down.
Comparing Characters to Numbers:
where char_data = 123
could be rewritten to:
where To_Number(char_data) = 123
Comparing Numbers to Characters:
where num_data = ‘123’
could be rewritten lik:e
where To_Char(num_data) = ‘123’
Tip: Comparing mismatched datatypes could cause an internal index suppression that is difficult to track down. Oracle will often place a function on the column that fixes the mismatch, but suppresses the index.
Here are the limits on indexes:
# of indexes per table - unlimited
# of columns per index - 32 (30 if bitmapped index)
Total size of all indexed columns cannot be more than 40% of database block size. So, the limit could be less than 32 columns if you have large columns you are indexing.
# of indexes per table - unlimited
# of columns per index - 32 (30 if bitmapped index)
Total size of all indexed columns cannot be more than 40% of database block size. So, the limit could be less than 32 columns if you have large columns you are indexing.
If you're going to index all the columns, you may want to consider an INDEX ORGANIZED TABLE - this is a normal table except that the table itself does not physically exist - index(es) are created to hold all the data for the table. This is done with the ORGANIZATION INDEX clause on the CREATE TABLE statement. It requires a Primary Key.
50. What is TNS Stands for?
Transparent Network Substrate
51. Explain Constraints.
CONSTRAINT clause
PURPOSE:
To define an integrity constraint. An integrity constraint is a
rule that restricts the values for one or more columns in a table.
SYNTAX:
Column constraint:
[CONSTRAINT constraint]
{ [NOT] NULL
| {UNIQUE | PRIMARY KEY}
| REFERENCES [schema.]table [(column)]
[ON DELETE CASCADE]
| CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PARALLEL [integer] | NOPARALLEL] ]
[ EXCEPTIONS INTO [schema.]table
| DISABLE }
Table constraint:
[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
| FOREIGN KEY (column [,column] ...)
REFERENCES [schema.]table [(column [,column] ...)]
[ON DELETE CASCADE]
| CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PARALLEL [integer] | NOPARALLEL] ]
[ EXCEPTIONS INTO [schema.]table[@dblink]
| DISABLE }
where:
CONSTRAINT
identifies the integrity constraint by the name constraint. Oracle
stores this name in the data dictionary along with the definition of
the integrity constraint. If you omit this identifier, Oracle
generates a name with this form:
SYS_Cn
where
n
is an integer that makes the name unique
within the database.
For the names and definitions of integrity constraints, query the
data dictionary.
NULL
specifies that a column can contain null values.
NOT NULL
specifies that a column cannot contain null values.
If you do not specify NULL or NOT NULL in a column definition, NULL
is the default.
UNIQUE
designates a column or combination of columns as a unique key.
PRIMARY KEY
designates a column or combination of columns as the table's primary
key.
FOREIGN KEY
designates a column or combination of columns as the foreign key in
a referential integrity constraint.
REFERENCES
identifies the primary or unique key that is referenced by a foreign
key in a referential integrity constraint.
ON DELETE CASCADE
specifies that Oracle maintains referential integrity by
automatically removing dependent foreign key values if you remove a
referenced primary or unique key value.
CHECK
specifies a condition that each row in the table must satisfy.
USING INDEX
specifies parameters for the index Oracle uses to enforce a UNIQUE or PRIMARY KEY constraint. The name of the index is the same as the
name of the constraint. You can choose the values of the INITRANS,
MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index.
For information on these parameters, see the CREATE TABLE command.
Only use this clause when enabling UNIQUE and PRIMARY KEY
constraints.
PARALLEL
specifies the number of processes that create the index in parallel.
You can only specify positive integer values greater than 1. If you
do not specify an integer, the degree of parallelism is based on
the parallelism specified in the table's definition.
NOPARALLEL
specifies that the index should not be created in parallel.
EXCEPTIONS INTO
identifies a table into which Oracle places information about rows
that violate an enabled integrity constraint. This table must exist
before you use this option. If you omit schema, Oracle assumes the
exception table is in your own schema. The exception table must be
on your local database.
DISABLE
disables the integrity constraint. If an integrity constraint is
disabled, Oracle does not enforce it.
If you do not specify this option, Oracle automatically enables the
integrity constraint.
You can also enable and disable integrity constraints with the
ENABLE and DISABLE clauses of the CREATE TABLE and ALTER TABLE
commands.
PREREQUISITES:
CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE
commands. To define an integrity constraint, you must have the
privileges necessary to issue one of these commands. See the CREATE
TABLE and ALTER TABLE commands.
Defining a constraint may also require additional privileges or
preconditions that depend on the type of constraint.
52. PL/SQL Tables
PL/SQL Tables (PL/SQL)
=============
Objects of type TABLE are called "PL/SQL tables," which are modelled
on (but not the same as) database tables. PL/SQL tables use a primary
key to give you array-like access to rows. The size of a PL/SQL table
is unconstrained. That is, the number of rows in a PL/SQL table can
increase dynamically.
PL/SQL tables can have one column and a primary key, neither of which
can be named. The column can have any scalar datatype, but the primary
key must have datatype BINARY_INTEGER.
Declaring PL/SQL Tables
-----------------------
PL/SQL tables must be declared in two steps. First, you define a TABLE
type, then declare PL/SQL tables of that type. You can declare TABLE
types in the declarative part of any block, subprogram, or package
using the syntax
TYPE type_name IS TABLE OF
{ column_type | variable%TYPE | table.column%TYPE } [NOT NULL]
INDEX BY BINARY_INTEGER;
where "type_name" is a type specifier used in subsequent declarations
of PL/SQL tables and "column_type" is any scalar datatype such as CHAR,
DATE, or NUMBER. You can use the %TYPE attribute to specify a column
datatype.
In this example, you declare a TABLE type called "EnameTabTyp":
DECLARE
TYPE EnameTabTyp IS TABLE OF CHAR(10)
INDEX BY BINARY_INTEGER;
You could have used %TYPE to provide the column datatype, as follows:
DECLARE
TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
Once you define type "EnameTabTyp," you can declare PL/SQL tables of
that type, as follows:
ename_tab EnameTabTyp;
The identifier "ename_tab" represents an entire PL/SQL table.
Like scalar variables, PL/SQL tables can be declared as the formal
parameters of procedures and functions. Some packaged examples follow:
PACKAGE emp_actions IS
TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
ename_tab EnameTabTyp;
sal_tab SalTabTyp;
...
PROCEDURE hire_batch
(ename_tab EnameTabTyp,
sal_tab SalTabTyp,
...);
PROCEDURE log_names
(ename_tab EnameTabTyp,
num BINARY_INTEGER);
...
END emp_actions;
Referencing PL/SQL Tables
-------------------------
To reference rows in a PL/SQL table, you specify a primary key value
using the array-like syntax
plsql_table_name(primary_key_value)
where "primary_key_value" has datatype BINARY_INTEGER. For example,
you reference the third row in PL/SQL table "ename_tab" as follows:
ename_tab(3) ...
You can assign the value of a PL/SQL expression to a specific row using
the following syntax:
plsql_table_name(primary_key_value) := plsql_expression;
In the next example, you assign the sum of variables "salary" and
"increase" to the fifth row in PL/SQL table "sal_tab":
sal_tab(5) := salary + increase;
Until a row is assigned a value, it does not exist. If you try to
reference an uninitialized row, PL/SQL raises the predefined exception
NO_DATA_FOUND. Consider the following example:
DECLARE
TYPE JobTabTyp IS TABLE OF CHAR(14)
INDEX BY BINARY_INTEGER;
job_tab JobTabTyp;
BEGIN
job_tab(1) := 'CLERK';
IF job_tab(2) = 'CLERK' THEN -- raises NO_DATA_FOUND
...
END IF;
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- here because job_tab(2) does not exist
...
END;
Inserting/Fetching Rows
-----------------------
You must use a loop to INSERT values from a PL/SQL table into a database
column. Likewise, you must use a loop to FETCH values from a database
column into a PL/SQL table. For example, given the declarations
DECLARE
TYPE EmpnoTabTyp IS TABLE OF NUMBER(4)
INDEX BY BINARY_INTEGER;
TYPE EnameTabTyp IS TABLE OF CHAR(10)
INDEX BY BINARY_INTEGER;
...
empno_tab EmpnoTabTyp;
ename_tab EnameTabTyp;
you might use the following procedure to INSERT values from the PL/SQL
tables into the "emp" database table:
PROCEDURE insert_emp_data
(rows BINARY_INTEGER,
empno_tab EmpnoTabTyp,
ename_tab EnameTabTyp,
...) IS
BEGIN
FOR i IN 1..rows LOOP
INSERT INTO emp (empno, ename, ...)
VALUES (empno_tab(i), ename_tab(i), ...);
END LOOP;
END;
Conversely, you might use the next procedure to FETCH all rows from the
database table into PL/SQL tables "empno_tab" and "ename_tab":
PROCEDURE fetch_emp_data
(rows OUT BINARY_INTEGER,
empno_tab OUT EmpnoTabTyp,
ename_tab OUT EnameTabTyp,
...) IS
BEGIN
rows := 0;
FOR emprec IN (SELECT * FROM emp) LOOP
rows := rows + 1;
empno_tab(rows) := emprec.empno;
ename_tab(rows) := emprec.ename;
...
END LOOP;
END;
However, you cannot reference PL/SQL tables in the INTO clause. For
example, the following SELECT statement is illegal:
PROCEDURE fetch_emp_data
(rows OUT BINARY_INTEGER,
empno_tab OUT EmpnoTabTyp,
ename_tab OUT EnameTabTyp,
...) IS
BEGIN
SELECT empno, ename
INTO empno_tab, ename_tab -- illegal
FROM emp;
...
END;
Deleting Rows
-------------
The DELETE statement cannot specify PL/SQL tables, so you cannot
delete individual rows from a PL/SQL table. Assigning NULL to a row
as follows does not work because the row remains:
sal_tab(3) := NULL;
However, you can use a simple workaround to delete an entire PL/SQL
table. Simply assign NULL to it, as shown in the following example:
DECLARE
TYPE NumTabTyp IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
sal_tab NumTabTyp;
BEGIN
/* Load salary table. */
FOR i IN 1..50 LOOP
sal_tab(i) := i;
END LOOP;
...
sal_tab := NULL; -- deletes sal_tab table
IF sal_tab(3) ... -- raises NO_DATA_FOUND
53. Explain Cursors in PL/SQL
1.9 Cursors in PL/SQL
Every SQL statement executed by the RDBMS has a private SQL area that contains information about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement. There can be either static cursors, whose SQL statement is determined at compile time, or dynamic cursors, whose SQL statement is determined at runtime. Static cursors are covered in greater detail in this section. Dynamic cursors in PL/SQL are implemented via the built-in package DBMS_SQL. See the book Oracle Built-in Packages and the corresponding Oracle PL/SQL Built-ins Pocket Reference, both from O'Reilly & Associates, for full coverage on DBMS_SQL and the other built-in packages.
1.9.1 Explicit Cursors
Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.
1.9.1.1 Declaring explicit cursors
To use an explicit cursor, you must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:
· A cursor without parameters, such as:
CURSOR company_cur IS SELECT company_id FROM company;· A cursor that accepts arguments through a parameter list:
CURSOR company_cur (id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = id_in;· A cursor header that contains a RETURN clause in place of the SELECT statement:
CURSOR company_cur (id_in IN NUMBER) RETURN company%ROWTYPE IS SELECT * FROM company;This technique can be used in packages to hide the implementation of the cursor in the package body. See the Section 1.14" section for more information.
1.9.1.2 Opening explicit cursors
To open a cursor, use the following syntax:
OPEN cursor_name [(argument [,argument ...])];where cursor_name is the name of the cursor as declared in the declaration section. The arguments are required if the definition of the cursor contains a parameter list.
You must open an explicit cursor before you can fetch rows from that cursor. When the cursor is opened, the processing includes the PARSE, BIND, OPEN, and EXECUTE statements. This OPEN processing includes: determining an execution plan, associating host variables and cursor parameters with the placeholders in the SQL statement, determining the result set, and, finally, setting the current row pointer to the first row in the result set.
When using a cursor FOR loop, the OPEN is implicit in the FOR statement. If you try to open a cursor that is already open, PL/SQL will raise an "ORA-06511: PL/SQL: cursor already open" exception.
1.9.1.3 Fetching from explicit cursors
The FETCH statement places the contents of the current row into local variables. To retrieve all rows in a result set, each row needs to be fetched. The syntax for a FETCH statement is:
FETCH cursor_name INTO record_or_variable_list;where cursor_name is the name of the cursor as declared and opened.
1.9.1.4 Closing explicit cursors
The syntax of the CLOSE statement is:
CLOSE cursor_name;where cursor_name is the name of the cursor declared and opened.
After all rows have been fetched, a cursor needs to be closed. Closing a cursor releases the private SQL area used by the cursor, freeing the memory used by that cursor.
If you declare a cursor in a local anonymous, procedure, or function block, that cursor will automatically close when the block terminates. Package-based cursors must be closed explicitly, or they stay open for the duration of your session. Closing a cursor that is not open raises an INVALID CURSOR exception.
1.9.1.5 Explicit cursor attributes
There are four attributes associated with cursors: ISOPEN, FOUND, NOTFOUND, and ROWCOUNT. These attributes can be accessed with the % delimiter to obtain information about the state of the cursor. The syntax for a cursor attribute is:
cursor_name%attributewhere cursor_name is the name of the explicit cursor.
The behaviors of the explicit cursor attributes are described in the following table.
| Attribute | Description |
| %ISOPEN | TRUE if cursor is open. FALSE if cursor is not open. |
| %FOUND | INVALID_CURSOR is raised if cursor has not been OPENed. NULL before the first fetch. TRUE if record was fetched successfully. FALSE if no row was returned. INVALID_CURSOR if cursor has been CLOSEd. |
| %NOTFOUND | INVALID_CURSOR is raised if cursor has not been OPENed. NULL before the first fetch. FALSE if record was fetched successfully. TRUE if no row was returned. INVALID_CURSOR if cursor has been CLOSEd. |
| %ROWCOUNT | INVALID_CURSOR is raised if cursor has not been OPENed. The number of rows fetched from the cursor. INVALID_CURSOR if cursor has been CLOSEd. |
Frequently a cursor attribute is checked as part of a WHILE loop that fetches rows from a cursor:
DECLARE caller_rec caller_pkg.caller_cur%ROWTYPE;BEGIN OPEN caller_pkg.caller_cur; LOOP FETCH caller_pkg.caller_cur into caller_rec; EXIT WHEN caller_pkg.caller_cur%NOTFOUND OR caller_pkg.caller_cur%ROWCOUNT > 10; UPDATE call SET caller_id = caller_rec.caller_id WHERE call_timestamp < SYSDATE; END LOOP; CLOSE caller_pkg.caller_cur;END;1.9.2 Implicit Cursors
Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block, you are working with implicit cursors. These statements include INSERT, UPDATE, DELETE, and SELECT INTO statements. Unlike explicit cursors, implicit cursors do not need to be declared, OPENed, FETCHed, or CLOSEd.
SELECT statements handle the %FOUND and %NOTFOUND attributes differently from explicit cursors. When an implicit SELECT statement does not return any rows, PL/SQL immediately raises the NO_DATA_FOUND exception and control passes to the exception section. When an implicit SELECT returns more than one row, PL/SQL immediately raises the TOO_MANY_ROWS exception and control passes to the exception section.
Implicit cursor attributes are referenced via the SQL cursor. For example:
BEGIN UPDATE activity SET last_accessed := SYSDATE WHERE UID = user_id; IF SQL%NOTFOUND THEN INSERT INTO activity_log (uid,last_accessed) VALUES (user_id,SYSDATE); END IFEND;| SQL Attributes | Description |
| %ISOPEN | Always FALSE since the cursor is opened implicitly and closed immediately after the statement is executed. |
| %FOUND | NULL before the statement. TRUE if one or more rows were inserted, updated, or deleted or if only one row was selected. FALSE if no row was selected, updated, inserted, or deleted. |
| %NOTFOUND | NULL before the statement. TRUE if no row was selected, updated, inserted, or deleted. FALSE if one or more rows were inserted, updated, or deleted. |
| %ROWCOUNT | The number of rows affected by the cursor. |
| %BULK_ROWCOUNT (Oracle8i) | A pseudo index-by table containing the numbers of rows affected by the statements executed in bulk bind operations. See the "Bulk Binds (Oracle8i)" section for more information on %BULK_ROWCOUNT. |
Use the RETURNING clause in INSERT, UPDATE, and DELETE statements to obtain data modified by the associated DML statement. This clause allows you to avoid an additional SELECT statement to query the results of the DML statement. For example:
BEGIN UPDATE activity SET last_accessed := SYSDATE WHERE UID = user_id RETURNING last_accessed, cost_center INTO timestamp, chargeback_acct;1.9.2.1 The SELECT FOR UPDATE clause
By default, the Oracle RDBMS locks rows as they are changed. To lock all rows in a result set, use the FOR UPDATE clause in your SELECT statement when you OPEN the cursor, instead of when you change the data. Using the FOR UPDATE clause does not require you to actually make changes to the data; it only locks the rows when opening the cursor. These locks are released on the next COMMIT or ROLLBACK. As always, these row locks do not affect other SELECT statements unless they, too, are FOR UPDATE. The FOR UPDATE clause is appended to the end of the SELECT statement and has the following syntax:
SELECT ... FROM ... FOR UPDATE [OF column_reference] [NOWAIT];where column_reference is a comma-delimited list of columns that appear in the SELECT clause. The NOWAIT keyword tells the RDBMS to not wait for other blocking locks to be released. The default is to wait forever.
In the following example, only columns from the inventory (pet) table are referenced FOR UPDATE, so no rows in the dog_breeds (dog) table are locked when hounds_in_stock_cur is opened:
DECLARE CURSOR hounds_in_stock_cur IS SELECT pet.stock_no, pet.breeder, dog.size FROM dog_breeds dog ,inventory pet WHERE dog.breed = pet.breed AND dog.class = 'HOUND' FOR UPDATE OF pet.stock_no, pet.breeder;BEGIN1.9.2.2 The WHERE CURRENT OF clause
UPDATE and DELETE statements can use a WHERE CURRENT OF clause if they reference a cursor declared FOR UPDATE. This syntax indicates that the UPDATE or DELETE should modify the current row identified by the FOR UPDATE cursor. The syntax is:
[UPDATE | DELETE ] ... WHERE CURRENT OF cursor_name;By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement. For example:
DECLARE CURSOR wip_cur IS SELECT acct_no, enter_date FROM wip WHERE enter_date < SYSDATE -7 FOR UPDATE;BEGIN FOR wip_rec IN wip_cur LOOP INSERT INTO acct_log (acct_no, order_date) VALUES (wip_rec.acct_no, wip_rec.enter_ date); DELETE FROM wip WHERE CURRENT OF wip_cur; END LOOP;END; 1.9.3 Cursor Variables
A cursor variable is a data structure that points to a cursor object, which in turn points to the cursor's result set. You can use cursor variables to more easily retrieve rows in a result set from client and server programs. You can also use cursor variables to hide minor variations in queries.
The syntax for a REF_CURSOR type is:
TYPE ref_cursor_name IS REF CURSOR [RETURN record_type];If you do not include a RETURN clause, then you are declaring a weak REF CURSOR. Cursor variables declared from weak REF CURSORs can be associated with any query at runtime. A REF CURSOR declaration with a RETURN clause defines a "strong" REF CURSOR. A cursor variable based on a strong REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN at runtime.
To use cursor variables, you must first create a REF_CURSOR type, then declare a cursor variable based on that type.
The following example shows the use of both weak and strong REF CURSORs:
DECLARE -- Create a cursor type based on the companies table. TYPE company_curtype IS REF CURSOR RETURN companies%ROWTYPE; -- Create the variable based on the REF CURSOR. company_cur company_curtype; -- And now the weak, general approach. TYPE any_curtype IS REF CURSOR; generic_curvar any_curtype;The syntax to OPEN a cursor variable is:
OPEN cursor_name FOR select_statement;FETCH and CLOSE a cursor variable using the same syntax as for explicit cursors. There are a number of restrictions on cursor variables:
· Cursor variables cannot be declared in a package since they do not have a persistent state.
· You cannot use the FOR UPDATE clause with cursor variables.
· You cannot assign NULLs to a cursor variable nor use comparison operators to test for equality, inequality, or nullity.
· Neither database columns nor collections can store cursor variables.
· You cannot use RPCs to pass cursor variables from one server to another.
· Cursor variables cannot be used with the dynamic SQL built-in package DBMS_SQL.
54. What is a cluster table?
A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database.
Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values. Before clustering, consider its
Cluster Keys
The columns defined by the CREATE CLUSTER command make up the cluster key. These cluster columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name.
Types of Clusters
A cluster can be either an indexed cluster or a hash cluster.
Indexed Clusters
In an indexed cluster, Oracle stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block, regardless of the number of tables and rows in which it occurs. This saves disk space and improves performance for many operations.
You may want to use indexed clusters in the following cases:
· Your queries retrieve rows over a range of cluster key values.
· Your clustered tables may grow unpredictably.
After you create an indexed cluster, you must create an index on the cluster key before you can issue any data manipulation language (DML) statements against a table in the cluster. This index is called the cluster index.
A cluster index provides quick access to rows within a cluster based on the cluster key. If you issue a SQL statement that searches for a row in the cluster based on its cluster key value, Oracle searches the cluster index for the cluster key value and then locates the row in the cluster based on its ROWID.
Hash Clusters
In a hash cluster, Oracle stores together rows that have the same hash key value. The hash value for a row is the value returned by the cluster's hash function. When you create a hash cluster, you can either specify a hash function or use the Oracle internal hash function. Hash values are not actually stored in the cluster, although cluster key values are stored for every row in the cluster.
You may want to use hash clusters in the following cases:
· Your queries retrieve rows based on equality conditions involving all cluster key columns.
· Your clustered tables are static or you can determine the maximum number of rows and the maximum amount of space required by the cluster when you create the cluster.
The hash function provides access to rows in the table based on the cluster key value. If you issue a SQL statement that locates a row in the cluster based on its cluster key value, Oracle applies the hash function to the given cluster key value and uses the resulting hash value to locate the matching rows. Because multiple cluster key values can map to the same hash value, Oracle must also check the row's cluster key value. This process often results in less I/O than the process for the indexed cluster, because the index search is not required.
Oracle's internal hash function returns values ranging from 0 to the value of HASHKEYS - 1. If you specify a column with the HASH IS clause, the column values need not fall into this range. Oracle divides the column value by the HASHKEYS value and uses the remainder as the hash value. The hash value for null is HASHKEYS - 1. Oracle also rounds the HASHKEYS value up to the nearest prime number to obtain the actual number of hash values. This rounding reduces the likelihood of hash collisions, or multiple cluster key values having the same hash value.
You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key.
If you cannot fit all rows for one hash value into a data block, do not use hash clusters. Performance is very poor in this circumstance because an insert or update of a row in a hash cluster with a size exceeding the data block size fills the block and performs row chaining to contain the rest of the row.
Cluster Size
Oracle uses the value of the SIZE parameter to determine the space reserved for rows corresponding to one cluster key value or one hash value. This space then determines the maximum number of cluster or hash values stored in a data block. If the SIZE value is not a divisor of the data block size, Oracle uses the next largest divisor. If the SIZE value is larger than the data block size, Oracle uses the operating system block size, reserving at least one data block per cluster or hash value.
Oracle also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. This does not apply to hash clusters because hash values are not actually stored in the cluster.
Although the maximum number of cluster and hash key values per data block is fixed on a per `-cluster basis, Oracle does not reserve an equal amount of space for each cluster or hash key value. Varying this space stores data more efficiently, because the data stored per cluster or hash key value is rarely fixed.
A SIZE value smaller than the space needed by the average cluster or hash key value may require the data for one cluster key or hash key value to occupy multiple data blocks. A SIZE value much larger results in wasted space.
When you create a hash cluster, Oracle immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters.
Adding Tables to a Cluster
You can add tables to an existing cluster by issuing a CREATE TABLE statement with the CLUSTER clause. A cluster can contain as many as 32 tables, although the performance gains of clustering are often lost in clusters of more than four or five tables.
All tables in the cluster have the cluster's storage characteristics as specified by the PCTUSED, PCTFREE, INITRANS, MAXTRANS, TABLESPACE, and STORAGE parameters.
Example I
The following statement creates an indexed cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a cluster size of 512 bytes, and storage parameter values:
CREATE CLUSTER personnel
( department_number NUMBER(2) )
SIZE 512
STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);
The following statements add the EMP and DEPT tables to the cluster:
CREATE TABLE emp
(empno NUMBER PRIMARY KEY,
ename VARCHAR2(10) NOT NULL
CHECK (ename = UPPER(ename)),
job VARCHAR2(9),
mgr NUMBER REFERENCES scott.emp(empno),
hiredate DATE CHECK (hiredate >= SYSDATE),
sal NUMBER(10,2) CHECK (sal > 500),
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) NOT NULL )
CLUSTER personnel (deptno);
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(9))
CLUSTER personnel (deptno);
The following statement creates the cluster index on the cluster key of PERSONNEL:
CREATE INDEX idx_personnel ON CLUSTER personnel;
After creating the cluster index, you can insert rows into either the EMP or DEPT tables.
Example II
The following statement creates a hash cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a maximum of 503 hash key values, each of size 512 bytes, and storage parameter values:
CREATE CLUSTER personnel
( department_number NUMBER )
SIZE 512 HASHKEYS 500
STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);
Because the above statement omits the HASH IS clause, Oracle uses the internal hash function for the cluster.
Example III
The following statement creates a hash cluster named PERSONNEL with the cluster key made up of the columns HOME_AREA_CODE and HOME_PREFIX, and uses a SQL expression containing these columns for the hash function:
CREATE CLUSTER personnel
( home_area_code NUMBER,
home_prefix NUMBER )
HASHKEYS 20
HASH IS MOD(home_area_code + home_prefix, 101);
No comments:
Post a Comment