DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Cluster Index

  1. #1
    Join Date
    Jul 2004
    Posts
    9

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Jul 2004
    Posts
    9
    Why to avoid index cluster?
    How to calculate right size for the key?

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =======
    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
    Last edited by tamilselvan; 07-22-2004 at 01:53 PM.

  5. #5
    Join Date
    Jul 2004
    Posts
    9
    Thank you. I will try that.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Has anybody used Index cluster?

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width