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?

75 rows will be returned. An explain plan will show this as a cartisean join.

>
> 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 are not always slower than tables but they can be. Normally, Oracle can merge the view with the SQL and come up the the correct execution plan. One example would be a simple view where the view accesses one table and does not have a group by clause. There is no difference between quering the view and the table in this case. If the view accessed two tables but your query only needed data from one, the view would be slower becuase the join would still happen. Also, if there is a group by clause in either case the optimizer will not be able to merge the SQL and the view. This could result in a less than optimal plan.

>
> What are the pitfalls of using sequences in a multi - user environment?
>

None that I am aware of.

> Is a SQL query (I.e., SELECT * FROM MYTABLE WHERE MYCOLUMN > 10000;)
> generally referred to as a query, statement or transaction?
>

It depends on who you are talking to.

> If the system.dual table stores the date, etc., why can't I see
> anything when I select * from dual;?
>

Dual is a psuedo table. There is really noting stored in it. It only exists because your must select from a table or view. Just like the v$ and x$ tables... they don't really exist.

> 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?
>

DBA is just a role that has privileges assigned to it. Since roles can be assigned table privs, system privs, and other roles you should check dba_sys_privs, dba_role_privs, and dba_tab_privs to see what is actually granted by the dba role.

> 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?
>

Index information is stored in dba_indexes and dba_ind_columns (or these are two views that allow access to that infomation). The optimizer will evaluate this information to decide whither or not to use an index in your query.

BTree indexes (normal indexes for Oracle) are sorted in ascending order in a btree format with doubly linked leaf nodes (I am assuming you are familiar with the btree format). The data stored includes the rowid of the table along with the index value (ie the columns that you are indexing in this index). Every row in the table in which all of the column values are not null are found in the index.

Bitmapped indexes store each distinct value of the index with a bitmap representation of the rows that contain them. If there are only three distinct values, there will be only 3 index entries.

> Thanks very much for taking the time to read these messages.
>
> Best wishes
>
> B. Pinto
  Received on Tue Mar 03 1998 - 00:00:00 CST