| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL question
On Sun, 28 Mar 1999 20:40:17 GMT, Mladen Gogala <mgogala_at_yahoo.com>
wrote:
>This is a logically valid solution to the problem, but performance of
>such solution will be less then satisfactory. If I'm not mistaken, the
>solution developed here is a software package for a shipping company,
>which can have literally thousands of events daily. "view on the fly",
>multiplied by 4 will kill that query, unless we are talking about 4 way
>SUN E10000 cluster. The only proper solution would be to redefine the
>underlying data model and separate the main events into separate tables.
>Performance begins with the data model. The alternative solution which
>would significantly speed up Jurij's query would be to create bitmap
>index on the 'EVE' column.
>Regards,
I generaly agree with you. There is simply not enough information on what the original poster wanted to have in the report, ie should the report include all the events or will there be some limiting factor of which events will be looked for. If the table is huge as you assume then the "full scanned" report would be meaningless as the number of rows returned would be 1/4 of all of the rows in a table!
So if the report should realy include all of the event# then I belive the table must be relatively small. In that case the performance should not be bad at all. On a test table of 2000 recors (500 distinct event#) on my old laptop (P133, 32 Meg, PO 7.3.2) it finishes in about 1 second. I also agree with you that in "full table report" bitmap on EVENT would speed things up.
If the table is huge then I belive the limiting factor would be based on the EVENT# or some date ranges. In this case a single (probably concatenated) b-tree index should make the querry very fast, unless the limiting range is so broad that the report will loose all of it's sence because of the number of returned rows. In the case of range limitation the bitmap index on EVENT looses it's sence.
Although the select with 4 inline views might look scarry, its explain plan is rather simple and straightforward and it doesn't show any "query killing intentions", unless the table is very large and without at lest PK index (which should be EVENT# + EVENT). In any case, during my testings it behaves significantly faster than the solution with SUM(DECODE..) + GROUP BY, proposed by Steve and Arlette.
>Mladen Gogala
Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |