RE: Advice needed please

how about index organizing the table ? or .. creating an index on all the columns of the table..?
this way the select will read only the index blocks..!!

> ----------
> From: Robertson Lee - lerobe[SMTP:lerobe_at_acxiom.co.uk]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Thursday, May 30, 2002 9:43 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Advice needed please
>
> Thanks Dennis.
>
> Anything whether it be a "reaction or an answer", is welcome.
>
> Regards
>
> Lee
>
>
> -----Original Message-----
> Sent: 30 May 2002 15:08
> To: Multiple recipients of list ORACLE-L
>
>
> Lee - Just some reactions, few answers.
> - Generally a process like this will be disk-bound, not CPU-bound, so
> idle
> CPU time is to be expected unless your disk is REALLY fast.
> - Multiple simultaneous full-table scans may not be any faster because
> the
> disk heads may need to flit to and fro in order to satisfy each process'
> request. Sometimes a single full table scan is as fast is it gets for a
> mechanical device like a disk. RAID will be faster, of course, but
> ultimately the RAID is composed of disks.
> - Trying for something faster than select * is a real challenge. To
> perform a full table scan, Oracle must read each data block. The
> alternative
> is index scanning, but this means reading an index block, fetching a data
> block, etc. Not faster if you're going to eventually read all data blocks
> anyway.
> - If select * isn't fast enough, you should consider using table
> partitioning. That way each process can separately scan a separate
> partition
> and separately write to your output files.
> Hopefully someone else will think of a bright idea I've missed.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Thursday, May 30, 2002 6:48 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Oracle 8.0.5
>
> Tru64 4.0f
>
> One of our developers here is writing a utility to provide fast unloads of
> tables (to replace fastunloader as it happens)
>
> His problem is as follows. Start from the bottom and work your way up. I
> would be really grateful if anyone can offer up some alternatives for us.
>
> Regards
>
> Lee
>
>
> > -----Original Message-----
> > From: Dudley Dave - ddudle
> > Sent: 29 May 2002 16:04
> > To: Robertson Lee - lerobe
> > Subject: RE: Do you still have that SQL Expert?
> >
> >
> > No, you miss the point. I'm explicitly NOT using PQ (or at least not
> > explicitly using it).
> >
> > Using a parallel hint on huge table unloads - with the
> > single-threaded version of the code (i.e. pipdynsql.v2.0.0) didn't seem
> to
> > make much difference at all. I didn't do the tests directly though,
> poeple
> > on the account did. So it may be that the tables already had a degree of
> > parallelism built in, in which case I'd guess the hint would be
> redundant.
> >
> > What I mean is that even if you use PQ for the server to extract the
> > data in parallel you still have the bottle neck of a single client to
> send
> > it all back to. That's what I was trying to get around. Assuming that
> > we're not generally using the full network bandwidth, I'd assume that
> > multiple clients ought to be able to dump out separate sections of a
> table
> > at the same time, at roughly the same speed at a single client would
> > unload a single table - i.e double the throughput.
> >
> > But I can't find anything on the web to tell me the best / most
> > efficient way to actually do this. (By the way, I've tried the
> NO_PARALLEL
> > hint too, to stop the server setting off too many conflicting slaves on
> > its side. Again no better as far as I could tell.)
> >
> > N.B. Not sure if you'd suggest it, but before you do: most of the
> > tables we'd really want to use this for are massive, and so are already
> > partitioned. So where I say "table" I mean either that or a partition
> > thereof. Besides, need a generic solution that doesn't rely on having to
> > partition your table to unload it quickly.
> >
> > By the way, I'm specifically testing speed of my original code (e.g.
> > pipdynsql.v2.0.0 user/pass "select * from table") against the new
> > multithreaded development code - i.e. regardless of the machine load at
> > the time, I want to see if multiple simultaneous unloads can be quicker
> > than a single unload client (at the expense of using more machine /
> Oracle
> > resource obviously).
> >
> > Is this making sense?
> >
> > Dave
> >
> > -----Original Message-----
> > From: Robertson Lee - lerobe
> > Sent: 29 May 2002 15:14
> > To: Dudley Dave - ddudle
> > Subject: RE: Do you still have that SQL Expert?
> >
> >
> > How are you using PQ, is it just a hint ??. Which tables are you
> > testing against.
> >
> >
> > -----Original Message-----
> > From: Dudley Dave - ddudle
> > Sent: 29 May 2002 14:27
> > To: Robertson Lee - lerobe
> > Subject: RE: Do you still have that SQL Expert?
> >
> > OK Clever-Trousers,
> >
> > As you're so hot on table/index disk striping...
> >
> > I've written the program "pipdynsql", which as you may have
> > heard (this lunchtime if not before), people want to use to replace
> > FastUnloader.
> >
> > I've been playing about with a new multithreaded version to
> > try to download a table in sections to multiple client threads which
> then
> > write back out to a single file (either ordered, or for max speed in
> > random/undefined order).
> >
> > Can you think of any quick ways to do this, or "tricks" to
> > try?
> >
> > I've tried ranges of rowids (as I'm told that's hold
> > parrallel query works) but the ROWID (tab) hint does not seem to go
> > through the table in rowid order. And it's a massive overhead to order
> by
> > rowid to work out non-overlapping ranges. And even if you do, you have
> to
> > say WHERE rowid >= xxx AND rowid <= xxx (as I say, can't force it to go
> in
> > ROWID "sort" order) so this tends to be slower than nect opt...
> >
> > tried assuming there's a unique index and giving start
> > points to each slave thread, which then selects a set number of rows.
> This
> > is prety quick, but even this seems slower than a simple SELECT * FROM
> > table (for the same number of records).
> >
> > tried loading temp "rowid" tables with sets of rowids and
> > each slave does a full table scan of its rowid set table, with where
> > clause connection to the data table.
> >
> >
> > Can't find anything better than my original method which
> > selects a unique key from an index with a "master" thread, for every
> x'th
> > rownum. Then hands these out to the slave threads to select * from table
> > where unique key >= given key for specified number of records. e.g.
> master
> > pulls out every 100,000th key with a modulus and each slave dumps out
> > 100,000 rows at a time, starting at the key its given.
> >
> > The above uses a temp view for the rownum bit to work. Also
> > tried without a master slave where the unique key is numeric, as you can
> > do the modulud directly on this. Tends to be slower - I guess due to MOD
> > func overhead? Tried adding CACHE(table) to this to try and make sure
> all
> > threads will get the majority of selected data from cache, rather than
> > each going to disk. But no better.
> >
> > Tried many, many other combinations of hints, but can't seem
> > to get more than "almost" double speed of a SELECT * from TABLE single
> > process approach - using about 5 threads. Even when there's loads of
> free
> > oracle/CPU resource available.
> >
> > Any ideas?!?!
> >
> > Dave
> >
>
>
> The information contained in this communication is
> confidential, is intended only for the use of the recipient
> named above, and may be legally privileged. If the reader
> of this message is not the intended recipient, you are
> hereby notified that any dissemination, distribution or
> copying of this communication is strictly prohibited.
> If you have received this communication in error, please
> re-send this communication to the sender and delete the
> original message or any copy of it from your computer
> system.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Robertson Lee - lerobe
> INET: lerobe_at_acxiom.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Robertson Lee - lerobe
> INET: lerobe_at_acxiom.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul
  INET: rahul_at_ratelindo.co.id

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).