Re: Newbie question: SQL*Plus command to see number of query result rows

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:4053798d$0$18490$afc38c87_at_news.optusnet.com.au...
>
> "Lara" <larroyo114_at_yahoo.com> wrote in message
> news:73447077.0403130816.3bf4ae67_at_posting.google.com...
> > Hi, I have what I think is a simple SQL*Plus question, but I can't
> > find the answer in any of my books.
> >
> > I'm trying to turn off SQL*Plus displaying the result of each row in a
> > select statement, and just want to see the number of rows the select
> > statement would generate. I'm trying to see if my select statement is
> > correctly generating the correct number of rows. I know it should
> > generate 154,280 rows, but it takes too long to sit here and wait to
> > see the results if every row of the query result displays.
> >
> > In other words (in a shortened example), I currently have:
> >
> > SQL> Select student_id from student;
> >
> > STUDENT_ID
> > ----------
> > 391
> > 392
> > 393
> > 3 rows selected.
> >
> >
> > Is there any command I can use to get:
> > SQL> Select student_id from student;
> >
> > 3 rows selected.
> >
> >
> > p.s. I don't think I can use COUNT because there are multiple joins
> > and subqueries throughout the select.
> >
> > Thanks!
>
> Some surprising other answers to your perfectly reasonable request, I
note.
> So here's my effort.
>
> Yes, you can turn off the results display.
>
> First make sure you have a plan_table created. If 'desc plan_table'
produces
> the error that the table doesn't exist, then create it by running the
script
> utlxplan.sql supplied by Oracle in ORACLE_HOME/rdbms/admin.
>
> With that done, issue the following command:
>
> set autotrace trace statistics
>
> Then execute your query. You won't get the results returned, but you will
> gets the line about '176573 rows selected', and you'll also get some other
> interesting information about how much work your SQL statement is doing.
>
> As a simple example, here is my test run:
>
> SQL> desc plan_table
> ERROR:
> ORA-04043: object plan_table does not exist
>
>
> SQL> @?\rdbms\admin\utlxplan
>
> Table created.
>
> SQL> set autotrace trace statistics
> SQL> select * from emp;
>
> 14 rows selected.
>
>
> Statistics
> ----------------------------------------------------------
> 250 recursive calls
> 0 db block gets
> 42 consistent gets
> 3 physical reads
> 0 redo size
> 1313 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 4 sorts (memory)
> 0 sorts (disk)
> 14 rows processed
>
> Just one word of warning. Autotrace needs access to some V$ views. If
you're
> not sufficiently privileged, you'll get warned about this when you attempt
> to trace a SQL statement. In that case, you'll need a dba to create the
> plustrace role, using a script provided by Oracle for the purpose (called
> plustrce.sql in ORACLE_HOME/sqlplus/admin) and then grant the new
> "plustrace" role to you).
>
> Regards
> HJR
>
>
>
>

so what is wrong with select count .... which is always possible and is what the OP wanted Received on Sat Mar 13 2004 - 15:16:31 CST