08-12-2012, 03:00 PM
Oracle SQL: 101 Frequently Asked Questions
Oracle SQL.pdf (Size: 5.08 MB / Downloads: 48)
Preliminaries
Before you can actually write ad hoc queries, there are some preliminaries
that must receive attention. This chapter discusses the most frequently
asked questions about issues that occur prior to actual query writing.
Query writers experienced with a specific database bring a wealth of
information to bear on an ad hoc query. This information accrues over
time in the form of database documentation, assembly of procedural
manuals and institutional data standards, discussions with data entry staff
and business policy makers, and much play and experimentation with the
data. For the most part, a query writer does this on his own.
However, a formal part of the information gathering includes assembly
of a set of tools that every query writer should own. This chapter begins
by discussing the tools that will help you explore a database —
simple queries that produce reports on table owners, table descriptions,
data column dictionaries, index inventories, and constraint conditions.
You’ll refer to these reports frequently as you write queries.
Exploring the Database
Whether you’re new to a database, to a functional area within the database,
to a data table, or even to a data column, you need to explore, play
with, and learn about the unknown.
This section provides several tools that make the exploration easier.
It includes reports on owners, tables, data dictionaries, indexes, and constraints
— all items essential for writing ad hoc queries.
Approach
Understanding a database is a bit like peeling an onion. Aside from the
tears that well up in your eyes occasionally, each time you peel back a database
layer there’s another layer underneath.
Data in Oracle databases is stored in tables, and tables have owners.
Lists of owners and tables serve as helpful guides to the database landscape
— a way of peeling back that first layer.
Owners
Run the program owners.sql listed in Appendix A (page 319). This produces
a frequency distribution that counts the number of tables by owner
and sketches the database at a high level of aggregation (see Figure 1-1).
In database systems comprised of modules such as a finance module or a
human resource module, table owners often distinguish the modular
areas. The number of tables provides a rough indication of complexity.
Approach
Lists of owners and tables won’t take you very far when you write ad hoc
queries. You’ll quickly need to identify what data columns exist in the tables
you intend to use and determine what data are stored in these columns.
A data dictionary gives you a place to begin this exploration. It
defines the data columns in a table and provides a brief description of the
data each column contains.
You’re likely to find, however, that a complete understanding of a
data column requires that you also consult office procedural manuals,
written or de facto input and maintenance standards, users familiar with
the data entry, or programmers who actually use the data column.
Dictionary
Run the program tabldict.sql listed in Appendix A (page 322). The sql
prompts you for a table name and a table owner and produces the type of
report shown in Figure 1-3.