Re: What's wrong with this sp?

I'm running Oracle 8 Personal Edition on Windows 2000 with 120 MG RAM, many services are running which sure take lots of memory. What could possibly cause the slowness of the query on view? Thanks.

DL

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:<huyI7.26286$XJ4.16197488_at_news1.sttln1.wa.home.com>...
> Actually these are not tables but views.
> The query should be fast. Also most likely in production this data is
> cached in memory.
> Jim
> "DL" <donlcs_at_yahoo.com> wrote in message
> news:604bb90c.0111140946.4e2abdf5_at_posting.google.com...
> > Well, I commented out cols.column_name column by adding "--" before
> > it, I bet
> > "--" is also a comment sign for Oracle. All right, what I really want
> > to do in this case is to improve query efficiency (it involves three
> > system tables), and I now found out that none of these tables
> > (dba_constraints, dba_cons_columns, dba_tab_columns) have any indexes,
> > without index query is sure slow, so, now, the issue is, first to
> > check if there's index for each table (for its relevant column), if
> > not, create an index for it. What do you think? Thanks.
> >
> > DL
> >
> > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<1cfI7.22711$XJ4.14082736_at_news1.sttln1.wa.home.com>...
> > > I don't have a database to try this on. But possibly the error is at
> > > > select c.nullable --, cols.column_name
> > >
> > > the -- is a comment in pl/sql.
> > >
> > > before defining the package do
> > > set serverout on
> > > define the package
> > > then
> > > show errors
> > >
> > > It should show the line and column where the problem is occuring.
> > > My guess is some sort of syntax error.
> > > Jim
> > >
> > > "DL" <donlcs_at_yahoo.com> wrote in message
> > > news:604bb90c.0111131052.73e0fc0_at_posting.google.com...
> > > > Hi,
> > > >
> > > > The doc is very limited in explaining the usage. I grab the concept
> > > > and turn it into the following code, running on PL*SQL 8.1.7 received
> > > > err:
> > > > package created with compilation errors:
> > > >
> > > > Create or replace package Pak
> > > > type PakType IS ref cursor;
> > > > Create or replace procedure cardi (ow IN varchar2, tbn IN varchar2,
> > > > cnYN OUT Pak.PakType)
> > > > IS
> > > > Begin
> > > > open cnYN for
> > > > select c.nullable --, cols.column_name
> > > > from dba_constraints cons, dba_cons_columns cols, dba_tab_columns
> > > > c
> > > > where cons.table_name = cols.table_name
> > > > and cons.owner = own
> > > > and c.table_name=tbn
> > > > and cols.column_name = c.column_name
> > > > and cons.constraint_name = cols.constraint_name
> > > > and cons.constraint_type = 'R'
> > > > and cons.table_name =tbn
> > > > End
> > > >
> > > >
> > > > Thanks.
> > > >
> > > > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<K%1I7.19325$XJ4.12861627_at_news1.sttln1.wa.home.com>...
> > > > > The ref cursor documentation is in the Oracle docs you got with the
> > > > > software. (in html). Look in the Application Developers guide
> PL/SQL.
> The
> > > > > distinct will have more effect as the number of rows grow.(in the
 result
> > > > > set)
> > > > > Jim
> > > > > "DL" <donlcs_at_yahoo.com> wrote in message
> > > > > news:604bb90c.0111122027.58c30dd6_at_posting.google.com...
> > > > > > Thanks, Jim, for throwing one stone (that almost hits two birds).
> > > > > > I'll find an Oracle book tomorrow to check on ref cursor. As for
 the
> > > > > > "distinct" function, I've commented it out and selects just one
 column
> > > > > > now, however, it has not reduced any processing time, strange.
> > > > > >
> > > > > > DL
> > > > > >
> > > > > > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<_LTH7.17193$XJ4.11730249_at_news1.sttln1.wa.home.com>...
> > > > > > > You need to read the documentation on ref cursor. You are not
> in
> sqlserver.
> > > > > > > Also if you can avoid distinct in the query - it makes the rdbms
 compare
> > > > > > > every row with every other row - very ineffecient.
> > > > > > > Jim
> > > > > > > "DL" <donlcs_at_yahoo.com> wrote in message
> > > > > > > news:604bb90c.0111120929.5612d6f8_at_posting.google.com...
> > > > > > > > Create procedure first_Oracle_sp(v_owner IN varchar2, v_tn IN
 varchar2)
> > > > > > > > AS
> > > > > > > > Begin
> > > > > > > > select distinct cols.table_name as tblname,c.nullable,
 cols.column_name
> > > > > > > > from dba_constraints cons, dba_cons_columns cols,
> dba_tab_columns
> c
> > > > > > > > where cons.table_name = cols.table_name
> > > > > > > > and cons.owner = v_owner
> > > > > > > > and c.table_name = v_tn
> > > > > > > > and cols.column_name = c.column_name
> > > > > > > > and cons.constraint_name = cols.constraint_name
> > > > > > > > and cons.constraint_type = 'R'
> > > > > > > > and cons.table_name = v_tn
> > > > > > > > End;
> > > > > > > >
> > > > > > > > Thanks.
Received on Thu Nov 15 2001 - 04:13:36 CET