All DBA concepts and useful scripts are in the below link
13 June 2013
25 May 2013
Oracle interview questions(SQL)
Contents
- Overview
- How can I transform a subquery involving the IN clause to a Join?
- How can I transform a statement involving an OR condition to a UNION ALL?
- How do I eliminate duplicate values in a table?
- How can I get a count of the different data values in a column?
- How can I get count/sum RANGES of data values in a column?
- How can I dynamically generate a list of comma-separated items?
- How can I get the time difference between two date columns?
- Can I retrieve only the Nth row from a table?
- Can I retrieve only rows X to Y from a table?
- Can I retrieve every Nth row from a table?
- Retrieve the TOP N Rows from a table?
- Is it possible to dump/examine the exact content of a database column?
- Can I code a MATRIX Report using SQL?
- Tree-Structured (Hierarchical) Queries
- Insert Large Number of Sequence Values using UNION
Structured Query Language (SQL) is an implementation of a "data sublanguage" used in almost all relational database systems. SQL was developed by IBM in the early 1970s for use in System R, and is "de facto" standard, as well as an ISO and ANSI standard.
In common usage, SQL supports four types of SQL statements: - Queries:
This type of SQL statement implements the standard relational operations such as SELECTION, PROJECTION and JOIN. The SELECT statement forms the basis for many other SQL operations. You can use SELECT statements to query tables, create views, in subqueries, or when creating a table defined as the result set of query.
- Data Manipulation Language (DML):
DML statements are used to allow data in the database to be added, amended or deleted. DML functionality is provided by the use of INSERT, UPDATE and DELETE statements.
- Data Definition Language (DDL):
This is the language that allows for objects to be created or altered. Provided below are a few example Oracle DDL statements:
- CREATE TABLE
- DROP TABLE
- ALTER TABLE
- CREATE INDEX
- DROP INDEX
- ALTER INDEX
- CREATE VIEW
- CREATE TYPE
- Data Control Language (DCL):
This is the language that allows for transaction control. Provided below are a few example Oracle DCL statements:
- COMMIT
- SAVEPOINT
- ROLLBACK
- SET TRANSACTION
Original Statement:
SELECT e.empno, e.ename
FROM emp e
WHERE e.deptno IN (
SELECT d.deptno
FROM dept d
WHERE d.loc = 'CHICAGO
);
New Statement: SELECT e.empno, e.ename
FROM emp e, dept d
WHERE d.loc = 'CHICAGO
AND d.deptno = e.deptno;
Original Statement:
SELECT dname, loc
FROM dept
WHERE loc = 'CHICAGO
OR loc = 'NEW YORK
New Statement: SELECT dname, loc
FROM dept
WHERE loc = 'CHICAGO
UNION ALL
SELECT dname, loc
FROM dept
WHERE loc = 'NEW YORK
Provided below are four methods for identifying or removing duplicate rows from a table:
Method 1: DELETE FROM emp a
WHERE rowid > (
SELECT min(rowid)
FROM emp b
WHERE a.emp_id = b.emp_id
);
Method 2: CREATE TABLE emp2 AS
SELECT distinct *
FROM emp;
DROP TABLE emp;
RENAME emp2 TO emp;
Method 3: DELETE FROM emp
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM emp
GROUP BY emp_id
);
Method 4: DELETE FROM emp a
WHERE EXISTS (
SELECT 'true'
FROM emp b
WHERE b.emp_id = a.emp_id
AND b.rowid < a.rowid
);
NOTE: If you where to create an index on the joined fields in the inner loop, it may be possible to eliminate N^2 operations as there would be no need to loop through the entire table on each pass be a record. SELECT dname
, sum(decode(job, 'CLERK', 1, 0)) Clerk
, sum(decode(job, 'SALESMAN', 1, 0)) Salesman
, sum(decode(job, 'MANAGER', 1, 0)) Manager
, sum(decode(job, 'ANALYST', 1, 0)) Analyst
, sum(decode(job, 'PRESIDENT', 1, 0)) President
FROM emp e, dept d
WHERE e.deptno (+) = d.deptno
GROUP BY dname;
DNAME CLERK SALESMAN MANAGER ANALYST PRESIDENT
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING 1 0 1 0 1
OPERATIONS 0 0 0 0 0
RESEARCH 2 0 1 2 0
SALES 1 4 1 0 0
A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).
SELECT
job
, sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000"
, sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000"
, sum(decode(greatest(sal,0), least(sal,999), 1, 0)) "Range 0-1000"
FROM emp
GROUP BY job;
JOB Range 3000-6000 Range 1000-3000 Range 0-1000
--------- --------------- --------------- ------------
ANALYST 2 0 0
CLERK 0 2 2
MANAGER 0 3 0
PRESIDENT 1 0 0
SALESMAN 0 4 0
I often find myself needing to dynamically generate a list of comma-separated items (i.e. a list of database files separated by commas). The output format, for example, would be:
... Datafile1, Datafile2, Datafile3 ...
Instead of thinking of the list as a linear set of items, re-write the set of items each on a separate line as follows:
...
Datafile1
, Datafile2
, Datafile3
...
Notice that a comma is used on each line except for the first item. We can now take advantage of a popular Analytic Function (RANK) to distinguish the first line.
Consider the following CREATE CONTROLFILE example: SET HEADING OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 9000
SET LINESIZE 135
SET TRIMSPOOL ON
SET TRIMOUT ON
SET ECHO OFF
SET SQLBLANKLINES ON
SPOOL cr_control_file_example.sql
prompt CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
prompt MAXLOGFILES 16
prompt MAXLOGMEMBERS 3
prompt MAXDATAFILES 100
prompt MAXINSTANCES 8
prompt MAXLOGHISTORY 3182
prompt LOGFILE
prompt GROUP 1 (
prompt '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log',
prompt '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
prompt ) SIZE 50M,
prompt GROUP 2 (
prompt '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log',
prompt '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
prompt ) SIZE 50M,
prompt GROUP 3 (
prompt '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log',
prompt '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
prompt ) SIZE 50M
prompt DATAFILE
SELECT
CASE WHEN ROW_NUMBER() OVER(ORDER BY name) = 1
THEN
' ''' || name || ''''
ELSE
' , ''' || name || ''''
END AS
FROM v$datafile;
prompt CHARACTER SET WE8ISO8859P1
SPOOL OFF
------------------------------------------------------------
CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 3182
LOGFILE
GROUP 1 (
'/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
) SIZE 50M,
GROUP 2 (
'/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
) SIZE 50M,
GROUP 3 (
'/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
) SIZE 50M
DATAFILE
'+ORCL_DATA1/orcl/datafile/example.263.623550121'
, '+ORCL_DATA1/orcl/datafile/soe.269.623559923'
, '+ORCL_DATA1/orcl/datafile/soeindex.270.623560217'
, '+ORCL_DATA1/orcl/datafile/sysaux.261.623550085'
, '+ORCL_DATA1/orcl/datafile/system.259.623549989'
, '+ORCL_DATA1/orcl/datafile/undotbs1.260.623550053'
, '+ORCL_DATA1/orcl/datafile/undotbs2.264.623550147'
, '+ORCL_DATA1/orcl/datafile/users.265.623550181'
CHARACTER SET WE8ISO8859P1
Method 1:
SELECT ename, job, hiredate
FROM emp
WHERE
rowid = (
SELECT rowid FROM emp
WHERE rownum <= 3
MINUS
SELECT rowid FROM emp
WHERE rownum < 3
);
Method 2: SELECT ename, job, hiredate
FROM emp
WHERE
rownum = 1
AND rowid NOT IN (
SELECT rowid FROM emp
WHERE rownum < 3
);
NOTE: Always remember that there is no explicit order in a relational database. SELECT ename, job, hiredate
FROM emp
WHERE
rowid in (
SELECT rowid FROM emp
WHERE rownum <= 7
MINUS
SELECT rowid FROM emp
WHERE rownum < 3
);
Method 1:
SELECT ename, job, hiredate
FROM emp
WHERE
(rowid,0) in (
SELECT rowid, mod(rownum,4)
FROM emp
);
Method 2: Using Dynamic Views: (available in Oracle7.2 and higher)
SELECT ename, job, hiredate
FROM ( SELECT rownum rn, empno, ename, job, hiredate
FROM emp
) d_table
WHERE mod(d_table.rn,4) = 0;
Method 1: Starting with Oracle8i, you can have an inner-query with an ORDER BY clause
SELECT ename, job, hiredate, sal
FROM ( SELECT empno, ename, job, hiredate, sal
FROM emp
ORDER BY sal DESC
)
WHERE rownum < 6;
Method 2: The following workaround will work with prior releases SELECT ename, job, hiredate, sal
FROM emp a
WHERE 5 >= ( SELECT count(distinct b.sal)
FROM emp b
WHERE b.sal >= a.sal
)
ORDER BY a.sal DESC
SELECT ename, job, dump(job)
FROM emp
WHERE ename = 'SMITH';
ENAME JOB DUMP(JOB)
---------- --------- ---------------------------
SMITH CLERK Typ=1 Len=5: 67,76,69,82,75
Type Code
|
Oracle Datatype
|
1
|
VARCHAR2
|
2
|
NUMBER
|
12
|
DATE
|
96
|
CHAR
|
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 emp e
GROUP BY job
)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
SELECT lpad(' ', level*4) || ename
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr IS NULL;
Employee Name
------------------------
KING
JONES
JHUNTER
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
NOTE: Using the "EMP" table from the SCOTT/TIGER schema makes a great test table since it has a "self-referencing" relation. (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 you are. Oracle can handle queries with a depth up to 255 levels.
The "START WITH" clause is used 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.
Here is an example SQL script that will insert 1,000,000 sequence values into a table using a series of UNION 's and a cartesian product.
DROP TABLE sequence_values
/
CREATE TABLE sequence_values (id NUMBER)
/
INSERT INTO sequence_values
SELECT
millions.x +
hundred_thousands.x +
ten_thousands.x +
thousands.x +
hundreds.x +
tens.x +
ones.x
FROM
(SELECT 0 x FROM dual
UNION SELECT 1 FROM dual
UNION SELECT 2 FROM dual
UNION SELECT 3 FROM dual
UNION SELECT 4 FROM dual
UNION SELECT 5 FROM dual
UNION SELECT 6 FROM dual
UNION SELECT 7 FROM dual
UNION SELECT 8 FROM dual
UNION SELECT 9 FROM dual) ones,
(SELECT 0 x FROM dual
UNION SELECT 10 FROM dual
UNION SELECT 20 FROM dual
UNION SELECT 30 FROM dual
UNION SELECT 40 FROM dual
UNION SELECT 50 FROM dual
UNION SELECT 60 FROM dual
UNION SELECT 70 FROM dual
UNION SELECT 80 FROM dual
UNION SELECT 90 FROM dual) tens,
(SELECT 0 x FROM dual
UNION SELECT 100 FROM dual
UNION SELECT 200 FROM dual
UNION SELECT 300 FROM dual
UNION SELECT 400 FROM dual
UNION SELECT 500 FROM dual
UNION SELECT 600 FROM dual
UNION SELECT 700 FROM dual
UNION SELECT 800 FROM dual
UNION SELECT 900 FROM dual) hundreds,
(SELECT 0 x FROM dual
UNION SELECT 1000 FROM dual
UNION SELECT 2000 FROM dual
UNION SELECT 3000 FROM dual
UNION SELECT 4000 FROM dual
UNION SELECT 5000 FROM dual
UNION SELECT 6000 FROM dual
UNION SELECT 7000 FROM dual
UNION SELECT 8000 FROM dual
UNION SELECT 9000 FROM dual) thousands,
(SELECT 0 x FROM dual
UNION SELECT 10000 FROM dual
UNION SELECT 20000 FROM dual
UNION SELECT 30000 FROM dual
UNION SELECT 40000 FROM dual
UNION SELECT 50000 FROM dual
UNION SELECT 60000 FROM dual
UNION SELECT 70000 FROM dual
UNION SELECT 80000 FROM dual
UNION SELECT 90000 FROM dual) ten_thousands,
(SELECT 0 x FROM dual
UNION SELECT 100000 FROM dual
UNION SELECT 200000 FROM dual
UNION SELECT 300000 FROM dual
UNION SELECT 400000 FROM dual
UNION SELECT 500000 FROM dual
UNION SELECT 600000 FROM dual
UNION SELECT 700000 FROM dual
UNION SELECT 800000 FROM dual
UNION SELECT 900000 FROM dual) hundred_thousands,
(SELECT 0 x FROM dual
UNION SELECT 1000000 FROM dual
UNION SELECT 2000000 FROM dual
UNION SELECT 3000000 FROM dual
UNION SELECT 4000000 FROM dual
UNION SELECT 5000000 FROM dual
UNION SELECT 6000000 FROM dual
UNION SELECT 7000000 FROM dual
UNION SELECT 8000000 FROM dual
UNION SELECT 9000000 FROM dual) millions
/
Table dropped.
Table created.
10000000 rows created.
Difference between Oracle and Sybase :
Oracle 9,10g | Sybase ASE 12,15 |
|---|---|
| An Oracle instance consists of: A) Six processes: PMON Process Monitor, SMON System Monitor, DBWn Database Writer, LGWR Log Writer, ARCn Archiver, and CKPT Checkpoint. Additional processes on a 10g RAC system may include RECO Distributed Transaction Recovery, CJQn Job Coordinator Process, LCKn Shared Resource Locking, LMDn Global Lock Manager, LMON Lock Manager, LMS* Global Cache Service, MMAN Memory Manager, MMNL Metrics Monitor, session hitory, metrics computation, MMON Metrics Monitor, statistics, snapshots, and PSPn a PL-SQL plug in. B) Data files which contain the tables and other data objects, control files which contain configuration information, redo log files for transaction processing, and archive files for recovery purposes. C) Configuration files which contains the instance attributes, and external security information |
A Sybase server consists of: A) two processes, data server and backup server ; B) devices which house the databases; one database (master) contains system and configuration data ; C) a configuration file which contains the server attributes. |
| Memory Model The Oracle memory model consists of: A) the SGA, which contains executable program code; B) the shared pool, which is split between the library cache and data dictionary cache, optimized queries are stored here; C) the redo log buffer, which is where transactions are stored before they are written to the redo logs; D) the db buffer cache, which is where database operations are stored before they are written to the data files. The Oracle dataserver runs as a multiple processes within the operating system; the number of users connected to the database is reflected in the number of processes managed by the OS. Each Oracle user connection requires 1 meg of server memory. |
Memory Model The Sybase memory model consists of: A) the program area, which is where the dataserver executable is stored; B) the data cache, stores recently fetched pages from the database device C) the stored procedure cache, which contains optimized sql calls The Sybase dataserver runs as a single process within the operating system; when multiple users are connected to the database, only one process is managed by the OS. Each Sybase database connection requires 40-60k of memory. The "total memory" configuration parameter determines the amount of memory allocated to the server. This memory is taken immediately upon startup, and does not increase. |
| Transaction Processing Transactions are written to the redo log buffer, where they advance to the redo logs, data file buffer, and data files. When a rollback occurs, block images are discarded from the redo log buffer; as the previous block data is held in the rollback segment blocks. Committed transactions are promoted to the archive logs. Archive logs are used to restore the data in the event of a hardware failure. A checkpoint operation flushes all updated (committed) memory blocks from the log buffer and database buffer pool. Note that transaction logging is optional, on a table by table basis, and archive logging is also optional. During an update transaction, record locking provides prevents data block collisions from occurring. Access to the "before" image of the record(s) is made available during this time, which reduces contention. This is a patented record locking mechanism. |
Transaction Processing Transactions are written to the data cache, where they advance to the transaction log, and database device. When a rollback occurs, pages are discarded from the data cache. The transaction logs are used to restore data in event of a hardware failure. A checkpoint operation flushes all updated (committed) memory pages to their respective tables. Transaction logging is required for all databases; only image (blob) fields may be exempt. During an update transaction, the data page(s) containing the row(s) are locked. This will cause contention if the transaction is not efficiently written. Record locking can be turned on in certain cases, but this requires sizing the table structure with respect to the page size. The locking mechanism can be bypassed by setting the isolation level, to allow data to be read from uncommitted transactions. |
| Backup Procedures Previous to Oracle 8, the only way to back up the database was through a "cold" backup. This involved shutting down the Oracle instance, backing up the data files, and restarting upon completion. Oracle 8,9, and 10g has the Recovery Manager package which facilitates backing up data files on-line. | Backup Procedures A "dump database" operation can be performed when the database is on-line or offline. Subsequent "dump transaction" commands need to be issued during the day, to ensure acceptable recovery windows. A new ASE 12.5 feature allows database dump files to be "mined" for a single table. Thus an entire database loaded is not required when only a single table restore is needed. |
| Recovery Procedures Recovery is achieved by restoring the data files, and verifying the control files are synchronized properly. When RMAN is used (it is now the standard in most production databases) recovery is accomplished via an automated set of processes. | Recovery Procedures A "load database" command loads the designated database with the named dump file. Subsequent "load transaction" commands can then be issued to load multiple transaction dump files. |
| Security and Account Setup Oracle is shipped with several built-in accounts: system, internal, and sys. Operating system authentication is required in order for a login to be created with similar privileges. After a login is created, access is then granted to the tables within schemas as needed. |
Security and Account Setup The initial login shipped with Sybase is "sa" (system administrator). This login has the role "sa_role" which is the super-user, in Sybase terms. User logins are added at the server level, and then granted access to each database, as needed. Within each database, access to tables can be granted per application requirements. A user can also be aliased as "dbo", which automatically grants them all rights within a database. |
| Database Creation Databases are initialized with the "create database" command. In most (99.9%) cases the database name is the same as the instance name, and there is only one database per instance. Within an Oracle instance, schemas are created which contain the tables for an application. Tables are referenced byschema_name.tablename. Each user is assigned a default schema upon creation; this schema name is the same as the user name. In order for tables to be referenced without the schema name, they must be owned by the user, or in the "system" schema. "Synonyms" can be created to bypass the prefix requirement. Typically, an "internal" or "system" level login will be used to access the database. A typical Oracle instance will have 12 data files, 6 redo log members, 6 archive files, and 4 control files, all spread across various disk subsystems. | Database Creation Databases are initialized with the "create database" command. It is not unusual for a Sybase server to contain many different databases. Tables are created within each database; users refer to tables by usingownername.tablename A typical Sybase database will consist of six segments spread across various devices (non-SAN environment). |
| Data Types Supported data types include number, char, varchar2, date, long raw, clob, and blob. In Oracle 10, numeric data types were introduced. Blob and clob (Oracle 8+ only) datatypes are implemented via pointers within the physical record structure ; the field contents are stored in dedicated blocks. As a result, each blob or clob field requires at least 2K of storage (depending on the database block size). Long raw datatypes are stored in-line, and are discouraged. For string data, the varchar2 type can be used for lengths up to 2000; the clob type can be used for longer field data. ( ** blob = binary large object, clob = character large object) Date fields are represented as a number of days, along with a decimals fraction (accurate to the minute). Additional accuracy can be obtained by purchasing a separate module. Sequences provide a means of autoincrementing an ID column - selecting sequence_name. | Data Types Supported data types include integer, decimal, float, money, char, varchar, datetime, image, and text datatypes. Text and image datatypes are implemented via pointers within the physical record structure ; the field contents are stored in dedicated pages. As a result, each text or image field requires at least 2K of storage (on most platforms). For string data, the varchar type can be used for lengths up to 4000; the text type can be used for longer field data. Datetime fields are stored as a number which is accurate to 1/300 of a second. Within a "create table" statement, a column can be flagged as an "identity" column, which causes it to be incremented automatically when rows are inserted. |
| Storage Concepts Tables are stored in tablespaces; a tablespace is made up of one or more data files. Although it is possible to use raw devices within Oracle, it is not recommended. Control files, rollback segments, and redo logs are all stored in separate files within the operating system. Note that Oracle 10g RAC features a special OFS (Oracle File System), which allows the database to get better i/o performance over conventional file systems. |
Storage Concepts Tables are stored in segments; a segment is an area within a device, with a name and a size, that is allocated for a database. The transaction log is stored in its own segment, usually on a separate device. |
| Partitioning Oracle 9i,10g does support semantic partitioning, by list, range, hash, hash range, and composite. | Partitioning Semantic "smart" partitioning is new in ASE 15. Tables can be partitioned by range, value, or round-robin. By default, all tables in ASE 15 are partitioned, round-robin. |
| Failover and High Availability Oracle offers several types of high availability configurations: Transparent Application Failover (TAF), Oracle Streams (send transactions to remote server), Data Guard, and Standby Database. | Failover and High Availability The ASE High Availability option allows server failover to occur within specified metrics. ASE 15 supports for server clusters, shared disk storage between servers, and multiple client failover in a cluster setting will be available in late 2007 / early 2008. |
| PL-SQL PL-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Arrays are supported (using the table datatype), as well as structures (the record type). Since PL-SQL procedures cannot return a result set, returning rows to a calling application requires implementing arrays as "out" variables. One powerful feature of PL-SQL is the ability to create custom functions which can be used within SQL statements. Regular Expressions are supported, in addition to User Defined Operators. |
Transact-SQL Transact-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Temporary tables are supported, which allows customized, private work tables to be created for complex processes. Any number of result sets can be returned to calling applications via SELECT statements. |
| Triggers Oracle supports select, insert, update, and delete triggers. Triggers can be set to fire 'before' or 'after' a transaction. NEW and OLD aliases are used to refer to the before and after images of the data. Triggers by default fire for all rows in the transaction; they can be set to fire for each row also. Oracle also supports DDL Event Triggers. |
Triggers Sybase ASE supports insert, update, and delete triggers. Triggers are fired after the transaction starts, and have the capability to roll back if required. Images of the 'before' and 'after' records are visible within the trigger. Triggers by default fire for all rows in the transaction. |
| Analytic Functions, OLAP Oracle 10 does support analytic functions like median, n-tile, and lead/lag, over partitions. |
Analytic Functions, OLAP Sybase ASE does not support analytic functions (median, n-tile, lead/lag, over partitions) at the moment; Sybase IQ, the data warehouse product, does support most analytic functions. |
| Flat File Processing Oracle includes the SQLLDR utility (SQL Loader) which can import via flat files. "Direct" mode imports are achieved by simply removing the indexes (and triggers) from the target table, and specifying direct=yes at the command line. There is no Oracle utility for exporting data to a flat file; sqlplus must be used to spool the data to a file. |
Flat File Processing Sybase ASE includes the BCP utility (Bulk-Copy) which can import or export data via flat files. "Fast" mode imports are achieved by simply removing the indexes (and triggers) from the target table. |
| Performance and scalability Oracle has always been known for its speed and performance. Oracle 10 supports over 15,000 active user connections. The patented record locking scheme made it an attractive candidate for canned applications marketed by Peoplesoft and the like. The ability to turn transaction logging on and off at will allows Oracle squeak by competitors during benchmark analysis runs. |
Performance and scalability On a regular basis, Sybase Adaptive Server Enterprise on Sun captures the #1 TPC benchmark for SMP performance, check sybase.com for the latest results. Scalability: Sybase 15 scales from handheld devices to enterprise level servers. |
| Price and Support Price per seat is relatively high, compared to other vendors. Support is achieved by opening "TAR" cases with the support team. Response is usually within 48 hours. | Price Price per seat is average, compared to other vendors; Sybase ASE is the leader in lowest transaction cost, with the best performance, among the leading enterprise database vendors. Support is achieved by opening cases with the support team. Response is usually within 24 hours. |
| Management and Development Tools (for Windows) SQL-Plus is the interactive query tool used with Oracle; it is useful for entering queries and stored procedures. Complex reporting capabilities are available for those willing to learn SQL-Plus extensions. Oracle Enterprise ships with Enterprise Manager, a full featured front end to the intricacies of Oracle. The main areas of the system (security, storage, schemas) are managed by totally separate applications, which is cumbersome at times. The "best of breed" product in this category is DB-Artisan by Embarcadero Technologies. Many developers choose Toad, as it is cheaper and has a lot of nice features. | Management and Development Tools (for Windows) ISQL is the interactive query tool used with Sybase ; it is useful for entering queries and stored procedures. Sybase Central is shipped with Sybase ASE. It offers a good interface for performing basic database tasks. The "best of breed" product in this category is DB-Artisan by Embarcadero Technologies. Sybase recently released Workspace, an Eclipse based SQL development environment, which features a stored procedure debugger and graphical SQL creator. For development, Sybase Inc. offers Workspace, Powerbuilder, Powerdesigner, Power J and its "Studio" line products. Powerbuilder remains the most robust, straightforward, and practical choice for windows development, supporting many other RDBMs in addition to Sybase ASE. |
Additional Features of Interest, in Oracle 10gORA_ROWSCN System Column A timestamp-like data type on each record, used for update consistency. FORALL Option A method by which to perform updates via 'bulk' operations. Significant performance improvements can be obtained via this mechanism. PRAGMA SERIALLY_REUSABLE Option A method by which packages can be managed in memory more efficiently. Function Based Indexes This allows results to be pre-calced into an index, eliminating the need for calculation at runtime. DBMS_APPLICATION_INFO.SET_ A method by which to set status or completion rate of long running processes. Package Initialization Section Allows the setup routines for a package to occur just once per session. Sorted Hash Clusters Allows groups of records to be read from disk via a hash identifer. WITH Clause Queries Allows a query to be referenced as a virtual table multiple times within a SQL block. | Additional Notes, Regarding Sybase ASEReplication Server Sybase's enterprise data replication facility is the leader in the industry; more Oracle customers purchase the Sybase offering than do Oracle's own product. Encryption ASE 15's new patent-pending encryption technology allows seamless encryption of columns with little or no change to most applications. Computed Columns ASE 15 supports both materialized and virtual computed columns; this eliminates the need to perform CPU-intensive calculations at runtime. Functional indexes can be built on these columns. |
24 May 2013
DBA Home
Simple Administration Concepts :
- Introduction to Oracle Database
- Managing Tablespaces and Datafiles
- Managing REDO LOGFILES
- Managing Control Files
- Managing the UNDO TABLESPACE
- SQL Loader
- Export and Import
- DATA PUMP Utility
- Flash Back Query Features
- BACKUP AND RECOVERY
- Log Miner
- General Database Information
- Disk I/O, Events, Waits
- Table space Information
- Session Statistics
- Hit/Miss Ratios
- User Information
- Rollback Segments
- Full Table Scans
- Contention/Locking
- Redo Log Buffer
- Data Dictionary Info
- Miscellaneous Reports
- Shared Pool Information
- Cursor/SQL Processing
- Database Objects
- Tables/Indexes
Subscribe to:
Posts (Atom)