Reputation
4,664
Top tag
Next privilege 5,000 Rep.
Approve tag wiki edits
Badges
1 9 22
Impact
~441k people reached

1d
comment MariaDB: inner join searching for 1 in TINYINT column not working
@gglasses : the schema you posted in sqlfiddle doesn't have foreign key constraint ( CONSTRAINT imagehit_ibfk_1` FOREIGN KEY (owner_id) REFERENCES image (id)`) , and it cannot be added because the sample data violates it.
Feb
4
answered ORA 01722: Invalid Number caused by different comparison
Feb
3
comment Join SQL Server table in OPENQUERY on multiple keys
@user2316040 : I can't try myself, but what if you INNER REMOTE JOIN (SELECT all_columns_except_timestamp_column FROM ...) A ? I hope it shouldn't complain about timestamp then...
Feb
3
comment How to compare dates on minute level in plsql?
@Phil : or TRUNC .
Feb
3
answered Join SQL Server table in OPENQUERY on multiple keys
Feb
2
comment Does the function inside the Select clause evaluate the passed in columns before the where clause?
You've already got an answer, so just to mention another option. Qualifying variables will resolve ambiguity as well (and [arguably] more readable) . For instance , access function argument FORM_ID within the body of function as func_get_Open_Days_2.FORM_ID , etc.
Jan
30
comment SQL Server - Export large table without primary key
Since the trigger body is very simple (just insert data to another table), performance impact will be minimal.
Jan
29
revised SQL Server - Export large table without primary key
added 26 characters in body
Jan
29
answered SQL Server - Export large table without primary key
Jan
26
comment aggregate on join latest value
CTE is not required, inline view (subquery in from) will work as well.
Jan
25
comment When the query was last executed?
I'd rather check v$sqlarea , not v$sql . Note, unless you're accessing database with sysdba account (or O7_DICTIONARY_ACCESSIBILITY parameter is set to true) you have to grant access to underlying tables directly (v$... are in fact public synonyms), so for v$sqlarea you need to grant select on SYS.V_$SQLAREA to [your_user]; Under normal circumstances (decently sized SGA and most of the queries use bind variables, not literals) you will be able to find the query executed within a day or so.
Jan
14
awarded  Notable Question
Jan
11
comment Turn on multi-threading in Oracle for ETL purposes
Compared to SQLServer, Oracle has way more parameters that control parallel execution (also, parallel execution may be not available depends on edition ). Check docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm .
Jan
8
comment Execute immediate call causes 'variable not in select list' error
@mustaccio : EXECUTE IMMEDIATE works perfectly regardless of how many rows query returns.
Jan
8
answered Execute immediate call causes 'variable not in select list' error
Jan
5
comment Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS
Yes, FTS is because of cursor_sharing set to FORCE, and it doesn't seem to be possible to avoid such behaviour(at least with 12C). Changing optimizer mode to FIRST_ROWS(1) gives better cost and execution time when condition in OR ('a'='b'). evaluates to false, but it will be very inefficient when condition evaluates to true. I'd consider using EXACT sharing mode (at least for that statement). Also, in some cases even flushing shared pool periodically is better than FORCE cursor sharing mode.
Dec
21
answered How can I optimise an Update on a large table?
Dec
10
answered Update if not exists statement
Dec
2
comment Catching Errors in procedure
It's not very clear what you are looking for. If insert causes unique constraint/index violation , but you still want to insert then you have to drop the constraint / replace unique index with non-unique. If you want to catch error, it's done by EXCEPTION WHEN ... block (assuming plsql tag in the question is correct). If you want to keep constraint but avoid duplicates, rewrite the query so it doesn't insert duplicates. However, you cannot have both - unique constraint, and insert that violates the constraint.
Nov
30
comment How to tune a SQL query?
I'm not sure if it improves performance, but the subquery select max(rn) FROM (select row_number() ... looks to me as a fancy version of simple SELECT count(1) from kas_cari k WHERE k.yilid = :yilid and k.id=k.baslik ...