Tuning a Parent-Child Table in Oracle Server 9.2.0.3.0

From: Leo J. Hart IV <leo.hart_at_nospam.fmr.com>
Date: Tue, 30 Mar 2004 10:16:39 -0500
Message-ID: <9cgac.472$Mn.941@news-srv1.fmr.com>

I have a table that stores a parent-child relationship:

 CREATE TABLE mor_dim_cache
  (cache_id NUMBER NOT NULL,
   member_id VARCHAR2(75) NOT NULL,
   member_desc VARCHAR2(75) NOT NULL,
   label_only VARCHAR2(1) NOT NULL,
   dim_seq_no NUMBER NOT NULL,
   parent_id VARCHAR2(75))
   TABLESPACE cfitd
/

It has a primary key:

 ALTER TABLE mor_dim_cache
 ADD CONSTRAINT mor_dim_cache_pk PRIMARY KEY (cache_id, member_id)  USING INDEX
   TABLESPACE cfitx
/

an alternate key:

 CREATE UNIQUE INDEX mor_dim_cache_ak ON mor_dim_cache    (
  cache_id ASC,
  member_id ASC,
  parent_id ASC
   )
   PCTFREE 10
   INITRANS 2
   MAXTRANS 255
   TABLESPACE cfitx
/

and three indexes:

 CREATE INDEX mor_dim_cache_prnt_cache_id_nx ON mor_dim_cache    (
  parent_id ASC,
  cache_id ASC
   )
   TABLESPACE cfitx
/

 CREATE INDEX mor_dim_cache_mem_id_desc_nx ON mor_dim_cache    (
  member_id ASC,
  member_desc ASC
   )
   TABLESPACE cfitx
/

 CREATE INDEX mor_dim_cache_cache_id_nx ON mor_dim_cache    (
  cache_id ASC
   )
   TABLESPACE cfitx
/

The table currently has 148,874 rows in it and has been analyzed completely. The member_id and parent_id columns make up the child and parent respectively, so

there will be a fair amount of uniquness there. The cache_id column currently only has 33 distinct values in the table (though this will grow with time).

A typical example of the type of query run on this table is:

 SELECT member_id AS ID,

      LEVEL,
      member_desc AS label

 FROM mor_dim_cache
 START WITH cache_id = 5
 AND parent_id = 'Organization'
 CONNECT BY PRIOR member_id = parent_id
 AND PRIOR cache_id = cache_id
/

Mind you, this typically will be inside a stored procedure and will use bind variables for efficiency. However, this simple query takes about 10 seconds to

return 10,990 rows. That seemed a little long to me considering there are databases out there that query MILLIONS of rows and return data in milliseconds, so

I started doing some analysis using the EXPLAIN PLAN and Quest's Xpert Tuning module. First, the explain plan:

 (1) SELECT STATEMENT CHOOSE

      Est. Rows: 1  Cost: 2
     CONNECT BY WITH FILTERING
            (4)  NESTED LOOPS
                (2)  NON-UNIQUE INDEX RANGE SCAN
DBHAB.MOR_DIM_CACHE_PRNT_CACHE_ID_NX  [Analyzed]
                     Est. Rows: 1  Cost: 1
                (3)  TABLE ACCESS BY USER ROWID DBHAB.MOR_DIM_CACHE
[Analyzed]
                (3)   Blocks: 644 Est. Rows: ?? of 148,874
                     Tablespace: CFITD
            (9)  NESTED LOOPS
                (6)  BUFFER SORT
                     Est. Rows: 1
                    (5)  CONNECT BY PUMP
                (8)  TABLE ACCESS BY INDEX ROWID DBHAB.MOR_DIM_CACHE
[Analyzed]
                (8)   Blocks: 644 Est. Rows: 1 of 148,874  Cost: 2
                     Tablespace: CFITD
                    (7)  NON-UNIQUE INDEX RANGE SCAN
DBHAB.MOR_DIM_CACHE_PRNT_CACHE_ID_NX  [Analyzed]
                         Est. Rows: 1  Cost: 1

This doesn't look too bad; it appears to be using all of the indexes I have out there (though I should probably change the mor_dim_cache_cache_id_nx index to a

bitmap index), but then I'm pretty much a novice at tuning, hence the use of Quest's software. Xpert Tuning's only suggestion at this point is that I

partition my table.

My question to you all is, is this reasonable? Do I have enough data in the table to justify partitioning for query performance and what sort of partitioning

technique should I use? Table? Index? Hash? Range? I assume that I should use some sort of hash partitioning on the cache_id column given its low degree

of cardinality (33 distinct values out of 148,871 rows).

If not, is there something else I should be doing to reduce query times?

Any help would be greatly appreciated.

Thanks,
Leo Hart Received on Tue Mar 30 2004 - 09:16:39 CST