SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Group by preserving the order

jeneeshNov 29 2006 — edited Jan 5 2011

Say for eg: my data is like this

--order by col3
col1 col2  col3
1       5     1
1      10    2
2      2      3 
2      5      4
1     15     5
I hav to get o/p as
col1  sum_col2
1       15
2       7
1      15
I have to group by preserving the order
Pls give me suggestions..

jeneesh

448389

Hi

dbms_space.create_index_cost can help you

1 person found this helpful

guruparan

I haven't created yet! But I can still run this proc? BTW, I am in Oracle 9.2

guruparan

Looks like Oracle 9.2 doesn't have it,

<pre>
SQL> desc dbms_space.create_index_cost
ERROR:
ORA-04043: object dbms_space.create_index_cost does not exist
</pre>

Mark D Powell

Answer

A rough estimate of the space the index will need can be made by

adding the expected actual length of each column plus 6 for the rowid plus 2 for the header X number of table rows that will have an entry so

Date are 7 bytes internal + 15 + 4 + 2 + 6 = 34 X 7.3M = 248200000 X overhead for block header, initrans, etc...

we will use 20% for block overhead or 1.2 X 248200000 = 297840000 which is about 285M

The actual allocation will vary depending on your tablespace extent allocation method.

HTH -- Mark D Powell --

Marked as Answer by guruparan · Sep 27 2020

1 person found this helpful

guruparan

Thanks Mark. That's about it. :)

Mark D Powell

You are welcome. Thank you for the points. If you get to build the index soon and can remember post back with the actual results.

-- mark --

448389

Yes is it to known the future size BEFORE create index by using statistics

look the Ask Tom http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1833377500346797242

for psoug this package exist since 7.3.4. I use it on 10.2.0[2-4] and 11g ({ORACLE_HOME}/rdbms/admin/dbmsspu.sql)
http://psoug.org/reference/dbms_space.html

my test also :
SQL*Plus: Release 11.1.0.6.0
SYS@SGA>create table tsygale as select level num from dual connect by level<=100000;

Table created.
SYS@SGA>@seg_tab
Enter value for segment_name: TSYGALE
old 3: where segment_name in ('&segment_name')
new 3: where segment_name in ('TSYGALE')

TABLESPACE_NAME SEGMENT_NAME MO
-------------------- ----------------------------------- ----------
SYSTEM TSYGALE 2
SYS@SGA>exec dbms_stats.gather_table_stats(user,'TSYGALE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.95

SYS@SGA>variable used number
SYS@SGA>variable alloc number
exec dbms_space.create_index_cost( 'create index isygale on tsygale(object_name,object_type,owner)', :used, :alloc );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
16:56:38 SYS@SGA>print used

USED
----------
2761720

16:56:49 SYS@SGA>print alloc

ALLOC
----------
4194304
SYS@SGA>create index isygale on tsygale(object_name,object_type,owner);

Index created.

Elapsed: 00:00:00.50

SYS@SGA>@seg_tabind
Segment name ?TSYGALE
old 3: where (segment_name in ('&&segment_name')
new 3: where (segment_name in ('TSYGALE')
old 5: segment_name in (select index_name from dba_indexes where table_name in ('&&segment_name'))
new 5: segment_name in (select index_name from dba_indexes where table_name in ('TSYGALE'))

TABLESPACE_NAME SEGMENT_TYPE SEGMENT_NAME MO
-------------------- ------------------ ----------------------------------- ----------
SYSTEM TABLE TSYGALE 8
SYSTEM INDEX ISYGALE 4

Elapsed: 00:01:22.31

So package estimate to 4 194 304 Octets and after the index created the size is to 4MO, so ....

Edited by: Sygale on Apr 8, 2010 5:02 PM

Jonathan Lewis

Sygale wrote:

for psoug this package exist since 7.3.4. I use it on 10.2.0[2-4] and 11g ({ORACLE_HOME}/rdbms/admin/dbmsspu.sql)
http://psoug.org/reference/dbms_space.html

Unfortunately the OP says he is using 9.2, and although the package has been around since 7.3 that particular procedure was not introduced until 10g - and it has a couple of flaws built in, see: http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.

There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)

+"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
Isaac Asimov

448389

Jonathan Lewis wrote:
Unfortunately the OP says he is using 9.2, and although the package has been around since 7.3 that particular procedure was not introduced until 10g - and it has a couple of flaws built in, see: http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Sorry for this mistake, have you a method to define on which version a functionality is delivered ?

I read your article, I see some errors in this package,
but it can help us to define if we have enough space on disk to create index or table in Clone environment. Before create it on production ;)

A real test is always better than an estimate test

I recommend to add 10% margin from development to production (the production database is always run ;))

Thanks

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 2 2011

Added on Nov 29 2006

9 comments

14,113 views

SQL & PL/SQL

Group by preserving the order

Comments

Post Details