| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Questions.
> Bernadette wrote:
> >
> > I'm using Personal Oracle 7 for Windows 95 (SQL Plus 3.3). I am
> > fairly new to Oracle and wonder whether anyone can answer the
> > following questions:
> >
> >
> > Why are views slower to use than tables? Also, I heard that this no
> > longer was an issue with the newest version of Oracle. Is this true?
Views can be perceptibly slower than tables because views USUALLY but
not always pull data from several tables. Oracle must perform the
appropriate joins on the tables and construct the view - a virtual table
- dynamically, that is, at the time the query against the view is
performed. Depending upon the complexity of the view and the number of
tables involved, constructing the view could be very expensive. On the
other hand, a view could be a subset of a table, presenting to the user
some but not all of the columns in the table. In this case, querying a
view should be no more expensive than querying a table. None of the
foregoing is undone by newer versions of Oracle, although they may
process views somewhat more efficiently than previous versions.
> >
> > What are the pitfalls of using sequences in a multi - user environment?
The use of sequences and the fact of a multi-user environment really do
not have much to do with each other. Sequences are Oracle objects which
generate numbers sequentially on request, via the NEXTVAL method; it is
also possible to find the value of the last sequence number generated
via the CURRVAL method. As the name implies, sequences generate
sequential integers - 1, 2, 3, 4, 5 ... - subject to some constraints
specified when the sequence is created - things like starting value and
maximum value and whether values can or cannot be generated more than
once. Sequences frequently, perhaps even usually, are used to generate
guaranteed-to-be-unique values for one or more tables' primary key
values. Sequences are user-independent objects, and if several users are
running applications that draw values from the same sequence, Oracle
will ensure that each user gets unique values. Oracle is designed as a
multi-user system; there should be no pitfalls.
> >
> >
> > If the system.dual table stores the date, etc., why can't I see
> > anything when I select * from dual;?
Dual is a small table with one column and one row, and it does not store
anything. It's function is to permit syntactic completeness for SQL
statements that do not actually query the database. For example, to find
the value of the last number generated by a sequence, the following SQL:
SELECT sSequence1.CURRVAL
FROM dual;
returns:
CURRVAL
To query the operating system's date and time, this SQL:
SELECT sysdate FROM dual;
returns
SYSDATE
SELECT customer, connect_time FROM usage WHERE customer = ( SELECT user FROM dual); returns CUSTOMER CONNECT_TIME ------------------------ ------------ SCOTT 18:36:21
These statements will not work with a table other than dual; this SQL:
SELECT user FROM usage;
returns
no rows selected.
> >
- Mark
 |
 |