-
Cluster Index
Does anybody know how to solve the following problem:
I have a tablespace - 50000MB and 2 empty tables. I have created the cluster key and cluster index on these tables. Right after that I was trying to perform an Insert as Select stmt. on one of those tables to insert 1600MB of data. This Insert stmt, for some reason, takes the whole space of the tablespace and because of that insert was failed.
If you have any idea or experience with the similar type of issue, please respond.
Thank you, I would really appreciate your help.
-
Avoid using index cluster.
You should not insert all the rows into one table , then insert all the rows into another table. Some time that will blow out ur tablespace. Also, you must calculate right size for the key.
To load data into 2 tables in a index cluster, follow the logic:
Code:
Loop
get one row and key from parent table
insert into parent table
insert into child table for the key
End loop
Tamil
-
Why to avoid index cluster?
How to calculate right size for the key?
-
=======
Why to avoid index cluster?
How to calculate right size for the key?
===============
There are many pitfalls in using index cluster:
1 Calculating cluster size is very difficult.
My assumption is after loading data only, we can calculate the size.
SQL is given below.
2 Truncate command can not be used on the table which is in a cluster.
3 Index cluster is suitable for only static data.
4 A lot of chaining will occur if the cluster size is set to low.
5 When the cluster size is set to high, space is wasted.
Avoid using index cluster. No big application uses index cluster.
How to find out right cluster size:
First load the data as I said above.
Find out the data object id for the cluster/table from dba_objects.
Run the SQL as sys given below after substituting the data object id:
Code:
select
'C_OBJ#' "CLUSTER",
round(
avg(
ts.bytes +
tcs.bytes +
nvl(xs.bytes, 0) +
nvl(xcs.bytes, 0)
) +
stddev(
ts.bytes +
tcs.bytes +
nvl(xs.bytes, 0) +
nvl(xcs.bytes, 0)
)
) "MIN SIZE"
from
( select
t.OBJ#,
37 +
vsize(nvl(t.OBJ#, 0)) +
vsize(nvl(t.DATAOBJ#, 0)) +
vsize(nvl(t.TS#, 0)) +
vsize(nvl(t.FILE#, 0)) +
vsize(nvl(t.BLOCK#, 0)) +
vsize(nvl(t.BOBJ#, 0)) +
vsize(nvl(t.TAB#, 0)) +
vsize(nvl(t.COLS, 0)) +
vsize(nvl(t.CLUCOLS, 0)) +
vsize(nvl(t.PCTFREE$, 0)) +
vsize(nvl(t.PCTUSED$, 0)) +
vsize(nvl(t.INITRANS, 0)) +
vsize(nvl(t.MAXTRANS, 0)) +
vsize(nvl(t.FLAGS, 0)) +
vsize(nvl(t.AUDIT$, 0)) +
vsize(nvl(t.ROWCNT, 0)) +
vsize(nvl(t.BLKCNT, 0)) +
vsize(nvl(t.EMPCNT, 0)) +
vsize(nvl(t.AVGSPC, 0)) +
vsize(nvl(t.CHNCNT, 0)) +
vsize(nvl(t.AVGRLN, 0)) +
vsize(nvl(t.AVGSPC_FLB, 0)) +
vsize(nvl(t.FLBCNT, 0)) +
vsize(nvl(t.ANALYZETIME, sysdate)) +
vsize(nvl(t.SAMPLESIZE, 0)) +
vsize(nvl(t.DEGREE, 0)) +
vsize(nvl(t.INSTANCES, 0)) +
vsize(nvl(t.INTCOLS, 0)) +
vsize(nvl(t.KERNELCOLS, 0)) +
vsize(nvl(t.PROPERTY, 0)) +
vsize(nvl(t.TRIGFLAG, 0)) +
vsize(nvl(t.SPARE1, 0)) +
vsize(nvl(t.SPARE2, 0)) +
vsize(nvl(t.SPARE3, 0)) +
vsize(nvl(t.SPARE4, 0)) +
vsize(nvl(t.SPARE5, 0)) +
vsize(nvl(t.SPARE6, sysdate)) bytes
from
sys.tab$ t
where t.DATAOBJ# = xxxxxxxxx
) ts,
( select
tc.OBJ#,
sum(
23 +
vsize(nvl(tc.COL#, 0)) +
vsize(nvl(tc.SEGCOL#, 0)) +
vsize(nvl(tc.SEGCOLLENGTH, 0)) +
vsize(nvl(tc.OFFSET, 0)) +
vsize(nvl(tc.NAME, 0)) +
vsize(nvl(tc.TYPE#, 0)) +
vsize(nvl(tc.LENGTH, 0)) +
vsize(nvl(tc.FIXEDSTORAGE, 0)) +
vsize(nvl(tc.PRECISION#, 0)) +
vsize(nvl(tc.SCALE, 0)) +
vsize(nvl(tc.NULL$, 0)) +
vsize(nvl(tc.DEFLENGTH, 0)) +
1 +
vsize(nvl(tc.INTCOL#, 0)) +
vsize(nvl(tc.PROPERTY, 0)) +
vsize(nvl(tc.CHARSETID, 0)) +
vsize(nvl(tc.CHARSETFORM, 0)) +
vsize(nvl(tc.SPARE1, 0)) +
vsize(nvl(tc.SPARE2, 0)) +
vsize(nvl(tc.SPARE3, 0)) +
vsize(nvl(tc.SPARE4, 0)) +
vsize(nvl(tc.SPARE5, 0)) +
vsize(nvl(tc.SPARE6, sysdate))
) bytes
from
sys.col$ tc
group by
tc.OBJ#
) tcs,
( select
i.BO#,
sum(
34 +
vsize(nvl(i.OBJ#, 0)) +
vsize(nvl(i.DATAOBJ#, 0)) +
vsize(nvl(i.TS#, 0)) +
vsize(nvl(i.FILE#, 0)) +
vsize(nvl(i.BLOCK#, 0)) +
vsize(nvl(i.BO#, 0)) +
vsize(nvl(i.INDMETHOD#, 0)) +
vsize(nvl(i.COLS, 0)) +
vsize(nvl(i.PCTFREE$, 0)) +
vsize(nvl(i.INITRANS, 0)) +
vsize(nvl(i.MAXTRANS, 0)) +
vsize(nvl(i.PCTTHRES$, 0)) +
vsize(nvl(i.TYPE#, 0)) +
vsize(nvl(i.FLAGS, 0)) +
vsize(nvl(i.PROPERTY, 0)) +
vsize(nvl(i.BLEVEL, 0)) +
vsize(nvl(i.LEAFCNT, 0)) +
vsize(nvl(i.DISTKEY, 0)) +
vsize(nvl(i.LBLKKEY, 0)) +
vsize(nvl(i.DBLKKEY, 0)) +
vsize(nvl(i.CLUFAC, 0)) +
vsize(nvl(i.ANALYZETIME, sysdate)) +
vsize(nvl(i.SAMPLESIZE, 0)) +
vsize(nvl(i.ROWCNT, 0)) +
vsize(nvl(i.INTCOLS, 0)) +
vsize(nvl(i.DEGREE, 0)) +
vsize(nvl(i.INSTANCES, 0)) +
vsize(nvl(i.TRUNCCNT, 0)) +
vsize(nvl(i.SPARE1, 0)) +
vsize(nvl(i.SPARE2, 0)) +
vsize(nvl(i.SPARE3, 0)) +
vsize(nvl(i.SPARE4, 0)) +
vsize(nvl(i.SPARE5, 0)) +
vsize(nvl(i.SPARE6, sysdate))
) bytes
from
sys.ind$ i
group by
i.BO#
) xs,
( select
ic.BO#,
sum(
13 +
vsize(nvl(ic.OBJ#, 0)) +
vsize(nvl(ic.COL#, 0)) +
vsize(nvl(ic.POS#, 0)) +
vsize(nvl(ic.SEGCOL#, 0)) +
vsize(nvl(ic.SEGCOLLENGTH, 0)) +
vsize(nvl(ic.OFFSET, 0)) +
vsize(nvl(ic.INTCOL#, 0)) +
vsize(nvl(ic.SPARE1, 0)) +
vsize(nvl(ic.SPARE2, 0)) +
vsize(nvl(ic.SPARE3, 0)) +
vsize(nvl(ic.SPARE4, 0)) +
vsize(nvl(ic.SPARE5, 0)) +
vsize(nvl(ic.SPARE6, sysdate))
) bytes
from
sys.icol$ ic
group by
ic.BO#
) xcs
where
tcs.OBJ# = ts.OBJ# and
xs.BO# (+) = ts.OBJ# and
xcs.BO# (+) = ts.OBJ#
union all
select
'C_FILE#_BLOCK#' "CLUSTER",
round(avg(ss.bytes + us.bytes) +
stddev(ss.bytes + us.bytes))
"MIN SIZE" from
( select
s.TS#,
s.FILE#,
s.BLOCK#,
20 +
vsize(nvl(s.FILE#, 0)) +
vsize(nvl(s.BLOCK#, 0)) +
vsize(nvl(s.TYPE#, 0)) +
vsize(nvl(s.TS#, 0)) +
vsize(nvl(s.BLOCKS, 0)) +
vsize(nvl(s.EXTENTS, 0)) +
vsize(nvl(s.INIEXTS, 0)) +
vsize(nvl(s.MINEXTS, 0)) +
vsize(nvl(s.MAXEXTS, 0)) +
vsize(nvl(s.EXTSIZE, 0)) +
vsize(nvl(s.EXTPCT, 0)) +
vsize(nvl(s.USER#, 0)) +
vsize(nvl(s.LISTS, 0)) +
vsize(nvl(s.GROUPS, 0)) +
vsize(nvl(s.BITMAPRANGES, 0)) +
vsize(nvl(s.CACHEHINT, 0)) +
vsize(nvl(s.SCANHINT, 0)) +
vsize(nvl(s.HWMINCR, 0)) +
vsize(nvl(s.SPARE1, 0)) +
vsize(nvl(s.SPARE2, 0)) bytes
from
sys.seg$ s
) ss,
( select
u.TS#,
u.SEGFILE#,
u.SEGBLOCK#,
sum(
4 +
vsize(nvl(u.EXT#, 0)) +
vsize(nvl(u.FILE#, 0)) +
vsize(nvl(u.BLOCK#, 0)) +
vsize(nvl(u.LENGTH, 0))
) bytes
from
sys.uet$ u
group by
u.TS#,
u.SEGFILE#,
u.SEGBLOCK#
) us
where
ss.TS# = us.TS# and
ss.FILE# = us.SEGFILE# and
ss.BLOCK# = us.SEGBLOCK#
/
Once you get the value, drop cluster and recreate it again.
Tamil
-
Thank you. I will try that.
-
Has anybody used Index cluster?
Tamil