Most Popular
1500 questions
29
votes
6 answers
How to create a conditional index in MySQL?
How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature.
Example: I want to create an index for NAME column just for rows with…
Maniero
- 2,728
- 6
- 28
- 29
29
votes
3 answers
What are some best practices for using schemas in SQL Server?
I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, but what is typical out there? I often, in my…
anon
29
votes
2 answers
How to transfer data using expdp and impdp commands?
I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp and impdp commands. I have questions regarding this:
Can I create a target…
jrara
- 5,333
- 20
- 56
- 65
29
votes
4 answers
How to update a table from a another table
I have one table A has column (id, field_1,field_2),
and another table B has column (id,field_2)
Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. So how to implement that ? BTW I am using…
zjffdu
- 399
- 1
- 3
- 4
29
votes
4 answers
Why is OS authentication considered poor security for Oracle databases?
Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says
Be aware that the REMOTE_OS_AUTHENT
parameter was deprecated in Oracle
Database 11g Release 1 (11.1), and is
retained only for backward
…
Leigh Riffel
- 23,854
- 16
- 78
- 152
29
votes
4 answers
MySQL InnoDB crash post-mortem
MySQL crashed on me this morning.
With the exception of the standard MySQL included databases everything I use is InnoDB.
I attempted to restart the MySQL daemon but it failed twice.
I then restarted the entire server and MySQL started correctly…
Patrick
- 4,229
- 7
- 28
- 28
29
votes
1 answer
disable constraints before using pg_restore.exe
When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same:
ERROR: insert or update on table "someTable" violates foreign key constraint "aConstraintName"
This is obviously due to the fact that I've…
Stephane Rolland
- 8,641
- 10
- 32
- 40
29
votes
3 answers
Why did MySQL remove the query cache feature after version 8.0?
Why did MySQL remove the query cache feature after version 8.0?
shaoyihe
- 447
- 1
- 4
- 8
29
votes
7 answers
What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?
I'm in a situation where I want to get the minimum value from of 6 columns.
I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like to know which would be better for performance.
The…
Rachel
- 8,477
- 20
- 50
- 74
29
votes
4 answers
Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?
I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result:
Impossible WHERE noticed after reading const tables`
The query I ran:
explain SELECT rid FROM relationship WHERE…
Alireza
- 3,636
- 10
- 37
- 44
29
votes
6 answers
How to restart PostgreSQL server under CentOS 7
I installed PostgreSQL 10.1 under a CentOS 7.3 environment.
The service is started (postmaster.pid file present under /var/lib/pgsql/10/data), but I need to reload configuration or restart the server following a change in pg_hba.conf.
However,…
Sébastien Clément
- 1,805
- 3
- 18
- 28
29
votes
1 answer
MySQL transaction size - how big is too big?
I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.
There are many aspects, and the imports may yield anywhere between 100k-1mil+ records.
This equates to a payload ranging from…
thinice
- 413
- 1
- 4
- 7
29
votes
1 answer
Postgres : Relation does not exist error
I used pg_restore to load my postgres db with a dump file.
I connected to my db with my user :
sudo -u arajguru psql dump
select current_user;
current_user
--------------
arajguru
Now I was able to see all the newly created tables:
dump=> \dt
…
Ayushi Rajguru
- 393
- 1
- 3
- 4
29
votes
2 answers
Why is count(*) slow, when explain knows the answer?
This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I can see a column in the output with the number of…
Benubird
- 475
- 1
- 4
- 8
29
votes
4 answers
Can spatial index help a "range - order by - limit" query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their frequencies:
lexikon
-------
_id integer PRIMARY KEY
word …
ypercubeᵀᴹ
- 97,895
- 13
- 214
- 305