294 reputation
110
bio website
location London, United Kingdom
age 30
visits member for 2 years, 9 months
seen Oct 13 at 13:15

Officially, I'm a PM, running the UK's largest business database (according to the marketing bumpf). Yes, that does mean I work in business information; feel free to pity me. I'm actually a data(base)?((manag|develop)er|analyst) and an occasional DBA and Python programmer. I'm also a whizz at 4DOS, which I don't get enough opportunity to play around with.

I can normally be found hanging out in Oracle, SQL, and PL/SQL or on meta and avoiding Python as there are a lot amazing answerers there.


Sep
24
awarded  Autobiographer
Sep
18
comment Separate table for 'scheduled payments' and 'completed payments'?
Constraints are the only thing that enforces relational integrity in a database, yes @Thomas. The theoretical benefit is that you can't have a transaction in two states... this is a rather important point for transactions, especially for companies such as banks - it's certainly not a theoretical benefit. The typical strategy for dealing with this is partitioning, as you mention.
Sep
18
comment Separate table for 'scheduled payments' and 'completed payments'?
I realise that not every DB supports partitioning; that has never been my point. A trigger does not enforce anything at all, so this isn't a solution to a lack of integrity... it's another method of attempting to ensure that integrity is maintained but it's not a guarantee.
Sep
17
comment Separate table for 'scheduled payments' and 'completed payments'?
Whatever you do @supercat you cannot enforce it in the database unless the transactions are in one table, as I say multiple times above. This is what my original comment states and is likely the reason for the implementation described by the other answer and the OP. Code, data access layers, personal knowledge about the immutability of objects etc do not change this fact. If it's not enforced then there are zero guarantees. You can try hard to prevent it but you can never be sure. You're yet to contradict this so I assume you agree, at which point I'm unsure what this conversation is about.
Sep
17
comment Separate table for 'scheduled payments' and 'completed payments'?
The last paragraph of this answer; this is the situation that partitions are designed to deal with.
Sep
17
comment Separate table for 'scheduled payments' and 'completed payments'?
New code, old code and manual intervention for example @supercat. If you're not enforcing it then it's not enforced and there will always be ways around whatever code you put in place to stop it.
Sep
17
comment Separate table for 'scheduled payments' and 'completed payments'?
You still can't enforce that a single transaction ID will only be in one table @supercat. This isn't something that should be done in code.
Sep
17
comment Separate table for 'scheduled payments' and 'completed payments'?
Having 2 tables does, however, mean that it's possible for a single transaction to exist twice and you cant enforce that this can't happen.
Apr
22
comment Numbering multiple dates per delivery
If you turn your query there into a sub-select then you don't need to worry about filtering...
Apr
19
comment DISTINCT not using an index
If there's a foreign key it does speed up the query @miracle, there's no need to query the original query; you can query a 5 row table instead. If this table exists you don't need to play around with different indexes, IOTs, ordered indexes, moving tablespaces onto different disks etc., you just query the 5 rows...
Apr
18
comment DISTINCT not using an index
It does @miracle173, by fixing the data-model you make the database work for you rather than having to change the structure of the database. It also means that if you massively increase the number of rows in the table you're not going to have another speed issue.
Apr
13
comment How slow is slow in postgres?
Please don't cross-post... this question is on it's way over there shortly, but now there's no point. If you're doing this then you're not going to be able to put it all on RAM and you're only going to use one core so the biggest potential problem is going to be disk I/O. You need good disks...
Feb
20
comment Database throughful design vs Quick and dirty design comparison
I deal with hundreds of millions of addresses... all I can say is definitely this. More importantly you're highly unlikely to ever get address data of the "quality" implied by the OP - it's all likely to be more like you describe.
Feb
19
comment Oracle idle connections lost
If it only happens remotely it's unlikely to be Oracle's fault. You need to talk to your sysadmins; it can be the server dropping idle connections or maybe some special firewall/proxy settings.
Nov
11
comment Oracle upgrade, v$version what is it actually doing?
Oracle 8i is a little old... (extended support ended almost 8 years ago). Rather than trying to solve whatever problems you have why don't you export the data (as you say you can do) and import it into an 11.2 database?
Oct
1
comment MySQL innodb database creation…why do this?
I don't know how you can expect anyone to tell you whether they were set up by mistake? Did you ask at your company for the reason why?
Sep
18
comment Connecting to Oracle Database with ODBC/Python
The best way to connect to Oracle from within Oracle is cx_Oracle. However, you might want to consider using Jython instead which you could call easily from within Oracle...
Jun
16
answered Call pl/sql function and discard result
Jun
8
awarded  Citizen Patrol
May
20
awarded  Caucus