Reputation
4,075
Top tag
Next privilege 5,000 Rep.
Approve tag wiki edits
Badges
3 13 24
Newest
 sql
Impact
~201k people reached

10h
comment Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine
Setting remote_os_authent to TRUE is a very bad idea and your sentences on this should be bold and in red :-) And the parameter is deprecated in 11g and higher.
15h
comment Why is SELECT * much faster than selecting all columns (in a different column order) by name?
I was already thinking about that and hoping so!
15h
comment Why is SELECT * much faster than selecting all columns (in a different column order) by name?
Yes. I would've hoped it would be possible to skip that step or make it shorter by using some "pointer shuffling", but that's a discussion for pgsql-hackers.
15h
comment Why is SELECT * much faster than selecting all columns (in a different column order) by name?
Thanks for responding here, Amit!
19h
comment Ongoing Administration comparison PostgreSQL vs Firebird
You really need to tell us more about your requirements. For example, how will the DBMS be deployed: will this be an embedded DBMS in a product? Deployed on a server at one client? Many clients? Hosted? What data volumes do you expect? How many (concurrent) users? How will backups be managed?
20h
comment How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE?
Sorry, got it! Yet the semantics are what you're missing: It only deletes from "Users", not from the tables in the "using_list" -- they're purely listed to be able to join tables to determine which rows from "Users" need to be deleted.
20h
comment How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE?
Where are you getting that syntax from? I don't see it described in the manual: postgresql.org/docs/9.4/static/sql-delete.html
20h
comment Why is SELECT * much faster than selecting all columns (in a different column order) by name?
If I list all columns in the same order as defined in the table, I get approximately the same times as if I select *.
20h
comment Why is SELECT * much faster than selecting all columns (in a different column order) by name?
I'm seeing differences also, though not as pronounced. My table has rows=514431 width=215, and I get approx 1.5s for the select * case and approx 2.2s for the select with columns listed in a different order.
May
22
comment Periodically update a table with new records from another server
Why do you want to do this? Tell us the business problem you're trying to solve, and maybe we can suggest a good way to implement it.
May
22
comment SQL actually executed as planned in ORACLE?
Same here. Personally I don't like the direction Oracle is going: adding layer upon layer of complexity to mask or try to fix fundamental design flaws. Moving to PostgreSQL has been a revelation for me.
May
22
comment SQL actually executed as planned in ORACLE?
@Vérace Adaptive Joins in 12c is one example, but I believe some other changes could be made on the fly in older versions too. blogs.oracle.com/optimizer/entry/what_s_new_in_12c
May
22
comment SQL actually executed as planned in ORACLE?
Broadly speaking this answer is correct, but each DBMS has salient differences to beware of.
May
22
comment SQL actually executed as planned in ORACLE?
Yes, the question is ambiguous: it could mean 1) Does the executor do something else than what was planned? or 2) Does a subsequent execution always choose the same plan (and execution)?
May
22
comment SQL actually executed as planned in ORACLE?
I have it at home in a folder from a course given by Jonathan Lewis. I'll try to dig it out.
May
22
comment SQL actually executed as planned in ORACLE?
Wrong: Oracle can and does change execution plan once starting execution in certain cases.
May
22
comment SQL actually executed as planned in ORACLE?
Hmm, two answers saying essentially the opposite!
May
21
comment Get the rank of a row based on a score I am generating from other field values
Or you can repeat your expression (table2.field1*0.4 + table2.field2*0.2 + table1.field1*0.7) wherever you would use the score column. (Note that you don't need any of the brackets either due to operator precedence.)
May
21
comment Get the rank of a row based on a score I am generating from other field values
It helps because you can treat your query as the table that you "plugin" to any of the queries in the answer you quoted, eg SET @i=0; SELECT id, name, score, @i:=@i+1 AS rank FROM (<your_query>) ranking ORDER BY score DESC; to use one of the answers as an example.
May
21
comment Get the rank of a row based on a score I am generating from other field values
You do know that you can use a subquery in the from clause?