Ingres FAQ (2 of 3)

Ingres FAQ (2 of 3)

Post by M Darrin Chan » Thu, 18 Nov 1993 07:03:28



  Deep South Local IUA

  John W. Olson    Voice:    (504) 391-6472
  Chevron Information Technology Company    FAX:    (504) 391-6472
  C/O Chevron Chemical Company    E-mail: j...@cplan046.ion.chevron.com
  Oak Point Plant
  P.O. Box 70, Highway 23
  Belle Chasse, LA  70037

  New England IUA

  Terry Cohen    Voice:    (617) 272-5060
  C/O Ingres    FAX:    (617) 272-3175
  No. 6 New England Executive Park
  3rd Floor, Burlington, MA  01803

  Northeast IUA

  Dan Fielding    Voice:    (508) 480-0086
  Auburn Systems Corporation
  313 Boston Post Rd. West, Suite 313
  Marlboro, MA  01545

  Mid-Atlantic IUA

  William J. Koster    Voice:    (301) 497-2712
  Computer Sciences Corporation    
  4600 Powder Mill Rd.
  Beltsville, MD  20705

  MI

  Rick Blyth    Voice:    (313) 483-6440
  Johnson Controls
  Ann Arbor, MI

  Twin City IUA    

  Dave Backman (Pres.)    Voice:    (612) 593-1122
  CSC Partners    FAX:    (612) 593-1135
  5500 Wayzata Blvd., Ste. 1100
  Minneapolis, MN  55446

  Greg Florin    Voice:    (612) 733-5233
  3M Company
  255-5N-06
  St. Paul, MN  55144

  Triangle Area IUA

  Jerry Helms    Voice:    (919) 787-8611    
  DB Basics    FAX:    (919) 571-0462
  P.O. Box 30754, Research Triangle
  Raleigh, NC  27622-0754

  NJIUA

  Stephanie Lyon    Voice:    (609) 737-0425
  SDL Consulting, Inc.
  629 Scotch Road
  Pennington, NJ  08534

  NM

  Dwight Coles    Voice:    (505) 268-2661
  RE/SPEC Inc.    FAX:    (505) 268-0040
  4775 Indian School Rd. N.E., Suite 300    E-mail: drco...@sandia.gov
  Albuquerque, NM  87110

  Los Alamos IUG

  Pan Jasmine    Voice:    (505) 665-1813
  P.O. Box 1663, M/S B254
  Los Alamos, NM  87545

  NYIUA

  Marie Marino    Voice:    (201) 587-1400
  ASK-Ingres Corporation    FAX:    (201) 587-1495
  6th Floor, Park 80 West Plaza
  Garden State Pkwy at Interstate 80
  Saddlebrook, NJ  07662

  PA

  Christine Locklane    Voice:    (412) 243-9020
  Ciber
  110 Baker Drive
  Pittsburg, PA  15236

  Bob Celeste    Voice:    (215) 641-7855
  Rohm & Haas, Inc.
  1727 Norristown
  Springhouse, PA  19477

  Western Penn. IUA

  Robert Praisner    Voice:    (412) 967-2374
  Computer Specialists
  110 Baker Drive
  Pittsburg, PA  15236

  TX

  Glenn Ball    Voice:    (713) 370-0025
  EE Yarborough
  13011 Green Rainbow
  Cypress, TX  77429

  Sandy Sheehan    Voice: (512) 328-2300
  Cooperative Computing, Inc.    FAX:    (512) 328-6461
  P.O. Box 160700
  Austin, TX  78716

  Salt Lake IUA

  Jerry Harper     Voice:    (801) 863-8947
  Morton-Thiekol Corporation
  9160 North Highway 83
  M-345, Strategic Receiving
  Bingham City, UT  80302

  Bill Wilson    Voice:    (801) 539-5251 Ext. 559
  Litton Guidance Systems Corporation
  2211 West Temple
  Salt Lake City, UT  84116

  VA

  John Morgan    Voice:    (804) 323-6789
  Relational Reality
  P.O. Box 13783
  Richmond, VA  23225

  WA

  Diane Hitzel    Voice: (206) 454-6363 Ext. 5322
  Puget Power and Light
  P.O. Box 97034
  MS OBC-07S
  Bellevue, WA  98009

  02.004 How can I contact my local users group?  (Canada)

  Atlantic Canada IUA

  Tracy Gulliver    Voice:    (506) 453-4564
  University of New Brunswick    FAX:    (506) 453-3590
  Computer Services Dept.    E-mail:     t...@unb.ca
  P.O. Box 4400
  Fredericton, New Brunswick
  E3B 5A3

  National Capitol IUA

  Mario Morel    Voice:    (613) 945-5190
  P.O. Box 1311    FAX:    (613) 945-5190
  Station B, Ottawa, Ontario
  Canada,  K1P 5R4

  Toronto Area Ingres Users Group

  Aaron Burns    Voice:    (416) 438-6650
  AG Simpson    FAX:    (416) 438-5141
  675 Progress Ave.
  Scarborough, Ontario
  Canada,  M1H 2W9

  AFUIAN (Francophone)

  Luc Valliere    Voice:    (514) 987-4338
  Universite de Quebec    FAX:    (514) 987-6503
  C.P. 8888, Station A
  Quebec, P.Q.
  Canada,  H3C 3P8

  Prairies IUG

  Adeline Skwara    Voice:    (306) 787-2548
  Director of System Services Branch
  Saskatchewan Energy & Mines
  Saskatchewan
  Canada

  West Coast IUG

  Karl Dohan    Voice:    (604) 278-3411
  MacDonald Dettwiler & Associates
  Vancouver, British Columbia
  Canada

  03.001 ftp site for INGRES tools

  A small number of INGRES tools are available by ftp from

     fractal.ucs.indiana.edu  (/pub/ingres)

  03.002 ingperl, sqlperl and DBperl

  There is a variant of Larry Wall's  popular `perl' language that
  provides a full-function (ie read/write) SQL interface to ASK/INGRES
  (and significantly NOT to University Ingres).  It is referred to
  by all of the above names by different people in different posts.

  The INGRES-friendly variant is available for anonymous ftp from:

    ftp.daemon.co.uk  (/pub/perl/db/sqlperl)

  As mentioned in the README file at that location, and it bears
  repeating here, there is an effort underway to define a standard API
  that will allow perl to access ALL the popular RDBMSs and it would be
  well to keep an eye on that process so that you don't end up being
  surprised that you've written a lot of non-standard code.  For the
  moment though, sqlperl is the only version with access to INGRES.

  03.003 tclsql

  tcl is an acronym for "Tool Command Language".  The version
  extended to talk to INGRES is `tclsql' and it is thought to
  be ASK/INGRES 6.4 specific.

  It is available for anonymous ftp from:

    harbor.ecn.purdue.edu   (/pub/tcl/extensions/tclsql-1.0.tar.Z)

  03.004 $II_SYSTEM/ingres/sig

  There are a number of unsupported utilities in the sig directory
  that is part of the standard distribution.  One of the notable
  items in this directory is Paul Neal's `schemadef' utility (see
  section 08.007 for further information).

  Some of these tools may not be compiled and may require either or both
  of ABF and ESQL/C to create an executable.

  03.005 NAIUA tool set

  The NAUIA tools committee has a small number of contributed tools
  available.  Contact the chair of the tools committee: Alain
  Charpentier (514)877-3301 charp...@ere.umontreal.ca

  New contributions are always welcome.

  04.002 Can I use ODBC?

  ODBC is Microsoft's implementation of the Call Level Interface
  specification defined by the SQL Access Group.

  INGRES has an ODBC driver.  This provides MS-Windows applications with
  transparent access to INGRES databases.  The ODBC driver and the MS
  Driver Manager are included in the INGRES/Net product for MS-Windows.
  INGRES can act as a server using ODBC.

  04.003 What is Grafsman?

  GRAFSMAN, an INGRES third-party application, lets developers add
  graphics to their applications on any of 35 platforms, ranging from
  DOS, to Windows, OS/2, UNIX, and VMS. Output can be directed to
  character terminals, X, DOS E/VGA, DEC REGis, Tektronix mono/color,
  Epson dot matrix, HP LaserJet, or PostScript, among many others.

  GRAFSMAN can be built into INGRES/ABF, INGRES/Vision, and
  INGRES/Windows4GL applications.

  For more information on GRAFSMAN, call The ASK Group at 1-800-446-4737
  or contact your local sales office.

  04.004 What is GQL?

  GQL is a third-party graphical query language supported and distributed
  by The ASK Group.  There is a read-only version called GQL/User and a
  read/write version called GQL/Update.

  Each site also needs at least a single copy of the GQL/Admin module
  to control the client GQLs.

  GQL/Design assists with database design and table definition.

  GQL is capable of accessing all the popular database management systems
  as well as INGRES, and a future version is expected to be able to
  join tables from disparate systems.

  05.001 What is a good book on INGRES?

  (Where the book can be ordered from The ASK Group the The ASK Group
  part number and price is indicated.)

  A Guide to Ingres
  = ===== == ======
  Chris Date
  Addison-Wesley
  1987
  ISBN-0-201-06006-X

  P/N 5-0-CDATE $32.95US

  This book is getting badly out of date but it does give a nice
  introduction to the older INGRES tools.  It concentrates on QUEL (it
  treats SQL only in passing and with barely suppressed scorn).  It has a
  good description of QUEL aggregate functions.   University Ingres users
  may find this book marginally more useful than users of ASK/INGRES
  would, especially because of the emphasis on QUEL.

  Ingres: Tools for Building an Information Architecture
  ======= ===== === ======== == =========== ============
  Carl Malamud
  Van Nostrand Reinhold
  1988
  ISBN 0-442-31800-6

  P/N 6-0-MALMD $35.95US

  According to Pamela Barker's review in the February 1993 NAUIA
  Newsletter, since 6.4 came out this book is a little stale too.
  It is recommended for readers who already have a basic knowledge
  of INGRES.

  INGRES SQL Developer's Guide
  ====== === =========== =====
  Carolyn and Jack Hursch
  Windcrest/TAB/MacGraw Hill
  1992

  Covers the INGRES implementation of SQL and embedded SQL.  Examples
  of embedded SQL use C.

  Programming in SQL with Oracle, INGRES, and dBASE IV
  =========== == === ==== ======  ======  === ===== ==
  John Carter
  Blackwell Scientific Publications,
  ISBN 0-632-03136-0
  1992

  [No first-hand reviews available.]

  Introduction to INGRES
  ============ == ======
  Margaret A. Zinky, James W. Everett, Linda J. Hawbaker
  PWS-Kent Pub. Co.
  ISBN 0-534-92869-2
  1992

  [No first-hand reviews available.]

  INGRES Database Administrator's Guide
  ====== ======== =============== =====
  (Release 6.3) P/N 63-99-17091 $50.00US
  (Release 6.4) P/N 64-99-17091 $50.00US

  This is nonpareil the best book on INGRES for the practicing DBA.  It
  is included in the standard documentation kit that comes with the base
  system.  (When you get a RTFM-type response to a posting, chances
  are this is the "FM" they meant.)

  The INGRES papers: anatomy of a relational database system
  === ====== ======  ======= == = ========== ======== ======
  Michael Stonebraker, editor.
  Addison-Wesley
  1986

  A collection of scholarly papers on University Ingres, edited by
  one of the chief architects, Professor Stonebraker.  These papers
  are literally only of academic interest (in the best sense of the
  term).

  05.002 What is a good book on relational databases?

  An Introduction to Database Systems, Vols 1, 2 and 3
  == ============ == ======== ======== ==== == = === =
  C. J. Date
  Addison-Wesley
  ISBN 0-201-14201-5

  Now venerable, this still seems to be the standard work.  It must be
  in its 5th or 6th edition by now.  This is a three volume set.  The
  essential one is volume 1.  This book covers all the major data
  management models, not just the relational model. These books have
  very nice annotated bibliographies.  Fairly rigorous and academic.

  Fundamentals of Database Systems
  ============ == ======== =======
  R. Elmasri and S. B. Navathe
  The Benjamin/Cummings Publishing Company
  ISBN 0-8053-0145-3

  This book compares favorably with Volume 1 of the Date book above.
  It covers all the major data models, not just the relational model.
  Extremely rigorous and academic.

  SQL and Relational Basics
  === === ========== ======
  Fabian Pascal
  M&T Books
  501 Galveston Drive
  Redwood City, CA 94063
  ISBN 1-55851-063-X
  $28.95

  This is a superb book for the practitioner and novice alike.  It
  cannot be recommended too strongly.  It is nominally aimed at the
  PC database market, but there is little or nothing in it that is
  really specific to PCs.  The three striking features of this book
  are: (1) it describes the database software selection process
  using tests for relational fidelity; (2) it describes in terms
  even your management can understand WHY relational fidelity is
  important and why you can't get away with playing fast and loose
  with Codd's rules forever, and (3) it describes how to live with
  SQL's manifold defects, in a refreshingly candid way.  Highly
  recommended.

  A Guide To The SQL Standard (Third Edition)
  = ===== == === === ========
  C.  J.  Date with Hugh Darwen
  Addison-Wesley
  ISBN 0-201-55822-X

  Covers SQL2 (SQL/92) with an overview of SQL3.  Good thorough book that
  does exactly what its title says:  interprets the (sometimes
  impenetrable) language of the official standard in a straightforward
  and lucid manner.  Recommended if you wish to fully understand every
  aspect of the SQL language.

  Incidentally, the ANSI standard document ANSI X3.135-1992, can be
  ordered from:

    Customer Service
    ANSI
    11 West 42nd St.,
    New York
    NY 10036

    Tel: (212)642-4900

  06.001 Where can I read about INGRES?

  inquire_ingres
  ==============

  The ASK Group publish a quarterly technical newsletter called
  inquire_ingres and a subscription is included with your support
  agreement.  Typical content is "How to..." or "How We..." articles
  submitted by customers; pronouncements from ASK Group personages;
  helpful hints, and product release schedules.

  The authors assume a high degree of technical knowledge in the reader.

  The newsletter is marked "INGRES CUSTOMER CONFIDENTIAL" and is not
  intended to be circulated.

  For copies, call:
  Sharon Squires
  Senior Technical Writer
  The ASK Group, Inc.
  1080 Marina Village Parkway
  Alameda, CA 94501-1041
  USA

  Spotlight on Ingres
  ========= == ======

  The ASK Group publishes a quarterly marketing newsletter/tabloid
  apparently consisting of the collected press-releases of the preceding
  quarter.  (These are also available through the BBS function of
  INGRES/Advisor, on a more timely basis.)

  NAIUA Newsletter
  ===== ==========

  The North American INGRES Users Association publishes the NAIUA
  Newsletter 3 or 4 times a year to members.  Membership is automatic
  for INGRES customers in North America, and is open to consultants
  and vendors of INGRES-related products.

  The newsletter is largely filled with the comings-and-goings of
  the executive; useful advertising (sometimes including Help-Wanted);
  helpful hints, and information about imminently forthcoming new features
  and products.

  For copies, call:
  NAIUA Headquarters at (408)649-0644

  Articles can be submitted to:
  Steve Caswell
  NAIUA Newsletter Editor
  Palmer & Associates, Inc.
  400 Pinnacle Way, Suite 430
  Norcross, GA 30071

  comp.databases.ingres
  =====================

  There are two Usenet newsgroups of interest to INGRES users:
  comp.databases and comp.databases.ingres

  Interspersed throughout the welter of Paradox questions that seem to
  constitute 95% of the traffic on comp.databases there are items either
  relating to INGRES or of interest to database programmers and DBAs in
  general.

  comp.databases.ingres is specific to INGRES, and is devoted almost
  exclusively to the commercial ASK/INGRES variant.  On the whole this
  seems to be a fairly responsive group with a moderate amount of
  traffic.

  comp.databases.* is archived at Indiana University and can be accessed
  through `gopher.'  Once you reach the Indiana University Bloomington
  Gopher, select `Computing Information & Help', and then `Database
  Support Group.'

  06.002 What are sources of reports on INGRES?

  Independent, in-depth reports on commercial software are very
  expensive.  Your local INGRES sales office will probably be able to
  provide free reprints of the relevant sections of favorable reports
  such as the ones by The Aberdeen Group and ButlerBloor.

  Aberdeen Group, Inc.
  92 State Street
  Boston, Massachusetts 02109

  Tel (617) 723 7890
  FAX (617) 723 7897

  ButlerBloor Ltd
  Challenge House
  Sherwood Drive
  Bletchley, Milton Keynes
  MK3 6DP, England

  Tel +44 (0)908 373311
  FAX +44 (0)908 377470

  07.001 What is INGRES/Advisor?

  INGRES/Advisor is an on-line technical support service that is
  available 24 hours a day.  It includes tools for accessing the
  so-called Knowledge Base; for viewing technical notes, for
  accessing the comp.databases.ingres newsgroup and for monitoring
  the progress of a technical support call.

  07.002 How can I access INGRES/Advisor?

  It is available to users on the Internet by

     telnet advisor.ingres.com

  and to everyone else (at no charge) through CompuServe (host name
  is ADVISOR).  Note that if you post to the comp.databases.ingres
  newsgroup using CompuServe, your posting really will get out into
  the world.

  You must be an ASK/INGRES customer with a support agreement in order
  to access the system.  You will be prompted for your contact ID
  number and a password.

  07.003 What is ProAlert?

  ProAlert is a service which provides access to documents describing
  known INGRES bugs and anomalies.  Where possible, the documents
  describe work-arounds.

  07.004 How do I log trouble calls?

  ASK/INGRES users who have support agreements will have two designated
  technical support contacts who are authorized to place trouble calls.

  Trouble calls can be placed by calling Technical Support at
  (510)769-1418, through the TSConnect option of INGRES/Advisor,
  or by E-mail.

  To log a call by E-mail you need a call-entry form.  To get a
  call-entry form mail a request to:

    tech_...@ingres.com

  with a subject field of (capitalization _IS_ significant):

    Get CE Form

  and a message body of:

    CONTACT ID: <contact_ID>
    LICENSE #: <license_nr>

  You will receive a call-entry form back, which you should copy for
  future use.   To actually log a call, make a copy of the call-entry
  form and complete it as directed.  Then mail to:

    tech_...@ingres.com

  with a subject field of (capitalization _IS_ significant):

    Enter New Call

  and for the body of the message, send the completed call-entry form.

  08.001 How can I store a large object in an INGRES database?

  (The Fall 1993 issue of inquire_ingres has an article starting
  on p.6 that covers this issue at length.)

  Until INGRES supports BLObs (Binary Large Objects), rumoured to
  included in the forthcoming 6.5 release, the biggest thing INGRES
  can cope with is an object that can be crammed into a char(2000) or
  varchar(2008).

  INGRES/Windows4GL has some methods for manipulating objects of
  up to 64kb, but if it isn't possible to use Windows4GL, or if
  the objects in question are bigger than 64kb, then other
  techniques are required.

  There are three approaches that can be used.  The first is to write
  a procedure that takes the large object and encodes it in the ASCII
  character set, and then stores it in (multiple, numbered) rows in a
  varchar(2004) column.  Another procedure is needed to extract the
  rows in the proper order and to decode the ASCII to recover the
  original object.

  Another approach with MUCH more overhead, but which does at least
  work, is to create a separate table for EACH instance of an object.
  The table is defined as a heap with a single i1 column.  Each byte
  of the object is written to its own row.  The advantage of this
  approach is that there is no need to write software for encoding and
  decoding the object.  

  A third technique is "horizontal decomposition" which involves encoding
  the object in ASCII, then splitting it into chunks of 2008 characters
  or less, and storing the chunks in two or more tables.  If you imagine
  the tables pasted up side by side then the large object appears as on
  long row spanning the separate tables.  This is preferable to the other
  two techniques because there are no worries about numbering and sorting
  the rows to ensure that the rows comprising the object are retrieved
  in the correct order.

  08.002 How can I suppress execution (I only want to see the QEP)?

  Use the 'set optimizeonly' command.

  08.003 Other than this FAQ, what is a good source of practical info?

  There are a number of useful notes included with the ASK/INGRES
  base system.  On UNIX systems running release 6.4/02 and later,
  you will find them in

  $II_SYSTEM/ingres/advisor

  The drift of some of these notes is repeated in some of the
  FAQs answered here.  Usually the INGRES note is referenced in that
  case.

  NB: Prior to release 6.4/02, the notes where supplied in the

  $II_SYSTEM/ingres/notes

  directory and were numbered differently.

  08.005 Why is INGRES reporting an incorrect row count?

  The rowcount reported by the 'help table' command is only an
  ESTIMATE of the number of rows in the table.  INGRES updates this
  information opportunistically.  It updates the information when
  a `modify' is done on the table, or when a sufficient number of
  rows in a table are updated by a single transaction, but it will quickly
  become stale again.

  It is slow, but you can ALWAYS get an accurate row count by doing:

     select count(*) from <table_name>

  08.006 How do I do an outer join?

  INGRES does not presently support the outer join.  This is expected to
  be available in Release 6.5

  As an interim solution a sufficient number of unions may achieve
  the required effect.

  08.007 How can I add/delete/alter a column in a table?

  There is no supported tool for this operation.  

  One common approach is to replicate the table using `create table XXX
  as select'; drop the table, and then create it again using `create
  table YYY as select' from the copy.

  Another (better) approach is to copy the table out of the database
  using `copydb -c <db_name> <table_name>' to generate a pair of SQL
  scripts called copy.out and copy.in (NOTE the -c argument to copydb.)

  copy.out is executed by `sql':

     sql <db_name> < copy.out

  The copy.in file contains commands to recreate and load the table and
  can be edited to drop the original table and re-declare it as required.
  The copy.in script is executed the same way:

    sql <db_name> < copy.in

  The advantage of the latter approach is that any grants, integrity
  constraints and secondary indices on the table are re-created too.
  NOTE that views ARE NOT re-created.  If the deleted table is involved
  in any views the views are automatically and silently destroyed when the
  table is destroyed.  They must be identified and preserved manually
  before starting.

  The third, and probably best, approach is to use the `schemadef'
  utility in $II_SYSTEM/ingres/sig.  However this utility is shipped as
  source and requires ABF and ESQL/C to compile it.  See the READ.ME file
  in the sig directory for more details.

  08.008 How can I grant other people access to my tables?

  INGRES releases prior to and including 6.4 do not allow users other
  than the database DBA (usually the user `ingres') to grant access
  to tables.  Only tables owned by the DBA may have permissions
  granted on them.  Tables created by ordinary users are private for
  all time.

  If you want to make a private table accessible, the only remedy
  presently available is to unload the table and reload it logged in as
  the DBA.  The following procedure will serve:

  1. login in as tha DBA for the database (usually ingres)
  2. copydb -u<user_id> <db_name> <table_name>
  3. sql -u<user_id> <db_name> < copy.out
  4. sql <db_name> < copy.in
  5. go into isql and grant permissions as required

  ******************************* NB *********************************
  This procedure will NOT copy any associated views, integrity
  constraints, or rules.  If you intend to destroy the original table,
  BE SURE to preserve/copy the views etc. first.  Destroying a table
  silently destroys all the associated views etc.
  ********************************************************************

  INGRES 6.5 will make it possible for users to grant permissions on their
  tables and for other users to address those tables as
  `owner_name.table_name'

  08.009 How can I change the ownership of a table/report/form?

  At present the only way to change the ownership of INGRES
  objects is to copy them out of the database with the appropriate
  tool and then reload them.  Generally this question arises when a
  private object must be shared, therefore the procedure described
  here is to change the owner to the DBA so that the object can
  be visible to everyone.

  Changing the ownership of tables is covered in section 08.008 above.

  An analogous procedure is used for forms.  

  1. Log in as the DBA (usually `ingres')
  2. copyform -u<user_id> <db_name> <file> <form_name>
  2. copyform -i <db_name> <file>

  And for reports:

  1. Log in as the DBA
  2. copyrep -u<user_id> <db_name> <file> <report_name>
  3. sreport <db_name> <file>

  08.010 What is a QEP and how do I interpret it?

  A QEP is a "query execution plan" and it the algorithm that the
  INGRES optimizer generates for satisfying a "query."  In this context
  a query is anything that requires locating rows the database, whether it
  be to select, update, or delete them.  

  The term is also used to refer to the displayed representation of
  the query plan produced when the `set qep' command has been given.
  A thorough explanation of how to interpret a QEP is beyond the scope
  of the FAQ.  Note US-38697 supplied by The ASK Group in the
  $II_SYSTEM/ingres/advisor directory provides an explanation (formerly
  r6004.dbms in $II_SYSTEM/ingres/notes).  This document is also
  available from INGRES/Advisor.

  A careful study of the QEP is a good way to identify problems in a
  badly behaved query.  Tech support may ask for a QEP to help isolate
  a problem.

  08.011 How can I ask for the first N rows?

  It can't be done.  The request is meaningless within the relational
  model.  All operations in a relational database are described in terms
  of mathematical set operations.  Sets do not incorporate the notion of
  ordering.

  What is meant by the "first" N rows?  Are these the first N rows
  according to the order they were entered?  Or the order in which they
  appear in the file where the base table resides?  (What happens when
  the table is re-organized?)  Or are the rows to be ordered according to
  some explicit sort key in the data?

  Only the latter ordering makes any sense in a relational database.
  However, to ask for anything less than all the rows described by
  the search predicate violates the relational principle of atomicity,
  which has it that all operations are executed completely or not at
  all.

  Extracting a subset of data at random, ie without an EXPLICIT
  search predicate and an EXPLICIT ordering, is very bad practice and
  is a sure sign that you are up to no good or that you are about to
  find out the hard way why the model includes the requirement for
  atomicity.

  If it is not possible to contrive a suitable restriction (search
  predicate) then one may have to suspect that the data model OR THE
  INTENTION is defective.

  Unfortunately, embedded SQL does provide a means of violating
  atomicity using cursors.  Cursors return rows one at a time and they
  can be closed at any time.  In a select loop atomicity can be violated
  by issuing exec sql endselect.  There are no doubt any number of
  pragmatic reasons for wanting to do this in an application, but
  careful reflection on the possible resulting modes of failure is
  advisable.

  You can't use the resource limiter to select just the first N rows.
  The resource limiter is pre-emptive.  If it thinks you are going to
  get more than the allowed number of rows you get no rows at all.

  08.012 Can I override the optimizer with my own execution plan?

  No.  The programmer will almost certainly not do a better job than the
  INGRES optimizer can, and even if he could, changes to the table
  location, structure or key distribution would ruin any execution plan
  the programmer dreamed up.

  If all the tables are properly organized, properly indexed and have
  little or no overflow, and if a query is not performing well, study it
  carefully.  Poor performance is almost always a result of one of two
  possibilities: there is an error in the WHERE clause--usually failing
  to make a necessary equi-join, or the search is intrinsically slow.

  In some cases generating statistics on the distribution of key (and
  non-key) values using optimizedb can make a marked improvement.
  (It may be well to do a sysmod after collecting statistics.  That will
  keep the statistics table in tip-top shape.)

  Other reasons for poor performance are: (1) one or more tables has
  no keys defined; (2) no defined key is being involved, or (3) the
  table has a lot of overflow pages.  Problem 1 is fixed by modifying
  the table to a suitable structure with a suitable key or keys.
  Problem 2 is solved by defining a suitable secondary index on the
  table.  The index should be defined with an appropriate structure too.
  The default is to organize it as a B-tree.  (You can define any
  number of secondary indices on a table, but because they are maintained
  dynamically, having too many can affect the performance of OLTP update
  and insert operations.)  Problem 3 is corrected by periodically
  modifying the table to its nominal structure.  Hash tables and ISAM
  tables are especially vulnerable to overflow problems in a busy
  database.

  Having said all this, the optimizer is the most arcane and complex
  part of a relational database engine--which is why so few even bother,
  and it is not surprising that it does VERY infrequently goof.  See
  the notes "How can I tell why my search is so slow" for ideas on
  determining if INGRES really has blundered.

  08.013 How can I tell why my search is taking so long?

  If the search has been used in production for some time, ie it is not
  under development, and there is every reason to expect that it SHOULD
  execute rapidly, begin by suspecting one of the tables is locked.  The
  INGRES ipm monitor will help to identify these kinds of access
  contention problems.

  If the search is under development, and it is not known for sure
  that it should execute quickly, the first step must always be to
  examine the WHERE clause to see if a missing condition is causing
  INGRES to generate a Cartesian product instead of a much more
  restricted equi-join.  An unqualified join on a table of M rows
  with one of N rows contains M*N rows.  Even quite modest tables
  will have products with MILLIONS of rows.

  Check that the tables are indexed appropriately for the search.
  It may be necessary to introduce a secondary index to prevent
  INGRES from scanning a table exhaustively when no keys are
  involved.  The 'help table <table_name>' command in isql will
  report the keys on the base table and the existence of any
  secondary indices.

  Be aware that searches for non-existence are almost always slow
  because they almost always require an exhaustive search.  You
  can't (in general) know it's not there till you've looked everywhere.

  Check for an excessive number of overflow pages on the tables in
  use.  Just how many pages is too many must be determined by
  experience, but overflow pages are scanned sequentially regardless
  of the nominal table structure and the mere existence of overflow
  pages requires that they be scanned.  The 'help table <table_name>'
  will report overflow pages.  Modify the table to its nominal
  structure to eliminate overflow pages.  For example if the parts table
  is nominally a hash table keyed on part_nr, then

    modify parts to hash on part_nr

  will restore it to optimal condition.  

  If none of these measures identify a significant problem, INGRES
  can be asked to dump its query execution plan (QEP) for inspection.
  This is done by issuing the 'set qep' command.  INGRES will execute
  the search as usual, and then print out the plan it used.  See
  advisor note US-38697 for details of interpreting QEPs.  If the search
  is taking too long for this to be feasible, then the execution
  phase can be suppressed by issuing the 'set optimizeonly' command
  as well.

--
M Darrin Chaney, Senior Database Programmer, University Computing Services, IU
mdcha...@indiana.edu             1000 E 17th St.           Work: (812)855-5492
mdcha...@iubacs.bitnet           Bloomington, IN 47408     Home: (812)333-6311
"I want- I need- to live, to see it all..."

 
 
 

1. Ingres Faq and Ingres and TCL

Where is the latest location of the Ingres FAQ ?

Is there anyone with some experience with Ingres in Linux and TCL Front ends?

thanks

 Pedro

2. Transaction Logs

3. Ingres FAQ update

4. SSPI Context Error

5. INGRES FAQ 1.71 available

6. My SQL Question

7. Where to find the Ingres FAQ

8. Private dimension data source

9. Where to find the INGRES FAQ

10. INGRES FAQ ????????

11. ingres faq which version ??

12. ingres faq

13. Where to find the INGRES FAQ