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:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > 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. > [EMAIL PROTECTED] > > > -----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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
