Conference ulysse::rdb_vms_competition

26.0. "Oracle 5.1 once again" by GOFFY::BERENS () Thu Aug 27 1987 11:08

I tried to write a brief description of Oracle. I've seen most of the
information already in other notes, but for interested ...

I have Oracle running on one of our systems, which may sometimes help
if you have any questions.

Piotr     
    
     
1       Oracle RDBMS

1.1     Data Manipulation Language

1.1.1   SQL - Query Language

Oracle uses SQL which is a de facto standard query language for relational   
databases. Oracle's SQL*Plus is a superset of IBM's SQL/DS and DB2, which   
means that applications developed for SQL/DS and DB2 will run unmodified on   
Oracle.
The same language is used for data definition and data manipulation.

1.1.2 Standard Data Manipulation Operations 

SELECT - to retrieve data 
INSERT - a row/rows allows a set of values or an output of a query to be     
             inserted as a new row in a table, 
UPDATE - columns(s) where the rows are selected by a select (WHERE) clause 
DELETE - row(s) where the rows are selected by a select (WHERE) clause

The operations are available interactively from SQL or from a precompiled 
application.

1.1.3   Supported Data Types

-  Character - ASCII string

-  Number  where maximum precision (total number of digits) and scale (number 
           of digits to the right of decimal point can be specified)

-  Date

-  Long - same as "Character" but ...

   It may be up to 65535 characters, but can only be used in limited ways eg
   a) no more than one long column per table, b) long column can not be used in 
   subqueries,functions,expressions, WHERE clauses or indexes c) a table 
   containing a long column can not be clustered. 


1.1.4   Logical Operators

= , != , > , >= , < , <=
[NOT] BETWEEN ... AND  
[NOT] IN ( list ) 
ANY , ALL
EXISTS
[NOT] LIKE ( character pattern 
		%  any sequence of zero or more characters 
		_  any single character )

IS [NOT] NULL				 

NOT , AND , OR


1.1.5   Sorting (Ordering by)

- in descending order
- in ascending order
- by multiple columns

1.1.6   Reduction

SELECT DISTINCT - to eliminate duplicate values

1.1.7   Grouping by

The rows selected as the result of the query can be grouped. This clause makes
it possible to perform aggregate functions on a subset of rows.
 
1.1.8   Joins

equi-join         joining tables (eg A and B) over a column element in A equal
                  to a column element in B

non-equi-join     joining tables (eg A and B) over a column element in A not 
                  equal to a column element in B

outer join        join including rows from table A having no matching rows in 
                  table B

1.1.9 	Union, Intersect, Minus

These operators combine output of two queries eg all distinct rows returned 
by either query individually ( UNION ).
	 
1.1.10  Nested Queries

The selection ie WHERE - clause of a query may be another, nested query. 

1.1.11 Hierarchical Processing

Oracle's extended SQL permits processing of hierarchical relationships within 
a single table eg parents - kids ( CONNECT ... PRIOR ... STARTING WITH clause)

1.1.12 	Built-In Functions

There is set of functions available for use in the queries such as
     
- Arithmetic Functions : ABS, MOD, POWER, ROUND, SIGN, SQRT, TRUNC

- Group Functions to calculate values on sets of columns : AVG, COUNT, MAX, 
  MIN, STDDEV, SUM, VARIANCE

- Date Handling Functions : apart from date arithmetics, TO_CHAR to convert 
  the date representation into asuitable ASCII string 

- String Handling Functions : LENGTH, INSTR (position of substring), UPPER,  
  LOWER, LPAD, RPAD (left/right padding), SUBSTR (extract substring), VSIZE 
  (number of bytes to store the string) 

- Other Functions : DECODE (decoding code values), NVL (returns specified
  value if column value is null, otherwise column value)

1.1.13  Integrity Control

  Oracle insures the data integrity by checking that the type of data being 
  stored conforms to the table definition ie CHARACTER, NUMBER, etc. The extra
  attributes checked are NOT NULL if a column must contain a value and UNIQUE 
  if a unique index is defined on a column or columns. No other constraints
  than NOT NULL and UNIQUE (index)can be specified on columns.

1.1.14  Security

The table access is controlled by means of GRANTing and REVOKing privileges.
There are three privileges which can be granted to a user identified by his 
username and password namely CONNECT, RESOURCE and DBA.
The users can also be authorized for automatic logon with their VMS username 
and no password ie the VMS user name is compared with the authorized name.

All tables are owned by their creator (RESOURCE privilege to create tables).
The DBA privilege allows everything. CONNECT privilege allows access for 
querying somebody else's tables.    

The data security is controlled by the function being performed. 
Privileges such as select,insert,update,delete,alter (column 
definitions), etc can be granted to selected users or public on an object 
bases (table, view, synonym).

The views can also be used to give users different insight into the same 
information depending on security aspects.


1.1.15 Concurrency Control 

At all times a query obtains consistent view of data ie not committed 
transactions are invisible to other users. The unmodified blocks can be
fetched from the BI file.

The row level locking is supported by means of reads for update ( special
clause on SELECT ... FOR UPDATE OF column). 

Explicit table locking includes LOCK table ,.. IN SHARE (queries) or SHARE 
UPDATE ( updating of some rows ) or EXCLUSIVE mode. A table can be locked 
with WAIT/NOWAIT mode.


1.2     Data Definition Language 

1.2.1 DDL

Data Definition Language is a part of SQL. The commands used include creating/ 
modifying/dropping of partitions, space, tables, views, indexes, clusters and
synonyms (partitions and files can not be dropped).

Partitions -
  The whole database is built of partitions ( CREATE PARTITION ). 
Space      - Space may be reserved in a partition for tables and 
  indexes ( CREATE SPACE ).
Table      - A table with all its columns and their attributes can be created 
  in reserved space (CREATE TABLE). 

View       - CREATE VIEW view_name AS query - where query's selection 
  conditions specifies what the view contains. Once defined the view can be 
  treated as a table. Only quering is allowed if the view joins tables or if 
  the query defining the view contains functions.

Synonym    - creates synonyms for tables or views.


A partition can be extended, space definition can be modified, table can be 
altered ie a column added or modified, etc. All the operations can be done 
without unloading/loading the database and some can be performed without 
shutting down the database.   

1.2.2 Clustering

  Two or more tables based on common values may be physically clustered 
  (CREATE CLUSTER) ie stored in same physical area. This may improve 
  performance of joins for related tables.

1.2.3   Indexing 
  
  Indexes on any column can be created/dropped at any time. The index can be 
  constructed on a group of column (concatinated index). It can be created as 
  unique (CREATE UNIQUE INDEX) in which case Oracle will ensure that no two 
  rows have the same value/combination of values in a column/columns. 
  Actually the uniqueness of a column can only be reinforced by using a unique
  index. 
  The indexes can be stored in compressed or uncompressed form. 
  
1.2.4   Limits

tables in a database			-
rows in a table				-
columns in a table			254
characters in a row			60960
characters in a character field 	240
digits     in a number    field 	105		
significant digits in a number field	40
range of values in a date field		1-Jan-4712 BC to 31-Dec-4712 AD
indexes on a table			-
tables or views joined in a query	-
levels of nested subqueries		16
characters in a name			30

1.3     Physical Storage Characteristic

An Oracle database is devided in partitions ( logical devision ). Each 
partition is made up of one or more physical files. Each file used as part of 
the database is a fixed length, 2k byte record, contiguous-best-try, 
sequential disk file. The files are accessed via RMS block I/O.
The files can be placed on different disks.

A partition can be added/extended - the database should be shut down for
users.
 
The maximum number of database files under VAX/VMS is 64.

When a table is created the space for table ie partition where it should reside
initial size and the size of extent can be specified. If not specified the 
default values are taken. The table will be automatically extended up to 15 
times ( 15 extents ). 

1.4     Report Facility ( within SQL )

  SQL*PLUS allows some formatting of the query output ( titles,column format, 
  heading, breaking on, computing sum ) and storing it in a file.

  The query itself may be stored in a file (command file), which can be 
  retrieved for execution either at sqlplus command level (SQLPLUS file params)
  or from within SQL.

1.5     Data Dictionary

  The data dictionary is integrated into Oracle. All new definitions 
  (partitions, space, tables, users, access privileges, etc) and all the 
  modifications are automatically stored in the dictionary.   
  All this information is stored in system tables, which can be queried as
  all other tables. 

1.6     Recovery

1.6.1   AI and BI

Before Image - transaction commitment or rollback.
               Automatic rollback of incomplete transactions on system starup.
	       Automatic rollback on a deadlock detection.

After Image Journalling - it is optional, can be switch off.
	       It is physical rather than logical journaling ie it copies all
	       modified database blocks to the disk. It allows a rollforward
	       operation in case of a disk crash.
	       It is not supported in a cluster.		 
	

1.6.2   Backup

EXPORT/IMPORT facility can be used for backup of the database. The modes of
         operation are : user  - user's part of the database 
                         table - some tables ( definitions and data )
                         full database - all user defined objects.

The backup with VMS Backup utility can also be used. 

1.7     Data Loader

This utility allows loading of data from sequential files into oracle database.

1.8     Cluster Support

Oracle supports cluster-wide database. The feature which is not supported is
cluster-wide after-image jornalling. Each member of the cluster has to run
Oracle kernel . 
The BI file is fixed divided between the nodes in a cluster (static).

1.9     Multiple and Remote Databases

Multiple databases on the same node or different nodes is supported. However,
the user or an application can have only one database open at any time. 

SQL*Star is a distributed database facility. It has two components namely 
SQL*Net and SQL*Connect.
 
SQL*NET provides the ability to log on to an Oracle database running on a 
remote host and to execute SQL commands against that database. Oracle supports
distributed databases on PC's. SQL*Net supports location transparency ie the
user does not need to know whether a table is located at the local node or a 
remote node. A distributed query is supported. A distributed update ie 
simultaneous update of a local and remote is not supported.

Downline loading ( extracting ) of a table from a host (mini or mainframe) to
the PC's own database is supported. Uploading (copying) of of database tables 
from a PC to a mini/mainframe is also supported.

SQL*Connect permits an Oracle user to log on to and issue SQL commands against
SQL/DS and DB2 databases on remote host. 


1.10    Utilities

1.10.1  Application Development

1.10.1.1 Precompilers

The supported precompilers are : C, COBOL, FORTRAN, PL/I, Pascal, Ada.
Data can then be manipulated by means of precompiled sql statements.
There is a separate precompiler for each language.

1.10.1.2 Application Generator

SQL*Forms - interactive application generator. It provides on-line help 
facilities, pop-up menues and screen painting.
It creates a form application, which can be started from DCL level or the 
SQL*Menu utility. The application can via created form directly access data 
in the database ie query it, update, insert or delete.

The elements of the application are :
- form    - an application can include many screen pages, which can be accessed
            sequentially or random via a menu
- block   - a block is part of a screen corresponding normally to a table in the
            database
- fields  - reflect columns in the database

Features of the application :
- the same form can be used for quering, inserting, updating or deleting rows
- field validations such as value range, format, length and any validation
  which can be expressed in an sql statement
- triggers on many levels are available 
	   - form  trigger  
	   - block trigger  
           - field trigger
  They may be executed on entry to or exit from a form, a block or a field.
  The block triggers may also be triggered by a specific operation for example
  pre/post-query on a query and similarly there are  pre/post-delete,update and 
  insert. The triggers can be sql statements or user-exit routines coded in 
  one of supported languages and linked to the interactive application 
  processor.   

  The supported terminals include VTxxx for DEC, 3270 for IBM and some others. 
  All functions required from the terminal are stored as character sequences 
  in the database. These description can be changed or new added. 

1.10.1.3 SQL*Menu 

This allows construction of menu driven application. 
The features are :
- local language option eg menus in German
- forward and backward linking of menus for going up and down the tree. Jumps
  accross the tree also possible.
- parameter substitution support - setting specific parameters for a session. 
- function key mapping
- restriction per user / class of users what they see of the menu ( security )


1.11    Report Utility 

SQL*Report 

  It allows interleaving of the results of database queries with text and 
  graphics into a single formatted report or document. 

  A range of high-speed and letter-quality printers are supported.


1.12    Spreadsheet

SQL*Calc

  It is a spreadsheet which invokes data directly from the Oracle database. 
SQL commands can be entered directly into the spreadsheet cells in the same 
way as formulas in a conventional spreadsheet. 
  Data in the database can be manipulated from the spreadsheet.
  SQL*Calc's formula entry procedures and command menus are compatible with 
those used by Lotus 1-2-3. A Lotus 1-2-3 to SQL*Calc conversion utility is 
included with SQL*Calc.
  SQL*Calc does not support interactive graphics ( see SQL*Graph).

1.13    Interactive Graphics

SQL*Graph 

  Data resulting from SQL queries can be presented in the form of color pie 
charts, bar graphs and line graphs.
  Graph commands allow user to change the color, pattern, size , etc of every 
element.
  Graph commands can be saved and reused.

  A variety of output devices is supported eg VT240, IBM PC, Tektronix, 
Hewlett-Packard devices.

 
1.14    Integration with other products

The following products have a direct interface to Oracle :
RS/1
MACCS
This means that it is possible to retrieve data from Oracle database directly 
from within RS/1 or MACCS.

Oracle does not support integration in allin1. 


1.15    Others

1.15.1  Portability

Oracle runs on many mainframes, minis and PC's. Oracle database and Oracle 
applications can be transported between the different computers.

Hi,
 
     By August 16 2 780's, an 8600, and 8700 were load balanced in a VAXCluster
with an ORACLE 5.1.17 shared database.  There are a total of about 30
instances of 13 different applications in the database.  No one is very large. 
Altogether about 240000 blocks of data.  Sometime just before the Sept. 16
the database may have become corrupted in the ORACLE database.  The BWR (buffer
writer) process would fail.  ORACLE support traced it to a table create that
a user process was doing.
One of the ORACLE routines called memcopy was getting a parameter for
the length of the memory copy to global buffers in the section file.  Parameter 
was bad, the length too long.  ORACLE support suggested validating the indexes
on the EXTENTS table and drop/recreate the indexes or exporting the entire
database, reinitialize and import.  The latter being the best solution.  I
misunderstood the former solution.  I ran Validate on the indexes of the
EXTENTS table.  Nothing wrong.  However it was meant to drop/recreate the
indexes regardless of the validation results.  It seems validate index
only checks links in the forward direction.  An entire export of the database
was started.  In effect it went out to lunch.  The entire export was aborted
when only several accounts had been exported in about 8 hours. about 4 hours
were spent on one user account that was exported interactively in several 
minutes.  A file was created to export user by user and the user export
begun. It took 43 hours(elapsed)!  Two of the export files had abnormal file 
ends and would not import. Lucky they were accounts that only had synonyms to
tables.  If it had been an account with data ....  Import took about
half the time.  Normally the rule of thumb is import takes about twice
the time of export.  In a support alert last fall ORACLE suggested the
addition of an index to the TABAUTH table which would improve performance
of export.  This was to be automatically included in versions 5.1.11 and after
as a part of IOR INIT.  It didn't make it in, fell through the cracks.  
According to their support this index (TAU$CTG) is supposed to do the same
thing as TAU$GTC.  TAU$GTC was there.  After the database was up and running
again, the TAU$CTG index was created and an account that took 3.5 hours to
export during the 43 hour marathon took 24 minutes of elapsed time.
Still have not heard whether the bad length parameter was caused by a bug in
the code or bad data in the system dictionary tables.
The import took about 17 hours... so an 8.5 hour export if it had been normal
for about 240000 blocks of data.  Still seems too long to me.
An organization across the hall is considering Rdb/VMS.  This episode added
a little emphasis to their choice. However in the last couple of days upper 
management is pushing them to use ORACLE because it's their "standard".
Now VAX SQL may make the difference.  It provides an argument that 
development can be done with VAX SQL on Rdb/VMS and minor mods to the
programs if there needs to compatility later with ORACLE.

FYI here's the index create statement, it must be done from 
the SYS account in ORACLE.
Create index tau$ctg on tabauth(tau$creator, tau$tname, tau$grantor)
nocompress;

If there is a 21503 dictionary error use 
Create index tau$ctg on tabauth(tau$creator, tau$tname, tau$grantor)
nocompress nosyssort;

/Tim

    The govt. organization mentioned in .1 went with ORACLE.  VMS will
    dissappear there in 1-2 years being replaced by UNIX OS on those VAX.
    
    /Tim

According to the Spring edition of Oracle's magazine, users need to take
advantage of undocumented product implementations in order to maintain tables in
sorted order for efficient Btree access. 

Rdb does this for you automatically in Version 3.0