Oracle

Slide 2: 

we have taken help from the net to make this ppt Please verify the information contained in our Ppt.

Slide 3: 

Group MembersAnushree Priolker 824Deepika Raja 830Pradnya kossambe 811

Slide 4: 

Objectives of the Presentation Features Datatypes DDLs

Slide 5: 

We can’t possibly talk about everything on this list in detail!

Slide 6: 

So instead we are going to pick just a couple of them to look at in depth...

Slide 7: 

Oracle: #1 RDBMS Vendor Overall

Oracle’s Commitment to Windows : 

7 Oracle’s Commitment to Windows 2000 2003 2001 1993 Windows Server 2003 Windows2000 WindowsNT Windows XP 2004 2005 Windows x64 8i -> 9i - Real Application Clusters, PL/SQL enhancements, XMLType enhancements 9i -> 10g - ASM space management, tracing and diagnostics, DML error logging, async commit, more PL/SQL changes, table/column encryption, restore points 10g -> 11g - Probably best to point you to technet on that one.

ORACLE 8i New Features : 

ORACLE 8i New Features Content Management for the Internet Oracle8i interMedia, Spatial, Time Series, and Visual Image Retrieval Java Data Warehousing and Very Large Data Bases (VLDB)

Slide 10: 

Database Features Partitioning Enhancements System Management Oracle Parallel Server Distributed Systems Networking, Security, and Oracle Advanced Security Program Interfaces Skip Seen Slides

Oracle8i interMedia, Spatial, Time Series, and Visual Image Retrieval : 

Oracle8i interMedia, Spatial, Time Series, and Visual Image Retrieval Integrate all your data into its datastore. The Oracle8i extensibility framework and object relational technology have been exploited to extend of reliability, availability, and data management capabilities of the database server to multimedia data. A new multimedia option, Oracle8i interMedia, services text, document, image, audio, video, and locational data in a single integrated package. The Spatial, Time Series, and Visual Image Retrieval options, available separately, also have been designed to utilize the Oracle8i extensibility architecture.

Slide 12: 

Oracle8i interMedia Oracle8i Spatial BACK

Oracle8i interMedia : 

Oracle8i interMedia Allows multimedia data to be managed in an integral fashion with other enterprise data. Applications can access interMedia through both object and relational interfaces Extensible Index Extensible Query Optimizer Filters Hierarchical Query Feedback BACK

Extensible Index and Extensible Query Optimizer : 

Extensible Index and Extensible Query Optimizer Creating an index is now much simpler because it is created and maintained using standard SQL. This is made possible through the Oracle8i extensible indexing framework as described in "User-Defined Operators and Extensible Indexing". The optimizer can be used to select the best plan for executing a CONTAINS query. It does so by analyzing collected statistics of all the tables and indexes affected by a CONTAINS query. BACK

Filters and Hierarchical Query Feedback : 

Filters and Hierarchical Query Feedback By default during indexing, interMedia Text uses the Inso Corporation filtering technology, which can filter most document formats. This filtering technology automatically recognizes document formats. Thus, this filter can be used to index single or mixed column formats. A custom or other filter may also be specified to filter documents during indexing. Given a query expression, interMedia Text returns related query term information (broader term, narrower term, related term) that can help refine queries. BACK

Oracle8i Spatial : 

Oracle8i Spatial Spatial data is any data with a location component. Oracle8i Spatial is designed for two groups of users: It enables traditional database customers to add useful spatial queries to their applications. It supports geographic information system (GIS) vendors who must store, retrieve, and manage very large spatial databases containing hundreds of gigabytes of geodata.

Oracle8i Time Series : 

Oracle8i Time Series Oracle8i Time Series enables time-stamped data to be stored efficiently in an Oracle database. It supports a basic set of functions--calendar, time series, and time scaling--to retrieve and process data.   Oracle8i Time Series provides the following kinds of functions: Calendar functions provide a convenient mechanism for defining time-related operations and ensuring the validity of time-related data. For example, arbitrary calendars can be defined over a fixed interval, such as a calendar of business days in the week or a calendar of quarterly dividend payment dates.

Slide 18: 

Time series functions provide analysis of time series data and include support for complex aggregation (such as moving average), mathematical operations (such as cumulative sum and cell-by-cell arithmetic operations), and data verification. Time scaling functions allow a time series function to be transformed from one time scale to another, such as from aggregate daily data into quarterly summaries. Time scaling is flexible because the source and target scales are determined by calendars, which can be customized. BACK

Data Warehousing and Very Large Data Bases (VLDB) : 

Data Warehousing and Very Large Data Bases (VLDB) The performance of queries is dramatically increased by materialized views and summary management, which allow frequently requested summaries to be stored and maintained. Parallelism is improved with more operations offered in parallel. Moving data from one database to another is made simpler and faster with transportable tablespaces. A materialized view is a stored summary containing precomputed results. Transportable Tablespaces This feature allows a user to move a subset of an Oracle database into another Oracle database BACK

Database Features : 

Database Features With Oracle8i, the Oracle database server provides a new extensibility framework for developers to extend the database to meet their needs. Many object relational enhancements have been incorporated into this release, and this functionality has been extended to all Oracle8i product configurations. Online transaction processing (OLTP) systems are usually concerned with entering and retrieving mission-critical data from day-to-day operations.

Database Features : 

Database Features Availability, reliability, and performance are extremely important for these systems. Oracle8i delivers significant enhancements in these areas, along with improvements in scalability, serviceability, and security. Advance Queuing has been significantly enhanced, continuing to develop the publish/subscribe messaging paradigm. BACK

Oracle Database 10g : 

Oracle Database 10g “Sophisticated Simplicity” - Sean McCown, InfoWorld

.NET Data Access : 

.NET Data Access VB .NET C# ASP .NET Any other .NET Language Oracle Data Provider For .NET Oracle Database Extensions for .NET (VB .NET, C#, C++)

ORACLE 10g : 

ORACLE 10g The idea of Oracle 10g is that the database will be able to dynamically "requisition" resources from the grid to meet levels of peak demand. Other enhancements for Oracle 10g are aimed at reducing costs and improving the quality of service by making the database easier to deploy and manage including enhancements to backup and recovery and data warehousing .

Features of Oracle 10g : 

Features of Oracle 10g As with Oracle 9i, one of the major focuses of Oracle 10g is on reducing costs and increasing up-time by making the database easier to manage. Other major enhancements in this area include:- Enhancing Enterprise Manager Availability Data Warehousing Skip Seen Slides

Enhancing Enterprise Manager : 

Enhancing Enterprise Manager Enhancing Enterprise Manager to be able to:- Manage the whole grid The complete stack of resources Simplifying installation and Configuration by reducing the number of initialization parameters. BACK

Availability : 

Availability Oracle 10g Real Application Clusters have been enhanced to provide Automatic Service Provisioning. Servers are automatically allocated to work loads and clients are automatically assigned to the server with the least load. Also on failure of a server, the surviving servers are automatically reallocated to work loads.

Availability : 

Availability Other enhancements in this area include: Flashback available at the row, transaction, table or database level. Recovery area on disk that is maintained automatically by the database and contains only those blocks changed since the last backup - thereby enabling faster recovery from media failure. Data guard (standby database) has been enhanced to enable compression and encryption of log traffic from the master database to the standby system; Tables can now be redefined without invalidating stored procedures. Support for rolling upgrades of the hardware, operating system and the database to reduce planned down time. BACK

Data Warehousing : 

Data Warehousing Oracle 10g Data Warehousing enhancements include: An increase in the size limits of the database to support ultra-large databases of millions of terabytes in size and ultra-large files of terabytes in size. The 4GB restriction on LOBs has been raised to 128 terabytes. Improvements to Real Application Clusters (RAC) enable resources to be allocated automatically and means that operational data can be used immediately without the need to copy it to another database.

Data Warehousing : 

Data Warehousing Enhancements to OLAP analytic A data-mining GUI and a new SQL model allow query results to be treated as sets of multi-dimensional arrays on which complex inter-dependent operations - such as forecasting - can be run without the need to extract data to spreadsheets or perform complex joins and unions on the data. A new changed data capture facility based on Oracle Streams provides low or zero latency trickle feeds that combined with integrated extraction, transformation and loading (etl) enable real-time warehousing. BACK

Performance Features of Oracle 10g : 

Performance Features of Oracle 10g Active Session History Reports Automatic PGA Memory Management Automatic Shared Memory Management Automatic Tuning of Multiblock Read Count Automatic Workload Repository Reports Configurable Automatic Workload Repository SQL Collection Enhanced End to End Application Tracing

Performance Features of Oracle 10g : 

Performance Features of Oracle 10g Improved System Statistics SQL Access Advisor SQL Profiles SQL Tuning Advisor SQL Tuning Sets V$SQLSTATS View

Slide 33: 

Oracle Database 10g Windows .NET Developer Strategy

Slide 34: 

Oracle 10g Grid Computing Strategy

Oracle 10g Goals : 

Oracle 10g Goals Highest quality of service Availability, performance, scalability, security Half the Cost Easiest to Manage 50% or more reduction in administration costs Most complete, integrated database

Oracle Grid Computing : 

Oracle Grid Computing Highest Quality of Service and Flexibility Half the Cost Easiest to Manage StorageGrid DatabaseGrid ApplicationServer Grid GridControl Grid Computing enable groups of networked computers to be pooled and provisioned on demand to meet the changing needs of the buisness.

Oracle Database 10g Awards : 

Oracle Database 10g Awards eWeek Analyst Choice Award and Top 10 Product of 2004 I.E. Readers’ Choice Award: Best DBMS for Data Warehousing CRN recommends 5/5 stars DM Review 2004 Winner of Readers Award for Database and Data Store InfoWorld “Database of the Year” 2004

Slide 38: 

ORACLE DATATYPES

Oracle Datatypes : 

Oracle Datatypes Each value manipulated by Oracle Database has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. When we create a table or cluster, we must specify a datatype for each of its columns. When we create a procedure or stored function, we must specify a datatype for each of its arguments.

Slide 40: 

DATATYPES Details Details Details Details Skip Seen Slides

Slide 41: 

ORACLE BUILT-IN DATATYPES

CHARACTER DATATYPE : 

CHARACTER DATATYPE Stores character (alphanumeric) data in the database character set or national character set. Character data is stored in strings with byte values corresponding to one of the character sets, such as 7-bit ASCII or EBCDIC, specified when the database was created.

CHAR Datatype : 

CHAR Datatype The CHAR datatype specifies a fixed-length character string. Values in a CHAR column have the length specified by size. If we insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. If we try to insert a value that is too long for the column, then Oracle returns an error. The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10) column, but the string is blank-padded to 10 bytes before it is stored. For example CHAR(10 CHAR), then you supply the column length in characters.

NCHAR Datatype : 

NCHAR Datatype The NCHAR datatype is a Unicode-only datatype. Define the column length in characters. Define the national character set when creating database. Width specifications of character datatype NCHAR refer to the number of characters. The maximum column size allowed is 2000 bytes. If we insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. we cannot insert a CHAR value into an NCHAR column, nor can we insert an NCHAR value into a CHAR column.

NVARCHAR2 Datatype : 

NVARCHAR2 Datatype The NVARCHAR2 datatype is a Unicode-only datatype. Supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as we specify it, provided the value does not exceed the maximum length of the column. Width specifications of character datatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes.

VARCHAR and VARCHAR2 Datatype : 

VARCHAR and VARCHAR2 Datatype The VARCHAR2 datatype specifies a variable-length character string. Supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as we specify it, provided the value does not exceed the column's maximum length of the column. If we try to insert a value that exceeds the specified length, then Oracle returns an error.

VARCHAR and VARCHAR2 Datatype : 

VARCHAR and VARCHAR2 Datatype We must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. The maximum length of VARCHAR2 data is 4000 bytes

NUMBER DATATYPE : 

NUMBER DATATYPE

NUMBER Datatype : 

NUMBER Datatype The NUMBER datatype stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.

NUMBER Datatype : 

NUMBER Datatype Specify a fixed-point number using the following form: NUMBER(p,s) where: p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision of up to 38 digits. s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127. Specify an integer using the following form: NUMBER(p) This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

NUMBER Datatype : 

NUMBER Datatype Specify a floating-point number using the following form: NUMBER The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

NUMBER Datatype : 

NUMBER Datatype Scale and Precision Specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.

NUMBER Datatype : 

NUMBER Datatype BINARY_FLOAT BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype. Each BINARY_FLOAT value requires 5 bytes, including a length byte. BINARY_DOUBLE BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

LONG AND RAW DATATYPE : 

LONG AND RAW DATATYPE

RAW and LONG RAW Datatypes : 

RAW and LONG RAW Datatypes The RAW and LONG RAW datatypes store data that is not to be interpreted (that is, not explicitly converted when moving data between different systems) by Oracle Database. These datatypes are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use.

RAW and LONG RAW Datatypes : 

RAW and LONG RAW Datatypes Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns. RAW is a variable-length datatype like VARCHAR2, When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, The binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data.

DATE TIME DATATYPE : 

DATE TIME DATATYPE The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. Values of datetime datatypes are sometimes called "datetimes". The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Values of interval datatypes are sometimes called intervals. Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype. If the time zones have not been set manually, Oracle Database uses the operating system time zone by default.

LARGE OBJECT DATATYPE : 

LARGE OBJECT DATATYPE

Large Object (LOB) Datatypes : 

Large Object (LOB) Datatypes The built-in LOB datatypes BLOB, CLOB, and NCLOB (stored internally) can store large and unstructured data such as text, image, video, and spatial data. BLOB, CLOB, and NCLOB data can be up to (4 gigabytes -1) * (database block size) in size. When creating a table, we can optionally specify different tablespace and storage characteristics for LOB columns or LOB object attributes from those specified for the table. LOB columns contain LOB locators that can refer to in-line (in the database) or out-of-line (outside the database) LOB values.

Large Object (LOB) Datatypes : 

Large Object (LOB) Datatypes Selecting a LOB from a table actually returns the LOB locator and not the entire LOB value. LOBs are similar to LONG and LONG RAW types, but differ in the following ways: LOBs can be attributes of an object type (user-defined datatype). The LOB locator is stored in the table column, either with or without the actual LOB value. BLOB, NCLOB, and CLOB values can be stored in separate tablespaces. When we access a LOB column, the locator is returned.

Large Object (LOB) Datatypes : 

Large Object (LOB) Datatypes LOBs permit efficient, random, piece-wise access to and manipulation of data. we can define more than one LOB column in a table. With the exception of NCLOB, we can define one or more LOB attributes in an object. we can select LOB columns and LOB attributes. we can insert a new row or update an existing row that contains one or more LOB columns or an object with one or more LOB attributes.

Large Object (LOB) Datatypes : 

Large Object (LOB) Datatypes In update operations, we can set the internal LOB value to NULL, empty, or replace the entire LOB with data. we can update a LOB row-column intersection or a LOB attribute with another LOB row-column intersection or LOB attribute. we can delete a row containing a LOB column or LOB attribute and thereby also delete the LOB value. we can access and populate rows of an in-line LOB column (a LOB column stored in the database) simply by issuing an INSERT or UPDATE statement. However, to access and populate a LOB attribute that is part of an object type, we must first initialize the LOB attribute using the EMPTY_CLOB or EMPTY_BLOB function. we can then select the empty LOB attribute and populate it using the DBMS_LOB package or some other appropriate interface.

BLOB Datatype : 

BLOB Datatype The BLOB datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. BLOB objects can store up to (4 gigabytes-1) * (database block size) of binary data. BLOB objects have full transactional support. Changes made through SQL, the DBMS_LOB package, or the Oracle Call Interface (OCI) participate fully in the transaction. BLOB value manipulations can be committed and rolled back.

CLOB Datatype : 

CLOB Datatype The CLOB datatype stores single-byte and multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the database character set. CLOB objects can store up to (4 gigabytes-1) * (database block size) of character data.

NCLOB Datatype : 

NCLOB Datatype The NCLOB datatype stores Unicode data. Both fixed-width and variable-width character sets are supported, and both use the national character set. NCLOB objects can store up to (4 gigabytes-1) * (database block size) of character text data. NCLOB objects have full transactional support.

BFILE Datatype : 

BFILE Datatype The BFILE datatype enables access to binary file LOBs that are stored in file systems outside Oracle Database. A BFILE column or attribute stores a BFILE locator, which serves as a pointer to a binary file on the server file system. The locator maintains the directory name and the filename. we can change the filename and path of a BFILE without affecting the base table by using the BFILENAME function.

BFILE Datatype : 

BFILE Datatype Binary file LOBs do not participate in transactions and are not recoverable. Rather, the underlying operating system provides file integrity and durability. The maximum file size supported is 4 gigabytes. The database administrator must ensure that the external file exists and that Oracle processes have operating system read permissions on the file. The BFILE datatype enables read-only support of large binary files. You cannot modify or replicate such a file.

ROWID DATATYPE : 

ROWID DATATYPE

ROWID Datatype : 

ROWID Datatype Each row in the database has an address. we can examine a row address by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the datatype ROWID. You can also create tables and clusters that contain actual columns having the ROWID datatype. Oracle Database does not guarantee that the values of such columns are valid rowids.

UROWID Datatype : 

UROWID Datatype Each row in a database has an address. However, the rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids. Oracle uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the ROWID pseudocolumn BACK

ANSI SUPPORTED DATATYPES : 

ANSI SUPPORTED DATATYPES BACK

User-Defined Types : 

User-Defined Types User-defined datatypes use Oracle built-in datatypes and other user-defined datatypes as the building blocks of object types that model the structure and behavior of data in applications. The sections that follow describe the various categories of user-defined types.

Object Types : 

Object Types Object types are abstractions of the real-world entities, that application programs deal with. An object type is a schema object with three kinds of components: A name, which identifies the object type uniquely within that schema Attributes, which are built-in types or other user-defined types. Attributes model the structure of the real-world entity. Methods, which are functions or procedures written in PL/SQL and stored in the database, or written in a language like C or Java and stored externally. Methods implement operations the application can perform on the real-world entity.

REFs : 

REFs An object identifier (OID) uniquely identifies an object and enables us to reference the object from other objects or from relational tables. A REF is a container for an object identifier. REFs are pointers to objects. When a REF value points to a nonexistent object, the REF is said to be "dangling". A dangling REF is different from a null REF.

REFs : 

REFs To determine whether a REF is dangling or not, use the condition IS [NOT] DANGLING. For example, given object view oc_orders in the sample schema oe, the column customer_ref is of type REF to type customer_typ, which has an attribute cust_email: SELECT o.customer_ref.cust_email FROM oc_orders o WHERE o.customer_ref IS NOT DANGLING

Varrays : 

Varrays An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the position of the element in the array. The number of elements in an array is the size of the array. Oracle arrays are of variable size, which is why they are called varrays.

Varrays : 

Varrays we must specify a maximum size when we declare the varray. When we declare a varray, it does not allocate space. It defines a type, which we can use as: The datatype of a column of a relational table An object type attribute A PL/SQL variable, parameter, or function return type

Nested Tables : 

Nested Tables A nested table type models an unordered set of elements. The elements may be built-in types or user-defined types. We can view a nested table as a single-column table or, if the nested table is an object type, as a multicolumn table, with a column for each attribute of the object type. A nested table definition does not allocate space.

Nested Tables : 

Nested Tables It defines a type, which we can use to declare: The datatype of a column of a relational table An object type attribute A PL/SQL variable, parameter, or function return type When a nested table appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table. BACK

Oracle SUPPLIED DATATYPES : 

Oracle SUPPLIED DATATYPES

Oracle-Supplied Types : 

Oracle-Supplied Types Oracle provides SQL-based interfaces for defining new types when the built-in or ANSI-supported types are not sufficient. The behavior for these types can be implemented in C/C++, Java, or PL/ SQL.

ANY TYPE : 

ANY TYPE

Any Types : 

Any Types The Any types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These datatypes let us dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. SYS.ANYTYPE This type can contain a type description of any named SQL type or unnamed transient type.

Any Types : 

Any Types SYS.ANYDATA This type contains an instance of a given type, with data, plus a description of the type. ANYDATA can be used as a table column datatype and lets us store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types.

Any Types : 

Any Types SYS.ANYDATASET This type contains a description of a given type plus a set of data instances of that type. ANYDATASET can be used as a procedure parameter datatype where such flexibility is needed. The values of the data instances can be of SQL built-in types as well as user-defined types.

XML TYPE : 

XML TYPE Source: The Edison Group

XML Types : 

XML Types Extensible Markup Language (XML) is a standard format developed by the World Wide Web Consortium (W3C) for representing structured and unstructured data on the World Wide Web. Universal resource identifiers (URIs) identify resources such as Web pages anywhere on the Web. Oracle provides types to handle XML and URI data, to access data stored within the database itself. It also provides a new set of types to store and access both external and internal URIs from within the database.

SPATIAL TYPE : 

SPATIAL TYPE

Spatial Types : 

Spatial Types Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled applications, geographic information system (GIS) applications, and geoimaging applications. Once the spatial data is stored in an Oracle database, we can easily manipulate, retrieve, and relate it to all the other data stored in the database.

SDO_GEOMETRY : 

SDO_GEOMETRY The geometric description of a spatial object is stored in a single row, in a single column of object type SDO_GEOMETRY in a user-defined table. Any table that has a column of type SDO_GEOMETRY must have another column, or set of columns, that defines a unique primary key for that table. Tables of this sort are sometimes called geometry tables. The SDO_GEOMETRY object type has the following definition: CREATE TYPE SDO_GEOMETRY AS OBJECT ( SDO_GTYPE NUMBER, SDO_SRID NUMBER, SDO_POINT SDO_POINT_TYPE, SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, SDO_ORDINATES SDO_ORDINATE_ARRAY);

SDO_GEORASTER : 

SDO_GEORASTER In the GeoRaster object-relational model, a raster grid or image object is stored in a single row, in a single column of object type SDO_GEORASTER in a user-defined table. Tables of this sort are called GeoRaster tables. The SDO_GEORASTER object type has the following definition: CREATE TYPE SDO_GEORASTER AS OBJECT ( rasterType NUMBER, spatialExtent SDO_GEOMETRY, rasterDataTable VARCHAR2(32), rasterID NUMBER, metadata XMLType);

MEDIA TYPE : 

MEDIA TYPE Oracle interMedia uses object types, similar to Java or C++ classes, to describe multimedia data. An instance of these object types consists of attributes, including metadata and the media data, and methods. BACK

Slide 93: 

DDL STATEMENTS IN ORACLE

DDLs : 

DDLs DDL stands for data definition language. DDL statements are SQL Statements that define or alter a data structure such as a table. Hence, a typical DDL statement is create table or alter table DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements: * CREATE - Creating a new database object. * ALTER - Altering the definition of an existing data object. * DROP - Dropping an existing data object.

DDLs : 

DDLs Data definition language (DDL) statements enable you to perform these tasks: Create, alter, and drop schema objects Grant and revoke privileges and roles Analyze information on a table, index, or cluster Establish auditing options Add comments to the data dictionary The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table. The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

How To Create a New Table? : 

How To Create a New Table? SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY, 2 subject VARCHAR(80) NOT NULL, 3 description VARCHAR(256) NOT NULL, 4 create_date DATE DEFAULT (sysdate)); Table created.

How To Create a New Table by Selecting Rows from Another Table? : 

How To Create a New Table by Selecting Rows from Another Table? SQL> CREATE TABLE emp_dept_10 2 AS SELECT * FROM employees WHERE department_id=10; Table created. SQL> SELECT first_name, last_name, salary 2 FROM emp_dept_10; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Jennifer Whalen 4400

How To Add a New Column to an Existing Table? : 

How To Add a New Column to an Existing Table? SQL> ALTER TABLE employees ADD (salary NUMBER); Table altered. SQL> SELECT first_name, last_name, salary 2 FROM employees FIRST_NAME LAST_NAME salary -------------------- ------------------------- ---------- Shelley Higgins 22000 William Gietz

How To Delete a Column in an Existing Table? : 

How To Delete a Column in an Existing Table? SQL> Alter table employees drop column last_name; Table altered SQL>Select last_name from employees; Error at line 1 ORA-00904: "LAST_NAME": invalid identifier

Slide 100: 

How to Drop an existing table? SQL>Drop table employees; How to truncate a table? SQL>Truncate table abc;

What Is a Table Index? : 

What Is a Table Index? An index is used to increase read access performance Indexing a database table provides rapid location of specific rows within that table, where indexes are used to optimize the speed of access to rows. An index contains the indexed columns plus a ROWID value for each of those column combination rows. When an index is searched through the indexed columns rather than all the data in the row of a table is scanned. The index space ROWID is then used to access the table row directly in the data space. An index row is generally much smaller than a table row, thus more index rows are stored in the same physical space, a block. As a result less of the database is accessed when using indexes as opposed to tables to search for data. This is the reason why indexes enhance performance

The basic factors of how to index : 

The basic factors of how to index The number of indexes per table. The number of table columns to be indexed. What datatypes are sensible for use in columns to be indexed? Types of indexes from numerous forms of indexes available.

The number of indexes per table. : 

The number of indexes per table. Whenever a table is inserted into, updated or deleted from, all indexes plus the table must be updated. Thus if one places ten indexes onto a single table then every change to that table requires an effective change to a single table and ten indexes. The result is that performance will be substantially degraded since one insert requires eleven inserts to insert the new row into both data and index spaces. The general rule is that the more dynamic a table is the fewer indexes it should have a dynamic table is a table changes constantly, such as a transactions table. Catalog tables on the other hand store information such as customer details; customers change a lot less often than invoices.

The number of table columns to be indexed. : 

The number of table columns to be indexed. Composite indexes are indexes made up of multiple columns. Minimize on the number of columns in a composite key. Create indexes with single columns. Composite indexes are often a requirement of traditional relational database table structures. With the advent of object-oriented application programming languages such as Java, sequence identifiers tend to be used to identify every row in every table uniquely. The result is single column indexes for every table. The only exceptions are generally many-to-many join resolution entities.

Datatypes of Index Columns : 

Datatypes of Index Columns Integers make the most efficient indexes. Try to always create indexes on columns with fixed length values. Avoid using VARCHAR2 and any object data types. Use integers if possible or fixed length, short strings. Also try to avaoid indexing on dates and floating-point values. If using dates be sure to use the internal representation or just the date, not the date and the time. Use integer generating sequences wherever possible to create consistently sequential values.

Types of Indexes : 

Types of Indexes There are different types of indexes available in different databases. These different indexes are applicable under specific circumstances, generally for specific search patterns, for instance exact matches or range matches. The most commonly used indexed structure is a Btree (Binary Tree). A Btree index is best used for exact matches and range searches. Bitmap index. Bitmap containing binary representations for each row. A zero implies that a row does not have a specified value and a 1 denotes that row having that value.

Indexing Attributes : 

Indexing Attributes Various types of indexes can have specific attributes or behaviors applied to them. These behaviors are listed below, some are Oracle specific and some are not. Ascending or Descending. Indexes can be order in either way. Uniqueness. Indexes can be unique or non-unique. Primary keys must be unique since a primary key uniquely identifies a row in a table referentially. Other columns such as names sometimes have unique constraints or indexes, or both, added to them.

Slide 108: 

Composites. A composite index is an index made up of more than one column in a table. Null values. Null values are generally not included in indexes. Sorting (NOSORT). This option is Oracle specific and does not sort an index. This assumes that data space is physically ordered in the desired manner.

Creating Indexes : 

Creating Indexes This section describes how to create indexes. To create an index in your own schema, at least one of the following conditions must be true: The table to be indexed is in your own schema. You have INDEX privilege on the table to be indexed. You have CREATE ANY INDEX system privilege.

Slide 110: 

To create an index in another schema, all of the following conditions must be true: You have CREATE ANY INDEX system privilege. The owner of the other schema has a quota for the tablespaces to contain the index or index partitions, or UNLIMITED TABLESPACE system privilege.

Slide 111: 

This section contains the following topics: Creating a Unique Index Explicitly Creating an Index Associated with a Constraint Creating a Large Index Creating a Unique Index Explicitly

Slide 112: 

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values. Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique index: CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx; Alternatively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. Creating an Index Associated with a Constraint

Slide 113: 

Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled. To enable a UNIQUE or PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.

Slide 114: 

Note: Specifying Storage Options for an Index Associated with a Constraint You can set the storage options for the indexes associated with UNIQUE and PRIMARY KEY constraints using the USING INDEX clause. The following CREATE TABLE statement enables a PRIMARY KEY constraint and specifies the storage options of the associated index: CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;

Specifying the Index Associated with a Constraint : 

Specifying the Index Associated with a Constraint If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you: Specify an existing index that the database is to use to enforce the constraint Specify a CREATE INDEX statement that the database is to use to create the index and enforce the constraint These options are specified using the USING INDEX clause.

Slide 116: 

The following statements present some examples. Example 1: CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1))); Example 2: CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi); Example 3: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci; If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.

Slide 117: 

Creating a Large Index When creating an extremely large index, consider allocating a larger temporary tablespace for the index creation using the following procedure: Create a new temporary tablespace using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement. Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace.

Slide 118: 

Create the index using the CREATE INDEX statement. Drop this tablespace using the DROP TABLESPACE statement. Then use the ALTER USER statement to reset your temporary tablespace to your original temporary tablespace. Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance.

Slide 119: 

How To List All Indexes in Your Schema? If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script: SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'EMPLOYEES'; INDEX_NAME TABLE_NAME UNIQUENES ----------------------- --------------------- --------- EMP_EMAIL_UK EMPLOYEES UNIQUE EMP_EMP_ID_PK EMPLOYEES UNIQUE EMP_DEPARTMENT_IX EMPLOYEES NONUNIQUE EMP_JOB_IX EMPLOYEES NONUNIQUE EMP_MANAGER_IX EMPLOYEES NONUNIQUE EMP_NAME_IX EMPLOYEES NONUNIQUE

Slide 120: 

How To Drop an Index? If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script: CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, birth_date DATE NOT NULL, social_number VARCHAR(80) UNIQUE NOT NULL); Table created.

Slide 121: 

CREATE INDEX student_birth_date ON student(birth_date); Index created. SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'STUDENT'; INDEX_NAME TABLE_NAME UNIQUENES ----------------------- --------------------- --------- SYS_C004129 STUDENT UNIQUE SYS_C004130 STUDENT UNIQUE STUDENT_BIRTH_DATE STUDENT NONUNIQUE DROP INDEX STUDENT_BIRTH_DATE;

Can You Drop an Index Associated with a Unique or Primary Key Constraint? : 

Can You Drop an Index Associated with a Unique or Primary Key Constraint? You can not delete the index associated with a unique or primary key constraint. If you try, you will get an error like this: ORA-02429: cannot drop index used for enforcement of unique/primary key. What Happens to Indexes If You Drop a Table? The answer is that if a table is dropped, all its indexes will be dropped too. What Happens to the Indexes If a Table Is Recovered?

Slide 123: 

If you dropped a table, and recovered it back from the recycle bin, what happens to its indexes? Are all indexes recovered back automatically? The answer is that all indexes will be recovered, if you recover a dropped table from the recycle bin. However, the indexes' names will not be the original names. Indexes will be recovered with the system assigned names when they were dropped into the cycle bin.

Slide 124: 

The following SQL script shows you this behavior: ALTER SESSION SET recyclebin = on; Statement processed. CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, birth_date DATE NOT NULL, social_number VARCHAR(80) UNIQUE NOT NULL); Table created. CREATE INDEX student_birth_date ON student(birth_date); Index created.

Slide 125: 

SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'STUDENT'; INDEX_NAME TABLE_NAME UNIQUENES ----------------------- --------------------- --------- SYS_C004141 STUDENT UNIQUE SYS_C004142 STUDENT UNIQUE STUDENT_BIRTH_DATE STUDENT NONUNIQUE DROP TABLE student; Table dropped.

Slide 126: 

SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'STUDENT'; no data found FLASHBACK TABLE student TO BEFORE DROP; Flashback complete. SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'STUDENT'; INDEX_NAME TABLE_NAME UNIQUENES ------------------------------ ---------- --------- BIN$K47Sg+udQv2tDUW5cWAIrQ==$0 STUDENT UNIQUE BIN$6WI0gc79QNqLSNGp2H2Q1Q==$0 STUDENT UNIQUE BIN$9HwZermkRt+9gonHS/klsQ==$0 STUDENT NONUNIQUE

How To See the Table Columns Used in an Index? : 

How To See the Table Columns Used in an Index? If you want to see the columns used in an index, you can use the USER_IND_COLUMNS view. Here is an example script for you: SELECT index_name, table_name, column_name FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES'; INDEX_NAME TABLE_NAME COLUMN_NAME -------------------- ---------------- ---------------- EMP_EMAIL_UK EMPLOYEES EMAIL EMP_EMP_ID_PK EMPLOYEES EMPLOYEE_ID EMP_DEPARTMENT_IX EMPLOYEES DEPARTMENT_ID EMP_JOB_IX EMPLOYEES JOB_ID EMP_MANAGER_IX EMPLOYEES MANAGER_ID EMP_NAME_IX EMPLOYEES LAST_NAME EMP_NAME_IX EMPLOYEES FIRST_NAME

How To Create a Single Index for Multiple Columns? : 

How To Create a Single Index for Multiple Columns? If you know a group of multiple columns will be always used together as search criteria, you should create a single index for that group of columns with the "ON table_name(col1, col2, ...)" clause. Here is an example of one index for two columns: CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, birth_date DATE NOT NULL, social_number VARCHAR(80) UNIQUE NOT NULL); Table created. CREATE INDEX student_names ON student(first_name,last_name); Index created.

Slide 129: 

SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'STUDENT'; INDEX_NAME TABLE_NAME UNIQUENES ----------------------- --------------------- --------- SYS_C004123 STUDENT UNIQUE SYS_C004124 STUDENT UNIQUE STUDENT_NAMES STUDENT NONUNIQUE SELECT index_name, table_name, column_name FROM USER_IND_COLUMNS WHERE table_name = 'STUDENT'; INDEX_NAME TABLE_NAME COLUMN_NAME -------------------- ---------------- ---------------- SYS_C004135 STUDENT ID SYS_C004136 STUDENT SOCIAL_NUMBER STUDENT_NAMES STUDENT FIRST_NAME STUDENT_NAMES STUDENT LAST_NAME The USER_IND_COLUMNS view confirms that index STUDEND_NAMES has two columns FIRST_NAME and LAST_NAME.

Slide 132: 

THANK YOU