Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!8g2000hse.googlegroups.com!not-for-mail From: Charles Hooper Newsgroups: comp.databases.oracle.server Subject: Re: Optimizing union based view Date: Mon, 3 Mar 2008 07:09:25 -0800 (PST) Organization: http://groups.google.com Lines: 343 Message-ID: <8127b08a-28bb-44d4-8769-2321097e1b61@8g2000hse.googlegroups.com> References: NNTP-Posting-Host: 205.208.133.102 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1204556965 24253 127.0.0.1 (3 Mar 2008 15:09:25 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 3 Mar 2008 15:09:25 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: 8g2000hse.googlegroups.com; posting-host=205.208.133.102; posting-account=xVXeFwkAAAAz3xgWc6VZyjXxx1jx4jb4 User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506),gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:442052 X-Received-Date: Mon, 03 Mar 2008 10:09:26 EST (text.usenetserver.com) On Mar 3, 8:48=A0am, Mathias Waack wrote: > Hi all, > > I'm having a performance problem with a view based on the union of two > tables: > > create table t1(c1 varchar(10), recno number); > alter table t1 add primary key (recno); > insert into t1old values ('r1', 1); > insert into t1old values ('r2', 2); > create table t1old(c1 varchar(10), recno number); > alter table t1old add primary key (recno); > insert into t1old values ('r0', 0); > create view t1view as select * from t1 union all select * from t1old; > > The application using this schema accesses records by its 'recno', so a > common query is: > > select /*+ first_rows(1) */ * from t1 where recno >=3D 1 order by recno; > > Some tables growing very fast, so I've started to expire old records into > backup tables, thats why we have t1 and t1old. Thus t1 union all t1old > gives us the original tables. > > Now starts my problem: if the application runs the same query as above on > the view: > > select /*+ first_rows(1) */ * from t1view where recno >=3D 1 order by recn= o; > > Oracle performs a full table scan over both tables after applying the > filter, thus we get something like this: > > sort > =A0 union all > =A0 =A0 select * from t1 where recno >=3D 1 > =A0 =A0 select * from t1old where recno >=3D 1. > > Usually the application fetches only one record (thats why the optimizer > hint), but in a very few cases it fetches more than one row. In the common= > case the query is very slow. I know, that > > select count(*) from t1view =3D select count(*) from t1 + select count(*) = from > t1old > > and > > select max(recno) from t1old < select min(recno) from t1 > > I think Oracle needs both information to perform better - but how to tell > it? > > Mathias Thanks for posting the DDL and DML for the test case. Since you indicated that there are many rows involved, I did not use your insert statement. Instead, I used: INSERT INTO T1OLD SELECT 'R'||TO_CHAR(ROWNUM-1), ROWNUM-1 FROM DUAL CONNECT BY LEVEL<=3D200000; INSERT INTO T1 SELECT 'R'||TO_CHAR(ROWNUM+200000-1), ROWNUM+200000-1 FROM DUAL CONNECT BY LEVEL<=3D100000; COMMIT; The above inserted 200,000 rows into T1OLD with values from 0 through 199,999, and 100,000 rows into T1 with values from 200,000 through 299,999. Now, collect table and index stats: EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>USER,TABNAME=3D>'T1',CASCADE=3D>TRU= E); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>USER,TABNAME=3D>'T1OLD',CASCADE=3D>= TRUE); Let's try to set up the query without using the view (on Oracle 11.1.0.6), specifying that RECNUM must be at least 250,000: SELECT * FROM (SELECT /*+ INDEX(T1) */ C1, RECNO FROM T1 UNION ALL SELECT /*+ INDEX(T1OLD) */ C1, RECNO FROM T1OLD ORDER BY RECNO) WHERE RECNO>=3D250000 AND ROWNUM<=3D1; ----------------------------------------------------------------------------= --------------------------------------------------------- | Id | Operation | Name | Starts | E- Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------= --------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.59 | 223 | | | | | 2 | VIEW | | 1 | 3 | 1 |00:00:00.59 | 223 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | | 1 |00:00:00.59 | 223 | 2048 | 2048 | 2048 (0)| | 4 | UNION-ALL | | 1 | | 50000 |00:00:00.50 | 223 | | | | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50000 | 50000 |00:00:00.20 | 221 | | | | |* 6 | INDEX RANGE SCAN | SYS_C0013568 | 1 | 50000 | 50000 |00:00:00.05 | 96 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| T1OLD | 1 | 1 | 0 |00:00:00.01 | 2 | | | | |* 8 | INDEX RANGE SCAN | SYS_C0013569 | 1 | 1 | 0 |00:00:00.01 | 2 | | | | ----------------------------------------------------------------------------= --------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=3D1) 3 - filter(ROWNUM<=3D1) 6 - access("RECNO">=3D250000) 8 - access("RECNO">=3D250000) Note that in the above, I am controlling the number of rows returned by the ROWNUM<=3D1 clause, and Oracle is pushing that restriction into the two halves of the UNION ALL, and it is also pushing in the RECNO restriction into the two halves of the UNION ALL. Trying again, this time attempting to force Oracle to recognize the MIN/MAX relationship: SELECT * FROM (SELECT /*+ INDEX(T1) */ C1, RECNO FROM T1 WHERE RECNO>=3D( SELECT MIN(RECNO) FROM T1) UNION ALL SELECT /*+ INDEX(T1OLD) */ C1, RECNO FROM T1OLD WHERE RECNO<=3D( SELECT MAX(RECNO) FROM T1) ORDER BY RECNO) WHERE RECNO>=3D250000 AND ROWNUM<=3D1; The DBMS Xplan: ----------------------------------------------------------------------------= ---------------------------------------------------------- | Id | Operation | Name | Starts | E- Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------= ---------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.60 | 320 | | | | | 2 | VIEW | | 1 | 3 | 1 |00:00:00.60 | 320 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 3 | 1 |00:00:00.60 | 320 | 2048 | 2048 | 2048 (0)| | 4 | UNION-ALL | | 1 | | 50000 |00:00:00.51 | 320 | | | | | 5 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 2 | 50000 |00:00:00.21 | 316 | | | | |* 6 | INDEX RANGE SCAN | SYS_C0013568 | 1 | 450 | 50000 |00:00:00.06 | 191 | | | | | 7 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | | | 8 | INDEX FULL SCAN (MIN/MAX)| SYS_C0013568 | 1 | 100K| 1 |00:00:00.01 | 2 | | | | | 9 | TABLE ACCESS BY INDEX ROWID | T1OLD | 1 | 1 | 0 |00:00:00.01 | 4 | | | | |* 10 | INDEX RANGE SCAN | SYS_C0013569 | 1 | 10000 | 0 |00:00:00.01 | 4 | | | | | 11 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | | | 12 | INDEX FULL SCAN (MIN/MAX)| SYS_C0013568 | 1 | 100K| 1 |00:00:00.01 | 2 | | | | ----------------------------------------------------------------------------= ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=3D1) 3 - filter(ROWNUM<=3D1) 6 - access("RECNO">=3D) filter("RECNO">=3D250000) 10 - access("RECNO">=3D250000 AND "RECNO"<=3D) This time the plan is a bit more complicated, and it takes 0.01 seconds longer to complete. Maybe, forcing the use of indexes with only two columns in the tables is actually slowing down performance: SELECT * FROM (SELECT C1, RECNO FROM T1 UNION ALL SELECT C1, RECNO FROM T1OLD ORDER BY RECNO) WHERE RECNO>=3D250000 AND ROWNUM<=3D1; ----------------------------------------------------------------------------= --------------------------------------------------------- | Id | Operation | Name | Starts | E- Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------= --------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.43 | 258 | | | | | 2 | VIEW | | 1 | 3 | 1 |00:00:00.43 | 258 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | | 1 |00:00:00.43 | 258 | 2048 | 2048 | 2048 (0)| | 4 | UNION-ALL | | 1 | | 50000 |00:00:00.35 | 258 | | | | |* 5 | TABLE ACCESS FULL | T1 | 1 | 50000 | 50000 |00:00:00.05 | 256 | | | | | 6 | TABLE ACCESS BY INDEX ROWID| T1OLD | 1 | 1 | 0 |00:00:00.01 | 2 | | | | |* 7 | INDEX RANGE SCAN | SYS_C0013569 | 1 | 1 | 0 |00:00:00.01 | 2 | | | | ----------------------------------------------------------------------------= --------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=3D1) 3 - filter(ROWNUM<=3D1) 5 - filter("RECNO">=3D250000) 7 - access("RECNO">=3D250000) The above completed 0.16 second faster. In the above, Oracle automatically used the index on the T1OLD table to determine that no rows would be returned from that table. A performance improvement that I was not expecting by using the view (this may be specific to Oracle 11.1.0.6): SELECT * FROM (SELECT * FROM T1VIEW WHERE RECNO>=3D250000) WHERE ROWNUM<=3D1; ----------------------------------------------------------------------------= ----------------------------- | Id | Operation | Name | Starts | E- Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------= ----------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 128 | | 2 | VIEW | T1VIEW | 1 | 3 | 1 |00:00:00.01 | 128 | | 3 | UNION-ALL | | 1 | | 1 |00:00:00.01 | 128 | |* 4 | TABLE ACCESS FULL | T1 | 1 | 50000 | 1 |00:00:00.01 | 128 | | 5 | TABLE ACCESS BY INDEX ROWID| T1OLD | 0 | 1 | 0 |00:00:00.01 | 0 | |* 6 | INDEX RANGE SCAN | SYS_C0013569 | 0 | 1 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------= ----------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=3D1) 4 - filter("RECNO">=3D250000) 6 - access("RECNO">=3D250000) The above completed in 0.01 seconds. I would suggest avoiding the use of the static view, if possible (even though in the above case it seems to have helped). You might need to perform occasional maintenance on the indexes if you are periodically deleting from the T1 table and adding those rows to the T1OLD table. See the following thread for the symptoms of what may happen if you do not maintain the indexes: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/th= read/79d0a3d85381dc64 Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.