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:
>
> If an equi-join is conducted without a where clause, why will a 15
> row table joined to a five row table return (15 x 5) 75 rows?

What you describe is not an equi-join, but a simple join. Oracle performs a join whenever multiple questions appear in the query's FROM clause, as is the case with you.
If you do not specify a join condition (where...), Oracle takes the "cartesian product" (of Descartes), which means he is combining each row with the other. This is the reason that you get such a large number of rows.
To reduce the number of rows, you have to specify join conditions.

Note: an equi-join is a join with a join condition containing an equality operator.

>
> 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?
>
> What are the pitfalls of using sequences in a multi - user environment?
>
> Is a SQL query (I.e., SELECT * FROM MYTABLE WHERE MYCOLUMN > 10000;)
> generally referred to as a query, statement or transaction?

Whatever SQL "sentence" you write is called an SQL statement.

If your statement is a SELECT statement, you can call it a query, because you are asking a question to your table. You ask it to give you these and those rows.

A transaction is a collection of sql statements, terminated either by a "commit" or "rollback".

>
> If the system.dual table stores the date, etc., why can't I see
> anything when I select * from dual;?
>
> If you grant DBA to someone, do they have all priviledges? Someone
> told me you had to also grant connect, resourse, etc... What's the
> correct answer?

I think, if you get the DBA role, you get all privileges.  

> I know how to create an index, but how do they work? Are they stored
> as part of the table or part of the data dictionary? How does Oracle
> know to refer to them?

Indexes are stored independently of the table they index. They are not stored in the data dictionary. They are objects as tables are objects.  

It is Oracle's optimizer's task to use the indexes. The optimizer looks for the best, most efficient way to execute a query.

If he decides to use the index, he scans it, finds the row-id of the corresponding table row, and directly locates the correct row.

I have not found an exact topic on how he decides to use an index or not. There are several situations where he doesn't take the index:

- when after a like you specify the wildcards "_" or "%"
- when you use a function to your indexed column (TO_NUMBER (column))
- when using NOT

> Thanks very much for taking the time to read these messages.

No problem,

Anne-Marie Ternes

anne-marie.ternes_at_cie.etat.lu Received on Thu Mar 05 1998 - 00:00:00 CST