Re: Can I do this strictly in SQL*Plus?

RK schrieb:
> Sorry, I should have given more conditions.
>
> Please try under such conditions:
>
> table1 and table2 are all very big tables, I doubt the UNION could do
> it, and do not bother to think about select in select, please;
>
> only 2 select, I assume one on each table, may be allowed or say,
> possibly executed in good time frame;
>
> please output like my format:
>
> Total: the-sum-number
> Count1: r1
> Count2: r2
>
> I do not know if a variable can take the result from the count, if
> someone help me that way, it may be better.
>
> Thanks.
>
>
>
>
>
>
>
> Maxim Demenko wrote:
>

>>RK schrieb:
>>
>>>Can someone help me, please? -- I want to do the following steps, and
>>>output strictly in SQL*Plus only:
>>>
>>>-1- select count(*) from table1;
>>>-2- select count(*) from table2;
>>>
>>>Assume the results are r1 and r2, two numbers, and I want the output to
>>>be like:
>>>
>>>Total: r1 + r2
>>>Count1: r1
>>>Count2: r2
>>>
>>>Can I do this strictly in SQL*Plus?
>>>
>>>Thanks.
>>>
>>
>>There are many ways,
>>for example
>>SQL> select sum(cnt),nvl(tname,'total')
>>   2  from (select count(*) cnt,'user_tables' tname from user_tables
>>   3  union all select count(*) cnt,'user_indexes' tname from user_indexes)
>>   4  group by rollup(tname)
>>   5  /
>>
>>   SUM(CNT) NVL(TNAME,'TOTAL')
>>---------- ------------------------------------
>>        348 user_indexes
>>        344 user_tables
>>        692 total
>>
>>SQL>
>>
>>Best regards
>>
>>Maxim

I am afraid that you don't clearly understand what you writing about. As you need count(*) from both tables , both tables need be accessed at least one time (in your case one time is also fully sufficient ). If you would explain the statement i posted above ( for simplicity let operate on the tables, not on the views ), you would see:

SQL> create table user_table as select * from all_tables;

Tabelle wurde angelegt.

SQL> create table user_index as select * from all_indexes;

Tabelle wurde angelegt.

SQL> set autotrace on explain
SQL> select sum(cnt),nvl(tname,'total')

   2 from (select count(*) cnt,'user_tables' tname from user_table    3 union all select count(*) cnt,'user_indexes' tname from user_index)    4 group by rollup(tname)
   5 /

   SUM(CNT) NVL(TNAME,'TOTAL')

---------- ------------------------------------
       1184 user_indexes
       1050 user_tables
       2234 total

Ausführungsplan

    0 SELECT STATEMENT Optimizer=CHOOSE     1 0 SORT (GROUP BY ROLLUP)

    2    1     VIEW
    3    2       UNION-ALL
    4    3         SORT (AGGREGATE)
    5    4           TABLE ACCESS (FULL) OF 'USER_TABLE'
    6    3         SORT (AGGREGATE)
    7    6           TABLE ACCESS (FULL) OF 'USER_INDEX'

SQL>  From that you could see, that both tables are accessed only one time. They are not accessed in parallel. They are accessed one after another. That way you would it do by selecting 1st count manually, storing it in the variable, then 2nd one , storing that in a variable and then outputting in a desired format. Of course that can be put into one procedure. And this one into a package. And the package can be called from external call. Or not ( if you prefer to do all within the sqlplus ). But to answer on your question - yes. Result of a select count can be a variable.
You can start reading here
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b12170/ch13.htm#i2699801

Best regards

Maxim Received on Tue Jun 07 2005 - 09:42:22 CDT