3,399 reputation
410
bio website
location Vancouver, Canada
age
visits member for 2 years, 9 months
seen 5 hours ago

Apr
17
answered SQl comand works in Workbench but not from Java Program
Apr
17
comment SQl comand works in Workbench but not from Java Program
How do you call it from java? You cannot just pass these lines to 'Statement.executeUpdate` . Each insert statement should be executed as a separate call to Statement.executeUpdate (and as far as I remember it should be no semicolon ). So you are getting connection object, set autoCommit to false, create a prepared statement object, execute inserts, and then commit (connection.commit)
Apr
12
comment Row_Number() not working with OVER PARTITION BY
@RateControl : Can you add D.CType to final resultset (I suspect it is TYPE1, not expected TYPE4 )? It appears to me that either B.I >= @Var1 in WHERE or joining C filters out fourth record from D .
Apr
6
comment Oracle 10g: same query, different plans on copies of the same DB
If you are using bind variables and histograms (enabled by default), I'd first of all blame bind peeking. Try disabling automatic histogram collection for the tables/columns involved.
Mar
28
awarded  Popular Question
Mar
27
revised SELECT first value from 3 tables that is not null
added 102 characters in body
Mar
27
answered SELECT first value from 3 tables that is not null
Mar
27
comment MySQL: Transaction and Deadlock
If it's one time operation, then maybe it's worth trying (on the other hand, for one time operation performance should not be an issue)... But it looks like a hack to me even if it works. I'm used to think that database is "owned" by enterprise, not by a particular application. Also, changing global isolation level may not work if the app sets it explicitly for the session.
Mar
27
comment MySQL: Transaction and Deadlock
Deadlocks mean there is something wrong in the application logic. All I can see from the question that there are several UPDATEs hitting the same table, so deadlocks may be attributed to the fact that mysql performs gap locks for non-unique search conditions (assuming default isolation level repeatable read is used). I'd focus on fixing the application[s]; if that is not possible for any reason then just execute them sequentially...
Mar
27
comment MySQL: Transaction and Deadlock
Changing global isolation level is not a good idea. First of all, it affects only new sessions, and most applications use one or the other implementation of connection pool which means they obtain physical connections in a bulk and very rare. Secondly, changing global isolation level requires more permissions and giving application SUPER privilege doesn't sound right to me...
Mar
27
comment MySQL: Transaction and Deadlock
Can you post deadlock trace? It can be found in mysql error log if "innodb_print_all_deadlocks" server flag is set, or using SHOW ENGINE INNODB STATUS; command which gives just last deadlock information (it should be enough though since all your deadlock seem to be pretty much similar).
Mar
26
answered Is it possible to know how many times a user connected to an Oracle database?
Feb
25
comment How to compare a result of a query to a number?
The same might be written using ANY , for instance , ` WHERE 0<ANY(SELECT amount FROM table2). It should produce the same results and execution plan as EXISTS` ...
Feb
24
comment To normalize or not to normalize for few distinct values
I guess you meant party table, not party_type which will be a small lookup. Depends on other requirements, you may store organization and person in one table ; that seems ok to me under some circumstances .
Feb
24
answered To normalize or not to normalize for few distinct values
Feb
22
comment Order of returned record with IN statement postgres
"Nicer" is quite subjective; would you consider using ORDER BY CASE WHEN ... THEN ... as nicer ? As to efficiency, I'm pretty sure optimizer will generate the same plan.
Feb
20
comment table or view does not exist
You need to use EXECUTE IMMEDIATE (docs.oracle.com/cd/B19306_01/appdev.102/b14261/…) for running dynamic sql.
Jan
23
comment What is the exact relationship between a database transaction and locking?
Clarification - I meant BEGIN TRANSACTION itself doesn't create DML locks; it should in fact issue some internal locks because it has to allocate resources, add an entry to system table[s] (if any) that holds active transactions, etc.
Jan
23
comment What is the exact relationship between a database transaction and locking?
As far as I know, BEGIN TRANSACTION itself issues no locks. Locks will appear after DMLs within transaction.
Jan
23
answered What is the exact relationship between a database transaction and locking?