| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: count(*) and nested tables
On Sat, 06 Dec 1997 11:44:11 -0600, Brent Collins <brent_at_ti.com> wrote:
>Is there a simple SQL query to count the total number of rows in
>a nested table?
>
>My table was set up as follows.
>
>create or replace type tsd_type as object (
> time_msec NUMBER,
> value NUMBER);
>
>create type tsd_pair as table of tsd_type;
>
>create table tsd_nested (
> timestamp_date DATE,
> tsds tsd_pair)
> NESTED TABLE tsds STORE AS tsdsnpair;
>
>I would like to know how many total rows are in tsds.
>Also, can you specify different storage parameters for the nested
>table and the parent table?
>
>Regards,
>Brent Collins
>Texas Instruments
>IT Services
In answer to "is there a simple sql query ..." the answer is yes, if the object type you created supports it. I changed your example from above to support counting the total number of rows in the nested table. I had to create another type in order to do it.
The reason you have to build in support for this is because nested tables are supposed to be tables that you ONLY query via the table they are nested in (eg: you never select against the entire nested table, the nested table isn't "important" enough to standalone, the information in the nested table only makes sense when used with its parent data). If you are frequently analyzing data in the nested table across rows of the parent table (reviewing the nested table without using the parent table) then it shouldn't be a nested table but a real parent/child table with application defined foreign keys.
btw- if you just need to know the number of rows right now and this isn't something your application needs to know on an ongoing basis, then you can just analyze the table and select out NUM_ROWS from the data dictionary (i demonstrate that below as well).
As for the storage parameters. the Nested Table will always pick up the default storage characteristics for the tablespace the parent table is created in (a nested table will always be in the same tablespace as the parent). You can override the default storage for the parent table but not the nested table. You can alter the nested tables storage (next, pctincrease, and so on) after the table is created. I demonstrate that below as well. The query against user_segments will show us that the parent tables initial storage is different from the nested tables initial storage (the parent got its storage from the storage clause, the nested table from the tablespaces default storage clause). We also see that we can have different NEXT and PCTINCREASES for nested tables and alter them at will afterwards.
And.... just in case you didn't read it in the application developers guide, you migh consider indexing the NESTED_TABLE_ID column in your nested table as it gets larger and larger so that it performs well in the long run.
create or replace type tsd_pair as object (
time_msec NUMBER,
value NUMBER);
/
create type tsd_pair_set as table of tsd_pair;
/
create or replace type tsd_type as object
(
timestamp_date date,
tsds tsd_pair_set,
member function cnt return number,
pragma restrict_references( cnt, wnds, rnds, wnps, rnps )
);
/
create or replace type body tsd_type
as
member function cnt return number is
begin
return tsds.count;
end cnt;
end;
/
create table tsd_nested ( t tsd_type )
storage ( initial 1m next 1m pctincrease 0 )
NESTED TABLE t.tsds STORE AS tsdsnpair;
alter table tsd_nested storage ( next 5m ); alter table tsdsnpair storage ( next 3m );
select segment_name, initial_extent, next_extent, pct_increase from user_segments where segment_name like 'TSD%';
REM Insert some dummy data into our table...
insert into tsd_nested
select tsd_type(sysdate,
cast( multiset( select hsecs, user_id from all_users, v$timer) as tsd_pair_set))
from dual;
insert into tsd_nested
select tsd_type(sysdate,
cast( multiset( select hsecs, user_id from all_users, v$timer) as tsd_pair_set))
from dual;
REM counts the total number of rows in the nested table REM AND the total number of rows in the parent table....
select sum( A.t.cnt() ), count(*) from tsd_nested A;
analyze table tsdsnpair compute statistics; select num_rows from user_all_tables where table_name = 'TSDSNPAIR';
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Dec 07 1997 - 00:00:00 CST
 |
 |