Reputation
721
Top tag
Next privilege 1,000 Rep.
See vote count; VIP usercard
Badges
4 8
Newest
 Good Answer
Impact
~78k people reached

  • 0 posts edited
  • 0 helpful flags
  • 7 votes cast
Dec
5
awarded  Good Answer
Nov
30
awarded  Yearling
Sep
24
awarded  Autobiographer
May
24
awarded  Nice Answer
Apr
27
comment constraint in oracle
I think you have to write a trigger that check the number of members when inserting or updating on the member table.
Mar
11
awarded  Student
Jan
31
comment How to change table/indexes structure for a query with two aggregated colums
@ypercube I just checked, as you suggested, and they give the exact same timing. Plan show and "Index Scan" when using the DESC ordered index, or a "Index Scan Backward" when using an ASC ordered index.
Jan
31
comment How to change table/indexes structure for a query with two aggregated colums
@ypercube as shown in the answer, having created both indexes with ASC and DESC, Postgresql selected the DESC ones, so I assume it is the best/fastest index.
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Colin'tHart I still have to try with 9, but on 8.3 it was faster with DESC.
Jan
30
revised How to change table/indexes structure for a query with two aggregated colums
typo
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
I just added the reverse order for second field in index, so that MAX function just get the first record indexed.
Jan
30
revised How to change table/indexes structure for a query with two aggregated colums
Index ordered DESC
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Mihai, I believe the index is ordered on these colums, so the max(third columns) is not directly accessible in the index and it require a scan. The two different indexes solve the problem
Jan
30
answered How to change table/indexes structure for a query with two aggregated colums
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Mihai, you are right and I didn't check the plan details. The new index is used in the first part of plan!
Jan
30
revised How to change table/indexes structure for a query with two aggregated colums
Added "addendum 1" results
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@dezso, your comment about the table structure is sadly not acceptable since this schema is used across many different DBMS. Let's say this is the schema that works everywhere (informix, oracle, sql-server, mysql, postgresql). About these limits (like 50 chars in length), they are required by the application.
Jan
30
comment How to change table/indexes structure for a query with two aggregated colums
@Mihai, your suggestion does not work since even with that new index, the plan does not change.
Jan
30
asked How to change table/indexes structure for a query with two aggregated colums
Dec
10
comment Internal query on CDEF$ and CON$ very CPU bound
Statistics are gathered by oracle default job. I just removed these statistics from the tables in this query and now it is really faster.