Home » RDBMS Server » Performance Tuning » select does a full table scan. 130.000.000 rows (Oracle 9)
select does a full table scan. 130.000.000 rows [message #356526] Fri, 31 October 2008 00:21 Go to next message
mclu
Messages: 3
Registered: October 2008
Location: Germany
Junior Member
Hi out there!
First I am a java programmer not a DBA.
So I am sorry for my knowledge regarding:

Big Database and select statement performance.

I have a Oracle 9 Database (as installed without any extra performance settings)
There is no Resource Plan active (don't know what this is anyway)


The Table is like this(script generated by toad):
CREATE TABLE mytable
(
  ID   INTEGER                       NOT NULL,
  A    NUMBER(10)                    NOT NULL,
  B    VARCHAR2(10 BYTE)             NOT NULL,
  C    NUMBER(32,2)                  NOT NULL,
  D    DATE                          NOT NULL,
  E    INTEGER                       NOT NULL,
  F    INTEGER                       NOT NULL,
  G    INTEGER                       DEFAULT '0'
)
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


I have an index on B
CREATE INDEX mytable_B ON mytable
(B)
NOLOGGING
NOPARALLEL;


There are 130.000.000 entries in the Table

PROBLEM:
I have to process the Data on Java

First I fetch all Distinct(B) of this table
and loop through all B's (around 1.000.000)
The Distinct call takes 8 Minutes.

But Then I have to fetch 1000000 times
select ID,A,C,D from mytable where B = ?

EACH CALL TAKES 5 MINUTES via java/jdbc. (time spend at oracle)

In my EM session monitor I can see:
SQL: SELECT id, a, c, d
    FROM mytable
    WHERE B = :1
Plan: 
select mytable
   Master.mytable table access (FULL)

--> so he do a table scan

If I do a query via toad it returns after seconds.

Can you give me performance hints? I thought it should use the index. jumps to the 50 resulting rows and return them.
This should not that slow, or?
Is the reason that I use a prepared statement?

THX A LOT. I want to have this computed before I am old Smile

Markus Lutum
Re: select does a full table scan. 130.000.000 rows [message #356551 is a reply to message #356526] Fri, 31 October 2008 02:25 Go to previous messageGo to next message
mclu
Messages: 3
Registered: October 2008
Location: Germany
Junior Member
Hey! I did a test with using direct sql from my java so no prepared statement is used. Then is is also fast.

So question: Why does it not use the index with a prepared statement?
Re: select does a full table scan. 130.000.000 rows [message #356683 is a reply to message #356551] Sat, 01 November 2008 00:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The :1 that you are passing into the query is a JAVA variable, right? Is it the same data type as the row in the table? ie. Are they both numbers or both strings? If they are different, your SQL will cast the VARCHAR column to a number and you will not be able to use an index.

This is very bad design and will always run poorly. See this article on PL/SQL tuning - the same theory applies to Java. If you really want to tune it properly, you will need to redesign.

Ross Leishman
Re: select does a full table scan. 130.000.000 rows [message #356927 is a reply to message #356683] Mon, 03 November 2008 04:20 Go to previous messageGo to next message
mclu
Messages: 3
Registered: October 2008
Location: Germany
Junior Member
Thx for reply and sorry because I already found out.

You are right. The table datatype was varchar and the given parameter was Java-Long.
I thought that maybe the java jdbc driver converts the data on the fly...
Thats is also the reason why it was so fast if I not use a prepared statement. In that case java converts the Long parameter to the sql string.

I did Long.toString(myParameter) while setting the parameters to my prepared statement and now it is faster....

OK It still takes 4 Days to process the database but that is ok for me...

Thx!
Markus
Re: select does a full table scan. 130.000.000 rows [message #357215 is a reply to message #356927] Tue, 04 November 2008 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that you would be better off executing a query like
select ID,A,C,D from mytable ORDER BY B
and looping throught the values that this returns, taking care to spot when the value of B changes.

From yuor description, you are looking at every row from the table - the quickest way to do this is a Full Table Scan, not by 1,000,000 seperate index scans.
Re: select does a full table scan. 130.000.000 rows [message #357217 is a reply to message #356526] Tue, 04 November 2008 05:34 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi, Markus.

Why do you need to access the same data twice:
1. Selecting all distinct B values
2. Selecting 1M times all rows for the specific B.

Use:
SELECT * from mytable

It will performs full table scan (as you are doing now probably anyway dusing select distinct), but you process will perform a single pass through the data.
If an order is important - make it
SELECT * FROM mytable ORDER BY b;


HTH.
Michael
Previous Topic: Whether Statistics upto date or not
Next Topic: gather stats how?
Goto Forum:
  


Current Time: Fri Dec 09 03:54:26 CST 2016

Total time taken to generate the page: 0.09764 seconds