query performance gains by removing operator hash match inner join While trying to apply the contents of this question below to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible.

SQL Server query performance - removing need for Hash Match (Inner Join)

I noticed the cost of 10% and was wondering if I could reduce it.
See the query plan below.

This work comes from a query thad I had to tune today:

SELECT c.AccountCode, MIN(d.CustomerSID)
FROM   Stage.Customer c
INNER JOIN Dimensions.Customer d  ON c.Email = d.Email
                                  OR (
                                          c.HomePostCode = d.HomePostCode
                                       AND c.StrSurname = d.strSurname
                                                                    )
GROUP BY c.AccountCode

and after adding these indexes:

---------------------------------------------------------------------
-- Create the indexes
---------------------------------------------------------------------

CREATE NONCLUSTERED INDEX IDX_Stage_Customer_HOME_SURNAME_INCL
ON Stage.Customer(HomePostCode ,strSurname)
INCLUDE (AccountCode)
--WHERE HASEMAIL = 0
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

CREATE NONCLUSTERED INDEX IDX_Dimensions_Customer_HOME_SURNAME_INCL
ON Dimensions.Customer(HomePostCode ,strSurname)
INCLUDE (AccountCode,CustomerSID)
--WHERE HASEMAIL = 0
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

CREATE NONCLUSTERED INDEX IDX_Stage_Customer_EMAIL_INCL
ON Stage.Customer(EMAIL)
INCLUDE (AccountCode)
--WHERE HASEMAIL = 1
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

CREATE NONCLUSTERED INDEX IDX_Dimensions_Customer_EMAIL_INCL
ON Dimensions.Customer(EMAIL)
INCLUDE (AccountCode,CustomerSID)
--WHERE HASEMAIL = 1
--WITH (ONLINE=ON, DROP_EXISTING = ON)
go

this is the new query:

----------------------------------------------------------------------------
-- new query
----------------------------------------------------------------------------

SELECT *
FROM (
SELECT AccountCode
     ,RO=ROW_NUMBER () OVER (PARTITION BY AccountCode ORDER BY CustomerSID)
     --,CustomerSID=MIN(CustomerSID) OVER (PARTITION BY AccountCode ORDER BY AccountCode)
       ,CustomerSID
FROM (
          SELECT c.AccountCode, D.CustomerSID
       FROM   Stage.Customer c
       INNER JOIN Dimensions.Customer d  ON c.Email = d.Email

          UNION ALL

          SELECT c.AccountCode, D.CustomerSID
       FROM   Stage.Customer c
       INNER JOIN Dimensions.Customer d  ON c.HomePostCode = d.HomePostCode
                                        AND c.StrSurname = d.strSurname
) RADHE
) R1
WHERE RO = 1

This has reduced the query execution time from 8 minutes to 1 second.

Everybody is happy, but still I would like to know if I could get more done,
I.e. by somehow removing the hash match operator.

Why is it there at the first place, I am matching all the fields, why hash?

the following links will provide a good source of knowledge regarding execution plans.

From Execution Plan Basics — Hash Match Confusion I found:

  From
  http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/

  "The hash join is one of the more expensive join operations, as it
  requires the creation of a hash table to do the join. That said, it’s
  the join that’s best for large, unsorted inputs. It is the most
  memory-intensive of any of the joins

  The hash join first reads one of the inputs and hashes the join column
  and puts the resulting hash and the column values into a hash table
  built up in memory. Then it reads all the rows in the second input,
  hashes those and checks the rows in the resulting hash bucket for the
  joining rows."

  which links to this post:

  http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

Can you explain this execution plan? provides good insights about the execution plan with, not specific to hash match but relevant.

  The constant scans are a way for SQL Server to create a bucket into
  which it's going to place something later in the execution plan. I've
  posted a more thorough explanation of it here. To understand what the
  constant scan is for, you have to look further into the plan. In this
  case, it's the Compute Scalar operators that are being used to
  populate the space created by the constant scan.

  The Compute Scalar operators are being loaded up with NULL and the
  value 1045876, so they're clearly going to be used with the Loop Join
  in an effort to filter the data.

  The really cool part is that this plan is Trivial. It means that it
  went through a minimal optimization process. All the operations are
  leading up to the Merge Interval. This is used to create a minimal set
  of comparison operators for an index seek (details on that here).

In this question:
Can I get SSMS to show me the Actual query costs in the Execution plan pane?
I'm fixing performance issues on a multistatement stored procedure in SQL Server. I want to know which part(s) I should spend time on.

I understand from How do I read Query Cost, and is it always a percentage? that even when SSMS is told to Include Actual Execution Plan, the "Query cost (relative to the batch)" figures is still based on cost estimates, which can be far off actuals

Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
gives good info for when you need to compare the performance of 2 different queries.

In Reading a SQL Server Execution plan you can find great tips for reading the execution plan.

Other questions/answers that I really liked because they are relevant to this subject, and for my personal reference I would like to quote are:

How to optimise T-SQL query using Execution Plan

can sql generate a good plan for this procedure?

Execution Plans Differ for the Same SQL Statement


Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000? The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000.

How can I measure transactions per second in SQL Server 2000?

I would suggest using the SQL Server Counters in the Windows PerfMon Utility.

This is for SQL 2005 but the instructions provide the gist of things for you.


Reset MongoDB root Password on Amazon Linux I have inherited a MongoDB and no one knows the root password. Is there a way to change it? I am running MongoDB shell version: 3.2.17.

I would like to back up the DB's but need access to see what's there first

Yes, you need to start your mongod to maintenance mode, without --auth. Then you can change root user password.


Privileges for database owner; application user Quick version:

What command should I issue to enable a database owner to allow it to access tables in this database and can this be done from that owner's account?

Longer Version:

I am creating a database on RDS.  I have a 'root' user that I have configured with Amazon.

Amazon automatically creates the group role 'rds_superuser' which is very privileged, but not actually a superuser.

I am creating a database and user for the application as follows:

create database master_integration;
CREATE ROLE master_application LOGIN ENCRYPTED PASSWORD '...' VALID UNTIL 'infinity';
GRANT ALL ON DATABASE master_integration TO GROUP rds_superuser WITH GRANT OPTION;
GRANT ALL ON DATABASE master_integration TO GROUP master_application;

\c master_integration;
ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO rds_superuser;

I updated this script to reflect suggestions by Craig Ringer regarding how I should be handling this.

When the app connects (with the master_application credentials) it creates  (and therefore owns) the tables.

My issue is that I cannot use my administrative (rootish) log in to run queries because that user has no privileges on the table.

I have been able to solve this before by running the following from the application account:

GRANT ALL privileges ON ALL TABLES IN SCHEMA public to rds_superuser;

But it seems hacky to have a subordinate user grant privs back to an administrative user.

So...Is there a command that I can run before or after I create the tables from the application which will ensure that the owner of the database can access the tables within the database?

update after re-trying the alter default privilegs...

This still does not grant access to the tables; I see it being suggested elsewhere and it makes complete sense, but it is not working for me.  From a psql shell:

master_integration=> \ddp
                           Default access privileges
      Owner       | Schema | Type  |             Access privileges
------------------+--------+-------+-------------------------------------------
 integration_root |        | table | integration_root=arwdDxt/integration_root+
                  |        |       | rds_superuser=arwdDxt/integration_root
(1 row)

master_integration=> \dp users
                           Access privileges
 Schema | Name  | Type  | Access privileges | Column access privileges
--------+-------+-------+-------------------+--------------------------
 public | users | table |                   |
(1 row)

integration_root is my superuser-ish user and users is a table within my database.

Update

I got a fairly useless response from someone at Amazon.

They asked me to call ALTER DEFAULT PRIVILEGES from the master_application login.  While this would probably work, it would not answer my question (which is how do I make this happen solely from the rds_superuser account).

I asked them to clarify this and they went away.

You want ALTER DEFAULT PRIVILEGES.

Give the rds_superuser default access rights to all new tables.

This only affects tables created after the ALTER. For existing tables you must GRANT rights.


PostgreSQL: Disk space not released after TRUNCATE I haveTRUNCATEd a huge (~120Gb) table called files:

TRUNCATE files;
VACUUM FULL files;

The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space?

UPDATE:
The disk space was released after ~12 hours, without any action on my side. I use Ubuntu 8.04 server.

According to comments in the source, truncate creates a new, empty storage file, and deletes the old storage file at commit time. (Docs suggest "storage file" is just a file as far as the OS is concerned, but I might be misunderstanding the terminology.)

  Create a new empty storage file for
  the relation, and assign it as the
  relfilenode value. The old storage
  file is scheduled for deletion at
  commit.

Since it seems to be deleting a file, I can imagine some cases in which the underlying operating system might not immediately free that space. I imagine that in some cases the storage file might end up in the Recycling Bin under Windows, for example. But in my case, truncating a table under PostgreSQL 9.something immediately increased the freespace under Windows.

Truncation is also recorded in the WAL log. I don't know how much effect that might have.


MySQL Daemon failed to start on CentOS 6 after automatic update MySQL suddenly stopped working this night, after an automatic update of MySQL.

[root@lincl1161 xxxx]# service mysqld restart
mysqld stopped:                                            [  OK  ]
MySQL Daemon failed to start.
mysqld started:                                            [FAILED]

Here is my mysql.log file:

[root@lincl1161 xxxx]# tail /var/log/mysqld.log
170314  3:41:06 [Note] /usr/libexec/mysqld: Normal shutdown
170314  3:41:06 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
170314  3:41:06 [Note] Event Scheduler: Waiting for the scheduler thread to reply
170314  3:41:06 [Note] Event Scheduler: Stopped
170314  3:41:06 [Note] Event Scheduler: Purging the queue. 2 events
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233579  user: 'admin'
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233578  user: 'admin'
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233575  user: 'admin'
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233574  user: 'admin'
170314  3:41:09  InnoDB: Starting shutdown...
170314  3:41:09  InnoDB: Waiting for 48 pages to be flushed
170314  3:41:10  InnoDB: Shutdown completed; log sequence number 53460683633
170314  3:41:10 [Note] /usr/libexec/mysqld: Shutdown complete
170314 03:41:10 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

And the YUM log (the server uses Plesk):

[root@lincl1161 xxxx]# tail /var/log/yum.log
Jan 15 04:30:07 Updated: httpd-tools-2.2.15-56.el6.centos.3.x86_64
Jan 15 04:30:08 Updated: httpd-2.2.15-56.el6.centos.3.x86_64
Jan 15 04:30:08 Updated: 1:mod_ssl-2.2.15-56.el6.centos.3.x86_64
Jan 31 03:47:55 Updated: policycoreutils-2.0.83-30.1.el6_8.x86_64
Jan 31 03:48:07 Updated: selinux-policy-3.7.19-292.el6_8.2.noarch
Jan 31 03:48:45 Updated: selinux-policy-targeted-3.7.19-292.el6_8.2.noarch
Jan 31 03:48:49 Updated: psa-phpmyadmin-4.6.6-cos6.build1205170125.17.noarch
Jan 31 03:49:52 Updated: psa-selinux-12.5.30-cos6.build1205170125.17.noarch
Mar 14 03:40:51 Updated: mysql-libs-5.5.54-36.x86_64
Mar 14 03:40:54 Updated: mysql-5.5.54-36.x86_64
Mar 14 03:41:06 Updated: mysql-server-5.5.54-36.x86_64

No manual changes in server nor Plesk configurations have been made since months. Any help will be appreciated.

That issue is related to a broken update by Plesk.

Please read the article https://support.plesk.com/hc/en-us/articles/115001906725 .

You can fix it by running this command:

install -m 0700 -o mysql -g mysql -d /var/lib/mysql-files

and start mysql:

service mysqld start


If your table is named Articles, do you name your column article_number or simply number? A question that I ask myself quite often is: If my table is named articles, should I name my column article_number or simply number?

Is there a best practice? Is there a difference if you create tables and columns in a database, or work in an abstract layer like an ORM, where the query would perhaps be

a = Article.get(1);
my_num = a.number;
// or
my_num2 = a.article_number

I would do article_id. It is tempting to just make it "id" but when it starts becoming a FK in other tables, it is nice to have the column have the same name on both sides.


Count issue in Mysql I query a table

SELECT COUNT(*) FROM sf_guard_user_group

return count 1 but table has total 28 records. But if I test it via putty from another machine, it shows count 28. Help to sort out.

I query a table "Select count(*) from sf_guard_user_group"

That's not true, according to the screenshot. I don't see the FROM clause there.


Deleting multiple rows based on the first instance of the value I have a table with the below structure. I would like to delete all of the records that records that match ColA for any of the ColB's that have a ColC that has an even-numbered value. But only for the first instance of ColA. (In  this example all of the AA records would be deleted)

ColA and ColB are unique and sorted in alphabetical order. ORderByCol is part of the query that builds this table: ROW_NUMBER() OVER(PARTITION BY ColA ORDER BY ColB)

ColA    ColB    ColC    OrderByCol
AA      A       8       1
AA      B       3       2
AA      F       5       3
BB      B       7       1
BB      D       9       2
CC      A       1       1
CC      Q       5       2

I can do this with an ColA in a subquery, but that seems like a lot of work and it is really slow.

Query:

DELETE T FROM Table1 AS T
WHERE T.ColA IN (
    SELECT DISTINCT ColA FROM Table1
    WHERE EXISTS (SELECT * FROM Table1 WHERE ColC % 2)
    AND ORderByCol = 1
)

So the query will give me a list of all unique ColA values that have the first item not primary, but it is really slow. I'm sure there is a faster way to do this.

EDIT: If someone knows a better way to do what I am asking that would be awesome! I always like learning new ways to do things in SQL. However I found out why my query was slow. I didn't have an index on the table because I thought that table variables (@table1) tables couldn't have indexes. Once I put in an index on my table it reduced my run time from 1 min 20 sec to 2 sec.

Thank you to every one who has helped out with this.

If this was SQL Server 2012 or later version, you could use the FIRST_VALUE window function:

WITH cte AS
  (
    SELECT
      FirstColC = FIRST_VALUE(ColC) OVER (PARTITION BY ColA ORDER BY ColB ASC)
    FROM
      dbo.Table1
  )
DELETE FROM
  cte
WHERE
  FirstColC % 2 = 0
;

In SQL Server 2005 FIRST_VALUE can be replaced with a correlated TOP (1) + ORDER BY subquery:

WITH cte AS
  (
    SELECT
      FirstColC = (
        SELECT TOP (1)
          ColC
        FROM
          dbo.Table1 AS toprow
        WHERE
          toprow.ColA = main.ColA
        ORDER BY
          ColB ASC
      )
    FROM
      dbo.Table1 AS main
  )
DELETE FROM
  cte
WHERE
  FirstColC % 2 = 0
;


Is it possible to use Native Clients from previous releases to connect SQL Server 2012? Is it possible to use SQL Server Native Clients from previous SQL Server releases (e.g. SQL Server 2008 R2 Natice Client) to connect SQL Server 2012 database?

Yes, although it's possible that some new functionality might not be supported by the older client. We have a number of programs using the 2008 Native Client to connect to 2012, and haven't observed any problems.


SSMS returns results immediately; ADO takes longer I am running a query that returns ~250k results.

It is basically SELECT * FROM my_schema.my_view (and the view is somewhat complex, and all table references in it are two-part)
I am not passing any parameters
I have tried setting ARITHABORT to ON and using OPTION (RECOMPILE)
Within 2 seconds of executing, SSMS has returned ~23k results
SSMS takes ~1m to return all ~250k results
With ADO and the same query, it takes ~1m to obtain the first result from the DataReader
PARAMETERIZATION is set to SIMPLE

Code example (using Dapper):

foreach (var result in connection.Query("SELECT * FROM dbo.my_view"))
{
    // process each result in here
}

I have read http://www.sommarskog.se/query-plan-mysteries.html and about a couple dozen other SO/DBA answers trying to make sense of this but I just can't seem to get my results streaming any faster. It almost seems like when I use ADO, SQL Server tries to store all of the results in a temporary table before returning.

What else can I check or try to get my results streaming immediately like they do in SSMS?

As Pablo Montilla mentioned in a comment on the question, I should have added a code sample to indicate that I was using Dapper. (I have since added the sample.)

The solution was to pass an argument to Dapper's Query method to tell it not to buffer all of the results before letting me iterate over them. The default behavior of Dapper is to buffer the results first.

foreach (var result in connection.Query("SELECT * FROM dbo.my_view", buffered: false))
{
    // process each result in here
}


automatic soft NUMA results in odd number of cores I ran sp_blitz on one of our SQL Server 2016 production servers and one of the findings was that there were CPUs with an odd number of cores. The detailed message indicated this is a really bad NUMA configuration.

SQL Server 2016 will use automatic soft NUMA if it detects physical processors with more than 8 cores. Our particular server has two sockets with 10 CPUs per socket.

The startup messages in the SQL Server error log indicate:

  Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.

As a result SQL Server created four logical NUMA nodes with 5 cores each.

Is this a performance problem?

We're using MAXDOP = 4.

Is parallelism an issue here?

We can't answer your question of "Is there a performance problem?" Only you can answer that. Are you getting acceptable performance in production? I can tell you that I'm aware of a production system that gets acceptable performance with four sockets of 10 CPUs each with auto soft-NUMA enabled, so it's not like it's impossible to get acceptable performance need with soft-NUMA nodes having an odd number of schedulers.

If you suspect that there are performance gains to be had by changing your NUMA configuration then you should consider testing that. As far as I know it really all depends on your workload. The three choices that you have are to keep auto soft-NUMA, disable auto soft-NUMA, or to attempt a manual configuration with de-affinitizing CPUs or manual soft-NUMA by changing the registry. I'm not sure if I have all of the details right below but you might find information about what changes within SQL Server as you change your configuration to be helpful.

The number of lazy writers is determined by the number of physical NUMA nodes, so soft-NUMA won't change it. You can have up to four transaction log writers in SQL Server 2016, with one per NUMA node within SQL server. Those transaction log writers won't be spread evenly over your NUMA nodes: they are assigned to CPUs 1, 2, 3, and 4 as needed. You will have one resource monitor per logical NUMA node. I believe that you will also have 1 I/O completion thread per logical NUMA node, but I haven't specifically looked at that and don't know if there's a limit.

Maybe going through a few example configurations will help. Suppose you disable auto soft-NUMA. For that configuration you will have 2 logical NUMA nodes of 10 schedulers in SQL Server, 2 resource monitors, 2 lazy writers, 2 I/O completion threads, and 2 transaction log writers. You would no longer see the sp_blitz finding.

Now suppose you enable auto soft-NUMA. For that configuration you will have 4 logical NUMA nodes of 5 schedulers in SQL Server, 4 resource monitors, 2 lazy writers, 4 I/O completion threads, and 4 transaction log writers.

With manual soft-NUMA you could do something like create 3 soft-NUMA nodes of 2 schedulers, 4 schedulers, and 4 schedulers per socket. That would also avoid the sp_blitz finding. You could also make 2 CPUs per socket not visible to SQL Server in which case you'd have 8 schedulers per socket and auto soft-NUMA would not apply. Both of those sound like rather odd configurations to me.

So which of those configurations is best suited for your workload? Would your workload benefit from having 4 transaction log writers instead of 2? Would your workload be harmed by having 4 resource monitors instead of 2? I have no idea. All you can do is benchmark it. I haven't been able to find any information from Microsoft as to when auto soft-NUMA could be harmful for performance. Their point of view seems to be that it generally improves performance and scalability and to leave it on unless you experience a performance problem. IMO sending MAXDOP 4 queries to 4 soft-NUMA nodes of 5 schedulers doesn't sound bad to me at all.


Oracle, error while exporting schema I'm using a command:

$ exp userid=apex_public_user/apex_public_user OWNER=apex_public_user DIRECT=Y CONSISTENT=Y FILE=/home/oracle/apex_public_user.dmp

and getting following output:

Export: Release 10.2.0.1.0 - Production on Fr May 18 11:55:20 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_REPCAT_UTL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REPCAT_UTL"
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 87
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_REPCAT_EXP.schema_info_exp
. exporting foreign function library names for user APEX_PUBLIC_USER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user APEX_PUBLIC_USER
About to export APEX_PUBLIC_USER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export APEX_PUBLIC_USER's tables via Direct Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_REPCAT_UTL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REPCAT_UTL"
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 87
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_REPCAT_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.

What can be wrong with the way how I export a schema?
The same error appears while exporting any other schemas.

Your database is unhappy.

Login to your Linux box as the DB owner user (usually oracle), restart it & then do the following to recreate the broken data dictionary objects:

sqlplus / as sysdba

@?/rdbms/admin/catrepr.sql

@?/rdbms/admin/catrep.sql

@?/rdbms/admin/utlrp.sql

Restart the database once done.

This is an unusual situation to be in - Has the DB been recently upgraded?


How to set Password for sqlplus /as sysdba I am new to Oracle. I would like to know how to set login password for sqlplus / as sysdba in oracle 11g.

I want to set the password for my next login. I don't want to login as sqlplus / as sysdba since anyone can log in using sqlplus / sysdba and can easily access the data or change password for the existing user.I need to restrict the login.

Say in a company 5 peoples are working and they are accessing the server with Admin userid and password to login the system and if some-one changes some modifications in the server using sqlplus / as sysdba it will affect the rest of the peoples right so in-order to avoid we need to restrict the login by setting password.

You'll need to decide whether you want to use OS authentication or use a password file. See http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba006.htm#i1006628 to help you decide.

Will users administer the database by first logging in to the database server using eg ssh? Then you can use OS authentication.

Best practice for OS authentication: No-one should be logging in as the oracle OS user. Each DBA should have their own OS account and login as that. Their OS account should be a member of the OS DBA group (remember that only members of the OS DBA group can login using sqlplus / as sysdba).

If you want to allow remote administration, then you'll either need a secure connection to the database (just like SSH provides that to the server when administering locally) or you'll need to use a password file.


PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call This is my remote function:

CREATE OR REPLACE FUNCTION public._test1()
RETURNS record
LANGUAGE plpgsql
AS $function$
DECLARE
 rec record;
BEGIN
  select 1,2 into rec;
  return rec;
END $function$;

This is my local function call:

SELECT x.a, x.b
FROM dblink('conn_str', 'select public._test1();')
as x(a int ,b int);

This is the error thrown:

ERROR: remote query result rowtype does not match the specified FROM clause rowtype

Since DBLINK requires that I define a schema to place the function's return items, how do I make the call to recognize a record type being returned by the function?

To understand what's going on, first check what is being done on the remote:

SELECT _test1();
 _test1
────────
 (1,2)

This does return a record, not two integers.  When the dblink() call reports

ERROR:  remote query result rowtype does not match the specified FROM clause rowtype

it's because locally you defined the row type as x(a int, b int), but that doesn't match the record on the remote.  The reason is that dblink() itself doesn't know about (as it itself has a SETOF record return type), therefore it cannot push this information to the remote.  So what you have to do is the following:

SELECT *
  FROM dblink('local', 'SELECT * FROM _test1() AS x(a int, b int)') AS t(a int, b int);

 a │ b
───┼───
 1 │ 2

Specifying the record type twice seems superfluous, but if you omitted the local one, it would result in another error:

SELECT * FROM dblink('local', 'SELECT * FROM _test1() AS x(a int, b int)') AS t;
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM dblink('local', 'SELECT * FROM _test1() AS x(a...
                      ^

Omitting the remote record definition:

SELECT * FROM dblink('local', 'SELECT * FROM _test1() ') AS t(a int, b int);
ERROR:  a column definition list is required for functions returning "record"
CONTEXT:  Error occurred on dblink connection named "local": could not execute query.

(The difference in the error message shows this time the problem is on the remote side.)

One ore thing that is different in my version is SELECT * FROM _test().  See what happens when you call the function in the SELECT list instead of the FROM clause:

SELECT * FROM dblink('local', 'SELECT _test1() AS x(a int, b int)') AS t(a int, b int);
ERROR:  syntax error at or near "("
CONTEXT:  Error occurred on dblink connection named "local": could not execute query.

You can make this nicer if you created the remote function as RETURNS TABLE (or, equivalently, define OUT arguments there):

-- on the remote
CREATE FUNCTION bla()
    RETURNS TABLE (a int, b int) LANGUAGE SQL AS $$
    SELECT 1, 2
$$;

SELECT * FROM dblink('local', 'SELECT * FROM bla() ') AS t(a int, b int);
 a │ b
───┼───
 1 │ 2

The record type is designed to be flexible, but it comes at a price.  You cannot avoid paying this price with dblink(), though, as it has to be able to accommodate any return type.


Query detailing differences between rows for a large amount of data I have a number of large tables, each with >300 columns. The application I am using creates "archives" of changed rows by making a copy of the current row in a secondary table.

Consider a trivial example:

CREATE TABLE dbo.bigtable
(
  UpdateDate datetime,
  PK varchar(12) PRIMARY KEY,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Archive table:

CREATE TABLE dbo.bigtable_archive
(
  UpdateDate datetime,
  PK varchar(12) NOT NULL,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Before any updates are executed on dbo.bigtable, a copy of the row is created in dbo.bigtable_archive, then dbo.bigtable.UpdateDate is updated with the current date.

Therefore UNIONing the two tables together & grouping by PK creates a timeline of changes, when ordered by UpdateDate.

I wish to create a report detailing the differences between rows, ordered by UpdateDate, grouped by PK, in the following format:

PK,   UpdateDate,  ColumnName,  Old Value,   New Value

Old Value and New Value can be the relevant columns cast to a VARCHAR(MAX) (there are no TEXT or BYTE columns involved), as I do not need to do any post-processing of the values themselves.

At the moment I can't think of a sane way of doing this for a large amount of columns, without resorting to generating the queries programmatically - I may have to do this.

Open to lots of ideas, so I'll add a bounty to the question after 2 days.

This is not going to look pretty, especially given the more than 300 columns and unavailability of LAG, nor is it likely to perform exceedingly well, but just as something to start with, I would try the following approach:

UNION the two tables.
For each PK in the combined set, get its previous "incarnation" from the archive table (the implementation below uses OUTER APPLY + TOP (1) as a poor man's LAG).
Cast each data column to varchar(max) and unpivot them in pairs, i.e. the current and the previous value (CROSS APPLY (VALUES ...) works well for this operation).
Finally, filter the results based on whether the values in each pair differ from each other.

The Transact-SQL of the above as I see it:

WITH
  Combined AS
  (
    SELECT * FROM dbo.bigtable
    UNION ALL
    SELECT * FROM dbo.bigtable_archive
  ) AS derived,
  OldAndNew AS
  (
    SELECT
      this.*,
      OldCol1 = last.Col1,
      OldCol2 = last.Col2,
      ...
    FROM
      Combined AS this
      OUTER APPLY
      (
        SELECT TOP (1)
          *
        FROM
          dbo.bigtable_archive
        WHERE
          PK = this.PK
          AND UpdateDate < this.UpdateDate
        ORDER BY
          UpdateDate DESC
      ) AS last
  )
SELECT
  t.PK,
  t.UpdateDate,
  x.ColumnName,
  x.OldValue,
  x.NewValue
FROM
  OldAndNew AS t
  CROSS APPLY
  (
    VALUES
    ('Col1', CAST(t.OldCol1 AS varchar(max), CAST(t.Col1 AS varchar(max))),
    ('Col2', CAST(t.OldCol2 AS varchar(max), CAST(t.Col2 AS varchar(max))),
    ...
  ) AS x (ColumnName, OldValue, NewValue)
WHERE
  NOT EXISTS (SELECT x.OldValue INTERSECT x.NewValue)
ORDER BY
  t.PK,
  t.UpdateDate,
  x.ColumnName
;


InnoDB UPDATE slow need a better option I'm using MySQL 5.5 with a table in InnoDB running:

  deleteme tinyint NOT NULL DEFAULT 0;`

UPDATE `import` SET  `deleteme` = 1; -- Set the delete field

LOAD DATA LOW_PRIORITY LOCAL INFILE "import.csv"
REPLACE INTO TABLE `import`  FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\n"
IGNORE 1 LINES (`id`, `name`, `m_id`, `sku`)
SET  `deleteme` = 0;

DELETE FROM `import` WHERE  `deleteme` = 1;

Almost all of the tables have over 200,000 rows in them, and it's taking to long to update the tables.  Is there a better, faster way to do this?

This is on a VPS with 2gb ram w/ 4 Cores and it's not taxing anything doing these updates, it's just SLOW.

http://sqlfiddle.com/#!2/66559/4

Demo of working code.

Using the information I've received from here, the web and several internet chat rooms, I've come up with.  Web source: http://www.softwareprojects.com/resources/programming/t-how-to-use-mysql-fast-load-data-for-updates-1753.html

  DEMO: http://sqlfiddle.com/#!2/4ebe0/1

The process is:

Import into a new temp table.
Update The old table information with information in Temp table.
Insert new data into the table. (Real world I'm making a new CSV file and using LOAD INTO for the insert)
delete everything that is no longer in the data feed.
delete the temp table.

This seems the fastest processes so far.

Let me know what your opinion is.


One slave, multiple masters MySql Is it possible to set-up MySql Replication to have one slave listening to two different masters?

Rolando's solution has many caveats.  The first being one replica stream is necessarily not replicating while the other works.  This is going to give you periods of time where your slave is out of synch.  You now have to play a delicate balancing act to ensure each has enough time to catch up when it has its "turn".

As described you also have to play book keeper of log positions to switch back to.  This really just seems buggy, opening the window for missing or inconsistent data or even breaking replication when it goes wrong (either being caused by even a just 'off by one' error in the log position)

I would recommend just running multiple mysql instances.  There's nothing stopping you from running two or more mysql's on the same machine.  They cannot both operate on the same port of course.  I don't really see this as being a problem though as every client and library allows you to specify something other than 3306.

Just specify port=3307 (or whatever in one of the .cnf files).

You will also want to take care in ensuring the individually configured buffer pools and other memory configurations aren't at odds with each other.  This is actually a benefit though as you can more finely tune those settings to the specific requirements of the individual databases that are being replicated.

This way you just have two replication streams running into the same server; never behind, no book keeping required, no "swapping" script required.


Load infile query issue in mysql We are using Load data infile query to load txt files in the database (mysql) .

we have a continues flow of text files to process using load query and is dumped in the table

but sometimes the load query just stops processing the files ..

we are not able to get what is causing this issue ..

is there anyway we could generate the sql exception or server exception to know what could have caused the problem to distinguish if its a server problem or the file problem ..

check the following link :

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

It will help you in detail about the Load infile query issue in mysql


MSSQL 2016 - Restore SSISDB from MSSQL 2012 On "MSSQL 2016 (SP2-CU11) (KB4527378)" I created empty SSISDB. After that I restored SSISDB from "MSSQL 2012 (SP4) (KB4018073)" to "MSSQL 2016 (SP2-CU11) (KB4527378)" On DB I set owner to sa and version to Compatible Level 2016(130).

On Integration Services I want to do upgrade. I click on Database Upgrade and I get error:

  The system cannot find the file specified (System)

Program Location:

   at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
   at System.Diagnostics.Process.Start()
   at Microsoft.SqlServer.IntegrationServices.UITasks.ActionHandler.PerformAction(String actionMoniker, IContext context)

How can I solve this problem?

Some screenshots

I tried to run wizard from disk a I got error:

I've ran into the same problem after restoring the SSISDB database from a lower SQL version to a higher SQL version; I've ran into:

SSIDDB Upgrade not working or erroring out on the "sql version doesn't match"
Validating packages erroring on customized_logging_levels not existing
all other errors one can encounter...

I noticed the newer SSISDB has more tables, views and different stored procedures. However there's a fairly easy solution, although unconventional.

restore the SSISDB on the "new" SQL (already Integration Services Catalog Enabled)
sp_changedbowner 'sa'
open master key decryption by password = 'yourkeyinserthere'
Alter Master Key Add encryption by Service Master Key
EXEC sp_change_users_login 'update_one', '##MS_SSISServerCleanupJobUser##','##MS_SSISServerCleanupJobLogin##'
ALTER DATABASE [SSISDB] SET COMPATIBILITY_LEVEL = 130 (or the one you need)
Install a CU higher then your current one

Installing the CU does the SSISDB upgrade and you can check that by looking at the table internal.catalog_properties. It will have a line "upgrade from".
Also, what if you are already on the latest CU? Well, uninstall your last CU and reinstall it. That works too.


Known pitfalls with MySQL partitioning Are there any known pitfalls with MySQL partitioning when server behavior differs a lot from the expected one?
I'll try to explain what I mean. Let's create partitioned table and fill it with random data.
CREATE TABLE PartitionedTable (
  id int(11) NOT NULL AUTO_INCREMENT,
  col int,
  PRIMARY KEY (id),
  KEY(col)
) PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (4000),
    PARTITION p2 VALUES LESS THAN (6000),
    PARTITION p3 VALUES LESS THAN (8000),
    PARTITION p4 VALUES LESS THAN MAXVALUE
    );

INSERT INTO PartitionedTable (col)
SELECT FLOOR(RAND() * 1000)  AS col FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4 ;

Now let's check the following queries (I'll use MySQL to be able to use EXPLAIN ANALYZE but it seems execution plan for other MySQL Server versions is the same).
The first query:
EXPLAIN ANALYZE SELECT col FROM PartitionedTable WHERE id >= 6000 AND id < 8000 ORDER BY col DESC LIMIT 1;

Execution plan:
-> Limit: 1 row(s)  (actual time=0.021..0.021 rows=1 loops=1)
    -> Filter: ((PartitionedTable.id >= 6000) and (PartitionedTable.id < 8000))  (cost=0.10 rows=1) (actual time=0.020..0.020 rows=1 loops=1)
        -> Index scan on PartitionedTable using col (reverse)  (cost=0.10 rows=1) (actual time=0.019..0.019 rows=1 loops=1)

Everything looks ok, server found necessary partition (you can see it if you run EXPLAIN without ANALYZE) and read one last row from index on col column.
The second query (which requests the same data but in a bit different way):
EXPLAIN ANALYZE SELECT MAX(col) FROM PartitionedTable WHERE id >= 6000 AND id < 8000;

Execution plan:
-> Aggregate: max(PartitionedTable.col)  (actual time=0.938..0.938 rows=1 loops=1)
    -> Filter: ((PartitionedTable.id >= 6000) and (PartitionedTable.id < 8000))  (cost=400.26 rows=2000) (actual time=0.037..0.799 rows=2000 loops=1)
        -> Index range scan on PartitionedTable using PRIMARY  (cost=400.26 rows=2000) (actual time=0.035..0.570 rows=2000 loops=1)

Now server found the same partition but decided to scan all data inside. I know that my example is artificial (I partitioned table by primary key and used conditions which exactly match partition borders) but this execution plan looks pretty inefficient and strange because server already has an index with properly sorted data and it was able use it in a corresponding way executing the first query.
Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f27c4b395b8f02a2972cc0ae306fe1cf

There are lots of pitfalls.

Simply adding PARTITIONing will not improve performance.
Change most of the indexes if you add PARTITIONing.
Don't use unless you have a least a million rows
Only BY RANGE() is useful.
It is essentially useless to PARTITION BY RANGE(the-primary-key)
There are only 4 use case for partitioning.
A PARTITIONed table is likely to be bigger than the un-partitioned equivalent.
UNIQUE and FOREIGN KEY are virtually unavailable.

More:  http://mysql.rjweb.org/doc.php/partitionmaint
SELECT  col
    FROM  PartitionedTable
    WHERE  id >= 6000
      AND  id  < 8000
    ORDER BY  col DESC
    LIMIT  1;

Sure, there is some partitioning pruning.  That is followed by a range scan in each picked sub-table.  Scanning a BTree is possibly faster without the pruning on top of it.
INDEX(key) has (silently) id tacked on the end.  So, this is an "index range scan" with or without partitioning.  But partitioning slows it down because it must do a sort if the results are not in a single partition.  (Change that id range to see how much worse things get.)
"Test case error": SELECT MAX(col) .. WHERE id ... cannot be optimized, with or without partitioning.  Furthermore, the Optimizer in 5.5 performs the query more efficiently.
I ran your queries on several versions of MySQL/MariaDB.  For the first query:
Partitioned:       0.5 to 1.7 ms
Not partitioned:   0.4 to 0.6 ms

Need I say more?


PostgreSQL: difference between collations 'C' and 'C.UTF-8' In PostgreSQL, what is the difference between collations C and C.UTF-8?

Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or what the actual encoding of a database is?

The PostgreSQL documentation leaves a lot to be desired (just sayin' 😼 ).

To start with, there is only one encoding for a particular database, so C and C.UTF-8 in your UTF-8 database are both using the UTF-8 encoding.

For libc collations: typically collation names, by convention, are truly two-part names of the following structure:

{locale_name}.{encoding_name}

A "locale" (i.e. "culture") is the set of language-specific rules for sorting (LC_COLLATE) and capitalization (LC_CTYPE). Even though there is sometimes overlap, this really doesn't have anything to do with how this data is stored.

An "encoding" is how the data is stored (i.e. what byte sequence equates to which character). Even though there is sometimes overlap, this really doesn't have anything to do with the sorting and capitalization rules of any particular language that uses the encoding (some encodings can be used by multiple languages that can have quite different rules in one or both of those areas).

To illustrate, consider storing Korean data:

ko_KR is the locale.
Possible encodings that work with this locale are:

EUC_KR (Extended UNIX Code-KR)
JOHAB
UHC (Unified Hangul Code / Windows949)
UTF8 (Unicode's 8-bit encoding)

Also consider the following, taken from the "Collation Support: libc collations" documentation (emphasis added):

  For example, the operating system might provide a locale named de_DE.utf8. initdb would then create a collation named de_DE.utf8 for encoding UTF8 ... It will also create a collation with the .utf8 tag stripped off the name. So you could also use the collation under the name de_DE, which is less cumbersome to write and makes the name less encoding-dependent...

  ...

  Within any particular database, only collations that use that database's encoding are of interest. Other entries in pg_collation are ignored. Thus, a stripped collation name such as de_DE can be considered unique within a given database even though it would not be unique globally. Use of the stripped collation names is recommended, since it will make one less thing you need to change if you decide to change to another database encoding. Note however that the default, C, and POSIX collations can be used regardless of the database encoding.

Meaning, in a database that uses the UTF-8 encoding, en_US and en_US.UTF8 are equivalent. BUT, between that database and a database that uses the LATIN1 encoding, the en_US collations are not equivalent.

So, does this mean that C and C.UTF-8 are the same?

NO, that would be too easy!!! The C collation is an exception to the above-stated behavior. The C collation is a simple set of rules that is available regardless of the database's encoding, and the behavior should be consistent across encodings (which is made possible by only recognizing the US English alphabet — "a-z" and "A-Z" — as "letters", and sorting by byte value, which should be the same for the encodings available to you).

The C.UTF-8 collation is actually a slightly enhanced set of rules, as compared to the base C rules. This difference can actually be seen in pg_collation since the values for the collcollate and collctype columns are different between the rows for C and C.UTF-8.

I put together a set of test queries to illustrate some of the similarities and differences between these two collations, as well as compared to en_GB (and implicitly en_GB.utf8). I started with the queries provided in Daniel Vérité's answer, enhanced them to hopefully be clearer about what is and is not being shown, and added a few queries. The results show us that:

C and C.UTF-8 are actually different sets of rules, even if only slightly different, based on their respective values in the collcollate and collctype columns in pg_collation (final query)
C.UTF-8 expands the characters that are considered "letters"
C.UTF-8, unlike C (but like en_GB), recognizes invalid Unicode code points (i.e. U+0378) and sorts them towards the top
C.UTF-8, like C (but unlike en_GB), sorts non-US-English-letter characters by code point
ucs_basic appears to be equivalent to C (which is stated in the documentation)

You can find, and execute, the queries on: db<>fiddle


11.2GB text file import MySQL or PostgreSQL I am out of my depth a bit perhaps but submit this query hoping someone has some experience importing large (11.2GB) tab delimited text file (2.2GB tar.gz file) into either MySQL 5.5.41 or PostgreSQL 9.3.6. Double precision for the fields will be required as it is spatial data (Latitude, Longitude and Elevation).

I have MySQL and PostgreSQL setup on Ubuntu 14.04 using phpMyAdmin and phpPGAdmin to interact with the servers. However I realise that command line interaction will probably be better.

I have read a bit and there seems to be a way to split data for MySQL (SQLDumpSplitter2 - http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/).

Any suggestions or alternative options greatly appreciated.

Thanks
Paul

For PostgreSQL, I would recommend using pgloader.

pgloader leaves the rows that it cannot import in a separate text file, so you can take a look at what went wrong, fix the errors, and finish importing the data. COPY simply crashed when it encounters a row it didn't expect, which is likely to happen several times for very big files.

pgloader can also be set up to load data in parallel, but I'm not sure how much help it would be if you're just loading a single table.

For MySQL, the standard LOAD DATA LOCAL INFILE is as fast as you're going to get (as far as I know). It spits out a warning for each row that it cannot import, so you can go back and look at that data later.

Other than that, 11.2 GB isn't too bad. Be sure to remove all existing indexes on the table that you're importing, and recreate the indexes afterwards.

I've imported text files of comparable size into both databases without a problem. Loading data and creating indexes might take a few hours, so just leave it overnight.


How are these null values stored in a NOT NULL column? We are replicating tables from SAP ECC 6.0 on HANA into an Oracle 10g warehouse, using SAP SLT.
Since starting this, we have noticed the NOT NULL column definitions from HANA are retained in the Oracle copies of the tables, but HANA stores many values as empty strings. Oracle stores empty (varchar) strings as NULLs and somehow this does not conflict with the NOT NULL column definition (i.e. we have NULL in a column defined as NOT NULL).

Querying these tables is producing strange results:

SELECT COUNT(*) FROM warehouse.table WHERE col IS NULL;
0
SELECT COUNT(*) FROM warehouse.table WHERE col = '';
0
SELECT COUNT(*) FROM warehouse.table GROUP BY NVL(col,'N');
X 503206
N 2377222

So we can tell that there are NULL values in these columns by using NVL or DECODE functions, but querying them is returning odd results.

We do get proper results once we alter the column:

ALTER TABLE warehouse.table MODIFY (col NULL);
Table altered.
SELECT COUNT(*) FROM warehouse.table WHERE col IS NULL;
390986

But of course we can't alter the column back:

ALTER TABLE warehouse.table MODIFY (col NOT NULL);
ERROR at line 1:
ORA-02296: cannot enable (warehouse.) - null values found

I can't tell if this is a problem with Oracle's implementation of empty string storage, or possibly just a quirk of interacting with SAP's SLT replication. It seems Oracle should not allow these rows with '' or NULL values as replication tries to place them there but we have not seen any errors indicating this.

Edit to add query requested by hypercube:

SELECT LENGTH(col) FROM warehouse.table GROUP BY LENGTH(col);
 2377222
1 503206

What you described is not normal.

As already mentioned in comments, in Oracle databases, empty/zero-length strings are treated as NULL.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm#i59110

  Note:

  Oracle Database currently treats a character value with a length of
  zero as null. However, this may not continue to be true in future
  releases, and Oracle recommends that you do not treat empty strings
  the same as nulls.

Also, WHERE col = '' will never return results, because it is basically WHERE col = NULL.

It is fairly simple to reproduce these wrong results by corrupting the data dictionary manually, but I do not know what caused it in your environment just from this information.

SQL> create table t1 (c1 varchar2(20) not null disable);

Table created.

SQL> select constraint_name from user_constraints where table_name = 'T1';

CONSTRAINT_NAME
------------------------------
SYS_C005148

SQL> insert into t1(c1) values ('');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1 where c1 is null;

  COUNT(*)
----------
         1

So far everything is normal.

The database can skip entire steps while executing a SQL statement based on a constraint. If you have an enabled, validated, NOT NULL constraint on col, and your predicate is col is null, the database knows that column can not contain NULL, so it will return 0 rows without actually executing the related steps. If you have an enabled, validated constraint on a column, the NULL$ column in the COL$ dictionary table for that column is set to 1. But even with a disabled, non-validated constraint, corrupting NULL$ is enough for the database to return wrong results.

The proper method for enabling that constraint should be (which obviously fails):

SQL> alter table t1 modify constraint SYS_C005148 enable validate;
alter table t1 modify constraint SYS_C005148 enable validate
                                 *
ERROR at line 1:
ORA-02293: cannot validate (SYS.SYS_C005148) - check constraint violated

Now I set NULL$ manually:

SQL> update col$ set null$ = 1 where obj# = (select object_id from user_objects where object_name = 'T1') and name = 'C1';

1 row updated.

SQL> commit;

Commit complete.

Then run the query again:

SQL> select /*+ gather_plan_statistics */ count(*) from t1 where c1 is null;

  COUNT(*)
----------
         0

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fjf8bcs2hhb7b, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 is null

Plan hash value: 4294799605

----------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |
|*  2 |   FILTER            |      |      1 |        |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement

Returns 0 rows, the table was not accessed at all (Starts = 0 on Operation Id 3), because of the FILTER on Operation Id 2: NULL IS NOT NULL, which obviously to FALSE.

If you use NVL(col, 'N') instead of col, then the database is not able to use this kind of optimization, will access the table and return the correct result:

SQL> select /*+ gather_plan_statistics */ nvl(c1, 'N'), count(*) from t1 group by nvl(c1, 'N');

NVL(C1,'N')            COUNT(*)
-------------------- ----------
N                             1

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  0zfyk18knxtfk, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ nvl(c1, 'N'), count(*) from t1 group by nvl(c1, 'N')

Plan hash value: 136660032

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       3 |       |       |          |
|   1 |  HASH GROUP BY     |      |      1 |      1 |      1 |00:00:00.01 |       3 |  1156K|  1156K|  323K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

No FILTER this time, and the table was accessed (Starts 1 on Operation Id 2 - TABLE ACCESS FULL).

There was a bug in 10g that caused wrong results with the optimization for CHECK constraints with NULL on transitive predicates:

Bug 5462687 - CHECK constraint can cause wrong results (Doc ID 5462687.8)

This will not help me, because I corrupted the dictionary myself. But if your actual query is more complex than you posted (otherwise do not bother with this), and have transitive predicates, you could try the workaround written in this, and disable this behaviour by setting event 10195

$ oerr ora 10195
10195, 00000, "CBO don't use check constraints for transitive predicates"
// *Cause:
// *Action:

Example:

alter session set event '10195 trace name context level 1';

I do not think this caused by how data is stored, rather just something about the constraints.


Using sys.dm_db_index_usage_stats for unhelpful or unused indexes I'm trying to discover indexes which may be in place which are only adding cost with no value to our database.  I've come across sys.dm_db_index_usage_stats and from MSSQL Tips the following:

  sys.dm_db_index_usage_stats

  This view gives you information about
  overall access methods to your indexes.  There are several columns
  that are returned from this DMV, but here are some helpful columns
  about index usage:

  user_seeks - number of index seeks
  user_scans- number of index scans
  user_lookups - number of index lookups
  user_updates - number of insert, update or delete operations

Does that mean if there is an index with very few / no seeks+scans+lookups but many updates, then the index is only costing us (have to update index when table is updated) but not gaining us anything?

Second question - Is the following query a decent way to discover this information?

SELECT DB_NAME([database_id]),
       OBJECT_NAME(dm_db_index_usage_stats.[object_id], [database_id]),
       TotalReads = (user_seeks + user_scans + user_lookups),
       Writes = user_updates,
       indexes.[name],
       *
FROM sys.dm_db_index_usage_stats
LEFT JOIN sys.indexes on dm_db_index_usage_stats.[object_id] = indexes.[object_id] and dm_db_index_usage_stats.[index_id] = indexes.[index_id]
ORDER BY (user_seeks + user_scans + user_lookups) ASC, user_updates DESC;

Just throwing this out there, because I remember the bad old days of having a different script for every index ailment you read about on the internet.

I co-author a free stored procedure called sp_BlitzIndex that will tell you about a whole bunch of stuff going on with your indexes all at once.

Some examples:

Aggressively locked indexes
Duplicates (based on keys)
Borderline duplicates (based on first key column)
Unused (with differentiation based on number of writes
High value missing indexes
HEAPs
And more!

The simplest example run is probably like this. Just change the database name.

EXEC sp_BlitzIndex @DatabaseName = 'StackOverflow', @Mode = 4

There are a ton more ways to run it, just check out the docs at the GitHub link.

Hope this helps!


Restrict Oracle DB Login based on SQL Client being used We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they exist in dba_users). Each user is assigned a specific role based on their area of work and they use the user id to log on to the application. The user may have read/write access to certain functionality in the application and for this their roles have the following permissions-

EXECUTE ANY LIBRARY
SELECT  ANY SEQUENCE
EXECUTE ANY TYPE
EXECUTE ANY PROCEDURE
UPDATE  ANY TABLE
SELECT  ANY TABLE
DELETE  ANY TABLE
EXECUTE ANY INDEXTYPE
INSERT  ANY TABLE

Now we have some users requesting for SQL Client access (i.e. TNS settings) so that they can connect to the DB and perform queries for their research. These business users are ofcourse knowledgeable in SQL but we want to restrict their access based on the type of client they use to log on to the database. Any client other than the application itself, should restrict them to "read only".

Is there a way to achieve this?

You can check it with a database trigger, for example:
CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED;

CREATE OR REPLACE TRIGGER LOG_T_LOGON
    AFTER LOGON ON DATABASE
DECLARE

    osUser VARCHAR2(30);
    machine VARCHAR2(100);
    prog VARCHAR2(100)
    ip VARCHAR2(15);

BEGIN

    IF ora_login_user IS NULL THEN
        RETURN;
    END IF;

    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address
    INTO osUser, machine, prog, ip
    FROM V$SESSION
    WHERE SID = SYS_CONTEXT('USERENV', 'SID');

    IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN
        IF LOWER(prog) <> 'your_application_name.exe' THEN
            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application');
        END IF;
    ELSE
        IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN
            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries');
        END IF;
    END IF;
    -- Successful login, continue as normal
END;
/

You can also check other conditions like IP-Address or the machine name.
SELECT privileges on tables and views you have to grant to the user or ROLE in the "classic" way. This trigger only prevents to logon to the database with certain tools.
Note, a user with System Privilege ADMINISTER DATABASE TRIGGER (for example DBA role, or SYS of course) never get the exception, i.e. they can logon to the database in any case and you cannot block this by the trigger.
Another note: This trigger is insecure! For example you can simply make a copy of your local sqlplus.exe and name it your_application_name.exe. Then this trigger would allow to use it.
With JDBC it is even a simple property you can set, see https://stackoverflow.com/questions/42027389/programatically-set-vsession-program-property


PostgreSQL: Correct way for subset calculations I have a huge table (around 10 million items). For simplicity, let's say it has only 2 columns: user_id and activity_id like this

user_id | activity_id
---------------------
1       | 1
1       | 2
1       | 3
2       | 1
2       | 2

I want to select all user_id with activity_id = 1, 2 NOT 3. In the case above it will be just one result: user_id = 2. I can do it using SELECT DISTINCT combined with INTERSECT and EXCEPT operators, but it seems to be extremely slow.

From what I know about databases, it can be improved with GIN and table partitioning, however I feel like it's not correct solution in the case of PostgreSQL (because subsets operators are slow by their own).

You can easily do this with arrays in Postgres:

select user_id, array_agg(activity_id) as activities
from users
group by user_id
having array_agg(activity_id) @> array[1,2]
   and not 3 = any(array_agg(activity_id));

The condition array_agg(activity_id) @> array[1,2] only returns those that have activity_ids 1 and 2  and the condition not 3 = any(array_agg(activity_id)) removes all those that contain activity_id = 3

If the table contains more than just those two columns, an index on (user_id, activitiy_id) will help as it enables Postgres to use an "Index Only Scan" instead of a full table scan. If there are only very users that have activity_ids 1 and two, an additional condition that only returns rows with either one of them (e.g. using a where exists condition) might help as it reduces the number of rows that need to be aggregated. In that case the index should be on (activity_id, user_id) to enable Postgres to remove unwanted rows efficiently.

On a table with 100.000 rows this ran in about 100ms on my laptop with Postgres 11 and a SSD.

Online example: https://rextester.com/YLN7221


Export to format compatible with SQL Server I need to provide data in a format that could be imported into a Microsoft SQL Server database; however, I do not have Windows or access to a SQL Server instance.

The data is currently in several CSV spreadsheets. I looked at BULK INSERT but found their support for CSV is quite limited, such as not recognizing double quotes (documentation: Specifying Field and Row Terminators). I have read that edge cases like when a field happens to include a quote (and so needs to be escaped) are not supported.

Is there a more reliable intermediary format that could be generated from Linux/OSX? For example MySQL dump, Excel, Sqlite, XML, etc?

I also need this import process to create the tables and not require coding. The person importing is non-technical. It would be a one-off process.

The target system is SQL Server 2008, though I am ideally interested in a solution not tied to a specific version.
The data to import is ~100MB in a single CSV file.
There are embedded field/row delimiters, which works fine when the fields are quoted with the Python csv module.
There is no need to handle foreign keys, indexes, constraints, etc.
The encoding is UTF-8.
The source is scraped data from German web pages currently stored in a CSV file. I could easily import it from there into MySQL / Postgres if that was a reliable vector.

I won't be able to test the import because I don't have direct access to the SQL Server database, so am looking for a more reliable approach. For example a MySQL dump, XLS, XML, etc.

There are some issues with this request:

What version and edition of SQL Server is the target system?
How much data is being imported? 10k, 10 Mb, more?
How many CSV files are there?
You have stated that handling of double-quotes is required, implying text-qualified fields and embedded text-qualifiers. Will there also be embedded field delimiters (i.e. , )? Those usually aren't a problem, but will there also be embedded row delimiters (i.e. \r\n or just \n )? These are the biggest problem for most CSV parsers.
In a comment on Scott's answer you raise a concern about: "Double quotes is one example and I expect there are other shortcomings if such a basic feature is missing - unicode, size limits, etc.". Can you please update the question to include these and any other missing requirements?
In a comment on the question, you ask, regarding SSMS: "will this create the required tables or they must be predefined?". Is creating the tables a requirement of this import process? If so, or even if creating the tables is an optional benefit, can it please be stated clearly in the question?
If the tables do not already exist, what needs to happen with regards to Foreign Keys, Indexes, Default Constraints, Check Constraints, etc?
What collation is being used, or at least is desired for the SQL Server tables? Is the plan to simply inherit the current default collation for the database that you are importing into? And to be clear about the term "collation" since it can have slightly different meaning depending on the system you are using, I am speaking of: Locale / LCID / Culture / Code Page (if applicable). It would also help to know if the source is sensitive or insensitive for case, accents, etc, or if a binary collation is being used.

  Is there a more reliable intermediary format that could be generated from Linux/OSX? For example MySQL dump, Excel, Sqlite, XML, etc?

The only truly reliable format will be something generated by SQL Server. XML is generally very reliable for transporting the data (this is what XML was meant to do), and doesn't have the parsing issues that are inherent in delimited files (i.e. embedded text qualifiers and delimiters). BUT, you still need to create the tables, and you need to write, and test the parsing of the XML into a result set so that it can be inserted into the tables. HOWEVER, (continued in the next item)...
You stated in that comment on Scott's answer:

  Problem is I won't be able to test the import because I don't have direct access to the MS SQL database, so am looking for a more reliable approach.

"Reliability" can only be determined through testing. It doesn't matter what should work, anything can go wrong. For example, many folks are not aware that it is common for XML to have an encoding of UTF-8, yet SQL Server only handles UTF-16 (Little Endian) for XML or even NVARCHAR data. Seeing as how this data is coming from "Linux/OSX", I would expect the default encoding to be UTF-8.

The encoding issue and other nuances should all (well, "mostly") reveal themselves in testing, but you have no way to test. Hence it will be difficult to get a very reliable answer regarding a reliable import mechanism.
What is the source of the data? I assume it is either Linux or OSX as those were mentioned in the question. But is it coming from MySQL specifically (since a "MySQL dump" was also mentioned)? Knowing if the source is MySQL or PostgreSQL or flat files, etc will help determine what tools are both available and best suited to this request.
Regardless of the source, however, keep in mind that the destination (i.e. Windows / SQL Server) is most "comfortable" dealing with UTF-16 (Little Endian) encoded data. So while UTF-8 might be the default output type for Linux/OSX-based systems, if there is an option for using UTF-16 Little Endian for the output encoding, that will help reduce potential issues once the script is moved over to the destination system.

One last thing to consider: You mention "MySQL dump" as a potential "reliable format", and those dumps include the DDL (i.e. CREATE TABLE) and DML (i.e. INSERT) statements. If you have the ability to write a script that contains the DDL statements (if necessary) and DML statements, then you don't have to worry about any formatting issues. Just convert the CSV data into INSERT statements and be done. The only issue you would be left with is not being able to test the scripts. But still, outside of which type of quote or brackets to use for object names and string literals, it's really hard to go wrong with:

INSERT INTO table (column1_name, column2_name, ...) VALUES (column1_val, column2_val, ...);

And to make things even easier:

Enclose table and column names in square brackets: [ and ] (e.g. [Customers]), or double-quotes: " (e.g. "Customers")
Enclose string, date, and GUID literals in regular single-quotes / apostrophes: '.  (e.g. 'some text')
If any string literals are for Unicode / NVARCHAR / XML columns, prefix those literals with a capital-"N": N  (e.g. N'some unicode text')
Make sure that any date / datetime values are formatted in a compatible way (there are several options and some depend on the current language setting in SQL Server, but generally YYYY-MM-DD HH:mm:ss.mmm should be fine)
Max size/length for CHAR / VARCHAR and BINARY / VARBINARY columns is 8000. Anything larger will need to use VARCHAR(MAX) and VARBINARY(MAX), respectively.
Max size/length for NCHAR / NVARCHAR columns (i.e. Unicode) is 4000. Anything larger will need to use NVARCHAR(MAX).

Since MySQL was mentioned in the question, even if indirectly, I figured it wouldn't hurt to test using mysqldump to see how close it can actually get. I used the following options:

--flush-logs
--compatible=mssql
--no-create-db
--no-tablespaces
--order-by-primary
--default-character-set=utf8
--flush-logs
--skip-set-charset
--hex-blob
--result-file
--skip-add-locks
--skip-triggers
--skip-comments
--skip-disable-keys
--compact
--skip-extended-insert
--skip-add-drop-table
--quote-names

Some notes:

I used utf8 as the charset since utf16le, ucs2, and a few others are not allowed in this particular context :-(
The bottom two options -- --skip-add-drop-table and --quote-names -- might not need to be explicitly stated as they might be implied by --compatible=mssql
At minimum you will need to do some string replacements on the output file to adjust the syntax for SQL Server. In the list below, pay close attention to the space before, and sometimes after, the "before" and "after" strings as they are necessary to prevent matching parts of valid strings.

" int(11) " -> " INT "
" timestamp " -> " DATETIME "
" text " -> " VARCHAR(MAX) "
" blob " -> " VARBINARY(MAX) "
" DEFAULT NULL" -> " NULL"  (no space at the end of either the "before" or "after" strings)

After making the 5 adjustments noted directly above, you are still left with a few potential issues:

Did any of the string replacements above accidentally match text within any string columns?
If there are any varchar columns with a length of 8001 or more, those lengths will need to be translated to the string MAX so that the resulting data type will be VARCHAR(MAX) instead of VARCHAR(20000).
If you have any Unicode data, especially any characters that do not fit into the Code Page of the default collation of the destination database, it will get converted into question marks ( ? ) unless you can find a way to prefix those string literals with a capital-N. Along these same lines, the CREATE TABLE statements will need to be adjusted for any Unicode columns such that they are NCHAR / NVARCHAR instead of CHAR / VARCHAR. Non-Unicode columns can remain as CHAR / VARCHAR.
potentially other nuances / differences that I have missed.

P.S. For the sake of completeness as it relates to migrating data from MySQL to SQL Server, Microsoft does have a Migration Assistant, but from what I can tell so far it requires a connection from SQL Server to MySQL and will not work on a dump file.

UPDATE

New info (from comment on this answer):

  The source is scraped data from German web pages currently stored in a CSV file. I could easily import it from there into MySQL / Postgres if that was a reliable vector.

So here's a thought. If you can get this data into one RDBMS, then why not another? Assuming you were going to set up a temporary Windows VM for testing anyway, you could just:

Install SQL Server 2008 Express Edition (i.e. free) on the VM
Create a database to hold this data (it will make life much easier if you can find out from your client what default collation their database is using and use the same one for this new database!)
Create the intended schema (tables, etc) for this data
Import this data directly into these tables
Take a BACKUP of the this database
Create another database that will act as your client's database
Write a script that will:

RESTORE the backup file into a new database with a new name (not the original name)
copy the tables / data from the new database into the "customer" database
DROP the new / data migration database

TEST!
Give the backup file and the script that makes use of it to your client.

While this does require that you write a T-SQL script, it does not require any additional software or install, and the data is already in columns of the proper datatypes and doesn't need to be parsed :-).

P.S. Again, for the sake of completeness I was also looking at SQL Server Data Tools (SSDT) as a possible solution as it can export schema and data into a single .dacpac file and import that into a destination. However, this does require installing SSDT. But, you wouldn't have to write a T-SQL script as SSDT handles all of the SQL for you.


Defragmenting indexes SQL Server bases on avg_page_space_used_in_percent I am wondering if I should take into account information from

sys.dm_db_index_physical_stats

column

avg_page_space_used_in_percent

when deciding on defragmenting/not defragmenting an index.

How is it possible that I see low avg_fragmentation_in_percent value and also low value for avg_page_space_used_in_percent?

Based on Brent Ozar article I could think that I should rebuild an index as there is a lot of free space on pages so it is a waste of disk and RAM space.
But scripts as Ola Hallengren's, for example, analyze only avg_fragmentation_in_percent, don't they?

I have these rules of thumb:

if there are fewer than 10,000 pages in a table, I don't really care about fragmentation;
if the storage layer is solid-state, I don't really care about fragmentation;
once or twice a month I might rebuild indexes on really fragmented tables, but only if fragmentation is demonstrably causing performance issues, which it usually isn't.

Fill factor can affect fragmentation, as can page splits, bad clustered index choices, and so on. Find the actual performance issues using a baseline, and focus on those.


SQL Server Query with literals runs instantly, using variables takes minutes For Microsoft SQL Server 2017 (RTM-CU20) on Azure running an ERP system (so changing the code here is not an option).

I have a table with over 42 million rows.  It's a machine operating stats tracking table.   Users enter operating statistics by equipment, date, shift and type of stat (Operating hours, Tons, whatever).

The table has a primary key structure like this (please no comments about nchar instead of nvarchar or storing dates as strings, that's how the ERP vendor provides it and that's just what I have to work with):

equip nchar(12)       This is a 12 character equipment number
key_type nchar(1)     One of two values E for equipment or G - always E here
shift_seq_no nchar(2) Sequence of shifts (usually 00 or 01)
stat_date nchar(8)    Date, YYYYMMDD
stat_type nchar(2)    A code for stat type, OH here (operating hours)
trc_seq_no nchar(3)   A sequence number for when there's more than one entry per shift

It appears that the calling code (java based web app making a JDBC call to the database with JBOSS in the middle of it all) queries a list of equipment scheduled for preventative maintenance in a given date range and goes looking for the most recent stat for each piece of equipment, since the row will contain the cumulative stat value for the equipment.

It then puts together a very long SQL query that effectively does this (replace x with appropriate variables:

select * from table
where (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x)
or ( and on and on)

There are 73 repeats of the 'OR' in total

I generated two queries based upon what I can see is running.

One where I just have a straight up set of 73 sets of literals (equip = '000001234567' and col = 'value' and etc) - this runs instantly.  The actual execution plan shows a Clustered Index Seek (Clustered) in a Nested Loop with a Constant Scan showing 73 executions of 1 row read and a run time of less than 1 second

I then took the same query and declared 428 variables and plugged them into the query in place of the 428 literals instead. The actual execution plan shows a Clustered Index Scan (Clustered) in a single step (Execution plan says SELECT -> Filter -> Clustered Index Scan).    The hint shows all 42,271,100 rows read with a run time of 5 minutes and 34 seconds when run in SSMS.  This is a web based application and the debug tracing through the online app comes in at over 8 minutes.   You can imagine that just isn't deemed acceptable for an online screen.

Now that the long story is complete, does anyone have any recommendations of SQL Server tuning that might be able to be done in order to make this query run via variables the same way it runs with literals since I can't touch the code.

I think I'll be sending this back to the vendor for analysis as well, but I was hoping that someone here in the brain trust might have an idea for a quicker solution (since my Google Fu has failed me at the moment).

I've seen several articles about this happening when querying a view and the general solution seems to be "add a WITH (RECOMPILE) hint" but I'm not using a view here, I'm querying a table directly.

Thanks in advance for any help here.  I'm primarily an Oracle guy and know enough about SQL Server to be dangerous.

The root cause of this is simple. The query optimizer makes row estimates to determine how it's going to resolve the query. The row estimates are based on the statistics for the columns involved. When you give the optimizer literals, it uses exact values to look at the statistics for row estimates. In this case, those estimates result in a plan that gives you the seek, meaning it's found that for the specific value there are only a few matching rows and it uses the index to find those rows.

When you give the optimizer a variable, it can't know what the value within the variable is. So, instead of looking at the statistics for a specific value and making the row estimates based on that, it uses an average based on the overall selectivity of the statistics. In this case, an average suggests to the optimizer that lots of rows are likely to match so a scan is the more efficient mechanism for retrieval.

First thing I'd try is an update to the statistics. It's possible that they're out of date and the row estimates with a variable are just inaccurate. For the best possible statistics, use the FULL SCAN option. That will take longer and use more resources.

Next, possibly changing the query to a stored procedure and using parameters instead of local variables could result in better plans. This is because parameter values are known at compile time through a process called parameter sniffing (sorry, I didn't name it). You may see more consistent behavior because of parameter sniffing (although, sometimes you can see inconsistent behavior because of parameter sniffing).

Next, yes recompile could help. It may be that your data is just too wildly skewed for the row estimates to ever be consistently right. Recompiling every time you execute will mean that a plan unique for the values passed will be generated. Further, recompiling means that the variable values are now known and the sniffing process is applied.

It's also possible that the query just needs some restructuring. Lots of ORs like that with long lists of filter criteria sounds like a catch-all query. Recompile can help there, but you also might be better off restructuring to either nested procedures (73 of them is a lot though) or using dynamic T-SQL to build individual queries instead of long, lists of OR statements.

The last thing you have is that you're on SQL Server 2017. You can enable Query Store and use plan forcing to make it use the plan that performs better.


mysql multi-source replication to single slave from Debian repo's? I've been experimenting with mysql 5.7's feature to backup multiple sources to a single slave, but have legacy db's on source servers where I'm not sure if upgrading to 5.7 will break things, and also I want to use standard packages in Debian repositories to more standardized support. Does anyone have experience doing something like this, and/or is there a better way to approach it?

You shouldn't have to upgrade the master servers until you want to.

Officially, replication requires that the slave server be the same version as the master or newer.  That might seem counterintuitive, but derives from the fact that the replication protocol is always forward-extensible, but not always backward-compatible.  New capabilities can be added to the replication format, and when this happens, it only adds to what the master might write, but it adds to what the slave must be able to interpret.

As a rule, newer slaves know all the tricks an older master can do.  In contrast, newer masters can blow the mind of an older slave, by doing things the older slave won't recognize.

Because of the way replication is designed, the slave can actually learn the version of a master, but the master has essentially no way to learn the same information about a slave... nor is this needed, because the master only has one binlog, and all connected slaves receive exactly the same event stream.

Officially, replication is supported with a gap of one major release... the slave running 5.5 with a master running 5.1 (GA skipped from 5.1 to 5.5, so that's "one.") or the slave running 5.6 and master running 5.5, etc.

Unofficially, from 5.1 onward, you can generally make it work with any gap in the releases, as long as the slave is always newer, and you don't enable slave features the master doesn't implement, where the feature requires both servers to have the same configuration, such as binlog checksums or GTIDs. (...and you understand that Oracle won't be interested in any bug reports about a larger gap not working...)

Connecting a 5.7 slave to any number of pre-5.7 should be workable... but an important point here, relevant to your situation is that in the master/slave relationship, the slave is really in control of the entire interaction.  It initiates the connection to the master, sets any options on the connection, and asks the master to start sending the replication stream.  The master then dutifully sends replication events until the slave disappears, at which time the master closes the abandoned connection and forgets all about the slave.

Try it. The worst thing that should be possible is that you may have to change some defaults on the slave to get it to negotiate successfully with the older masters, but the older masters should not need any changes or be negatively impacted in any way by a connection from newer slave.

It's a matter of opinion, but I never, ever let the package manager mess with my MySQL servers. The package manager can be a bit of a black box, and if anybody is going to screw up my servers, it's going to be me.  If an upgrade ever fails, it's going to be me who broke it. If you use the "Linux Generic" tarballs from Oracle, it's pretty straightforward.

If you symlink /usr/local/mysql to point to the tarball's extraction directory (which is named for the version and architecture) and then symlink data inside the extraction directory to point to your "datadir," then upgrading MySQL to a new version is as simple as extracting the new tarball, stopping the server daemon, changing 2 symlinks, restarting the daemon, and running mysql_upgrade. If the new version won't start for any reason, well, hey, the old version is still right there.  Move the symlinks back, and punt.

The only dependency you have to take care of manually is installing libaio1, which is in the standard distro repositories, just not installed by default.


PostgreSQL - trying to speed up query responses from view that has join and group by I have these two tables:

precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).

Column   |         Type
---------+----------------------------
 id      | varchar
 country | varchar(2)
 time    | timestamp without time zone
 freq    | varchar
 prec    | float(4)
(2,481,069 rows)

areas: store multipolygon areas for all Europe down to municipality resolution.

Column   |         Type
---------+----------------------
 id      | varchar
 country | varchar(2)
 geom    | geometry
(162,573 rows)

I need to create a view that combines the spatial and temporal values from these tables, so I created it this way:
create or replace view prec_geo
as select * from
    (select
        max(country) country,
        id,
        "time",
        sum(prec) as prec,
    from precipitaion
    where id <> 'ALL'
    group by "time", id) as prec,
    areas.geom
where prec.id = areas.id and prec.country = areas.country;

The view now shows the columns and values I need to make further queries.
In particular, I need to make queries selecting different time and freq.
EXAMPLE:
select geom from prec_geo where "time" = '2020-03-13 00:00:00' and freq = 'daily' and country = 'it';

I created different indexes on my tables in the hope to speed up the queries.
In precipitation table I created:

a combined index on multiple cols (time(1), freq(2), coutry(3))
a non unique index on id col (because it is used in the join to create the view)

In areas table I created:

a spatial PostGIS index on the geom col
a combined index on multiple cols (country(1), id(2))

I then ran VACUUM ANALYZE on both tables.
Still, the result of the query takes about 7 to 10 seconds, while I would need faster responses.
What would you suggest in order to increase performance?
Info on my setup: PostgreSQL10, PostGIS 2.5.

where id <> 'ALL'

is not sargable. Try to rewrite with id IN (...) or id > 'ALL'
Create an index like this one :
CREATE INDEX X ON precipitaion (id, "time") INCLUDE (country, prec)


Sum of two marks divided by 2 for each student The image shown below is the answer I found, but I want the sum of two marks divided by 2. The image shows that we take only the better mark from task 1 and task 2. I want the better mark from task 1 and task 2 and add the "pen paper" mark divided by 2 for each student. My code is shown below:

WITH StudentMarks
AS (
SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
WHERE  m.AssessmentToolID IN ('3','4')
)
SELECT Class
,Division
,Subpart
,Term
,Subject
,StudentID
,Mark
,Grade
,Assesmenttool
,Studentname
FROM StudentMarks a
WHERE Mark = (
    SELECT max(Mark)
    FROM StudentMarks
    WHERE Class = a.Class
        AND Division = a.Division
        AND Subpart = a.Subpart
        AND Term = a.Term
        AND Subject = a.Subject
        AND StudentID = a.Studentid

    )

union

SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
where (M.AssessmentToolID=5)

"I want the better mark from task 1 and task 2 and add the "pen paper" mark divided by 2 for each student."

Let's work backwards from the answer that you want. Suppose that you had a derived table or CTE with the following columns:

StudentName
Task1Mark
Task2Mark
PenPaperMark

With that data structure it would be fairly straightforward to calculate what you need, right? Something like this:

SELECT
  StudentName
, 0.5 * (PenPaperMark + CASE WHEN Task1Mark >= Task2Mark THEN Task1Mark ELSE Task2Mark END)
FROM
(
...
) t;

You may need to account for NULL values or do some kind of rounding, but if you have the columns that I listed before you can perform the necessary calculation. So how can you get one row per student with the column values that you need?

It looks like your source data has one row per student and per mark. You need a way to combine the multiple rows from each student into one summarized row per student. PIVOT can turn rows into columns. You can also code it as a GROUP BY query. Since you want one row per student you would group by that column. So something like this could work for a definition of t:

SELECT
StudentName
, MAX(CASE WHEN AssesmentTool = 'Task 1' THEN Mark ELSE NULL END) Task1Mark
, MAX(CASE WHEN AssesmentTool = 'Task 2' THEN Mark ELSE NULL END) Task2Mark
, MAX(CASE WHEN AssesmentTool = 'Pen Papper Test 1' THEN Mark ELSE NULL END) PenPaperMark
FROM
...
GROUP BY StudentName

All that remains is to complete the FROM clause with the appropriate tables from your data model.


VARCHAR missing index not showing up in missing index dmv or query plan suggestions I have a table with 307200 records, with f_column being VARCHAR(30). No missing index are shown in the missing indexes dmv or suggested in the execution plan.
SELECT f_column, f_column2 FROM t_table WHERE f_column = '23BE46F3-E9A9-4526-A2F8-3F51818025B5'

The query returns 5 results, costing 9958 logical reads. With the index below, it costs 3 logical reads.
CREATE NONCLUSTERED INDEX IX_t_Table_f_column ON t_table (f_column) INCLUDE (f_column2)

What could cause the missing indexes to not show up in both the missing indexes dmv and execution plan when adding the index would benefit the query tremendously?
I initially suspected statistics, and updated all statistics for the table, but the issue still persists. For some reason, adding or altering a column on the table will cause SQLServer to come back to its senses, and the missing indexes appear in missing indexes dmv as well as the execution plan on the query's next execution.

Almost certainly because it is seen as a trivial plan and those do not activate the missing index feature, there´s a workaround in this blog by Mr Darling
https://www.erikdarlingdata.com/sql-server/whats-the-point-of-1-select-1/


SQL Query Killed, remains in Kill/Rollback with estimated time increasing I killed a transaction and now it remains in Killed/Rollback status and whenever I run Kill 64 with statusonly I get this output:

SPID 64: transaction rollback in progress. Estimated rollback completion: 56%. Estimated time remaining: 12860 seconds.

However it stays at 56% and the estimated time only increases. It has been this way for a few hours atleast.

The query that I killed was dbcc shrinkdatabase(new2012, 10)

How large is your database? It's not uncommon for rollback transactions to take considerably longer than the operations they are trying to do, especially if affected objects have dependencies.

Another important question is what environment are you running this on? I'm assuming you're not running it on live, which leads to a couple more questions, specifically:
-How long can you afford to wait for it?
-Can you kill the database and restore a backup?

If you can't wait for an long or potentially unknown amount of time, your best bet might be simply restoring a backup. If you don't want to do that, you might want to wait for a while longer. You can use the sys.dm_exec_requests DMV to get information on wait times and whether or not it's being blocked, and on what might be blocking it.

SELECT *
FROM sys.dm_exec_requests

After you find columns you might find useful, you can revise the select statement to get rid of the spam.

For more information, see this post by Paul Randal (SQL MVP and author) on the shrink command: http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/


Adding Row Version to SQL Server table I have a table in a database with three columns: ID, Key and Value.  Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's schema is this:

RowID (new ID field for this table, identity(1,1) column)
ID (from source table)
Key (from source table)
Value (from source table)
Version (integer)

So anytime the first table record's changed, it creates a new record in the second with the table everytime.

For every record with a matching ID, I want to add a unique incremental version number.  If a record with ID of 1 is entered into this table 5 times, those records should have version numbers of 1, 2, 3, 4, and 5.  Is the way to accomplish this through a subquery for existing version numbers, or setting up a partition, or something else?

Thanks.

In such cases I am storing version numbers, and using referential integrity to make sure version numbers begin with 1 and have no gaps. Because I use constraints, I am sure my solution works in high concurrency situations. I have described this approach in this article