Re: My first large table

From: abc <abc_at_abc.com.au>
Date: Mon, 21 Jul 2003 17:07:15 +1000
Message-ID: <bfg3eq$8or$1@mws-stat-syd.cdn.telstra.com.au>

After reading what Billy wrote, I like to share with you my VLT experience as I am still looking
for an answer

I have 2 two tables, A is VLT:
Table A: 2 million rows per month since Jun-2002 to today. Table B: 27 rows

Two simplified SQLs with Explain Plans as follows:

SQL#1
select /*+ index(a) */ count(*) from A
where stat_date between '01-JUL-2002' and '31-JUL-2002' group by stat_date

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=30 Bytes=240)   PARTITION RANGE (ITERATOR)
    SORT (GROUP BY NOSORT) (Cost=3 Card=30 Bytes=240)       INDEX (RANGE SCAN) OF A_PI1 (NON-UNIQUE) (Cost=3 Card=1041521 Bytes=8332168)

SQL#2
select /*+ index(a) */ count(*) from A, B where B.model_name = 'A'
and A.stat_date between B.start_date and B.end_date group by a.stat_date

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=223 Card=408 Bytes=14280)   SORT (GROUP BY) (Cost=223 Card=408 Bytes=14280)     NESTED LOOPS (Cost=3 Card=35552 Bytes=1244320)

      INDEX (RANGE SCAN) OF  B_1 (UNIQUE) (Cost=1 Card=1 Bytes=27)
      PARTITION RANGE (ITERATOR)
        INDEX (FAST FULL SCAN) OF A_PI1 (NON-UNIQUE) (Cost=2 Card=14220830

Bytes=113766640)

A.Stat_date is non unique index A_PI1

B.Start_date, B.End_date, B.model_name are Unique Index

I have tried using Parallel query with SQL#2 but that did not change much the end results compared to #1
As I need to automate the SQL#1 without editing it each month, therefore SQL#2 is the most suitable
to use, however performance cost multiplied by many times !

How can I reduce this overhead cost in SQL#2 ???

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:1a75df45.0307181001.79a64888_at_posting.google.com...
> johnleslie_at_madasafish.com (John Leslie) wrote:
>
> > Now we have a requirement to load large amounts of data into a table
> > which will be held for 3 months, resulting in several million rows.
> > The data will be loaded using sqlldr and thereafter queried for adhoc
> > report purposes only.
>
> John, interesting how relative a very large table (VLT) is.. a few
> million is not that large anymore. Heck, I think that a half billion
> is sizeable, but that is small fry compare to the guys running
> terrabyte warehouses with billions of rows. :-)
>
> > Just want to know, what are the basic things I need to do in setting
> > up this table to ensure decent performance.
>
> SQL*Loader is the right approach IMO. Direct loading is compulsory
> though if you want to push large volumes into the table in the
> shortest time period.
>
> > Do I need to reorganize the table indexes on regular basis to retain
> > performance? If so how? As you can tell I'm quite new at this!
>
> I will suggest looking at things like partitioning, local indexes,
> bitmap indexes and so on.
>
> If the queries are specific (i.e. hitting a small data set), make sure
> that the indexes work for them. Always try and work from the smallest
> set to the largest when joining. Watch out for nested loop and hash
> joines.. both can turn nasty under the wrong conditions.
>
> When the queries are not specific (you will see that when explain
> plans start showing index range scans), consider using parallel query.
> The time spend on range scanning indexes can often be put to better
> use hitting the table with a full scan in parallel. Or can be turned
> in to parallel index scans.
>
> Stay away from any row-based processing. You may get away with a 5ms
> overhead per row on a small table. On a VLT, that can turn into many
> hours of runtime.
>
> There's nothing really special about working with VLTS's. They only
> tend to show logic errors and physical design errors, much clearer and
> much sooner than small tables. :-)
>
> Above all, I think its where the real fun is in Oracle... and where
> you get to see what it is truly capable of.
>
> Oh yeah - last words. When in doubt. Explain plan and tkprof. I still
> find it amazing that many developers working on Oracle do not even
> seem to know what the plan table is.
>
> --
> Billy
Received on Mon Jul 21 2003 - 02:07:15 CDT