DB collation is used for comparison instead of column collation I am moving data from one SQL Server to another. lets call them source and destination SQL server.

The source SQL Server has collation SQL_Latin1_General_CP1_CI_AS and the destination has collation Hebrew_CI_AS.

The destination's SQL Server's tables and their columns have collations SQL_Latin1_General_CP1_CI_AS - which is good.

I asked source SQL Server to generate script for moving some tables. When I run that script on the destination SQL Server I get an error:

  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AS" and "Hebrew_CI_AS" in the equal to
  operation.

Why is it that the collation Hebrew_CI_AS is used (SQL Server Collation) and not the collation of the column?

Thanks

If you do not specify a collation when creating a table (regular or temporary or variable), then the database's default collation is used. When creating a temporary table (not table variable), it exists in tempdb, so tempdb's default collation is used. TempDB's default collation is derived from the server default since the server default was used to create the model database which is in turn used to create all new databases, including tempdb.

Fortunately, you can use the COLLATE clause to specify which collation to use for the column(s), regardless of any database's default collation. And if you are creating temporary tables in databases that have different collations and you don't always know ahead of time what the collation will need to be, then you can specify DATABASE_DEFAULT which will use the current database's default collation instead of tempdb's default collation.

You can see this behavior by running the following tests. The setup creates a database with a collation that I assume is not your server's default (so that it can be obviously different from tempdb).

Test Setup

USE [master];
IF (DB_ID(N'CollateEBCDIC') IS NULL)
BEGIN
  CREATE DATABASE [CollateEBCDIC]
    COLLATE SQL_EBCDIC277_CP1_CS_AS;
END;
GO

Test Queries

1) Creating a table without specifying the COLLATE clause will use that database's default collation:

USE [CollateEBCDIC];

CREATE TABLE dbo.TestCollate (Col1 NVARCHAR(50));

SELECT name, collation_name
FROM   sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'dbo.TestCollate');

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

2) Creating a temporary table without specifying the COLLATE clause will use tempdb's default collation:

-- DROP TABLE #TestCollate1;
CREATE TABLE #TestCollate1 (Col1 NVARCHAR(50));

SELECT name, collation_name
FROM   tempdb.sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate1');

Returns:

name    collation_name
Col1    SQL_Latin1_General_CP1_CI_AS

3) Creating a temporary table with specifying the COLLATE clause will use the specified collation. If you don't always know what that collation will be, you can specify DATABASE_DEFAULT which will use that database's default collation:

-- DROP TABLE #TestCollate2;
CREATE TABLE #TestCollate2 (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT);

SELECT name, collation_name
FROM   tempdb.sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate2');

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

4) Declaring a table variable without specifying the COLLATE clause will use the current database's default collation:

SELECT name, collation_name
FROM   sys.dm_exec_describe_first_result_set(N'
DECLARE @TestCollate3 TABLE (Col1 NVARCHAR(50));
INSERT INTO @TestCollate3 (Col1) VALUES (NCHAR(42));
SELECT * FROM @TestCollate3;
', NULL, NULL);

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

Test Cleanup

DROP DATABASE [CollateEBCDIC];

Contained Database Wackiness

When working within a Contained Database, there are two differences from the behavior noted above:

Temporary Tables use the Database's default Collation (i.e. DATABASE_DEFAULT) for string columns that are not declared using the COLLATE keyword. Test #2 shows Temporary Tables using the Instance's default Collation.
Database-level meta-data (e.g. names of objects, Users, Indexes, columns, etc) always has a Collation of Latin1_General_100_CI_AS_KS_WS_SC (i.e. CATALOG_DEFAULT) regardless of the Database's default Collation.


Exclude databases from automatic full backup I have 10 databases on my SQL Server. We do an Automatic Full Backup every day at 00.00.

The issue is there are two databases out of the 10 that should not be backed up as these databases are currently not required by the management; but I have no idea how to remove these databases from the Automatic Full Backup.

I am not able to find any script or anything related to the backup. The only thing I was able to find is the Full Backup option on the SQL Agent. Also these backups are done by SQL Server. Please help me out in resolving this issue.

When I went to SQL Server Agent> Jobs> Full Backup I found the following:

I am not able to make it out what exactly is this. Are these backups or something else? The files generated by these jobs are consuming my drive space and these jobs are running on daily basis.

Its easy using T-SQL.

I would suggest not to use Maiantenance plan. Instead go with a much flexible and widely adapted TSQL based solution - SQL Server Backup by Ola Hallengren

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES, -DB1,DB2', -- All user databases, except Db1, DB2
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24


CouchDB n & q values I'm new to CouchDB. I'm going through its config. Current configuration is as below.

n = 3

q = 8

I understand n = 3 means 3 nodes and q = 8 means 8 shards. I don't have a cluster in place. Is it possible to make n = 1 without affecting data consistency? (This is a production DB.)

Will it help me reduce overall DB size?

From the official documentation, the parameter n is "The number of copies there is of every document. Replicas." so if you set it to 1 you will use less space for sure. Now if this is a production environment that may not be a good idea unless you have your database on a RAID with redundancy.


User called mysql in fresh mariadb install on debian I just installed MariaDB 10.4 on Debian 10.
In addition of root, there is a user called mysql.

Can I remove this user ?
What is the purpose of this account ?

Thanks.

MariaDB is a fork of MySQL, and as such it uses the latter name for many important things, including its command line tool mysql, daemon binarymysqld, and the user IDmysql` under whose authority the daemon runs. As mentioned in the comments, most Linux daemons run as their own dedicated user IDs for improved security.

Do not delete the user mysql.


Distribution Agent is failing in Transaction Replication I am replicating large tables using transaction replication.

The problem I am facing while applying snapshot at subscriber.
Because of large table, active distribution transaction fill-up transaction log which cause to fail Distribution agent.

Action I have taken:

-CommitBatchSize -- 10000
-SubscriptionStreams -- 10000
-BCPBatchSize -- 10000

Yet, I am facing same issue.

Thanks!

Know Your Limitations

The Transaction Log has data files on (hopefully by itself) one or more drives. How large is this?
How much of the Log data file is already full from uncommitted entries?
For every transaction, it must be logged. So large batches takes a lot of space.

Replication uses BCP files that use MSReplicated objects that insert, update, and delete rows one at a time. You can see these procedures in the target subscriber database under the names

[dbo].[sp_MSdel_<article>]
[dbo].[sp_MSins_<article>]
[dbo].[sp_MSupd_<article>]

So if you wish to see what is happening on the session level, you can see this during your testing.

  Fun Fact: This is also why Replication cannot have IDENTITY constraints allowed
  on the target table (you will see the NOT FOR REPLICATION ) because
  the batches are not necessarily in that the table has settled in.

Batches Represent a set of transactions that are committed at the same time.

Set the CommitBatchThreshold flag to a much lower number. Such as 100 and go lower if need be.

Your "fix" actually made some of this worse. From the BOL:

  -CommitBatchSize Is the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default is 100.

  -CommitBatchThreshold Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The
  default is 1000.

  replication distribution agent | Microsoft Docs

A Batch is a connected set of transactions, usually between a GO statement.

  A batch of SQL statements is a group of two or more SQL statements or
  a single SQL statement that has the same effect as a group of two or
  more SQL statements.

  Batches of SQL Statements | Microsoft Docs

Advanced Techniques: You can guesstimate the amount of space a typical row soaks up using the DATALENGTH() function, if you want to understand more about your LOG.

There is much more to replication, but I am sure this is enough to get you where you want to be. :)


Automatically and robustly restarting a Galera cluster Galera clusters, somewhat famously, have a requirement that nodes need to be shut down and brought back up again in a certain order.
There are a few questions here and on other sites about recovering from unclean shutdowns, which may cause split brain situations in the cluster. For manual restarts, I usually follow a procedure that relies on checking files and potentially even database state and is, frankly, too fragile and too much manual work for my taste.
I think it should be possible to encode this manual procedure right into the systemd unit files of a cluster, so that when the failure condition is resolved and machines boot back up, the cluster members also come back up in the correct sequence automatically. This will definitely involve listing all cluster member nodes in some central location, but should be re-usable apart from that.
So, is there a commonly used, best-practice way to re-start a Galera cluster after any sort of shutdown, automatically during system startup?

I would say the best practice would be to ensure that one node is always still running, when the others have stopped normally. (systemctl stop mysql/mariadb)
Then restart one of the previously stopped nodes, and when that restarted node reports in logfiles that it connected to the running old node, and that it's ready to serve connections, you may restart the old one still running.
If all nodes have been stopped, you need to figure out manually which is the newest node (by using (ubuntu):galera_recovery, note the highest change no.)
Maybe even have edit the safe_to_bootstrap line in the grastate.dat file.
That node then needs to restart the cluster (create the new unique ID for the others to connect to) (by using (ubuntu): galera_new_cluster).
The remaining nodes simply start db with 'systemctl start mariadb'.
Sounds as if there's a reason they didnt yet built that in to systemd ;) ?


Tips for catching changes in table data over time interval for export to Data Warehouse I've recently been tasked with exporting data from one of our databases to a central Data Warehouse. The export is supposed to be incremental since a full load every 24 hours would be a massive waste of resources, bandwidth and time.

And therein lies my question, what is the best way to catch both new rows as well as changed rows in the database? I've seen recommendations on everything from Timestamps to creating a DateTime column and update it manually on each insert/update, and now I'm at a complete loss.

If I were to go with the DateAndTime column, I figure that there MUST be a way to create for instance a trigger or similar to catch all inserts and updates, or am I wrong?

I know that this question isn't quite as down to the nitty gritty as many other questions, but I'm really stuck and will appreciate all help and/or hints of where to look. Thanks in advance. :)

The simplest way to handle this may be a bit field like IsDirty that is updated with a trigger, and set to 0 for the whole table when you run your ETL.

If this is set to 1 for every row that is updated, and you give it a default value of 1, then you can make export views for your ETL that filter on IsDirty = 1 so you only interact with new rows.

If you need to know when data was changed and/or have multiple ETL processes hitting the same data, then you will need to have a datetime field and store export dates and times in your config table.


Understanding partition elimination I am trying to understand the below query being run from our developers on read only database which just runs forever.

Table here is partitioned table on partition column trickey per day.

Query is something like below

Select
   a,
   b,
   c,
   d,
   trickey
from
   table
where
   (1=1)
   and (1=1) and convert(varchar(10),trickey,112) BETWEEN 20190913 and 20190913

Will above query not eligible for partition key elimination OR how can i improve or tune up the above query?

Also what is the benefit of (1=1 )used in where clause and does that help or degrade performance?

@Justin- Modified query

Select
       a,
       b,
       c,
       d,
       trickey
    from
       table
    where
       (1=1)
       and (1=1) ---and convert(varchar(10),trickey,112) BETWEEN 20190913 and 20190913 replace this one with modified version

and trickey>=cast('2019-09-13' as date)
and trickey< cast('2019-09-14' as date)

You should be able to get the estimated query plan in SSMS without running the query.

If you do, you'll see that SQL Server is not going to be able to use partition elimination because you have a function on the partitioning column.  Just like using a function on an indexed column prevents SQL Server from using the index, using a function on a partitioning column prevents SQL Server from doing partition elimination.  SQL Server has no idea how to prove which partition function( column ) data might be in so it has to scan all of them.

Assuming trickey is a datetime column, you likely just want to compare it against dates directly rather than using a function

and trickey >= cast( '2019-09-13' as date )
and trickey <  cast( '2019-09-14' as date )


ON DUPLICATE KEY UPDATE in INSERT INTO SELECT with GROUP BY I have the query of
INSERT IGNORE INTO t2 (ID,P1,P2,Items)
    SELECT ID,P1,P2,SUM(Items)
    FROM t1 GROUP BY ID,P1,P2;

Since I need to run this query frequently to inspect changes in t1, I need to add
ON DUPLICATE KEY UPDATE t2.Items=SUM(t1.Items)

but it gives ERROR 1111 (HY000): Invalid use of group function error.

Like all functions you need to make a further select with the data
And please next time provide a minimal reproducible example, so that the answer can be doe quicker and with much less effort

CREATE TABLe t2 (ID INT primary key,P1 INT,P2 INT ,Items INT )

INSERT INTO t2 VALUES (1,1,1,1)

CREATE TABLe t1 (ID INT primary key,P1 INT,P2 INT ,Items INT )

INSERT INTO t1 VALUES (1,1,2,2)

INSERT IGNORE INTO t2 (ID,P1,P2,Items)
    SELECT ID,P1,P2, t1sum
    FROM
    (SELECT ID,P1,P2,SUM(Items) t1sum
    FROM t1 GROUP BY ID,P1,P2) t3
ON DUPLICATE KEY UPDATE t2.Items=t3.t1sum

✓

SELECT * FROM t2

ID | P1 | P2 | Items
-: | -: | -: | ----:
 1 |  1 |  1 |     2

db<>fiddle here


Query plan showing a missing index even though the index is present in the table In one of my production SQL server instance, I am facing an issue. A query is taking long time to run. While checking SQL query plan, I found that it was giving suggestion for creating a missing index. I went to the recommended table and found that the suggested index was already present in the table but was some not getting used.  Maintenance plans (of rebuilding indexes, updating statistics etc) are done on a regular basis.I am not sure why the index is not getting used ? and why query plan keeps on giving suggestion for missing index when it already exists? Any help would be really appreciated.

Based on the information in your question, I think you've hit a bug that is referenced in this blog (Missing index DMVs bug that could cost your sanity…) and addressed in this Connect item

Per the blog post:
The bug is this: the missing index code may recommend a nonclustered index that already exists. Over and over again. It might also recommend an index that won't actually help a query.


Issue Moving data from 11g to 18c I have an rman database backup from Oracle 11g XE which i am trying to import into oracle 18c XE database using below commands

rman target /
shutdown abort;
startup nomount;
SET DBID DBID_FROM_ORACLE11G
restore controlfile from 'PATH_TO_CONTROL_FILE';
alter database mount;

After "alter database mount" I am getting below error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/29/2019 11:12:45
ORA-65101: container database set up incorrectly

Is there something I am missing or is there anything wrong with my procedure

Yes.

If you want to the save a lot of time and trouble, forget RMAN for this specific task. Oracle XE 11g is limited to 11 GB data. Data Pump can easily handle that.

Data Pump Export

Data Pump Import


Case Statement for on same month i have a CASE statement, which classify my Customer-Revenue and set Flag, when Revenue <> 0 is. But my problem i should search also same month record on my case statement. Logic : if two records (Revenue & Credit) exist on same month and sum of those 2 records are zero then put flag.

my Script :

    SELECT CUSTOMER,
CASE WHEN SUM(FLAG) > 0 THEN 'X' ELSE 'Y' END FLAG
FROM (
    SELECT CUSTOMER, CALMONTH, SUM(INVCD_VAL),
    CASE WHEN INVCD_VAL <> 0 THEN 1 ELSE 0 END AS FLAG
     FROM
        (
        SELECT CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6), SOLD_TO, REVENUE FROM CUST_HISTORY_MIS
        )
    GROUP BY CUSTOMER, CALMONTH, INVCD_VAL
    )
GROUP BY CUSTOMER
ORDER BY CUSTOMER

My Source and Target tables look like :

Thanks alot for your answer.

Posting the create table statements and insert statements instead of pictures could really help  the next time you ask a question. Less trouble for both parties as you will get a faster answer.

Having said that, below is a way to do it but there are a few considerations / uncertanties due:

the datatypes not being known
'?' returning 'Y' in your example resultset
...

Calculating it for each customer for each month:

;WITH CTE AS
(
        SELECT CALMONTH as CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6) as CALMONTH, SOLD_TO, REVENUE as INVCD_VAL FROM CUST_HISTORY_MIS
)

SELECT DISTINCT CALMONTH,
                CUSTOMER,
                CASE WHEN SUM(INVCD_VAL) OVER(PARTITION BY CALMONTH,CUSTOMER) = 0 then 'Y' else 'X' END AS HISTORY_FLAG
FROM CTE;

But as you will notice, this returns some more values than the result set specified, I also added CALMONTH.

Resultset

CALMONTH    CUSTOMER    HISTORY_FLAG
201701      2340002          Y
201701      2340003          X
201701      2340004          Y
201702      2340001          X
201703      2340001          X
201703      2340016          Y
2019-0      2340001          X
2019-0      2340017          X
2019-0      2340018          X
2019-0      2340019          X
2019-0      2340020          X

To get closer to your resulset, I decided that whenever a customer has a revenue that is <> 0, the flag for that customer is set to 'X', even if he has a revenue of 0 in one of the months.
Query

;WITH CTE AS
(
        SELECT CALMONTH as CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6) as CALMONTH, SOLD_TO, REVENUE as INVCD_VAL FROM CUST_HISTORY_MIS
)
, CTE2 AS
(
SELECT CALMONTH,CUSTOMER, CASE WHEN SUM(INVCD_VAL) OVER(PARTITION BY CALMONTH,CUSTOMER) = 0 then 'Y' else 'X' END AS HISTORY_FLAG
FROM CTE
)

SELECT CUSTOMER, MIN(HISTORY_FLAG) as HISTORY_FLAG
FROM CTE2
GROUP BY CUSTOMER;

Resultset

CUSTOMER    HISTORY_FLAG
2340001         X
2340002         Y
2340003         X
2340004         Y
2340016         Y
2340017         X
2340018         X
2340019         X
2340020         X

Dataset used

CREATE TABLE Customer_history(CALMONTH char(6),CUSTOMER bigint,INVCD_VAL INT);

CREATE TABLE CUST_HISTORY_MIS(CALDAY date,SOLD_TO bigint, REVENUE   INT);

INSERT INTO Customer_history(CALMONTH,CUSTOMER,INVCD_VAL)
VALUES('201702',2340001,10),('201703',2340001,-10)
,('201701',2340002,40)
,('201701',2340002,-40)
,('201701',2340004,0)
,('201701',2340003,-20)
,('201703',2340016,0);

INSERT INTO CUST_HISTORY_MIS(CALDAY,SOLD_TO,REVENUE)
VALUES('20190203',2340001,5),
('20190203',2340001,5),
('20190301',2340019,11),
('20190330',2340017,12),
('20190311',2340018,NULL),
('20190313',2340020,NULL);


How can I benchmark a PostgreSQL query? I want to benchmark a query containing a user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark?

I know that timing can be turned on with \timing at the psql prompt, but ideally I'd like to have a script that takes care of everything automatically: running the query a few times, clearing PostgreSQL cache after each run (probably by restarting the PostgreSQL's service), and outputting the average running time (and memory used is a plus).

The widely used tool is the SQL command EXPLAIN ANALYZE, possibly with more options for more details in the answer. That outputs the query plan with the planner estimates plus actual execution times.

Why would you want to clear the cache? The generally more likely use case is that the cache is populated. If you still want to go that route, here is a related answer on SO.

Not resetting the cache, here are two simple ways to test with many iterations:

Simple UDF

EXPLAIN ANALYZE
SELECT f_myfunc(g) FROM generate_series (1,1000) AS t(g);

Or with random input - random numbers between 0 and 5000 in the example:

EXPLAIN ANALYZE
SELECT f_myfunc((random()*5000)::int) FROM generate_series (1,1000) AS t(g);

Or with a real life table:

EXPLAIN ANALYZE
SELECT f_myfunc(my_column) FROM my_tbl;   -- LIMIT n

More complex functions / queries

CREATE FUNCTION f_test(ct int, sql text) RETURNS void AS
$func$
DECLARE
   i int;
BEGIN

FOR i IN 1 .. $1 LOOP
    EXECUTE sql;  -- not safe against SQLi!
END LOOP;

END
$func$ LANGUAGE plpgsql

Call:

EXPLAIN ANALYZE
SELECT f_test(100, $x$SELECT * from MADLIB.gp('mock3', '{x1, x2, x3}', '{y1}', 100,20, 3)$x$

Careful: The query is actually executed!
Careful: Not fit for public use. Possible SQL injection.

Again, you can use random parameters if needed. Possibly with the USING clause of EXECUTE.


Percona Xtrabackup fails to backup InnoDB table with fulltext index I'm trying to make a backup of a large InnoDB table with a fulltext index using Percona Xtrabackup (2.4.12).

I have innodb_file_per_table set.

To create the backup, I'm running:

innobackupex --tables-file=tables.txt --no-timestamp /path/to/backup

Then I create the table exports:

innobackupex --tables-file=tables.txt --apply-log --export /path/to/backup

The --export run produces an error message for each of the fulltext index files ("FTS...") in my database directory:

InnoDB: Failed to find tablespace for table `webdb`.`FTS_00000000000005ce_0000000000000630_INDEX_1` in the cache...

Two questions:

Do I have to do the --export run right after I create the backup ? Or can I delay that until I actually need to restore a backup ?

What can I do about the error for the FTS files ? Is that normal ? Will Mysql re-create the fulltext index automatically or is it even included in the main tables .idb file ?

Update:

It would also help if someone could report that backing up tables with fulltext search using Xtravackup did or did not work with Mysql 5.7 for him. Then at least I would know if its a bug in the tool.

Do I have to do the --export run right after I create the backup ? Or
  can I delay that until I actually need to restore a backup ?

Not necessarily, as long as the backup completes with completed Ok then it is assumed that the backup is successful. It is still best practice to test your backups regularly.

  What can I do about the error for the FTS files ? Is that normal ?
  Will Mysql re-create the fulltext index automatically or is it even
  included in the main tables .idb file ?

This is expected if in your test.txt file the table with FULLTEXT indexes is not included. Basically you are not backing up that particular table so it will be skipped during the backup operation and during the prepare operation

Easily reproduced if you create test1 and test2 tables and then on the test1 table make sure you create a FULLTEXT index. On the backup stage:

181215 10:31:22 [01] Skipping ./test/FTS_000000000000002e_0000000000000033_INDEX_1.ibd.

On the prepare stage:

InnoDB: Failed to find tablespace for table `test`.`FTS_000000000000002e_0000000000000033_INDEX_1` in the cache. Attempting to load the tablespace with space id 30

One more note, it is recommended to use xtrabackup binary instead of innobackupex because starting in version PXB 8.0 innobackupex is removed.


Does the primary key of a table help decide what block a row is written to in Oracle? I'm trying to refactor a slow Oracle database, where the developers naively chose a VARCHAR2 UUD4 as the primary key for what is really an append only time series dataset. I've found that the uniqueness check for the primary key (frustratingly I can't quite convince the senior developer to drop that uniqueness constraint) and foreign key checks for UUID4s are the cause of poor performance.
This is the first time I've touched Oracle in my career, so I'm unfamiliar with how it works.  In MySQL the primary key absolutely effects how the table is allocated physically, but I am getting mixed messages about how it affects things in Oracle.  Obviously there are index-optimised-tables, but those have to explicitly set.
Looking at the index statistics for the primary key, the clustering factor is nearly as high as the row count.  Did the random primary key have anything to do with that?
If I have a random primary key, does it affect how the row is physically allocated? For instance, would it mean a similarly random allocation of the row to a block?

If so, how can I change that so the database will try to cluster the writes to a smaller set of blocks (to reduce random IO)?  Would moving to a 'clustered' identifier like a UUID1 improve
insert performance?

No, the values of a row have no implications for where Oracle will store it in a heap table. The only real exception is that if your row is going to be huge, Oracle will probably have to put it on a new block.
What makes you think the foreign key check is the cause for your poor performance? What’s the process that is too slow and how slow is it? A foreign key check on an insert requires very little IO as it’s just an existence check against an indexed column. There is usually much more significant low hanging fruit.


How to merge 2 tables in Oracle I have 1 big table with 25 milions of rows and i have to merge data of another table with 500,000 rows into the big table. the rows of both tables have the same columns and i have to check if the key columns exist then i update the rest of the columns , otherwise i insert the new row.
i would like to know which is the best way to do the update:
1) update the columns according to the key columns
2) delete the old column and insert the new one instead.

You can fully achieve this with MERGE:

MERGE INTO big_table B
USING (SELECT key_col, col1, col2 FROM small_table) S
ON (B.key_col = S.key_col)
WHEN MATCHED THEN UPDATE SET B.col1=S.col1, B.col2=S.col2
WHEN NOT MATCHED THEN INSERT (B.col1, B.col2)
VALUES (S.col2, S.col2)
/


convert csv to insert sql statements I have a CSV file and I want to convert it into a SQL INSERT statements.But I could only find online converters but I would like to know can I create it manually ?

You could open it in Excel then write a concatenate function that builds an insert statement, then copy it down the rows.

But if you really just want to get the data into a table, you could load it using the 'SQL Server Import and Export Wizard', or build an SSIS job to do it.


Quick nearest neighbor search in the 150-dimensional space I want to create a database using any of the possible RDBMS. It will have a table with approximately 150 columns. The objective is to perform nearest neighbor search of some other objects. So it's a NNS in the 150-dimensional space.

I already tried to use some obvious methods like L1 or L2 distances but of course it takes a lot of time for tables with many rows. Also I tried to look at the KD-tree (note I did not test it) and PG-Strom but they are not a good solution for data with a many dimensions.

Can I somehow improve the speed of the described search using math methods (like KD-tree) or tech methods (like PG-Strom)?

I will try to use any RDBMS which allow to improve speed of the NNS. But MySQL and PostgreSQL are the most appropriate DBMS for me.

PostgreSQL 9.6 using cube

First install the cube extension

CREATE EXTENSION cube;

Now we will create some n-dimensional space with 100,000 points in 50 dimensions. In addition we'll add a GIST index.

CREATE TEMP TABLE space_nd
AS
  SELECT i, cube(array_agg(random()::float)) AS c
  FROM generate_series(1,1e5) AS i
  CROSS JOIN LATERAL generate_series(1,50)
    AS x
  GROUP BY i;

CREATE INDEX ON space_nd USING gist ( c );
ANALYZE space_nd;

Now we will generate a single point and use the <-> operater to find the nearest point using Eucledian distance.

WITH points AS (
  SELECT cube(array_agg(random()::float)) AS c
  FROM generate_series(1,50)
    AS x
)
SELECT i,
  pg_typeof(space_nd.c),
  pg_typeof(points.c),
  cube_distance(space_nd.c, points.c)
FROM space_nd
CROSS JOIN points
ORDER BY space_nd.c <-> points.c
LIMIT 5;

PostgreSQL 9.6+ supports other distance operators over cube. All of which can use the GIST index we created. Namely,

a <-> b float8  Euclidean distance between a and b.
a <#> b float8  Taxicab (L-1 metric) distance between a and b.
a <=> b float8  Chebyshev (L-inf metric) distance between a and b.

That said there is one caveat,

  To make it harder for people to break things, there is a limit of 100 on the number of dimensions of cubes. This is set in cubedata.h if you need something bigger.

You ask for 150 dimensions. That may present a minor complication.


don't want to be able creating stored procedures with incorrect Right now I can execute a CREATE PROCEDURE or an ALTER PROCEDURE script with incorrect table and column references in the MS SQL Server Management Studio and using ADO.NET commands. The scripts run fine, but if I open the created stored procedures on MS SQL Server using Management Studio, then I'll see incorrect tables and columns being highlighted in red. I want to get those errors when creating or altering stored procedures. How do I do that?

No, you can't change the way deferred name resolution works. See this Connect item, this Connect item and also this section on Erland Sommarskog's "SET STRICT_CHECKS ON" proposal: http://www.sommarskog.se/strict_checks.html#killDNR


MySQL can take more than an hour to start I have a mysql (Percona) 5.7 instance with over 1Million tables. When I start the database, it can take more than an hour to start. Errorlog doesn't show anything, but when I trace mysqld_safe, I found out that MySQL is getting a stat on every file in the DB.

Any idea why this may happen? Also, please no suggestion to fix my schema, this is a blackbox. Also keep in mind that I've heard it all regarding how bad the design is....

Thanks

The solution is to drastically decrease the number of files.  I see 3 approaches, depending on your version:

Plan A (the only option for 5.6 and earlier):

Set innodb_file_per_table=OFF, restart your client, then do ALTER TABLE ... ENGINE=InnoDB all but the 1000 largest tables.  This will shovel all the non-huge tables into ibdata1.

Plan B (if you have at least 5.7, and you have 10-1000 databases):

If you have multiple databases, create a General Tablespace per database.  Put non-huge tables into those tablespaces.

Leave the huge tables in their own tablespaces.

To facilitate either Plan A or B, write a SELECT against information_schema that creates the desired ALTER statements, then execute them.

Plan C:  Move to 8.0, which might actually work reasonably fast with a million tables due to its Data Dictionary.  (Or, you might need a combo of Plan C together with A or B.)


Can every SPARQL query be expressed as a GraphQL query? The question is really formulated in the title :-)

I am working on an RDF project but I want to make it as easy as possible to interact with the API. Therefor GraphQL seems the most likely.

UPDATE:
Got an answer on Quora.

We are currently working on a GraphQL library to query a SPARQL Endpoint according to an OWL schema. It will be the main API access point for the DATAtourisme, french government project to handle and share touristic open data.

Contact me to tell me more about your project, i will tell you if the library can fit your needs.


Storing mail in database, data type I've found pretty poor informations about mail databases.

My need is to archive mail messages downloaded from a normal pop3 or imap account: I am an ordinary user and I do not own the server, but just the mailbox. So I should exclude solutions like dbmail, suggested in other answers.

Two questions:

1) can MySQL be used for this purpose?

2) If yes, which is the best data type to be used for the column which will contain the body of the mail? It can include attachments (represented as sequences of characters, as you know) and its length is definitely unpredictable: it may vary from a few bytes to several MiB and it should not impose a (small) upper limit to the design. It should have a decent performance, if exists.

In brief: which is the most common approach when handling this kind of data with a MySQL database?

First, find out if "mail" guarantees that all data is utf8 or ascii or whatever.  If utf8, then use

MEDIUMTEXT CHARACTER SET utf8mb4

for a limit of 16MB.  For a 4GB limit, change to LONGTEXT.

If the mail is not forced into some character encoding, then use

MEDIUMBLOB or LONGBLOB

(without a charset).


Operators similar to 'in' that compares two sets I want to compare two sets of values and see whether there are intersections. The first set of values are specified at the runtime and the second set of values are stored in a row of a database.

Here are some sample code:

CREATE TABLE #tab1 (var1 INT, var2 INT , var3 INT)

INSERT INTO #tab1
VALUES (1,2,3),(0,0,0),(0,4,0)

Select
a = (case when 1 IN (var1,var2,var3) OR
               2 IN (var1,var2,var3) OR
               3 IN (var1,var2,var3)
    THEN 1 else 0 end)
from #tab1

I feel that the code is too cumbersome, and am wondering whether there are some simple way to do it. Thanks!

You can just JOIN to real or dynamically derived tables:

DECLARE @var1 INT=2;
DECLARE @var2 INT=15;
DECLARE @var3 INT=-7;

IF EXISTS (SELECT 1 FROM
             (VALUES (1),(2),(3))tableNums(number)
             INNER JOIN (VALUES (@var1), (@var2), (@var3))tableVars(var)
             ON tableNums.number=tableVars.var
       )
    SELECT 1    --or do something else interesting
ELSE SELECT 0   --or do the opposite

If the list was very long, I'd probably create it in a #temp table in advance instead of doing it in the statement, but the JOIN and EXISTS logic would be the same.

Similarly, we could used INTERSECT instead of JOIN:

CASE WHEN EXISTS ( SELECT * FROM (VALUES (1), (2), (3)) AS x(i)
                 INTERSECT
                   SELECT * FROM (VALUES (@var1), (@var2), (@var3)) AS y(i)
                 )
THEN 1 ELSE 0 END

Test at dbfiddle.uk.


MySQL has a funny authentication style

Actually, you need to use two functions

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

You must be logging as an anonymous user. CURRENT_USER() will tell you what mysqld allowed you to come in as.

My guess is that qaz is the OS user and it was chosen by the mysql client as the user name to login.

Your set up is not secure. Please run this:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE user='root' AND host<>'localhost';
UPDATE mysql.user SET password=PASSWORD('whateverpassword')
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

This does three(3) things:

Blocks anonymous users
Keeps only root@localhost
Assigns a password to root@localhost

If you cannot log back into mysql, do the following

service mysql restart --skip-grant-tables --skip-networking
mysql -e"UPDATE mysql.user SET password=PASSWORD('whateverpassword') WHERE user='root' AND host='localhost';"
service mysql restart

I wrote earlier posts about this

Cannot drop anonymous user from mysql.user
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Secure method beside phpMyAdmin for remote mysql
MySQL : Why are there "test" entries in mysql.db?
mysqladmin user account not secure?


Postgres multicolumn vs combined single column indexes The answer is always "run it and see how it works on your dataset" of course, but looking for general guidelines here. I have three queries:

... WHERE A = '...' AND P = '...' AND Q = '...'
... WHERE B = '...' AND P = '...' AND Q = '...'
... WHERE C = '...' AND P = '...' AND Q = '...'

The first query featuring A will be more common. Any general recommendations on whether to use 5 separate single column indexes, or whether to use 3 combined (A, P, Q), (B, P, Q), (C, P, Q) ?

If the P = '...' AND Q = '...' is highly selective, I would just put one index on that pair of columns and leave it at that.  If it is only moderately selective and if I needed individual indexes on A, B, and C anyway for other queries, then I'd probably tack P and Q onto the end of each of those indexes, unless the P and Q columns are wide and so would bloat the size of the indexes.

I wouldn't use 5 separate single column indexes for this, unless I needed each one for some other purpose anyway.  They would have to be combined with a BitmapAnd, which is much less efficient than using a multi-common index with equality conditions.


Worse query performance even after improving the query by making it to run in parallel mode I have a SQL Query which gets generated from application using EFF.
It uses a view and this view has joins to multiple tables in there.
The query runs slow and when i looked at the execution plan,i see that query runs in parallel(wanted it that way as it is a complex query and serial execution is very slow,so purposefully removed serial forcing components).
I also noticed some keylook up and i kind of ignored it earlier because the cost of that operation looked small.
Now the query runs slow and and i created covering indexes to remove key lookup,but even after that the plan uses the old index and is not using covering indexes.Not sure why?

Here is the executing plan from Paste the plan.
https://www.brentozar.com/pastetheplan/?id=Bk1YECnOH

Any findings from the plan,which can help to improve the performance is highly appreciated.

I checked statistics and it is updated today morning ,so it is not the cause.
I am using SQL Server 2016 and have 105GB of RAM allocated to SQL Server out of the 128GB.It is enough i believe.
I have 10GB of tempdb space allocated.

I don't see any other bad signs other than keylook up and i am not an expert in dicing and slicing execution plan.So any help is greatly appreciated.

One thing stands out clearly for me - this warning in the XML query plan:

<Warnings>
    <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="N'Public'=CONVERT_IMPLICIT(nvarchar(20),[r].[SecurityLevel],0)"/>
</Warnings>

A little known speed killer in SQL Server is passing an nvarchar() parameter to use in an indexed seek against a varchar() indexed value.  What SQL Server does in this case is to convert each value in the indexed field to an equivalent nvarchar() value, and compare them against the parameter.

Why doesn't it convert the nvarchar field to a varchar and use the index? Because all of varchar is a subset of nvarchar, but not all of nvarchar is a subset of varchar. Thus, to do the correct comparison, the varchar values must be converted to nvarchar values.

The effect of this is that the index is not used as an index (and may not be used at all, depending on the fields needed.)  Instead, the index is scanned from beginning to end to identify the matches, with each index entry having the value converted, one by one.  This is very slow.

There are two ways to handle this:

Change the field in the table and index to nvarchar
(Recommended) Change the parameter value to varchar before using it in the lookup.

The change can be handled in a number of ways. Either change the parameter type in the stored procedure being called, change the parameter type being passed by the application (There are articles on how to do this for Entity Framework calls to SQL), or add a CAST/CONVERT call in the code (which may be more trouble than it is worth.)


Exclusive SUMs with CASE I'm looking for a way to make each SUM exclusive, i.e., it doesn't count one record more than once. You might think that with the way the date ranges are set up, it wouldn't count more than once, but in this particular database application the dates are very peculiar. TotalCount is correct, but I'm getting 2 additional records for Count1 and Count2. I have tried wrapping the SUMs in an outer CASE, but have not been able to get it to work. Any ideas?

select
 Sum(case   --'Before Admission'
 when ((EvalCheckboxDate < UnitArrivalTime) and (m_value<>'No' or m_value   is not null)) then 1 else 0
 end) as Count0,

 Sum(case   --'Within 24 Hours'
 when ((EvalCheckboxDate >= UnitArrivalTime and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count1,

 Sum(case  --'Between 24 and 48 Hours'
 when ((EvalCheckboxDate > dateadd(hh,24,UnitArrivalTime) and EvalCheckboxDate <= dateadd(hh,48,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count2,

 Sum(case   --'Greater than 48 Hours'
 when (EvalCheckboxDate > dateadd(hh,48,UnitArrivalTime) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count3,

 Sum(case   --'Recorded as Not Done'
 when m_value='No' then 1 else 0
 end) as Count4,

 Sum(case   --'Not Recorded'
 when m_value is null then 1 else 0
 end) as Count5,
--
count(*) as TotalCount
from TFDataset

Here's one way to make them mutually exclusive:

select count(case X when 'A' then 1 end)
     , count(case X when 'B' then 1 end)
     , ...
from (
  select
    case   --'Before Admission'
    when ((EvalCheckboxDate < UnitArrivalTime)
     and (m_value<>'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate >= UnitArrivalTime
     and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime))
     and (m_value<>'No' or m_value is not null)) then 'B'
    when ...
    [...]
    end as X
  from TFDataset
) as T

I.e. the idea is to use one case stmt to classify each row and then count the amount of each one. Thye code will be a bit simpler if you can live with something like:

select X, count(1)
from (
  select
    case   --'Before Admission'
    when ((EvalCheckboxDate < UnitArrivalTime)
     and (m_value<>'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate >= UnitArrivalTime
     and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime))
     and (m_value<>'No' or m_value is not null)) then 'B'
    when ...
    [...]
    end as X
  from TFDataset
) as T
group by X

and then transpose the result in the application


Creating a SQL Login and User on an AlwaysOn Replica I have a database (DB1) which is synced to (DB2) on another server (via AlwaysOn - SQL Server 2014).

I want to give a user read access to DB2. I create a SQL login and database user in DB1. The new database user is migrated to DB2, but the SQL login is not (which makes sense).

The problem is that when I attempt to create a SQL login for the user on DB2 and give the user read rights to DB2, tied to the user account that came from DB1, I get an error saying DB2 is read only and cannot be modified.

So, how can I accomplish this?

You need to create the login on the DB2 server. Use Robert's script so that it grabs the sid and password, well if it's a SQL account that is.

Then grant the permission on DB1. That permission will auto-magically get copied over to DB2 due to the AG.

There is no way to just grant permission on DB2. It has to be done on DB1. You can control access by disabling the login on DB1 though.


Choosing a database for big data Let's say I want to write a web service that stores home automation device readings such as thermostats, humidifiers, etc. The data would take the form of:

user_id | device_id | tag | value | timestamp

A user could have 5, 10, or 20 devices connected based on account type and readings could be taken at 1, 5, or 10 minute intervals based on account type. The service could record 10, 50, or 100 tags for each connected device.

This means I would theoretically need to support a batch insert of 2000 rows per user per minute.

Let's say I want to scale to 10,000 users. That means an upper limit of 20,000,000 rows inserted per user per minute. So 28 billion rows per day.

What kind of databases should I be considering to handle this kind of load and how would it scale? I'd especially be worried about storage space, I think it would need to make use of cloud storage.

I've heard of a company which is doing this. They're using a Cassandra cluster with several nodes (in two data centers).


Why expdp does not include identified by statement? I have the following error when I do a impdp (from a pdb in a 12c to 11g. I also used the parameter version=11.2.0)

IMPDP LOG:

ORA-01938: IDENTIFIED BY must be specified for CREATE USER
Failing sql is:
 CREATE USER "BRELELE" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"

But when I've created the user I gave a password for

create user BRELELE identified by manager default tab....

Why is this happeninng?

This is a known bug in 11.2.0.1 which is fixed in the 11.2.0.2 patch set. You should patch the database - or even better upgrade to a version that is supported.

You could create the user in advance before doing the import.


Why am I getting nondeterministic results with the deterministic function STDEV()? This is type of query I'm trying to run:

WITH CTE_Ordered AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId
  FROM SourceTable
),
CTE_Top1_PerPartition AS
(
  SELECT *
  FROM CTE_Ordered
  WHERE PartitionRowId = 1
),
CTE_Calculations AS
(
  SELECT AVG(NumberField1) AS NumberField1_Avg, StdDev.StdDev AS NumberField1_StdDev
  FROM CTE_Top1_PerPartition
  CROSS JOIN
  (
     SELECT STDEV(NumberField1) AS StdDev
     FROM CTE_Top1_PerPartition
  ) AS StdDev
  GROUP BY StdDev.StdDev
)

-- Final Select
SELECT *
FROM CTE_Calculations

Every time I run the final select, my NumberField1_StdDev value changes even though SourceTable is isolated and doesn't change.

I noticed if I select CTE_Top1_PerPartition into a temp table first and then run the rest of the query off of that temp table, then I get the same result for NumberField1_StdDev every time.

I would guess it has something to do with the order the results are returned in the CTE_Top1_PerPartition CTE, but I'm not sure why.

row_number is not deterministic if there can be ties (i.e. rows with the same PartitionField and DateField values).  Any of the tied values might end up with a PartitionRowId of 1 which would presumably change the final result.

You could use rank instead of row_number but that would cause you to consider all the tied rows which may not be what you want.  You could also update your analytic function to add additional order by criteria to ensure that row_number returns a deterministic first row.


SQL Server "Suspect" database? What do you do when you have a database that is marked as Suspect?

Restore from the last backup?

Please advise.

First thing is to make sure you DO NOT detach that database.

Restoring from the last known goodbackup is fine. Otherwise you will need to use the EMERGENCY repair mode (I am assuming you are running SQL 2005 or higher). Here are a couple of posts from Paul Randal on the subject. Read them both before you start taking any action.

Creating, detaching, re-attaching, and fixing a SUSPECT database

EMERGENCY-mode repair: the very, very last resort


How to resolve COM server application local activation permission for machine account I am trying to enable SSIS package logging to event logs. When an attempt is made to write to the event log the following error appears in the system event logs:

  The application-specific permission settings do not grant Local
  Activation permission for the COM Server application with CLSID
  {806835AE-FD04-4870-A1E8-D65535358293}  and APPID
  {EE4171E6-C37E-4D04-AF4C-8617BC7D4914}  to the user DOMAIN\machine$
  SID (S-1-5-21-16020293-282541685-632688529-241633) from address
  LocalHost (Using LRPC). This security permission can be modified using
  the Component Services administrative tool.

I've dealt with this error before but the fact the error relates to a machine account has thrown me. Does this mean I have to specifically grant permissions to the machine account in component services? The app pool account running under the NETWORK SERVICE account.

As @spaghettidba said, the error message is pretty clear. APPID EE4171E6-C37E-4D04-AF4C-8617BC7D4914 is Microsoft SQL Server Integration Services 12.0. It looks like you already knew what you had to do:

Either grant access to the machine account in Component Services \ DCOM Config \Microsoft SQL Server Integration Services 12.0 (In properties \ Security \ Launch and Activation Permissions)
or
Change under which account the service request is being made.


Upgrading to SQL Server 2012 from SQL Server 2008 I have upgraded my SQL Server 2008 R2 Enterprise edition to SQL Server 2012. Even after upgrade I can see & use SQL Server 2008 R2 instance without any issue. Did my upgrade went fine?

I can open default instance both from SQL Server 2008 and 2012. Also I have updated the compatibility level of all databases to 110.

After in-place upgrade

Edit - version

After running

SELECT @@Version

SQL Server 2012

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

SQL Server 2008 R2

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Turning my comments to answer :

SSMS is just a tool to connect to SQL Server.

You did an inplace upgrade, but were connecting to SQL Server 2012 using SSMS 2008R2 and SSMS 2012.

Using T-SQL - will give you the correct version:

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

Best practice:

Before upgrade - run the above script to get the version and after upgrade, run it to make sure that you are on correct right version.
Always use the latest version of SSMS (regardless of SQL Server version).


data guard / network failure: deadline before global crash? In a Maximum Availability scenario where there's one primary and one physical standby database, how long can we expect the system to run without problem when the standby site becomes out of reach (example: network error, disaster on the B site etc.)

The log archive dest state setting must be set to DEFER. But does it simply mean everything will roll fine until my local hard drive (primary database) explodes the +REDO area?

Is a simple anticipation enough to calculate the "autonomy" of the primary database or is there a way to guarantee it?

If the standby site becomes unreachable, the primary site will not delete archivelogs as they will be still needed by the standby site. If you try to backup and delete these archivelogs, you will receive an ORA-08137 error, and eventually your archive area will become full and your database will stall.

Setting log_archive_dest_state_n to DEFER lifts this "restriction". If the stanbdy database becomes unreachable, you can manually set log_archive_dest_state_n to DEFER, with that, you disable that destination, and the primary site will be able to delete the archivelogs.

An other way to work around this is to write your backup scripts in a way that is prepared to handle this situation. By adding the FORCE option to a DELETE operation, RMAN skips the above check and deletes the archivelogs specified regardless of the state of the standby database. For example adding this line to the end of your backup script:

delete force noprompt archivelog until time 'sysdate -1' backed up 1 times to 'sbt_tape';

The above command deletes all archivelogs older than 1 day and backed up at least once to your backup server.


Select fixed number of records/rows from growing table from SQL Server? Background: A SQL-Server database table is used to store weekly reports of some system memory usage, meaning that each week a new table row/record/line is inserted.

The question is how a fixed number (e.g. 5 records) of records can be queried, with the side-condition that those records are equally spaced within the time-range.

if this was the table

id | memory
-----------
01  1231
02  1241
03  1260
04  1300
[.... other 92 rows ...]
97  2001
98  2002
99  2020

and with said 5 records I would like have a table as such

id | memory
-----------
01  1231
25  1600
50  1800
75  1900
99  2020

since it would most represent the overall picture of the data.

There's probably a more elegant way to achieve this but it can be done by using the NTILE function inside a CTE.

WITH cte AS (

  SELECT NTILE(5) OVER (ORDER BY id asc) AS dist,
  memory
  FROM TABLE)

  SELECT TOP 1 id, memory FROM cte WHERE dist = 1

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 2

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 3

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 4

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 5;


MySQL Replication ERROR 1130: error conecting to master I'm having difficulty connecting MySQL slave database to master using MyWorkBench.

I tried:

executing

CREATE USER 'replication'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON . TO 'replication'@localhost' WITH GRANT OPTION;

using IP address instead of the server name

I confirmed that:

I followed all the steps for setting server_id for slave and master
I could ping both master and slave servers

Can anyone help me with this, please?

Check the quotes.
ON *.*

GRANT ALL PRIVILEGES ON . TO 'replication'@'localhost' WITH GRANT OPTION;


Why create a Materialized View Log with Primary Key or RowID, etc? I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED VIEW LOG on the base table.

I'm looking at how to create one and I see that I can do:

create materialized view log on t
create materialized view log on t WITH PRIMARY KEY ;
create materialized view log on t WITH ROWID ;
create materialized view log on t WITH ROWID, PRIMARY KEY ;
create materialized view log on t  WITH SEQUENCE ;
create materialized view log on t WITH (column)

I don't see or understand the benefits or cons of this various options

This is what I'm thinking of creating:

CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY

CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1
    NOLOGGING
    CACHE
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS
        SELECT *
        FROM LLATTRDATA D
        WHERE
            (D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR
            (D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR
            (D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR
            (D.DEFID = 3072256 AND D.ATTRID = 5);

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => 'me',
    tabname => 'MV_LLATTRDATA_TEST1');
END;

A MView Log table is with the name MLOG$_BASE_TABLE_NAME. Check this log table which show the changes being tracked. For eg, Primary key, ROWID, columns, OLD/NEW, DML Type, etc.

I had a similar challenge when designing a MView. I could try out following 3 to create the MView

WITH ROWID
WITH PRIMARY KEY
WITH (columns...)

When using ROWID and PRIMARY KEY, base table's record identification is present on the LOG table. In this case a query on the MView will have to fetch data from its MView storage and MView Log and the base table.
In the last approach, the columns specified in the list is stored on LOG table itself. In this case does oracle refer to the base table again or will it use the values available from the MView Log?

Further, the view log you have created, must specify INCLUDING NEW VALUES like below
CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY INCLUDING NEW VALUES
This will ensure you have fast refresh working.

These are the oracle pages i have gone through
https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6003.htm


Check for existing matches to find the field their grouped-by So I have a table of values associated with different sessions using a field called sessionid, lets say the table has 3 other fields: itemcode, itemcost, & itemeffect.

What I end up doing, is getting a standard set of items (rows) from another table that contains the defaults for each country, and then store it in this sessiontable with a guid in the sessionid field. The user can then modify the items for their session only.

What I want to do instead is to search for an existing session match that has the same items and respective values, to avoid session repetition as the processing later in the application is expensive (reports etc).
  A match would return the sessionid, otherwise I will stash the new session and items for repeated use.

I'm struggling to search for a solution on stackExchange (im working off this q/a, I've realised EXIST and UNION won't work because I dont know the sessionid and my users session guid wont match (hence the question to begin with).

I'm thinking something to do with group by sessionid, and check with the worlds longest sql query (or LINQ madness) with a check for each item and item-field, and addtionally a check that no other (unmatched) items exist in matching sessions, and in theory there will only ever be one match or none!

Here's my pseudo-code:

useritems = getCountryDefaultItems();
var existingid =(
                select grpresult from
                   (select item from db.sessionints
                   group by item.sessionid
                   where item in useritems (ignore sessionid)
                   ) g
                where g.count == useritems.count
                )
          .first().sessionid;

I've had a go, and I believe it should be FULL OUTER JOIN so I hear, but sqlfiddle says no so I played with left + right + inner joins :/ sqlfiddle

SELECT COUNT(1),a.SessionID
    FROM sessionitems a
    RIGHT JOIN useritems b   -- should be FULL OUTER JOIN ?
        USING (`SessionID`,`InterventionCode`, `Included`, `IntLvl`, `Uptake Baseline Smokers`, `RelativeEffect`, `Cost`, `IntType`, `Baseline`, `Current`, `Alternative`, `UpPopulation`, `EffPopulation`, `Name`)
       WHERE  (a.`InterventionCode` IS NULL OR b.`InterventionCode` IS NULL) AND  (a.`Included` IS NULL OR b.`Included` IS NULL) AND  (a.`IntLvl` IS NULL OR b.`IntLvl` IS NULL) AND  (a.`Uptake Baseline Smokers` IS NULL OR b.`Uptake Baseline Smokers` IS NULL) AND  (a.`RelativeEffect` IS NULL OR b.`RelativeEffect` IS NULL) AND  (a.`Cost` IS NULL OR b.`Cost` IS NULL) AND  (a.`IntType` IS NULL OR b.`IntType` IS NULL) AND  (a.`Baseline` IS NULL OR b.`Baseline` IS NULL) AND  (a.`Current` IS NULL OR b.`Current` IS NULL) AND  (a.`Alternative` IS NULL OR b.`Alternative` IS NULL) AND  (a.`UpPopulation` IS NULL OR b.`UpPopulation` IS NULL) AND  (a.`EffPopulation` IS NULL OR b.`EffPopulation` IS NULL) AND  (a.`Name` IS NULL OR b.`Name` IS NULL)
GROUP BY a.SessionID;

, but sqlfiddle is having errors doing MSSQL schema today, so I've used mysql for now which doesnt support FULL OUTER JOIN on sqlfiddle:
  I can get a count of exactly matching rows when there's an exact match, but the id comes back null, and if I remove the COUNT(1) and return * it returns only one row (is this a restriction on results in nested select queries) which might be okay as the session is a match, but I'm worried I have overlooked something and don't appreciate where the query will fall down...

It's a mess but I think it works:

http://sqlfiddle.com/#!9/585394/2

SELECT SessionID as ExistingMatch FROM (

    SELECT COUNT(c.ainc) as intcodematch,SUM(c.bnos) as Incorrect, c.SessionID FROM
      (
        SELECT a.SessionID,a.InterventionCode as ainc, b.InterventionCode as binc, IF(b.InterventionCode IS NULL,1,0) as bnos
            FROM sessionitems a
            left JOIN useritems b
              ON  a.`InterventionCode` = b.`InterventionCode` AND  a.`Included` = b.`Included` AND  a.`IntLvl` = b.`IntLvl` AND  a.`Uptake Baseline Smokers` = b.`Uptake Baseline Smokers` AND  a.`RelativeEffect` = b.`RelativeEffect` AND  a.`Cost` = b.`Cost` AND  a.`IntType` = b.`IntType` AND  a.`Baseline` = b.`Baseline` AND  a.`Current` = b.`Current` AND  a.`Alternative` = b.`Alternative` AND  a.`UpPopulation` = b.`UpPopulation` AND  a.`EffPopulation` = b.`EffPopulation`
        GROUP BY a.SessionID,a.InterventionCode

     ) c GROUP BY c.SessionID

) q WHERE q.Incorrect = 0 AND q.intcodematch = (SELECT COUNT(InterventionCode) FROM useritems)

I think I need to add support for nulls in Cost which is a possible situation or cast them to a '0'.

Any thoughts?


Find queries performing full scans I have a monitoring system that is telling me that the Full Scans /sec on one of my servers is rather high.  I am trying to determine which queries may be causing the issue.  Is there a simple/easy way to determine which queries are performing full scans on the server?

This query will identify Table and Index Scans where no seek predicate is present in the query plan.  Keep in mind this query is pretty computationally and I/O heavy, so you should probably not run this on a schedule in production.

Also, keep in mind, since this looks at the plan cache, there are a variety of circumstances where queries may be performing scans that are not reported, including plans that have been evicted from the cache, single-use plan stubs when ad-hoc query plans are enabled, queries with OPTION (RECOMPILE), zero-cost plans, etc.

By design, I've filtered out #temp tables and @table variables.  Depending on your requirements, it might be good to eliminate those filters.

Also, if you have plans with over 128 levels of nesting, the xquery processor will report an error.

The first column of the results shows the number of times the plan has been used - this number is reset if the plan is evicted, or if the server restarts, or if the plan cache is cleared.  The 2nd column is the entire query plan - if you click the value in the results pane of SQL Server Management Studio, you'll see the nice graphical plan pop up.  The TableScans and IndexScans columns contain details about each particular table or index that was scanned, including the columns pertinent for the query.

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS qp)
SELECT decp.usecounts
    , QueryPlan = deqp.query_plan
    , TableScans = CONVERT(xml,
        (
        SELECT ts.x.query('..')
        FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:TableScan/qp:Object') ts(x)
        WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
            AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
            AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
        FOR XML PATH (''), ROOT ('xml'))
        )
    , IndexScans = CONVERT(xml,
        (
        SELECT ts.x.query('..')
        FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:IndexScan/qp:Object') ts(x)
        WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
            AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
            AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
        FOR XML PATH (''), ROOT ('xml'))
        )
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) deqp
WHERE (
    deqp.query_plan.exist('/qp:ShowPlanXML//qp:TableScan') = 1
        AND EXISTS (
            SELECT 1
            FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:TableScan/qp:Object') ts(x)
            WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
                AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
                AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
            )
    )
    OR (
    deqp.query_plan.exist('/qp:ShowPlanXML//qp:IndexScan') = 1
        AND EXISTS (
            SELECT 1
            FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:IndexScan/qp:Object') ts(x)
            WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
                AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
                AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
            )
    )
ORDER BY decp.usecounts DESC;

Another caveat emptor for the above query; it filters #temp tables by looking for table names that have a # anywhere in the name.  So, [this#is#a#dumb#name] will be excluded.  As would [hell@].

Also, the wonderful sp_BlitzCache, by the Brent Ozar team, does a great job looking for scans.  FYI, I'm not affiliated with them, I just think they do great work.


postgresql backup and restore I'm trying to backup the database I made in postgresql but it doesn't seem to work

root@localhost:/home/tests# sudo -s -u postgres
postgres@localhost:/home/tests$ psql
psql (8.4.17)
Type "help" for help.
postgres=# pg_dump test_site > payroll.dump.out
postgres-# ls
postgres@localhost:/home/tests$ exit
root@localhost:/home/tests# ls # Where is the backup?
cars  manage.py  media  staticfiles  test.db  tests  tests.db  test.wsgi

Can someone help me , please :)

pg_dump is not an SQL command. It's a separate command, like psql. So you can't run it from within a psql session, you must run it from a regular shell.

The reason you're not getting an outright error is that you didn't write a semicolon on the end of the line. That's why the prompt changed, too:

postgres=# pg_dump test_site > payroll.dump.out
postgres-# ls
        ^ <-- See how it goes from = to - ?

That indicates that it's a continuation line, so psql is buffering up a statement and hasn't sent it yet.


What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null? I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this fake table:

CREATE TABLE "tbl" (
    "id" serial,
    "name" varchar(40),
    "num" int,
    "time" timestamp
    PRIMARY KEY ("id"),
    UNIQUE ("id")
);

Also name, num, time are not explicitly stated as NOT NULL, in reality they are, because the enforcement happens on the application side.

My feeling is that it should be changed, but the counterpoint is that the application level makes sure that null values can't appear here and no one else manually modifies the table.

My question is: What are the benefits (performance, storage, consistency, something else) and drawbacks (assuming that I already verified that there are no nulls present at the moment, and from the business logic there should be no nulls) by setting an explicit NOT NULL constraint?

We have a good code review process and a reasonably good documentation, so the possibility that some new person would commit something that breaks this constraint is not really enough to justify the change.

This is not my decision, so this is exactly why I am looking for other justifications. In my opinion, if something can't be null and a database allows you to specify that something is not null - then just do it. Especially if the change is super simple.

What happens when a new programmer arrives and has to write an app against that db? They don't know that field x has to be NOT NULL.

Another programme might assume that all field x's are NOT NULL for performing counts say, but some now are NULL because of the new programme, leading to inconsistent and difficult to trace errors.

IMHO it is always best to enforce data integrity rules as near to the data as possible, i.e. in the database. That way, new apps and/or programmers can't mess up your data.

Programmers, applications, languages and frameworks come and go. Data and databases tend to persist. The database is your last line of defence against inconsistent, potentially erroneous data.

Make maximum use of your database's integrity constraint enforcement mechanisms, even at the expense of performance. A slow system that produces correct results is infinitely superior to a fast one that gets things wrong!


Existing DB2 connection does not work inside a script I connected to the DB2 instance, and I can successfully verify that the connection exists:

db2inst1@hostname:/tmp$ db2 connect

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

However, when I run the following script

db2inst1@hostname:/tmp$ cat test.sh
#!/bin/bash
db2 connect

the connection seems to disappear:

db2inst1@hostname:/tmp$ ./test.sh
SQL1024N  A database connection does not exist.  SQLSTATE=08003

I wonder,

What is reason for that?
How to work in scripts with the existing connection?

As @mustaccio points out in the comment, a connection is local to its subshell.

In order to call the shell script in the context of the current shell, source could be used:

db2inst1@hostname:/tmp$ source test.sh

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

Of course, . also works:

db2inst1@hostname:/tmp$ . test.sh

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME


Local indexes vs Global indexes for partitioned tables in Oracle I have partitioned a table that is growing almost at a rate of 7-8 million rows a day. The partitioning has been done using a timestamp column as data can be archived or discarded a few weeks later. I have also created an index on the table which are on primary key or another value that is unique. My indexes are partitioned as well, however, the partitioning of index has been done using a hash function and does not include the partition key of table (which is a timestamp). So I have a few questions.

For an ever-growing table which has majority inserts, does partitioning alleviates slowness with inserts and updates or partitioning indexes is enough to gurantee a faster insert and update. I have been told that there may not be value in partitioning indexes and table differently. Does it make sense to partition tables to speed up inserts?
The table is a write-intensive table. It is currently mostly inserts, one update per row and 2-3 lookups within seconds of creation by indexed id and then the record is never accessed for any operation.
Is it optimal to define local indexes on the unique id or is it better to define global indexes and partition them as I have already done? If I define global indexes, without table partitioning key in it (timestamp, which is not used in lookup), will the access be more expensive if the number of partitions is huge?
What are the downsides of having global partitioned indexes for constantly growing data? Once I decide to remove the partitions at a later point of time, since the indexes are not partitioned by timestamp but instead by unique id, will the operation cause a direct impact on functioning indexes?

Any other recommendations will be helpful.

…does partitioning alleviates slowness with inserts and updates or partitioning indexes is enough to guarantee a faster insert and update…Does it make sense to partition tables to speed up inserts?

No, absolutely not. The primary benefit of partitioning is the low cost of dropping partitions. You said "data can be archived or discarded a few weeks later", which might make you a genuine candidate for partitioning.

  Is it optimal to define local indexes on the unique id or is it better to define global indexes and partition them as I have already done?…What are the downsides of having global partitioned indexes for constantly growing data? Once I decide to remove the partitions at a later point of time, since the indexes are not partitioned by timestamp but instead by unique id, will the operation cause a direct impact on functioning indexes?

Using global indexes goes against the grain of partitioning in the first place — they are marked 'unusable' if you drop a partition.

You may find this ORACLE-BASE article a useful read.


Should developers be allowed to use LocalDB vs a "development" instance? Much like the vein of the question that was posted here previously around "Should developers be able to query production databases?" I wanted to get your thoughts on another particularly annoying topic!

Many companies prevent developers from install SQL Server Express and the like on development machines, instead promoting the use of centralised development SQL Servers.

Specifically this is done to ensure:

Patch level consistency between development servers and production
Ability to prove and validate any patches on the above
Data security; only data on the development servers gets used for development
Recoverability; data is recoverable and still backed up
Collation differences that can cause problems when moved to production

To me all these arguments are particularly invalid, with perhaps the exception of the patching ones; but if a database on a local machine is purely used for development activities, and not testing, then the patching would be proven out when an application progressed through Test / UAT etc to Production.

Collation does not appear to be a valid reason, as if this was a concern for the database it should be set when it's created anyway. Only SharePoint and SCCM have issues around this as far as I know ;)

Now, assuming it's ONLY for development, and the database will not be "moved" to production and the only movements would be:

Scripts that created the database being generated for deployment to production
Backups from "production" third party systems being restored and truncated where appropriate for validation and development

Can anyone see any issues? Am I missing something?

I guess one of the biggest concerns would be the ability for local db instances coming out of date, but thats a software management issue, not a DBA one IMO.

To me all these arguments are particularly invalid

Ok. But they aren't to the rest of us. Why?

Patch level consistency between development servers and production

the patching would be proven out when an application progressed through Test

Patching can fix stability and data corruption issues which would otherwise plague developers. It should be done on development machines regardless.

Data security; only data on the development servers gets used for development

It's useful to separate "what should be" from "what is". Developers end up with sensitive (not necessarily PII, but not free either) data on their databases. It happens.

Recoverability; data is recoverable and still backed up

Super important.

Collation differences that can cause problems when moved to production

Only SharePoint and SCCM have issues around this

Anything which uses a temp table will have this issue. It's extremely common. You never notice it because most people go for a standard collation in the first place.

assuming it's ONLY for development, and the database will not be "moved" to production

Why would we assume that? Things often go from development to production. How else do you get production populated for the very first time? Pure scripts? Not necessarily when the application has been in pre-production for some time.

I guess one of the biggest concerns would be the ability for local db instances coming out of date, but thats a software management issue, not a DBA one IMO.

You simply need to issue a statement about what you do and don't support and why.
LocalDB is a core part of SSDT now and unavoidable. However it's not remotely accessible and doesn't have a scheduling component (Express has similar issues). Hence it's not generally supported by DBAs in regards to backups, maintenance, and integrity checks.
But consolidating to centralised development servers still makes sense. And now that Developer Edition is free, it's even easier to justify having more of them.


MySQL: display only the dates of the days that fall on a Sat. or Sun. between two given dates? How can I get all the dates of the Saturdays and Sundays that fall between two given dates using MySQL?

Startdate = 2020-04-08
Enddate = 2020-04-21

The answer must be:

2020-04-08
2020-04-14
2020-04-15
2020-04-21

I'm a newbie to MySQL andI tried some queries that I got from the internet that show all dates between two dates now I wish to narrow those dates down to only Sat. and Sun.

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2020-04-08' and '2020-04-21

Your query does indeed generate dates - but do you really need to generate 100,000 days between 1970 and 2243-10-16 when all you need are dates from next month - see the fiddle here?

Getting code from the internet can be great - most of us do it all the time (it's the whole raison d'être of dba.stackexchange and all of the SE sites), but we have to be careful that a general solution is really suitable for our needs.

On my own laptop I profiled your query using the technique outlined in the MySQL documentation here.

Your query spends 0.173519 seconds executing.

If it's changed to this (commented out 3 lines):

SELECT selected_date, DAYNAME(selected_date) AS "Weekend dates"
FROM
(
  SELECT adddate(DATE(NOW(0)), t1*10 + t0) AS selected_date
  FROM
  (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
  (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1
--  (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2
 -- (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 -- (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) tab
WHERE
  (selected_date BETWEEN '2020-04-08' AND '2020-04-21')
  AND (DAYOFWEEK(selected_date) = 1 OR DAYOFWEEK(selected_date) = 7)
ORDER BY selected_date;

during which only 100 dates are generated by the inner query, only 0.000212 seconds are spent executing - almost 1000 (818) times faster. May not seem like much, but if you have (don't know your circumstances) many of these queries hitting the server, this could add up! In any case, it's obviously better to reduce your processing where reasonably possible.

The result is:

selected_date   Weekend dates
   2020-04-11        Saturday
   2020-04-12          Sunday
   2020-04-18        Saturday
   2020-04-19          Sunday

whereas you say that the data you say you want is:

2020-04-08
2020-04-14
2020-04-15
2020-04-21

The days you specify fall on Sat. and Sun. in March of 2020 and not April. It's not really important in any case - you can just change your query's BETWEEN clause to cope with any date changes.

Again, the internet is great, but you can't beat the documentation! In particular, if you are sticking with MySQL, it's worthwhile getting to know its functionality in detail and I would recommend that you bookmark these two pages - MySQL's date and time functions and MySQL's string functions.

Finally, I would suggest that you upgrade to MySQL version 8 - it underwent a raft of very powerful enhancements that are (IMHO) essential in any dev/DBA's toolkit. Analytic - aka Window - functions, Common Table Expressions, Regular Expression enhancements inter alia  you can check them out here.

p.s. welcome  to the forum!


How much RAM I should assign to SQL Server 2008 running on W2K3 I'm planning an installation for SQL 2008 over Windows 2003.  The server has 16g RAM. How much RAM I should assign to SQL server?

12GB, leave 4GB for the OS.

This 12GB is for the various caches. The binaries and various out-of-process calls will use the 4GB, along with the OS.


Always On Availability Groups on Windows 2012 Standard I am trying to enable the Always On Availability Groups feature of SQL Server 2012 Enterprise Edition on Windows Server Standard Edition 2012, but it says the feature requires X64 Enterprise Edition of SQL Server 2012 or Windows 2008 Enterprise edition or above.

I already have Windows Standard 2012 and SQL Server 2012 Enterprise Edition, why can't I enable this feature?

Per the information you provided from SELECT @@VERSION you are running the x86 version of SQL Server 2012. It would appear that the OS and SQL Server architecture must match in order for Availability Groups to function.

This means that you would either need to change your SQL Server installation to the x64, or go to a prior version of Windows (2008) and install the x86 version of that.

My recommendation would be to adjust your existing install and make it x64. This provides many enhancements such as not needing to mess with things like PAE and AWE when using more than 4GB of memory.


What permission should be enabled to create user? What permission do i need to enable to a user to be able to create other user's ,role's .
I tried giving him the role db_securityadmin still didn't work.

Requires ALTER ANY LOGIN or ALTER LOGIN permission on the server and ALTER ANY USER permission on the database.


Why is Log File Auto Grow sometimes performed by my application user and sometimes by sa? Many database tasks (including Data File Auto Grow, Log File Auto Shrink, and Data File Auto Shrink) are completed only by the sa user, but Log File Auto Grow seems to be associated with both sa and my application user via .Net SqlClient Data Provider. Is there a reason for this?

Whichever transaction requires additional log space will grow the log file within that transaction, so it can complete.

So if userA is performing a bulk load of data and uses up all available space in the log file, you will see a growth event for the log being called by userA.


primary key constraint Sorting Is having a field in primary key constraint optimizes sorting of the table ?

Here's the schema of the table:

CREATE TABLE Persons (
Field1 long NOT NULL,
Field2 long NOT NULL,
Field3 long NOT NULL,
Field4  long NOT NULL,
Filed5 long NOT NULL,
CONSTRAINT PK_Person PRIMARY KEY (Field1 Asc,Field2 Asc,Field3 Asc,Field4 Asc));

The sorting query: Select * from Persons order by Field4

The field4 in primary key constraint doesn't provide any uniqueness with respect to values.

The only reason it's added is to optimize the sorting on field4, since sorting on a column that does have an clustered index helps in optimization.

In short, Is having the field4 in primary key constraint helping with the optimization of the above sorting query, even though the primary key constraint is created using ascending order of field1 then field2 then field3 and the field4 ?

No, it won't help. If Field4 were the first column, then it would have helped. Like asking if going through a telephone directory/book will help you sort peoples names by firstname. :-)

Edit: Erik posted one example where this index can help. Requires you to return all of the rows for a certain Field4 value (i.e., equal-comparison on all but Field4).


How to tune the query on all_constraints in Oracle 12c? How to tune the following query, as it's taking around 8 seconds:

select constraint_name,table_name
                from  all_constraints
         where  r_constraint_name in
                               (select  constraint_name
                                  from  all_constraints
                                where table_name='SUPPLIER');

SUPPLIER is an example table name.

Often the RULE-hint helps when querying dictionary views.

select /*+ RULE */ constraint_name,table_name
                from  all_constraints
         where  r_constraint_name in
                               (select  constraint_name
                                  from  all_constraints
                                where table_name='SUPPLIER');

But your query is not precise. Objects have an owner, too.

select /*+ RULE */ owner, constraint_name,table_name
                from  all_constraints
         where  (r_owner, r_constraint_name) in
                               (select  owner,constraint_name
                                  from  all_constraints
                                where table_name='SUPPLIER'
                                      and owner='SOMEONE');

But this query can be formulated as a join.

select /*+ RULE +*/ ref.owner, ref.constraint_name,ref.table_name
  from  all_constraints ref, all_constraints cons
  where ref.r_constraint_name=cons.constraint_name
    and ref.r_owner=cons.owner
    and cons.owner='&OWNER'
    and cons.table_name='&TABLE';

If you prefer the modern JOIN syntax

select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  from  all_constraints ref JOIN all_constraints cons
    on (ref.r_owner=cons.owner and ref.r_constraint_name=cons.constraint_name)
  where
  cons.owner='&OWNER'
  and cons.table_name='&TABLE';

here are some timing results, with and without RULE hint on a 12c database (12.1.0.2.0)

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.44
SQL> select ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:03.95
SQL> select ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.00
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.02
SQL> select ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:03.94
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.27
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.16

I also gathered statistics  on the SYS objects using

SQL> exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', -
> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
> method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:03:41.58

but that didn't help.

On 11g systems (11.2.0.4.0) the query without hint performs far better, but even there the hint accelerates the query

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select  ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.65
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select  ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.66
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.18
SQL>


Filtered indices: Why include the filtered-on field? This just drives me mad.

Consider a simple table with irrelevant columns removed:

create table boxes (
  row_id int not null identity(1,1) primary key,
  location varchar(15) null,
  dismantled bit not null default 0,
  rank int not null default 0
  /* irrelevant columns, text and numbers */
)

For a certain important query, I want a filtered index:

create nonclustered index anindex ON boxes (rank)
where (dismantled=0 and location is null)

This creates an index that contains about 150 records out of about 150k. Which is fine.

Now we query the table:

select top (1) row_id
from boxes
where dismantled = 0 and location is null
order by rank;

The execution plan is weird. Index scan on anindex accompanied by a bookmark lookup using the seeked row_id to confirm that location is null. Records that pass the lookup are selected.

Why on Earth? location is null by the very definition of the used index, is it not?

Now, if I do a stupid thing and include location in the filtered index...

create nonclustered index anindex ON boxes (location, rank)
where (dismantled=0 and location is null)

Then all of a sudden the execution plan is great, index seek on anindex with no lookups.

This is just one example, I stumble upon this issue every time I want to use a filtered index. Every time I end up forced to include the useless fields, upon which the index is filtered, to the list of indexed fields, and only then the server uses the index properly; otherwise, it either scans it or ignores it completely.

What gives? Is it a recommended practice to include the useless fields?

This has been previously raised on Connect and Closed as "Won't Fix".

  We'll close this because it seems a narrow scenario and we don't see
  doing anything about it in the forseeable future.

That's quite disappointing IMO. Perhaps upvote and comment that item.

Edit: BOL has an example of using WHERE EndDate IS NOT NULL in a filtered index and that does not do a key lookup but replacing IS NOT NULL with IS NULL does lead to a lookup so it appears as though sometimes it is avoided...


Add column to a table for every shema in an oracle db 11g Our oracle db has various users with same schema. I would like to add a column to one table in those schemas. The same column to same table in every schema. I know that I can do that manually, like that

alter table user1.EMER_COMP add TELEFONE  varchar2(20) NULL;

alter table user2.EMER_COMP add TELEFONE  varchar2(20) NULL;

...

Is there an easier way to do this?

begin
  for t in (select * from dba_tables where table_name = 'EMER_COMP')
  loop
    execute immediate 'alter table "' || t.owner || '"."' || t.table_name || '" add TELEFONE varchar2(20) NULL';
  end loop;
end;
/


Shipping big WAL archives to hot standby I'm looking to extend my PostgreSQL 9.4 master with a few slaves in hot standby read-only for load balancing.

The idea would be to update the slaves only at defined times (once every 24/48 hours) to avoid migration issues with the application server code and also because the "freshness" of the slaves is not so important.

What would be the effect of suddenly introducing a 1-2 GB of WAL archives to the WAL restore folder on the slave? Would there be a big performance effect on the incoming queries to the slave? Would the slave be available for queries while the WAL logs are restored into the DB?

Alternatively, I've been thinking about copying the whole data folder over from the master with rsync. The negatives I see here are that the required disk space on the slave is 2 x the data folder (extra non-running copy needed to do rsync on), and that the DB server on the slaves has to be restarted to switch to the new data folder.

First, in case there's an implicit misconception here: Note that the restore is not atomic. Your queries on the replica will see the intermediate states between the prior and new master state. It'll happen faster than it did on the master - assuming the replica replays reasonably fast - but clients will still see it and there's no way around that.

If you're trying to give clients an apparently atomic switch between old and new states you can't do it just by copying a bunch of WAL over.

  What would be the effect of suddenly introducing a 1-2 GB of WAL archives to the WAL restore folder on the slave?

Minimal. The only significant effect will be the disk I/O of writing it as you copy it.

  Would there be a big performance effect on the incoming queries to the slave?

Mild to moderate, probably mild. It'll have more impact than streaming them continuously would since the replica will be running restore flat-out so it'll churn the disk, kernel buffer cache and shared_buffers more.

Hard to say in detail, depends on workload, RAM, disk subsystem, etc.

  Would the slave be available for queries while the WAL logs are restored into the DB?

Yes.

Another alternative you may not have considered. It still needs 2x the disk, but:

Run pgbouncer in front of the replica.
pg_basebackup the replica locally to a temporary datadir. Yes, you can take a pg_basebackup from a replica.
Copy the WAL to the copied replica.
Start it, wait for it to catch up.
When it's caught up, change pgbouncer's configuration to point to the  new DB and sighup it to get it to reload its configuration.
Shut down the old replica

... and repeat, switching between the two datadirs.


How can I get a single row on a inner join not to double my sum I have two tables (DEP_DEZ & CAR_EXP):

ID  FK_ID_CHESTIONAR    TOTAL_E TOTAL_F TOTAL_STOC
261 644                 87      152     48

ID  MASA_TOTALA FK_ID_CHESTIONAR
405 20          644
406 45          644

if I make an:

SELECT CHT.ID,
       SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC)
       FROM CHEST cht
            INNER JOIN DEP_DEZ
               ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
   GROUP BY CHT.ID

The result is 644 and 287 (87+152+48). And it is ok, but if I make:

SELECT CHT.ID,
       SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC),
       SUM(CAR_EXP.MASA_TOTALA),
       FROM CHEST cht
            INNER JOIN DEP_DEZ
               ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
            INNER JOIN CAR_EXP
               ON CAR_EXP.FK_ID_CHESTIONAR = CHT.ID
   GROUP BY CHT.ID

the result is 644 and 574 (287*2 - not ok) and 65 (45+20 - ok)

How can I make not to double my "287" in sum?

You could use multiple SELECT Statements, like this:

SELECT CHT.ID,
   (SELECT SUM(TOTAL_E + TOTAL_F + TOTAL_STOC)
   FROM DEP_DEZ WHERE FK_ID_CHESTIONAR = cht.ID),
   (SELECT SUM(MASA_TOTALA) FROM CAR_EXP WHERE FK_ID_CHESTIONAR = CHT.ID)
   FROM CHEST cht
GROUP BY CHT.ID