I have 2 queries with only one difference that I want to merge in just one query I have two queries in MySQL that are basically the same, where the only difference between them is a min() or max(), I would like to know if it is feasible to join the two.

I believe that the combination of the two will give me again of significant disengagement and less rework.

My goal is to know the "valor_bruto_atual" from the first day and the last day.

First query:

SELECT
    titulo_id as titulo_id,
    SUM(valor_bruto_atual) AS valor_max,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
WHERE
    user_id = 1
        AND ativos_extratos.data_import_id IN (SELECT
            MAX(ID) as max_id
        FROM
            data_imports
        WHERE
            data_import BETWEEN '2018-11-01' AND '2018-12-10'
        GROUP BY week(data_import))
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber
ORDER BY data_import ASC , titulos.nome_titulo ASC

Second query:

SELECT
    titulo_id as titulo_id,
    SUM(valor_bruto_atual) AS valor_min,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
WHERE
    user_id = 1
        AND ativos_extratos.data_import_id IN (SELECT
            MIN(ID) as min_id
        FROM
            data_imports
        WHERE
            data_import BETWEEN '2018-11-01' AND '2018-12-10'
        GROUP BY week(data_import))
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber
ORDER BY data_import ASC , titulos.nome_titulo ASC

The only change was the fancy name of the column and the min() or max() function inside the where condition.

It will be something close to

SELECT
    titulo_id as titulo_id,
-- calculate both SUMs, select values for each dependent by what value it matches to
    SUM(CASE WHEN ativos_extratos.data_import_id = borders.min_id THEN valor_bruto_atual END) AS valor_min,
    SUM(CASE WHEN ativos_extratos.data_import_id = borders.max_id THEN valor_bruto_atual END) AS valor_max,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
-- moved subquery and condition by it from WHERE
    INNER JOIN ( SELECT
                    MIN(ID) as min_id,
                    MAX(ID) as max_id
                 FROM
                    data_imports
                 WHERE
                    data_import BETWEEN '2018-11-01' AND '2018-12-10'
                 GROUP BY week(data_import)
               ) borders ON ativos_extratos.data_import_id IN (borders.min_id, borders.max_id)
WHERE
    user_id = 1
-- removed subquery and condition were there
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber

I am too lazy to debug - do it by yourself.


How to use character classes in a Postgres regex select? I'm trying to create a query that selects strings that contain the number 21, without hitting strings such as 210 or 121. My efforts below have shown my progress, in that I've managed to apply a constraint for the beginning of the word, but I'm stumped in terms of providing delimiter at the end.
I would like to match 21 and 21X where X is a nondigit character.
Normally I'd use [] match both situations where there is a nondigit character or the end of the string, but I'm stymied on this.

I can filter based on the start of the word and character classes work:

# SELECT "buildings"."street_name" FROM "buildings"  WHERE (street_name ~ '\m21[s0]');
 street_name
-------------
 21st
 210th

And here I can successfully use the nondigit (\D) constraint:

 # SELECT "buildings"."street_name" FROM "buildings"  WHERE (street_name ~ '\m21\D');
 street_name
-------------
21st

But when I try to use the nondigit constraint to define a class i.e. \m21[\D] (in an effort to match the string '21') it will fail.

How can I define a regex that matches my target (in the case 21) with a non-digit suffix or string terminator ?

SELECT "buildings"."street_name"
FROM "buildings"
WHERE (street_name ~ '\m21\D*\M');

Explanation:

\m: beginning of word
21: the string to match
\D*: zero or more non-digit characters
\M: end of word
m21\D*\M: a word that starts with 21 followed by any number of non-digits up to the end of the word

If you wanted to limit the query to only ordinal street names (e.g. "21st") and avoid matching things like "21A", you could instead use \m14(st|nd|rd|th)?\M for the pattern.


Strange Backup and Alerting Issue I received alerts from one of my database servers over the weekend that was somewhat disconcerting.  There was an alert for each single database within the maintenance plan.

A full backup has never been taken for database *SeanDatabase*

I've had a maintenance plan in place for years that has run without fail and the associate logs backup this claim.  Also, the backups are time stamped and in the place expected.

I did a restore in a test environment and ran a consistency check without any errors.

I'm not sure that I really see this as a true error or cause for concern considering the aforementioned evidence.  So, maybe this is just an anomalous event?  Either way, I'd like to poke around and a assuage my sensibilities.

Has anyone ran into this situation before?  If so, what did you do to investigate?

Virtualized system on VMWare
SQL ver.  10.50.6220
Windows Server 2008R2 w/SP1

You can get an idea of what backups have been taken, and when, by looking at the result of this query:

DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history

SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSize = bs.compressed_backup_size
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType =
        CASE bs.type
            WHEN 'D' THEN 'Database'
            WHEN 'L' THEN 'Log'
            ELSE '[unknown]'
        END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.database_name = @DBName
    OR @DBName IS NULL
ORDER BY bs.backup_start_date DESC;

By default, this will show you all backup activity that has occurred on the current instance since the last time msdb was created/restored, assuming backup history has not been deleted.


MariaDB Galera Cluster (master-master) does sync Tables but no data I'm currently experiencing some sync-issues on my MariaDB Galera Cluster which is a master-master scenario. I've got everything set up properly and the cluster is syncing the DDL changes flawless.

The output of the DB-Parameters seems right because every relevant variable is correct (both nodes are: ready, have the same sync-state and no errors are detected).

I made the check by creating a table and checking if it's syncing to the other node - after that i did it backwards. Everything works except the syncing of the data from the first node to the second.

Is there a way to initiate tis sync-process manually? Might there be a log-file which i oversaw?

Best Regards
k

Galera doesn't support but the InnoDB ENGINE (and XtraDB, which is essentially the same). While there is experimental support for MyISAM, by activating wsrep_replicate_myisam, that is not guarantee to work, as it is not transactional.

The DDL that you executed worked because they do not use the ROW format, but STATEMENT-based replication.

While we could hack and maybe make it work, it would work poorly. I highly recommend you to change the tables to InnoDB: ALTER TABLE your_table ENGINE=InnoDB, FORCE; After that, any change will be synced automatically as desired.

References:

MariaDB Galera Cluster limitations: https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/
Aria is not supported: https://groups.google.com/forum/#!msg/codership-team/-VTXuYVnbN0/d-v4NVTQDNwJ


Can I connect to SQL Server 2008 R2 database from SSIS 2012? I need to redevelop an ETL process and I'm evaluating options.

I'm running SQL Server 2008 R2 database, and SSIS 2008 will take too long to develop and test the required packages. I understand that SSIS 2012 is significantly improved and will help me develop the packages far more efficiently, but I don't know about the versioning considerations.

Can I run an SSIS 2012 package on a SQL Server 2008 R2 server? Or can I run it on a SQL Server 2012 database and connect to SQL Server 2008 R2 databases? Are there any issues that I'm going to come across connecting in this way?

You would need to save the package to a SQL 2012 instance and run it from there. It will connect to R2 just fine.

This is because a SQL Server 2012 SSIS package cannot be saved to a lower version of SQL Server or run from a lower version of SQL Server. That said, an SSIS package (in any version of SQL Server) can connect to any data source that is ODBC or OLE DB compliant. So an SSIS Pacakge created in SQL Server 2012 can connect to an Oracle Database as a connection, it can connect to a text file as a connection or it can connect to SQL Server 2008 R2 as a connection.

Also the benefit of keeping your development in SSIS 2012 is that you don't have to worry about upgrading that package when you finally move all of your infrastructure to SQL Server 2012. You can also take advantage of the new features in SSIS 2012, as you rightly pointed out.


How do I Create Tablespace in Oracle 11g? I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used:

SQL> create Tablespace HRMT
2 datafile
3 size
4 ;
size
*
ERROR at line 3:
ORA-02236: invalid file name

My book and all websites that I have been to suggest that I specify the file path for the DATAFILE, and when I specify a path, I get another error message.

SQL> create tablespace vania
2 datafile 'home/oracle/hait/'
3 size 10M
4 ;
create tablespace vania
*
ERROR at line 1:
ORA-01119: error in creating database file 'home/oracle/hait/'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory

Do I need to create the datafile first before I can create the TABLESPACE? Are there pre-existing datafiles in the database that I can use? What would be the proper syntax to create a tablespace and/or a datafile? How and where do I create a .dbf file to create the tablespace?

The answer to your question can be found in the SQL Language Reference (excerpt follows).

  Creating Basic Tablespaces: Examples

  This statement creates a tablespace
  named tbs_01 with one data file:

  CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dbf' SIZE 40M     ONLINE;

It sound like you are new to Oracle databases.  Oracle provides a wealth of documentation at http://docs.oracle.com/en/database.  Specifically, I recommend reading their excellent introduction to the database called the Concepts Guide.


Partition an unpartitioned primary key index to become locally partitioned by primary key I tried:

alter index clave_primaria_de_b local;

and I read:

ALTER INDEX

But I don't know how to do it.

You cannot convert a global index into a local index with an ALTER INDEX command. You need to drop the index and recreate it using the LOCAL keyword.


PowerShell script to send an email with SQL results in HTML format Does anyone have a PowerShell script to query results from a SQL Server database and send it out as an email in HTML format?

Following is a script I use to send query results in an HTML-formatted email.  Basically pipe the result of invoke-sqlcmd to ConvertTo-Html and then to Out-String, and then massage it a bit to get the table to display nicely.  If you don't add the padding style, everything will run together.

Send-MailMessage example below is for unathenticated, so if you have to authenticate to your SMTP server you'll need to fix that up

$Alerts = invoke-sqlcmd -ServerInstance dbserver -Database dbname -Query "SELECT * FROM some_table" | SELECT Severity, ServerName, Heading | ConvertTo-Html | Out-String
$Alerts = $Alerts.Replace("", "")
Send-MailMessage -To me@mycompany.com -From me@mycompany.com -BodyAsHtml $Alerts -Subject "Alerts" -SmtpServer "smtp.mycompany.com"


Is it Possible that a variable name changes in a stored procedure after a restore? I often restore my Testing database from Production db, but this time I noticed something odd:
we have a stored procedure called (USP_AddTS), one of its variable names have slightly a different name in both databases (which I capitalized in example below):

USE [ProductionDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
 (  @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),
@systemID tinyint,
@RELEASEDATE smalldatetime,
…………
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..

values (@tsNum,@tsVersion,@tsTitle,@releasedate,….

2)  USE [TestingDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
(   @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),
@systemID tinyint,
@TSRELEASEDATE smalldatetime,
…………
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..

values (@tsNum,@tsVersion,@tsTitle,@TSreleasedate,….

We recently added a new column named (TransmittalReleaseDate) to the table (TransmittalSheet), so we updated the (USP_AddTS) to include the new variable (@ReleaseDate) like I’m showing above , but for some reason after I restored my Testing DB from a recent  copy from ProductionDB, the variable name got updated to  @TSReleaseDate only in Testing DB, unlike the ProductionDB that shows it as @ReleaseDate, is this possible or am I mistaken?
There was another variable line from the declared list above that I commented out from the ProductionDB and I made sure it was saved, but from the TestingDB was not commented out and still available.

I will keep an eye when I refresh or restore again, as I may incorrectly chose the prior date’s backup copy to restore from, but I thought of asking here too.
I'm using SQL Server 2008R2, Thanks.

Its not possible for the variable name to change by itself, its likely you restored from a different (older? newer?) backup than what you thought. You can check sys.objects for the stored procedure create/modified dates and compare across the 2 databases.


GDPR compliant multitenant MYSQL DB I wonder what are the GDPR constraint on a MySQL DB for a multitenant SaaS application.
While a solution with a single DB per each multitenant customer and restricted  access will probably be fine, what about a single DB?

How do I guarantee a proper insulation of data for single tenants?
Pure applicative solutions seems weak to me (...where tenantID=xx for each query).

Will I be forced to create restricted views on each table or for most queries?
How well will MySQL 6.7 handle this? I've read about indexing views and performance, I'm not sure what will happen to my application if I switch every table to a view.

Have new versions of MySQL improved? Should I use a db proxy like MaxScale?
Or maybe it's reason enough to switch to postgres or other db?

Regardless of how you solve the problem, you have to go through some sort of "router".  And that may be the least secure part.

Separate computer for each customer -- you need to tell user which machine to go to.

Separate process or API or VM on same machine -- user must log into the appropriate path.

Separate databases (one per customer) on single MySQL server -- either API or MySQL could handle security.

Single database, but separate rows in the tables -- Now the API must take responsibility for security.  That is, the user must not have direct access to MySQL, only indirect through an API layer.

Each has "privacy by design".


Cyclic relationships in ER Diagrams I am trying to draw an generic entity-relationship diagram (ERD) to model a software object (Let's say a XML file). This XML file has references to other XML files. Also, if a XML file (say "file-A") is referred by another XML file (say "file-B"), then "file-B" cannot referred by other XML files.

If I need to model those relationships in an ERD, the source entity and the target entity becomes same. I want to know whether this is acceptable or not? Because, if it is acceptable, specifying cardinality becomes an issue as different readers interpret the cardinality in different ways:

Eg - an XML file can have references to zero or more XML files
Eg - an xml file can have only one reference to another XML file

Yes it is acceptable.  In ER modeling, this is known as a reflexive relationship.  In a relational model, it's implemented by a foreign key that references another row in the same table.

The simplest example is a table of employees, where the PK is EmployeeID (or just ID) and there is an FK called SupervisorID.  This kind of thing is done all the time.


Data from more than 1 query - possible union needed? I have a query (Query A) that pulls data from my Customer table.
I have another query (Query B) that pulls data from a list of records associated with various customers.
-   Every customer on Query B exists on Query A….but not every customer on Query A has a record on Query B

What I’m trying to do is to take 1 field from Query B, and pull it into Query A….but when I do that, it essentially limits the entire scope of Query A to only those customers present on Query B.

Is there a way that I can have Query A display it’s full list of customers, with a column next to it that displays data from Query B only if there is data on Query B?

I hope I’m making sense!

You need to use a LEFT JOIN to get all of QueryA  and whatever records exist for QueryB

SELECT * FROM QueryA as A LEFT JOIN QueryB as B on A.columnid=B.columnid


Using EXCEPT with NOT EXISTS SELECT DISTINCT cname
FROM account AS cust_account
WHERE NOT EXISTS ( SELECT type
                   FROM account
                   EXCEPT
                   SELECT type
                   FROM account
                   WHERE account.cname = cust_account.cname )

Can someone please explain step-by-step how the query produces the result required by the question? I am just learning about NOT EXISTS and am not sure what values it takes from the parenthesis and where the WHERE clause belongs with.

This is sample table account

There are three separate parts to the query. I'll label them so I can reference each more clearly:

SELECT DISTINCT cname             -- Part A
FROM account AS cust_account
WHERE NOT EXISTS ( SELECT type    -- Part B
                   FROM account
                   EXCEPT
                   SELECT type    -- Part C
                   FROM account
                   WHERE account.cname = cust_account.cname )

First the inner query. Part C lists all type values for account rows owned by the customer currently being considered by the outer query (Part A). Conceptually you can think of the DBMS reading all rows that match the outer query then stepping through them one at a time. For each outer row it executes the inner query. (In reality likely something more time-efficient happens but it will be logically equivalent to this.)

Part C is sometimes called a correlated sub-query because it is matched (correlated) with a value coming from the outer query. In this case it is matching on cname. It is the WHERE clause that tells the DBMS to match Part C to Part A. cust_account is an alias defined in the outer query. Because a single table - account - is used three times in the statement the alias allows us to specify exactly which of these three we want to reference. It is the alias which allows us to say "match Part C to Part A."

Part B lists all type values from table account. This part has no WHERE so it lists all accounts for all users, not just the one currently being considered by the outer query. This part is not correlated.

The EXCEPT takes the results of the query above it (Part B) and removes those rows which occur in the query below it (Part C). Since Part B is "all types that are in the database" and Part C is "all types the current cname owns" then "B except C" is "all types except those which the current cname owns." If we have a list of all types, and we remove those which the cname owns, what is left is those types which the cname does not own. So if the inner query has any rows (for the current cname) we know there is at least one type of account that cname does not own.

EXISTS() returns true if the inner query has row(s). So NOT EXISTS() returns true if the inner query is empty. As we have just shown, it will be empty if the current cname owns all types of accounts. So the outer query lists all cname which have all types of account - as requested in the problem.


Divide, round and replace in one select statement - tranformation from bytes to GB in SQL Server I have a database which has me a value, a sharesize in bytes and a sharename. Which is pretty accurate but not useful for my enduser. Therefore I transformed the value to GB.
Which worked great by using a simple divide.
lets assume my DB consists of two columns. Sharename and Sharesize.
Sharename | Sharesize

Share 1   |71685107549

Now I run this:
SELECT TOP (1000)
      [Sharename]
      ,[ShareSize]
      ,(ShareSize / 1e+9) SharesizeGB
  FROM [mydb].[dbo].[myshares]

The output is:
ShareSize   SharesizeGB
71685107549 71,685107549

Now I need to replace the "," with a "dot" and round the result to just have two digits after the dot.
Is that possible using only a select statement ?
For my example its not needed to divide by 1024.

The query returns a float data type, which is a binary structure that has no comma or dot decimal separator. It is the rendering application that converts the value into a string format for display purposes, which can honor the client's regional settings if programmed to do so.
Although you could change the T-SQL to return a formatted string like the example below, be aware client sorting will use string instead of numeric rules. Note this example explicitly specifies culture en_US, which uses a dot decimal separator, to override the current session language setting. Also added ORDER BY for deterministic results with the TOP clause.
SELECT TOP (1000)
      [Sharename]
      ,[ShareSize]
      ,FORMAT(ShareSize / 1e+9, '#,##0.000000000000', 'en-US') SharesizeGB
FROM [mydb].[dbo].[myshares]
ORDER BY (ShareSize / 1e+9) DESC;


Strange Log Shipping Error Message Environment and Server Details:

12.0.4457
Windows Server 2012 R2 standard
VMWare Virtual Machine
Dell Equal Logic SAN

SQL Error Log Message:

  The log shipping secondary database ServerName.DatabaseName has restore threshold of 45 minutes and is out of sync. No restore was performed for 34089 minutes. Restored latency is 2 minutes. Check agent log and log shipping monitor information.

I've verified that the logs are being restored on the log shipping secondary, matched up the LSNs, also, I have the Log Shipping Monitor from Red Gate that verifies my logs are up to date. Further, the aforementioned error message is for every database in the instance.

I've built a similar setup in a lab environment and I get the same error messages.

What gives, anyone have any insight?
Can I safely ignore these messages since I know the logs are being
backed up, copied, and restored?

Having 10s of thousands of error messages per week makes it pretty difficult to peruse and check other possible issues

I can't comment under the question but i can tell you what i did. I had a similar issue like that due to a break in the restore chain. I tried manually editing the monitor tables to "hack" the replaying of restore logs so that the Alert Job won't throw those alerts but i ended up digging a bigger hole for myself.
Note that even though you are seeing the LS_Restore job giving success messages that doesn't mean it is actually doing a Restore. Click on the View History and look for something similar to this: Number of log backup files restored: 0. (Note that this could mean there's no data to restore or there's data to restore but the log file next in line to restore is missing). I was getting a false positive (wrong successful LS_Restore) but the log files restored were always 0 even though it couldn't find the log file needed (you would see a message saying: Can't find previous log shipping file to be restored).

As a test ensure that there's committed activity in the production database then go to the DR and check the view history for LS_Restore. You want to see something like: Number of log backup files copied: 1. If for 6 restores you don't see a change from "0" files restored then i suggest:

Restore your Secondary database using a full + differential back up of the production
OR
Re-initialize the log shipping process (delete current log shipping setup and start over the process)


I have mistakenly deleted Datafiles. In my production server I have mistakenly deleted datafiles. When I am trying to open the database I am getting an error. The files are not important but the database is not opening up.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>  alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/mars/oradata/MARS/system01.dbf'

I have tried plenty of ways to recover but it is not opening.
LIKE

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf'

Since you don't care about the data, you can try the following which will offline and drop the datafile in question allowing you to open the database.

SQL> startup
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2255904 bytes
Variable Size            1275069408 bytes
Database Buffers          436207616 bytes
Redo Buffers                6795264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\ORCL\TRASHED01.DBF'

SQL> alter database datafile 6 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL>


What is a bucket in database? I was implementing a code that involves a database and buckets. Just wanted to ask what is bucket and can someone describe it with an example?

Thanks!

In Oracle, one of the fundamental concepts for the CBO (Cost Based Optimizer) is that of the bucket.

When collecting statistics about the distribution of data in a table (v. important for optimising the query plan) Oracle can split the data into buckets (or bands or segments) for the purpose of constructing histograms to determine the distribution of the data.

It can't really be better explained than here:

  Data skew in a column can make it difficult for the optimizer to
  accurately estimate the cardinality of an operation. Without a
  histogram it will assume an even distribution amongst the distinct
  values for the column.

  Take the case of a Yes/No flag for active
  records in a table. There may be 1 million rows in the table with only
  100 being marked as active. The optimizer would assume half are marked
  as "Yes" and half as "No", which would be a really bad assumption in
  this case. Histograms describe the distribution of data in a column,
  which helps identify data skew and allows the optimizer to make a
  better decision.

Checkout the links on the same page - oracle-base is a super site for all things Oracle by Tim Hall who really knows his stuff!

See here for a bit more detail, or Jonathan Lewis' site for in-depth information. Lewis is the CBO man - he literally "wrote the book" (Cost-based Oracle Fundamentals) about this (not bedtime reading! :-) ).


Add column with default in postgresql without table level lock Have such a problem - table with over 20mln rows.

When i add new column with default - postgresql lock table for over 40 minutes so my application stop working for this time.

So instead of

ALTER TABLE "test" ADD COLUMN "field" boolean DEFAULT True NOT NULL;

I do

ALTER TABLE "test" ADD COLUMN "field" boolean NULL;
ALTER TABLE "test" ALTER COLUMN "field" SET DEFAULT true;

after which every new row will be with true by default, so now i need to update 20mln current rows.
I update them in batches:

WITH cte AS (
SELECT id as pk
FROM "test"
WHERE  "field" is null
LIMIT  10000
)
UPDATE "test" table_
SET "field" = true
FROM   cte
WHERE  table_.id = cte.pk

after which i do

ALTER TABLE "test" ALTER COLUMN "field" SET NOT NULL;

And everything is allwright but step when i update rows is so much slow. Can you give my some advice about improving speed of update?

Currently it update 10000 in about 2 minutes.
I try decreasing size to 1000 - it was better (3.5 minutes for 10000) but it still to slow.
I also try create index before update, but it doesnt give better results (as i understand it will give better result but when huge path of table will be updated).

This problem is solved in PostgreSQL 11, which unfortunately isn't released yet at this moment. The release is planned for the third quarter of 2018.

There is a more detailed explanation in this blog post, but the short version is that in Version 11 the table won't have to be rewritten to add a default value to a column. So adding a column with a default should be pretty much instant.


MySQL 5.5 to 5.7 doesn't work, apparently this is a feature? Has anyone found a way yet of circumventing MySQL 5.7s insistence that it's Master must have a server_UUID.

I currently have multiple instances of MySQL 5.5 over various sites, and want to look at upgrading to MySQL 5.7.
To do this I was planning to set up a test-site using some MySQL 5.7 databases running as a set of master > slaves from my main site.

But this failed at the first hurdle, as MySQL 5.7 insists that the Master must have a SERVER_UUID !! Depsite the official documentation telling us we should upgrade our slave databases before we upgrade the master.

  In other words, when upgrading to MySQL 5.7, the slaves must be MySQL
  5.7 before you can upgrade the master to 5.7.

http://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.html

As it stands the only option appears to be to install, configure, and test a copy of MySQL 5.6 (which I'll never actually use) to act as an itermediary between my live system and test system. But that just introduces a whole load of additional complexity when trying to run like for like testing between 5.5. and 5.7 slaves (e.g. is it MySQL 5.7 that caused problem X, or was it MySQL 5.6 doing something on the way through etc).

I see that there is a BUG report for this, which they have since decided is Not actually a BUG, but a feature!!!

The problem is that MySQL 5.5 does not support GTIDs, but MySQL 5.7 does and have it activated by default.

As the MySQL manual suggests, you can disable GTIDs on the 5.7 slaves to perform your tests, and later, when all your servers will be upgraded to 5.7, activate GTIDs on both master and slaves.

see http://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.html for more information.

  If you are upgrading an existing replication setup from a version of
  MySQL that does not support global transaction identifiers to a
  version that does, you should not enable GTIDs on either the master or
  the slave before making sure that the setup meets all the requirements
  for GTID-based replication. For example server_uuid, which was added
  in MySQL 5.6, must exist for GTIDs to function correctly. See Section
  18.1.3.2, “Setting Up Replication Using GTIDs”, which contains information about converting existing replication setups to use
  GTID-based replication.


Create a "INTO" table with primary key Maybe for this community my problem is easy, but for me (a simple Java programmer) it's a BIG problem.

I have a Big DB with more and more data. So, the external db admin had create a job that show me in a temporary table the data that I need. But he had create the table without a primary key and when with my java project go to read this table, I obtain an error.

I can't read this table because the primary key don't exist.

Can I insert in the procedure the possibility to create an autoincremental primary key without changing the structure of this complex procedure?

This is the begin of the stored procedure code:

USE [MYDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[spSchedula_Scadenzario]
as
begin

    drop table MYDB.dbo.tmpTable

 select
  aa.*
    into MYDB.dbo.tmpTable
from (...)

Thanks in advance

Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are:

Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much prefer this method as it gives you complete control over the Table that is being created, such as including the auto-increment column and specifying that it be the Primary Key. For example:

CREATE TABLE dbo.tmpTable
(
  tmpTableID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ...
  {all columns represented by aa.* in the sample query in the Question}
);

If you can't change how / when / where the table is being created, you can always add a Column later, and when doing so, you are allowed to specify both that it be an IDENTITY column and have the Primary Key created on it. For example:

ALTER TABLE [dbo].[tmpTable]
  ADD [tmpTableID] INT NOT NULL
  IDENTITY(1, 1)
  PRIMARY KEY;

This even works if the Table already has data in it: the new IDENTITY column will be populated as expected, starting with the value specified for the seed parameter. However, there is no way to control the order in which the values are assigned (which is one of several reasons to go with option #1, if at all possible).

Additional notes:

Are you sure that you need a Primary Key and not simply an auto-incrementing / unique column? While it is typically a good idea to have a Primary Key, it is neither required nor the same thing as an auto-incrementing column. I only ask because both the title and text of this Question state that you need a Primary Key, yet you are saying in a comment on the Answer that you accepted that simply having the auto-increment column worked.
The table you are using is not actually a temporary table. Real Temporary Tables have names starting with #, or ## for Global Temporary Tables. The table you are using dbo.tmpTable is just a regular, permanent table that is prefixed with "tmp" to indicate that it is probably just for this process and not part of the data model.

If the app code doesn't need to access this "temporary" Table, and the only reference to it is within this Stored Procedure, then you might consider changing it to be a real Temporary Table, which has the advantage of being cleaned up when the process completes, in which case you wouldn't need the DROP TABLE statement.
If you will be using a permanent Table instead of a Temporary Table (in which case you need to clean it up yourself), then the DROP TABLE statement should be conditional, such that it doesn't error if the Table doesn't exist:

IF (OBJECT_ID(N'dbo.tmpTable') IS NOT NULL)
BEGIN
   DROP TABLE dbo.tmpTable;
END;

Rather than doing SELECT *, you should specify the full column list. Using * makes the process more likely to break when you add columns / fields to tables or subqueries (whatever aa is an alias for).


Does Oracle PL/SQL have a standard ASSERT routine? I'd like to use an ASSERT routine functionally similar to the one found in other languages, i.e. a construct (be it a procedure, syntax...)

ASSERT( <condition>, <msg>)

such that when the <condition> passed in the first argument is false an exception is raised with the specified <msg> descriptive message.

I know this is trivial to do by hand but I'm asking if there's a standard one provided with the DBMS.

Having to write my own one or import one from 3rdy-party packages would be impractical, since I'd need it to be completely portable and transparent to every project I'm working on.

There isn't a built-in assertion procedure in SQL or PL/SQL, so you will have to write your own.

There are two ways of going about this. You can either manually raise an exception, as discussed in this Oracle article, or you can write a wrapper for the raise_application_error procedure, which is documented in the Oracle exception handling section of the documentation.

I'll add that exceptions were designed for this kind of scenario, so you'd be better off taking your programmer hat off for a second & using your DBA hat :)


Read MS Sql query high latency w.r.t. Remote Query I'm trying to read a table in a MS SQL Server using python, specifically SQLalchemy, pymssql, and pandas.read_sql. I want to execute the query, put the results into a pandas Dataframe, and continue on with my work. The sql database I'm accessing is a part of the Wonderware product line, is composed of more than 700 tags over a time period of 2 years.

I want to be able to read a simple query such as the following:

sql_query = '''
SELECT DateTime, TagName, Value

    FROM Runtime.dbo.AnalogHistory

    WHERE
        DateTime BETWEEN '2014-05-26 00:00' AND '2014-06-26 00:00'
    AND
        TagName IN (
            'dPC503.ActualValue',
            'dPC504.ActualValue')
    AND
        wwRetrievalMode = 'Cyclic'
    AND
        wwResolution = 5000
'''

The query results in a dataframe object about 100 MB in size and takes a little under 10 min (423 s)

If my math is correct, 100 MB in 423 seconds is about 230 kB/s, which I think over an Ethernet connection is just painfully slow. The first question I have is, where is this speed going?

I posted a related question here on stackexchange.com. Someone commented on my post and told me to look into the Execution plan - I'm just a beginner and have no clue what to do with this information.

Running the query on a desktop with SSMS installed resulted in the same query runtime. After looking into it, I see that the execution plan is broken into 3 parts, heavily favoring the remote query:

SELECT - Cost: 0%
Compute Scalar - Cost: 0%
Remote Query - Cost: 100%

Opening up the Remote Query execution plan into an xml file results in this file snippet.

I'm not sure what this is telling me. The gentlemen who kindly commented on my previous question and asked/gave me some helpful questions/pointers that I hope someone here would be able to expand on. The following are from him, and my follow up questions are in italics:

Which indices are defined for the Runtime.dbo.AnalogHistory table and the execution plan for this query? How do I determine this?
I think when you executed exactly the same query a few times the vast majority of the data pages needed for the result set were already in the MS SQL Server buffer pool and because of that 100% of the cost is in the final 'Remote Query' step, where it 'Sends a sql query to another than the current sql server', otherwise it would have to read data from disk, which is usually the slowest part (unless you have an extremely slow network). So, anyway you would have to check the execution plan. Where in the execution plan is this information hiding?

Create a copy of the table into a #temporary table on the server you are running the query on, then run the query based off the temp table.

I am not an expert on why certain queries run slower over linked servers and others do not. However, if you move that data temporarily over to the server you are running the query on, it often makes it run faster.  Someone more experienced can answer why this is the case.


Getting information on users payments via subscriptions My current table db design looks like this :

- User has one subscription
- One subscription has many payments

Table subscription has a user_id, which is FK to user table.
Table payment has a subscription_id which is a FK to subscription table.

How can I count/see which users have more than one payment or at least sort them by number of payments.

This is what I tried and it doesn't look like it's doing what I want:

SELECT count(subscription.user_id) as cnt, subscription.id FROM payment
INNER JOIN subscription ON payment.subscription_id = subscription.id
INNER JOIN user ON user.id = subscription.user_id
WHERE (payment.refunded_date is null) AND (subscription.deleted_at IS NULL)
group by subscription.id
order by cnt

What is the easiest/optimal way to accomplish this?

I find that if I try to write out what my query is doing, it tends to illuminate the answer

-- for everything that is in payment
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of subscriptions
SELECT
    COUNT(subscription.user_id) as cnt
,   subscription.id
FROM
    payment
    INNER JOIN
        subscription
        ON payment.subscription_id = subscription.id
    INNER JOIN
        user
        ON user.id = subscription.user_id
WHERE
    (payment.refunded_date is null)
    AND (subscription.deleted_at IS NULL)
GROUP BY
    subscription.id
ORDER BY
    cnt

Assuming I read your query correctly, I think your issue is that you are counting how many subscriptions an ID has. You didn't specify how the results were wrong, but I'm guessing you get 1 back.

If you want to know the total count of payments, you would count that instead.

-- for everything that is in payment
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of payments
SELECT
    COUNT(payment.subscription_id) as cnt
,   subscription.id
FROM
    payment
    INNER JOIN
        subscription
        ON payment.subscription_id = subscription.id
    INNER JOIN
        user
        ON user.id = subscription.user_id
WHERE
    (payment.refunded_date is null)
    AND (subscription.deleted_at IS NULL)
GROUP BY
    subscription.id
ORDER BY
    cnt


Powershell invoke sqlcmd How is it possible to execute a query as a applicationintent=readonly using powershell?

I have an cluster alwayson environment.
I want to call a procedure with invoke sqlcmd to the readonly database (secondary). I have the listener name and the database name.

Currently Invoke-Sqlcmd does not support the ability to connect to a read-only secondary replica.

The SQL Server, and some of the PowerShell community, however started working with the Program Manager at Microsoft to get SQLPS and SSMS improvements organized. A Trello board was opened in the past month that we are showing by votes what priority we want Microsoft to take on the improvements. This has actually already gotten some things added to the SSMS March preview.

I created a card for the Application Intent functionality so go vote it up and the connect item linked.

Right now your only option is to call sqlcmd within PowerShell using the -K ReadOnly.


How to free up disk space to the OS, ideally without using SHRINKFILE? Our server admin is asking me to free up disk space on our SQL server, since the drive where the SQL datafiles reside is at 97% usage.

I have found a table that has grown out of control. It now contains over 300 million rows, so I will set a job to delete these overnight.

What are my options to then free up the disk space?
Will the deletion of those rows free up space for other tables to use?

I would like to avoid SHRINKFILE as it is not recommended.

First, I would confirm what the server admin is seeing, by running some free-space diagnostics on your SQL Server:

/* Get SQL Server Drive Usage Stats */
IF OBJECT_ID('master.sys.dm_os_volume_stats') IS NOT NULL
    SELECT vs.volume_mount_point AS Drive, vs.file_system_type AS [Type]
        ,vs.logical_volume_name AS LogicalName
        ,MAX(CAST(  1.0*vs.total_bytes  / 1073741824 AS DECIMAL(18,2)))AS[Drive Size (GB)]
        ,CAST(SUM(  1.0*size) / 128 / 1000 AS DECIMAL(18,2)) AS [SQL Size (GB)]
        ,MAX(CAST(  1.0*vs.available_bytes/1073741824 AS DECIMAL(18,2)))AS[Free Space (GB)]
        ,MIN(CAST(100.0*vs.available_bytes/vs.total_bytes AS DECIMAL(5,1)))AS[Free Space (%)]
    FROM master.sys.master_files AS f WITH (NOLOCK)
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
    GROUP BY vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name
    ORDER BY 1
    OPTION (RECOMPILE);

SELECT Drive
    ,ISNULL([ROWS], 0) + ISNULL([LOG], 0)
        + ISNULL([TempROWS], 0) + ISNULL([TempLOG], 0)
        AS 'TotalUsed (MB)'
    ,[ROWS] AS 'Data (MB)'
    ,[LOG] AS 'Logs (MB)'
    ,[TempROWS] AS 'TempData (MB)'
    ,[TempLOG] AS 'TempLogs (MB)'
FROM (
    SELECT LEFT(Physical_Name, 3) 'Drive'
        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc 'FileType'
        ,SUM(size) / 128 'SizeMB'
    FROM master.sys.master_files
    GROUP BY LEFT(Physical_Name, 3)
        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc
    ) Results
PIVOT(SUM(SizeMB) FOR FileType IN ([ROWS], [LOG], [TempROWS], [TempLOG])) pvt
ORDER BY 1
OPTION (RECOMPILE);

Then check which files are the biggest/worst on the problem drive:

/* Get Individual Database Stats */
SELECT mf.database_id 'DB_ID'
    ,DB_NAME(mf.database_id) 'DBName'
    ,d.state_desc 'DBState'
    ,d.recovery_model_desc AS RecoveryModel
    ,CASE WHEN Log_ReUse_Wait_Desc = 'NOTHING' THEN ''
          ELSE Log_ReUse_Wait_Desc END AS LogReUseWait
    ,mf.[File_ID]
    ,mf.NAME 'LogicalName'
    ,mf.type_desc 'Type'
    ,mf.Physical_Name
    ,mf.state_desc 'FileState'
    ,CAST(size / 128.0 + 0.5 AS INT) AS SizeMB
    ,CAST(max_size / 128.0 + 0.5 AS INT) AS MaxSizeMB
    ,CASE is_percent_growth
        WHEN 0 THEN CAST(growth / 128 AS VARCHAR(10)) + ' MB'
        ELSE CAST(growth AS VARCHAR(10)) + ' %' END AS 'AutoGrowth'
    ,CASE
        WHEN d.STATE <> 6 /* 6 = OFFLINE */
        AND mf.type_desc = 'ROWS'
        AND mf.database_id <> 2 /* not TempDB */
            THEN 'USE '+QUOTENAME(DB_NAME(mf.database_id))
               + ';   DBCC SHRINKFILE(' + CAST(file_id AS VARCHAR(2))
               + ',1,TRUNCATEONLY);
GO'
        ELSE '' END AS 'ShrinkTruncateOnlyCommand'
FROM [master].sys.master_files mf
LEFT JOIN [master].sys.databases d ON d.database_id = mf.database_id
WHERE 1 = 1
    AND d.STATE <> 6 /* 6 = OFFLINE */
    AND mf.database_id > 4 --user DBs only
    --AND mf.database_id <= 4 --system DBs only
ORDER BY SizeMB DESC, DB_NAME(mf.database_id), [file_id]
OPTION (RECOMPILE);

Before you go the SHRINKFILE route, you should ask yourself some higher level questions, like:

Why does the server admin need more free space on that drive?
What will happen to my DB and its data if the file takes up the whole drive and cannot grow when needed? (hint: it's Bad Stuff.)
Should the server admin add space/drives to my SQL Server box, so I don't have to SHRINKFILE right now?

Since you have found a table with data that can (hopefully) be deleted (check with the people who use that data!), go ahead and delete the data, possibly in batches if you are worried about locking the table for a long time:

DELETE FROM <table> WHERE <pick the primary keys to delete in this batch>

If there are useful rows left in your table after getting rid of the old/useless ones, I would rebuild the table's Primary Key:

USE YourDB;

SELECT s.[Name] + '.' + t.[Name] AS ObjectName
    ,'ALTER INDEX [' + i.[Name] + '] ON [' + s.[Name] + '].[' + t.[Name] + '] REBUILD'
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
    AND i.type = 1 --clustered index
    AND OBJECTPROPERTY(t.[object_id], 'IsUserTable') = 1
JOIN sys.schemas s ON s.schema_id = t.schema_id
    --AND s.[Name] = 'YourSchema'
    AND t.[Name] = 'YourTable'
ORDER BY 1

...and finally shrink the database file. I would try using the TRUNCATEONLY option first, as it's faster/safer than a normal SHRINKFILE which rearranges pages within the DB file:

/* Get Individual Database Stats */
SELECT d.Name DB
    ,mf.type_desc
    ,CASE
        WHEN d.STATE <> 6 /* 6 = OFFLINE */
        AND mf.type_desc = 'ROWS'
        AND mf.database_id <> 2 /* not TempDB */
            THEN 'USE '+QUOTENAME(DB_NAME(mf.database_id))
               + '; DBCC SHRINKFILE(' + CAST(file_id AS VARCHAR(2))
               + ',1,TRUNCATEONLY);'
        ELSE ''
        END AS 'ShrinkTruncateOnlyCommand'
FROM sys.master_files mf
LEFT JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.Name = 'YourDB'
    AND mf.type_desc = 'ROWS' --data only
ORDER BY DB_NAME(mf.database_id), [file_id]
OPTION (RECOMPILE);


Get object script in SQL Server via T-SQL? I need to get the creation script of an object in a SQL Server database. I need the exact same script that was used to create it. Like when you use SSMS to alter a view you already defined, and then the script is there, just the way you've defined it earlier.

I need to do this in T-SQL. Where does SQL Server store the scripts related to the database objects? I can't find the related view in system catalog views.

What I'm trying to find is something like:

select creation_script -- I know this column doesn't exist
from sys.objects
where name = 'CustomersView'

This is not feasible in T-SQL. Not all object have a definition stored and you would have to reverse engineer CREATE scripts for basic things like tables and indexes.

Rather than reinvent the wheel (in T-SQL), do the proper thing and reuse the component dedicated for this purpose the SMO Scripter class. Is already available, works, is up to date with all the latest SQL Server objects, is free to redistribute along with SMO. Simply move your scripting logic out of T-SQL.


Index Maintenance Plan At our organisation we are migrating to SQL Server from visual foxpro dbf and hence had no maintenance plans and have no DBA. We have gone through our backup plans and Index maintenance is only index rebuild as and when required.

I wanna propose a new plan for index maintenance but wanna be sure if am wrong about it.its like Daily Index reorganizing and index rebuild as and when required (weekly or as late as monthly).

As I understand reorganize can be done online and will reduce accumulation of fragments.

Does clustered index duplicate the table or it just reorganizes the table?

Also would like to know if there are any other aspects of sql server maintenance that i need to look-up.

Sorry for so many question, just curious about many things.

Take a look at http://ola.hallengren.com/

He has a great maintenanceplan which is free to use. I am using it for every project.


In Oracle, why would an OR / LIKE(A,B) take far longer than running the same query for each condition separately and unioning I have a complicated subquery that looks something like this

SELECT ...
FROM ...
WHERE
   my_column IN (1,3)

It takes a long time to run.

I tried splitting it out into:

SELECT ...
FROM ...
WHERE
   (my_column = 1 OR my_column = 3)

and that had no impact on the performance.

However, when I run:

SELECT ...
FROM ...
WHERE
   my_column = 1

and

SELECT ...
FROM ...
WHERE
   my_column = 3

Each runs quickly separately.

And when I union the results:

SELECT ...
FROM ...
WHERE
   my_column = 1
UNION ALL -- or UNION
SELECT ...
FROM ...
WHERE
   my_column = 3

It also runs quickly.

My question is, why would this EVER be the case?

If you're not clearing the buffer cache between runs, you don't know how much faster the query's running due to optimization and how much of that is due to the data already being cached.  Using the AUTOTRACE functionality in SQL*Plus allows one to see how much work (physical reads and logical reads) go into obtaining the result set.

Setting event 10053 to show to the optimization of the SQL statement would show why Oracle made the choice it did to not perform OR-expansion, which is what transforming a query with an IN condition or an OR statement into a UNION ALL query of disjoint queries.

Without more data, I suspect my_column is indexed, but you don't have a histogram on it.  A histogram would tell Oracle how relatively common or rare those values are in your table.  If that doesn't fix it, the USE_CONCAT hint can force Oracle to change Query 2 above into Query 5.


Sum of a column that is already a sum This is my query:

select d.department_id, d.department_name, sum(e.salary) AS total
from department d inner join employee e on d.department_id=e.department_id
group by d.department_id,d.department_name

I want the Grand total by sum of total column (the alias column). I get this by using this query:

select d.department_id, d.department_name, sum(e.salary) AS total
from department d inner join employee e on d.department_id=e.department_id
group by d.department_id,d.department_name WITH ROLLUP

But I want this by a another column, like using the sum function. Is it possible?

Case 1:  Do you want the extra column to have only one entry, at the bottom?

Case 2:  Do you want a column with a running total that increases row by row?

For Case 1, there are two approaches:

1a: Do it in your application code; this is probably cleaner.

1b: You want to do it entirely in SQL:

SELECT ... (what you have, plus an extra column with an empty string) ...
UNION ALL
SELECT (a bunch of blanks, then SUM(to get grand total) ...

For Case 2, you need an @variable that keeps the running total:

SELECT ... SUM(e.salary) AS ..., @run := @run + SUM(e.salary) AS ... ...

To get @run initialized, you could either have a separate SET statement or fold it into this query by using a 'cross join':  JOIN ( SELECT @run := 0 ) AS z (with no ON clause).


Select data from table join or UDPATE? I have a question regarding PostgreSQL DB performance.  I am given a CSV file with a lot of rows (let's say around 1 million rows) that represents updates to a database table (let's call it MY_TABLE).  I would like to copy the data into MY_TABLE from the CSV file.  It is presumed that all the rows in the CSV are updates to the data in MY_TABLE and are not new inserts.  I have 2 options for doing this, and I would like to know which one I should expect to be more performant and why (if possible):

Option 1:

1) Create a secondary table, let's say TEMP_TABLE.
2) Upload all the data from the CSV into TEMP_TABLE.
3) Natural join (on PK) MY_TABLE x TEMP_TABLE.
4) Select rows from MY_TABLE x TEMP_TABLE into MY_TABLE where the PK from the join matches the PK in MY_TABLE.

Option 2:

1) Create a SQL script on client-side containing N UPDATE operations of the form:

UPDATE MY_TABLE SET VALUES (data-from-csv) WHERE MY_TABLE.PK = (PK-data-from-csv)

where N is the number of rows in the CSV.

2) Upload that script to the database and execute it.

Which of these options is more performant and why?  Thanks.

I would think that Option 2 is more preferable because of the following:

Less overall writes to the database (no extra table required)
Less overall WAL activity (Option 1 involves both an INSERT/COPY and an UPDATE for each row involved)

In either case, you could encounter some table bloat, in which case you'd want to make sure you VACUUM after doing this operation.


How to optimize query with ORDER and LIMIT? I have simple query:

SELECT *
FROM "reviews"
WHERE (reviews.branch_id = '12345678')
    AND (reviews.provider IN('first', 'second'))
    AND (is_hidden = FALSE)
ORDER BY reviews.date_created DESC
LIMIT 13

Table structure:

CREATE TABLE reviews (
    id bigint NOT NULL,
    provider character varying(32) NOT NULL,
    branch_id bigint NOT NULL,
    text text NOT NULL,
    date_created timestamp with time zone DEFAULT now() NOT NULL,
);
CREATE INDEX reviews_provider_branch_id_date_created_idx ON reviews(provider, branch_id, date_created DESC) WHERE is_hidden=false;

Explain:

Limit  (cost=3278.96..3278.99 rows=13 width=1183) (actual time=15.657..15.668 rows=13 loops=1)
Buffers: shared hit=1834
->  Sort  (cost=3278.96..3283.32 rows=1743 width=1183) (actual time=15.654..15.665 rows=13 loops=1)
    Sort Key: date_created DESC
    Sort Method: top-N heapsort  Memory: 41kB
    Buffers: shared hit=1834
    ->  Index Scan using reviews_provider_branch_id_date_created_idx on reviews  (cost=0.43..3238.00 rows=1743 width=1183) (actual time=0.082..13.388 rows=1879 loops=1)
          Index Cond: (((provider)::text = ANY ('{first,second}'::text[])) AND (branch_id = '12345678'::bigint))
          Buffers: shared hit=1834
Planning time: 0.548 ms
Execution time: 15.791 ms

Without ORDER:

Limit  (cost=0.43..24.58 rows=13 width=1183) (actual time=0.047..0.088 rows=13 loops=1)
Buffers: shared hit=11
->  Index Scan using reviews_provider_branch_id_date_created_idx on reviews  (cost=0.43..3238.00 rows=1743 width=1183) (actual time=0.046..0.086 rows=13 loops=1)
    Index Cond: (((provider)::text = ANY ('{first,second}'::text[])) AND (branch_id = '12345678'::bigint))
    Buffers: shared hit=11
Planning time: 0.387 ms
Execution time: 0.135 ms

Is there any way to speed up this query?

While the records under branch_id = '12345678' and each of the two providers are sorted in the order you want, they aren't in a sorted order once they've been pulled out (because you've got two sets that are pushed together).

You might get an improved sort if you pulled the top 13 records for the branch_id and each of the providers separately. These should already be in the order you want. Then, UNION ALL the two result sets and grab the top thirteen from that. Since they're already sorted in the right order, the engine may realize that and do something faster than a HEAP SORT.

NOTE: Depending on the total number of rows involved, the simple expedient of grabbing the top 13 rows for each branch and provider may speed things up tremendously, in that the heap sort has to sort through all the possible matches to find the top 13, whereas we've limited the possible row count to 26 at most.

Of course, this doesn't scale well. If the number of branches and providers in the query is ultimately variable, then you would either need to write a new version of the query each time the number of either of those changed, or build some really hairy dynamic SQL. If that's the case, then trying to further limit the pool of candidate rows should help; consider putting some sort of date range into the query.

UPDATE: joanolo kindly put together the actual code for this, with a data sample of 900 records; here's the dbfiddle.uk link. It confirmed that the engine did pick something other than a heap sort - specifically, a quicksort. However, as joanolo notes, you need a reasonably large number of records for this to make a difference; with 900, it's faster, but not that much.


Union tables by mask in PostgreSQL In database I have many tables like with names:

"t_2015_09_01_z15"
"t_2015_09_02_z15"
"t_2015_09_03_z15"
"t_2015_09_04_z15"
"t_2015_09_05_z15"
...
"t_2015_10_01_z15"
"t_2015_10_02_z15"
"t_2015_10_03_z15"
"t_2015_10_04_z15"
"t_2015_10_05_z15"
...

Please help create function which union tables by mask, for example

SELECT maskunion('public', 'mask', 'tablename');

where public - scheme name, mask - mask name ('t_2015_09_' - union all tables for 09 mounth, 't_2015_' - union all tables for 2015 year), tablename - output table name.

I found a similar function https://stackoverflow.com/questions/4202135/how-to-drop-multiple-tables-in-postgresql-using-a-wildcard footgun, but this function is for deleting tables by mask. Now I manually union each table by query:

CREATE TABLE t_2015_10 AS
SELECT * FROM t_2015_10_10_z15
UNION ALL
SELECT * FROM t_2015_10_11_z15
UNION ALL
SELECT * FROM t_2015_10_12_z15
UNION ALL
SELECT * FROM t_2015_10_13_z15
UNION ALL
SELECT * FROM t_2015_10_14_z15
UNION ALL
SELECT * FROM t_2015_10_15_z15;

Here is a safer and more elegant version of your function:

CREATE OR REPLACE FUNCTION maskunion(_schema text, _tblbase text, _target text)
  RETURNS void AS
$func$
DECLARE
   _safe_target text := quote_ident(_schema) || '.' || quote_ident(_target);
BEGIN
   -- EXECUTE 'DROP TABLE IF EXISTS ' || _safe_target;  -- possibly dangerous!
   -- safer alternative:
   IF to_regclass(_safe_target::cstring) IS NOT NULL THEN
      RAISE EXCEPTION 'Target already exists: >>%<<', _safe_target;
   END IF;

   EXECUTE (
      SELECT 'CREATE TABLE ' || _safe_target || ' AS TABLE '
          || string_agg(quote_ident(schemaname) || '.'
                     || quote_ident(tablename), ' UNION ALL TABLE ')
      FROM   pg_tables
      WHERE  schemaname = _schema
      AND    tablename LIKE (_tblbase || '%')
      ORDER  BY tablename  -- better retain order
   );
END
$func$ LANGUAGE plpgsql;

This assumes the same schema for target and source tables.
Same call as in your own answer:

SELECT maskunion('public', 'tiles_2015_10_', 'newtable');

This concatenates and executes a command of the form:

CREATE TABLE newtable AS
          TABLE t_2015_10_10_z15
UNION ALL TABLE t_2015_10_11_z15
UNION ALL TABLE t_2015_10_12_z15
...

Major points

Like @Craig commented: CREATE TABLE AS is much more elegant.
I added a check whether the target table exists. Blindly dropping any table passed as parameter would make the function quite dangerous.
TABLE tbl is just short for SELECT * FROM tbl.
How to check if a table exists in a given schema
Make sure to escape identifiers properly:

SQL injection in Postgres functions vs prepared queries

BTW, the footgun function you refer to is a bit dated. You can TRUNCATE or DROP multiple tables with one command, and current Postgres has more elegant options for dynamic SQL:

Truncating all tables in a Postgres database


What makes it so much faster to write to the "WAL" compared to directly to the "real database"? PostgreSQL works like this, if I have understood things correctly:

I make an INSERT, DELETE or UPDATE query to a table in my PG database.
PG immediately writes this query and its parameters to the HDD/SSD in the form of a raw (internal) log, probably in binary format.
Assuming that the table is small enough to store in RAM, PG now INSERTs/DELETEs/UPDATEs this "RAM table" to reflect the change, but does not actually change the database files.
At some later point, perhaps in seconds, perhaps in minutes, and perhaps even hours or days later, when it "has time", PG updates the database files with the information from this "WAL" log file, and deletes the relevant line(s)/entries from the WAL log file.

The explanation I've been given for this seemingly strange ritual is that it's much faster to write to the WAL than it is to write to the database files directly for each query.
But why is this? I assume that PG's WAL log file is more sophisticated than just "adding lines to the end of a text file", since that could result in two queries writing to it at once and thus losing or corrupting data. So it has to be more sophisticated than that. Which means more overhead. At which point I wonder what real performance benefit there is to doing this, since both, at the end of the day, involve writing to a slow HDD/SSD disk.
However, I'm not doubting this. I'd just like to know why it's so much faster.
It seems to me that the problems with writing to the database files are the same with writing to the WAL. In both cases, you need to make sure that it's done orderly and properly. My only guess is that maybe various constraint checks and whatnot are expensive to look up.
Apparently, the concept of a "WAL" is unique to PostgreSQL, but it sounds like just common sense to keep at least some sort of "tabs" on what queries have been executed in order to account for power losses and software crashes. Just blindly writing to the database file each time but not even keep any kind of log sounds like a recipe for disaster, and I cannot imagine that any database ever has done this. Imagine if somebody makes a transaction of a million dollars right when the power is cut and then they lost a million dollars from their account but the other person received no money! Not even a database software from the 1940s probably worked like that...

You must have missed the answer while reading documentation:

Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The log file is written sequentially, and so the cost of syncing the log is much less than the cost of flushing the data pages. This is especially true for servers handling many small transactions touching different parts of the data store. Furthermore, when the server is processing many small concurrent transactions, one fsync of the log file may suffice to commit many transactions.


PostgreSQL streaming replication lag for synchronous standbys We use PostgreSQL 9.5 in a master + synchronous hot-standby setup, i.e., using synchronous_commit = on and using synchronous_standby_names='*'.

Consequently, the standby has sync_state = sync in the master's pg_stat_replication_table.

We also use postgres_exporter, which uses the following statement to extract the system's replication lag from the master's stats.

SELECT *, pg_current_xlog_location(),
       pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float
FROM pg_stat_replication;

Question: Does the above call to pg_xlog_location_diff always return 0 in a synchronous setup, or are there any cases when there is a measurable replication lag?

To my understanding, if the above statement shows a lag, it means that the master has committed a transaction without waiting for the standby, which should never happen in a synchronous setup.

Note that we moreover use replication slots, have setup the cluster using repmgr, and do not override synchronous_commit locally for individual transactions.

The pg_xlog_location_diff within your query may  return more than 0.

There are three writing timing of WAL data to WAL segment file: 1) When a transaction is committed, 2) When WAL buffer is filled up, 3) When WAL writer process writes periodically (default is 200 milliseconds).

And basic sequence of streaming replication is shown below:

(master) Write WAL data.
(master) Send WAL data to a slave.
(slave) Receive WAL data and replay it.
(slave) Return some information (e.g. replay location, receive location, flushed location.)

I show an extreme example to clarify the problem. If you run a long transaction to insert huge data, master continues to send WAL data  till the transaction is committed.  When you issue your query during the period from 2 to 3, pg_current_xlog_location() returns the current location whereas replay_location returns the previous replay location, pg_xlog_location_diff(pg_current_xlog_location(), replay_location) therefore returns more than 0.

Other case is the following: The configuration parameter max_standby_*_delay is enable and a conflict occurs, your standby suspends replaying WAL data, therefore pg_xlog_location_diff() returns more than 0.

(Both of examples are logical extreme cases.)

In any case, I think your problem does not depend on whether replication mode is sync or async. The difference between sync and async is that the master confirms a synchronous slave commits transactions or not.
Your problem is related to general sequence of WAL data sending and replaying, but is not depend on the synchronous mode.

(Consider how a long transaction is committed in synchronous mode. I discussed what happens when your query is issued  during the period from 2 to 3 (it's very short period) in the streaming replication sequence I showed. I did not discussed when transaction is committed because it is not essential of the answer, i think.)


What is the difference between these two index declarations and do I need both? I was examining a script with 'Display Estimated Execution Plan' and it made several recommendations for indexes to add to the database.  I added them and the script did indeed run much faster.  Now I'm looking at putting these into our standard form for inclusion into our regular upgrade scripts and I noticed what might be a redundancy:

CREATE NONCLUSTERED INDEX [TableA_ColA]
ON [dbo].[TableA] ([ColA])
INCLUDE ([ColB])
GO

CREATE NONCLUSTERED INDEX [TableA_ColB_ColA]
ON [dbo].[TableA] ([ColB],[ColA])
GO

I can see from the documentation that there are some differences between the two indexes as far as space usage is concerned, but in this case ColA is int and ColB is nvarchar(255), well under the 900 byte limit, so I'm not sure that the INCLUDE in the first index is necessary.

So is this just an arbitrary result of the execution plan analyser?  It seems to me if I have the second index the first one is redundant?

They satisfy different queries.

The first one will be useful for queries that seek on ColA = X and return ColB (without the INCLUDE this would require a lookup back to the base table)

The second one will be useful for queries that seek on ColB = Y

Both of them can be used for queries like ColA = X AND ColB = Y but the second one will do this more efficiently as the first index would require a seek on ColA then the residual predicate be evaluated against all matching index rows.


Retrieving data if control file is lost in oracle Database Suppose if the databases control file is lost permanently (no backup).
Can we retrieve the data from data file in Oracle database?

You may create new controlfiles with a CREATE CONTROLFILE statement including a list of data files and redo logs.

startup nomount

CREATE CONTROLFILE REUSE DATABASE TEST RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
        LOGFILE
          GROUP 1 '/u01/oradata/test/redo01a.rdo', '/u02/oradata/test/redo01b.rdo'  SIZE 500M
        DATAFILE
          '/u01/oradata/system01.dbf',
          '/u01/oradata/undotbs01.dbf'
        CHARACTER SET WE8ISO8859P1
        ;
recover database;
alter database open;

You need to list your actual database files and come up with sane parameters for your environment, including character set, the size of the redo logs and the name of the database.

If you are unable to RECOVER DATABASE you may instead use ALTER DATABASE OPEN RESETLOGS;

Full documentation for CREATE CONTROLFILE can be found in the  Oracle Database SQL Reference


Does ISOLATION LEVEL READ UNCOMMITTED affect Deletes? I have an exceptional circumstance that made me use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.  (I am using sp_getapplock to ensure single process access to a given row.)

I am trying my best to avoid any kind of lock escalation. I know clearly what ISOLATION LEVEL READ UNCOMMITTED does for reads, but I am not sure about deletes.

I saw some example code that was doing a delete using ROWLOCK and READPAST, along with ISOLATION LEVEL READ UNCOMMITTED but I was not clear on what that would do, so I have not been using it for now.

Is that needed to prevent lock escalation of my deletes or is ISOLATION LEVEL READ UNCOMMITTED sufficient?

Note:
In case there is interest, here and here are the relevant sprocs for this question.

I am failry clear on what ISOLATION LEVEL READ UNCOMMITTED does for reads, but I am not sure about deletes.

It does nothing.  You can't modify the database without proper locking.  That would produce not just the failures and nonsense results that dirty reads produces, but would corrupt the database.


recreate row IDs in sorted table I built word2vec dictionary as a mysql table. There is millions of rows like this:

ID      CREATED                 UPDATED                 KEYWORD     COUNT   NUMPYBLOB   NUMPYBLOB ... ...
121201  2018-03-08 04:01:23     2018-03-08 21:54:34     iulius         32
121202  2018-03-08 04:01:23     0000-00-00 00:00:00     admonitivo      1
121203  2018-03-08 04:01:23     0000-00-00 00:00:00     lulius          1
121204  2018-03-08 04:01:23     2018-03-08 16:56:14     lugh           20
121205  2018-03-08 04:01:23     2018-03-08 22:34:31     contingencias  66
121206  2018-03-08 04:01:23     2018-03-08 19:50:34     liberándola    12
121207  2018-03-08 04:01:23     0000-00-00 00:00:00     agostillo       1
121208  2018-03-08 04:01:23     2018-03-08 22:47:39     autonomista   212
121209  2018-03-08 04:01:23     2018-03-08 05:54:12     astrológicamente4
121210  2018-03-08 04:01:23     2018-03-08 18:54:43     redondearse     5

Now I want to sort this table by word occurrence counter (COUNT) and when the table is sorted rewrite autoincremental ID row from zero to X in order to word ocurrency goes down. I can't use COUNT column as primary key, because it is varies continuously and not unique.
How I can do it?


MongoDB page faults even when all data is in resident in RAM I have made a sample app that emulates what my real one does. It simply iterates over all documents of a specific collection over and over.

I start mongo (as a windows service) and mongostat shows almost no resident memory.

insert  query update delete getmore command flushes mapped  vsize    res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
*0     *0     *0     *0       0     1|0       0      6.03g  6.12g    71m      0 local:0.0%          0       0|0     0|0    62b     3k     3   09:35:11

I use the touch command on the single collection I have (which is about 2GB in size) and mongostat shows:

insert  query update delete getmore command flushes mapped  vsize    res faults          locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
*0     *0     *0     *0       0     5|0       0      6.03g  6.12g   2.1g 427937 HamsterSchool:0.0%          0       0|0     0|0   408b     4k     3   09:39:59
*0     *0     *0     *0       0     1|0       0      6.03g  6.12g   2.1g      0         local:0.0%          0       0|0     0|0    62b     3k     3   09:40:00

Which seems as though the entire collection is now resident in memory, but when I run the sample app, no matter how long, there are always a lot of page faults:

insert  query update delete getmore command flushes mapped  vsize    res faults          locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
*0     68     *0     *0       0     1|0       0      6.03g  6.12g   2.1g  35616 HamsterSchool:0.0%          0       0|0     1|0     6k    74m     4   09:43:15
*0     68     *0     *0       0     4|0       0      6.03g  6.12g   2.1g  36068 HamsterSchool:0.0%          0       0|0     0|0     6k    75m     5   09:43:16

So, why are there any page faults at all if everything was already "touched"?. It's important to note that mongo runs on a 32GB RAM 64bit windows machine and 27GB of RAM is free at all times.

The page faults metric for MongoDB on Windows essentially contains hard (actually hitting disk) and soft (reallocating a pointer in memory) page faults.  If you run the same experiment on Linux, the page fault metric only reports hard page faults and you will see the behavior you expect.

This is a known issue with the Windows version and the relevant issue for tracking/voting is SERVER-5799


How to count number of consecutive events matching status of last event? In MS-SQL Server 2008, I have a table of events in the following format:

Controller   |  ExecutionTime        |  Result
1            |  2012-09-24 09:00:00  | 0
1            |  2012-09-24 09:01:00  | 0
2            |  2012-09-24 09:02:00  | 1

I need to analyze this table to produce a result that:

Identifies each controller whose latest event was an error (result=1)
Identifies how many consecutive failures that controller has had (# events since the most recent successful event)

I've made some progress toward this task, I can identify the controllers whose most recent event was a failure - see this SQL Fiddle.  But I could use some help finding an approach to counting the # of consecutive failures that has led to this state.

Thanks!
John

Here's one solution. Not very efficient I'm afraid:

SELECT
    t.Controller,
    MIN(t.ExecutionTime) AS FirstFaultInSeries,
    MAX(t.ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
FROM
  ( SELECT
        Controller,
        COALESCE(MAX(CASE WHEN Result = 0 THEN ExecutionTime END)
                ,'19000101')
          AS LatestGood
    FROM t
    GROUP BY Controller
  ) g
  JOIN t
    ON t.Controller = g.Controller
   AND t.ExecutionTime > g.LatestGood
GROUP BY
    t.Controller ;

Tested: Fiddle

And another:

WITH cte AS
  ( SELECT
        Controller, ExecutionTime, Result,
        ROW_NUMBER() OVER (PARTITION BY Controller
                           ORDER BY ExecutionTime DESC
                          )
          AS Rn,
        ROW_NUMBER() OVER (PARTITION BY Controller, Result
                           ORDER BY ExecutionTime DESC
                          )
          AS RnResult
    FROM t
  )
SELECT
    Controller,
    MIN(ExecutionTime) AS FirstFaultInSeries,
    MAX(ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
FROM
    cte
WHERE
    Result = 1
  AND
    Rn = RnResult
GROUP BY
    Controller ;


PostgreSQL Database not opening I tried opening my PostgreSQL database and I am getting following error:

  An error has occurred:
  could not connect to server: No buffer space
  available (0x00002747/10055)   is the server running on "localhost"
  (::1) and accepting TCP/IP connections on port 5432?   could not
  connect to server : no buffer space avalaible (0x00002747/10055)   is
  the server running on "localhost" (::1) and accepting TCP/IP
  connections on port 5432?

I have installed PostgreSQL in D drive. I am using PHP on Windows.

How can I solve this problem?

Update:

My PostgreSQL problem was solved the problem how means I installed the PostgreSQL in D drive so, I seriously I checked C drive space there was less memory so, I uninstalled some applications where installed in C drive after restarting my PC automatically started. But one thing we should remember where ever we install the applications like C drive, D drive and so on.. but that application acquire the C drive buffer space so, in C drive minimum space is required memory is up to 5 GB then we won't get any application error.

No buffer space available corresponds to a network error where the client is not enable to allocate the resources to initiate a connection.

Generally, this means that there are other programs holding too many connection handles (sockets).

According to a post on IBM developerWorks:

  This error can occur on Windows, particularly around socket
  operations. The error is translated from the Winsock error code
  WSAENOBUFS, 10055. The most common cause of this error is that Windows
  is configured for the default maximum of 5,000 in-use ports. This can
  be monitored by watching netstat or perfmon and can be changed with
  the MaxUserPort registry parameter.

  A more advanced cause for this error is non-paged pool exhaustion[...]

Uninstalling software will solve the problem for good only if that software was responsible for consuming too many simultaneous connections in the first place. Otherwise, getting back some of these resources may happen as a side-effect of say, rebooting, or just waiting a bit so that TCP connections in TIME_WAIT state disappear, or individually terminating programs that held these connections open.


Query "last" records in a table with non-sequential key fields My goal is to clone a table from an Oracle database to a SQL Server database every midnight. I am using Talend Data Integration for this task.

The problem is that this table has around 70 million records (~100GB of data, including indexes), and I can't just drop it then load it again, because it is too resource intensive (especially because other tasks must be done).

The obvious solution would be an incremental update: I query the newly inserted records from Oracle and insert them into SQL Server.

So far, so good.

But the table I am querying from in Oracle has no ordering at all. Its unique key is composed of four fields that can receive any value, and I have no control over them.  I must ensure that only "new" records are returned, but there is not a field (or set of fields) that I can use to sort the table and 'force' a sequential ordering. To be clear: none of the columns stores any kind of CreationDateTime datum.

I have only SELECT permissions on the Oracle database, so I can't make any changes, for example to flag records I've already imported.

I can connect to both databases from the same computer.

Querying the destination database for each record is also very resource intensive. Network and disk overheads are a problem, but they are not my main concern here.

Has anyone faced this problem before? Any idea how to solve it? Maybe there is no solution for this problem, but I was expecting some sort of 'trick': some hidden Oracle column, internal procedure, or other dark magic...?

My last resort is to make some assumptions to import, like "assume these records are ordered and import, then check consistency of some well chosen samples, if inconsistent, run full import again". This is very far from ideal, but at least I will not run a full import every time.

You can use ORA_ROWSCN pseudocolumn to find rows that have been changed since last update. By default it tracks changes by database block, not by row, but it can be used to limit the amount of data transferred between databases.

I would suggest a following procedure:

Find the current Oracle SCN (System Change Number)

select dbms_flashback.get_system_change_number from dual;
Get rows that have been changed since last update

select * from [source_table] where ora_rowscn > [last scn] and ora_rowscn <= [current scn]
Resolve possible duplicates in your target database
Store the [current scn] to target database so it can be used as [last scn] during the next iteration


Encrypt and decrypt text using the same key I need to write a stored procedure that will accept two input string parameters and will encrypt the data as explained below.

First parameter should be the message to encrypt and second parameter should be the key for encryption. The encryption key must be min. 6 to 8 characters mix of alphabets & numbers. Procedure should perform encryption by adding the key to message. In addition to this, the procedure should also decrypt back the message using same key.

I am able to encrypt input text using a key, but not able to decrypt text using same key.

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_EncryptWithKey;
CREATE PROCEDURE `sp_EncryptWithKey`(IN `entext` varchar(50), IN `keyval` varchar(8))

begin
declare des varchar(500);
declare len int;
set len=length(keyval);
if keyval regexp '[A-Za-z]' && keyval regexp '[0-9]' && length(keyval)>=6 && length(keyval)<9

then

    begin

    SELECT entext,keyval INTO @A,@B;
    SET @C=AES_ENCRYPT(@A,@B);
    SELECT @C as encrypt_value;

    SELECT AES_DECRYPT(@C,@B) as decrypt_value;
    end;
else
     select 'key length must be between 6-8' as error;

      end if;
end
$$

DELIMITER ;

In the stored procedure, if my input text is ABCD and key is pqrs123 then the output is encrypted text e.g. Ÿå4¸Wòm. If I give input text as Ÿå4¸Wòm with same key I should get ABCD as decrypted text.

Do not attempt to copy&paste the encrypted value.  Do not try to put the encrypted value in any kind of TEXT field, only BLOB (or BINARY).


Count where previous record exists in either of two tables I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another.

The table that I'm sampling has a composite PRIMARY KEY consisting of a bigint and character.  The other table has the same structure.

A "previous" record has the same character and a bigint - 1 as the counted record either on the same table or on the other table.

In pseudo:

counting_table
   character_column
   bigint_column

other_table
   character_column
   bigint_column

counting_table will never have a parallel record on other_table because the parallel record on counting_table is deleted when its primary key equivalent is inserted into other_table.

I thought I could do this with subqueries, but I can't seem to figure out how to access the outer query's data from inside the subquery.

How can my intent be implemented optimally?

Inner join on counting_table.bigint-1 = other_table.bigint and counting_table.char = other_table.char

SELECT *
FROM counting_table t1
INNER JOIN other_table t2
    ON t1.bigint_column-1 = t2.bigint_column
    AND t1.character_column=t2.character_column


acl to allow nested groups in owner attribute I'm trying to create an ACL entry to allow users specified in the owner attribute of a group to manage the group, and also allow groups, including nested groups to be specified as owners.

I've got this rule, which allows admin access to nested members of a specific group:

{0}to * by ssf=128 set="user & [cn=Administrators,ou=LDAP,dc=Applications,dc=example,dc=com]/member*" manage by * break

And I've got this rule which works for granting management permissions to users that are directly specified as owners:

{2}to dn.children="dc=Groups,dc=example,dc=com" by ssf=128 set="user & this/owner" manage

but so far I haven't been able to figure out how to combine the two concepts to allow specifying groups as owners.  Anyone gotten something like this working?

Almost as soon as I posted the question, I saw the dumb mistake I made -- I forgot to include the permission to be granted in the acl entry.  This entry is tested and works for users as owners, as well as groups and nested groups as owners (with a break-through so non-owners aren't rejected outright):

to dn.children="dc=Groups,dc=example,dc=com" by ssf=128 set="user & this/owner" manage by ssf=128 set="user & this/owner*/member*" manage by * break


SQL Server AlwaysOn AG Synchronous In a SQL Server AlwaysOn AG synchronous mode scenario, if the secondary node goes down, is the primary down as well (since it cannot write to and commit to the secondary)?

No.  If the secondary drops, the primary node will continue operation.  However, you need to be aware of how much disk space the queued transactions will consume.  If you lose the second node for an extended period of time, it may make sense to evict it from the cluster so that the primary node won't queue the transactions.


RDS is quickly running out of storage, how can I debug this? I am using RDS and  looking at my POSTGRES size via \l+ and I see that it is using 154 GB hard disk space.  However, when I look at my StorageSpace statistic in RDS, it says that I’m using: 210GB.  I am trying to figure out where the 56GB that are unaccounted for, are coming from?  I found that 16GB of this is Log Files.  Can it be pglogical and my WAL taking up a lot of space (I recently learned that the sync is not replicating at the moment). Any other ideas of what it can be?

I figured out that one of my replication ‘subscriptions’ was broken on a Subscriber database.  Once, I fixed the subscription, pglogical started replicating successfully, and my storage went up 40GB instantly.


How do I allow non sysadmin accounts the ability to create/query linked servers in SQL Sever 2014? I want to allow a non-admin account the ability to query a linked server.

If I create a linked server with the following under a sysadmin account like below.

EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVER', @srvproduct=N'', @provider=N'IhOLEDB.iHistorian', @datasrc=N'linkedservername'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

When I run SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT  * FROM ihTags') from a sysadmin account (no matter what account it is), it works.

If I run the query from a non-sysadmin account I get the following error.

  Msg 7416, Level 16, State 2, Line 2
   Access to the remote server is denied because no login-mapping exists.

According to Article: Access to the remote server is denied because no login-mapping exists.

Need to specify User ID in the provider string if Non-SYSAdmin accounts are used.

Example:

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'SQLNCLI',
    @provstr = N'SERVER=MyServerName\MyInstanceName;User ID=myUser'


MySQL secure random string generator I am looking to generate a cryptographically secure string for password reset in MySQL. I know I could do this at the application level in PHP; but that requires the OpenSSL extension which many of my customers might not have.

If I can do it in MySQL 5 in a secure way that is widely available that would be ideal. Is this possible?

Note: This is for generating a secure token for password reset. It doesn't have anything to do with a secure connection, so using https is not a solution.

There are many encryption methods available in mySQL.

If you need two way encryption you could use aes_encrypt which has the accompanying aes_decrypt

If if you only need a secure hash then you could use sha2

The following statement could get you a similar result to openssl_random_pseudo_bytes

SELECT HEX(SHA2(CONCAT(NOW(), RAND(), UUID()), 512));

The statement above takes NOW() and concatenates it with RAND() and a UUID(), then performs a 512 bit SHA2() encryption on the result, and then converts that to HEX()


SQL Server Backups suddenly change size Yesterday while I was traveling, one of our SQL Server 2016 servers choked on a TempDB that was full. Since I'm the only DBA, one of the system engineers tried to "fix" the server by rebooting.

This triggered some sort of iSCSI/SAN/LUN/etc. cascade of insanity that resulted in the entire data drive being lost (user DBs, system DBs, error log files... all gone). Their response was to supply me with a nice, new, iSCSI drive since they couldn't get the old one back. (sigh)

So today, I restored the system DBs from backup, then restored the user DBs from backup. I then did a full backup on all of the freshly restored databases. Just in case.

But I noticed something.

I restored a two-day-old backup of Master that was 785,489 KB. After completing all of the other restores, the new backup of Master is 718,519 KB.

I restored a two-day-old backup of MSDB that was 36,019 KB. After completing all of the other restores, the new backup of MSDB is 18,333 KB.

(I can't really do a similar comparison on user DBs because they were Full, Diff, and Log restores...)

The backups are compressed. These were full backups that I restored from, and full backups I created. As system databases, there are no diff or log backups being applied in between.

Is it normal for a just-restored database's backup to not be the same size or at least nearly the same size as the original?

A database backup contains the allocated extents plus the parts of the log required to replay any activity that occurred during the backup, and restore the database to the point-in-time at which the backup completed.

So your first backup file could contain more log records than your second backup.

David


SQLCLR assembly throws error when multiple queries run simultaneously I have been using SimMetrics to implement the QGramsDistance search in SQL Server. The SimMetrics is used as a SQL CLR assembly. The search results are promising and everything was well, until of late, the database has grown and the number of records have crossed a few hundred thousand with multiple users performing searches. Now, I encounter this error.

Msg 6522, Level 16, State 2, Procedure SchoolNameAutoCompletion, Line 42
A .NET Framework error occurred during execution of user-defined routine or aggregate "QGramsDistance":
System.InvalidOperationException: Collection was modified; enumeration operation may not execute.
System.InvalidOperationException:
   at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at System.Collections.Generic.List`1.Enumerator.MoveNextRare()
   at System.Collections.Generic.List`1.Enumerator.MoveNext()
   at SimMetricsMetricUtilities.QGramsDistance.GetActualSimilarity(Collection`1 firstTokens, Collection`1 secondTokens)
   at SimMetricsMetricUtilities.QGramsDistance.GetSimilarity(String firstWord, String secondWord)
   at MyAssembly.DbTextFunctions.StringMetrics.QGramsDistance(SqlString firstWord, SqlString secondWord)

I have tried using the latest version of the SimMetrics in SQL Server 2012 using the .NET framework v4.0.30319 but has not made a difference. It's pretty clear that this error occurs only when more than a single search is performed.
Has anyone come across such an issue and have it resolved?

The type of error that you are getting (i.e. Collection was modified;) is most likely caused by sharing the collection across multiple threads. If the variable holding the collection is defined as static, then that variable is not thread-safe since all sessions share the same App Domain for all Assemblies in a particular Database that are owned by the same User.

And, judging by the class name of StringMetrics, the namespace of SimMetricsMetricUtilities, and the input parameter names, I am guessing that you are using the SQLCLR port recommended in the "News" section of the SimMetrics repository (please note: I am being intentionally vague in not linking to this "News" page or mentioning the title of the blog post containing this particular SQLCLR port since, in its current form, it should not be used due to incorrect usage of the SQLCLR API in a few areas). The main problem with that code (given just a quick review) is that it instantiates static classes for each algorithm. Hence you are sharing those collections across sessions, and even with the algorithm being quick, you are now calling the methods frequently enough to have those sessions (i.e. threads) stepping on each other. Lucky for you that Generic Lists throw these errors.

You might be able to fix this by:

getting rid of the static class constructor
getting rid of the static readonly variables
adding the following line just above the return in the QGramsDistance method in the StringMetrics class:

QGramsDistance _QGramsDistance = new QGramsDistance();

Yes, it needs to be instantiated with each execution of the SQLCLR UDF.
In the SqlFunction attribute, change IsPrecise to be false. Using the FLOAT datatype is specifically imprecise ;-).

Changes #1 and #2 only need to be done once to cover the functions/methods for all of the algorithms. Change #3 should be done across the rest of them using the class name that is appropriate for each particular method. And Change #4 should be done across all of those SqlFunction methods, and is the exact same change across all of them.


Can Microsoft Log Parser 2.2 open SQL LDF files? From the technet article for Microsoft's Log Parser 2.2 it states that

"Can't find an input format you need?  The new COM input format makes it possible to create your own custom Input Format and plug it into the Log Parser engine."

Is it possible to create such a COM input format for Microsoft SQL LDF files (2005/2008), and if so any examples of how it can be done?

We are wanting the ability to investigate the LDF files to better support our development staff without breaking the bank.

Log != transaction log

That said, if you were hell bent on torturing yourself or a development team with an arduous time consuming task that would yield minimal value, this would be an ideal project. The cheaper option (if viewing transaction log records is really what you need) would be a 3rd party tool like ApexSQL Log.

Depending on what you're trying to achieve with this, the undocumented fn_dblog command might be of interest to you. Also see Tracking Transaction Log Activity in Denali for a neater way of handling this with extended events in the 2012.

If you expand your question with a better description of why you want to look at log records, we can suggest alternative (less hacky) approaches.


Oracle Function Accepting Precision 2 Number I'm trying to define an Oracle function that only accepts a NUMBER with a precision of, at most, 2 digits:

create or replace FUNCTION ftest(x NUMBER(2)) RETURN NUMBER IS
  BEGIN
    RETURN 42; -- body of function is not relevant for this question
  END;

However, it does not compile:

Error(1,24): PLS-00103: Encountered the symbol "(" when expecting one of
       the following:     := . ) , @ % default character The symbol ":="
         was substituted for "(" to continue.

How can I fix ftest's definition to only allow an input NUMBER of precision 2?

in oracle plsql function or procedure prototype you can just specify datatype and not length or precision and so you want control length or precision you should handle it in your function or procedure body for example:

create or replace FUNCTION ftest(x NUMBER) RETURN NUMBER IS
BEGIN
    if length(x) > 2 then
    return;
    end if;
    RETURN 42; -- body of function is not relevant for this question
END;

Or

create or replace FUNCTION ftest(x NUMBER) RETURN NUMBER IS
t number(2);
BEGIN

    t:=x;
    RETURN 42; -- body of function is not relevant for this question
    EXCEPTION WHEN VALUE_ERROR THEN
    RETURN -1;

END;