Oracle : Is my DISTINCT function working properly?

Your two queries are counting the same thing.

Consider this data:

create table table1 (doc_id varchar(20));
create table table2 (doc_id varchar(20));

insert into table1 values ('foo');
insert into table1 values ('foo');
insert into table1 values ('bar');
insert into table1 values ('bar');

insert into table2 values ('foo');
insert into table2 values ('foo');
insert into table2 values ('bar');
insert into table2 values ('bar');

count(distinct doc_id) returns 2 for both tables.

But the following returns no rows at all:

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;

So the count of that is of course zero.

If you change the sample data your second query still counts something different:

delete from table2;
insert into table2 values ('foo2');
insert into table2 values ('foo2');
insert into table2 values ('bar2');
insert into table2 values ('bar2');

count(distinct doc_id) still returns 2 for both tables, so the difference is still 0 between those two counts

But,

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;

Now only returns foo and bar because foo2 and bar2 are not in table1.

If you add another distinct value to table2:

insert into table2 values ('bar3');

The difference in the distinct count from both tables is 1 (2 distinct values in table1, 3 in table2), but the minus still returns the two distinct values from the first table.


WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY room ORDER BY `date` DESC) rn
             FROM mdata)
SELECT id,room,`date`,`temp`
FROM cte
WHERE rn=1

If a table contains huge array of data you can slightly restrict the amount of scanned records:

WITH
cte1 AS (SELECT room, MAX(`date`) `date`
         FROM mdata
         GROUP BY room),
cte2 AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY room ORDER BY `date` DESC) rn
         FROM mdata
         WHERE `date` >= (SELECT MIN(`date`)
                          FROM cte1))
SELECT id,room,`date`,`temp`
FROM cte2
WHERE rn=1


Restrict Full access to certain User of Database

If you are trying to prevent the owner of the instance or any Windows or SQL Server administrators from being able to access your database, the answer is no.

Essentially you should assume that any administrator on the box owns and can gain access to everything on the box, including your database.

You may be able to protect the actual data in some cases (check out Always Encrypted in SQL Server 2016, for example), but that's about it. The administrator can access everything you don't encrypt (and much of the stuff you do in current versions), drop your database, lock you out, etc.

If you don't trust the service provider that's hosting your database, you should host elsewhere. After all, any host worth their salt should have agreements in place that legally protect you, even though not physically, from internal data breach.


Return even empty groups when grouping by date

Move the condition from the WHERE clause to a conditional count:

SELECT
    COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END),
    CD_Carrier,
    DATEPART(DAY, DT_Arriving) as [Date]
FROM TB_AGIL
WHERE
    DT_Arriving >= @date
    AND DT_Arriving < DATEADD(MONTH,+1,@date)
GROUP BY
    CD_Carrier,
    DATEPART(DAY, DT_Arriving)
ORDER BY
    [Date];

Other approaches to implement the deprecated GROUP BY ALL syntax can be found here.


Is it possible to set stand-by database to active, do changes and then continue restore chain?

(since this is longer than a comment .. so posting this as an answer)

In sql server enterprise edition, you can leverage database snapshots.

If I understand your question, you want to do some changes on a standy (not live) copy of your database and then discard those ?

If that is true, then you can create a database snapshot of your main database using :

CREATE DATABASE snapshotDBName... AS SNAPSHOT OF yourPRODDBNAME

Then do the modifications - Inserts / Updates / Deletes using regular TSQL and once you are done with your modifications, you can drop the snapshot using

DROP DATABASE snapshotDBName

You can read more on SQL Server Database Snapshots

Note: If this is not what you want, then I will delete this answer .. which I wrote assuming stuff.


What factors do we need to consider in order to enable auto-growth settings for my PROD database

You've made an impressive start, here, starting to gather raw data that you can monetise and present to the business.  "This much data, this much cash each year".

To make plans about data usage, you need to have an understanding of what's being stored in the database and, more importantly, how long your business should retain it (or, indeed, how long they're allowed to keep it).

All too often, business people just want to keep everything.  "Disk space is cheap".

That's fine, as long as they're prepared to put up the money for the extra disk space involved and for the overhead of [your] maintaining the table structures with things like Partitioning, to keep their Applications performing well.

These days, however, there are additional, regulatory restrictions on how long data can be kept for and when it must be deleted at a User's request and, again, your business needs to come up with mechanisms for getting rid of data that they're no longer entitled to have or have been asked to delete.

It's not something you can do on your own; you need to be working with your business to come up with the strategy together.


Should I exclude mysql database from multi master replication?

Since the mysql schema is usually manipulated with GRANT and REVOKE commands, there is no need to let the mysql schema replicate. Why ?

Someone could hack privileges in by means of INSERT commands into mysql.user.

Such hacks are possible because I wrote about it before

Oct 28, 2011 : MySQL: trying to create root user
Sep 12, 2011 : How do you gain access to embedded MySQL database via localhost command line?
Mar 06, 2011 : Installing default MySQL Tables in Windows (mysql_install_db)

If these hacks were done on a Master, a Slave might intercept them. The new grants on the Slave would not be active until someone restarted mysqld on the Slave or ran FLUSH PRIVILEGES; on the Slave.

So, to answer your question: exclude it via the my.cnf


mysql CREATE TRIGGER after insert on Update column with count

The immediate issue with your first statement is that it has BEGIN without a corresponding END. In this case, END should be at the very end of the CREATE TRIGGER statement.

Once you add the END keyword, the statement becomes technically correct, but you will have trouble executing it in MySQL. The reason is that semicolons, while being standard statement delimiters in SQL, are also treated by MySQL in a special way. MySQL splits the input text at semicolons and sends each part of the text to the server separately. The server thus receives incomplete, syntactically incorrect pieces and returns an error. See here for more details:

Defining Stored Programs

So, to prevent MySQL from doing that, you need to instruct it to use a different symbol as a delimiter – that is what the DELIMITER command is for. Consequently, your second attempt should be something like this:

DELIMITER $$

CREATE TRIGGER `estatecat_piece`
AFTER INSERT ON `estate`
FOR EACH ROW
BEGIN
  UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);
END$$

DELIMITER ;

The first DELIMITER command tells MySQL to parse the input text until $$ is encountered from that point on. Your CREATE TRIGGER should, therefore, end with the $$ symbol so that MySQL can consume it in its entirety and send the whole statement to the server. Finally, the second DELIMITER command restores the standard delimiter for MySQL to resume normal processing of your commands.

There is also a simpler solution, but it works only in cases similar to yours. The trigger body in your case consists of a single statement. That allows you to omit the keywords BEGIN and END from your statement and, as a result, avoid using the DELIMITER command at all:

CREATE TRIGGER `estatecat_piece`
AFTER INSERT ON `estate`
FOR EACH ROW
UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);

The above trigger will work exactly the same.

On a different note, your UPDATE statement may have a flaw: as currently written, it will update the piece column in every row with the same row count – that of the category matching the inserted row. If the table has multiple rows, each for a different category, you probably need to introduce a filter on new.estatecat_id to your UPDATE statement, similar to the filter in the subquery, in order to update only the corresponding piece value:

UPDATE
  estate_category
SET
  piece = (
    SELECT
      COUNT(*)
    FROM
      estate
    WHERE
      estate.estatecat_id=new.estatecat_id
  )
WHERE
  ... /* specify the necessary column name */ = new.estatecat_id
;


How can a Cassandra node see another node as down?

Running nodetool enablegossip on the host that appeared down to other nodes fixed it for me and for now. However, it appeared as down to all other nodes I checked. Running in a non-cloud environment.

I was curious what my other nodes said and I found one that had the same issue (like your 10.0.9.8, showing 10.0.10.8 as down). Only running nodetool enablegossip on 10.8 didn't help. But running disablegossip first and then enablegossip again did!


Efficiencies and pitfalls of a dynamically updated "Uber" View to streamline future code

To summarize discussion on the topic, creating this sort of "Uber" view with several tables that have matching primary keys and similar (if not the same) row counts is not necessarily a bad idea for the purpose of streamlining code.

Additionally, any tables or columns not used in subsequent queries on such a view are dropped from the execution plan in most cases, so performance is most likely unharmed by unnecessary joins in the view definition.

However, how the view is used down the road is an important consideration to bear in mind and should be avoided in multi-layer view within view, or view within stored procedure calls.

One interesting little note I learned while testing this out is there is a View width limit of 1,024 columns in SQL Server, so my particular case is simply not possible to accomplish.


Did you read the rest of the docs?

  Output when running DBCC against an earlier version of Analysis
  Services

  DBCC is only supported on databases running on a SQL Server 2017
  instance. Running the command on older systems will return this error.

Executing the query ...
The DBCC element at line 7, column 87 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command.
Execution complete

Updated
Here is a link that explains things a bit more in detail than the docs you're are referencing.

https://www.mssqltips.com/sqlservertip/4077/consistency-checks-for-sql-server-analysis-services/


@ypercube's answer does manage this partially as a metadata only change.

Adding the constraint with NOCHECK means that no rows will need to be read to verify it, and if you are starting from a position where the column does not contain NULL values (and if you know none will be added between checking and adding the constraint) then, as the constraint prevents NULL values being created from future INSERT or UPDATE operations, this will work.

Adding the constraint can still have an impact on concurrent transactions however. The ALTER TABLE will need to acquire a Sch-M lock first. Whilst it is waiting for this all other table accesses will be blocked as described here.

Once the Sch-M lock is acquired the operation should be pretty quick however.

One problem with this is that even if you know the column in fact has no NULLs the constraint is not trusted by the query optimiser which means that the plans can be sub optimal.

CREATE TABLE T (X INT NULL)

INSERT INTO T
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values

ALTER TABLE T WITH NOCHECK
  ADD  CONSTRAINT X_NOT_NULL
    CHECK (X IS NOT NULL) ;

SELECT *
FROM T
WHERE X NOT IN (SELECT X FROM T)

Compare this with the simpler

ALTER TABLE T ALTER COLUMN X INT NOT NULL

SELECT *
FROM T
WHERE X NOT IN (SELECT X FROM T)

One possible problem you might encounter with altering the column definition in this way is that it not only needs to read all the rows to verify that they meet the condition but also can end up actually performing logged updates to the rows.

A possible half way house might be to add the check constraint WITH CHECK. This will be slower than WITH NOCHECK as it needs to read all rows but it does allow the query optimiser to give the simpler plan in the query above and it should avoid the possible logged updates issue.


The curious case of HADR_SYNC_COMMIT waits

First the description of the wait event that your question is regarding is:

  Waiting for transaction commit processing for the synchronized secondary databases to harden the log. This wait is also reflected by the Transaction Delay performance counter. This wait type is expected for synchronized availability groups and indicates the time to send, write, and acknowledge log to the secondary databases.

  https://msdn.microsoft.com/en-us/library/ms179984.aspx

Digging into the mechanics of this wait you have the log blocks being transmitted and hardened but recovery not completed on the remote servers.  With this being the case and given that you added additional replicas it stands to reason that your HADR_SYNC_COMMIT may increase due to the increase in bandwidth requirements.  In this case Aaron Bertrand is exactly correct in his comments on the question.

Source: http://blogs.msdn.com/b/psssql/archive/2013/04/26/alwayson-hadron-learning-series-hadr-sync-commit-vs-writelog-wait.aspx

Digging into the second part of your question about how this wait could be related to application slowdowns.  This I believe is a causality issue.  You are looking at your waits increasing and a recent user complaint and drawing the conclusion potentially incorrectly that the two have a relationship when this may not be the case at all.  The fact that you added tempdb files and your application became more responsive to me indicates that you may have had some underlying contention issues that could have been exacerbated by the additional overhead of the implicit snapshot isolation level overhead when a database is in an availability group.  This may have had little or nothing to do with your HADR_SYNC_COMMIT waits.

If you wanted to test this you could utilize an extended event trace that looks at the hadr_db_commit_mgr_update_harden XEvent on your primary replica and get a baseline.  Once you have your baseline you can then add your replicas back in one at a time and see how the trace changes.  I would strongly encourage you to use a file  that resides on a volume that does not contain any databases and set a rollover and maximum size.  Please adjust the duration filter as needed to gather events that match up with your waits so that you can further troubleshoot and correlate this with any other teams that need to be involved.

CREATE EVENT SESSION [HADR_SYNC_COMMIT-Monitor] ON SERVER  -- Run this on the primary replica
ADD EVENT sqlserver.hadr_db_commit_mgr_update_harden(
    WHERE ([delay]>(10))) -- I strongly encourage you to use the delay filter to avoid getting too many events back, this is measured in milliseconds
ADD TARGET package0.event_file(SET filename=N'<YourFilePathHere>')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


Quoting Paul Randal here:

  In the FULL or BULK_LOGGED recovery models, the only thing that
  clears the log is a log backup.

http://www.sqlskills.com/blogs/paul/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx


When choosing which structural table design for one-to-many relations

I think some of the confusion is in how you labeled things. Object A and Object B are different from Table A and Table B, because as bbaird points out, Object A and Object B are the same Object (or entity in the context of databases).
From my understanding of your problem description you can think of your data as if it were actual Parents and Children for a second to help conceptualize. Parents could go into one Table and Children in a separate Table but they share close enough characteristics because both are People and probably should live in the same primary Table. Then you can have a second Table that represents the direct relationship of a Child to its Parent. After thinking of it this way, your first design choice makes more sense (which is a pretty common design choice for a Parent-Child relationship set of data.)
Note: The above answer was prior to OP updating his question. Please see the accepted answer for further clarification.


I liked Martin's suggestion of using event notifications, so I followed his example link, and put this together for our server. You'll need to either put your email address in the call to sp_send_dbmail, or modify the procedure to read email addresses from a config table of some sort. Also, adjust the blocked process threshold to your liking. The result is a concise report message, with info about the blocked and blocking processes.

The error handling is really basic; it just ends the conversation if it receives an error message or end dialog message.

Make sure Database Mail is configured on your system to allow the use of sp_send_dbmail.

USE msdb
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'blocked process threshold', 30
RECONFIGURE
GO

CREATE QUEUE BlockedProcessQueue

CREATE SERVICE BlockedProcessService ON QUEUE BlockedProcessQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

USE master
GO

CREATE FUNCTION [dbo].[wait_resource_name](@obj nvarchar(max))
RETURNS @wait_resource TABLE (
    wait_resource_database_name sysname,
    wait_resource_schema_name sysname,
    wait_resource_object_name sysname
)
AS
BEGIN
    DECLARE @dbid int
    DECLARE @objid int

    IF @obj IS NULL RETURN
    IF @obj NOT LIKE 'OBJECT: %' RETURN

    SET @obj = SUBSTRING(@obj, 9, LEN(@obj) - 9 + CHARINDEX(':', @obj, 9))

    SET @dbid = LEFT(@obj, CHARINDEX(':', @obj, 1) - 1)
    SET @objid = SUBSTRING(@obj, CHARINDEX(':', @obj, 1) + 1, CHARINDEX(':', @obj, CHARINDEX(':', @obj, 1) + 1) - CHARINDEX(':', @obj, 1) - 1)

    INSERT INTO @wait_resource (wait_resource_database_name, wait_resource_schema_name, wait_resource_object_name)
    SELECT db_name(@dbid), object_schema_name(@objid, @dbid), object_name(@objid, @dbid)

    RETURN
END
GO

CREATE PROCEDURE StartBlockedProcessNotification
AS
CREATE EVENT NOTIFICATION BlockedProcessNotification ON SERVER FOR BLOCKED_PROCESS_REPORT TO SERVICE 'BlockedProcessService', 'current database'
GO

EXEC sp_procoption 'StartBlockedProcessNotification', 'startup', 'on'
EXEC StartBlockedProcessNotification

USE msdb
GO

CREATE PROCEDURE BlockedProcessActivationProcedure
AS

--Service Broker
DECLARE @message_body xml
DECLARE @message_body_text nvarchar(max)
DECLARE @dialog uniqueidentifier
DECLARE @message_type nvarchar(256)

WHILE 1 = 1
BEGIN --Process the queue
    BEGIN TRANSACTION;

    RECEIVE TOP (1)
        @message_body = message_body,
        @dialog = conversation_handle,
        @message_type = message_type_name
    FROM BlockedProcessQueue

    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('Nothing more to process', 0, 1)
        ROLLBACK TRANSACTION
        RETURN
    END

    IF @message_type = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
    BEGIN
        DECLARE @mail_body nvarchar(max)

        DECLARE @post_time varchar(32)
        DECLARE @duration int
        DECLARE @blocked_spid int
        DECLARE @waitresource nvarchar(max)
        DECLARE @waitresource_db nvarchar(128)
        DECLARE @waitresource_schema nvarchar(128)
        DECLARE @waitresource_name nvarchar(128)
        DECLARE @blocked_hostname nvarchar(128)
        DECLARE @blocked_db nvarchar(128)
        DECLARE @blocked_login nvarchar(128)
        DECLARE @blocked_lasttranstarted nvarchar(32)
        DECLARE @blocked_inputbuf nvarchar(max)
        DECLARE @blocking_spid int
        DECLARE @blocking_hostname nvarchar(128)
        DECLARE @blocking_db nvarchar(128)
        DECLARE @blocking_login nvarchar(128)
        DECLARE @blocking_lasttranstarted nvarchar(32)
        DECLARE @blocking_inputbuf nvarchar(max)

        SET @post_time = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/PostTime)[1]', 'datetime'), 109)
        SET @duration = CAST(@message_body.value(N'(//EVENT_INSTANCE/Duration)[1]', 'bigint') / 1000000 AS int)
        SET @blocked_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)[1]', 'int')
        SET @waitresource = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@waitresource)[1]', 'nvarchar(max)')
        SET @blocked_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)[1]', 'nvarchar(128)')
        SET @blocked_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@currentdb)[1]', 'int'))
        SET @blocked_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)[1]', 'nvarchar(128)')
        SET @blocked_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@lasttranstarted)[1]', 'datetime'), 109)
        SET @blocked_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/inputbuf)[1]', 'nvarchar(max)')
        SET @blocking_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)[1]', 'int')
        SET @blocking_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)[1]', 'nvarchar(128)')
        SET @blocking_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@currentdb)[1]', 'int'))
        SET @blocking_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)[1]', 'nvarchar(128)')
        SET @blocking_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@lasttranstarted)[1]', 'datetime'), 109)
        SET @blocking_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(max)')

        SELECT
            @waitresource_name = wait_resource_object_name,
            @waitresource_schema = wait_resource_schema_name,
            @waitresource_db = wait_resource_database_name
        FROM master.dbo.wait_resource_name(@waitresource)

        SET @mail_body = 'Posted: ' + ISNULL(@post_time, '') + CHAR(10) +
            'Duration: ' + ISNULL(CAST(@duration AS varchar) + ' s', '') + CHAR(10) +
            CHAR(10) +
            '==========Blocked Process==========' + CHAR(10) +
            'SPID: ' + ISNULL(CAST(@blocked_spid AS varchar), '') + CHAR(10) +
            'Wait Resource: ' + ISNULL(ISNULL(QUOTENAME(@waitresource_db) + '.' + QUOTENAME(@waitresource_schema) + '.' + QUOTENAME(@waitresource_name), @waitresource), '') + CHAR(10) +
            'Hostname: ' + ISNULL(@blocked_hostname, '') + CHAR(10) +
            'Current Database: ' + ISNULL(@blocked_db, '') + CHAR(10) +
            'Login Name: ' + ISNULL(@blocked_login, '') + CHAR(10) +
            'Last Transaction Started: ' + ISNULL(@blocked_lasttranstarted, '') + CHAR(10) +
            '----------Input Buffer----------' + CHAR(10) +
            ISNULL(@blocked_inputbuf, '') + CHAR(10) +
            CHAR(10) +
            '==========Blocking Process==========' + CHAR(10) +
            'SPID: ' + ISNULL(CAST(@blocking_spid AS varchar), '') + CHAR(10) +
            'Hostname: ' + ISNULL(@blocking_hostname, '') + CHAR(10) +
            'Current Database: ' + ISNULL(@blocking_db, '') + CHAR(10) +
            'Login Name: ' + ISNULL(@blocking_login, '') + CHAR(10) +
            'Last Transaction Started: ' + ISNULL(@blocking_lasttranstarted, '') + CHAR(10) +
            '----------Input Buffer----------' + CHAR(10) +
            ISNULL(@blocking_inputbuf, '') + CHAR(10)

        EXEC sp_send_dbmail @recipients = 'Your address here', @subject = 'Blocked Process Report', @body = @mail_body
    END
    ELSE IF @message_type IN ('http://schemas.microsoft.com/SQL/ServiceBroker/Error', 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
        END CONVERSATION @dialog
    END

    COMMIT TRANSACTION
END

GO

ALTER QUEUE BlockedProcessQueue WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = [BlockedProcessActivationProcedure],
    MAX_QUEUE_READERS = 1,
    EXECUTE AS OWNER
)


Moving a collection to a new database (v2.2)

A mongodump and mongorestore (or a similar script to accomplish the same thing) to backup and re-import the data is the most straight forward way of doing this at the moment until SERVER-6063 is implemented.

The only other option I can think of would be to move all chunks to a single shard, wipe out the sharding config for the collection (effectively unsharding it) and then do the rename on that shard.  Once that was complete, you could re-shard the collection.

This would be:

Dangerous (messing with the config meta data is always a bad idea)
Unsupported (if you get into trouble, it's probably up to you to fix it)
More complicated (since the first recommended thing to do would be to backup your data via mongodump)

As one other alternative - can you move everything else (perhaps unsharded) out of this database?  Obviously this would not be helpful if you have other large sharded collections in this DB, but just wanted to make sure it was considered.


Oracle 11g max login fail attempts workaround

The account that your application server uses to connect to the database should not lock out under any circumstances. It's a very simple security issue - it makes for the easiest denial-of-service attack in the world. Send n db login requests with random password -> application can't connect. You should definitely set this to unlimited.

(You should also talk to the manager of the DBA if he is really being so willfully unhelpful, but that's a non-technical issue that I don't think Stack Exchange can help you with. :D )


SELinux was installed and it didn't allow access to those files.

Disabling it solves the problem.


Optimize MySQL query and index for multiple column range query

By "10minutesago" do you mean NOW() - INTERVAL 10 MINUTE?

order by priority DESC sorts only on priority.  The ordering within it is unpredictable.  It might be ascending, it might be descending, or seemingly random.

Your INDEX(job_status, priority, lease_date) won't get as far as lease_date.  It will take care of where job_status="IN_PROGRESS" and it might then walk through the rows in priority order.  But that's all.

You could say ORDER BY priority DESC, lease_date DESC, but and lease_date < "10minutesago" will not make use of the index.

Think of building an index thus:  First have any number of columns tested with =.  Then have one range column.

(After OP's edit)

Your example implies that you need

ORDER BY priority ASC, lease_date ASC

As for the question of efficiency, ...

The WHERE clause needs

INDEX(job_status, lease_date)  -- in this order; adding `priority` won't help

If the optimizer would prefer to focus on the ORDER BY, then

INDEX(job_status, priority, lease_date)  -- in this order

Notes:

job_status is first because it is tested with =.
Tacking on just lease_date makes it possible to handle the entire WHERE in the index.  But there will still be an "filesort" to handle the ORDER BY.
Tempting the Optimizer to focus on the ORDER BY with the second index (to avoid the filesort) may or may not backfire.  It depends on how much of the data needs to be skipped over because of the lease_date filter.

If you have a thousand rows in the table, simply add both indexes; the query will be "fast enough".  If you have a million rows, then you are stuck with a tough problem and should rethink the queuing mechanism.  No matter what you do with the indexes, occasionally the wrong index will be used and the query will run painfully slow.  Be aware that a hiccup in the system can make a queue sporadically jump from a civilized number of entries to a huge number.

Do you imply that this table is a "queue"?  How long does it take to perform an item in the queue?  If it is a "short" time, then "Don't queue it, just do it".

Add priority to WHERE

If you change to

select * from jobs
    where job_status = "IN_PROGRESS"
      AND priority = 1
    and lease_date < NOW() - 10 MINUTE
    order by priority
    limit 100

then either of these becomes optimal:

INDEX(job_status, priority, lease_date)
INDEX(priority, job_status, lease_date)

See my Cookbook for discussion.

If you have a mixture of with and without priority, then have these two:

INDEX(job_status, lease_date),
INDEX(job_status, priority, lease_date)

The Optimizer will pick between them.


MYSQL Database PIVOT and wants in specific format

I have tried to make it using GROUP BY in MYSQL, Hope this helps!

SELECT
    channelid,
    deviceid,
    pointtime,
    SUBSTRING_INDEX(GROUP_CONCAT(pointvalue ORDER BY pointid ASC SEPARATOR '^'),'^',1) AS pointid3_1,
    SUBSTRING_INDEX(GROUP_CONCAT(pointvalue ORDER BY pointid ASC SEPARATOR '^'),'^',-1) AS pointid3_11
FROM
    Think.pointhistory
GROUP BY
    pointtime,
    channelid;


Can I use a timeseries DBMS to store video?

From a research study involving participants with a camera, I have chunks of video recording. I have to split them as part of the preprocessing into short sequences of around 5 min long. Each file will be named after its time-stamp in the sequence. I can store them as binary blobs in a database.

It doesn't make much sense to split apart a video, unless for security. It's a container with an audio and video stream. Normally they're stored in a container like AVI, MP4 or MKV (the open standard). In MKV-parlance, you have "Chapters". This is effectively a timeseries database.

Also, you may want to be careful if you're extracting and cutting them. Keep in mind most video streams are compressed, and de-compressing and re-compressing can introduce artifacts and such.

Here are the fields store in the Chapter's data

See Also

My Answer to Should binary files be stored in the database?


Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

Why are you rebuilding the indexes? Do you have any evidence that the rebuild is required/needed/beneficial? Have you considered when to use Reorganize vs. Rebuild?

Consider using one of the many index maintenance scripts like the ones from Ola Hallengren or Michelle Ufford. These scrips have intelligence in them to do appropriate maintenance action according to the index fragmentation and will reduce the offline duration by rebuilding only when truly required (hopefully never).

IF a rebuild is needed then there is nothing you can do w/o going to an Enterprise Edition and using online index rebuild.

Oh, one more thing: do not shrink the log. You are only causing it to take more time when it grows back.


Why is it not possible to alter a view with a "CREATE OR REPLACE"?

It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet.

To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It'd also need to look for whole-row references and disallow changes in those cases.

It's less clear why adding a column is not permitted. Again, I suspect that's down to whole-row references. If pg_depend was checked for whole-row references without finding any, and the new column appeared at the end, it'd be OK to add it.

However, views created with SELECT * FROM wouldn't "inherit" the new column, because * gets expanded into a column-list during view creation, though. So if you had view_A that does a SELECT * FROM view_B, and you added a column to view_B, it wouldn't appear in view_A. Yet if you dropped and re-created view_A, the column would appear. Needless to say, that's not good. To cope with that, PostgreSQL would have to keep track of whether a given view's column list ("targetlist" in PostgreSQL internal terms) came from a * wildcard. Which is more complicated than you'd think because you can write someview.* too.

All in all - it's complicated, and nobody's wanted it enough to do the work to implement it.


creating user related user specific content

Do not create a table for each user!  Create a table for each category of information you want to store.

In a relational database, a table typically represents a list of a particular type of thing which is of interest to your system.  This can be a tangible thing, like users, or an intangible thing, like interests.  The point is that each table is about a kind of thing.  The things themselves are rows in a table.  Every row in a table needs to have the same structure.  If you have no consistent structure for your data then you have to consider a No-SQL database instead.

For relational databases, study the rules of database normalization.  This will help you to understand how to structure your database tables.


The 16.04 seconds is a timer on the client that measures the amount of time that passed between the call and the execution of the query (if you want to be more specific than that, the wall clock time between calling start_timer() and mysql_end_timer(), something that can lead to hilarious results like this one I got).

The reason that you think that it took you more than 30 minutes to execute is probably because it doesn't have into account the output to the stdout. You can check that it really takes 16 second by doing:

mysql> pager cat > /dev/null
mysql> <your query here>
mysql> nopager

If you want to measure how much time takes to write to the standard output, you can do:

$ time mysql -u <your user> -p<your password> <your database> \
  -e "select population1 < 200000 from city"

Note: Are you sure you want to print a 0 or a 1 for ALL rows from that table? Maybe the option --safe-updates and/or using a GUI can help you a bit with your queries?


optimizing a select query of 1.5 million entry

Would that work?

SELECT s0_.id, s0_.lowresimageurl
FROM app_instagram_shop_picture s0_
FORCE INDEX ( idx_deleted_shopId )
WHERE EXISTS (SELECT 1 FROM app_instagram_shop s1_
            WHERE s0_.shop_id = s1_.id
                AND s1_.deletedAt IS NULL
                AND s1_.expirydate IS NULL
                AND s0_.deletedAt IS NULL)
ORDER BY s0_.checked ASC
LIMIT 0 , 30


Does shardCollection acquire lock in mongo db

Yes, the collection is available during the process. Actually, that command sh.shardCollection will take only milliseconds to execute, but actual balancing work will take hours or days to be ready. Balancing will not lock collection, it's just moving chunks from one shard to other one.

Just be careful when selecting sharding key, you should have good knowledge of basics of sharding and how it works.


Does Write-Ahead-Logging allow me to make concurrent changes to a table?

A DELETE and/or an UPDATE are both writes!

Only one process can write at any one time - that is an INSERT, an UPDATE or a DELETE at the same time. You can SELECT with as many processes as you like, but only one of the others at a time!


SQL CLR very slow on Amazon EC2 Instance (SQL Server 2014 CU6)

It turns out that the CLR was not enabled and for some reason the exception wasn't being trapped and SQL Server took about 15 seconds just to say that the CLR was not enabled but during that time the instance is being kept at 100% CPU and hogged down.

In case anyone happens to have the same problem, you can turn the CLR on by running this:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO


How to rotate the CMK of Always Encrypted using SSMS?

If you look at ALTER COLUMN ENCRYPTION KEY, e.g. at MS:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-column-encryption-key-transact-sql

There are options to both ADD a Column Master Key (start rotation) and DROP (finish rotation).


You can use the --quiet option to suppress output including the mongo shell version and startup warnings during the connection process.
Those startup warnings are usually issues an administrator would want to be aware of, but you may prefer to suppress them for a development environment or for running scripts. For a production system you would be best addressing the reported issues to remove future startup warnings.
For example, connecting to a 3.6 deployment with a 4.4 shell:
$ mongo
MongoDB shell version v4.4.3
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("f9219e8d-01ff-46e6-9e9d-b4de68207bdf") }
MongoDB server version: 3.6.17
WARNING: shell and server versions do not match
---
The server generated these startup warnings when booting:
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten]
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten]
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten]
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. Number of files is 256, should be at least 1000
---

Adding --quiet and connecting to the same MongoDB deployment:
$ mongo --quiet
>

The warnings being suppressed in this example indicate:

My mongo shell version (v4.4.3) is much newer than my server version (v3.6.17). Mixing these versions may have unexpected outcomes, particularly given so many intervening MongoDB releases. For a local installation I normally expect these versions to be identical, and for production usage I would want the major versions to be matching (i.e. both v4.4.x or v3.6.x).
Access control is not enabled on this deployment, so anyone can connect with full access to read & write data. This is a serious issue for a production deployment, but I would follow the MongoDB Security Checklist for any deployment where you are concerned about securing your data.
Resource limits are too low, so this deployment may run into problems with too many connections or open files. I would certainly fix this warning for a production environment, but also address for a development environment to avoid unexpected interruptions.


How to create pg_trgm compound indexes with date columns

Please pick identifiers for your example queries which are not reserved words.

The pg_trgm module only supports text-like columns.  Your date column is presumably a date type (or a timestamp, or something like that), not text-like.

To do what you want, you first need to allow date-like data to be indexed in a gin index.  For example, by create extension btree_gin.

Then you can create your index:

create index on bar using gin (col1 gin_trgm_ops, date_column);

You don't need to provide the operator class for the date_column, because btree_gin provides default classes.  The operator class you do provide needs to be next to the column it applies to.  They are per-column, not per-index.

Note that while this answers your question, it probably doesn't solve your problem.  The above index is legal and can be built, but it will not speed up your query (beyond what a pg_trgm indx on just col1 does).  If you want help making the query run faster, you should show us the EXPLAIN (ANALYZE) of the existing query, as well as other information.


Note the MariaDB reference manual (not blog), says 'better fix coming in 5.7', the 5.7 manual includes a big note about native partitioning being deprecated in 5.7.17 and therefore native partitioning is available. So really this advice doesn't apply if you have MySQL-5.7.17+.


Use @@servername and db_name() on notify operator task

Notify Operator Tasks are independent of a database context.

Furthermore, the text that's entered into the Properties dialog box gets passed into a statement resembling the following (obtained by clicking the View T-SQL button):

EXECUTE msdb.dbo.sp_notify_operator @name=N'My Operator',
    @subject=N'Subject',@body=N'Message Body'

So what do you do?

Getting the server context is easily solved by switching to an Execute T-SQL Statement Task where you can put whatever SQL you want in there.

That doesn't solve the database name issue, though, because you'll get back either master or msdb and not any particular user database. I don't believe it's possible using Maintenance Plans to get the database name for what you're trying to do. The Back Up Database Task is entirely self-contained.

What you would need to do is use SSIS proper and put a Back Up Database Task and Notify Operator Task within a loop container, where the flow would be executed for each database individually.

Or, you could take a completely different approach and use a script-based backup solution and customize it to suit your exact needs.


Postgresql - get relation name from relid or RelOptInfo data structure

You want RelationGetRelationName(Relation).

If you don't have a Relation, use get_rel_name(Oid). This uses the syscache so you must have an open transaction. Beware though, it doesn't schema-qualify the name. If you need that, you should probably do a syscache lookup yourself (see the code for get_rel_name in src/backend/utils/cache/lsyscache.c) so you can also get_namespace_name the schema name. Then use quote_qualified_identifier.

I am constantly surprised that Pg doesn't seem to have a public C-level equivalent of the regclass formatting in regclassout. See src/backend/utils/adt/regproc.c.

There's generate_qualified_relation_name, but it's static for some reason. I think I'll submit a patch proposing to move it, generate_relation_name  and get_relation_name to lsyscache.c and make them public.

In the mean time I suggest shameless ripping off what generate_relation_name or generate_qualified_relation_name does if you're doing something where get_rel_name isn't good enough.

Update: patch submitted. Feel free to shamelessly rip off that function.


MS SQL rolling grouped sum for every new data entry

This will get you the desired result set.
It works by 1) creating the missing [object, date, owner] row combinations and then 2) doing a "data smear" to carry the most recent non-NULL amount forward. And 3) filter out any rows that did not have a previous amount to pull from.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

CREATE TABLE #TestData (
    Object CHAR(2) NOT NULL,
    [Date] DATE NOT NULL,
    Amount INT NOT NULL,
    Owner CHAR(1) NOT NULL,
    TypeOwner CHAR(1) NOT NULL
    );
INSERT #TestData (Object, Date, Amount, owner, TypeOwner) VALUES
  ('01', '20180101', 10, 'A', 'X'),
  ('02', '20180101', 50, 'B', 'Y'),
  ('01', '20180101', 30, 'C', 'X'),
  ('01', '20180203', 25, 'B', 'Y'),
  ('02', '20180203', 20, 'B', 'Y'),
  ('01', '20180206', 15, 'A', 'X'),
  ('02', '20180206', 20, 'A', 'X');

--SELECT * FROM #TestData;

WITH
    cte_cross_values AS (   --
        SELECT
            o.object,
            d.date,
            ot.owner,
            ot.typeowner
        FROM (
            VALUES ('01'),('02') ) o (object)
            CROSS APPLY ( VALUES ('2018-01-01'),('2018-02-03'),('2018-02-06') ) d (date)
            CROSS APPLY ( VALUES ('A','X'),('c','X'),('B','Y') ) ot (owner, typeowner)
        ),
    cte_rolling_amtount AS (
        SELECT
            cv.object,
            cv.date,
            RollingAmount = CONVERT(INT, SUBSTRING(MAX(bv.bin_val) OVER (PARTITION BY cv.owner, cv.typeowner, cv.object ORDER BY cv.date), 4, 4)),
            cv.owner,
            cv.typeowner
        FROM
            cte_cross_values cv
            LEFT JOIN #TestData td
                ON cv.date = td.Date
                AND cv.object = td.Object
                AND cv.owner = td.Owner
                AND cv.typeowner = td.TypeOwner
            CROSS APPLY ( VALUES (CONVERT(BINARY(3), td.Date) + CONVERT(BINARY(4), td.Amount)) ) bv (bin_val)
        )

SELECT
    ra.object,
    ra.date,
    amount = SUM(ra.RollingAmount),
    ra.typeowner
FROM
    cte_rolling_amtount ra
WHERE
    ra.RollingAmount IS NOT NULL
GROUP BY
    ra.object,
    ra.date,
    ra.typeowner
ORDER BY
    ra.date,
    ra.typeowner,
    ra.object;

Results:

object date             amount      typeowner
------ ----------       ----------- ---------
01     2018-01-01       40          X
02     2018-01-01       50          Y
01     2018-02-03       40          X
01     2018-02-03       25          Y
02     2018-02-03       20          Y
01     2018-02-06       45          X
02     2018-02-06       20          X
01     2018-02-06       25          Y
02     2018-02-06       20          Y


Specify build and probe tables to hash join?

When you hint a query like that, you force the join order -- you've actually answered your own question.

If you want to test join types and still leave the optimizer with room to explore things, use query options, like this:

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s
ON edh.ShiftID = s.ShiftID
OPTION(MERGE JOIN, HASH JOIN);


How to get SQL Server Profiler to show amended tables

Try a trace on SP:Completed and RPC:Completed with column filters on TextData for:

% insert %
% delete %
% update %

Might get the odd spurious result but it'll narrow the field. Add SQL:BatchCompleted if you have a mix of procs and statements.


How to stop all nodes on FCI SQL server?

So if I want to shut off my active/passive FCI SQL service, I plan to
do the following, are the steps correct?

The only steps you need are these:

Bring down FCI SQL services:

Stop Role on the role that runs my SQL FCI to prevent failover    between active/passive node.

Bring up FCI SQL Services:

Start Role on the role that run my SQL FCI.

With these you can properly manage the role services to be stopped like you need with the least impact. Once the role is stopped you can check on services.msc that SQL Server service stopped if you wanna be sure. It might take some time for that to happen because the service is going to stop gracefully, so wait until the role appears as stopped.

Another option I could think off but unsure its validity, basically
i'm stopping the SQL service and at the same time refuse it to have
the ability to failover to another node.

If you manipulate the services like this from windows services instead of using the Failover Cluster Manager, it's gonna be like fighting against the cluster: you trying to bring the services down and the cluster trying to keep them up.


cron command running postgres at high cpu

You've been hacked.  There has been a recent trend for crypto miners to hide their mining software by naming it 'cron'.  It is not the actual cron.

You probably had a poor password for your PostgreSQL superuser account (probably named "postgres"), which the hackers then used it to download and launch software as the OS user "postgres".  Or, maybe you had a poor password for the OS user "postgres", and they logged into that directly.

The paranoid approach is to nuke the machine from orbit and rebuild it.  The lax approach is to change your passwords (both OS and internal to PostgreSQL) and reboot, and check ~postgres/.ssh/authorized_keys for entries that shouldn't be there.


using a join to find data in a second table?

In SQL Server 2016 or better, you can use STRING_SPLIT. I didn't dig in to all of your rules but here is a general approach. Note that the DECLARE @Tables are just there to mock up data. You'd remove those and change @Table1 and @Table2 in the final query to test against your real tables.

DECLARE @Table1 TABLE(folder varchar(32), OwnerNumber varchar(32));
INSERT @Table1 VALUES('folderx','1,2'),('foldery','1,3');

DECLARE @Table2 TABLE(record int, emailaddress varchar(320), samId varchar(32));
INSERT @Table2 VALUES(1,'user1@domainX.com','user1'),
  (2,'Billy@domainy.com','william'),(3,'Admin@domainz.com','Tom');

-- input params
DECLARE @folder varchar(32) = 'folderx',
        @samID  varchar(32) = 'Tom';

SELECT * FROM @Table1 AS t1
OUTER APPLY STRING_SPLIT(t1.OwnerNumber, ',') AS sp
FULL OUTER JOIN @Table2 AS t2
ON sp.value = t2.record
WHERE t1.folder = @folder
   OR t2.samID = @samID;

Now, of course, with this brittle design, you'll need to protect yourself from values in OwnerNumber like 1,foo,2 but hopefully that's something the home grown application somehow already protects you from.


Dedicated Resources on VMs hosting SQL Server

What you're asking is, "Is it okay if we overcommit CPUs?"

It's okay until you start experiencing performance bottlenecks that are related to CPU consumption. The same answer holds true with network overcommitment as well - it's not like you're putting 5 separate 10Gb Ethernet cards in the host, and dedicating one card per VM. Virtualization is all about overcommitment, and walking the line between resource availability and resource requirements.

Generally speaking, you don't want to overcommit on memory, though, since SQL Server will actively use whatever memory you present to it. Rather than letting the VMs page to the host's disk, you're better off doing VM-by-VM analysis to figure out which VMs could live with less memory, and then configuring them with less memory to begin with.


Just converting the part of comment to an answer so as to close this question:

It seems like someone had the database detached, because of which database was not visible and hence you were also able to view its respective mdf and ldf.

Right Click Databases-->  click Attach--> Choose the respective mdf files for the database in question.

Refresh and check , the database should be visible.


Optimize table and query, aggregate by date or date-hour

Create a new column, update it with the daily value,and add this column in the index.
Here is an example.This is just to remove the function-on-column situation,where an index can`t be used.Be patient,it will take a while on 100 M rows.

SQL Fiddle


AWS RDS MYSQL How do I reduce the DB size?

One idea (wholly un-researched, so there might be a better way, but this should work):

Create a new RDS instance that is smaller in size.
Dump the old one into the new one:

mysqldump -h <old-RDS> -u <user> -p<password> | mysql -h <new-RDS> -u <user> -p<password>

Whack the old one.

Of course, this assumes your total db size is smaller than 20GB, and that you're just looking to end up with a smaller instance. If you're trying to shrink the size of the database, in total, that's a different question.


After reading your posting I understand that when you establish USBLAN_LDR service on windows server 2008, system prompt an error message.

According the information what you provided right now, it’s hard to determine what cause this issue, you may like to post more information about your environment (Event log ,Other  System prompting etc… ).

Please check if it worked with assign a  Local System account instead of Local Service account to run this service.

Open service MMC snap-in. ·
Right UBSLAN_LDR service , and click “properties”.
Click “Log on” tab , and assign a local account for running this
services.

Seems this issue related to a third party software, I suggest to consult software vendor for future troubleshooting.

Please understand that I don't mean to bounce you between different solution provider as I am fully aware how time consuming this can be. However, they really are in a better position to be able to assist you with this issue as they may have experienced similar issues.


How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT?

If you have MySQL 5.7.13 or later, you may use JSON_UNQUOTE() instead of JSON_EXTRACT() or ->> instead of ->. Example:

SELECT field->>"$.foo.barr" FROM table;


Merging multple pg_dumps with columns using auto key

What I would do is,

Start a transaction.
Update all constraints on the database you're merging in to ON UPDATE CASCADE
Set all of the ids to be offset by the max(id) of the table you're merging into, with a comfortable margin (so you know they won't overlap at the time of merger)
Merge them in.
Restart the sequence at a number equal to or greater than the maxid of the table's column.

Sample

CREATE TABLE foo ( foo_id serial PRIMARY KEY );
INSERT INTO foo DEFAULT VALUES;
CREATE TABLE bar ( foo_id int REFERENCES foo );
INSERT INTO bar (foo_id) VALUES (1);

Now

BEGIN;
  ALTER TABLE bar
    DROP CONSTRAINT bar_foo_id_fkey ,
    ADD CONSTRAINT bar_foo_id_fkey
      FOREIGN KEY (foo_id)
      REFERENCES foo(foo_id)
      ON UPDATE CASCADE;
  UPDATE foo SET foo_id = foo_id + 1e6;
COMMIT;

Now you've got,

TABLE bar;
 foo_id
---------
 1000001
(1 row)

Now when you load db2, set the max id to max(foo_id) on the seq.

SELECT setval('foo_foo_id_seq', (SELECT max(foo_id) FROM foo), true);
 setval
---------
 1000001
(1 row)

And, now when the next row is inserted, it calls nextval (under the hood), and gets

SELECT nextval('foo_foo_id_seq');
 nextval
---------
 1000002
(1 row)

If you're doing this kind of a stuff a lot, look at using UUID's instead of integer sequences.


strange situation with very large partitioning table

So you have a huge partition at the end because you forgot to create new empty partitions. You could do this (will be an offline operation):

drop all unaligned indexes
create an empty staging table, with identical structure as your data table (including all aligned indexes) with the proper constraints to accept the last partition
switch the last partition into the staging table. now the data has moved into the staging table, the last partition of the real table is empty
split the partitioning function as needed, since the last partition is empty the operations will be instantaneous. Remember to leave an empty last partition
create a temporary partitioning scheme for the staging table aligned with the partitions in the tail of the real table
rebuild the staging table on the temporary partition scheme created above. Now the staging table is aligned with the empty partitions in the tail of the real table. If the rebuild has log space issues use an online rebuild and take frequent log backups. consider also enabling bulk_logged mode.
switch each partition one by one from the staging table into the real table.
add back any unaligned index you need (you shouldn't have unaligned indexes to start with if you ask me...)
drop the staging table and temp partitioning function/scheme
start maintaining the partitioning so you don't have to repeat this next time.


Oracle - Any way to view uncommited changes to a particular table?

There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes.

1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require multiple days or weeks of humans figuring out where to run a pipeline in one transaction).  Your batch process could create a new workspace (which is logically like creating a new transaction), make whatever changes it would like in that workspace while committing whenever it wanted.  In a separate session, you wouldn't see any of the committed changes until you entered the batch process's workspace.  When the batch process finished, it could merge it's workspace back into the live workspace which is the equivalent of committing a transaction.

2) The DBMS_XA package can be used to allow you to "hand off" a transaction from one session to another and to allow one session to connect to a transaction started by another session.  This is a pretty obscure package to be using, but there was a nice example of using it in the PL/SQL Challenge (you may need a free account to access it) recently.

3) If you're just trying to see the status of the batch process rather than seeing the actual data, the batch process can write logging information using autonomous transactions that you could then query from another session. Or you could use the DBMS_APPLICATION_INFO package to have your application update various attributes in V$SESSION and/or V$SESSION_LONGOPS so that you could monitor the status of the load from another session.


INSERT statements in a transaction and locking a range of rows

If you want to prevent messing up the sequence, you can lock the tables involved when you are doing the inserts. You will have to assess whether the duration of this locking is acceptable or not.

The command would be... Insert into table WITH (TABLOCK) (values).....

The scope of the transaction described in your question would not prevent external inserts from happening. Hence my suggestion of using table locks.


Difference of write authorization on file system with mySQL

Your problem is that MySQL cannot write to your home directory, as by default it will run as the MySQL user, or as some other user you defined when you installed and configured it, if not using the default rpm or other install package. Users are not, by default, able to write to another user's home directory as it is each account's private space.

/tmp is by default writable by all users. As such MySQL can write there. Depending on user groups and umask on your system you may be able to read that output as your user.

If you don't want to use /tmp you could create another directory where your user and MySQL can write and make sure your user and MySQL are members of the same group.


Replacement for Pragma Restrict references

I have several poor answers. I hope one of them works for you.

Moving the packages to another schema may be an option. You might then grant that other schema a bare minimum set of privileges. This would get ugly for name resolution, though.
Auditing the code by searching the schema might be another option:

SELECT * FROM dba_source
WHERE schema = 'ME'
  AND name LIKE 'MYPROC'
  AND (UPPER(text) LIKE '%UPDATE %'
       OR UPPER(text) LIKE '%INSERT %'
       OR UPPER(text) LIKE '%DELETE %');

Defining the package as AUTHID CURRENT_USER would scale back the permissions at runtime to the calling user's permissions. This solution has its own can of worms.


Is it possible to get a history of when a cluster failed over and which node became the active node?

That depends on how your define "history". There will be, in the cluster log, system log, and potentially sprinkled in other logs depending on a few things, the events that show a failover has occurred. For example, in the event logs you might search for event 1069 which would tell you that a resource failed in the cluster.

However, logs don't go back until the beginning of time. So if it's extremely recent history you're after then you might be ok. If you're looking at since inception, the answer is, nope you can't if you weren't already capturing this data.

Extended note: It also depends on how you define failover, as per manual or automatic. Additionally if you have the SQL Server engine errorlog, you can parse it to find when the node name is not the same after startup (FCI failover) or the line items for role synchronization changes (AG).


Regex doesn't work like that. You cannot specify numerical range.

  [] brackets specifies character sets to match. ^ character inside
  [] means negation = anything outside the specified character set. I
  assume you wanted to indicate the start of the string which is also
  marked with ^ but outside of the [] part.

[^1-5] means any character which is not a number between 1 and 5
[^1-50] means any character which is not a number between 1 and 5 and not 0

In more details:

[^1-5] boxes

7 boxes of frogs matches because 7 is not a number between 1 and 5 followed by a spaces and the word boxes

48 boxes of frogs matches because 4 doesn't match (regular machine not in the first step) -> 8 is not a number between 1 and 5 followed by a spaces and the word boxes the actual match is: 8 boxes of frogs

[^1-48] boxes

Doesn't match 48 boxes of frogs because the rules specifies now characters from the set of (1,2,3,4,8) and the string fails on this check.

Fix your regex

In your case probably you want to use name regexp '^[0-9]+ boxes of frog'


Select,From,Join,Where within 1 query MySQL

from the error you've described, I am pretty sure it´s a mere missing single quotes around the word "boy" in your query. That's why removing the where clause it works.

SELECT `customer`.*,order.status
FROM `customer`
JOIN `order` on customer.id = order.id
WHERE customer.type = 'boy'


Transaction replication from SQL Azure database to on-premise SQL server

The following list of article might provide adequate information for you to put together an Azure SQL Data Sync:

Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

  SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

Set up SQL Data Sync (Preview)

(Emphasis mine)

  In this tutorial, you learn how to set up Azure SQL Data Sync by creating a hybrid sync group that contains both Azure SQL Database and SQL Server instances. The new sync group is fully configured and synchronizes on the schedule you set.
  [...]

  Add an Azure SQL Database

  In the Member Database section, optionally add an Azure SQL Database to the sync group by selecting Add an Azure Database. The Configure Azure Database page opens.

  On the Configure Azure Database page, do the following things:

  In the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself.
  In the Subscription field, select the associated Azure subscription for billing purposes.
  In the Azure SQL Server field, select the existing SQL database server.
  In the Azure SQL Database field, select the existing SQL database.
  In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.
  In the Username and Password fields, enter the existing credentials for the SQL Database server on which the member database is located. Don't enter new credentials in this section.
  Select OK and wait for the new sync member to be created and deployed.

  Add an on-premises SQL Server database

  In the Member Database section, optionally add an on-premises SQL Server to the sync group by selecting Add an On-Premises Database. The Configure On-Premises page opens.

  On the Configure On-Premises page, do the following things:

  Select Choose the Sync Agent Gateway. The Select Sync Agent page opens.
  On the Choose the Sync Agent Gateway page, choose whether to use an existing agent or create a new agent.

  If you chose Existing agents, select the existing agent from the list.

  If you chose Create a new agent, do the following things:

  a. Download the client sync agent software from the link provided and install it on the computer where the SQL Server is located.

    Important
    You have to open outbound TCP port 1433 in the firewall to let the client agent communicate with the server.

  b. Enter a name for the agent.

  c. Select Create and Generate Key.

  d. Copy the agent key to the clipboard.

  e. Select OK to close the Select Sync Agent page.

  f. On the SQL Server computer, locate and run the Client Sync Agent app.

  g. In the sync agent app, select Submit Agent Key. The Sync Metadata Database Configuration dialog box opens.

  h. In the Sync Metadata Database Configuration dialog box, paste in the agent key copied from the Azure portal. Also provide the existing credentials for the Azure SQL Database server on which the metadata database is located. (If you created a new metadata database, this database is on the same server as the hub database.) Select OK and wait for the configuration to finish.

    Note
    If you get a firewall error at this point, you have to create a firewall rule on Azure to allow incoming traffic from the SQL Server computer. You can create the rule manually in the portal, but you may find it easier to create it in SQL Server Management Studio (SSMS). In SSMS, try to connect to the hub database on Azure. Enter its name as <hub_database_name>.database.windows.net. To configure the Azure firewall rule, follow the steps in the dialog box. Then return to the Client Sync Agent app.

  i. In the Client Sync Agent app, click Register to register a SQL Server database with the agent. The SQL Server Configuration dialog box opens.
  j. In the SQL Server Configuration dialog box, choose whether to connect by using SQL Server authentication or Windows authentication. If you chose SQL Server authentication, enter the existing credentials. Provide the SQL Server name and the name of the database that you want to sync. Select Test connection to test your settings. Then select Save. The registered database appears in the list.

  k. You can now close the Client Sync Agent app.

  l. In the portal, on the Configure On-Premises page, select Select the Database. The Select Database page opens.

  m. On the Select Database page, in the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself. Select the database from the list. In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.

  n. Select OK to close the Select Database page. Then select OK to close the Configure On-Premises page and wait for the new sync member to be created and deployed. Finally, click OK to close the Select sync members page.
  To connect to SQL Data Sync and the local agent, add your user name to the role DataSync_Executor. Data Sync creates this role on the SQL Server instance.

  Step 3 - Configure sync group

  After the new sync group members are created and deployed, Step 3, Configure sync group, is highlighted in the New sync group page.

  On the Tables page, select a database from the list of sync group members, and then select Refresh schema.
  From the list of available tables, select the tables that you want to sync.
  By default, all columns in the table are selected. If you don't want to sync all the columns, disable the checkbox for the columns that you don't want to sync. Be sure to leave the primary key column selected.
  Finally, select Save.

Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

  SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

  Data Sync is based around the concept of a Sync Group. A Sync Group is a group of databases that you want to synchronize.


does the size of tempdb matter to the buffer pool or does the used size of tempdb matter to the buffer pool?

No tempdb physical size does not matter (you should properly (equally size) you tempdb files (not more than 8) with TF 1118 and 1117 enabled).

  Or does this only have an effect if tempdb is actually in use?

A system with heavy usage of tempdb, you will see many dirty tempdb pages in the buffer pool.

Remember that the Buffer pool is used for caching index and table pages as they are modified or read from the disk. Also, there is a concept called buffer pool disfavoring which prevent pages from a different database being flushed from the buffer pool when a large operation occurs (e.g. bulk activities).

  so wouldn't this have an affect on things like the page life expectancy?

Read up Page Life Expectancy isn’t what you think…


How can I reclaim space after nulling out LONGTEXT records on MySQL?

Yes and no.

In InnoDB, "small" columns are stored with the record in a BTree.  "Large" columns are stored elsewhere.  The cutoff between small and large is complex.  So let me ask how big your LONGTEXT values actually are.

When you shrink something that is stored with the record, it is unlikely to free up any space other than in the one 16KB block in question.  This can be reused if some row is inserted or a column is updated to be bigger.

Or, if two adjacent BTree blocks are only half full, the engine may coalesce them, thereby freeing up a block.  More on free blocks later.

If you are NULLing a large value (let's say more than 3KB), then the space in the off-record storage is freed.  This will free up some number of blocks, depending on the actual size.

What about the freed up block(s)?  Well, they are parts of "extents".  Etc, etc.

Bottom lines:

Nulling a column may free up block(s)
Those blocks may be available for future activity.
The blocks will not be released back to the filesystem of the OS.  That is ibdata1 nor .ibd will shrink.

OK, I told you more than you needed.  Let me re-address the question of "Can I pay for a smaller VM by doing such NULLing?"

Again, Yes and no.

No, the disk allocation will not automatically shrink.

Yes, in that you could take action to give the space back to the FS.  Now, to discuss this...

OPTIMIZE TABLE blocks usage of one table, but shrinks its disk footprint if it had been created with innodb_file_per_table = ON.  Else, no benefit.
Dumping the entire database and starting over by reloading it on a fresh MySQL instance, will release space, regardless of the above setting.  But this is a big task.
Check with the VM provider.  They may have a way of doing that dump+reload.

To get an estimate of the disk space needed, see the SELECT in http://mysql.rjweb.org/doc.php/memory#another_algorithm .  But double what it gives you -- you do need space for various other things.


phpmyadmin is no longer letting me set auto_increment value

https://stackoverflow.com/questions/64214549/mysql-information-schema-bugged/64214609#64214609
MySQL 8.0 tries to cache the statistics about tables, but there seem to be some bugs in the implementation. Sometimes it shows table statistics as NULL, and sometimes it shows values, but fails to update them as you modify table data.
See https://bugs.mysql.com/bug.php?id=83957 for example, a bug that discusses the problems with this caching behavior.
You can disable the caching. It may cause queries against the INFORMATION_SCHEMA or SHOW TABLE STATUS to be a little bit slower, but I would guess it's no worse than in versions of MySQL before 8.0.
SET GLOBAL information_schema_stats_expiry = 0;

The integer value is the number of seconds MySQL keeps statistics cached. If you query the table stats, you may see old values from the cache, until they expire and MySQL refreshes them by reading from the storage engine.
The default value for the cache expiration is 86400, or 24 hours. That seems excessive.
See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_schema_stats_expiry


Manipulate SQL statement before execution on server

So you need to make a choice:

Change to Compatibility Level = 100 and break your code.
Set Compatibility Level = 80 and your code will live a little longer.
Find some way to globally edit your code, dynamically or statically, so that you can switch to Compatibility Level = 100.

It would be interesting to understand why you want Level = 100 when you do not want the code to change.

If you install SQL Server 2008 R2 the SQL Server features available for the system (such as compressed backups) will work without regard to any database's compatibility level.  So, keep your database (a little while longer) in compatibility level 80.

EDIT: Responding to your comment on Mirroring.  Mirroring is a server feature first introduced (for production use) with SQL Server 2005 SP1.  Since this is a feature of the server, it will work for all compatibility levels running on your SQL Server 2008 R2 server.

Therefore, keep your compatibility level at 80 (SQL Server 2000) level and use mirroring on the databases where you need it.

There is a lot of documentation on mirroring, but since this is new to you, you should start by reading Ron Talmage's white paper at: http://technet.microsoft.com/en-us/library/cc917680.aspx

Please note, however, that SQL Server 2008 R2 is the last version to support SQL Server 2000 (80) compatibility. And SQL Server 2008 R2 is no longer under mainstream support since 7/8/2014, though the limited extended support will continue into 2019.

This means that for your SQL Server next upgrade you must upgrade your T-SQL code.

END OF EDIT

Since you mention DDLs and EXEs, you seem to be indicating that your SQL queries are in the client not existing as stored procedures, functions, etc in the SQL Server. If you decide to write a wrapper code for use in your EXEs and DLLs and include it in all the locations.  That sounds as disruptive as just changing the SQL Queries.

Since you are using the *= syntax your code should look like this:

SELECT a.IdColumn, b.Description
   FROM EntityMaster a, EntityDescription b
   WHERE a.IdColumn *= b.IdColumn

So, there is more than a single edit needed to change the code to:

SELECT a.IdColumn, b.Description
   FROM EntityMaster a LEFT OUTER JOIN EntityDescription b
        ON a.IdColumn = b.IdColumn

And I expect you have much more complex code than that.


Understanding Logging Priority & Options in Oracle DB

The database generates redo even without archivelog mode, I guess you meant enabling archivelog mode by logging.
What you asked for is not possible.
Conventional path DML will generate redo, it can not be turned on/off per tablespace.
Direct path loads (INSERT /*+ APPEND */, INSERT /*+ APPEND_VALUES */, CTAS, Data Pump import) and maintenance operations (like table move, index build) can bypass logging, but that is not your everyday workload.
You can exclude tablespaces from the backup, but you can not exclude them from redo.


Postgres 9.6 Physical and Logical replication

I would suggest having the same wal_level on all servers, as as it defines the amount of information you will have when WAL is created (on the primary, these files are then streamed to the standbys).

The available wal_level settings (with each setting writing more information to each WAL, as well as all that of lower levels) from low to high are:

minimal
replica (older values archive and hot_standby are mapped to this)
logical

In this case I would set wal_level = logical for all. As WAL is only created in one place this won't hurt, and it has the added benefit of keeping working if you ever promote a standby to a master.

You mention hot_standby in the diagram, this has now been renamed replica (although both still work).

Other than that this topology should be fine, we use something like this - although you have logical replication from the standby, you'll need to move this to coming from the master. There was a rejected patch to allow this to come from a standby in PostgreSQL 10, hopefully this comes through in PostgreSQL 11.


SQL Server 2008 mirrored Database with manual failover, need reporting database

There's a few questions you need to ask first.

Are you using SQL Server Enterprise Edition?  Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise.

Are you already paying for licensing on the mirror?  There's a lot of fine print, but you basically get the mirror for free - only as long as you're not querying it.  However, if you're going to add snapshots on the mirror and offload the reporting work there, then you'll have to start paying for the mirror.

Are you doing synchronous mirroring?  If so, the mirror hardware needs to be as fast as possible in order to keep up with writes on the primary.  Adding query loads to the mirror may slow down your primary's writes, and that may be a no-go for the business.

Is the business okay with data being as-of-yesterday?  No one can be running a query while you drop and recreate the snapshot, which generally means you can only refresh it after-hours.

Do you have enough space for a growing snapshot?  If you do high-volume activities like index rebuilds or reloading ETL tables, your snapshot can grow - by a lot.  Ideally, if your database is 1TB (and nearly full), you'll need another 1TB free for the snapshot - not right away, but it can happen if you do things like rebuild all indexes.  You'll want to know your change rate, design free space for that, and implement monitoring on both free space and database growth.


How to plan for change to log backup schedule?

If I change to every 15 minutes, do I have to set a new start time?

Nope! The start and end time is when the schedule is active. So, assuming you still want to take t-log backups every 15 minutes all day, then you just need to adjust that value and the when the next hour/half hour occurs it will take the backup.

  So for example, if I set a new job at 10:00am, would I also need to change the start time to 10:00am?

No - because 10AM is still between 12:00AM and 11:59PM, so the schedule will still be active.


Using rownum without an order by will give you random results each time you run your query. To do a proper paging, you need to put this into a derived table together with an order by

select *
from (
  select foo.*
  from foo
  order by some_column -- this is important!!
) t
where rownum between 101 and 200;

For more details please read the explanation on rownum works in the manual http://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns009.htm#SQLRF00255

For a very good discussion on how to do paging efficiently in Oracle (and other DBMS) I can highly recommend this site: http://use-the-index-luke.com/sql/partial-results/fetch-next-page

Edit after revealing the real statement:

My answer doesn't really change: you are not applying an order by in the inner query.

Your current query looks like this:

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
     ....
  ) v
)
WHERE ROWNUM BETWEEN 101 AND 200;

So there is no order by applied to the statement where the rownum is generated.

You need to apply the order by when selecting from derived table named v not inside it  (and you don't really need the rownum as recnum in the inner query either)

SELECT *
FROM (
  SELECT *
  FROM (
     ....
  ) v
  ORDER BY lastupdate ASC
)
WHERE ROWNUM BETWEEN 101 AND 200;


How to create a MongoDB index with isodate?

The order of the fields in the index definition is important.
The indexed values are sorted in the order the fields appear in the spec, so in the index: { _id: 1, Field2: 1,Field4: 1, "Field1.date":1}, the values will be sorted first by _id, then by Field2, etc.
This means that in order to find documents with dates in the range you specified, it would have to examine every index entry.
It that case is probably just as efficient to scan all of the documents.
Instead build an index on { "Field1.date":1, _id: 1, Field2: 1,Field4: 1}.  That will allow the query executor to find all of the documents by scanning a single subset of the index, and it will have all of the projected fields available to avoid fetching any documents.


Speeding up a GROUP BY, HAVING COUNT query

You want the 10 most common values for "groupingsFrameHash" with their respective counts (excluding unique values) - a common task. This specification caught my attention, though:

  a fuzzy approximation is good enough

This allows for radically faster solutions. Postgres happens to store just those approximations in the system catalogs, the total count in pg_class and most common values in pg_statistic. The manual about the nature of these numbers:

  Entries are created by ANALYZE and subsequently used by the query planner. Note that all the statistical data is inherently approximate, even assuming that it is up-to-date.

You have been warned.

Also consider the chapter Statistics Used by the Planner in the manual.

If you have set up autovacuum properly and the contents of your table do not change too much, these estimates should be good. In case you run this query immediately after substantial changes to the table (so autovacuum didn't get a chance to kick in), run ANALYZE first (or better VACUUM ANALYZE if you can spare the time). You can also fine-tune precision, that's beyond the scope of this question ...

There are security considerations. Quoting the manual again:

  pg_statistic should not be readable by the public, since even
  statistical information about a table's contents might be considered
  sensitive. (Example: minimum and maximum values of a salary column
  might be quite interesting.) pg_stats is a publicly readable view on
  pg_statistic that only exposes information about those tables that are
  readable by the current user.

All this considered, you can get quick estimates:

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_stats s
CROSS  JOIN LATERAL
   unnest(s.most_common_vals::text::text[]  -- use your actual data type
        , s.most_common_freqs) WITH ORDINALITY v ("groupingsFrameHash", freq, ord)
CROSS  JOIN (
   SELECT reltuples FROM pg_class
   WHERE oid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
   ) c
WHERE  schemaname = 'public'
AND    tablename  = 'zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    attname    = 'groupingsFrameHash'  -- case sensitive
ORDER  BY v.ord
LIMIT  10;

There are a couple of noteworthy features in this query:

Provide all identifier strings unescaped and case sensitive.
unnest() for multiple arrays requires Postgres 9.4 or later. Details:

Passing arrays to stored procedures in Postgres

pg_stats.most_common_vals is a special column with the data pseudo-type anyarray (not available in user tables). It can store arrays of any type. To decompose, cast to text and then to the array type of your column type. Assuming text[] in the example:

s.most_common_vals::text::text[]

Replace with your actual data type.
I added WITH ORDINALITY to unnest() (Postgres 9.4 or later) to preserve the original order of elements. Since the numbers in the arrays are ordered by descending frequency, we can work with that sort order right away. Consider:

PostgreSQL unnest() with element number

This takes around 1 ms or less - no matter how many rows there are in your table.

Experimental optimizations

If you still need to squeeze out more performance and you have superuser access, you could use pg_statistic directly:

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_attribute a
JOIN   pg_class     c ON  c.oid = a.attrelid
JOIN   pg_statistic s ON  s.starelid = a.attrelid
                      AND s.staattnum = a.attnum
     , unnest(s.stavalues1::text::text[]
            , s.stanumbers1) WITH ORDINALITY v ("groupingsFrameHash", freq, ord)
WHERE  a.attrelid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    a.attname  = 'groupingsFrameHash'
ORDER  BY v.ord
LIMIT  10;

As we are getting closer to the core of Postgres, you need to know what you are doing. We are relying on implementation details that may change across major Postgres versions (though unlikely). Read details about pg_statistics in the manual and comments in the source code.

To squeeze out the last drop, you could even hard-code the attribute number of your column (which changes if you change the position of the column in your table!) and rely on the order of rows returned by unnest(), which normally works:

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_class     c
JOIN   pg_statistic s ON s.starelid = c.oid
     , unnest(s.stavalues1::text::text[], s.stanumbers1) v("groupingsFrameHash", freq)
WHERE  c.oid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    s.staattnum = int2 '6'  -- hard-coded pg_attribute.attnum
LIMIT  10;

Get your own estimates

With the new TABLESAMPLE feature in Postgres 9.5 you can base your aggregates on a (more or less) random sample of the table:

SELECT birthday, 10 * count(*) AS estimate
FROM   big
TABLESAMPLE SYSTEM (10)
GROUP  BY 1
ORDER  BY estimate DESC
LIMIT  10;

Details:

Fast way to discover the row count of a table

Exact counts

If you need exact counts, the best query depends on data distribution and value frequencies. Emulating a loose index scan (like @Mihai commented) can very well improve performance - in a limited fashion, though, (like @ypercube commented) since you need to consider all distinct values for your sort order. For relatively few distinct values the technique still pays, but for your example with ~ 25k distinct values in a table of ~ 100k rows the chances are slim. Basics:

Optimize GROUP BY query to retrieve latest record per user

But first you probably need to tune your cost settings. Using SET LOCAL enable_seqscan = off; is primarily meant for debugging problems. Using it in your transaction is a measure of last resort. It may seem to fix your problem at hand, but can bite you later.

Rather fix the underlying problem. My educated guess is that your setting for random_page_cost is unrealistically high. If most of your database (or at least most of the relevant parts) fit into available cache, the default setting of 4.0 is typically much too high. Depending on the complete picture it can be as low as 1.1 or even 1.0.

The fact that Postgres incorrectly estimates a sequential scan to be faster, while using the index is ten times faster would be a typical indicator for misconfiguration:

Keep PostgreSQL from sometimes choosing a bad query plan


Why database diagrams are not working when domain account is attached to database owner?

You don't need to be sa or another sysadmin account to create database diagrams, but I think you may need to do this in order to initially install database diagram support.

If you do this as sa, you should then be able to let domain\username create diagrams. Most of the resources online will tell you that if domain\username is the database owner it should work, but there is a slight difference when you just set a login as the owner of a database and they aren't already a user inside the database: they get mapped to the special dbo account. I seemed to be able to do this in SQL Server 2012, but I used SQL accounts not domain accounts. So I don't know if that's what is causing your issue, but I think I have provided a valid workaround.


How to tell if Database Certificate Used?

This will provide details about the certificates encrypting any database that has TDE encryption enabled:

use master
select
    db_name(db.database_id) DatabaseName,
    c.name,
    *
from sys.databases db
join sys.dm_database_encryption_keys dek
    on
    dek.database_id=db.database_id
join sys.certificates c
    on
    dek.encryptor_thumbprint=c.thumbprint
where
    db.is_encrypted=1

For all other column level encryption within a user database, backup the Database Master Key, migrate it to the other environment and restore it after restoring the user database.


How to recover a DB with a missing .mdf file?

Technically, you don't have to have an MDF file. Your database and log files can be named any extension - MP3, PDF, ZIP, you name it. (Sometimes I play terrible jokes on my coworkers.)

If you want to check the file names before you detach the databases, run this query in the database you want to examine:

SELECT * FROM sys.database_files

You can also right-click on the database in SSMS and click Properties, then look at the files from there.


The error log of MySQL records the details why MySQL can not restart, check the error log and update it in Questions would be helpful.

If MySQL is installed by apt-get or some other system's ways and uses the default configurations, error log always in /var/log/mysql.

If my.cnf changes, check the modified my.cnf in /etc/mysql/my.cnf or other paths to find the log_error param.


Sql Server Database 2008 R2 is in recovery mode still after 16 hours

It sounds like the session you tried to kill was possibly a runaway and all the work it was doing now has to be rolled back as part of crash recovery. A large log file like that will always take time to finish recovery, especially if it is trying to rollback a large transaction.

This query may give you an estimated time for recovery, but it's not always accurate so use as a guide only.

SELECT command AS [Command],
    DB_NAME(database_id) AS [DB],
    CAST(percent_complete AS DECIMAL(5,2)) AS [PercentComplete],
    DATEADD(SECOND, estimated_completion_time, GETDATE()) AS [ETA],
    wait_resource,
    wait_time,
    wait_type,
    blocking_session_id
FROM sys.dm_exec_requests
WHERE command = 'DB STARTUP'

Once the database is recovered, your temptation may be to shrink that log to remove all the VLFs, however, remember that too few VLFs can cause problems as well. Take a look at this article by Kimberly Tripp where she outlines the algorithm SQL uses to determine the number of VLFs to create during a growth, and some guidelines on sizing your log files correctly.

  What needs to be done for :

  to Kill that particular Session which is creating the block ( Header Block set to 1 in activity monitor)?
  how do i resize the log file and see to it that it has good enough space?
  is it possible to delete the inactive VLF?
  how do i make the database to become online faster ( after restart of 2008 R2 service)?

You probably can't. If it is trying to rollback work it has previously undertaken, you simply have to wait for it to finish. You can try KILL spid WITH STATUSONLY to get the status of the roll back, but the majority of time this doesn't return much useful information.
You need to back it up (to mark log portions inactive), shrink it and then regrow it to your optimal size (see Kimberly's article).
Yes, shrinking will do that, but remember to adjust your growth settings and manually grow the file to a suitable size to control VLF numbers in future. Do not use percentage growth factors and do not use too large an MB growth factor. Remember that log files cannot be instantly initialised like data files can be, so every growth has to be zeroed before it can be used.
You really can't. Recovery takes as long as it takes, and you have to wait it out. You don't want inconsistent data in your database, and part of consistency is the rolling back of failed transactions such as this. You can monitor the DB STARTUP session and check for waits or blockers and see what you can do to remove those obstacles, but you won't likely be able to do much.

You can protect against slow recovery in the future by fixing your log growth settings to prevent excess VLFs and implementing monitoring for runaway sessions. Capping your log files can prevent them from growing excessively, but there is always a risk that legitimate transactions get rolled back due to insufficient log space in that scenario.

Take a look at this article regarding the database recovery process to get a full understanding of what is happening.


Create New Database user to login to SQL Server Management Studio

I had to enable: 'SQL Server and Windows Authentication Mode', then restart the SQL Server 2012 service.


Adding group rights in db2 for a stored procedure

I ran into a similar problem and while this wasn't what happened here, this answer may help others running into this.

Make sure the OS group mygroup is all lowercase. DB2 on AIX does not recognize uppercase letters in the group name and will fail to validate a user belongs to a group if that group name has uppercase letters. eg:

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(mygroup) <- this works

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(MyGroup) <- this does not work

This is documented here: DB2 UDB Security


Is it OK to use WITH INIT for my Transaction Log Backup to avoid having it grow too large?

No, do not use WITH INIT for transaction log backups to the same file. If you do that, you may as well not take them in between full/diff backups.

Typically you will backup the log to a unique file each time. Each one will have some form of a timestamp in the name and have a .trn extension rather than .bak. This is mostly for management purposes - it makes it really easy to identify when a log backup was taken, when it is safe to delete, whether it should be part of your point-in-time recovery operation, etc.


Assuming the column uuid is supposed to be UNIQUE, this table definition should save some space and help performance:

CREATE TABLE public.calls (
   id serial PRIMARY KEY,
   result int4 DEFAULT 0 NOT NULL,
   uuid uuid UNIQUE NOT NULL  -- creates the index you need automatically
   call_start timestamp NOT NULL,
   call_end timestamp,  -- so this can be NULL?
   duration int4,
   recording_duration int4,
   callsid varchar(128),
   destination varchar(256),
   campaign varchar(128),
   request_data varchar(4096),
   status varchar(64),
   recording_file varchar(256),
   recording_url varchar(256),
   recording_text varchar(4096),
   recording_download bool DEFAULT false,
   description varchar(4096),
   analysis varchar(4096),
   is_fax bool DEFAULT false,
   is_test bool,
   hangup_cause varchar(128),
   media_detected bool DEFAULT false,
   sip_callid varchar(256),
   hangup_cause_override varchar(256),
   is_blacklisted bool DEFAULT false,
   sip_error varchar(256),
   hangup_cause_report varchar(128),
   summary varchar(1024)
);

The most important feature here is the UNIQUE constraint, which is implemented with a unique index, and an index is what you need more than anything else (like @ypercube already commented).

If uuid is not unique, create a plain btree index on it.

If uuid is not a valid uuid, leave it as character type (varchar or text), but still create that index.

Size and performance considerations for data type varchar vs. uuid:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

All my other suggested changes are minor improvements. Detailed explanation here:

Configuring PostgreSQL for read performance

If you don't need to enforce a particular maximum length I would just use text for all your character columns. But that has hardly any immediate effect on performance. Some of the columns might be converted to a more fitting type (with an actual performance benefit).

Any downsides of using data type “text” for storing strings?
Should I add an arbitrary length limit to VARCHAR columns?

Indexes and UPDATE

So why does that tutorial page say:

  When should indexes be avoided?
  [...]
  - Tables that have frequent, large batch update or insert operations.

That's misleading by omission.
You desperately need the one index on uuid to support the predicate for your update. All other indexes slow your update down, because they require additional work to keep them current after an update. So if you have no use for the PRIMARY KEY on id (to allow FK constraints to it for instance), you can drop that (and make uuid the PK instead - also indexed automatically).


Why did MySQL remove the query cache feature after version 8.0?

There is a detailed blog from the MySQL server team about this, where Matt Lord says:

The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines.
We considered what improvements we could make to query cache versus optimizations that we could make which provide improvements to all workloads.
While these choices themselves are orthogonal, engineering resources are finite.  That is to say that we are shifting strategy to invest in improvements that are more generally applicable to all workloads.


Difference between fetched and returned tuples in postgresql

If you do select count(*) from million_row_table, one million row wills be returned, but only one row will be fetched.

I can't see I've ever found these fields useful for diagnosing performance problems.  Find your slow query and do an EXPLAIN (ANALYZE, BUFFERS) of it.


Is GRANT DELETE TABLE t TO PUBLIC and the revoke the right to DELETE TABLE t FROM user1 possible?

If something is granted to PUBLIC, that's it.  There is no 'blacklist' or 'grant to public but not to Joe'.  You should manage this with profiles.


After I re-installed SSRS 2014 (with default config) I get HTTP 404 when I try to access the ReportServer or Reports pages

Well I'm not exactly sure what went wrong, but I reinstalled the whole SQL Server and then it worked again (as apposed to reinstalling only a few components last time.


SQL Developer auto trace does not show cascading deletes

Autotrace in SQL Developer gets you the v$sql_plan of your query and the session stats accumulated during the execution of your query - it does not show any recursive SQL that may get executed.

To see that you'd actually want to trace your session.


SqlServer output and return value in procedure

It looks like you are attempting to give the value of @name back to the caller, but that is not the purpose of RETURN. When you give an argument after RETURN it must be an integer which indicates the status of the procedure.

Better off declaring @name as an output parameter or SELECT @name.

Ref. https://msdn.microsoft.com/en-us/library/ms174998.aspx


ORA-01157: cannot identify/lock data file 201

Most likely this is in a database that was cloned from an other database and the tempfiles are not added. Oracle needs a functional temporary tablespace to be able to process joins and sorts. dba_temp_files is a view that does this.
Solution is to simply add a few tempfiles to the temporary tablespace.


After I replaced master mdf/ldf files, can no longer log in

Login details are stored in master, so it makes sense that they would be gone if you've replaced the master database files.

The files in the template data folder are referred to in User Instances for Non-Administrators as "clean system database". I don't have an Express edition to hand to check but I would assume that if your now using a "clean" master database, the only login that would be present is "sa" with a blank password.