Sudheer Bhat

By setting fast=true ?

There isn't defined set of rules to improve the performance of the application. Few generic comments:
1. Define what is the expected response time of your application.
2. Once you have defined 1 check if you have good enough hardware to support it.
3. Both 1 and 2 are satisfactory, yet application is slow, then check for any network latency issues.
4. 1, 2 and 3 seems to be doing okay, then instrument your application to determine where exactly the time is spent. Is it the database, is it the middle tier code, is it something else?
5. Once you have determined the bottleneck, then work to resolve it.

Martin Vajsar wrote:

Sudheer Bhat wrote:Your query would look something like SELECT ... where field1 = NVL(:1,field1) and field2 = NVL (:2, field2) ... (If your DB is Oracle)

In JDBC, the :1, :2 should be replaced by question mark.

However, if I'm not seriously mistaken, such query will never use an index on any of these columns (certainly not on Oracle).

I just ran a simple test case against my Oracle DB and it seems to pick up the index.
SQL> create table test_index_usage as select * from dba_objects where rownum < 50000; Table created. SQL> create index test_index_usage_n1 on test_index_usage(object_name); Index created. SQL> var x varchar2(30); SQL> exec :x := 'TEST'; PL/SQL procedure successfully completed. SQL> set autotrace traceonly explain SQL> select * from test_index_usage where object_name = nvl(:x,'TEST'); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=73 Card=507 Bytes= 104949) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_INDEX_USAGE' (TABLE ) (Cost=73 Card=507 Bytes=104949) 2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX_USAGE_N1' (INDEX) (Cos t=1 Card=203)

And this as expected doesn't pick up the index.

SQL> select * from test_index_usage where upper(object_name) = nvl(:x,'TEST'); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=196 Card=507 Bytes =104949) 1 0 TABLE ACCESS (FULL) OF 'TEST_INDEX_USAGE' (TABLE) (Cost=19 6 Card=507 Bytes=104949) SQL>

Check your database documentation for null value function(s). Oracle has NVL and a quick lookup on mysql documentation says that COALESCE or IFNULL can be used. I haven't used MySQL functions so not much I can add to that.

Your query would look something like SELECT ... where field1 = NVL(:1,field1) and field2 = NVL (:2, field2) ... (If your DB is Oracle)

From your post not really sure what you want to achieve. You may need to check dba_tables?

Martin Vajsar wrote:
Yes, it could definitely be an issue. Try creating the type using CREATE TYPE and if it is not enough, use fully qualified name.

+1 for above. I don't think there is anyway to refer to user defined types in the packages via JDBC directly. The access has to be via UDT's created by CREATE TYPE.

Jitesh,

Why not build the SQL query based on the conditions selected?

The better way to copy records from one to another is insert into table2 select * from table1 where created_date <= systemdate-60. This assumes that your table has a column to store the created date. The syntax might slightly differ from database to database.

In case 1, you acquire the lock on the row you are updating so that no one else can acquire the lock. So all other interested transactions who may try to update this row has to wait. So the advantage with approach 1, is that you dont have an issue of "lost updates".

I think this post needs to be moved to the JDBC forum. I am not sure how to move it. Next time around, please choose the forum carefully!

Is it throwing an exception? Even if it throws an exception you will never know that unless you run debugger and step line by line through the code because you have a empty catch block.

Also probably you want to use a PreparedStatement instead of Statement.

Your code is not right. You are setting a string literal to the query in list where as DB expects the in list to be numbers.

The query which gets exected in your case will look like
SELECT ...... FROM table where something in ('1,2,3');
Make sure your query gets constructed as
SELECT ...... FROM table where something in (1,2,3);
Where 1, 2 and 3 are numerical values (assuming org_id is of type number in the DB).

This is resolved.
I did the below instead of creating a new blob object.
BLOB blob = BLOB.createTemporary(txn.getJdbcConnection(),false,BLOB.DURATION_SESSION); outputStream = blob.setBinaryStream(0L); inputStream = new ByteArrayInputStream(byteArray); byte[] buffer = new byte[blob.getBufferSize()]; int bytesRead = 0; while((bytesRead = inputStream.read(buffer)) != -1){ outputStream.write(buffer,0,bytesRead); } outputStream.close(); inputStream.close();

I added this to the BLOB array and used the BLOB array to create the sql ARRAY.
Works great now!.

Note: I got a lead to this in stackoverflow when I was searching the web.

Hi All,

DB: Oracle 11g.

I have a table type of BLOB defined in the DB.
create or replace my_type as table of blob.

I have a UI where users can upload multiple files as attachment to an service order getting created. I use Oracle BC4J, hence these documents are stored as BlobDomain.

In my java class, I am trying to create an ARRAY of these blobs and it invariably fails.

What I have tried so far:
1. Create a ArrayDescriptor for my_type. Try to create the ARRAY using the descriptor and BlobDomain Array. Code fails with "Fail to convert to internal representation"
2. Create a ArrayDescriptor for my_type. Try to create the ARRAY using descriptor and Object[] or byte[] (byte array is derived by making call to getByteArray(long offset, int length) method on the BlobDomain. Same error as above.
3. Create a ArrayDescriptor for my_type. Try to create the ARRAY using descriptor and BLOB[]. BLOB array created from byte[] derived from BlobDomain. Here the call goes to DB, but DB throws ORA-22927: invalid LOB locator specified.

I tried to use oracle.sql.STRUCT with the combination of ArrayDescriptor but that approach also failed with "Fail to convert to internal representation"

I am not sure how to use ARRAY for BLOB tables. I have been successfully using for all other native DB types and also user defined ADT's.
Any pointers for the same?

Thanks in advance.

Paul Clapham wrote:Include a non-null column from your "child" table.

As Paul has stated, if you include a column name in the count() function then it shows the count of the non null values for that particular column.Here is a link from w3schools about the count function.

I don't have Oracle on my laptop. I will check it at work on Monday. Will trace and tkprof it on Monday! =).

Thanks Martin for correcting me on the INSERT ALL. I missed it!.
Just curious, have you ever traced a INSERT ALL? Assuming insert all inserts into 2 tables a record each, the execute count shows 2 in the tkprof?