Home » SQL & PL/SQL » SQL & PL/SQL » Assistance with querying large table (Oracle 11g)
Assistance with querying large table [message #570659] Tue, 13 November 2012 18:56 Go to next message
JeffTN
Messages: 3
Registered: April 2012
Location: Tennessee
Junior Member
I'm at a bit of a loss at the "best practices" way to approach this. I've been given a scenario where I have a table, we'll call it "Customers." The Customers table has 20 million records. There's a related table, one we'll call "Orders" that has approximately 50 orders for each customer. customers.customer_number = orders.customer_number.

I am trying to do something like this:

select customer.customer_number, customer.name, customer.address from customer
where orders.customer_number = customer.customer_number and orders.widget_color = "blue"

orders.widget_color is not indexed and there are approximately 1 billion rows in the table.

Obviously I run into ORA-02393 (Exceeded call limit on CPU usage) after about 2 minutes of trying to run pretty much everything I've tried.

I'm guessing this may be a situation where a cursor/loop is required to limit processing to a smaller record set at a time. What would be the proper way to handle this?

Thanks in advance!
Re: Assistance with querying large table [message #570660 is a reply to message #570659] Tue, 13 November 2012 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
I seriously doubt the SQL that you posted was actually run.
SELECT customer.customer_number, 
       customer.name, 
       customer.address 
FROM   customer 
WHERE  orders.customer_number = customer.customer_number 
       AND orders.widget_color = "blue" 

should the FROM clause include "orders" table?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>orders.widget_color is not indexed
all the columns in the WHERE clause should be indexed.
Re: Assistance with querying large table [message #570674 is a reply to message #570659] Wed, 14 November 2012 01:49 Go to previous message
John Watson
Messages: 4524
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Obviously I run into ORA-02393 (Exceeded call limit on CPU usage) after about 2 minutes of trying to run pretty much everything I've tried.
This error is because your DBA has imposed a limit (probably through a profile) that prevents your session from exceeding that much CPU. He will have done this for a reason. Better talk to him.
--
Edit: typos

[Updated on: Wed, 14 November 2012 01:49]

Report message to a moderator

Previous Topic: How to update a clob value
Next Topic: oracle tunning
Goto Forum:
  


Current Time: Wed Sep 03 03:37:08 CDT 2014

Total time taken to generate the page: 0.06215 seconds