Prepared Statement Hanging.

[Sandeep Jain]

Hello Guys,

I am facing a strange problem with JDBC Connection.

I have used Classes12.zip, oracle9i.jar,ojdbc14.jar as the Driver.

The Program is as follows.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import java.sql.ResultSet;

public class x {

public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc racle:thin:@172.21.4.63:1521 00FR1", "xyz", "xyz");


String query = "select * from cdr where phone_number=? AND day_of_year between 291 AND 292 " ;
PreparedStatement stmt = conn.prepareStatement(query);
stmt.clearParameters();
stmt.setString(1,"[phone]");
ResultSet rs = stmt.executeQuery();


/* String query = "select * from cdr where phone_number='[phone]' AND day_of_year between 291 AND 292" ;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
*/
if(rs.next())
System.out.println("Successful");
else
System.out.println("Unsuccessful");
rs.close();
stmt.close();
conn.close();
}
}
When I Comment the Prepared Statement and use normal Statement the query comes out very fast however When I use Prepared Satement it just hangs .

The Load in the Table is around 2000 million. and the Phone number is indexed. and day_of_year is partitioned.

Any help is appriciated as to why Prepared Staement is hanging.

Thanks in Advance

Sandeep Jain

[Ulf Dittmer]

You have a table with 2 billion entries? Impressive.

Can you run an analyzer to see what plan is actually executed? Maybe the index is disregarded for some reason, and a table scan would take some time for such a big table.
[ November 07, 2005: Message edited by: Ulf Dittmer ]