How Does Oracle Handle Multiple Concurrent INSERTs Against One Table I am trying to understand Oracle 11g a little more closely.  My question is simple: how does Oracle handle two sessions that are attempting to insert records into a single table at the same time.

For example, INSERT A runs at 1:00PM against Table 1.  It will take 5 minutes to complete.
INSERT B is executed at 1:02 PM against Table 1.  It will take 1 minute to complete.

What will happen?  Will Oracle queue INSERT B to be executed after INSERT A is finished?  Will INSERT B run simultaneously with INSERT A?

Thanks!

The inserts don't affect each other unless they would have a conflict with unique or primary key constraints. They should be independent. I believe this concurrency issue is one of the reasons why Oracle's sequence is a separate object (cached, surrogate PK generation separated from insert).

In that respect, they operate on the uncommitted table as it is seen to their session. This behavior is described in the stackoverflow post below.

https://stackoverflow.com/questions/3194999/dml-by-multiple-users-commit-scenarios-in-oracle


Make SqlClient default to ARITHABORT ON First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection.

For performance reasons I've created an indexed view. As a result, updates to tables referenced in the view need to be done with ARITHABORT ON. However, the profiler shows that SqlClient is connecting with ARITHABORT OFF, so updates to those tables are failing.

Is there a central configuration setting to make SqlClient use ARITHABORT ON? The best I've been able to find is to manually execute that each time a connection is opened, but updating the existing code base to do this would be a fairly large task so I'm keen to find a better way.

Seemingly preferred approach

I was under the impression that the following had been tested already by others, especially based on some of the comments. But my testing shows that these two methods do indeed work at the DB level, even when connecting via .NET SqlClient. These have been tested and verified by others.

Server-wide

You can set the user options server configuration setting to be whatever it is currently bit-wise ORed with 64 (the value for ARITHABORT). If you do not use the bit-wise OR (|) but instead do a straight assignment (=) then you will wipe out any other existing options already enabled.

DECLARE @Value INT;

SELECT @Value = CONVERT(INT, [value_in_use]) --[config_value] | 64
FROM   sys.configurations sc
WHERE  sc.[name] = N'user options';

IF ((@Value & 64) <> 64)
BEGIN
  PRINT 'Enabling ARITHABORT...';
  SET @Value = (@Value | 64);

  EXEC sp_configure N'user options', @Value;
  RECONFIGURE;
END;

EXEC sp_configure N'user options'; -- verify current state

Database-level

This can be set per-database via ALTER DATABASE SET:

USE [master];

IF (EXISTS(
     SELECT *
     FROM   sys.databases db
     WHERE  db.[name] = N'{database_name}'
     AND    db.[is_arithabort_on] = 0
   ))
BEGIN
  PRINT 'Enabling ARITHABORT...';

  ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;
END;

Alternate approaches

The not-so-good news is that I have done a lot of searching on this topic, only to find that over the years a lot of others have done a lot of searching on this topic, and there is no way to configure the behavior of SqlClient. Some MSDN documentation implies that it can be done via a ConnectionString, but there are no Keywords that would allow for altering these settings. Another document implies it can be changed via Client Network Configuration / Configuration Manager, but that does not seem possible either. Hence, and rather unfortunately, you will need to execute SET ARITHABORT ON; manually. Here are some ways to consider:

IF you are using Entity Framework 6 (or newer), you can try either:

Use Database.ExecuteSqlCommand: context.Database.ExecuteSqlCommand("SET ARITHABORT ON;");
Ideally this would be executed once, after opening the DB connection, and not per each query.
Create an interceptor via either:

DbConfiguration.AddInterceptor
DbInterception.Add

This will allow you to modify the SQL before it is executed, in which case you can simply prefix it with: SET ARITHABORT ON;. The downside here is that it will be per each query, unless you store a local variable to capture the state of whether or not it has been executed and test for that each time (which really isn't that much extra work, but using ExecuteSqlCommand is probably easier).

Either of those will allow you to handle this in one spot without changing any existing code.

ELSE, you could create a wrapper method that does this, similar to:

public static SqlDataReader ExecuteReaderWithSetting(SqlCommand CommandToExec)
{
  CommandToExec.CommandText = "SET ARITHABORT ON;\n" + CommandToExec.CommandText;

  return CommandToExec.ExecuteReader();
}

and then just change the current _Reader = _Command.ExecuteReader(); references to be _Reader = ExecuteReaderWithSetting(_Command);.

Doing this also allows for the setting to be handled in a single location while requiring only minimal and simplistic code changes that can be mostly done via Find & Replace.

Better yet (Else Part 2), since this is a connection level setting, it doesn't need to be executed per each SqlCommand.Execute__() call. So instead of creating a wrapper for ExecuteReader(), create a wrapper for Connection.Open():

public static void OpenAndSetArithAbort(SqlConnection MyConnection)
{
  using (SqlCommand _Command = MyConnection.CreateCommand())
  {
    _Command.CommandType = CommandType.Text;
    _Command.CommandText = "SET ARITHABORT ON;";

    MyConnection.Open();

    _Command.ExecuteNonQuery();
  }

  return;
}

And then just replace the existing _Connection.Open(); references to be OpenAndSetArithAbort(_Connection);.

Both of the above ideas can be implemented in more OO style by creating a Class that extends either SqlCommand or SqlConnection.

Or Better yet (Else Part 3), you can create an event handler for the Connection StateChange and have it set the property when the connection changes from Closed to Open as follows:

protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
    if (args.OriginalState == ConnectionState.Closed
        && args.CurrentState == ConnectionState.Open)
    {
        using (SqlCommand _Command = ((SqlConnection)sender).CreateCommand())
        {
            _Command.CommandType = CommandType.Text;
            _Command.CommandText = "SET ARITHABORT ON;";

            _Command.ExecuteNonQuery();
        }
    }
}

With that in place, you only need to add the following to each place where you create a SqlConnection instance:

_Connection.StateChange += new StateChangeEventHandler(OnStateChange);

No changes to existing code are needed. I have just tried this method in a small console app, testing by printing the result of SELECT SESSIONPROPERTY('ARITHABORT');. It returns 1, but if I disable the Event Handler, it returns 0.

For the sake of completeness, here are some things that do not work (either at all or not as effectively):

Logon Triggers: Triggers, even while running in the same session, and even if running within an explicitly started transaction, is still a sub-process and hence its settings (SET commands, local temporary tables, etc) are local to it and do not survive the end of that sub-process.
Adding SET ARITHABORT ON; to the beginning of each stored procedure:

this requires a lot of work for existing projects, especially as the number of stored procedures increases
this does not help ad hoc queries


Running queries/jobs via SMSS - SQL service and SQL agent service accounts (SQL Server) If a command, for example the below command, is executed from SQL Server Management Studio while I am logged in the session with a windows authentication login, I understood that the access to the path 'C:\SQL2019\BookStore.bak'is done  via the sql service account (set in the SQL Server Configuration Manager) and not via my login. However, if an INSERT statement is done, is it done via my login in that case?

BACKUP DATABASE [BookStore] TO  DISK = N'C:\SQL2019\BookStore.bak'
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'BookStore-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Also, I have an agent job set and in the job history it states "Executed as user: sqlagent1" which is the  sql agent service account that I have set in the SQL Server Configuration Manager. The run as in the job step properties has been left empty. However, there is no login created for this account under the Logins in SQL Server. From where does this account get permission?

Job ownership is an important concept in SQL Server Agent. SQL Server Agent sets the security context for job execution based on the role of the user owning the job.

By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account.
(about run as empty : Users who are not sysadmin have to access to the proxy account explicitly granted to their role or username)

The exception to this rule is T-SQL job steps, which execute under the security context of the job owner.

If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.

A common mistake when setting up jobs is to make "sa" the job owner – this will cause all T-SQL job steps to execute as the SQL Agent service account, which is a system administrator account. A better option is to set a non-sysadmin account as the job owner, and explicitly grant only the required database permissions to this account.

https://stackoverflow.com/questions/12112276/sql-agent-job-run-as-drop-down-list-is-empty

https://www.red-gate.com/simple-talk/sql/database-administration/setting-up-your-sql-server-agent-correctly/


Restoring MySQL dump ERROR 1449 I took hot backup (dump) from my Master MySQL DB with below command

  mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > ~/dump.sql

Master Version: MySQL-server-5.5.41-1.el6.x86_64

At the time of restoration on slave we are getting this error, have I done something wrong.

  [root@Slave ~]$ mysql -u root -p < dump.sql
  Enter password:
  ERROR 1449 (HY000) at line 150536: The user specified as a definer ('lipl_ga_app'@'%') does not exist

Slave Version: mysql-community-server-5.6.28-2.el6.x86_64

The DB is huge and we don't want to start the restoration process again.

@ Nawaz Sohail I have taken backup of mysql user from below command

  mysqldump -u root -p mysql user > user_table_dump.sql

Restored

  mysql -u root -p mysql < user_table_dump.sql

I tried to solve this in many ways but, didn't find any workaround. then I tried to redo the whole thing again with the below steps.

I took a backup of mysql user from Master Host,

  mysqldump -u root -p mysql user > user_table_dump.sql

and restored mysql user on slave,

  mysql -u root -p mysql < user_table_dump.sql

then I restored the master dump on slave.

  [root@Slave ~]$ mysql -u root -p < dump.sql

The master dump restored without any error.


Why is this not in 2NF and 3NF form? R = {A, B, C, D, E, F}

Dependencies:

{A} -> {C,F}
{C} -> {D}
{B} -> {E}

a. this relation is in 1st normal form (NF). Explain why it is not in 2nd NF and not in 3rd NF.

b. make the required adjustments to convert relation to 2nd NF.

c. make the required adjustments to convert relation to 3rd NF.

If I understand you correctly,

{A} determines {C,F}, and {C}determines {D}, so {A} determines {C, D, F}

Also {B} determines {E},

Put together, this means {A, B} uniquely determines the entire row {A, B, C, D, E, F}. Therefore, columns {A, B} is a key of relation R.

Now, 2NF is violated if a non-key field is determined (functionally dependent) by a proper subset of the key, which is obviously the case here for {B} -> {E}. i.e. part of the key {A, B} alone is sufficient to determine a non-key field {E}. This causes all the redundancy issues etc. and violates 2NF. So R is not 2NF.

To normalize R to 2NF, we need to decompose R into

R1 = {A, C, D, F}

and

R2 = {B, E}

R3 = {A, B}

Now, {A} is key for R1 and {B} is key for R2, and they have no proper subsets (non-empty). {A, B} is key for R3 and there is no non-key field in R3. Therefore, R1, R2, R3 are in 2NF.

3NF is violated if a non-key field functionally depends on another non-key field. This is the case for R1 where we have {A} -> {C} and {C} -> {D}, which causes similar problems with the violation of 2NF. To fix/normalize this, we can split R1 = {A, C, D, F} further into:

R11 = {A, C, F}

and

R12 = {C, D}

Now, it's easy to verify that R11, R12, R2, R3 together are in 3NF.


Find rows where data resets So I have a table tbl where I'm trying to pull data that increments at a variable rate.  I need to identify when the data resets so I can make a cumulative value.  Here's an example table:

ID    VAL
 1     12
 2      2
 3     14
 4     20
 5     24
 6     25
 7      2
 8     11
 9     14
10     15
11     18
12      0
13      5

How do I identify in a SQL query which rows have reset data?

In this table example, I'd want to identify row IDs 2, 7, and 12 as the points where the data resets.

The end result is so I can add rows 1, 6, and 11 (with other data) to the current value for a total cumulative value.

;WITH x AS
(
  SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID)
  FROM dbo.your_table
)
SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL
FROM x
WHERE VAL < Prev_VAL
ORDER BY ID;

If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is about 4X as expensive:

;WITH x AS
(
  SELECT ID, VAL, Prev_ID = ROW_NUMBER() OVER (ORDER BY ID)
  FROM dbo.your_table
)
SELECT c.ID, c.VAL, p.VAL, Cumulative = c.VAL + p.VAL
FROM x AS c
LEFT OUTER JOIN x AS p
ON c.Prev_ID = p.Prev_ID + 1
WHERE c.VAL < p.VAL
ORDER BY c.ID;


Show Processlist state "Statistics" Please note that it's not a duplicate thread because I already set variable optimizer_search_depth and didn't have any impact.

Sometimes, our database/application services crashed due to max connection limit. When we see show processlist output we found lot of sessions in the "statistics" state. Our system immediately get normal if we kill these connections. We are not running any DDL/optimize/stats-update related commands during that period. We are using 5.7.12 on Linux Centos 7.5

How can I find the source of these sessions?

This can be related to Bug 83912. If you run SHOW ENGINE INNODB STATUS you may see transactions like this:

  ---TRANSACTION 4216198446592, not started sleeping before entering InnoDB

In order to fix it:

SET GLOBAL innodb_thread_concurrency=0;


Can't change authentication mode from Windows to both SQL Server and Windows As the title suggests I'm having problems changing the authentication mode from Windows to both SQL Server and Windows mode in version 12.0.2. I have tried right right-clicking the server, go to security and change it there but I'm getting an error when doing this. I installed it originally using both but changed it to windows only mode earlier this evening.

The error message I'm getting is long winded but the end of it is:

  The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error; 229)

Any idea what that means? I'm guessing I don't have the required permission but I'm not familiar enough with it to know what to do next. I have the sa password but can't enter it while I'm stuck in Windows mode.

I've been trying to figure out why I can't seem to write to the database all day and in desperation tried changing the mode to see if that would fix it... but it's only made things worse.

While connected using Windows credentials I tried ALTER LOGIN sa ENABLE also and was given the following error message in response:

  Cannot alter the login 'sa', because it does not exist or you do not have permission.

I did try altering the registry too to change the mode but realised I was reading a post relating to SQL Server 2005

Thanks in advance for your help.

The EXECUTE permission was denied on the object
  'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'.
  (Microsoft SQL Server, Error; 229)

You can fix the problem by granting the user direct access to the procedure.

USE master
GO

GRANT EXECUTE ON [sys].[xp_instance_regread] TO [DOMAIN\USER];

--To veriy that the user has been granted the privilege
EXECUTE AS USER = 'DOMAIN\USER';Select * from fn_my_permissions('xp_instance_regread','Object')

  Cannot alter the login 'sa', because it does not exist or you do not
  have permission.

Error message saying that, You do not have administrator's permissions

Log into SQL Server Management Studio as an Administrator, because only admin user has permission to change passwords for users.

How to change sa user password see here: How to change a password for the sa user in MS SQL? and this link


Where should you define foreign keys? Is it better to define foreign keys in the database or in the code part of an application?

Put the foreign keys on the database.  Even if you validate the data in the application before you save it the FK's are a good piece QA backup.  For a first approximation, applications always have data issues.  Leaving controls like this out of the system just invites failure modes where data gets corrupted silently.

There's nothing like working in data warehousing for a few years to see this in action.  You spend your time picking up the pieces after noddy mistakes by application developers who thought they could enforce data integrity in the application code.  Spend any time doing this and you will conclude that application managed data integrity is little more than a conceit.

In addition, the query optimiser can use foreign keys to infer things about table joins, so FK's will result in more efficient query plans.

There are plenty of other benefits to foreign keys as well.  Do everyone a favour - put the FK's on the database.


The database manager failed to allocate shared memory because an operating system kernel memory limit has been reached I have installed IBM DB2 10.5.5 and I want to create a new database there.

When I entered command

db2 create database test1

I am getting a error saying

SQL1084C  The database manager failed to allocate shared memory because an
operating system kernel memory limit has been reached.  SQLSTATE=57019

Following is the output I get when I run "ipcs -l"

 ------ Shared Memory Limits --------
max number of segments = 10000
max seg size (kbytes) = 4882812
max total shared memory (kbytes) = 33554432
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 4096
max semaphores per array = 250
max semaphores system wide = 1024000
max ops per semop call = 32
semaphore max value = 32767

------ Messages Limits --------
max queues system wide = 16384
max size of message (bytes) = 65536
default max size of queue (bytes) = 16384

What is the reason for this? How can I fix it?
I am running on Ubuntu 14.04 with 8GB physical memory.

I just had the same problem! In my case the problem was just the memory of the DB2 instance.

In windows type:

db2 get dbm cfg | findstr MEMORY

In linux:

db2 get dbm cfg | grep MEMORY

You will get something like that:

Global instance memory (4KB)          (INSTANCE_MEMORY) = 500000

The solution in my case was simply set the same threshold value but by activating the automatic mode:

db2 update dbm cfg using INSTANCE_MEMORY 100000 automatic immediate

The command "immediate" apply the settings now without the need to restart the instance. So if you get the settings again will have:

Global instance memory (4KB)          (INSTANCE_MEMORY) = AUTOMATIC(5000000)

Now try to connect to your database again. I believe that will again connect normally.

According to the DB2 documentation:

  This parameter specifies the maximum amount of memory that can be
  allocated for a database partition if you are using DB2® database
  products with memory usage restrictions or if you set it to a specific
  value. Otherwise, the AUTOMATIC setting allows instance memory to
  grow as needed.

Font: IBM Knowledge Center


Change drive letter (which contains system dbs) Is it possible to safely change the drive letter for a volume which holds only system databases?

What precautions should be taken and how should it be done? I know I can just go to computer management > storage and change drive letter but can have negative consequences on SQL Server operation?

You should follow the procedure outlined in the MSDN article "Moving System Databases"


How to change the path of data during database restore + SQL Server 2008 I am trying to restore a big database in SQL Server 2008 R2. The database is about 700GB in size. My SQL Server is installed in C drive which has only 100GB of space left in it. I have the .bak file on the D drive from which I need to restore the database. Is there a way I can restore this database without increasing the space on C drive? (The D drive has 5TB of space and I would want SQL Server to access/place the data from/on D drive)

Any input will be highly appreciated.

You can redirect the data files using the move command

USE [master]
RESTORE DATABASE [{DBName}]
FROM  DISK = N'{PathToBakFile}'
WITH  FILE = 1,
MOVE N'{DataFileLogicalName}'
    TO N'{NewPath}',
MOVE N'{LogFileLogicalName}'
    TO N'{NewPath}'
GO


Weak Entities + Partial Keys + 2NF I'm a bit confused on the concept of weak entities and normalization and couldn't find any answers online. I know that generally weak entities have a partial key to uniquely identify its own records but the primary key is usually a composite key made up of this partial key and the foreign key (that refers to the weak entity's parent's primary key). Before I thought we could just have the foreign key be the sole primary key for the weak entity but apparently this is not such a good idea.

I am confused however on normalization, specifically 2NF where we have to make sure that no non-prime attribute is solely dependent on a subset of the primary key.

I'm missing something crucial in my understanding here. So wouldn't a weak entity be in violation of 2nf? Because a non-prime attribute could be dependent solely on the foreign key?

So wouldn't a weak entity be in violation of 2nf? Because a non-prime attribute could be dependent solely on the foreign key?

No.  Consider a typical example:

SalesOrder(OrderId,OrderDate) PK (OrderID)
SalesOrderItem(OrderId,OrderItemNum,ProductId,Quantity,Price) PK (OrderId,OrderItemNum)

No OrderItem non-key attribute is functionally dependent on OrderItemNum.  OrderItemNum could be repeated for multiple orders.  In practice it's often not, but for the purposes of normalization it's just the trailing column of the primary key.


Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher In Sql Server 2014, how to query a list of Subscriptions & its articles for a given Publication from Publisher? (transactional replication)

Yes, there are easier ways to query from Distributor but I need a query from Publisher side.

Remote Distributor model (if it helps)

Thanks,

To get subscription list from a publisher database, you can run below tsql :

use publisherDB;
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid

Incase if someone needs for merge replication :

use publisherDB
go
select distinct
    sa.name as articlename,
    sp.publisher as publisherserver,
    sp.publisher_db as publisherdb,
    sp.name as publicationname
    ,ss.subscriber_server as subscriberservername
from dbo.sysmergearticles sa
join dbo.sysmergepublications sp on sa.pubid = sp.pubid
join dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
order by subscriberservername


TDE Master Key Rotation Does changing the TDE Master Key (DB Master Key and/or the DB encryption key) always require decryption and re-encryption?  If not, at what version did SQL Server begin to allow you to change the Master Key and not have to decrypt/re-encrypt?

My background is in Oracle, which handles TDE a little differently.

Does changing the TDE Master Key always require decryption and re-encryption?
  The DB Master Key and/or the DB encryption key.

The main two secrets involved in TDE are the Database Encryption Key (DEK) and the Server Certificate. The DEK is what actually encrypts and decrypts the data in the database, but the Server Certificate is used to protect (among other protections already involved) the Database Encryption Key (DEK).

To your question, If you rotate the DEK you must decrypt and encrypt all data in the database because it is the key which does this.

If, however, you rotate the Server Certificate protecting the DEK, then no data encryption or decryption of the physical database would need to take place.

It doesn't matter the version or type of software, if you encrypt data with an asymmetric key pair and want to rotate to another asymmetric key pair, you'll first need to decrypt the data with the old set of keys and encrypt it with the new.


OpenEdge Progress Replication There's data sitting in OpenEdge Progress databases. We'd like to replicate that data to a PostgreSQL "datalake" using a software called HVR (high volume replication I think). What the software does is basically install a local agent and replicate data from a source DB to a target DB.

Does anybody know if Progress allows such a thing?

I know it has its own replication mechanisms but that's not what we're looking for.

You don't have many options here:

1) if you are on OE11.7 and later, you can use OpenEdge Change Data Capture to track data and replicate it http://knowledgebase.progress.com/articles/Article/How-to-implement-CDC-Change-Data-Capture-with-OpenEdge

2) if you are on older version, you might use Pro2 software. I am aware of Pro2SQL for MS SQL target and Pro2Oracle for Oracle DBA.

https://www.progress.com/campaigns/openedge/Pro2v5-Webinar

EDIT: did not read the question in full: your best option is to ask Progress itself, but I don't see why not: your agent will be just another user connecting to database, so if you have enough user licences, you should be fine.

Edit#2: on older versions of OE Pro2 uses older solution to track changes: it installs triggers for tables you want to replicate, they fire up and record change in auxhillary database, then replication agent ran on Management Server actually replicate records to Target. Management server also contains tools to generate SQL schema updates, new triggers if Source schema changes, etc.

CDC by itself allows you to track changed records but the rest is your responsibility: creating schema for target, generating SQL loads, etc. With newer version of OE, Pro2 utilizes CDC to speed up replication.


How to scale when database gets huge? I'm creating a project that could get a lot of users very quickly and I wanted to know what is the suggested approach I should consider from the start.

I'm developing it with a PHP framework and MYSQL both optimized as far as my knowledge allows :P I basically want to start small...I mean running on a simple VPS and scale to better hardware as needed but in the end I was thinking that one database's table could be become a real problem.
Basically there will be a table that stores activities every user could do 100 times per day (I'm not going into details) so considering that in the future I would have, say, 10k users doing that every day I would get to a point of millions of new data per day (even if it just stores 2-3 numbers). And this table even if it's not queried to retrieve data at every visit but only only to INSERT I would need to read it and manipulate data...and what happens with 1 trillion rows? I'm not an expert but I'm pretty sure that having those numbers in a table cannot be good especially if it grows everyday non-stop.

SO my questions are:

is there another database method I should start with? Something in front of MYSQL made for large tables? If any!
how do big web sites handle these things? They store "part" of the rows on multiple servers?
is it OK to use the simple BIGINT (primary key) for this table considering that is not unlimited? I read that using varchars as primary key slows down the database
significantly.

could services like AWS do this for me transparently?

Thank you.

Some suggested reading for you:

http://highscalability.com/  The greatest blog ever in regards to scaling websites.  Tons of fantastic information there.
Read about sharding.  http://en.wikipedia.org/wiki/Shard_(database_architecture)


Low cardinality estimate in hash match operator I am trying to fix a performance problem with one of our reporting queries on SQL Server 2008 R2.
I have included the part of the query that is causing low estimate.  This part is further joined with other tables. Since the estimate for this one is so low, further joins end up being nested loop and causing the query to run forever.
select n.Transactionid
from nath n
WHERE StatusId = 3 and
Date IS NOT NULL and
NOT EXISTS (SELECT 1 FROM nath
WHERE Transactionid= n.Transactionid
AND StatusId = 3
AND HistoryId < n.HistoryId)

Estimated plan

The estimate for the hash match is only 1.17 but in reality there are 550K records coming out. Statistics have been updated with full scan.
I ran the exact same query on one of our SQL Server 2014 instances and it produced better results; the estimate was 557K on the hash match operator. I then tried trace flag 9481 to force the old cardinality estimator on 2014 and the estimates were back to 1. So I think the issue is something to do with old CE estimating self joins.
I tried trace flag 4199 on SQL Server 2008 R2 but that did not help.
Actual execution plan
I didn't want the actual tables names to be visible, so I created similar tables with fewer columns and different table and column names.  The estimates are slightly off than mentioned above but the bigger problem still persists.
SQL Server 2014 with TF 9481
(I don't have a SQL Server 2008 R2 test environment):

SQL Server 2014

Please let me know if there is anyway to fix this wrong estimate.
Repro
The issue can be simulated with the below script:
    create table nat ( c1 int identity(1,1) primary key,c2 int)

    declare @a int=1
    declare  @b int =1
     while @a<10000
     begin
        set @b=1
         while @b<=5
         begin
                insert into nat select  @a
                set @b=@b+1
         end
     set @a=@a+1
     end

select * from nat a where not exists (select 1 from nat b where b.c2=a.c2
 and b.c1<a.c1)
 OPTION(QUERYTRACEON 9481); -- estimated no of rows from hash match 1
select * from nat a where not exists (select 1 from nat b where b.c2=a.c2
 and b.c1<a.c1)  -- estimated no of rows from hash match 49995

I have done some testing with the above query on SQL Server 2012 and I am not able to force the new cardinality estimator behaviour with trace flag 4199.
Current testing results:

SQL 2014 - High estimates on Hash match operator
SQL 2014 with TF 9481 - Low(1) estimate
SQL 2012 - Low(1) estimate
SQL 2012  with TF 4199 - Still low estimates

How is it that I am able to replicate old cardinality behavior on 2014, but not able to get new CE estimates on 2012?
Is it that the change is not part of trace flag 4199 and only came about in 2014?
Changing the NOT EXISTS to a left join seems to produce a better estimate.

The question of why one cardinality estimation model produces closer results than the other in this case is actually not that interesting. The original CE estimates that not finding a matching row has a very small probability; the new CE calculates that it is almost certain. Both are 'correct', just based on different modelling assumptions. Fundamentally, multi-column semi joins are tricky to evaluate based on single-column statistical information.

It is much more interesting to think about what the query is trying to do, and how we can write it in a way that is more compatible with the statistical information available to SQL Server.

A key observation is that the query will return row(s) with one value per group. In the case of the original query, that is row(s) with the minimum HistoryId value for each Transactionid. In the repro, it is row(s) with the minimum c1 value for each different value of c2. The NOT EXISTS query is just one way of expressing that requirement.

SQL Server has good statistical information about distinct values (density) so all we need to do is write the query in a way that makes it clear we want one value per group. There are many ways to do this, for example (using your repro):

SELECT *
FROM dbo.nat AS N
WHERE N.c1 =
(
    SELECT MIN(N2.c1)
    FROM dbo.nat AS N2
    WHERE N2.c2 = N.c2
);

or, equivalently:

SELECT N.*
FROM dbo.nat AS N
JOIN
(
    SELECT
        N.c2,
        MIN(N.c1) AS c1
    FROM dbo.nat AS N
    GROUP BY
        N.c2
) AS J
    ON J.c2 = N.c2
    AND J.c1 = N.c1;

This produces an exactly correct estimate of 9999 rows in 2008 R2, 2012, and 2014 (both CE models):

With a natural index (which would probably be unique as well):

CREATE INDEX i ON dbo.nat (c2, c1);

The plan is even simpler:

You may not always be able to get this very simple plan form, depending on indexes, and other factors. The point I am making is that using basic grouping and joining operations often gets better results from the optimizer (and its cardinality estimation component) than more complex alternatives.

Final notes to clear some misconceptions in the question: the 'new CE' was introduced in 2014. TF 4199 enables plan-affecting optimizer fixes. TF 9481 specifies the original ('legacy') CE, and is only effective on 2014 and later versions.


Firebird query performance with text search, also some numbers I have situation as follows: I'm having a table OFERTY of 10k+ records, and when I select * from it, I get

PLAN (OFERTY NATURAL)
Executing...
Done.
656 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 312 seq.
Delta memory: -95312 bytes.
Total execution time: 0.188s
Script execution finished.

This is all fine, but a problem manifests when I need to present data from many joined tables on a listview, and allow filtering on text fields using starting with clause. I get these numbers:

PLAN JOIN (JOIN (OFERTY NATURAL, KL INDEX (KONTRAHENT_PK), PL INDEX (KONTRAHENT_PK),
PERSONEL INDEX (RDB$PRIMARY28), STATUSY INDEX (RDB$PRIMARY34), KOLORYSTYKA INDEX
(KOLORYSTYKA_PK), MODELE INDEX (MODELE_PK), A INDEX (IDX_SLOWNIKI_NUM_DZ), B INDEX
(IDX_SLOWNIKI_NUM_DZ), C INDEX (IDX_SLOWNIKI_NUM_DZ), RKOL INDEX (REJESTR_PK), RSZY
INDEX (REJESTR_PK)), KLIENCI_PROJEKTANCI INDEX (RDB$PRIMARY53))
Executing...
Done.
67370 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 11199 index, 10669 seq.
Delta memory: -20 bytes.
Total execution time: 2.610s
Script execution finished.

As you can see, query time is skyrocketing. I think it reads ALL the records instead of first-some and I don't know why. There are indexes everywhere there's a need for them (as far as I know). The very same query, but without following text search clause:

/*kl.kt_nazwa is from table KONTRAHENCI which has alias KL and is joined to OFERTY*/
and kl.kt_nazwa starting with 'strefa'

has exactly the same plan, but performs as follows:

Executing...
Done.
10801 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 2444 index, 200 seq.
Delta memory: -1036 bytes.
Total execution time: 0.203s
Script execution finished.

That's a HUGE difference. It doesn't need to read ALL the records from table KONTRAHENCI. So I have two questions here:

Is there a way of text search in firebird that does not mandate reading ALL records from table (in this case: KONTRAHENCI)?
there's also awful lot of fetches. Please explain what they are?

Nothing suspicious in the plan itself, though I do wonder what your actual query looks like. If KONTRAHENCI.kt_nazwa has an index and there also is an index for the field in OFERTY that links to the primary key field of KONTRAHENCI, then a better plan might be something like:
PLAN JOIN (JOIN (KL INDEX (KT_NAZWA_INDEX), OFERTY INDEX (LINK_FIELD_KONTRAHENCI...

Are you by any chance not using INNER JOINs, but LEFT or RIGHT [OUTER] JOINs? That would prevent Firebird from choosing the optimal index.


How do flush innodb table, copy files, then unlock tables from Windows bat file? I can successfully execute "FLUSH TABLES myTable FOR EXPORT;" in MySql Workbench. Then I can copy the cfg and ibd files I need, and then I can go back to workbench and run "UNLOCK TABLES" and all is well. But I would like to do this from a Windows batch file.
However, if I run the following bat file:

mysql -username u -password < prepareTableForCopy.txt

and prepareTableForCopy.txt contains the following:

use mydata;
FLUSH TABLES myTable FOR EXPORT;

I get no errors, but when I look in the data folder the files I need to copy are not there. It seems that as soon as the flush is run and the script returns the files are gone.

How can I fix this so that I can achieve my goal which is to have a .bat file that prepares the table for export, copied the correct files to another directory. zips the files and then unlocks the table?

Your problem is to maintain two sessions

Session #1 : Lock the table
Session #2 : Copy .ibd file

Note the Following

Session #1 must remain alive in order to hold the lock
Session #1 must remain alive long enough for Session #2 to copy the .ibd file

Steps to Perform

Session #1 sleeps for 30 seconds after issuing the lock
Pause 5 seconds to give Session #1 time to lock the table
Session #2 copies the .ibd file
When Session #1  terminates, the lock is implicitly released (unlocked)

This gives Session #2 25 seconds to copy the .ibd file

Here is the Windows BAT file

@echo off
rem
rem Setup Path to MySQL Folders
rem
set BASE_FOLDER="C:\Program Files\MySQL\MySQL Server 5.6"
set DATADIR=%BASE_FOLDER%\data
rem
rem Setup Path to .ibd file and Destination Folder
rem
set COPY_FROM_DB=mydata
set TABLE_TO_COPY=mytable
set IBD_TO_COPY=%DATADIR%\%COPY_FROM_DB%\%TABLE_TO_COPY%.ibd
rem
rem Setup Destination Folder
rem
set TARGET_FOLDER=D:\BackupFolder
rem
rem Setup MySQL Username and Password
rem
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
rem
rem Setup Session to Lock Table and Hold Lock for 30 seconds in the Background
rem Pause 5 Seconds
rem Perform Physical Copy
rem
set TIME_TO_SLEEP=30
set TIME_TO_PAUSE=5
set SQL=FLUSH TABLES %TABLE_TO_COPY% FOR EXPORT ; DO SLEEP(%TIME_TO_SLEEP%)
start mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"%SQL%"
mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"DO SLEEP(%TIME_TO_PAUSE%)"
copy %IBD_TO_COPY% %TARGET_FOLDER%\.

GIVE IT A TRY !!!

Note : You can change TIME_TO_SLEEP and TIME_TO_PAUSE if it takes more  than 30 Seconds to copy


How can I make an Async Slave track all of the nodes in a Percona XtraDB Cluster? I'm seeing a lot of tutorials that claim I have to pick just one of the nodes in my master-master replicated PXC to act as master for an async slave using vanilla mysql replication, and if something goes wrong it's "easy to change the master to one of the other nodes."  That's all well and good, but I don't want to be the one answering the pager at 2 a.m. to do a simple CHANGE MASTER TO because one of my nodes went down.  Ideally the point of an HA cluster is that all but one node can go down and all dependents are none the wiser.

It occurred to me that with GTID replication I might be able to get away with vanilla mysql multi-source replication, simply listing each node in the master-master PXC as a master to the async slave.  They all replicate the same gtid's, which would be idempotent on duplicate updates to the slave, right?  Would that work?

Or: is there a better way?

UPDATE

Based on Rick James answer I feel I must clarify.  The PXC cluster is in a single data center and is a three node HA setup all well and good on its own.  The Async Slave would be an additional 4th node, out of data center and at the end of a high latency VPN/WAN link, not running PXC/Galera at all, but instead just a regular Percona Server instance replicating over vanilla GTID/Binlog Master/Slave replication against the PXC cluster.  The goal is that it can treat each of the 3 nodes in the PXC cluster as a master.  The problem is that the tutorials I read say I have to choose only one to be the master, and thus the single point of failure - not for the cluster, but for the role of "master status" for this 4th node async slave until somebody goes and changes which master it is tracking by hand.  This is why I'm hoping to do multi-source vanilla replication treating each PXC node as its own master to the slave; as long as one stays up, the remote slave stays in sync.

I can not merely switch the slave to run PXC and make it a 4th distributed node in the cluster because it will not be replicating the entire database  but only certain tables; because it is a lower power than the existing cluster nodes; and because I do not want to slow down the cluster (which is used in high-performance production and needs ACID compliance) with the slave (which is allowed to get out of sync a little bit).

You can get rid of the pager, but not if you expect >= 50% failure.

PXC (Galera), spread across 3 geographic locations, is an excellent HA configuration.

Optionally, one of them can be a "garbd" -- light weight, no data -- node only for providing a "quorum".

If any one server goes down, the other two declare that they have a quorum and continue running.

If the network goes down, and 2 servers are split from the 3rd, then the 2 declare quorum and the 1 gracefully fades away.

"Normally" you need to plan for a single point of failure.  That is what the above provides.

If you really need to survive (without a midnight page) two failures, then you need 5 (or more) nodes in the cluster -- and place them in 5 geographic locations.  (Else a tornado, earthquake, etc, could take out multiple nodes.)

I am unclear on what you mean by a "Slave tracking all nodes".  Perhaps you want to set up two 3-node clusters with async replication from one to the other?  That is possible, and it has some degree of failover.

Some people even put the 3 nodes in a single location at one end; the the other 3 at a single location at the other end.  This gives them most of the HA, but without the dreaded latency you may experience between the 3+ nodes in a WAN cluster.

Depending on where your clients are, and which node each client prefers to talk to, a WAN Cluster may be both faster and more reliable than any non-Cluster configuration.  (That is another discussion.)

As for "all but one node can go down" -- that is [probably] a theoretical impossibility, especially when you consider that "split brain" is one of the failure cases.  The Cluster depends on discovering which subset of more than 50% of the nodes are still talking to each other.


Why is this faster and is it safe to use? (WHERE first letter is in the alphabet) Long story short, we're updating small tables of people with values from a very large table of people. In a recent test, this update takes ca 5 minutes to run.

We stumbled upon what seems like the silliest optimization possible, that seemingly works perfectly! The same query now runs in less than 2 minutes and produces the same results, perfectly.

Here is the query. The last line is added as "the optimization". Why the intense decrease in query time? Are we missing something? Could this lead to problems in the future?

UPDATE smallTbl
SET smallTbl.importantValue = largeTbl.importantValue
FROM smallTableOfPeople smallTbl
JOIN largeTableOfPeople largeTbl
    ON largeTbl.birth_date = smallTbl.birthDate
    AND DIFFERENCE(TRIM(smallTbl.last_name),TRIM(largeTbl.last_name)) = 4
    AND DIFFERENCE(TRIM(smallTbl.first_name),TRIM(largeTbl.first_name)) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
AND LEFT(TRIM(largeTbl.last_name), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å')

Technical notes: We are aware that the list of letters to test might need a few more letters. We are also aware of the obvious margin for error when using "DIFFERENCE".

Query plan (regular): https://www.brentozar.com/pastetheplan/?id=rypV84y7V
Query plan (with "optimization"): https://www.brentozar.com/pastetheplan/?id=r1aC2my7E

It depends on the data in your tables, your indexes, .... Hard to say without being able to compare the execution plans / the io + time statistics.

The difference I would expect is the extra filtering happening before the JOIN between the two tables.
In my example, I changed the updates to selects to reuse my tables.

The execution plan with "the optimization"

Execution Plan

You clearly see a filter operation happening, in my test data no records where filtered out and as a result no improvements where made.

The execution plan, without "the optimization"

Execution Plan

The filter is gone, which means that we will have to rely on the join to filter out unneeded records.

Other reason(s)
Another reason / consequence of changing the query could be, that a new execution plan was created when changing the query, which happens to be faster.
An example of this is the engine choosing a different Join operator, but that is just guessing at this point.

EDIT:

Clarifying after getting the two query plans:

The query is reading 550M Rows from the big table, and filtering them out.

Meaning that the predicate is the one doing most of the filtering, not the seek predicate. Resulting in the data being read, but way less being returned.

Making sql server use a different index  (query plan) / adding an index could resolve this.

So why doesn't the optimize query have this same issue?

Because a different query plan is used, with a scan instead of a seek.

Without doing any seeks, but only returning 4M rows to work with.

Next difference

Disregarding the update difference ( nothing is being updated on the optimized query)
a hash match is used on the optimized query:

Instead of a nested loop join on the non-optimized:

A nested loop is best when one table is small and the other one big. Since they are both close to the same size I would argue that the hash match is the better choice in this case.

Overview

The optimized query

The Optimized query's plan has parallellism, uses a hash match join, and needs to do less residual IO filtering. It also uses a bitmap to eliminate key values that cannot produce any join rows . (Also nothing is being updated)

The non-optimized query

The non-Optimized query's plan has no parallellism, uses a nested loop join, and needs to do residual IO filtering on 550M records. (Also the update is happening)

What could you do to improve the non-optimized query?

Changing the index to have first_name & last_name in the key column
list:

CREATE INDEX IX_largeTableOfPeople_birth_date_first_name_last_name
on dbo.largeTableOfPeople(birth_date,first_name,last_name)
include(id)

But due to the use of functions and this table being big this might not be the optimal solution.

Updating statistics, using recompile to try and get the better plan.
Adding OPTION(HASH JOIN, MERGE JOIN) to the query
...

Test data + Queries used

CREATE TABLE #smallTableOfPeople(importantValue int, birthDate datetime2, first_name varchar(50),last_name varchar(50));
CREATE TABLE #largeTableOfPeople(importantValue int, birth_date datetime2, first_name varchar(50),last_name varchar(50));

set nocount on;
DECLARE @i int = 1
WHILE @i <= 1000
BEGIN
insert into #smallTableOfPeople (importantValue,birthDate,first_name,last_name)
VALUES(NULL, dateadd(mi,@i,'2018-01-18 11:05:29.067'),'Frodo','Baggins');

set @i += 1;
END

set nocount on;
DECLARE @j int = 1
WHILE @j <= 20000
BEGIN
insert into #largeTableOfPeople (importantValue,birth_Date,first_name,last_name)
VALUES(@j, dateadd(mi,@j,'2018-01-18 11:05:29.067'),'Frodo','Baggins');

set @j += 1;
END

SET STATISTICS IO, TIME ON;

SELECT  smallTbl.importantValue , largeTbl.importantValue
FROM #smallTableOfPeople smallTbl
JOIN #largeTableOfPeople largeTbl
    ON largeTbl.birth_date = smallTbl.birthDate
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.last_name)),RTRIM(LTRIM(largeTbl.last_name))) = 4
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.first_name)),RTRIM(LTRIM(largeTbl.first_name))) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
AND LEFT(RTRIM(LTRIM(largeTbl.last_name)), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å');

SELECT  smallTbl.importantValue , largeTbl.importantValue
FROM #smallTableOfPeople smallTbl
JOIN #largeTableOfPeople largeTbl
    ON largeTbl.birth_date = smallTbl.birthDate
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.last_name)),RTRIM(LTRIM(largeTbl.last_name))) = 4
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.first_name)),RTRIM(LTRIM(largeTbl.first_name))) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
--AND LEFT(RTRIM(LTRIM(largeTbl.last_name)), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å')

drop table #largeTableOfPeople;
drop table #smallTableOfPeople;


Select data where column value exists in seperate rows for same reference id This is on a MySQL database. Locations have services.

What I want is to get all results where service_id is 2 AND 3, not OR so using IN is out I believe.

Database

----------------------------
| location_id | service_id |
----------------------------
|      1      |      2     |
|      1      |      3     |
|      2      |      2     |
----------------------------

Expected Results

----------------------------
| location_id | service_id |
----------------------------
|      1      |      2     |
|      1      |      3     |
----------------------------

Here's your query:

SELECT
   T.location_id,
   GROUP_CONCAT(T.service_id) AS serviceids
FROM `Table` AS T
GROUP BY T.location_id
HAVING serviceids IN ('2,3');

You could use ORDER BY inside the GROUP_CONCAT():

SELECT
   T.location_id,
   GROUP_CONCAT(T.service_id ORDER BY service_id ASC) AS serviceids
FROM `Table` AS T
GROUP BY T.location_id
HAVING serviceids IN ('2,3');

EDIT, Another way:

SET @LOCATION_ID=0;
SET @LOCATION_ID=(
    SELECT
        T.location_id
    FROM `Table` AS T
    GROUP BY T.location_id
    HAVING GROUP_CONCAT(T.service_id ORDER BY T.service_id ASC) LIKE '%2,3%');
SELECT
    T.location_id,
    T.service_id
FROM `Table` AS T
WHERE T.service_id IN (2,3) AND T.location_id=@LOCATION_ID;

Result:

Hope this help.


Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean? In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth.

For example:

create or replace function something.function_name()
returns setof record as
$$
-- code
$$ language plpgsql;

Looking in this book in the "Part IV: Programming with PostgreSQL" I have found no mention of this type of creation declaration with a function name in two parts.

In the postgresql documentation, at the create function section, the only similar thing is when they deal about argtype or regtype than could be written in the form: table_name.column_name%TYPE. But it's not related to the name of the function.

So, what is this syntax related to ?

It's the name of a schema.
In other words, a schema-qualified function name. Just like you can schema-qualify tables or views or types or even operators (you just don't have to, normally).

Also learn about the schema search_path.


SQL Server 2016 DB Mail Not Sending I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and execute. I entered a rule for the firewall outbound port 587. I tried another mail account and profile with the same unsent issues. The only entries in the logs (db mail logs) are starting and ending of the service. There are no errors anywhere that I can find. The emails appear to simply enter the send queue and never leave it. The accounts can send and receive email on their own and from a SQL Server 2014 instance on another machine.

I've got a queue of items with sent status "unsent" and checked all the normal places with expected results in all of them, aside from a long queue of unsent mail:

SELECT * FROM msdb..sysmail_event_log order by log_id DESC

SELECT * FROM dbo.sysmail_mailitems

SELECT * FROM dbo.sysmail_sentitems

USE msdb
SELECT sent_status, *
FROM sysmail_allitems

 SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

 EXECUTE msdb.dbo.sysmail_help_status_sp

I have tried turning it off and on again... so did I miss a DMV etc. that could shed light on this situation? Is this a known issue with SQL Server 2016 that I haven't in my searches? Any other possible steps to get this mail sent?

On a whim, when double checking permissions, I double clicked on the actual DB mail executable. The result on the SQL Server 2014 machine was an empty command window. On SQL Server 2016 clicking the DB Mail executable presented this message:

I cannot find this requirement anywhere in the SQL Server 2016 documentation, yet it is clearly a requirement. DB Mail is functioning perfectly with no other changes than installing .NET 3.5.


Migrating MySQL from Windows to Linux I'm new to MySQL.  However I am very familiar with Oracle and MSSQL.  This is what I have done so far:

Installed MySQL 5.7 on RHEL 7.2
Created a test db called "mydb" and test table called "employee".
Location of these files are
/usr/var/lib/mydb
/usr/var/lib/mydb/employee.frm
/usr/var/lib/mydb/employee.idb
Able to connect to MySQL from a client machine and can see the "mydb".

So my server and connectivity are fine.  Now I want to move some of the MySQL 5.0 databases from an old Windows box to this new RHEL Linux box.

Sample of old MySQL 5.0 database files:

region.myd
region.myi
region.frm

I don't have much knowledge of the MySQL database architecture.  I just noticed the MySQL 5.0 has extensions of .frm, .myd and myi while the MySQL 5.7 has .frm and the .ibd

My question is: How can I quickly migrate those two "region" files to my new RHEL box so they conform to the .frm and .ibd file format?

Thank you

OK Brace yourself. You can migrate the table from Windows by copying it and converting it.
STEP 01
Copy those three(3) files into the data folder /usr/var/lib/mydb in Linux
You should have

/usr/var/lib/mydb/region.frm
/usr/var/lib/mydb/region.myd
/usr/var/lib/mydb/region.myi

STEP 02
Change the filename name extensions of two of the files
cd /usr/var/lib/mydb
mv region.myd region.MYD
mv region.myi region.MYI

Required for MyISAM tables in Linux

See my old post Error When Restoring Backed Up Tables
See the chat session that I used to troubleshoot this question

STEP 03
Change the owner of the three files
cd /usr/var/lib/mydb
chown mysql:mysql region.frm
chown mysql:mysql region.MYD
chown mysql:mysql region.MYI

STEP 04
Convert the Table to InnoDB
mysql> use mydb
mysql> ALTER TABLE region ENGINE=InnoDB;

That's it.
GIVE IT A TRY !!!


Recursively find the path to all leaves descending from a given parent in a tree I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like:
    0     1
    |     |
    2     3
    |     |
    4     5
   / \    |
 *6*  8  *7*
      |
     *9*

There are many parents, all children have one parent, parents have 0-5 children (but the graph is very "long" – most parents only have one child). There are no cycles.
I'm trying to efficiently identify the path to the furthest descendants of a specific node (and not to any intermediate nodes). E.g. in the above:

get_leaf_paths(1) would return 1 row: {1, 3, 5, 7}
get_leaf_paths(2) would return 2 rows: {2, 4, 6} and {2, 4, 8, 9}

Sample table:
CREATE TABLE graph (
    id bigint PRIMARY KEY,
    parent_id bigint,
    FOREIGN KEY (parent_id) REFERENCES graph(id)
);
INSERT INTO graph (id, parent_id)
    VALUES (0, NULL),
           (1, NULL),
           (2, 0),
           (3, 1),
           (4, 2),
           (5, 3),
           (6, 4),
           (7, 5),
           (8, 4),
           (9, 8);

I'm hoping for a result that looks something like:
SELECT get_leaf_paths.* FROM get_leaf_paths(0);
path
-----
{0, 2, 4, 6}
{0, 2, 4, 8, 9}
(2 rows)

In my initial attempt at a function with a recursive query, I've had trouble selecting only the furthest leaves, especially since some branches are shorter than others (e.g. 6 and 9 above are at different depths). The paths can be very deep (thousands or millions of elements), so I would also like to avoid the excessive memory usage of generating paths for every intermediate node.
Any ideas would be greatly appreciated. Thanks!

WITH RECURSIVE
cte AS ( SELECT id,
                parent_id,
                id::TEXT path,
                NOT EXISTS ( SELECT NULL
                             FROM graph gr
                             WHERE graph.id = gr.parent_id ) is_leaf
                FROM graph
                WHERE id = 2 /* initital node id */
         UNION ALL
         SELECT graph.id,
                graph.parent_id,
                cte.path || ',' || graph.id,
                NOT EXISTS ( SELECT NULL
                             FROM graph gr
                             WHERE graph.id = gr.parent_id )
         FROM cte JOIN graph ON cte.id = graph.parent_id)
SELECT path
FROM cte
WHERE is_leaf

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2e40ff454f302033bf5e8cba8b0b0d85
Multiple initial nodes may be applied too (WHERE id IN (0, 1)).


how to structure availability relationships I am building a recruitment database and need to structure 'current availability' data against candidates which can be one of three options - available now, a specific date (e.g. 5/3/2019) or a relative date (e.g. 4 weeks). When querying the database in my application the relative date will be calculated on the fly.

I am trying to work out the best way to store this data in a relational database. In my project I am using MySQL 5.7. I have tested and can see that storing this as JSON is possible e.g. the availability field in candidates table can be JSON with the following

{
    'immediate': true
}

or

{
    'relative': {
        'period': '4',
        'unit': 'Weeks'
    }
}

or

{
    'date': '2019-03-05'
}

the query can be something like this

select `candidates`.*,
CASE
    WHEN availability->>'$.immediate' = 'true' THEN date(now())
    WHEN availability->>'$.date' THEN date(availability->>'$.date')
    WHEN availability->>'$.relative.unit' = 'week' THEN date(DATE_ADD(now(), INTERVAL availability->>'$.relative.period' WEEK))
    WHEN availability->>'$.relative.unit' = 'month' THEN date(DATE_ADD(now(), INTERVAL availability->>'$.relative.period' MONTH))
END as available from `candidates`
order by `available` desc

Although this works I am adverse to using JSON in a relational database so wondering how the same result could be achieved in other more relational ways.

IMO you overcomplicate things. Just add an available DATE field to the candidate table. Then queries will be simpler and more performant.

SELECT * FROM candidate WHERE available < NOW() ORDER BY available DESC;


Why is installing ORACLE ODBC client a complex task? I'm following the ODBC driver instructions in the link below. I think it would help me deal with issues if I understood why this has to be so complex. Cannot be that ORACLE would not make it easier if they could. Why installing an odbc is not just downloading one or two installers and hit "next, next, next"?

http://ppm.activestate.com/libs/oracle-instant-client/10.1.0.4/win32/ODBC_IC_Readme_Win.html

You've pointed to instructions for installing the Oracle Instant Client.  That is a very slimmed down version of the Oracle Client that is intended to be bundled with third party applications and installed as part of their installer packages.  It's not something that normal people are expected to manually install (other than the third-party application developer that is working on building the installer that includes the Instant Client).

If you download the full Oracle client, you'll get a nice, friendly GUI where you hit Next a bunch of times.  The issue is that it's much bigger (because it contains a lot more options) and it's more difficult to transparently embed in a third-party's installer.


postgresql GROUP BY and latest date filter by non group by statement? currently I have the following table:

CREATE TABLE demo (
  id SERIAL PRIMARY KEY,
  key TEXT NOT NULL,
  other_key TEXT NOT NULL,
  quantity BIGINT NOT NULL,
  date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

Now I want to group by the query like that:

SELECT other_key, SUM(quantity) FROM demo GROUP BY other_key;

This works so far so good, however now I want to filter by the key and also print the latest date for the table, is there a good way to do so?

Pseudo (will fail since key is not in group by)

SELECT other_key, SUM(quantity), MAX(date) FROM demo GROUP BY other_key WHERE key = ?;

my initial idea is a sub query:

SELECT other_key, SUM(quantity), MAX(date) FROM (SELECT * FROM demo WHERE key = ?) GROUP BY other_key;

Is there a better way to do so?
And what would be a good index for the table?

My current index is:

CREATE INDEX demo_all_idx ON demo (key, other_key, quantity);

Bonus (edited):

Is there any way to sort by the MAX(date) then?
Is there a way to create a aggregate function that gets the oldest date where the quantity is great than zero? i.e. some kind of inventory event store where the newest date should not be the latest entry made and instead the newest entry made where the quantity isn't subtracted / zero?
like consider the following table:

id | key     | other_key | quantity | date
---+---------+-----------+----------+-----------------------
 6 | 0A19882 | 01/01     |      100 | 2016-08-30 00:00:00+02
 7 | 0A19882 | 01/02     |      -50 | 2016-09-01 00:00:00+02
 8 | 0A19882 | 01/01     |      100 | 2016-09-02 00:00:00+02
 9 | 0A19882 | 01/02     |      100 | 2016-08-31 00:00:00+02
11 | 0A19882 | 01/03     |      100 | 2016-08-31 00:00:00+02
12 | 0A19882 | 01/03     |     -100 | 2016-09-02 00:00:00+02
13 | 0A19882 | 01/03     |      100 | 2016-09-04 00:00:00+02

The date for 01/01 should be 2016-08-30 00:00:00+02 while for 01/03 it should be 2016-09-04 00:00:00+02 since the event with id 12 has reached zero.

The WHERE clause goes before the GROUP BY:

SELECT
    other_key,
    SUM(quantity) AS sum_quantity,
    MAX(date)     AS max_date
FROM demo
WHERE key = ?
GROUP BY other_key
ORDER BY max_date ;

By the way key is a reserved keyword in SQL - although it isn't in Postgres. It would be best to be avoided as a column or table name.

For the additional question, to also calculate the cumulative sums (order by date) and then find the (oldest) date that these sums went positive and stayed positive, it's easier done with some window functions:

SELECT
    other_key,
    total_sum_quantity, max_date,
    CASE WHEN cumulative_sum > 0 THEN cumulative_sum END AS cumulative_sum,
    CASE WHEN cumulative_sum > 0 THEN date END AS oldest_positive_strike_date
FROM
  ( SELECT
        *,
        ROW_NUMBER()
            OVER (PARTITION BY other_key
                  ORDER BY date DESC)  AS rn
    FROM
      ( SELECT
            other_key, quantity, date,
            SUM(quantity) OVER (PARTITION BY key, other_key) AS total_sum_quantity,
            MAX(date) OVER (PARTITION BY key, other_key)     AS max_date,
            SUM(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS cumulative_sum,
            LAG(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS prev_quantity
        FROM demo
        WHERE key = '0A19882'
      ) AS t
    WHERE (cumulative_sum  > 0 AND cumulative_sum-quantity <= 0)
       OR (cumulative_sum <= 0 AND cumulative_sum-quantity  > 0)
       OR (prev_quantity IS NULL)
  ) AS t2
WHERE rn = 1 ;

Test at rextester.com.

Some notes:

The cumulative_sum returned is the cumulative sum at the point of the oldest_positive_strike_date. If the total cumulative sum is not positive, both these columns will show NULL.
The PARTITION BY key, other_key can be replaced by PARTITION BY other_key. I left as it is, in case you need to run the query not just with one key value but with more, eg. for the whole table or with WHERE key IN (...).
The ORDER BY date will be deterministic if the (key, other_key, date) has a UNIQUE constraint/index. If there is a chance that you have two rows with same key, other_key and date, replace that with something that can identify a row, eg. ORDER BY date, id.
The "obvious" index that will benefit the query would be on (key, other_key, date, quantity). Postgres may choose a different plan though, scanning the table or using the index and also checking the values against the table. this depends on various factors. Experiment with various table sizes and with the work load you expect.
Since the initial WHERE key = ? condition will restrict the rows to about 100 (from a 100K table), it might be more efficient to use a CTE that gets these rows first, using something like the following. You may get away with a simple index on (key) and have good performance:

WITH a AS
  ( SELECT *
    FROM demo
    WHERE key = ?
  )
SELECT ... ;          --- the query as it is, without the `WHERE`


Logon Trigger preventing any connection I was testing a logon trigger to restrict connections to certain IPs. It looks like the trigger was not correct. Now I can't log in to the server even as SA.

Is there a way to fix this?

You can connect to the instance using the Dedicated Administrator Connection (DAC).
It only accepts local connection by default.

Sqlcmd can be used:

sqlcmd -S localhost -U sa -P password -d master -A

  The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A)

  You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<_instance_name_>.

SQL Server Management Studio can also be used:

  You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<_instance_name_>.

However trying to establish a connection from Object Explorer using Connect to Server in SSMS will fail with this message:

  Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design.

From a disconnected Query Editor or connected with a regular user, reconnect using either of:

Query menu / Connection / Change Connection
Popup menu / Connection / Change Connection

Once connected, DISABLE TRIGGER can then be executed:

DISABLE TRIGGER logonTrigger ON ALL SERVER;


How do I rule out SQL Server as the reason for a slow website A website we host has recently been experiencing slow load times. It's been ruled out that the problem lies with the web server and it maybe database related.

What steps or procedures do I take to confirm or rule out that SQL Server is the reason for the slow load times? Also, without a baseline, what may be behaviours/characteristics of SQL Server to look out for that could indicate that there is a problem?

I'd suggest you need a more definitive problem statement - is it all web pages? If it's just some, what are those web pages doing? What is the acceptable load time, and what is the current load time? Is it all users, or just some?

I used to be a web admin, and my first check would have been the webserver - is memory and CPU ok? Do the OS and web logs show anything? Can the scripts output a log to show what's going on? eg I used to put the database stuff in one section, and write times/durations for each database activity to a log, when activated.

If you need to check the SQL Server, check the SQL & NT logs, and again check the memory, disc IO, and CPU - task manager will give you an ideal of two of these (not disc IO).  Ideally, SQL Server should be on a dedicated box rather than shared with anything.

Whilst someone is using the website, look at either Activity Monitor or sp_who2 - are you able to see the query dawdling? Is there any blocking? Keep refreshing... if the query is runnable/suspended/running for a while, then you need to find why it is slow. If you can't see a query in those states, it may not be SQL Server.


Add a column at a specific position in system versioned temporal table I need to add a column to a temporal table. The Microsoft documentation only suggests appending the new column using the following code:
ALTER TABLE dbo.Department
    ADD TempColumn INT;

However, this would cause the column to appear below the audit columns, including the mandatory system versioning columns 'ValidFrom' and 'ValidTo'. Whilst this is not an issue in terms of performance, all of our non-system versioned tables keep the audit columns as the last columns.
I have tried setting system versioning off but can't work out how to make the changes as I would with a normal user table whilst preserving the data in the history table.
SSMS allows you to create the code for modifying tables using the 'Design' option but this is also not available for temporal tables. I would usually use code that is similar to the 'Design' option output for adding a column where a new table is created with the new column and a suffix in the table name, the old table is dropped and the new table is renamed.
How can I do this for temporal tables please?

Once you have fully turned versioning off for that table, the base table and the history table are just two normal tables. So you can:

Turn of history
Add the column into the base table in the place you want (you'll need to use the "new table, copy data, drop old, rename new" approach as you can't add columns in a particular place otherwise)
Repeat this column add for the history table
Recreate the versioning relationship between the two tables

But I would recommend avoiding the extra complication and just adding the new column to the end unless this is causing you a problem beyond aesthetics.


Reference assembly in sqlproj not being deployed to server I have a Visual Studio 2013 Database Project which takes a modified version of fastJSON as a reference. I selected Generate DDL in the reference properties as shown in this screenshot:

And in the .sqlproj msbuild file:

<Reference Include="fastJSON">
  <HintPath>..\..\fastjson\output\net40\fastJSON.dll</HintPath>
  <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
</Reference>

However bin/debug/Project.sql does not contain a line for CREATE ASSEMBLY fastJSON . . .. Adding the assembly manually works and my project will then deploy and run. What do I do to get Visual Studio to deploy my assembly?

I played around a bit to figure out what these settings do... when I commented, I only mentioned what our settings are without understanding them; I'm not the project lead for our migration to Database Projects, so I wasn't familiar with the minutiae of this stuff.

The Model Aware property needs to be set to True.

This property is poorly documented, but from my understanding, enabling it will expose the objects within the assembly to the database model so they can be used in the SQLCLR wrappers (i.e., CREATE ... EXTERNAL ...) and the references can be resolved. If the project doesn't include these objects in the model, there's no way they'll be scripted in the output. Also, if there's a reference to an object in the assembly and this property is set to False, the project will fail to build.

The Generate Sql Script property I mentioned is in a similar state of documentation. This controls whether the publicly visible members in the assembly are automatically scripted into the output. We have it disabled to retain the flexibility of renaming the SQLCLR wrappers and selectively including only the objects we need. Which way you set this is up to your discretion.

Hopefully that helps the public documentation of these properties!


How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup? I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 years, I want to configure mySQL with some of the same features. I want to turn on logging for all transactions, and setup hot backups, hopefully using open source software. I'm also not sure which database engine to use.

I have been looking through the documentation, and I am just not finding the steps for setting up a mySQL instance to work they way I think it should work. I know I can do a logical backup, but that doesn't allow me to do point in time recovery. I know I can use all of the default settings, but if I am able to market my skills as a mySQL DBA, I want to use as many of the advanced features as possible.

Thanks.

Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter.

For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed).

For hot backups I recommend to use percona-xtrabackup. It can stream data directly to a remote server so you don't even have to have double the free space available on the backup host. The documentation is very good and detailed and you can also find many source online.

I hope this helps getting you started. Good luck!


Locking because of triggers mysql I have a memory table and I am updating it somewhere around 3000 updates per second. I setup a another table in Innodb and then created a Trigger so that all data can be send to the innodb table as a backup since memory table can disappear if server reboots.

My problem is I get errors like this Waiting for table level lock, from my show process list. I am unable to update Memory table anymore and all my operations blocked.

As soon as I delete these triggers no more locks and I can continue updating the tables.

Can anyone help with this, do I need to create triggers differently or some setting need to be changed ?

In one situation, I found it possible to ingest data faster into InnoDB than into MEMORY.  Perhaps you should simply get rid of the MEMORY table and the TRIGGER.

I discuss that an many other tidbits in my high-speed ingestion blog.

Do you have multiple inserters?  Do you insert multiple rows in a single INSERT?  Are you pulling the data out of the MEMORY table?  How often, how, when, etc?  Is Replication involved?  Is "normalization" happening?  Summarization?


How to Create relationship with more than one foreign key I am using mysql and I have trouble in creating relationships of these table:

CREATE TABLE `db_menu` (
  `***menu_id***` int(255) NOT NULL,
  `menu_name` varchar(255) NOT NULL,
  `menu_code` varchar(255) NOT NULL,
  `menu_price` varchar(255) NOT NULL,
  `menu_image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_reserve` (
  `***user_id***` int(255) NOT NULL,
  `***menu_id***` int(255) NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `total` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_sale` (
  `***user_id***` int(255) NOT NULL,
  `***menu_id***` int(255) NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `total` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_status` (
  `***menu_id***` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_user` (
  `***user_id***` int(255) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_pass` varchar(255) NOT NULL,
  `user_type` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

my problem is to create relationship of these tables

  db_menu(menu_id) = db_status(menu_id)   db_user(user_id) and

  db_menu(menu_id) = db_reserve(user_id)(menu_id) db_user(user_id) and

  db_menu(menu_id) = db_sale(user_id)(menu_id)

thank you!!!

I guess you want do something like this:

CREATE TABLE product (category INT NOT NULL,
                      id INT NOT NULL, price DECIMAL,
                      PRIMARY KEY(category, id)
                     ) ENGINE=INNODB;

CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)
                      ) ENGINE=INNODB;

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            INDEX (customer_id),
                            FOREIGN KEY (product_category, product_id)
                                REFERENCES product(category, id)
                                ON UPDATE CASCADE ON DELETE RESTRICT,
                            FOREIGN KEY (customer_id)
                                REFERENCES customer(id)
                          ) ENGINE=INNODB;

See more here


How to read the actual value of @P0 in SQL server profiler When I use SQL server profiler to trace the SQL statement flow using SP:StmtCompleted,
I can only read the variables as @P0, @P1, etc. like:
insert into CaseFollowup (createDate, duration) values (@P0, @P1)

Can I read the actual value of @P0, @P1?
Thanks for your help!

Use rpc_completed to get parameter values. Also, use extended events instead of profiler if you're on SQL 2012 or newer.


Patching SQL Server 2014 Versus SQL Server 2014 Express Need to apply a Service Pack to the following SQL Express install:

Installed version : 12.0.2000.8 (2014) Express Edition

Not sure if I can apply the latest SQL 2014 Service Pack SP4 or stick to the latest Express Service Pack I have found SP3.

Are service packs for SQL 2014 different than the service packs for SQL 2014 Express?

Are service packs for SQL 2014 different than the service packs for
  SQL 2014 Express?

There is no difference in service packs or cumulative updates between SQL Server Express edition and other editions such as developer, standard or enterprise.

When looking at the latest build of sql server 2014, SP3 CU4 is the last one with build number: 12.0.6329.1 (Latest build). I'm not sure where SP4 is coming from?

When looking at the applies to field from SP3 CU4:

  Applies to: SQL Server 2014 Developer SQL Server 2014 Enterprise SQL
  Server 2014 Enterprise Core SQL Server 2014 Standard SQL Server 2014
  Business Intelligence SQL Server 2014 Express SQL Server 2014 Web

You will find SQL Server 2014 Express

Download SP3 CU4 here

Remember that you would have to apply the service pack first, and then add the CU.
A list of sql server (2014) build versions can be found here, here and an overview of all the recent updates here .

EDIT

  ...I get the impression that I need to update to SP3
  1st THEN apply the CU4 patch. Since I'm starting with the base version
  of SQL Express (SP1 or SP2 not applied), can I just skip right to
  installing SP3 and then CU4 or do I first need to apply SP1 and SP2?

Since Service packs are cumulative you can directly install SP3 while skipping SP1 and SP2 and then install CU4.

  Also If I were to download SP3, I have a number of choices of files to
  download. Not sure which to pick. See options from this link:
  microsoft.com/en-us/download/details.aspx?id=57473 Appreciate any
  additional guidance you can provide...

The link provided is to download sql server express / local db / express with tools / express advanced edition with the SP3 installed when you don't have express installed.

You should download the regular SP3 patch, without express.

If you follow this link, you will see these two options:

Click on the one without express, SQL Server 2014 SP3

The ones with express are complete instances of sql server with the patch added.

Just to show you that I am not making this up, when scrolling down on the patch notes of SP3, go to the part: "Select the correct file to download and install"

Only install this one if you have no 64-bit version of SQL Server 2014 Express.

And for the correct file: (I think SP2 is a mistake, supposed to be SP3)

A patch for any Edition, express included. The Service pack you need, SP3 is this one .

Afterwards, download and install CU4  for SP3 from here


Double range query on one column uses wrong seek predicate This problem comes up in the real world, but is already present in the following trivial example. I am using SQL Server 2017.
Given a table with only one non-id column, which takes only one value:
create table #test (id bigint not null, status smallint not null)

/* Populate with 10000 times value 10 */
;WITH numbers(Number) AS
(SELECT 1 AS Number
 UNION ALL
 SELECT Number+1 FROM numbers where Number<10000
)

insert into #test (id,status)
select number,10 from numbers option(maxrecursion 10000)

/* Create fresh index */
create index index_status on #test ([status])
DBCC SHOW_STATISTICS ("tempdb..#test", 'index_status') WITH HISTOGRAM

Now we query a double range on this column (0 results, since the only value is outside both ranges):
select 1
from #test
where status < 10
and status <> 2

The chosen execution plan uses an Index Seek, with

Seek Predicate "status < 2 AND status > 2"
Predicate "status < 10"

and consequently needs to read all 10000 rows. This surprises me, since I can read off the index statistics to conclude that a Seek Predicate on "status < 10" would be more efficient, since this already filters out all rows.
Question: Why are Seek Predicate and Predicate chosen 'the wrong way'?
I am not interested in alternatives or workarounds, I am only interested in understanding this choice of execution plan. For example we can rewrite the query using a UNION ALL on both range queries seperately, forcing two efficient Seek Predicates:
select 1
from #test
where status < 2

union all

select 1
from #test
where status > 2 and status < 10

Running
dbcc traceon (3604,8606)

And then the query with a #temp table the final tree before cost based optimisation looks as follows (with the NE/ <> predicate first and the LT/ < predicate second) - the deviation of the predicate order from the "Input tree" happens from the "Simplified Tree" stage and stays that way in subsequent trees.
*** Tree After Project Normalization ***
        LogOp_Project

            LogOp_Select

                LogOp_Get TBL: #test #test TableID=-1583795211 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                ScaOp_Logical x_lopAnd

                    ScaOp_Comp x_cmpNe

                        ScaOp_Identifier QCOL: [#test].status

                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=2)

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [#test].status

                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=10)

            AncOp_PrjList

                AncOp_PrjEl COL: Expr1003

                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)

With a permanent table the tree looks as follows.
*** Tree After Project Normalization ***
        LogOp_Project

            LogOp_Select

                LogOp_Get TBL: test test TableID=645577338 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                ScaOp_Logical x_lopAnd

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[test].status

                        ScaOp_Identifier COL: ConstExpr1004

                    ScaOp_Comp x_cmpNe

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[test].status

                        ScaOp_Identifier COL: ConstExpr1005

            AncOp_PrjList

                AncOp_PrjEl COL: Expr1003

                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)

The second case is quite fragile. Adding a query hint - such as OPTION (RECOMPILE) it reverts to the first case. It seems to retain the initial order of the predicates, as written in the query, in the cases that simple parametrisation is attempted (this is not attempted for the temp table #test).

select 1 from test where  status <> 2 and status < 10 gives the "bad" plan again with the seek on <> 2 and residual on < 10

My supposition is that the index matching code just picks the first seekable predicate available in input tree order in the rare case that there are multiple competing seek predicates for the same column.
Specifically the SelPredNorm transformation rule appears to cause the predicate re-ordering. The following gives the desired plan.
select 1
from #test
where status < 10
and status <> 2
option (queryruleoff SelPredNorm);

As for why this does not kick in (in this case) with auto parameterised queries that question is still not addressed.


Parking Lot table for employees I am having dificulty designing a table that stores the information about a parking lot with 100 spaces that are arranged by 5 columns x 20 rows for employees, guests, handicap, executives, contractors and using an outer join sql statement.

I am using an Oracle  DBMS. I have a project to do for a Database Fundamentals course in college that requires the creation of several tables (employees, retirementplan, healthplan, healthcost, healthtype, ParkingSpace).

The requirement is that the HR personnel will assign the parking space. So I am assuming that it needs a create view table or some kind of scenario where there is a prompt for HR to assign the space to that person.  I am also of the mindset that each space should be named so it can be referenced.  How is this accomplished.  Can someone please guide me or provide the basics, please.  I am a newbie so please take it easy on me.  Thanks a million.

I drew an ER diagram for the parkinplace model.

(The dia file can be found here)

A parkingplace can be assigned to at most  one employee. There may be parkingplaces that are not assigned to employes (e.g. parkingplaces for guests).
One can store the column/row coordinate of each parking place. This may be expected for this homework problem. One may not do this in practice because these coordinates are not realy useful
when on administrates the parking places. If there are parking places added that are not on this area then column and rows may not make sense. But if one has no column/row attribute a string attribute for specifying the location may be useful. This attribbute could hold the column/row information as string.
So basically the parking place table containts 100 entries, one for each parking place.

create table employee(employee_id number  primary key,
                      first_name varchar2(30),
                      last_name varchar2(30))
/
create table reservation(name varchar2(30) primary key)
/
create table parkingplace(parkingplace_id number
                            primary key,
                          reservation_name varchar2(30)
                            references reservation(name),
                          employee_id number
                            references employee(employee_id),
                          grid_column number not null,
                          grid_row number not null,
                          unique(grid_column,grid_row),
                         check(grid_column between 1 and 5),
                         check(grid_row between 1 and 20),
                         check(employee_id is null
                               or reservation_name !='guest') )
/
create index idx_reservation_name on parkingplace(reservation_name)
/
create index idx_employee_id on parkingplace(employee_id)
/

The following script fills the tables with testdata

insert into reservation(name) values('employees')
/
insert into reservation(name) values('guests')
/
insert into reservation(name) values('handicap')
/
insert into reservation(name) values('executives')
/
insert into reservation(name) values('contractors')
/
declare
n number;
begin
n:=0;
for r in 1..20 loop
  for c in 1..5 loop
    n:=n+1;
    insert into parkingplace(
      parkingplace_id,
      reservation_name,
      grid_column,
      grid_row) values(n,'employees',c,r);
    end loop;
  end loop;
end;
/

update parkingplace
 set reservation_name='handicap'
  where grid_column=1 or grid_column=5
/

update parkingplace
 set reservation_name='guests'
  where grid_row=1
/

insert into employee(employee_id, first_name, last_name)
            values(1,'Clark','Kent')
/
update parkingplace
  set employee_id=1
  where parkingplace_id=6
/
insert into employee(employee_id, first_name, last_name)
            values(2,'Loise','Lane')
/
update parkingplace
  set employee_id=2
  where parkingplace_id=7
/

insert into employee(employee_id, first_name, last_name)
            values(3,'Lana','Lang')
/
update parkingplace
  set employee_id=3
  where parkingplace_id=8
/

insert into employee(employee_id, first_name, last_name)
            values(4,'Bruce','Wayne')
/
update parkingplace
  set employee_id=4
  where parkingplace_id=9
/

If you want to display all 100 parkin places with the employee names if there is an employee assigned then you have to use a LEFT OUTER JOIN.

select
grid_row,
grid_column,
reservation_name,
first_name,last_name
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id);

Because we have stored the geometrical information (row and column number) we can output the row * column matrix with the appropriate query

select grid_row,c1,c2,c3,c4,c5
from (
  select
grid_row,
grid_column,
first_name,
last_name
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id)
 )
pivot (max(first_name||' '||last_name) for grid_column in (
  1 as c1,
  2 as c2,
  3 as c3,
  4 as c4,
  5 as c5))
order by grid_row;

A demonstration can be found in sqlfiddle

This PIVOT extension of the select statement is implemented in Oracle since version 11. Prior version of Oracle have to use technics similar to that shown by @kkarns in a comment.

select
 grid_row,
max(decode(grid_column,1, first_name||' '||last_name,null)) c1,
max(decode(grid_column,2, first_name||' '||last_name,null)) c2,
max(decode(grid_column,3, first_name||' '||last_name,null)) c3,
max(decode(grid_column,4, first_name||' '||last_name,null)) c4,
max(decode(grid_column,5, first_name||' '||last_name,null)) c5
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id)
group by grid_row
order by grid_row
;


Get base table from materialized view in postgresql I use materialized view in postgresql. when retrieving the list of materialized view (select * from pg_matviews), in the "definition" column, there is a query building the materialized view. how do i retrieve the list of base tables from "definition"?
example query materialized view :
CREATE MATERIALIZED VIEW rental_by_category
AS
SELECT c.name AS category,
    sum(p.amount) AS total_sales
   FROM (((((payment p
     JOIN rental r ON ((p.rental_id = r.rental_id)))
     JOIN inventory i ON ((r.inventory_id = i.inventory_id)))
     JOIN film f ON ((i.film_id = f.film_id)))
     JOIN film_category fc ON ((f.film_id = fc.film_id)))
     JOIN category c ON ((fc.category_id = c.category_id)))
  GROUP BY c.name
  ORDER BY sum(p.amount) DESC
WITH NO DATA;

i want to get list of materialized view like this:

rental_by_category
base table
payment
rental
inventory
film
film_category
category

I think something like this should do it:

SELECT DISTINCT
  pg_class.oid::regclass
FROM pg_rewrite
JOIN pg_depend ON
  pg_depend.classid = 'pg_rewrite'::regclass AND
  pg_depend.objid = pg_rewrite.oid AND
  pg_depend.refclassid = 'pg_class'::regclass AND
  pg_depend.refobjid <> pg_rewrite.ev_class
JOIN pg_class ON
  pg_class.oid = pg_depend.refobjid AND
  pg_class.relkind IN ('r','f','p','v','m')
WHERE
  pg_rewrite.ev_class = 'rental_by_category'::regclass

This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a RECURSIVE CTE).

pg_class.relkind IN ('r','f','p','v','m') covers all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docs.

Many different kinds of dependencies are stored in pg_depend, and not all of them are relevant to your question. For example, the view's pg_rewrite entry has a dependency on its own pg_class entry; the pg_depend.refobjid <> pg_rewrite.ev_class constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.


Row size too large (> 8126) I'm facing the following problem.

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In

  current row format, BLOB prefix of 768 bytes is stored inline.

It appears just when I'm uploading a image to the database (it has <1Mb). Precisely, just 100kb.

I've tried a lot of things: to change the properties "max_allowed_packet", "innodb_log_file_size" (namely, to increase the size to 512M) and nothing...

I dont know the cause of the trouble.

To illustrate, the table

TABLE(
    `passeio` int(4) unsigned NOT NULL COMMENT 'identitificador do passeio',
      `data_inclusao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `nome_passeio` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `inicio` date NOT NULL,
      `fim` date NOT NULL,
      `por_que_ir` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `pdf_roteiro` mediumblob NOT NULL,
      `incluso` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `nao_incluso` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `valor_descricao` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `valor_vista` decimal(10,0) NOT NULL,
      `valor_total_parcelado` decimal(10,0) NOT NULL,
      `numero_parcelas` int(2) unsigned NOT NULL,
      `forma_pagamento` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `avisos_importantes` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `foto_principal` blob NOT NULL,
      `foto_2` blob NOT NULL,
      `foto_3` blob NOT NULL,
      `foto_4` blob NOT NULL,
      `foto_5` blob NOT NULL,
      `foto_6` blob NOT NULL,
      `foto_7` blob NOT NULL,
      `foto_8` blob NOT NULL,
      `foto_9` blob NOT NULL,
      `foto_10` blob NOT NULL,
      `foto_11` blob NOT NULL,
      `foto_12` blob NOT NULL,
      `foto_13` blob NOT NULL,
      `foto_14` blob NOT NULL,
      `foto_15` blob NOT NULL,
      `foto_16` blob NOT NULL,
      `foto_17` blob NOT NULL,
      `foto_18` blob NOT NULL,
      `foto_19` blob NOT NULL,
      `foto_20` mediumblob NOT NULL,
      `valor_entrada` decimal(10,0) NOT NULL,
      `data_partida` date NOT NULL,
      `local_partida_1` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_1` time NOT NULL,
      `local_partida_2` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_2` time NOT NULL,
      `local_partida_3` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_3` time NOT NULL,
      `local_partida_4` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_4` time NOT NULL,
      `local_partida_5` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_5` time NOT NULL,
      `local_partida_6` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_6` time NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Bill Karwin has addressed this before in his answer to Row size error with MySQL
I also addressed this in the past : MySQL: Row size too large (> 8126)
Based on his post, and the fact that you still have several TEXT and VARCHAR fields, you should set the following values higher in my.cnf:
[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M

Then, restart mysqld.
UPDATE 2015-01-26 23:15 EST
Your comment

In act of despair, I've erased yesterday all the blob columns. I've leave just one. The problem disappeared . But I'll create them again an will try your approach. As soon as possible I return with the result. Thanks for the indication

You should not put 20 BLOB in one table. You should create table to hold the BLOBs
CREATE TABLE mi_fotos
(
    id INT NOT NULL AUTO_INCREMENT,
    passeio INT NOT NULL,
    foto BLOB,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Store your photos in this table and have passeio in this table link back to your original table.