Archiving of old data We're currently running into some performance problems since our database is getting too big. There are data stored from the last 10 years and I don't see a reason why the data older than 2 years have to be stored in the same tables as the new data.

Now since I don't have very profound experience in administrating databases, I'm looking for the best ways to archive old data.

Info

There are about 310'000'000 records in the database in total.
The database needs 250 GB on the hard disk.
The Server version is SQL Server 2008 with compatibility level SQL Server 2005 (90), but we're planning on upgrading to SQL Server 2012 soon

I've thought about two possibilities:

New Database

Create Database similar to the one on the production server and insert all the old data in the new database.

Disadvantage: Since linked servers are not allowed in our environment, it would be difficult to join the old data if needed

History Schema

Create a new schema f.e. [hist] with the same tables as in the production database. Insert all old data in these new tables in the new schema.

Advantage: Easy joining, if old data would be needed in the future

Do you prefere one of the solutions over the other?

Why?

Are there any better possibilities?
Are there existing tools with which this task is easily possible?
Any other thoughts?

Thanks in advance

Edit

Additional Question:

Would the newly created archive table also need primary / foreign keys?

Or should they just have the columns but without keys / constraints?

I think the answer to many of your questions is that it depends. What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB in size.

Perhaps your queries are performing table scans on the entire fact table even when only a small portion (e.g., the last year) of the date range is needed? If there is a particular query that is most important to optimize, considering posting your schema, query, and an actual execution plan in another question to see if it can be optimized.

    Do you prefer one of the solutions over the other?

I generally prefer the history database, and I think Guy describes good reasons for this in his response.

The primary disadvantage I see for a history database (as opposed to a schema) is that you can no longer use foreign keys for your archive table. This may be fine for you, but it's something to be aware of.

The disadvantage you listed for this approach is not accurate; you will be able to query across databases on the same server easily and the query optimizer generally handles cross-database queries very well.

    Are there any better possibilities?

If you need to query the archive data regularly, I might consider partitioning the table by date. However, this is a big change that can come with a lot of performance implications, both positive (e.g., partition elimination, more efficient data loading) and negative (e.g., slower singleton seeks, greater potential for thread skew in parallel queries). So I wouldn't make this decision lightly if it's a heavily used database.

    Would the newly created archive table also need primary / foreign keys?
    Or should they just have the columns but without keys / constraints?

I would recommend having at least the primary key and unique indexes so that you can get the data integrity benefits they provide. For example, this will prevent you from accidentally inserting a year of data into the history table twice. And as a side benefit it may improve performance if you do need to query the history table.

    Any other thoughts?

Since you are using Enterprise edition and planning to upgrade to SQL 2008+, you might consider data compression for this table. Compression will certainly reduce disk space, but depending on your server's disk and CPU resources it may also improve query performance for reads by reducing disk I/O and improving memory utilization (more data fits in cache at once).


Ignore white spaces for comparison in unique constraints of Oracle How can I tell to Oracle DBMS to ignore white spaces while comparing varchar values for Unique Constraints.

It means that NAME 1 is exists in a table column with Unique Key, inserting NAME1, causes constraint violation exception

You can't do this with a unique constraint but you can do this with a unique index:

create unique index ix_name
   on the_table(regexp_replace(the_column, '\s+', ''));

If you want a case-insensitive index (where name 1 and NAME 1 are taken as the same value) you need to convert everything to lower or uppercase:

create unique index ix_name
   on the_table(upper(regexp_replace(the_column, '\s+', '')));


Distributing a semi-large key/value list I have a semi-large (around 30 million records) key/value list which I want to load into redis.

The data is stored in PostgreSQL (as master data) and we want to distribute this to clients for faster (local) lookups.

The keys are Belgium telephone numbers (national format, so strings between 9 and 10 digits). The values are 5 letter strings.

There would be a redis-server on each client (around 10 clients per customer). The full dump will distributed once or maybe twice a year, while the updates are between 100-1000 a day (only business days between 08:00 and 18:00).

Is this an efficient way to do this?

Side remark: if redis isn't the correct choice, other suggestions are also welcome.

An RDB would be the fastest way to replace/create an entire dataset. You can prepare the bi-annual forklift update beforehand at HQ at your convenience and ship it out to the customers. Do note that loading from RDB requires downtime.

As for the 0.1-1K updates/day per client-local Redis database - some of the real questions are:

How many updates are done to the master database?
Are all updates applied to all client/local databases?
Can the updates be batched?
Is there a latency budget for updates to be shipped to clients?
10 clients/database per customer, but how many customers overall?

I assume there are many possible solutions to this challenge, but I'm for KISSing it pimple :)


ProxySQL user not connecting - XtraDB I'm trying to set up a xtraDB Cluster consisting of 3 nodes. Now i've got the cluster up and running and per the instructions i'm trying to set up ProxySQL for load balancing.

So i've installed proxySQL on all 3 nodes. And now i'm trying to configure it with the admin tool. But everytime that i run the command:

[root@node1 log]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable

It comes back with:

This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)

ProxySQL read/write configuration mode is singlewrite
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxysql_admin'@'' (using password: YES)
Please check the ProxySQL connection parameters! Terminating.

Now i would say the error is pretty straight forward. However i have the user proxysql_admin properly defined in both the MySQL cluster and in the .cnf files.

Here's my proxysql-admin.cnf file:

# proxysql admin interface credentials.
export PROXYSQL_DATADIR='/var/lib/proxysql'
export PROXYSQL_USERNAME='proxysql_admin'
export PROXYSQL_PASSWORD='placeholder_pass'
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT='6032'

# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME='proxysql_admin'
export CLUSTER_PASSWORD='placeholder_pass'
#export CLUSTER_HOSTNAME='localhost'
export CLUSTER_HOSTNAME='ccloud'
export CLUSTER_PORT='3306'

# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME='monitor'
export MONITOR_PASSWORD='placeholder_pass'

# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME='proxysql_user'
export CLUSTER_APP_PASSWORD='placeholder_pass'

# ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID='10'
export READ_HOSTGROUP_ID='11'

# ProxySQL read/write configuration mode.
export MODE="singlewrite"

# ProxySQL Cluster Node Priority File
export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf

Here's the user on the mysql cluster:

mysql> select User,Host from mysql.user;
+----------------+-----------+
| User           | Host      |
+----------------+-----------+
| proxysql_admin |           |
| proxysql_admin | %         |
| mysql.session  | localhost |
| mysql.sys      | localhost |
| proxysql_admin | localhost |
| proxysql_user  | localhost |
| root           | localhost |
| sstuser        | localhost |
+----------------+-----------+

I've tried changing up the interfaces a few times but that doesn't seem to be the problem.
Anyone have any idea why my user isn't connecting?

Just in case, here's the my.cnf of the cluster on the node i'm trying this on:

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[mysqld]
server-id=1
datadir=/mysql-data
socket=/mysql-data/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_name=ccloud
wsrep_cluster_address=gcomm://ip1,ip2,ip3

wsrep_node_name=pxc1
wsrep_node_address=ip1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:placeholder_pass

pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

Looks like your proxysql db doesn't have user proxysql_admin. Please use default proxysql user credentials (admin/admin).


Why I cannot let EXPLAIN update statements? I can imagine that it's actually more important to explain SELECT statements, but I wonder why there's no possibility to use EXPLAIN on UPDATEs/INSERTs.  I may use a WHERE-statement in an UPDATE where indexes are being used (I guess?), it should be possible to see if they are actually used.  Additionally, in the case of using a SELECT-statement inside an UPDATE, I also cannot use explain for this part without getting a syntax error.
Did I misunderstand something about the processes on UPDATEs and INSERTs?  How else can I else debug those statements?

MySQL currently doesn't support this (unlike nearly all other DBMS).

I think this will be in 5.6 but I'm not entirely sure.

I don't think there is any workaround for that (except upgrading to a DBMS that does support this)


Changing TEXT to VARCHAR In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text.

Currently the longest entry has length 360  (found using SELECT MAX(LENGTH(value)) ) and the theoretical biggest value would be coming from a varchar(511) column.
There is no index on the column.

I was advised to try and see if we can move this column from text to something else, but I'm struggling to find out if this will help at all. I have done some research of course, but I would like too see if I'm correct. I know 'random advice' is not too be taken too seriously, but at first glance it seemed to me it has some merit: text is big, and max of 511 chars isn't that big.

But, as far as I can find, (bigger?) varchar/varbinary and text/blob are handled the same when using InnoDB. The issue one might face with large values is that not everything is stored in the same 'row' (page? not sure of the terminology here) on the disc when using text. There are several sources that say this is a misunderstanding as some of the text is actually saved in the same place a normal row would be saved. (I hope I'm describing this right)

In the end, my conclusion was that there isn't any specific extra value in changing this column to some sort of varchar (probably varchar(511)) at the moment.

This sounds vaguely familiar. Oh yea, I wrote about this back on Aug 14, 2012  : error 1118: row size too large. utf8 innodb. Here is additional information from a Percona Expert on this subject : InnoDB create table error: “Row size too large”

I can see this putting a strain on an InnoDB table in terms of row length limits. Turning TEXT fields of ominous lengths into VARCHARs or even CHARs in InnoDB will have no effect (at least, no positive effects).

Your concern is quite valid and merits some respect. Why?

Some would do one of the following:

Perform the conversion and deal with (mostly complain about) worse consequences
Perform the conversion in a Development server, see the effects, and judge accordingly
Ask why, do research, carry out due diligence in a Development server

It sounds like you did Option #3 from the above list. Your conclusion, given the lengths of data, is correct for your given dataset.


Performance difference for COALESCE versus ISNULL? I've seen a lot of people use the COALESCE function in place of ISNULL.  From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when using it.  However, ISNULL seems easier to read since it seems more clear what it's doing.

I also realize that ISNULL is kind of tricky since it acts differently on different database servers and in different languages.

All of that, in my mind, boils down to style and standards.  Given that style is subjective, is there any reason to use COALESCE over ISNULL (or vice versa)?  Specifically, is there a performance advantage of one over the other?

ISNULL is Sybase/SQL Server specific
COALESCE is portable

Then

ISNULL take 2 arguments
COALESCE takes 1-n arguments

Finally, and the fun bit. The result datatype and length/precision/scale

ISNULL is the same as the first argument
COALESCE is the highest according to datatype precedence

This last bit is why ISNULL is usually used because it's more predictable (?) and COALESCE can add unintended data type conversions: which is where the "it's slower" bit comes from

DECLARE @len10 varchar(10); --leave it NULL
SELECT
    ISNULL(@len10, '0123456789ABCDEF'),     -- gives 0123456789
    COALESCE(@len10, '0123456789ABCDEF');   -- gives 0123456789ABCDEF

All datatypes being the same, you won't see any practical difference...


How to stop the execution of a long running insert query? I've executed an INSERT INTO ... SELECT as a job on my MySQL instance and then I realized that number of records that is selected by the WHERE condition is more than my patience. It's already taken 12 hours and there's no sign of the job being done.

My previous experience tells me that there's a big chance to corrupt the data if I just kill / stop the MySQL instance. And I know no other way to do so! Does anyone know a safe way to gracefully ask MySQL to stop whatever it is doing?

And I'm using InnoDb and MySQL 5.6 on Ubuntu.

Do you mean by "kill / stop the MySQL instance" the linux command kill -9 ...?
Yes, this might lead to corrupt data.

Which instead should not leave corrupt data is the build-in kill command of MySQL. See the lower part of the page, which states that it might take the thread some time to actually notice the presence of the kill flag. The part which states it does not roll back updates when transactions are not used (which implies that it rolls back updates if transactions are used), etc. I therefore assume this does not corrupt any data (besides killing repair and optimize as noted there).

You can find out the thread id you need for this command with the command show processlist.


MySQL partitioned tables? I have a database that supports a web application with several large tables.  I'm wondering if partitioned tables will help speed up certain queries.  Each of these tables has a colum called client_id.  Data for each client_id is independent from every other client_id.  In other words, web queries will always contain a where clause with a single client_id.  I'm thinking this may be a good column on which to partition my large tables.

After reading up on partitioned tables, I'm still a little unsure as to how best to partition.  For example, a typical table may have 50 million rows distributed more or less evenly across 35 client_ids.  We add new client_ids periodically but in the short term the number of client_ids is relatively fixed.

I was thinking something along these lines:

CREATE TABLE foo (
    id INT NOT NULL PRIMARY KEY,
    ... more column defs here...
    client_id int
)
PARTITION BY KEY(client_id)
PARTITIONS 35;

My question.  Is this an optimal strategy for partitioning these types of tables?  My tests indicate a considerable speedup over indexing on client_id, but can I do better with some other form of partitioning (i.e. hash or range)?

Partitioning could quite possibly help you in this situation.  By putting the data into a partition, the database will have a smaller table for each client to deal with.  This could help performance a lot.

Here is an example of how it would help.  The data in the table would otherwise be interleaved, so different clients would have rows on a data page.  Reading the data from a single client would then "clutter up" the available page cache with records from other clients.

However, it will not help very much if all the clients are accessing data concurrently, so all are competing for available memory.  Partitioning probably will not make this worse, but it won't necessarily help.

Also, MySQL automatically partitions the indexes on the table, which is generally a good thing.

Often partitioning is applied to a date field, so older data gets put into less used partitions.  This may be a case where another field is useful.  But, if all the clients need all their data at the same time, then the partitioning may not give you much of a gain.


MySQL: Filter results by joining a vertical table Situation: I've got a table users and a table preferences along with some others. The table users is aligned horizontally (id, username,...) whereas the table preferences is aligned vertically (user_id, preference_key).

Table users has about 300K rows, preferences about 60M (about 20 out of 100 preferences per user)

Task: Find me all users that have a given combination of preferences (and/or pattern)

My first approach is this, run by a subquery

SELECT users.id FROM users LEFT JOIN ..other tables .. WHERE .. AND users.id IN (
   SELECT t1.id FROM
  (
     SELECT user_id as id, count(*) as total
     FROM preferences
     WHERE pref_key IN (key1, key2, key3, etc..)
     GROUP BY id
     HAVING total > 3
  )
)

having > 3: number of keys to match in an and-pattern, >0 for or-pattern

This approach is rather slow and I don't know if this approach is any good at all, as it looks complex with the subquery and IN () arguments.

For the performance - way more reads than writes - is the vertical alignment of the table structure optimal?


How to force MySQL master to overwrite the slave We have a LAMP architecture website.

Currently we have a production server running on the cloud and each developer has a complete copy of both the code and the database. Code is synced through Git and it's working fine. However, the database is synced by dumping the production server nightly and import everything into the local database. As the database grows larger, this process is taking unreasonably long time.

So I tried to set up a master slave replication where the production server is the master and my developing workstation as a slave. The problem is, during developing, we have to write to the slave for testing purpose. And some write operations causes errors during the replication such as primary key conflict, etc.

I am wondering if there is a way, other than dump/import, with master-slave replication where we can force the master to overwrite the slave in case any conflicts happens. For example, during the replication, the slave should delete anything that has conflict with the master and apply the changes happened on the master.

Or if this is not possible, is there a better way that I can refresh my local database with the server? This does not need to be 100% identical or real-time. I understand that the process I described above may lead to more discrepancies over time if the local changes do not cause conflicts. This can be solved by dump/import method as long as this does not happen too frequently.

I use nightly lvm snapshots of production databases that are made static by removing the files needed for replication (master.info, relay-logs), and making that snapshot writable. You'll need to shut mysql down for a few minutes to make the snapshot and have room on the machine to hold that snapshot, but it's an easy and elegant solution.


Display column title as in the query without 'AS' statement I query data from multiple tables using JOIN statements like this :

SELECT u.id,u.name,d.id,d.name FROM user u RIGHT JOIN dog d ON... ;

When I get the answer, in psql for example, I've got this kind of rows title :

|  id   | name  |  id  |  name  |
+-------+-------+------+--------+

Instead, I'd like to get the original and precise notation :

|  u.id   | u.name  |  d.id  |  d.name  |
+---------+---------+--------+----------+

For now I have to use AS statements, but it's quite repetitive to re-write and it add length to the query and makes it less readable.

Is there a way to get this result with a SQL keyword or with a DBMS parameter?

I searched but wasn't able to find anything on this specific problem.

There is no built-in method for modifying the column names displayed in the results of a query.  This is by-design.

Use the AS <alias> construct to control the column-names.  Format your query for readability, as in:

SELECT u.id         AS "u.id"
     , u.name       AS "u.name"
     , d.id         AS "d.id"
     , d.name       AS "d.name"
FROM user u
    RIGHT JOIN dog d ON...;

Alternatively, you could rename all the columns, prefixing them with a different prefix for each table, or making them unique more descriptively. For example: dog_id, dog_name, user_id, user_name. This would work for almost all cases (except self-joins).

Be aware that modifying the column names after you've implemented your database into production will result in a vast amount of work ensuring you've changed all affected code both inside and outside the database. On the other hand, renaming generic names like id or name would certainly be beneficial in the long run. I would expect the benefits to outweigh the trouble of going through code to fix the names after the renaming.

As an aside, the practice of using id as the name of the surrogate key for every table results in a spaghetti mess of unreadable and unintelligible code that is error prone.  Take for example:

CREATE TABLE T1
(
    id int PRIMARY KEY
);

CREATE TABLE T2
(
    id int PRIMARY KEY
    , t1_id int
);

CREATE TABLE T3
(
    id int PRIMARY KEY
    , t1_id int
    , t2_id int
);

SELECT *
FROM T1
    INNER JOIN T2 ON t1.id = t2.id
    INNER JOIN T3 ON t1.id = t2.id AND t2.id = t3.id
WHERE t2.id = 1;

Pretty clearly, the above query should be written as:

SELECT *
FROM T1
    INNER JOIN T2 ON t1.id = t2.t1_id
    INNER JOIN T3 ON t1.id = t2.t1_id AND t2.id = t3.t2_id
WHERE t2.id = 1;

If you do this instead, it becomes far clearer from the outset what is intended:

CREATE TABLE T1
(
    t1_id int PRIMARY KEY
);

CREATE TABLE T2
(
    t2_id int PRIMARY KEY
    , t1_id int
);

CREATE TABLE T3
(
    t3_id int PRIMARY KEY
    , t1_id int
    , t2_id int
);

SELECT *
FROM T1
    INNER JOIN T2 ON t1.t1_id = t2.t1_id
    INNER JOIN T3 ON t1.t1_id = t2.t1_id AND t2.t2_id = t3.t2_id
WHERE t2.t2_id = 1;

Added bonus; the output now shows somewhat more explicit column names:

╔═══════╦═══════╦═══════╦═══════╦═══════╦═══════╗
║ t1_id ║ t2_id ║ t1_id ║ t3_id ║ t1_id ║ t2_id ║
╚═══════╩═══════╩═══════╩═══════╩═══════╩═══════╝

And as you are using PostgreSQL, you can also benefit from making the join conditions more compact by using the USING clause:

SELECT *
FROM T1
    INNER JOIN T2 USING (t1_id)
    INNER JOIN T3 USING (t1_id, t2_id)
WHERE t2.t2_id = 1;

Not only do your queries become arguably more readable this way, there is also effect on the output: the join columns are not repeated. The above statement would produce output like this:

╔═══════╦═══════╦═══════╗
║ t1_id ║ t2_id ║ t3_id ║
╚═══════╩═══════╩═══════╝


Multiple string replacements I've got a replacement map table

CREATE TABLE #ReplacementMap (old NVARCHAR(10), new NVARCHAR(10))
INSERT INTO #ReplacementMap VALUES ('A',5)
INSERT INTO #ReplacementMap VALUES ('C',9)
INSERT INTO #ReplacementMap VALUES ('D',4)

and a table of strings

CREATE TABLE #String1 (name NVARCHAR(50), string1 NVARCHAR(100))
INSERT INTO #String1 VALUES ('John','AB')
INSERT INTO #String1 VALUES ('Kyle','ABC')
INSERT INTO #String1 VALUES ('Steven','ABCD')

in which I need to replace bits of string based on the replacement map table so that I get the below results:

John,5B
Kyle,5B9
Steven,5B94

My current solution is to nest REPLACE function but due to the number of replacements that I need to do, it's not an elegant way to it.

A SQLCLR function could be used to emulate the TRANSLATE Transact-SQL function new to SQL Server 2017.

Function definition

CREATE ASSEMBLY [Translate] AUTHORIZATION [dbo]
FROM [encoded data, 9218 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[Translate]
(
    @Input nvarchar(4000),
    @Find nvarchar(4000),
    @Replace nvarchar(4000)
)
RETURNS nvarchar(4000)
AS EXTERNAL NAME
    [Translate].[UserDefinedFunctions].[Translate];

Usage

SELECT
    S.[name],
    S.string1,
    Result = dbo.Translate(S.string1, N'ACD', N'594')
FROM #String1 AS S;

╔════════╦═════════╦════════╗
║  name  ║ string1 ║ Result ║
╠════════╬═════════╬════════╣
║ John   ║ AB      ║ 5B     ║
║ Kyle   ║ ABC     ║ 5B9    ║
║ Steven ║ ABCD    ║ 5B94   ║
╚════════╩═════════╩════════╝

This simple demo implementation uses a case-sensitive comparison.

Source code

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [SqlFunction(
        DataAccess = DataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true,
        SystemDataAccess = SystemDataAccessKind.None
    )]
    [return: SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
    public static SqlChars Translate
        (
            [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
            SqlChars Input,
            [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
            SqlChars Find,
            [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
            SqlChars Replace
        )
    {
        if (Input.IsNull || Find.IsNull || Replace.IsNull)
        {
            // Return unchanged input for any NULL parameters
            return Input;
        }

        if (Find.Length != Replace.Length)
        {
            throw new ArgumentException("Find and Replace parameters must have the same length.");
        }

        // For each character in the input string
        for (int i = 0; i < Input.Length; i++)
        {
            // For each character in the Find string
            for (int j = 0; j < Find.Length; j++)
            {
                // If the character matches...
                if (Input[i] == Find[j])
                {
                    // ...replace it
                    Input[i] = Replace[j];
                }
            }
        }
        return Input;
    }
}


How to efficiently extract data from a csv file and save it in mysql I have a csv file, maybe 1000 or more rows, and echo row’s data is related to two table in mysql. Example,

table `book`(id, title, …)
table `scene`(id, book_id, short_content, position…)

And the csv file look like:

title,short_content1,position1,short_content2,position2,…
...

I want to extract data from it and save it to mysql. Also in the mysql maybe already have the same record, so I don’t want save the same data to mysql.
 My solution is:

First, fetch all the data related to the file, a big data set labeled BS.
Second, for each row in file compare to the BS and create the book, and store the book_id for his scene.
Finally, batch create the scene.

This is my poor solution, and if the csv file has 1000 rows, then the solution will take a large memory space and 1000 + 1 times mysql calls.

I see Bulk database update/insert from CSV file and Update Oracle database from CSV post. But I still confuse how to deal with csv file which related to multiple tables.

Can anyone give me a better solution.
 Thanks.

If there are several rows with a consistent number of columns in the CSV, then

CREATE TABLE with lots of columns
LOAD DATE into it.
Do "unpivot" code to rearrange it into the 'real' tables you want.

If there is only one row in your CSV, then write code in your favorite programming language to break it apart.

Not sure what to do if you have an inconsistent number of columns.

You can use INSERT IGNORE and/or IODKU` to deal with duplicates.


How to set a flag in SQL, when there is a change in value I have column which tells fee rates.
When there is change in value in that column, I need to set a flag for that corresponding row.
How can i check whether there is change in value from previous row and current row and flag it accordingly. There are l0's of thousands of records.

Please find below, when rate changes, then that row I have to flag.

It depends on what you want to do based on that flag.

If you only care that the row has "changed" at some point, then a flag might be enough.
If you care when the change happened, then a [nullable] timestamp might be more useful.
If you care if the change happens more than once, then you need a whole new table to record all of those changes, possibly even with "before" [and "after"] values.

In any case, the answer is the same - a Trigger.


can't backup 11.2.0.2 database after catalog upgrade to 11.2.0.4 (main question is 4th)

1.
We today have almost all databases running with Oracle 11.2.0.2.
Actually we have an RMAN database created (in 11.2.0.2) and a Catalog with user (called RMAN112)

2.
We are now installing new databases with version 11.2.0.4.

3.
What should we do in order to backup these new database ?

should we create a new user in the current RMAN databases ? (rman114 ?) even if those RMAN db's are running 11.2.0.2 ?
should we create a new RMAN database running 11.2.0.4, and new catalogs, dedicated for these 11.2.0.4 databases ?
should we upgrade the current catalog and how ?

I've just tested to register a new 11.2.0.4 database in the current catalog (11.2.0.2), it works, but then i had to do an "upgrade catalog" twice.
only problem : the backup of all the 11.2.0.2 databases now fail with errors like :

RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/21/2014 16:15:34
RMAN-06004: ORACLE error from recovery catalog database: ORA-04068: existing state of  packages has been discarded
ORA-04061: existing state of package "RMAN112.DBMS_RCVMAN" has been invalidated
ORA-04065: not executed, altered or dropped package "RMAN112.DBMS_RCVMAN"
ORA-06508: PL/SQL: could not find program unit being called: "RMAN112.DBMS_RCVMAN"
How do i fix that ? and what is the best practice ?
after running a few "resync catalog;" it looks like i can backup a few 11.2.0.2 instances, but i don't really understand how it works..

thanks a lot

regards,

You should be able to backup an 11.2.0.2 database using RMAN with an RMAN repository that is set to 11.2.0.4. There seems to be some invalid objects in your RMAN repository schema. You may want to try compiling all of the invalid objects in your RMAN repository to see if they become valid. This probably has nothing to do with you backing up an 11.2.0.2 database with an 11.2.0.4 RMAN repository.


How can I connect to an Oracle Autonomous Data Warehouse with a 3rd party IDE (DataGrip)? I'm trying to connect to a Oracle Autonomous Data Warehouse database with Jetbrains DataGrip. Oracle provides me with a wallet file (a zip), with contains tnsnames.ora, a keystore, ojdbc.properties, and some other files.

I'm having a lot of trouble using this information to connect to the database using DataGrip. I found a thread on the DataGrip support forums, but I'm not having any luck with that either.

Jetbrains support thread: https://intellij-support.jetbrains.com/hc/en-us/community/posts/360001792539-Connect-with-Oracle-Cloud
Relevant Oracle documentation: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/adwud/connect-using-client-application.html

What I did:
1. Created the 'TNS_ADMIN' environment variable and set it to:
C:\\Users\\xxx\\Documents\\[folder with wallet files]
2. Added the Oracle JDBC driver files (ojdbc8.jar, osdt_cert.jar, oraclepki.jar, osdt_core.jar) to the standard Oracle driver in DataGrip
3. edited the 'sqlnet.ora' file to include the path to the wallet files
4. Added the following to the Data Source VM Options:

-Doracle.net.tns_admin=C:\\Users\\xxx\\Documents\\[folder with wallet files]
-Djavax.net.ssl.trustStore=truststore.jks
-Djavax.net.ssl.trustStorePassword=[password]
-Djavax.net.ssl.keyStore=keystore.jks
-Djavax.net.ssl.keyStorePassword=[password]
-Doracle.net.ssl_server_dn_match=true
-Doracle.net.ssl_version=1.2

Set connection type to URL only
Tried different connection strings in the URL field:

jdbc:oracle:thin:@//adb.eu-frankfurt-1.oraclecloud.com:1522/xxxxxx_adw1_high.adwc.oraclecloud.com?TNS_ADMIN=C:\\Users\\xxx\\Documents\\[folder with wallet files]

jdbc:oracle:thin:@xxxxxx_adw1_high.adwc.oraclecloud.com?TNS_ADMIN=C:\\Users\\xxx\\Documents\\[folder with wallet files]

jdbc:oracle:thin:@//adb.eu-frankfurt-1.oraclecloud.com:1522/mnr6yzqr22jgywm_adw1_high.adwc.oraclecloud.com

Result:

Connection to ADW1 failed.
[08006][17002] IO Error: Got minus one from a read call, connect lapse 32 ms.,
Authentication lapse 0 ms.

I have also tried using the 'Service name' and 'TNS' connection types and filled in the info from tnsnames.ora. No dice, same error.

Also tried explicitely setting the 'tcp.validnode_checking' parameter to null.

(The connection works fine with sqldeveloper)

What's the proper way to do this?

DataGrip 2021.1 provides Oracle 21.1 JDBC driver with all required jar files.
Read DataGrip help about Oracle connection using wallets.
There are some additional Oracle references.


Clustered index update, how changing the index only at different column value? I've table,
example as

Id int primary key(clustered index)
Name varchar(255)not null (non-clustered index)
..
..
Other columns

I execute stored procedur as

update table1 set table1.Name=isnull(@Name,table1.Name) ,( updating other columns) where Id=@Id;

many times Name equals @Name;
In execution plan i saw

if i change stored procedure as

update table1 set ( updating other columns) where Id=@Id;

i saw Cost:15%

Question:

I need to change this procuder for two situation when

table1.Name= @Name
table1.Name <> Name.

In first case table1.Name doesn't change value or index doesn't update and clustered index update cost 15%;

in second case table1.Name changes value and clustered index update cost 50%.

Why index is updating when update a equal value?

i can't select "Name" and compare befor updatings

I know that create varchar column  index isn't good idea

Update

update query with change Name column(execution CPU time 200ms)

update query without change Name column(execution CPU time 70ms)

Update

maybe anybody can help with trigger if it can help with this issue?

Update

Question is closed

how many record will updated at one go and how frequently ?

  Why index is updating when update a equal value?

Because optmizer is not smart enough to detect if you are updating same value.

It is good that index is rebuild of its own,though it is time consuming.

If you disable index then you have to rebuild it later.

your situation is very serious ,1.one row, 30 000 per minute
Hope you have handle the requirement correctly.

As by definition of index or disadvantage of index is that
it slow down update statement if that table is so frequently updated.

Now you have to answer how frequently Name is use in predicate and how.is index helping you there.Depending upon all this it is better to drop index on Name.

Don't keep any non clustered index in these column which are use in this proc.

since @name 10% is null you need dynamic sql whether you drop index or not.
it will improve your query on 90% occasion.

sp_executeSql example (just an example)

declare @Name varchar(250)
declare @othercol varchar(250)
declare @Id int=1

DECLARE @ParmDefinition nvarchar(500);
set @Sql='update table1 set'

if (@Name is not null)
set @Sql=@Sql+' Name=@Name1 ,'

set @Sql=@Sql+' othercol=@othercol1 '

set @Sql=@Sql+' where Id=@LocalID '

SET @ParmDefinition = N'@LocalID int , @Name1 varchar(250) , @othercol1 varchar(250)';

print @Sql

EXECUTE sp_executesql @Sql, @ParmDefinition,
                      @LocalID = @Id,@Name1=@Name,@othercol1=@othercol ;


Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view? Sorry for the long question, but let me give you my scenario.  I have a DB that houses data exported from another DB.  The containing tables are copies from the other (non-SQL) database.  Each column name in these export tables is named using the following pattern:

UserFriendlyName_OriginalColumnName

Since our user's are primarily interested in the UserFriendlyName portion of the column, we have created a View for each table, where the name of each field is strictly the UserFriendlyName portion of the column name.

Most of these Views are strictly queries of one data table.  For some of these views, however, I link to one other data table to pull out a description field or two.  Note, this is never more than one linked table.

Performance wise, which method if better?  If I am linking one data table to another for a description field, can I link straight to the description tables View so that I don't have to alias the column?  Is it significantly better to use two data tables in the View and alias each column from the parent tables?

This may seem a bit arbitrary but this data exportation project is getting bigger and bigger.  I'd like to formalize my process in how I link my information together.  The goal is to strictly use the Data Views if and when possible.  However, if this adds a sizable amount of performance degradation, I'd rather stick with always using Data Tables in all of my Data Views.

I might be misunderstanding your question. Are you asking if given the following tables and views:

CREATE TABLE dbo.Foo
(
    [col1_abc] INT
    , [col2_def] INT
)
GO

CREATE TABLE dbo.Bar
(
    [col1_ghi] INT
    , [col2_jkl] INT
)
GO

CREATE VIEW dbo.vwFoo
AS
SELECT
    [col1_abc] AS col1
    , [col2_def] AS col2
FROM
    dbo.Foo
GO

CREATE VIEW dbo.vwBar
AS
SELECT
    [col1_ghi] AS col1
    , [col2_jki] AS col2
FROM
    dbo.Bar
GO

Will this:

SELECT
    f.col1
    , f.col2
    , b.col2
FROM
    dbo.vwFoo f
INNER JOIN
    dbo.vwBar b
ON  b.col1 = f.col1

perform comparably to:

SELECT
    f.[col1_abc]
    , f.[col2_def]
    , b.[col2_jki]
FROM
    dbo.Foo b
INNER JOIN
    dbo.Bar b
ON  b.[col1_ghi] = f.[col1_abc]

If that's the question the answer is yes, the performance will be identical.

In SQL2000, the optimiser sometimes struggled with deeply nested complex views joining to each other but not with simple scenarios such as this. I've not encountered the problem so much in 2005+.


mariadb Unknown system variable 'server_uuid' Im trying to setup replication between mariadb(10.1.31-MariaDB - MASTER) and mysql(5.7.12-log - SLAVE). After executed the start slave, I got the below error.

  Fatal error: The slave I/O thread stops because a fatal error is
  encountered when it tries to get the value of SERVER_UUID variable
  from master.

Then I tried to get the value of master_uuid.

MariaDB [(none)]> show variables like '%server_uuid%';
Empty set (0.00 sec)

Then tried to setup this in mysql prompt.

MariaDB [(none)]> set global  server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33;
ERROR 1193 (HY000): Unknown system variable 'server_uuid'

And also I tried to setup auto.cnf in data directory.

[auto]
server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33

chmod 644 auto.cnf

But still im getting the fatal error is encountered when it tries to get the value of SERVER_UUID variable from master.

MariaDB doesn't have that variable, MySQL has. MariaDB is not a drop-in replacement for MySQL. The error you see makes me think that replication from 10.1 to 5.7 is not possible.

One could investigate more, but take a look at this compatibility table: there is not an explicit incompatibility statement, but at least they don't guarantee that replicating from 10.1 to 5.7 is possible.

Even if you choose to assume that such replication is possible and find a way to set it up, MySQL supports some syntax that MariaDB 10.1 does not support - not because it's more advanced, but because they took different directions. Such statements will break replication.


DIRTY_PAGE_POLL without activated Indirect Checkpoint Since I encounter some performance issues on my SQL Server 2014 (12.0.2370.0 (X64)) I'm dealing with enormes amounts of different wait types.

One of the top 5 wait type is "DIRTY_PAGE_POLL".

I've read this wait type will only appear when I activate the "Indirect Checkpoint" feature, which is a part of the sql server since sqlserver 2012.

None of my databases has set the Target Recovery Time.

So, why is my SQL Server polling for dirty pages without the indirect checkpoint feature?

According to SQLSERVER-DBA.COM

The background-thread is waiting to poll dirty pages even when indirect checkpoints are disabled (which is the default setting of each database)

  The interesting aspect of DIRTY_PAGE_POLL wait type , is it still appears as a wait type, even though Indirect Checkpoint is not enabled, as the background thread  is waiting to poll for dirty pages.


Design with inheritance and two 1 to many relationships to the same table Quick schema overview:
Below is a schema being a subject of my question. In the design there are two "regular" tables questions and notes as well as two "abstract" tables:

elements, with "concrete" implementations:

video_clips, podcasts, books, articles

markers with following "implementations":

book_pages, article_pages and time_frames

What I would like to achieve:
I would like to be able to put a note or a question in an element. But along with the information that let me find it quicker, thus f.e. if book was my element, I would use a page marker (book_page). If podcast was my element I would use a minute marker (time_frame).

To give an example: Let's say I have:

a book B1 with 100 pages
a book B2 with 200 pages
a podcast P1 that lasts 8 minutes

Now I want to be able to put a note for page number 21 for the book B1 saying: Unlike many other metals, iron does not form amalgams with mercury. - f.e. because that statement drew my attention while reading the book and I want to highlight that by putting a note with that sentence, and connect it to B1 p. 21.
Let's say in podcast P1 in minute 4 I heard a sentence: Bank A has better mortgage plan than bank B because X Y Z. I want to be able to put a question related to P1 and minute 4 saying: Why X is better in bank A and not C?

Problems:
There are two important topics related to that design:

Inheritance
Two 1 to many relationships to the same table

I could get rid of the markers table, and relate book_pages with books, article_pages with articles and time_frames with both podcasts and video_clips. The problem is that I would have two FK in time_frames (let's assume it's acceptable with CHECK constraint) and three foreign keys in both notes and questions (book_page_id,article_page_id,time_frame_id) - and that is unacceptable. I've introduced markers table to solve that issue and have only one FK in notes and questions. Unfortunately that comes with high price, because now querying for an element needs twice more joins (for elements and markers).

I am not looking for the perfect solution because giving the problems there isn't one, I know I need to adopt the compromise but the question is if my compromise/design is acceptable or not ?

I think you can (and should) simplify this. Class inheritance doesn't make as much sense in SQL (DDL) as it does in OO programming languages. Instead, you should follow normalization rules.

I'd suggest a solution like this:

CREATE TABLE media (
  media_id int unsigned auto_increment PRIMARY KEY,
  media_type enum('book', 'article', 'podcast', 'video') NOT NULL
  -- Other media columns here
  -- If you need a few columns specific to one media_type, that's OK
);

CREATE TABLE media_comment (
  media_comment_id int unsigned auto_increment PRIMARY KEY,
  media_id int unsigned NOT NULL,
  comment_type enum('question', 'note') NOT NULL,
  marker int unsigned COMMENT 'unit: seconds for video/podcast, page for book/article',
  comment_body TEXT NOT NULL,
  CONSTRAINT media_comment_fk1 FOREIGN KEY (media_id) REFERENCES media(media_id)
);

This has the advantage of significantly simplifying your queries as well as being easier to read / understand.

If you need a few columns specific to certain media types, that's OK. These columns will then be NULL for the other media types. The overhead of NULL values is very small, though varies with database system / storage engine. See e.g. here for an explanation of the InnoDB storage engine in MariaDB and MySQL: NULL in MySQL (Performance & Storage) (StackOverflow)


Availability Group configuration & licencing I am about to setup a 3 node AV group for SQL Server 2012 my configuration will be -

London-Srv1 - PRIMARY
Core Database  (OLTP)
set to READ_WRITE

London-Srv2 - SECONDARY PASSIVE synchronous
Core Database   (OLTP)
automatic failover
no connections allowed (for quick writes from PRIMARY)

Cambridge-Srv1 - PASSIVE asynchronous
Core Database  (OLTP)
no automatic failover
READ_ONLY connections

A Reporting DB is currently restored nightly from core and then heavily indexed. My original intention was to move it to the London-Srv2 and leave it outside the availability group. But I moved the READ_ONLY connections to the Cambridge server as read my synchronous server should not allow connections to make the writes as fast as possible. I would also have to licence both REPLICA nodes, which isn't an option at that cost!

IF I configure READ_ONLY routing to Cambridge, does SQL Server detect which are read_only queries automatically or does every connection string have to specify that they are read only?

Will this perform OK with a decent link between data centres or should I licence the Srv2 in London and keep all READ_ONLY connections accessing here, along with reporting database ?

For a bit of background, its a bookings website, so not huge volumes of data and losing a minute or 2 of data is not mission critical like financial orgs

In order to redirect reporitng app or application with only read only queries you need to specify read only intent in application connection string (web.config or app.config in case client application is .NET)

The below is excerpts from SQL Server BOL

  The application intent connection string property expresses the client
  application’s request to be directed either to a read-write or
  read-only version of an availability group database. To use read-only
  routing, a client must use an application intent of read-only in the
  connection string when connecting to the availability group listener.
  Without the read-only application intent, connections to the
  availability group listener are directed to the database on the
  primary replica."

Regarding your second question, if network throughput between two DC is OK you should be fine.

As long as any secondary replica is only solely used for DR (disaster recovery) and does not perform any work like read only request or backup you do not need to license it.


Update Trigger in MYSQL Here is my requirement :-

I have a table called "users" and field called "Admin".
Field "id" is primary key.
Wants to update "Admin" field to "1" if id=1000;

Tried this ,but not working.I tried "New" instead of "old".Also I tried "After" instead of "Before". But it is not working..

Any suggestions please...

DELIMITER $$
CREATE TRIGGER update_user_admin
Before UPDATE ON users
FOR EACH ROW
BEGIN

  UPDATE users
    SET admin = 0
  WHERE old.id = 672;

END;
$$
DELIMITER ;

Unfortunately you can't use a trigger to update the table that called the trigger: from the documentation:

  A stored function or trigger cannot modify a table that is already
  being used (for reading or writing) by the statement that invoked the
  function or trigger.

Could you move the admin field to a separate table which just holds the current admin id (or list of), and then you could update that with the trigger.

(of course if your admin is always id:1000, then you probably don't need the trigger to start with)

You would also need to consider whether to fire this AFTER INSERT and/or UPDATE.

An example trigger would be:

DELIMITER $$
CREATE TRIGGER update_user_admin
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF new.id = 1000 THEN
        UPDATE userAdmin SET id = new.id;
    END IF;
END;
$$
DELIMITER ;


Maintenance Plan on Replication Subscriber I have seven subscribers to a replicated publication using transactional replication in SQL Server 2012.

Do I treat the subscribers just like a normal database and perform maintenance via a maintenance plan on them - or should they be left alone / treated any differently?

I know that Microsoft recommend backing up subscriber databases as usual, so I presume this would be part of a normal maintenance plan?

Do I treat the subscribers just like a normal database and perform maintenance via a maintenance plan on them - or should they be left alone / treated any differently?

Yes, subscriber databases should be treated as regular user databases & you should intelligently perform Index maintenance (and do backups as well) . Infact, replication does not replicate index maintenance. Also, from BOL :

One suggestion - please do not use maintenance plan !


Can we migrate oracle database 10gR2 to cross platform I want to know can we migrate Linux based oracle database 10gR2 (10.2.0.1.0) to windows server 2003 with oracle database 10gR2 (10.2.0.3.0). I have seen the documentation related to database migration where i don't understand the compatibility section. Is this important in migration that both host and target database versions and patch level should be same.
Please help me.

this is the output that i get when i run the transport.sql to migrate my database from Linux to Windows

E:\new\test>set ORACLE_SID=newdb
E:\new\test>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 19 20:49:08 2014

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> @transport.sql
ORACLE instance started.

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             150998160 bytes
Database Buffers          293601280 bytes
Redo Buffers                7094272 bytes

Control file created.

ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

ERROR:
ORA-03114: not connected to ORACLE

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Your database has been created successfully!
* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
*    or the global database name for this database. Use the
*    NEWDBID Utility (nid). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if there the database was not opened in UPGRADE mode
DOC>
DOC>   If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC>   re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
2  WHERE status != 'OPEN MIGRATE';
ERROR:
ORA-03114: not connected to ORACLE

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction With the Partitioning, OLAP and Data Mining options

To answer your question if you can migrate a linux database to a higher oracle release on a windows platform: Yes you can.

There are a few things to keep in mind...

Linux and windows use little endian, unix uses big endian -- basically it depends on the CPU not so much on the OS.

If you want to minimize downtime and restore the database on the windows server while the linux server is still productive, you have to apply the archivelogs from the linux database on the windows database until you do the switch. Basically this should work but there are some bugs I faced when doing this. So I would not recommend this option at least not with 10g or 11g (never tried it with 12c). Oracle officially supports DataGuard with windows and linux from 11gR2 but as I said there are some bugs. It failed in 3 of 5 attempts.

I personally don't like export-import migrations because you always have some trouble with invalid objects, characterset conversion, missing data, missing public synonyms etc.

--> I would recommend the the following action plan:

shutdown the linux database
startup mount the linux database
create an rman backup
restore the database on windows
open the database with alter database open resetlogs upgrade
run catupgrd.sql
do post-upgrade tasks


Postgresql constrains on FK I am trying to design a (part of a) database which has to accomplish the following:

There is a students table, containing a bunch of students.
There are educations in the database.
Each student can have 0..n educations.
There are x different types of educations, in which x is small (<10) and known in advance.
The different types of education all have the same type of data associated with it (location, name etc.)
Some more data is associated with a student-education connection (e.g. grades, start date etc.).
The type of data which is associated with a student-education connection depends on the type of education (e.g. a masters degree has a specialization, a course does not).

I try to create a good database design to represent this data, however there are quite a few difficulties. A design I came up with is as followed:

Student table, which contains student data
Education table, which contains data of educations. There is a type column to specify the type.
Student_Education junction table, which links students with educations.
x <type>Education tables which will contain data associated with student-education connections (thus a FK to Student_Education is always present). Each education type will have it's own table.

However, there is a problem with this design: a <type>Education row should only be allowed to reference a Student_Education connection when the education type matches. E.g. a MasterEducation row can only reference a row in the Student_Education table that references a row in the Education table with type == master.

Would it be possible to add a constraint which can check exactly that?

If not, what other options are available?

From a design perspective, why would the Student_Education table be needed at all?

The information that a specific student follows a certain type of education is implied by the existence of the corresponding row in the <type>Education table.

If Student_Education's reason for being is for SQL joins, it could be a view returning the union of all the <type>Education tables.

As for the foreign keys to Education, to be sure that each entry in <type>Education can only refer to its corresponding type, I can see two options:

As @hypercube suggests in the comments, add a <type>Education.educ_type column with a fixed value and a table-level foreign key:
FOREIGN KEY (educ_id,educ_type) REFERENCES Education(unique_id,type)
Split Education into as many subtables as they are education types, and have column-level foreign keys from each <type>Education.educ_id to the corresponding subtable. It's also the proper design choice if the Education table should be expanded in the future with columns that are specific to certain education types. Education could also be turned into a view as the union of the subtables, at least for the common columns.


Find all IP's or users who access a specific SQL Server Let's say I found a SQL Server and I have no idea which applications connect to it. Maybe I find one application but I am not sure if it is the only one using it.

Is there a nice way of finding all distinct connections?

You can look at the currently running processes in SSMS in Activity Monitor.

You can also get it using sys.dm_exec_sessions as well as sys.dm_exec_connections and sys.dm_exec_requests.

Query:

SELECT DB_NAME(database_id) as [DB]
    , login_name
    , nt_domain
    , nt_user_name
    , status
    , host_name
    , program_name
    , COUNT(*) AS [Connections]
FROM sys.dm_exec_sessions
WHERE database_id > 0 -- OR 4 for user DBs
GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;

Hostname, process name or domain user names often give more information than just an IP address. Other columns can be added such as: login_time, last_successful_logon

IP and other information can be retrieved with a JOIN to sys.dm_exec_connections on session_id: client_net_address, local_net_address, connect_time, ports, ...

sys.dm_exec_requests can also give useful information: command type, sql_handle, ...

This query is just a sample. You must JOIN these 3 views together and output/store relevant information from either of them.

Data from these view disapear when SQL Server is restarted. Therefore if the application is not regularly connected it may be a good idea to store them on a regular basis using a job or a script (Powershell).


AWS Athena: syntax for grouping data for week ending on a Saturday I'm having some issues with the Presto syntax in grouping data by separate weeks, ending on Saturdays. I have this field called "timestamp," and I've been using the the following to group by weeks. However, I noticed that date_truncs weeks ending on a Monday and using a date_add doesn't necessary group all rows correctly.

select date_add('day', 5, date_trunc('week', date(substr(timestamp, 1, 10)))) as week_ending

Any suggestions on how I can group weeks ending on Saturdays? Thanks!

select substring(
          cast(date_add(
                  'day',
                  5+(cast(dow(
                             date_parse('2020-04-12','%Y-%m-%d')
                             )=7
                          as integer)
                    )*7,
                  date_trunc(
                     'week',
                     date_parse('2020-04-12','%Y-%m-%d')
                  )
               ) as varchar
          ),
          1,
          10
       ) as week_ending;


SQL-SERVER: how to get the maximum length of a variable of VARCHAR help Hello, in this question im trying to get the maximum length of a name but i cnat seem to figure out what im doing wrong, i've search the web for answer but find none. i tried to put the MAX function in the select clause but it didnt seem to fix it, what am i doing wrong?? Thank you

You are comparing integer to string. You need to compare length of customer name with max of length and not with direct customer name.

You could modify your code as below:

SELECT
CustomerName AS LongestName,
LEN(CustomerName) AS LengthOfName,
FROM
MsCustomer
where
len(CustomerName) = (SELECT MAX(LEN(CustomerName)) from MsCustomer)

I am not really sure what are you trying to do with STATUS = 'Customer' code.

There are tonnes of article on web for finding the record for max of certain things.

Hope this answers your concern.


Connection pooling - NodeJS or Database products? Consider the mysql package. The description covers connection pooling thus,

  Rather than creating and managing connections one-by-one, this module
  also provides built-in connection pooling using
  mysql.createPool(config).

Then, there is the connection pool setting in proxysql as described here.

Similarly, in case of PostgreSQL too, there is the pg package supporting connection pooling and pgboucner pooling feature.

How are these approaches (npm package versus product supporting tools) different?
Ideally, I would like to configure connection pooling at the database level so that a developer is free from handling connections. Can this be a preferred approach?

You can use connection pooling on any level, but I believe that it is best done with a connection pool inside the application server code for these reasons:

You don't need an extra process like pgBouncer that you have start and monitor.
You don't have any more TCP connections than necessary, thus conserving resources and being faster.

This holds if you have a single application server. If several of them access the same database, connecting pooling in the application server isn't enough. It is still a goid idea if you want to limit the DB resources each one can use, but you will still need another connection pool to limit the total number of database connections.

Such a connection pool can be implemented in the database if the RDBMS supports it (PostgreSQL doesn't), or it can be an external tool like pgBouncer.


Is the PostgreSQL 'connection limit' shared across all users in a role? I have created a role that many users can share:

> create role foo_read_only connection limit 5;

I have then assigned permissions to that role:

> grant select on table1 to foo_read_only;

Then I have assigned that role to people:

> grant foo_read_only to fred;
> grant foo_read_only to ginger;

Question: Do both fred and ginger get a connection limit of 5, or would that limit be shared between them?

Thanks!

The CONNECTION LIMIT attribute only affects roles that are able to log in (that is "users", or those roles which have the LOGIN attribute set).

Roles that are a member of another role with a CONNECTION LIMIT do not inherit that limit, so in the situation you describe above fred and ginger would have the default connection limit of -1 (unlimited).

To limit fred and ginger you would need to specifically set a CONNECTION LIMIT on those roles.


How to use Case Statement with BCP I am using BCP as follows,

bcp "SELECT * FROM [dbo].[GpsPoint] where id in (551043, 551044, 551045)" -n

This works 100%. I have omitted the rest of it because it is just server, user, password settings etc.

If I change the query to this,

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

With a case statement the BCP import gives this,

Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid date format
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

The contactNo field is an INT NOT NULL in both source and destination, so I cannot see why translating the value using case then casting it to INT is not working?

My BCP import looks like this (minus the server, user, password etc),

bcp GpsPoint IN "c:\temp\GPSTracking\gpspoint.dat" -n

UPDATE:

If I change the query to this it works,

SELECT id, queueid, 8022 as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

So it is definitely the case statement. And the issue does not resolve if I use ELSE 0 in the case statement. On my actual query I have a where clause (the where clause on the very first query above) which selects only 3 records as a proof of concept, and they all have 8022 as the value. The ELSE 0 never gets used if I add it and I still get the same error.

As you mentioned the contactNo is INT NOT NULL, in your CASE statement currently you are handling only for the value 8022 and not for the remaining values. If you add the ELSE 0 in the case statement it will solve the issue. It will select 0 for the entries other than contactNo = 8022

So the working code will be:

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 ELSE 0 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing
FROM [dbo].[GpsPoint]

More explanation:

CASE contactNo WHEN 8022 THEN 1234 END this statement will return 1234 for the contactNo is 8022 and for the remaining entries it return only NULL.
So the CAST(NULL AS INT) also returns NULL only.
So inserting the NULL value into the NOT NULL value causing the error.


Discrepancy between system catalog view and sp_change_users_login? I want to check for orphaned users on a database. To that end, I use the following query on my SQL Server 2005 instance to get all users and their accompanying logins for a specific database (say MyDB):

SELECT  U.name AS UserName
        ,U.type_desc
        ,U.create_date
        ,U.modify_date
        ,U.default_schema_name
        ,P.name AS LoginName
        ,P.is_disabled
        ,P.type_desc
        ,P.default_database_name
        ,P.create_date
        ,P.modify_date
FROM    MyDB.sys.database_principals AS U
LEFT OUTER JOIN master.sys.server_principals AS P ON U.sid = P.sid
WHERE   U.type IN ('G', 'S', 'U')
ORDER BY UserName;

When I compare the results of this query with the results of USE MyDB; EXEC sp_change_users_login @Action = 'Report'; things do not seem to add up. The first query gives me 12 users of which 6 do not have logins; but the call to sp_change_users_login returns only 1 orphaned user.

Even if I take users like guest, INFORMATION_SCHEMA and sys out of the equation, there are still two users without a login attached in my first query that do not show up in the call to sp_change_users_login. What am I missing?

EDIT
I have changed use of the deprecated sysuser system table to the database_principals system view, according to the handy MS Mapping System Tables to System Views article (and Aaron of course).

First, sp_change_users_login is deprecated feature and is discontinued in SQL Server 2014.

Secondly, EXEC sp_change_users_login @Action = 'Report' is not a proper way to get orphan users list. As with 'Report' parameter, it will execute following query. According to which it will return users where issqluser = 1 (It must be a SQl Server Login), and no result if orphan user is NT User instead of SQL User.

  select UserName = name, UserSID = sid,* from sysusers
            where issqluser = 1
            and   (sid is not null and sid <> 0x0)
            and   (len(sid) <= 16)
            and   suser_sname(sid) is null
            order by name

According to BOL "sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN."

Best script to find orphan users against all databases on an instance is already provided by "Mark Storey-Smith"

Find orphaned users


Is it safe to call pg_ctl reload while doing heavy writes? I am running Postgres 8.4, and I would like to change the authentication method for a user. It's my understanding that I have to do this in pg_hba.conf, and then reload the configuration files with pg_ctl reload (or, in this case, service postgresql reload) for the settings to take effect.

However, I am also importing a ton of data from flat files using multiple processes.

Is it safe to do this?

Yes, it is safe, at least on 9.2, and I'm not aware of any major changes in that regard since 8.4. If absolute certainty is required you should initdb a test instance to experiment with before changing the production system.

In the following tests with Pg 9.3-pre, if PostgreSQL cannot re-read the config file due to a syntax error or other serious problem it'll skip the reload, eg:

LOG:  received SIGHUP, reloading configuration files
LOG:  syntax error in file "..../postgresql.conf" line 215, near token "!"
LOG:  configuration file "..../postgresql.conf" contains errors; no changes were applied

If an individual parameter is invalid it'll apply the other changes but skip that param:

LOG:  received SIGHUP, reloading configuration files
LOG:  -10 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
LOG:  configuration file "..../postgresql.conf" contains errors; unaffected changes were applied

Even if you somehow managed to find a case where PostgreSQL would shut down or even crash, the worst that you'd have to do would be start it back up again and resume your writes from the last commited point. Not-yet-committed work is guaranteed to be rolled back and committed work is guaranteed to be applied to the database after disconnect, a database restart or even after a full system crash.

If your bulk writes don't periodically commit their work and write a local checkpoint, they probably should so you can resume them if they're interrupted. Reducing transaction lengths also makes some admin tasks easier and more efficient for the database.


An IN clause gets "rewritten" to this form, essentially:

WHERE
    TableID = 260905384
    OR TableID = 260915601
    OR TableID = 260929877
    ...

I have heard, and observed, that SQL Server has a hard coded limit of 64 OR predicates that it will put in a scan or seek operator.  This isn't publicly documented anywhere as far as I can tell, and there isn't any way I'm aware of to change it either.

Beyond 64 OR expressions, as you mentioned, you end up with the "Constant Scan" plan, which drives multiple seeks or scans into the index (one for each literal).

Putting that many values literal values in an IN construction is generally considered a bad idea.  If possible, change the way the query is written.  You could, for example, insert all of those values into a temp table, and then do an INNER JOIN to that temp table table on the TableID column.


Can I run a mongos without any config servers? I just want to use mongos as a tool to scale up the number of connections I can open to mongo. So can I run the mongos without any config servers?

Has any one done this before?

You cannot run a mongos without a config server, but you shouldn't have to in order to "scale up" your connections in any case - all you would be doing is adding an unnecessary layer between the driver you are using and the replica set.  Unless you are using sharding, having that layer is not necessary, and can introduce performance issues in some cases (such as unbatching delays).

Your driver (which you don't mention) will have connection pool settings (the Java version can be found here) and perhaps multiplier options to determine how many connections you will open to your replica set, and how many threads will block and wait for a connection from the pool.

Some general advice:

If you do go down the route of having a single shard (which is what your replica set would effectively become with config servers and mongos processes), then do not run with a single config server - always run with 3 config servers in production, period.
Be careful with connections - each connection on the MongoDB side will require 1MB of memory (as will each connection on a mongos).  If you ramp up connection levels you will be using memory for connection heap rather than data - this can be significant (10GB of memory for 10,000 concurrent connections)
If you do ramp up connections, make sure your ulimit settings can handle it

Finally, this use of mongos as a general proxy has been proposed before.  In theory it would simplify some of the driver code needed if there was always a mongos in the connection path, but it is not as simple as just allowing it.  The relevant feature request can be found (and voted for, watched etc.) here:

https://jira.mongodb.org/browse/SERVER-1594


When can SARGable predicates be pushed into a CTE or derived table? Sandbag

While working on Top Quality Blog Posts®, I came across some optimizer behavior I found really infuriating interesting. I don't immediately have an explanation, at least not one I'm happy with, so I'm putting it here in case someone smart shows up.

If you want to follow along, you can grab the 2013 version of the Stack Overflow data dump here. I'm using the Comments table, with one additional index on it.

CREATE INDEX [ix_ennui] ON [dbo].[Comments] ( [UserId], [Score] DESC );

Query One

When I query the table like so, I get an odd query plan.

WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score DESC
     )
SELECT *
FROM   x
WHERE  x.Score >= 500;

The SARGable predicate on Score isn't pushed inside the CTE. It's in a filter operator much later in the plan.

Which I find odd, since the ORDER BY is on the same column as the filter.

Query Two

If I change the query, it does get pushed.

WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score >= 500
ORDER BY x.Score DESC;

The query plan changes, too, and runs much faster, with no spill to disk. They both produce the same results, with the predicate at the nonclustered index scan.

Query Three

This is the equivalent of writing the query like so:

SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score >= 500
ORDER BY c.Score DESC;

Query Four

Using a derived table gets the same "bad" query plan as the initial CTE query

SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score DESC ) AS x
WHERE x.Score >= 500;

Things get even weirder when...

I change the query to order the data ascending, and the filter to <=.

To keep from making this question overlong, I'm going to put everything together.

Queries

--Derived table
SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score ASC ) AS x
WHERE x.Score <= 500;

--TOP inside CTE
WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score ASC
     )
SELECT *
FROM   x
WHERE  x.Score <= 500;

--Written normally
SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score <= 500
ORDER BY c.Score ASC;

--TOP outside CTE
WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score <= 500
ORDER BY x.Score ASC;

Plans

Plan link.

Note that none of these queries take advantage of the nonclustered index -- the only thing that changes here is the position of the filter operator. In no case is the predicate pushed to the index access.

A Question Appears!

Is there a reason that a SARGable predicate can be pushed in some scenarios and not in others? The differences within the queries sorted in descending order are interesting, but the differences between those and the ones that are ascending bizarre.

For anyone interested, here are the plans with only an index on Score:

DESC
ASC

There are a few issues in play here.
Pushing predicates past TOP
The optimizer cannot currently push a predicate past a TOP, even in the limited cases where it would be safe to do so*. This limitation accounts for the behaviour of all the queries in the question where the predicate is in a higher scope than the TOP.
The work around is to perform the rewrite manually. The fundamental issue is similar to the case of pushing predicates past a window function, except there is no corresponding specialized rule like SelOnSeqPrj.
My personal opinion is that an exploration rule like SelOnTop remains unimplemented because people have deliberately written queries with TOP in an effort to provide a kind of 'optimization fence'.
* Generally this means the predicate should appear in the ORDER BY clause associated with the TOP, and the direction of any inequality should agree with the direction of the sorting. The transformation would also need to account for the sorting behaviour of NULLs in SQL Server. Overall, the limitations probably mean this transformation would not be generally useful enough in practice to justify the additional exploration efforts.
Costing issues
The remaining execution plans in the question can be explained as cost-based choices due to the distribution of values in the Score column (many more rows <= 500 than >= 500), and the effect of the row goal introduced by the TOP.
For example, the query:
--Written normally
SELECT TOP (101)
    c.UserId,
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC;

...produces a plan with an apparently unpushed predicate in a Filter:

Note that the Sort is estimated to produce 101 rows. This is the effect of the row goal added by the Top. This affects the estimated cost of the Sort and the Filter enough to make it seem like this is the cheaper option. The estimated cost of this plan is 2401.39 units.
If we disable row goals with a query hint:
--Written normally
SELECT TOP (101)
    c.UserId,
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

...the execution plan produced is:

The predicate has been pushed into the scan as a residual non-sargable predicate, and the cost of the whole plan is 2402.32 units.
Notice that the <= 500 predicate is not expected to filter out any rows. If you had chosen a smaller number, like <= 50, the optimizer would have preferred the pushed-predicate plan regardless of the row goal effect.
For the query with Score DESC and a Score >= 500 predicate:
--Written normally
SELECT TOP (101)
    c.UserId,
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score >= 500
ORDER BY
    c.Score DESC;

Now the predicate is expected to be very selective, so the optimizer chooses to push the predicate and use the nonclustered index with lookups:

Again, the optimizer considered multiple alternatives and chose this as the apparently cheapest option, as usual.


Effectively saving a graph (terabytes) in a database I'm new here so I'm not entirely sure what tags to use so please notify/edit if this should be changed, thanks!
Background
I have a graph with ~4 billion nodes and ~1 trillion edges that I want to store in a database (like sqlite) as in this way data can be inserted on the hard disk rather than in RAM as many other graph data structures require.
Data format
This graph format looks like:
accession node position orientation
23101.1   1    1        plus
23101.1   100  2        plus
...
23101.1   100  1        min
...
~1trillion

The plan
I think of creating TABLES for accession, node, and orientation that I will refer to using foreign keys. So then the "main" table will be
accession node  position orientation
<FK1>     <FK2> 1        <FK3>

(P.s. not sure if I should do if for the position too, but this will simply be 1 till graph-path-length).
The question
I only have little knowledge of SQL but based on code like this I would have to execute something like the code below for each line in the graph file:
    INSERT INTO main (accession, node, position, orientation) VALUES
        ( SELECT id from accessions WHERE accession=23101.1,
          SELECT id from nodes WHERE node=1,
          1,
          1
);

and also catch an error when the node/accession does not exist and insert it. However, I wonder whether obtaining the foreign keys with the SELECT .. WHERE will not get really slow when they are billions of rows in the table? So overall, what would be a proper way to store this information in the database (if at all)?

Column < Row < Block < Table
One column had a number, date, string, or a small number of other possibilities.  It takes a few bytes or many bytes.
One row like you described (4 columns each taking a few bytes) will take perhaps 40 bytes when you add in some overhead.
One block is 16KB and holds (in your case) a few hundred rows.  I mention a block because it is the unit of caching in InnoDB tables.  All activity is done on blocks in RAM.  If an operation needs to work on the rows in a block but that block is not in RAM, it will be brought into RAM, possibly pushing out some other block.  (Read about "caching".)
One table is composed of as many blocks as are needed -- billions in your case!
So,...  because of caching those billions of blocks can be handled.  But with a lot of I/O.
I am assuming you need one row to describe one edge.  And probably another table with nodes -- and 4 billion rows in that table.
These are huge numbers.  Am I misinterpreting the number of edges and nodes?  What do you expect to walk through all the edges for some operation?  If so, let's see...

40TB in edge table;
2.5 billion blocks, each needing to be read at least once;
SSD drive that can handle 1K reads/second;
2.5 million seconds to read all edges once;
That's about 1 month.

Somethin's gotta give!


What's the difference between "shrink" file vs simply reducing the initial size? I'm totally new in DB Administration. So i got confused about the "file shrink" functionality offered on SQL Server Management Studio (SMSS) or via T-SQL. Why I would I need to use that "file shrink" functionality if I can simply lower the initial size of the db?

I tried to (edit) reduce the initial size of my DB and it succefully reduced my physical file of the db. Which would also work quite the same if I use that "shrink" feature.

That "Initial Size" label is misleading.  It's really the "current size" of the file.  Also, changing that number and clicking "OK" does a SHRINKFILE behind the scenes, so the two things you're comparing are actually...the same thing
Here's my copy of the StackOverflow2010 database (where the current size really is 9,105 MB):

If I change that to "5":

And then click the "Script" button in the top left of the window, this is what SSMS generates:
USE [StackOverflow2010]
GO
DBCC SHRINKFILE (N'StackOverflow2010' , 5)
GO

So the answer to your question is that these two features of SSMS do the same thing.
By the way, I highly recommend using that "Script" button in SSMS frequently as you are learning.  It's a great way to understand what the SSMS GUI is doing, and is also helpful in automating and overcoming the limitations of the user interface as you get more comfortable with T-SQL scripts.
Note that you should be wary of shrinking database files, see here for more information:
I Need to Shrink My Database - I just freed a lot of space
When is it OK to shrink a Database?


How does SQL Server order results when joins are used? How does SQL Server figure out the order of the records in the result set of query execution?

I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I am selecting the order also changes. When I execute the below SQL with a SELECT * I get the same records but in a much different order.

 SELECT TOP (900)
    AD.ATTACHMENTID,
    AD.NAME,
    AD.ISINLINE,
    AD.INSERTEDDATETIME,
    ATMT.ATTACHMENTBLOB,
    U.UFID
  FROM ATTACHMENTDETAIL AD WITH (NOLOCK)
  INNER JOIN MESSAGEATTACHMENT MA ON MA.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN ATTACHMENT ATMT ON ATMT.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN MESSAGE MSG ON MSG.ID = MA.MESSAGEID
  INNER JOIN MESSAGEDETAIL MD ON MD.MESSAGEID = MA.MESSAGEID
  INNER JOIN [USER] U ON U.ID = MD.USERID
  LEFT OUTER JOIN XmlExtractionMapping XM ON MA.MESSAGEID = XM.MessageId
    WHERE AD.FILEBOXTOKEN IS NULL
    AND (XM.XMLEXTRACTIONDATE IS NOT NULL OR
         (MSG.MESSAGESOURCEID = 1 AND MD.FolderId <> -4))
    AND AD.ISINLINE = 'FALSE'

Since you haven't told SQL Server how to order the results, it is free to do so in whatever is the most efficient. In this way it will depend on what is the cheapest to sort, and the columns you select will drive that because it in turn depends on the cheapest index(es) to use to get at the information requested by the query. This can change from execution to execution not just by changing the query text but also by things like data changes, statistics updates, freeproccache, service packs, hotfixes, etc. This is especially volatile with so many tables involved if the underlying data is changing rapidly, as it tends to do in OLTP applications.

If you want a predictable order, why don't you use ORDER BY on the query? As written, the SQL specifies that any 900 rows are acceptable, in any order.

Even though, in a lot of cases, you see the same order over and over again for the same query, there is no guarantee unless you say ORDER BY <something>.


MySQL my.cnf vs my-huge.cnf I've recently installed MySQL server and client 5.5.28 through rpm packages on an openSUSE machine, and I was not able to locate my.cnf file. I look into all directories mysql --verbose --help | grep -C3 my.cnf printed which was /etc/my.cnf (no such a file), /etc/mysql/my.cnf(no directory as mysql), usr/etc/my.cnf (don't have etc in my usr) and ~/.my.cnf (doesn't exist). Then I tried find / -name *.cnf which gave me:

/usr/share/doc/MySQL-server-standard-5.0.24a/my-huge.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-innodb-heavy-4G.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-large.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-medium.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-small.cnf
/usr/share/ssl/openssl.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

I want to edit my.cnf file to practice replication and high availability. I read somewhere that having my.cnf file won't be necessary to use MySQL well not in my case... so I was thinking to rename one these my-*.cnf to my.cnf and put it in the locations that MySQL would look for my.cnf file. But I'm not sure first if this would work, and second which one to pick to be able to practice replication.

I don't want to make it complex for myself, so I was thinking to take the medium one... Am I doing the right thing?

If you are doing replication and you are just practicing, the my.cnf you choose is not that important. However, the smaller the config, the better. For example, if you DB Server has 2GB RAM, you should not choose my-innodb-heavy-4G.cnf.

If you started up mysql with a my.cnf, you create the default 10M or 18M ibdata1 and 2 5M InnoDB log files (ib_logfile0, ib_logfile1). The medium cnf has the following InnoDB settings:

[root@******** ~]# cat /usr/share/mysql/my-medium.cnf | grep innodb
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

Since everything related to InnoDB is commented out, you should OK to choose my-medium.cnf

Simply run this:

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
service mysql restart

Now, as for MySQL Replication, I have Good News : I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with all the steps you need to automate a Slave's creation.

Here is a couple more of my posts with more advanced replication setups

May 07, 2012 : Setting Circular Replication in mysql
Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?


Warning before issuing delete/update command in mysql/oracle , then executes Is there any utility / function either in mysql / oracle that prevent I issue some delete/update command wrongly, a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to run this command" , if yes then delete/update command will execute?

I have googled this, but not a specific solution is provided for this.
I tried SOURCE also, but it is not exactly what I want.

Do any one have any idea over this?

If I'm about to fire update command on > 100 000 records, a prompt should come asking "do you really want to run?", like in Unix if we use rm filename, it asks if you are you sure you want to delete?

if i issue some delete/update command wrongly

There is no such thing as a "wrong" delete or update command unless it is "wrong" in the sense of being syntactically invalid, referencing nonexistent objects, or attempting something for which the user does not have permission... and those aren't going to execute anyway.

So you must be talking about something like this:

DELETE FROM t1; -- not wrong, deletes all of the rows by excluding none
UPDATE users SET email = 'test@localhost'; -- not wrong, updates all rows with same value

There is sure-fire no way for the system to correctly guess "hey, that seems wrong" and ask you to rethink your decision. The DBMS's job is to do what you ask in the most optimal way, not second-guess whether you know what you're doing.

MySQL does have a mode that tries to protect you, by stopping you from executing updates or deletes that aren't using an index to find rows, with the --i-am-a-dummy flag that enables sql_safe_updates on a per-connection basis -- or you can enable that setting at a global level on the server.

$ mysql --i-am-a-dummy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4642931
Server version: 5.5.30 MySQL Community Server (GPL)

mysql> use sakila;
Database changed

mysql> delete from film;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table
                    without a WHERE that uses a KEY column

I would argue that this fixes the wrong problem, which is that you have privileges granted to people that they are not qualified to hold.

You could, of course, disable autocommit, requiring you to type COMMIT; to actually commit your DML changes to the database, but that's only true and fully helpful if you're exclusively using a transactional storage engine like InnoDB, and not MyISAM, so I don't know how much that solves.

Those blessed with extra lazyiness will figure out that they can type ; COMMIT; at the end of every statement (true in the mysql cli, not universally).

The person who is sufficiently conscientious to think before committing is probably sufficiently conscientious to not type queries that don't make sense.


Count orders 12 months back from a certain month group by that month I want to create a table with months counting orders from current month to 12 months back. I have data with a create_date and an order_id. So I need to convert the date to a format of yyyymm and count all order from selected month and sum up all orders that where made until 12 months back.

The outcome should look like:

I started with this query but it only counts orders for the selected month and not includes orders for former months.

SELECT TO_CHAR(create_date,'yyyymm') AS yyyymm,
       COUNT(order_id ) AS Number_of_orders_12_month_period
FROM Orders
WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create_date) AND TO_CHAR(create_date,'yyyymm')
group by 1

I don't have experience with Redshift but

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create__date) AND TO_CHAR(create_date,'yyyymm')

looks wrong to me. You don't want to compare create_date with itself, you want to compare it with current_date, and you want to compare after each date is converted to a month. For instance:

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN TO_CHAR(DATEADD(MONTH,-12,current_date),'yyyymm') AND TO_CHAR(current_date,'yyyymm')


Delete records if not present in subquery I want to delete records from a table that are not found in a subquery. But it's not having the desired result.

The table has multiple rows per SKU. Each price row has a valid_from and valid_to date fields. I want to erase all prices but keep those that are currently valid (where valid_to is greater or equal to current date). I want to maintain future prices (where valid_from is in the future), but one step at a time.

Here's what I tried:

delete from prices
    where prices.id <> (
        select distinct on (sku) prices.id
        from prices p2
            where
                p2.valid_to >= CURRENT_DATE
            and p2."type" = 'regular'
            and prices.id = p2.id
        order by sku, p2.valid_from desc, p2.inserted_at desc
    )

The table has around 500k rows. This subquery returns around 23k rows. I expect that my above query will delete everything but those 23k rows.

    select distinct on (sku) prices.id
    from prices
        where
            valid_to >= CURRENT_DATE
        and "type" = 'regular'
    order by sku, valid_from desc, inserted_at desc

But it's not deleting anything. Why does it not work the way I expect it to?

CREATE TABLE "public"."prices" (
    "id" Bigint DEFAULT nextval('prices_id_seq'::regclass) NOT NULL,
    "type" Character Varying( 255 ) NOT NULL,
    "unit_price" Numeric( 11, 3 ) NOT NULL,
    "wholesale_price" Numeric( 11, 3 ),
    "min_wholesale_quantity" Numeric( 11, 3 ),
    "valid_from" Date NOT NULL,
    "valid_to" Date NOT NULL,
    "product_cost" Numeric( 11, 3 ) NOT NULL,
    "tax_rate" Numeric( 11, 3 ) NOT NULL,
    "store_product_id" Bigint,
    "inserted_at" Timestamp Without Time Zone NOT NULL,
    "updated_at" Timestamp Without Time Zone NOT NULL,
    "sku" Character Varying( 2044 ),
    PRIMARY KEY ( "id" ) );

CREATE INDEX "prices_store_product_id_index" ON "public"."prices" USING btree( "store_product_id" Asc NULLS Last );

CREATE INDEX "index_sku" ON "public"."prices" USING btree( "sku" Asc NULLS Last );

ALTER TABLE "public"."prices"
    ADD CONSTRAINT "prices_store_product_id_fkey" FOREIGN KEY ( "store_product_id" )
    REFERENCES "public"."store_products" ( "id" ) MATCH SIMPLE
    ON DELETE Cascade
    ON UPDATE No Action;

Use the "not in", and don't refer to the outer query:

delete from prices
where (sku,id) Not in (
    select p2.sku, p2.id
    from prices p2
        where p2.valid_to >= CURRENT_DATE
        and p2."type" = 'regular'
)


The most straightforward approach is to use a date table with all possible dates, then OUTER APPLY to get your hit count for each date, where appropriate.
Something like this:
SELECT  d.Date
        ,ISNULL(hc.HitCount, 0) AS HitCount
FROM    dbo.Date AS d
        OUTER APPLY (
                        SELECT  COUNT(1) AS HitCount
                        FROM    Table AS t
                        WHERE   1 = 1
                                AND t.Date = d.Date
                    ) AS hc
WHERE   1 = 1
        AND d.Date BETWEEN @StartDate AND @EndDate;

Here's a script to build and fill your date table:
CREATE PROC [dbo].[usp_GrowDateDimension]
(
    @Years TINYINT NULL = 25
)
AS
DECLARE @MaxDate DATE;

IF NOT EXISTS (
                  SELECT    1
                  FROM      sys.objects AS o
                  WHERE     o.name = 'Date'
              )
BEGIN
    CREATE TABLE dbo.Date
    (
        DateKey               INT          NOT NULL
        ,FullDateAlternateKey DATE         NOT NULL
        ,DayNumberOfWeek      TINYINT      NOT NULL
        ,EnglishDayNameOfWeek NVARCHAR(10) NOT NULL
        ,DayNumberOfMonth     TINYINT      NOT NULL
        ,DayNumberOfYear      SMALLINT     NOT NULL
        ,WeekNumberOfYear     TINYINT      NOT NULL
        ,EnglishMonthName     NVARCHAR(10) NOT NULL
        ,MonthNumberOfYear    TINYINT      NOT NULL
        ,CalendarQuarter      TINYINT      NOT NULL
        ,CalendarYear         SMALLINT     NOT NULL
        ,CalendarSemester     TINYINT      NOT NULL
        ,Yesterday            AS (CASE
                                      WHEN FullDateAlternateKey = CONVERT(DATE, DATEADD(DAY, (-1), GETDATE())) THEN (1)
                                      ELSE (0)
                                  END
                                 )
    );
END;

/* Get ceiling of Date dimension */
SELECT  @MaxDate = MAX(d.FullDateAlternateKey)
FROM    dbo.Date AS d
WHERE   d.FullDateAlternateKey <> '2999-12-31';

/* Set Start/Stop points based on ceiling */
DECLARE @CurrentDate DATE = DATEADD(DAY, 1, ISNULL(@MaxDate, '2000-12-31'));
DECLARE @EndDate DATE = DATEADD(YEAR, @Years, @CurrentDate);

/* Insert Rows */
WHILE @CurrentDate < @EndDate
BEGIN
    INSERT INTO dbo.Date
    (
        DateKey
        ,FullDateAlternateKey
        ,DayNumberOfWeek
        ,EnglishDayNameOfWeek
        ,DayNumberOfMonth
        ,DayNumberOfYear
        ,WeekNumberOfYear
        ,EnglishMonthName
        ,MonthNumberOfYear
        ,CalendarQuarter
        ,CalendarYear
        ,CalendarSemester
    )
    SELECT  YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate) AS DateKey
            ,@CurrentDate AS FullDateAlternateKey
            ,DATEPART(dw, @CurrentDate) AS DayNumberOfWeek
            ,DATENAME(dw, @CurrentDate) AS EnglishDayNameOfWeek
            ,DAY(@CurrentDate) AS DayNumberOfMonth
            ,DATENAME(dy, @CurrentDate) AS DayNumberOfYear
            ,DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
            ,DATENAME(mm, @CurrentDate) AS EnglishMonthName
            ,MONTH(@CurrentDate) AS MonthNumberOfYear
            ,DATEPART(q, @CurrentDate) AS CalendarQuarter
            ,YEAR(@CurrentDate) AS CalendarYear
            ,((DATEPART(QUARTER, @CurrentDate) - 1) / 2) + 1 AS CalendarSemester
    WHERE   NOT EXISTS  (
                            SELECT  1
                            FROM    dbo.Date AS d
                            WHERE   d.FullDateAlternateKey = @CurrentDate
                        );

    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate);
END;