3,769 reputation
614
bio website
location Vancouver, Canada
age
visits member for 3 years, 7 months
seen 2 days ago

2d
awarded  Popular Question
Jan
23
comment Error in trigger: table X is mutating, trigger/function may not see it
You cannot directly access the table that triggers the action inside the body of row level trigger (In your case you're trying to select FROM SUMMITTEST.APL_LOG inside row-level trigger on APL_LOG ) . Common workaround is relatively simple but requires some coding (asktom.oracle.com/pls/asktom/… ) . However, I'd rather not have such a big query inside the trigger body. It's always possible to create a procedure that performs all required actions and use only that procedure for inserting into APL_LOG
Jan
16
comment How this can return more than 1 result?
I wonder if that is stated anywhere in documentation ? You are 100% right , explain shows full scan for such query (contrary to const primary 1 row if I change subquery to constant value) . However, I'm surprised that if the query is rewritten to JOIN syntax (e.g. ... JOIN (SELECT FLOOR.... as rnd)b ON (b.rnd = a.user_id)) it evaluates only once and never return more than 1 row (explain confirms that no full scan involved). Even in case of LEFT JOIN FLOOR(RAND) seems to be evaluated only once.
Jan
6
answered Full table scan - high water mark
Jan
5
comment Understanding multiple table join with aggregation
It's not the same for those 2 particular queries. I'm not arguing logical stages, but physical order can be completely different.
Jan
5
comment Understanding multiple table join with aggregation
@ErwinBrandstetter: It seems to me that there is a big difference between this question and links in your comment . The latter doesn't give optimizer any choice but perform cross join and then filter out duplicates to satisfy distinct. In the former I'd expect optimizer to generate the same plan as for your query .
Jan
4
comment Understanding multiple table join with aggregation
@ErwinBrandstetter : Nothing tricking about approach with distinct. It gives correct result, as well as other solutions like yours or mentioned in the links you provided. Performance-wise distinct might behave worse, but I'm not absolutely sure about it without seeing real tests with real data.
Dec
30
comment Understanding multiple table join with aggregation
I believe distinct will do that (note, it's counting distinct link1.id and link2.id, not link1.main_id/ link2.main_id ). Thus, in your example for main.id = 2 there will be 1 distinct link1.id and 2 distinct link2.id...
Dec
30
comment Understanding multiple table join with aggregation
You seem to confuse 0 with NULL . The first 2 outputs won't have any zeros. link1.main_id and link2.main_id might be either equals to main.id or NULL. And why not just COUNT(DISTINCT link1.id) + COUNT(DISTINCT link2.id) ?
Dec
29
comment How to set the sequence to the last used id for all tables in Oracle?
@Russell England : There is no "sequence name for table name" in Oracle. Sequence is not related directly to a table - you may use the same sequence to generate values in multiple tables or columns. Normally, you adopt some naming convention for sequences, say [table_name]_SEQ , so it's more readable.
Dec
16
awarded  Notable Question
Dec
8
comment How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)
@miracle173: Execution plan in any case is not created by user. Hints may influence how it's created, and in some cases hints are the only way to improve performance. I can't tell for sure whether hints give any benefits in this particular case; I just gave an example of how to make Oracle join 2 tables using sort merge join.
Dec
6
answered database design for optional association and temporal
Dec
3
comment Prevent row from being inserted with a trigger
You're catching exception in EXCEPTION block, so nothing is rolled back. You need either to re-throw it or deliver error through raise_application_error
Nov
26
comment Query shows SELECT 0
@Reaces Select 0 might be a part of library that the application uses to access database. It can be used , for instance, to check connectivity .
Nov
20
comment How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE?
I'm sure when statement gets executed only one row is locked (LIMIT 1). However, during execution of the statement the engine may put locks on all records that satisfy WHERE (note, order on which rows are actually locked is never guaranteed). I'm not postgres guy, so I can't state this is what happens in your case, but it would explain deadlocks.
Nov
18
awarded  Popular Question
Nov
13
answered Constrain Partial Values in Timestamp
Nov
13
comment Slow execution of a subquery with 0 matches
I think the main reason is ORDER BY authorID in subquery (ignoring the fact that order by is useless in this case as RolandoMySQLDBA pointed out) . Note: there is no Followers.authorId so it's resolved to Posts. authorId
Nov
6
comment Failed to drop column - value too large
Is it any chance you have ddl triggers on database or schema level ?