partitions vs. tables + union all views

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Mon, 08 Apr 2002 22:06:48 GMT
Message-ID: <3cb211a6.1463864936@news.globix.com>

I have a table w/ 170+ million rows
out of which only 4million are really needed permanently each row is marked w/ type
i was thinking of splitting the table into 3 parts table_permananent
table_sessiondata (most of the stuff that can be purged periodically here)
table_other (some 'non-standard' types sometimes get inserted) and have each needed table populated through an instead of trigger

but I am worried about selects..
if this was a partitioned table, the optimizer would have easily identifed which partition to go for, but the explain plan of the view looks like this:

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 VIEW OF 'MY_VIEW'

   2    1     UNION-ALL
   3    2       FILTER
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'table_permanent'
   5    4           INDEX (UNIQUE SCAN) OF 'TABLE_PERMANENT_PK'


(UNIQUE)

   6    2       FILTER
   7    6         TABLE ACCESS (BY INDEX ROWID) OF 'table_sessiondata'
   8    7           INDEX (UNIQUE SCAN) OF 'TABLE_SESSIONDATA_PK'


(UNIQUE)

   9    2       TABLE ACCESS (BY INDEX ROWID) OF 'table_other'
  10    9         INDEX (UNIQUE SCAN) OF 'TABLE_OTHER_PK' (UNIQUE)

Even though 2 our of 3 'subselects' would result in nothing, would this have a drammatic impact otherwise?

I figure not, since the 'b-trees' it would have to go down on each of the queries wouldn't be as deep, plus the key is actually cut by 30%. What's the cost of looking for a key that doesn't exist anyway?

Why not have the application select from different tables you ask? I want this change to be transparent to the application

Why not use partitions you ask?
I want to continue using 'Standard Edition'

Thnx for any input.

.......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email

Received on Mon Apr 08 2002 - 17:06:48 CDT