Home » SQL & PL/SQL » SQL & PL/SQL » Joining one little and one big table. Noob question. (Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production)
Joining one little and one big table. Noob question. [message #394517] Fri, 27 March 2009 10:14 Go to next message
kukuruku
Messages: 2
Registered: March 2009
Junior Member
Hello.

I'm hardly familiar with Oracle.
I want fast answer, so i ask you, gurus for help.

I have two tables. First table A, 12000 rows 1 column id.
Second table B, 210 billion(210 000 000) rows, about 15 columns. Id - is unique in two tables.

I want to create table which contains data from table B with id's from table A.
How to do it better?

I do query like that

select a.* from a, b
where a.id=b.id

It rans more than hour and doesn't stop. I ran it through TOAD.

Re: Joining one little and one big table. Noob question. [message #394520 is a reply to message #394517] Fri, 27 March 2009 10:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Toad will just use more Rollback segments.
Being in 8i, hope you have a ton of them.
Just search for CTAS with append hint and nologging options.
Gather stats on both tables & indexes before using CTAS.
Since one of table is small, may be you can make use of a hash join.

[Updated on: Fri, 27 March 2009 10:30]

Report message to a moderator

Re: Joining one little and one big table. Noob question. [message #394530 is a reply to message #394520] Fri, 27 March 2009 12:38 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Can you please explain this more - "Toad will just use more Rollback segments." Thanks!
Re: Joining one little and one big table. Noob question. [message #394537 is a reply to message #394530] Fri, 27 March 2009 12:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Actually, that was an overstatement.
Toad ***might*** eat more Undo.

Michel has better words (and rants) Wink
http://www.orafaq.com/forum/m/391934/42800/?srch=toad+undo+space#msg_391934

I myself have cursed every living being around me and folks in outer space for those midnight/mid day/mid nowhere pages I got with undo related issues.
Never actually realized before that it was always with Toad users.

[Updated on: Fri, 27 March 2009 13:06]

Report message to a moderator

Re: Joining one little and one big table. Noob question. [message #394573 is a reply to message #394520] Fri, 27 March 2009 18:27 Go to previous messageGo to next message
kukuruku
Messages: 2
Registered: March 2009
Junior Member
Mahesh Rajendran wrote on Fri, 27 March 2009 10:20
Toad will just use more Rollback segments.
Being in 8i, hope you have a ton of them.
Just search for CTAS with append hint and nologging options.
Gather stats on both tables & indexes before using CTAS.
Since one of table is small, may be you can make use of a hash join.



Well, thanks for answer.

As i look at first links in google about "CTAS with append hint", this links say that i don't have to use any hint in CTAS at all.

Indexes. Big table has indexes on "id" column. Small table doesn't have any. Do i need to create it?

How do i use hash joins? Doesn't Oracle use them automatically if they are appropriate?

What about TOAD Rollabcks again. Does it means what client software do i use? TOAD or sql plus - does it matter?


May be my questions looks a bit silly. But i have a little knowledge about all this Oracle stuff.
Re: Joining one little and one big table. Noob question. [message #394575 is a reply to message #394573] Fri, 27 March 2009 18:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>this links say that i don't have to use any hint in CTAS at all.
My wordings were not right. My apologies.
What I meant is,
you can use CTAS with nologging or
CTAS without data and insert with APPEND.
>>How do i use hash joins? Doesn't Oracle use them automatically if they are appropriate?
if the statistics are updated and CBO thinks it is better this way, it will do it.
You can try hints again, it is upto CBO to use it.
http://download.oracle.com/docs/cd/A87860_01/doc/index.htm
>>TOAD or sql plus - does it matter
You have to deal with RBS anyhow.Seem toad makes it little more painful with long running queries.
My concern was with this 8i version. All later versions use
undo tablespace and retention and it is way way better.

I would just let CBO do the job.
If this operation is frequent, you may look into fine tuning it.
And you really want to upgrade. 8i is ancient .
Re: Joining one little and one big table. Noob question. [message #394583 is a reply to message #394537] Fri, 27 March 2009 20:39 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
thank you so much =)
Previous Topic: Error executing statement: ORA-06550
Next Topic: Oracle 10g
Goto Forum:
  


Current Time: Sat Dec 10 05:27:33 CST 2016

Total time taken to generate the page: 0.09837 seconds