Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Beasty View

Re: Beasty View

From: OraSaurus <granaman_at_not_home.com>
Date: Fri, 25 Jun 1999 01:05:30 GMT
Message-ID: <uBAc3.1141$4p2.1095@news.rdc1.ne.home.com>


In article <7kt26u$42e10$1_at_titan.xtra.co.nz>, "Adrian Simpson" <adsimpson_at_xtra.co.nz> wrote:
>I have a view that selcts from 4 tables and 1 view. The size of the tables
>range from 300,000 rows to 6.5 million rows. This figure will grow each
>week. At the moment it takes over twelve hours to get it's act together and
>start selecting the rows. It runs on a server that has heaps of grunt so
>that should not be a problem.
>
>Apart from optimising it so that it uses all of the indexes in all of the
>tables (which we are doing at the moment) is there any other suggestions out
>there, anything we are forgetting and should be doing?

Yes. Obviously. You don't say *how* the join occurs or what is the nature of the view. Lets see...

Best case: volume mostly irrelevant, all joins on a single-column numeric primary key, no index stagnation, and query criteria dictates returning only the primary key values. The view is defined by "create view V as select dummy from dual;" Hmmm... Should take a few milliseconds...

Worst (well, not quite) case:
Table A=300,000 rows, B=1,000,000 rows, C=2,000,00 rows D=6,500,000 rows, View V= Full cartesian product of three 1,000,000 row tables... No indexes... Several of the tables have long raws, every column of every table is retreived, and the query is something like:

        select a.col1, ..., b.col1, ..., c.col1, ..., d.col1, ..., v.col1
            from a, b, c, d, v
           order by <something truely silly>;

Yep! That could take a while... Pluto might be colonized before its done. (Count(*) = 300,000 * 1,000,000 * 2,000,000 * 6,500,000) * (1,000,000** 3)

Have you tried explain plan? How about running the query after "set autotrace on"? Try it (explain plan first please). See what its actually doing! Then post the query and the explain output...

Received on Thu Jun 24 1999 - 20:05:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US