PostgreSQL not using index during count(*) I have a COUNT(*) query in PostgreSQL that runs often, and looks like:

SELECT COUNT(*)
  FROM customer
 WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);

This query takes between 30-60 seconds to run and searches millions of records.

EXPLAIN ANALYZE shows it's doing a sequential scan, so I created the index:

CREATE INDEX customer_by_source ON customer (source_id)
WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);

However, EXPLAIN ANALYZE still shows it's doing a sequential scan and isn't using the index.

How do I speed up this query and make it use the index?

Edit: My Postgres version is 9.3.3. The table has about 20 million records, divided pretty evenly among each source_id, of which another 5 aren't included in the list.

If I interpret your added information correctly, you have 21 distinct  source_id with roughly 1 million rows each (divided pretty evenly among each source_id). That means your query counts roughly 3/4 of the whole table. An index typically can't buy much either way.

Test with EXPLAIN (just EXPLAIN is enough for this purpose) to see the estimated cost for each variant:

EXPLAIN SELECT .... -- your query

And take note of all cost= numbers in the output.

Then (only in your session):

SET enable_seqscan = off;

And repeat the procedure. Once more after:

SET enable_indexscan = off;

This should tell you why Postgres chooses a sequential scan. Typically, if your index is substantially smaller than the table (index entries substantially smaller than table rows and / or substantially fewer rows) and if conditions for an index-only scan are met, Postgres will choose that route - unless your cost settings are seriously out of touch with the reality of your setup.

Most importantly, the visibility map has to show that whole pages are visible to all transactions. VACUUM updates the visibility map after writes. Read details on the linked Wiki page.

You can try your query once more immediately after running VACUUM ANALYZE customer;.

If index-only scans are not possible, a sequential scan will most probably be faster than a bitmap index scan, meaning that your index would be of no use.

Update for Postgres 9.6

There was an important restriction for partial indexes, improved in Postgres 9.6. The release notes:

  Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed (Tomas
  Vondra, Kyotaro Horiguchi)

  For example, an index defined by CREATE INDEX tidx_partial ON t(b) WHERE a > 0 can now be used for an index-only scan by a query that
  specifies WHERE a > 0 and does not otherwise use a. Previously
  this was disallowed because a is not listed as an index column.


IntelliSense alternative using SSMS 2012 in a contained database as a contained user? According to this answer about contained database disadvantages:

  If you connect to a contained database as a contained user, SSMS will
  not fully support IntelliSense. You'll get basic underlining for
  syntax errors, but no auto-complete lists or tooltips and all the fun
  stuff. I filed a bug about this issue, and it remains open.

So is there any way to get IntelliSense-style functionality? Can anyone vouch for a product that will work in this scenario?

I really don't think this is a problem with Management Studio, just that the user the tool has to act on behalf of does not have the permission to pull the metadata required to support IntelliSense. So I doubt other tools will fill the need. Your choices are:

Try other tools like SQL Prompt and SQL Complete
Use a server-level login with adequate DB privileges for development (vs. application runtime)
Live without IntelliSense

Hopefully they will fix this problem at some point. Even my Connect item incorrectly blames the UI when the problem is with the permissions (not) granted to the contained user.


"connectivity libraries needed are not found" error in IBM Data Studio UPDATE

I am getting the following error when I try to create a new database in IBM Data Studio v3.1.1.0.

The connectivity libraries that are needed for local or remote non-JDBC operations were not found. To provide these libraries, you can install IBM data server client or a local DB2 server instance.

I have already started the instance using

db2start

command.

After searching exhaustively, I am not able to find any help on the internet regarding this error.

You may need to point IBM Data Studio to the database drivers. They are usually installed with DB2 under /myDB2_InstallationDirectory/SQLLIB/java.

The drivers themselves tend to be

db2jcc.jar
db2jcc_license_cu.jar

There are other drivers in that folder as well, but these two tend to be the main ones for Java (which IBM Data Studio is based on).


Optimze performance for LIMIT / OFFEST on collected result of multiple queries I have large tables in Postgres 10.4 (many millions rows) and three piped SELECT queries. The percentage of rows contributed to the total result for each SELECT is roughly:

SELECT 1 : ~5 % rows
SELECT 2 : ~5 % rows
SELECT 3 : ~90 % rows

I can use UNION and ORDER BY and a RANK column, but it's very slow because Postgres collects rows from all subqueries and applies ORDER BY RANK on the result set.

Is there a way to have SELECT 1 executed, and only if that does not provide enough rows SELECT 2 is executed, etc.

There is a general technique to achieve this, with UNION ALL and LIMIT:

SELECT 1 ...
UNION ALL
SELECT 2 ...
UNION ALL
SELECT 3 ...
LIMIT N;

Postgres evaluates nested SELECTs in order and stops as soon as enough rows have been returned. The rest is never executed.

This optimization does not happen with an outer ORDER BY, which forces Postgres to collect all candidate rows and sort before applying the LIMIT. Nor does it work for UNION (instead of UNION ALL) which also considers all rows before removing duplicates and, finally, the LIMIT.

You need parentheses around each nested SELECT that has ORDER BY or LIMIT in addition to the outer LIMIT.

Related:

How to force order of WHERE clauses in PostgreSQL?
Optimize a query on two big tables
How to give precedence to rows from one table over matching rows in another?
Constraint exclusion to fetch latest N rows


Database Design for NoSQL Fantasy Baseball League I am trying to create a proper schema to place scraped data into, using a MongoDB database.

Currently it looks like

Team {
    id : id,
    name : String,
    teamLink : String,
    username : String,
    userLink : String,
    league : Number,
    Rank : {
        global : Number,
        division : Number
    },
    Points : {
        global : Number,
        division : Number
    }
    Stats : [{
        desc : String,
        value : String,
        globalRank : Number,
        globalPts : Number,
        divRank : Number,
        divPts : Number
    }]
}

League {
    id : Number,
    name : String,
    link : String,
    division : Number,
    acrive : Boolean
    rankings : [{
        week : Number,
        ranks : [{
            team : String,
            position : Number,
            points : Number
        }]
    }]
}

The problem is that a lot of this makes it hard to quickly rank over something as simple as a single stat (e.g. HR). So I am trying to better understand on how to model the data to say, quickly sort over HRs or easily obtain the rankings. I come from a relational db background so I am having a hard time adjusting without joins.

Thank you

Team {
    id : id,
    name : String,
    teamLink : String,
    username : String,
    userLink : String,
    league : Number,
    Stats : [{
        desc : String,
        value : String,
        globalRank : Number,
        globalPts : Number,
        divRank : Number,
        divPts : Number,
        year:Number
    }]
}

League {
    id : Number,
    name : String,
    link : String,
    division : Number,
    acrive : Boolean
}

rankings : {
            week : Number,
            league_id: Number,
            year: Number,
            ranks : [{
                team_id : Number,
                position : Number,
                points : Number
            }]
        }

match {
       id : Number,
       league : Number
       team_visit : Number,
       team_ext : Number,
       ining : Number,
       time_start : Datetime,
       time_game : Number,
       team_visit_score : Number,
       team_ext_score : Number
}

EDIT: explain schema.

First is necessary separate all stats for team in a interval time,
because changes in time for season, years, stats, etc...
Add the match documents, for each match a document. Separate all
for    documents, League, Teams, Ranks, is better for search and
relation    with others team in match, rank...
And the solution is MySQL I'm not so sure about that, because if you
have a lots data in match, league, teams, ranks... Is Better documents with all stats of any team, or all rank in any interval time
because in MySQL you need JOINS for that.


Can I avoid TABLOCKX on DELETE? Goal:

Delete several billion records old records (roughly 600GB) across three tables with zero downtime.

Methodology and Background:

I plan to delete batches of records associated with 1 Million MyIDs at a time (ie MyID BETWEEN 1 AND 1000000). While the delete is running, two of the tables will be very hot but activity on the third table can be safely suspended. In the two hot tables, MyID is the clustering key. In the third, cold, table I have a non-clustered index on MyID.

Besides the DELETE operation, the other activity in the hot tables will consist of INSERT, probably several per second. MyID is an IDENTITY and the newly inserted records will not be in range of any of the DELETE batches.

If it helps understand potential performance, SELECT of an aggregate of a sample batch of these rows takes < 1 second on the tables clustered by MyID and about 2 seconds on the non-clustered table. I do not have a prod replica for this particular database to play in so I cannot say how long the deletes will take, however once I deploy this to DEV I'll have a better idea.

Side note: To address log size, I am wrapping these batches in transactions and upping the TLog backup frequency from 15 to 5 minutes. I have 150GB of available log space.

The Question

I read in the MS docs:

  By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes.

I am very wary of using locking hints, but in this case can I safely use one to avoid TABLOCKX? Are there other ways besides a locking hint to avoid TABLOCKX?

Delete several billion records old records (roughly 600GB)

In this case it's may be more convenient to copy the rows that should remain rather than delete. Note that all 600Gb will move to your log file surely.

  By default, a DELETE statement always acquires an exclusive (X) lock
  on the table it modifies, and holds that lock until the transaction
  completes.

This is wrong. SQL Server never starts with table lock unless the corresponding hint was specified.

  By default, SQL Server will acquire the finest-grain lock possible, in
  order to attain the greatest concurrency. In most cases, this means
  SQL Server will acquire row (RID or  KEY) locks. SQL Server can
  acquire hundreds or thousands of individual locks on data  in a single
  table without causing any problems. In some cases, however, if SQL
  Server  determines that a query will access a range of rows within a
  clustered index, it may instead acquire page locks. After all, if
  every row on a page is going to be accessed, it's easier to manage a
  single page lock than dozens, or hundreds, of row locks. In other
  cases, primarily when there is no usable index to help process a
  query, SQL Server may lock an entire table right at the beginning of
  processing a query.

In your case whith filtering on PK ranges: MyID BETWEEN 1 AND 1000000 it most probably will start with page locks. And here all depends on how many records fit in one page. If locking of 1000000 records with page granularity takes less than 5000 locks you'll be fine with page locks, if you'll have more locks per statement lock escalation will happen and your table will be completely locked.

  ...

  In addition to escalating locks when an
  instance-wide threshold is crossed, SQL Server  will also escalate
  locks when any individual session acquires more than 5,000 locks in a
  single statement. In this case, there is no randomness in choosing
  which session will get  its locks escalated; it is the session that
  acquired the locks.

SQL Server Concurrency: Locking, Blocking and Row Versioning by K.Delaney

So only by testing and monitoring locks acquired you can find the optimal batch size and avoid lock escalation. You can approximately calculate your batch size if you know the average of rows per page in your table. Or you can force page locking by using PAGLOCK and even disable lock escalation on your table:

ALTER TABLE MyTable SET ( LOCK_ESCALATION = DISABLE )


Should every job, SSIS project, SSRS have its own sql login Apologies for asking an opinion type question, but I'd like to know what is best practice.

I have a bunch of sql servers and every job as well as every connection manager for every SSRS report & SSIS project all use one login, sysadmin of course. I see it as a problem for two reasons:

I would like every SSRS, SSIS etc to use their own logins with only the required permissions.
I also would like to know immediately what's causing blocking etc., rather
than seeing one sysadmin login every time and then checking to see
what's running the offending query.

My fellow DBA believes this is madness due to the number of logins required (hundreds per server), suggesting one login per database with the db_datareader role would be more appropriate. Can anyone share links to articles or offer advice?

Thanks

I have a bunch of sql servers and every job as well as every
  connection manager for every SSRS report & SSIS project all use one
  login, sysadmin of course.

Since you asked for best practices, by using sysadmin like that you are breaking The Principle of Least Privilege which states that:

  [...] Administrative tasks are broken out using fixed server roles, and the
  use of the sysadmin fixed server role is severely restricted.

  I would like every SSRS, SSIS etc to use their own logins with only the required permissions.

I'd say creating and managing one login for each user, app, job or services would be going to the oposite extremity and that's a problem too, for, as your fellow DBA said, it would make it a really difficult job to manage so many logins, but you can achieve a middle term by using roles and Windows groups for that matter. Check the Principals doc.

  I also would like to know immediately what's causing blocking etc.,    rather than seeing one sysadmin login every time and then checking to
  see what's running the offending query.

Quoting @Dan Guzman's comment: For the purpose of identification, you can specify the application name in the connection string instead of separate logins.

Once you configured the Connection strings and application name you can retrieve it from sys.sysprocesses by using sp_who2, sp_whoisactive or sys.dm_exec_sessions DMV.


Overcoming the SQL Server Express Edition limitation Microsoft SQL Server 2014 Express edition has a database size limit to 10GB. Now, is that just for a single instance or the overall size that the edition will allow? Or does that means that I can have as many databases using the edition provided each database is less than 10GB?

From Express Edition:

  SQL Server Express includes 10GB of storage per database

and from Features Supported by the Editions of SQL Server 2014, Cross-Box Scale Limits:

  Maximum relational database size: 10 GB

This latter reference makes it clearer that the 10GB limit per database is applied to relational data, so it excludes log files* as well as FILESTREAM and FileTable data (both supported in Express Edition).

* See Database size limitation in SQL Express by Arvind Shyamsundar


Why is turning on Event Scheduler Maxing my CPU? I have search the web for the last week trying to figure this out.
I have exported my tables and events, re-installed the lastest MySQL and imported.
I have tried checking individual procedures.
My situation has me at a complete standstill, here it is:
I install the latest MySQL 8.0 from MySQL Installer on Windows 10 x64-bit, here is the slightly modified my.ini file: my.ini
Whenever I enable event_scheduler (with any form of an activated event, even a single one liner "SELECT * FROM [Table_Without_Data];") which reoccurs every second,
I see an indefinite number of "event_worker" threads opening all of which are stuck in the "opening tables" state and sleeping.
My CPU maxes out, and slows down my whole pc.
Additionally I have 8Gb RAM and an i7-3520M CPU @ 2.90GHz
It does not appear when I check full processlist that anything is frozen because of locks which only leaves me to believe it is something in the server configuration.
I also notice that this will not occur if i start the DB and edit the scheduled event, then set event_scheduler=ON, until I send a query anyhow.
Any ideas? Need any further info?

UPDATE

After extenuating efforts to try to crowd source a solution, I've decided this must be a MySQL bug and I've shared it here: BUG#: 100449 Other reference post is here: Stack Overflow - DDeathlonger

The answer actually has turned out to be a bug which is reproducible - Bug#: 100449


SSRS 2017 - Client found response content type of '', but expected 'text/xml' I've got SQL Server 2017 Developer installed on my workstation, and just installed SSRS 2017. I am able to navigate in the SSRS portal, and upload files of dummy data, and create KPIs. However, when click "+New", then "Dataset" or "Data Source", I get the message "Something went wrong. Please try again later." The only clue in a log file is in my RSPortal_yyyy_mm_dd......log file which shows the following:

OData exception occurred: Microsoft.SqlServer.ReportingServices2010.RSConnection2010+MissingEndpointException: The attempt to connect to the report server failed.  Check your connection information and that the report server is a compatible version. ---> System.InvalidOperationException: Client found response content type of '', but expected 'text/xml'

I get the same error if trying to upload an .rdl file.

I did a "next/next/next" install of SSRS, keeping the defaults. The only unusual thing is that I am logged in with a machine account, not a user account. This machine account does show in the top right corner of the SSRS web portal, so that is passing through at least.

What could be happening here?

I couldn't figure out the specific failure reason here, but found that if I switched to a domain account instead of "Virtual Service Account" or "Network Service" built in to Report Service Configuration Manager and then restarted the machine, things started working properly


Connect tables in a cube based on historical data So this may be the wrong place, and may seem pretty basic, but I'm new to building up cubes, so please forgive me.

Imagine a setup described by the following:

CREATE TABLE [dbo].[Orders] (
    OrderNumber int,
    OrderDate smalldatetime,
    ProductNumber int,
    UnitPrice money,
    Qty int
)
CREATE TABLE [dbo].[Products] (
    ProductNumber int,
    ProductName varchar(30),
    CreatedDate smalldatetime,
    EndDate smalldatetime NULL
)

INSERT INTO Products VALUES (1,'My Product','1/1/2011','5/5/2011')
INSERT INTO Products VALUES (1,'My New Product','5/6/2011',NULL)
INSERT INTO Orders VALUES (1,'1/3/2011',1,1)
INSERT INTO Orders VALUES (2,'6/6/2011',1,1)

SELECT * FROM Orders o INNER JOIN Products p ON o.ProductNumber = p.ProductNumber AND o.OrderDate BETWEEN p.CreatedDate and p.EndDate

As you can see, my product numbers get reused and I need to be able to see what the product was at the time the order was placed. Easy enough to do with SQL, but I'm having trouble trying to build a cube in SSAS that takes it into consideration.

Any help would be appreciated.

First your query doesn't look right, as you aren't accounting for NULL end dates. Fix that first, so that you have a flattened view of both tables.

Once, you have the query working properly, you will use it as your DSV. In the DSV editor add that query as a 'named query'. That named query will be the source for your cube.

Once the cube is created off of that view, add your QTY & UnitPrice as measures. You can use ProductNumber, ProductName, CreatedDate, and EndDate as dimension attributes, depending on what you want to see. The important part is that you build the dimension off of the same named query that the measures came from.

Here is the important part: Once you have the facts and measure setup, go to the 'Dimension Usage' tab on the cube editing window. You should only have one relationship to configure - set it to Fact.

You're done. A date dimension could be useful here, but that isn't related to your question.


SQL Server user cannot select from a table it just created? I'm having trouble understanding permissions in SQL Server (in this case SQL Server 2012, but not sure how version-dependent this issue is). I can create a database, create a login/user on master, create a user for that login on the new database, grant 'CREATE TABLE' and 'ALTER' on the [dbo] schema in that new database, and then (as the new user) create the table but not select from it.

Is there some different permission necessary to SELECT from tables I just created? The end game would be to be able to GRANT various permissions on that table to [public] but if I can't SELECT from it then I'd imagine I can't do anything else on it? (Code follows)

--logged in as 'sa'
    USE [master]
    GO

    CREATE DATABASE [ThisTestDB]

    CREATE LOGIN [TestAcct]
        WITH PASSWORD = 'Passw0rd',
        DEFAULT_DATABASE = [ThisTestDB]

    CREATE USER [TestAcct]
        FOR LOGIN [TestAcct]
    GO

    USE [ThisTestDB]
    GO

    CREATE USER [TestAcct]
        FOR LOGIN [TestAcct]
    GO
--executed all lines above: Command(s) completed successfully

--still logged in as 'sa'
    USE [ThisTestDB]
    GO

    GRANT CREATE TABLE TO [TestAcct]
    GO

    GRANT ALTER ON SCHEMA :: [dbo] TO [TestAcct]
    GO
--execute the above lines: Command(s) completed successfully

--now, logged in as 'TestAcct'
    USE [ThisTestDB]
    GO

    CREATE TABLE tThisTable (
        id      INT IDENTITY(1,1),
        ts      DATETIME,
        Data    VARCHAR(1000))
--execute the above lines: Command(s) completed successfully

--still logged in as 'TestAcct'
    SELECT  *
    FROM    tThisTable
--executed the above, error thrown:
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'tThisTable', database 'ThisTestDB', schema 'dbo'.
*/

Granting schema modification is not, and should not be, the same as granting DML rights.  Grant the user the exact rights that user needs, including 'SELECT', etc, as necessary.

See https://msdn.microsoft.com/en-us/library/ms178569.aspx for details.


Add a flag when certain conditions are met I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.

Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.

Thank you and really appreciate all the help I get from this wonderful group.

Sample Table

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE)
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1'
    UNION ALL
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT Person
    , TransactDate, STARTDATE, END_DATE
    , MIN(IN_PUNCH) OVER (PARTITION BY Person,TransactDate)  AS IN_PUNCH
    , MAX(OUT_PUNCH) OVER (PARTITION BY Person, TransactDate) AS OUT_PUNCH
    ,HOURS
    ,PAYCODE
FROM SampleData

Current Results

Person  TransactDate STARTDATE  END_DATE    IN_PUNCH OUT_PUNCH HOURS PAYCODE
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     3     REG1
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     4     REG2
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     4     LEAVE1
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     2     LEAVE2
1234    08/05/2015   08/05/2015 08/05/2015  08:00    09:00     1     LEAVE1
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     2     LEAVE2
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     1     REG1

Desired Results

Person  TransactDate STARTDATE  END_DATE    IN_PUNCH OUT_PUNCH HOURS PAYCODE FLAG
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     3     REG1    NULL
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     4     REG2    NULL
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     4     LEAVE1  1
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     2     LEAVE2  2
1234    08/05/2015   08/05/2015 08/05/2015  08:00    09:00     1     LEAVE1  NULL
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     2     LEAVE2  NULL
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     1     REG1    NULL

Thank you for the quick help. Unfortunately. it's a 2008 server and I'm not familiar with OUTER APPLY. Can a solution be posted without the OUTER APPLY?

There are any number of ways to express this in T-SQL. One that works (without using APPLY - which you should learn to love, by the way) is below, with comments inline:

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE)
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1'
    UNION ALL
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT
    SD.PERSON,
    SD.TRANSACTDATE,
    SD.STARTDATE,
    SD.END_DATE,
    MIN(SD.IN_PUNCH) OVER (
        PARTITION BY SD.PERSON,SD.TRANSACTDATE) AS IN_PUNCH,
    MAX(SD.OUT_PUNCH) OVER (
        PARTITION BY SD.PERSON, SD.TRANSACTDATE) AS OUT_PUNCH,
    SD.HOURS,
    SD.PAYCODE,
    -- FLAG COLUMN
    CASE
        WHEN
        (
            -- Number of LEAVE1 or LEAVE2 paycodes
            -- for the same person and date
            SELECT COUNT_BIG(DISTINCT SD2.PAYCODE)
            FROM SampleData AS SD2
            WHERE SD2.PERSON = SD.PERSON
            AND SD2.TRANSACTDATE = SD.TRANSACTDATE
            AND SD2.PAYCODE IN ('LEAVE1', 'LEAVE2')
        )
        = 2 --Both LEAVE1 and LEAVE2 are present
        THEN
            -- Decode current paycode
            CASE SD.PAYCODE
                WHEN 'LEAVE1' THEN 1
                WHEN 'LEAVE2' THEN 2
                ELSE 999 -- Impossible
            END
        ELSE NULL
    END AS FLAG
FROM SampleData AS SD;

Output

Using APPLY

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE)
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1'
    UNION ALL
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT
    SD.PERSON,
    SD.TRANSACTDATE,
    SD.STARTDATE,
    SD.END_DATE,
    CA1.IN_PUNCH,
    CA2.OUT_PUNCH,
    SD.HOURS,
    SD.PAYCODE,
    -- FLAG COLUMN
    CASE
        WHEN CA3.LEAVE_PAYCODES = 2
        AND SD.PAYCODE = 'LEAVE1'
        THEN 1
        WHEN CA3.LEAVE_PAYCODES = 2
        AND SD.PAYCODE = 'LEAVE2'
        THEN 2
        ELSE NULL
    END AS FLAG
FROM SampleData AS SD
CROSS APPLY
(
    -- Minimum IN_PUNCH for the current
    -- PERSON and TRANSACTDATE
    SELECT TOP (1) SD2.IN_PUNCH
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    ORDER BY SD2.IN_PUNCH ASC
) AS CA1
CROSS APPLY
(
    -- Maximum OUT_PUNCH for the current
    -- PERSON and TRANSACTDATE
    SELECT TOP (1) SD2.OUT_PUNCH
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    ORDER BY SD2.OUT_PUNCH DESC
) AS CA2
CROSS APPLY
(
    -- Count distinct LEAVEx PAYCODEs
    -- for the current PERSON and TRANSACTDATE
    SELECT COUNT_BIG(DISTINCT SD2.PAYCODE)
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    AND SD2.PAYCODE IN ('LEAVE1', 'LEAVE2')
) AS CA3 (LEAVE_PAYCODES);


Soft ON DELETE CASCADE I am familiar with the ON DELETE CASCADE method, but I don't want to do a real delete, just a soft delete. This is implemented with a column named isDeleted (0/1).

Is there a way for me to cascade this soft delete from a parent to a child? A built in command? or is up to me to write the code?

I am thinking that what I am doing is in fact an update. So I'd be using an ON UPDATE CASCADE, but will that update the same columns on the FKs? or just the primary key?

For what you are describing you would want to use a trigger on the table where you want the soft delete to cascade from.

Here are a few references on triggers:

MSSQLTips

Microsoft's documentation

Triggers have their place but they can quickly get out of hand when triggers fire other triggers or when the trigger execution takes a significant amount of time to complete.

Additionally performance degradation can be a factor with triggers as they happen synchronous when an insert/update/delete statement is made on the table which can extend the execution time of the statement.

Microsoft's Nested triggers

Info from Paul White on triggers

DML triggers for multiple data rows


Omit max entry from each group in postgres? I have a query which produces data similar to the following:

a_key     | a_timestamp | a_value
---------------------------------
345223452 | 2016-03-07  | 1
029370982 | 2017-01-12  | 1
984656834 | 2017-02-03  | 1
928459238 | 2014-08-06  | 2
298338793 | 2015-07-10  | 2
203897932 | 2015-09-12  | 3
123477399 | 2016-05-03  | 3

I need to eliminate the most recent timestamp for each given value.

Keys are all unique.  Values in this query are all non-unique (that is, there will be no value that is not contained in at least two rows) due to filters already applied.

It seems there should be some easy way to do this with an aggregate function or a "group by" clause, but I can't seem to work it out.

How can I filter this result to exclude the most recently seen key for each value?

(If it helps, I only really need the key from the results, not all three columns.)

I am using Postgres 9.3.11.

My first thought would be to use a window function like ROW_NUMBER(), almost identical to your solution.

Here are a few more ways to write this query:

WITH mytable AS
  ( --- the query --- )
SELECT a_key
FROM mytable AS t
WHERE EXISTS
      ( SELECT *
        FROM mytable AS n
        WHERE t.a_value = n.a_value
          AND t.a_timestamp < n.a_timestamp
      ) ;

Using another window function, LEAD():

SELECT a_key
FROM
  ( SELECT a_key,
           (LEAD(a_timestamp) OVER (PARTITION BY a_value
                                    ORDER BY a_timestamp)
            IS NOT NULL) AS ok
    FROM mytable
  ) AS pointless
WHERE ok ;

A variation on the above, using a different condition to check which rows to keep:

           (LEAD(a_value) OVER (ORDER BY a_value, a_timestamp)
            = a_value) AS ok

And a rather weird solution:

SELECT a_key
FROM mytable

EXCEPT

( SELECT DISTINCT ON (a_value) a_key
  FROM mytable
  ORDER BY a_value, a_timestamp DESC
) ;

Modified NOT IN to NOT EXISTS:

SELECT a_key
FROM mytable AS t
WHERE NOT EXISTS
      ( SELECT 1
        FROM mytable AS m
        WHERE m.a_value = t.a_value
        HAVING MAX(m.a_timestamp) = t.a_timestamp
      ) ;

And modified again to a JOIN:

SELECT t.a_key
FROM mytable AS t
  JOIN
    ( SELECT a_value, max(a_timestamp) AS a_timestamp
      FROM mytable
      GROUP BY a_value
    ) AS m
  ON  t.a_value = m.a_value
  AND t.a_timestamp < m.a_timestamp ;

Regarding performance, I did a test with a small 200K rows table (not a subquery), with and without indexes, of the various methods.

Since the query needs to return a large majority of rows (more than 50% and could be close to 100% depending on the distribution), I wouldn't expect indexes to be particularly helpful.

The window function solutions (ROW_NUMBER(), RANK(), LEAD()) performed quite well and similarly to each other (less than 2 sec).

The EXISTS method came a bit slower and last was the DISTINCT ON method (around 3 sec).

The NOT IN method by @joanolo shows a materialized subplan and was really slow (but it may be more efficient if the mytable subquery returns fewer rows). Modified to a similar NOT EXISTS lowered the response time to about 3 seconds. The JOIN modification was somewhat better, around 2 - 2.5 sec.

The plans showed sequential scans of course and most improved with indexes, doing index scans instead (and lowering response time to about 1-1.5 sec for the window functions and the join methods).

(I used (a_value, a_timestamp) and (a_value, a_timestamp, a_key) indexes and variations changing to timestamp DESC but the actual indexes are more or less irrelevant to the specific example, since we have no idea how complex the sybquery is.)


Mandatory to use "Three Parts Name" in T-SQL statements of SP Using three parts name in SQL statements is a good practice it helps in object identification and performance improvement as well. It can also help for many other purposes like if you want to move SPs,View Functions to different database with same T-SQL statement..

So I wanted to create a policy which can enforce developers to use "Three Part Naming" when they'll create SPs, Views and functions.

Is there any inbuilt policy or way in SQL Server 2016 which can help me out?

Response will be appreciated.. :)

Using three-part names in queries that reference objects in the current database is a terrible practice and you should create a rule against it.  The most frequent problem with it is that it prevents you from having two instances of your application database on a single server, and can lead to unintentional cross-database access.
Using two-part names in dynamic SQL has some, slight, performance benefit as the object name resolution doen't have to check the user's default schema and then the dbo schema.  But in a view or stored procedure is not as helpful as object name resolution uses the object's schema.  And in the paradigm case of a proc/view in dbo referencing a table in dbo it doesn't help at all.


ORA-39014 import in Oracle 19c from DMP file of Oracle12c I have been testing out expdp/impdp of databases from 12c to 19c( newly created database) but one db have game me this error:
nohup impdp system/****@CKLPROD CONTENT=METADATA_ONLY PARALLEL=4 DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_CKLPROD_20210317_B419C.DMP LOGFILE=IMP_CKLPROD_20210317_B419C_META.log > IMP_CKLPROD_20210317_B419C_META.out &
> Processing object type
> DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE ORA-39014: One or
> more workers have prematurely exited. ORA-39029: worker 1 with process
> name "DW00" prematurely terminated ORA-39014: One or more workers have
> prematurely exited. ORA-39029: worker 2 with process name "DW00"
> prematurely terminated Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" stopped due
> to fatal error at Wed Mar 17 14:04:14 2021 elapsed 0 00:18:42

the alert log have the following ORA errors:
Line 122: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 355: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 508: ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Line 639: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 781: ORA-00313: open failed for members of log group 1 of thread 1
Line 784: ORA-00313: open failed for members of log group 1 of thread 1
Line 788: ORA-00313: open failed for members of log group 2 of thread 1
Line 791: ORA-00313: open failed for members of log group 2 of thread 1
Line 795: ORA-00313: open failed for members of log group 3 of thread 1
Line 798: ORA-00313: open failed for members of log group 3 of thread 1
Line 803: ORA-00313: open failed for members of log group 1 of thread 1
Line 806: ORA-00313: open failed for members of log group 1 of thread 1
Line 810: ORA-00313: open failed for members of log group 2 of thread 1
Line 813: ORA-00313: open failed for members of log group 2 of thread 1
Line 817: ORA-00313: open failed for members of log group 3 of thread 1
Line 820: ORA-00313: open failed for members of log group 3 of thread 1
Line 2208: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 3062: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 3900: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 4733: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 5055: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 5834: ORA-07445: exception encountered: core dump [ph2csql_idndef_to_diana()+39] [SIGSEGV] [ADDR:0x0] [PC:0x55CDCD7] [SI_KERNEL(general_protection)] []
Line 5853: ORA-07445: exception encountered: core dump [ph2csql_idndef_to_diana()+39] [SIGSEGV] [ADDR:0x0] [PC:0x55CDCD7] [SI_KERNEL(general_protection)] []

It looks like your Oracle instance is failing you. But let's roll it up from the beginning.
IMPDP in Parallel
To execute the IMPDP command with the PARALLEL parameter you have to meet the following requirements:

This parameter is valid only in the Enterprise Edition (check) of Oracle Database 11g or later.

To import a table or table partition in parallel (using PQ slaves), you must have the DATAPUMP_IMP_FULL_DATABASE (check) role.

Transportable tablespace metadata cannot be imported in parallel.

Metadata cannot be imported in parallel when the NETWORK_LINK parameter is also used

The following ojbects cannot be imported in parallel:

TRIGGER
VIEW
OBJECT_GRANT
SEQUENCE
CONSTRAINT
REF_CONSTRAINT.

Reference: 3. Data Pump Import | PARALLEL (Oracle Help Center)
ORA-xxxxx Errors

Disclaimer
Due to copyright restrictions I am only able to provide you with some general links to the Oracle Doc IDs which may or may not provide you with a solution to your issue(s). Registration is required to view this information in full.

ORA-00800 Errors

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], for Oracle Linux (Doc ID 2718971.1) (Oracle on *nix systems)
ORA-00800: Soft External Error, Arguments: [Set Priority Failed], [VKTM] [Check traces and OS configuration] (Doc ID 2551359.1) (Oracle on IBM AIX systems)

ORA-00313 Errors

ORA-00313 at Startup After a New Redo Log Member is Added (Doc ID 1005110.6)

ORA-07445

ORA-7445 Troubleshooting Tool (Oracle Online Troubleshooting Tool)

This option requires you to upload an IPS package to Oracle in order for them to analyse the data. If you create an SR with Oracle and supply the error message ORA-7445 in the case, then you will be automatically redirected to this tool. There is however a downloadable version under that link.
If All Else Fails
There do seem to be issues when trying to import 12c objects into 19c so you might want to contact a certified Oracle consultant or if you have a Support Identifier with Oracle open up a new Service Request.


How to turn this query into a Pivot? If theres something I can't learn, is PIVOT.

People are trying to teach me this, I'm reading blogs about this but I just can't understand the logic.

How can I turn this query , into a pivot?

The query:

select
    case datename(weekday,Vit_DataAberturaReal)
        when 'Monday' then 'Segunda-Feira'
        when 'Tuesday' then 'Terça-Feira'
        when 'wednesday' then 'Quarta-Feira'
        when 'Thursday' then 'Quinta-Feira'
        when 'Friday' then 'Sexta-Feira'
        when 'Saturday' then 'Sábado'
        when 'Sunday' then 'Domingo' end as 'Dia_Da_Semana',
    case datepart(hour,Vit_DataAberturaReal)
        when '0' then '00:00'
        when '1' then '01:00'
        when '2' then '02:00'
        when '3' then '03:00'
        when '4' then '04:00'
        when '5' then '05:00'
        when '6' then '06:00'
        when '7' then '07:00'
        when '8' then '08:00'
        when '9' then '09:00'
        when '10' then '10:00'
        when '11' then '11:00'
        when '12' then '12:00'
        when '13' then '13:00'
        when '14' then '14:00'
        when '15' then '15:00'
        when '16' then '16:00'
        when '17' then '17:00'
        when '18' then '18:00'
        when '19' then '19:00'
        when '20' then '20:00'
        when '21' then '21:00'
        when '22' then '22:00'
        when '23' then '23:00'
        when '24' then '24:00'  end as Hora,
    count(Vit_DataAberturaReal) as Count_Reps_Abertas
from tb_Vitima
where Vit_DataAberturaReal between '2016-01-03 00:00:00.000' and '2016-01-16 23:59:59.000'
group by datename(weekday,Vit_DataAberturaReal),datepart(hour,Vit_DataAberturaReal)
order by 1,2

This is the result:

And this is what I want:

The PIVOT synxtax is:

WITH query as (
    your query...
)
SELECT Dia_Da_Semana, [00:00], [01:00], [02:00], ...
FROM query
PIVOT (
    MAX(Count_Reps_Abertas)
    FOR Hora IN ([00:00], [01:00], [02:00], ...)
) as piv
;

You can refer to: Using PIVOT and UNPIVOT

Your query goes to the query CTE. This query/CTE must return 3 columns:

row name = [Dia_Da_Semana]
column name = Hora
value = Count_Reps_Abertas

The SELECT and the FOR ... IN(...) must contain a list of all the rows to be pivoted into columns.

Why is there a MAX? Pivot needs an aggregate function such as count, sum, max or min. This is because you could have several rows for each pair of name and month and need to group their values. You have only 1 row for each pair of name and hour but you still have to use it although there is only 1 value to add or max. Therefore you could use either of max, min or sum.


Role audit in Oracle 10g Is it possible with Oracle auditing to find out if the role can be revoked from particular user without loosing any privileges that he actually uses?

Something like AUDIT webadmin_role BY webapp_user WHENEVER SUCCESSFUL?

TL;DR: You cannot audit the roles usage.

You can however use the following views to determine which system privileges, object privileges, and other roles are granted to the roles which are in turn granted to the current user:

ROLE_ROLE_PRIVS describes the roles granted to other roles. Information is provided only about roles to which the user has access.
ROLE_SYS_PRIVS describes system privileges granted to roles. Information is provided only about roles to which the user has access.
ROLE_TAB_PRIVS describes table privileges granted to roles. Information is provided only about roles to which the user has access.

The only complicated thing here is privileges and roles inherited from other roles. If it's not the case for you, and the roles granted to your user have one level of inheritance, i. e. these roles are not children of any other roles, then you can just use the following queries to determine the privileges inherited by a particular user from the roles.

Determine system privileges:

select role, privilege
  from role_sys_privs
  where role in (select granted_role from dba_role_privs
                   where grantee = 'SPONGEBOB');

      ROLE            PRIVILEGE
----------    -----------------
 INDEX_MGR     CREATE ANY INDEX
 INDEX_MGR       DROP ANY INDEX

Determine object privileges:

select role, owner, table_name, column_name, privilege
  from role_tab_privs
  where role in (select granted_role from dba_role_privs
                   where grantee = 'SPONGEBOB');

      ROLE     OWNER      TABLE_NAME     PRIVILEGE
----------    ------    ------------    ----------
  HR_QUERY        HR     DEPARTMENTS        SELECT
  HR_QUERY        HR       EMPLOYEES        SELECT

In this example the user SPONGEBOB is assigned two roles INDEX_MGR and HR_QUERY from which he inherits system and object privileges. But what if these roles inherit privileges from other roles? For such case let's implement some more complex model:

APP_USER                  CREATE SESSION
 \
  APP_UNLIM_USER          UNLIMITED TABLESPACE
   \
    INDEX_MGR             CREATE ANY INDEX, DELETE ANY INDEX
            |
        SPONGEBOB
            |
     HR_QUERY             SELECT ON HR.EMPLOYEES, SELECT ON HR.DEPARTMENTS

As you might guess, APP_USER is a role granted to another role APP_UNLIM_USER, which in turn is granted to the role INDEX_MGR. The roles INDEX_MGR and HR_QUERY are granted directly to the user SPONGEBOB. In the diagram, all the privileges granted to the roles are put in front of the roles' names to which they are granted in the corresponding lines, e. g. APP_USER role is granted only CREATE SESSION privilege.

Let's query ROLE_ROLE_PRIVS view described earlier on behalf of the SPONGEBOB user:

select role, granted_role from role_role_privs;

           ROLE       GRANTED_ROLE
---------------    ---------------
      INDEX_MGR     APP_UNLIM_USER
 APP_UNLIM_USER           APP_USER

From the result, you can see that the role inheritance matches the one shown in the diagram. Now, we can use the role names returned by the query to determine what privileges these roles are granted.

First, let's present the roles' names in one column:

select role           from role_role_privs
union
select granted_role   from role_role_privs;

           ROLE
---------------
 APP_UNLIM_USER
       APP_USER
      INDEX_MGR

Then we'll determine which roles are assigned to the user SPONGEBOB directly. As you can see on the diagram above, these are HR_QUERY and INDEX_MGR:

select username, granted_role from user_role_privs;

  USERNAME     GRANTED_ROLE
----------    -------------
 SPONGEBOB         HR_QUERY
 SPONGEBOB        INDEX_MGR

Now, we'll combine the two previous queries together to have all roles' names in one result:

select role         from role_role_privs
union
select granted_role from role_role_privs
union
select granted_role from user_role_privs;

             ROLE
  ---------------
   APP_UNLIM_USER
         APP_USER
         HR_QUERY
        INDEX_MGR

Having obtained all the roles' names, we can now determine which privileges are granted to that roles. (In the examples below I replaced the previous query code with MYROLES for brevity.)

System privileges:

select role, privilege from role_sys_privs where role in (MYROLES);

           ROLE                PRIVILEGE
---------------    ---------------------
      INDEX_MGR           DROP ANY INDEX
       APP_USER           CREATE SESSION
      INDEX_MGR         CREATE ANY INDEX
 APP_UNLIM_USER     UNLIMITED TABLESPACE

Object privileges:

select role, owner, table_name, privilege
  from role_tab_privs where role in (MYROLES);

     ROLE     OWNER      TABLE_NAME     PRIVILEGE
---------    ------    ------------    ----------
 HR_QUERY        HR     DEPARTMENTS        SELECT
 HR_QUERY        HR       EMPLOYEES        SELECT

Since you now have all the privileges shown in a user-friendly format, you can automatically construct the auditing statements to determine which privileges are actually used. For example, the following query will return you the audit statements which you can use to check what system privileges are used:

select privilege, 'audit ' || privilege || ' by spongebob whenever successful;' stmt
  from role_sys_privs where role in (MYROLES);

                                                                STMT
--------------------------------------------------------------------
              audit DROP ANY INDEX by spongebob whenever successful;
              audit CREATE SESSION by spongebob whenever successful;
            audit CREATE ANY INDEX by spongebob whenever successful;
        audit UNLIMITED TABLESPACE by spongebob whenever successful;

The following query returns the object auditing statements:

select 'audit ' || privilege || ' on ' || owner || '.' || table_name
       || ' whenever successful;' stmt
  from role_tab_privs where role in (MYROLES);

                                                STMT
----------------------------------------------------
 audit SELECT on HR.DEPARTMENTS whenever successful;
   audit SELECT on HR.EMPLOYEES whenever successful;

In the same vein, you can construct NOAUDIT statements to stop auditing the privileges.


Single table or multiple tables for optional keys If I have a table with multiple optional keys, is it better to have a single table with nullable keys or multiple tables with not nullable keys.

One benefit I could see is the separate tables would permit all keys to be not null which would be better for data integrity.

SINGLE_TABLE

KEY1       KEY2       KEY3      VALUE
-----------------------------------------
1          NULL       NULL      ValueA
1          2          NULL      ValueB
1          2          3         ValueC

or

TABLE_1

KEY1     VALUE
---------------
1        ValueA

TABLE_2

KEY1       KEY2     VALUE
--------------------------
1          2        ValueB

TABLE_3

KEY1       KEY2       KEY3      VALUE
-----------------------------------------
1          2          3         ValueC

Based on your model, a single table with nullable keys is superior. To find all of the values in the value column, you have to join three different tables and maintain that join in any query operations -- including updates (if you add a key3 to ValueB, now you have to move the entire record to a new table: an insert plus a delete instead of an update).

Better to have null fields so your record -- and any additional columns associated with that record out beyond the Value column -- don't have to be migrated between tables by your business logic tier. Instead, direct update statements can manage these changes.


Do we need wal_keep_segments when we have archive_command? I'm trying to clarify some concepts about how archive_command in conjunction with wal_keep_segments

Please correct me if I'm wrong.

wal_keep_segments: if slaves fall behind wal_keep_segments then downstream connections will fail and we have to recovery all downsteam connections (slaves)

archive_command: if slaves lag behind wal_keep_segments (excess) then pg start to purge oldest segment in pg_wall/to archive/

If two above statements are correct, does pg always wait for wal_keep_segments excess before doing purge segment to archive_command? that mean downstreams need to always failed.

in recovery.conf, I have:

restore_command = 'cp /var/lib/postgresql/11/main/archive/%f %p'

does that mean I don't need to set wal_keep_segments if I've set archive_command?

Edit: I need to clarify some questions:

does file under archive/ folder auto rotate by pg? or we need crontab to clean it up?
in case of disaster, we can manually recovery from archive/, I wonder does we really need to set wal_keep_segments to non-zero number? (or keep it default to zero so all segments will be put in archive?
if we set both wal_keep_segments and archive_command, does pg wait till segments excess wal_keep_segments before purging to archive/ or does it purge immediately?

No, wal_keep_segments and archive_command are independent from each other.

As soon as a WAL segment is finished, it will be archived, no matter how wal_keep_segments is set. wal_keep_segments keeps the file from being deleted at the next checkpoint after it has been archived.


Avoiding two LEFT JOINs? Given these 2 tables:

CREATE TABLE aaa (
  x number primary key,
  y varchar2(10))

CREATE TABLE bbb (
  x number primary key,
  y varchar2(10))

I insert two columns:

insert into aaa values (1, 'foo')
insert into bbb values (1, 'bar')

Then, I query for:

select a.x, a.y, b.y from aaa a
LEFT JOIN bbb b on a.x = b.x

which returns:

1 foo bar

Then, I insert a row with x = 2 into bbb:

insert into bbb values (2, 'bar')

Re-running the above query returns the same result.

However, I'd like to write a query to get a result of:

1 foo bar
2 null bar

Can I achieve that generically without doing:

select b.x, b.y, a.y from bbb b
LEFT JOIN aaa a ON b.x = a.x

Your join clause is the issue here.

The reason the query is returning the same result is because your primary table is aaa, and there is no x=2 record in aaa.

a left join returns everything in the left table (aaa) whether or not it matches the right table (bbb).  Since x=2 does not exist in aaa, it will never join to the x=2 record in bbb.

As Justin Cave states, if you don't want to use bbb as your left table (as you stated) you'll need to use a FULL OUTER JOIN which will return all records from both tables, regardless of whether or not they have a match in the other table.

Something like this should work:

SELECT COALESCE(a.x,b.x) AS x
,a.y
,b.y
FROM aaa a
FULL OUTER JOIN bbb b
    ON a.x=b.x


how can I see the arguments passed to a stored proc on Azure? The query that I'm using:

SELECT
    deqs.execution_count,
    deqs.last_execution_time,
    dest.objectid,
    SUBSTRING(dest.[text], (deqs.statement_start_offset/2)+1,
                        ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                              ELSE deqs.statement_end_offset
                            END - deqs.statement_start_offset)/2)+1) AS statementtext
    , dest.[text]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where dest.[text] like '%foo%'
order by deqs.last_execution_time DESC

That gives me 10 hits, but unfortunately I can't see what arguments were passed in. In regular SQL land I'd just attach the profiler, but that's not an option. This answer on SO makes me think it's not possible, which is hard to believe -- seeing proc arguments is an important debugging tool.

How can I see the last few procs that were run, along with their arguments on Azure?

Today, I think you would have to add logging to the stored procedures (or to the calling application). Trace / profiler are not supported on Azure SQL Database, and never will be.

At some point in the future, you should be able to use database-scoped Extended Event sessions. Bob Beauchemin talked about this here and here, and there is an official marketing statement from Microsoft's Tiffany Wissner here (but nothing technical):

Preview Available for Latest Version of Azure SQL Database

If you have an Azure database and you happen to be on the most recent public version (13.0.15 as far as I can tell), you can start to see some evidence of her statements about Extended Events. For example, I have a context menu item that I can't do anything with yet:

So, it's coming, just not yet. Until then I can't really think of a decent way to obtain runtime parameter values for called procedures unless you change the application to retrieve actual, post-execution plans and log the XML output somewhere. And if you're going to do that, well, you may as well just add the logging of the actual values - much easier to consume.

This answer, of course, is subject to change as it gets older.


I just implemented a service at my company to run ANALYZE TABLE regularly on InnoDB tables.
The reason I did this is that we were affected by this bug: InnoDB persistent statistics not persistent enough
.
When using InnoDB persistent statistics, automatic table statistics are refreshed after about 10% of the rows are modified. There's a counter that keeps track of this, but the counter itself is not persistent; it starts over at 0 if the MySQL Server restarts.
We have some tables that are very large, with hundreds of millions of rows. We also restart MySQL Server regularly, because we stay up to date faithfully on MySQL updates and OS updates. The combination of these two things causes us to do restarts before the 10% of rows are modified, and therefore the InnoDB table stats never update.
So I created a service that visits every database replicaset every day. It checks the table mysql.innodb_table_stats and specifically the last_update column. This will tell me how long it has been since the table stats were updated.
If that value is more than 90 days old for a given table, I definitely need to run ANALYZE TABLE.
Otherwise I do a random sampling and pick 10% of the tables to analyze. This should result in every table getting analyzed on average every ten days.
Naturally by checking mysql.innodb_table_stats, only InnoDB tables are considered, but we made a policy that we only support InnoDB tables in our company.


Create Temporary Access to production support We're thinking of creating a web site where a manager can provide temporary access to a person for a limited set of data in our production SQL azure database for production support.   Auditing is mandatory.  The support user  would need to be their domain account so we can audit the activity.  Write it to a table, add the user to an AAD group that has needed permissions, a logic app would remove that user from the group after x minutes.
Is there an easier way or better solution for this?

We looked, and didn't find a packaged product that met our similar needs. We wanted to be able to get away from having so many developers with dbowner (and even sysadmin) access to databases and instances.
We ended up writing our own desktop application. It doesn't quite match your requirements, but it is close.

The user runs the application, which authenticates them via Windows authentication. There is a lookup that then determines which instances/databases they can request elevated permissions to. And how far they can elevate. All of which has to be approved ahead of time by their supervisor. There are four options for elevation level: (lowest to highest )

R+W – Read and write only on a specific database.
DDL and R+W – You can create objects, change schema on a specific database as well as read and write.
[DB]OWNER – Full access to a specific database.
SYSADMIN – Grants you full access to the instance and all the databases.

They also have to fill out a field to explain why they need the elevated permissions. The program then creates a temporary SQL login for them that they can use to do their elevated work. (We tried to do it by adding them to an Active Directory group, but something didn't work right. I don't remember what it was. We'll try that again some day.)
It logs the fact that they asked for elevated rights to a table that we can then run reports from. We also set up our third-party compliance auditing tool to specifically audit all activities from these temporary elevated logins. The process also sends an e-mail message to the person's supervisor whenever they request elevated access.
We then have a scheduled job that removes these temporary logins at 6:00 a.m. the next day.


Is there any way to speed up a query with 3x UNION on the same large table? Assuming that the following query is legitimate and executes successfully, is there any way to speed it up?

SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes
UNION
SELECT bar from T1
UNION
SELECT baz from T1
UNION
SELECT qux from T1

The four columns shown above are all of type nvarchar(128), non-sparse and nullable. I am using SQL Server 2008. Thanks!

EDIT: Provided more info. I cannot add indices because it is a Stage table. Also, removing the DISTINCTs; I totally forgot about that.

Phil: How do I consolidate this into a single query?

You can certainly project foo, bar, baz and qux in a single scan (SELECT foo, bar, baz, qux FROM T1).  A CROSS APPLY TVF can transform each row (4 columns) into 4 rows (1 column) and then apply DISTINCT on top. If the original plan had 4 scans of T1 which would dominate the cost, this may be better.


Moving all Heap Tables to another Filegroup in SQL Server In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this?

This question is only for Nonclustered Indexes.

https://stackoverflow.com/questions/4237238/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server

In the process of optimizing an older legacy database.

I want to write a script to move all heap tables with a cursor loop similar to the Stack Overflow link above.

As far as I know, you have two options:

Physically move the data.
Create a new empty heap in the secondary filegroup / Copy the data / rename the object, everything inside of implicit transaction. And don't forget to script out indexes, constraints etc.
Create a clustered index in the secondary filegroup, and then drop it.

I'd prefer the first option.


Fetch one value from random row where value is not x I have a games table in my db, which has id column(pk) and slug column(unique) - other columns are not relevant. Now, what I'd like to get is one row, or it's slug value only, which would be random row, but the slug must not match myslug. I've come up with:

SELECT slug, nameslug FROM games
JOIN
  (SELECT ROUND(MIN(id) + RAND() * (MAX(id) - MIN(id))) AS pk
   FROM games) AS rand
WHERE id >= rand.pk AND slug <> '20piaedfl6ah'
LIMIT 1;

but it sometimes returns false as it doesn't check if that row's slug column value is the same at the point where it searches for random row.

At a glance, I'm not convinced that your original query is too far from wrong.

Unless, I'm greatly mistaken, the only time it should be unable to return a results is when the target slug happens to have the MAX(id) as its id, and when that row is the only valid row to consider. If the only id greater than or equal to the randomized id value you've calculated is the one eliminated by the other WHERE condition, there are no rows to return.

If that's the case, then something like this may work:

SELECT slug, id, rand.pk FROM games
JOIN
  (SELECT ROUND(MIN(id) + RAND() * (MAX(id) - MIN(id))) AS pk
   FROM games WHERE slug <> '20piaedfl6ah') AS rand
WHERE slug <> '20piaedfl6ah'
  AND id >= rand.pk
LIMIT 1;

Here's a fork of @Vérace's DBFiddle with your original query and my modification of it. I made sure that the row to avoid has the highest ID

Here, we've ensured that neither end of our range matches our row to avoid. As long as that's true, then we should be OK with any value in our range. If the rand.pk matches the slug to avoid (or matches no rows at all - I assume there may be gaps in the ID sequence), then we know there's at least one row available that's OK - the one that matches our MAX(id)value (which cannot be either a gap row, or the row to avoid).

In numerous runs of the DBfiddle, I never saw the second query (which is the one protected against not finding a match) take longer than the first - however, I must note that the time to set-up the schema is included there.

And, of course, if there's only one row available, and its slug matches, you would still find no rows - however, I'd hold that in that case, finding no rows is the correct behavior.


Find Max() updated time for a group of users Database is MariaDB 10.3.25
I have 2 columns that are relevant:
UserID and LastUpdate
UserID consists of user@domain
LastUpdate is a date field.
Here is my issue - I have the current query:
select a.UserID,
substring_index(a.UserID, '@', -1),
max(a.lastupdate)
from MyTable a
group by a.UserID
having max(a.lastupdate) < '2020-03-31'

This shows all the Users that haven't updated in just over a year and the domain. However there is the following scenario that I want to account for:
UserID LastUpdate
A@A.com 2020-08-16
B@A.com 2019-05-16
C@A.com 2021-05-05
With the current query, B@A.com will be captured, and therefore domain A.com will be captured as not in use, but the user C@A.com is still active - I want to do a Max(a.lastupdate) but have it grouped by  substring_index(a.UserID, '@', -1)
but also spit out all the users for that domain.
I'm sure the answer is starring me in the face...

See if its what you want
-- this main query retrieve all users under the subquery domain
SELECT a.UserID
    , substring_index(a.UserID, '@', -1)
    , MAX(a.lastupdate)
FROM MyTable a
WHERE substring_index(a.UserID, '@', -1) IN (

    -- this subquery search all domain inactive in last 12 months
    SELECT substring_index(a.UserID, '@', -1) AS domain
    FROM MyTable a
    GROUP BY substring_index(a.UserID, '@', -1)
    HAVING MAX(a.lastupdate) < '2020-03-31'

)
ORDER BY substring_index(a.UserID, '@', -1), a.lastupdate


Error Referencing wp_users as Foreign Key I'm fairly new to SQL. I'm trying to create a FK for one of my tables (time_sheet) to one of the Wordpress tables (wp_users). But I'm hitting an error and I'm not entirely sure why that is.

This is my SQL statement to create the table time_sheet:

Create table Time_Sheets
(
 id bigint AUTO_INCREMENT NOT NULL,
    project_id varchar(255) not null,
    username varchar(250) not null,
    task varchar(255) not null,
    description varchar(255) not null,
    hours int(10) not null,
    time_date varchar(255) not null,
    PRIMARY KEY (id),
    FOREIGN KEY (project_id) REFERENCES Time_Sheet_Projects(project_id),
    FOREIGN KEY (task) REFERENCES Task_List(name),
    FOREIGN KEY (username) REFERENCES wp_users(display_name),
    CHECK (hours > 0)
)

Here's an image of the Wordpress database schema:

When I take out this line FOREIGN KEY (username) REFERENCES wp_users(display_name), the query run perfectly fine.

I've check the name/column name and they are correct. I'm not sure why this isn't working.

show create table wp_users

CREATE TABLE `wp_users` (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_status` int(11) NOT NULL DEFAULT '0',
 `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 PRIMARY KEY (`ID`),
 KEY `user_login_key` (`user_login`),
 KEY `user_nicename` (`user_nicename`),
 KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Edit: So it turned out the database engine being different was the problem. After switching wp_users to InnoDB, everything is working nicely.

The MyISAM engine doesn't support foreign keys. Use the InnoDB engine instead.

Other observations:

The referenced table has no index on display_name. As per documentation, it is required to have an index on the referenced field.

It is better however to use the ID field from wp_users table to be referenced, and use this ID to lookup display_name, like:

FOREIGN KEY (user_id) REFERENCES wp_users(id)

In addition, change the field in the time_sheet table from username varchar(250) not null, to user_id BIGINT not null...


Inconsistency between maxbytes & bytes. Oracle Query:
Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1,
ROUND (SUM (d.maxbytes) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME'

Output:

size1: 70, size2: 64

As you can see the max bytes allowed for the data files for a particular table-space is only 64GB however it has somehow extended to 70GB!!
Scenario: At a given point the table-space got completely filled (64GB) and I saw 100% used in one of my routine checks. So I went ahead and temporarily truncated some unwanted tables to free up some space. I didn't encounter the cannot extend error anywhere in any of my processes (although it might have occurred) but what bugs me is that the total size of the table-space just increased from 64GB(max) where it should have stopped to 70(gb).
Please advise how it could have happened or any option I have missed !! and where is the extra 6 GB being stored.
Thanks in advance.
Edit:
Output of :
select bytes,maxbytes,autoextensible
  from dba_data_files
 where tablespace_name = 'TABLESPACE_NAME';

15728640000 4294967296  YES

30303846400 32212254720 YES

28626124800 32212254720 YES

MAXBYTES is 0 for datafiles that are not autoextensible, but BYTES is greater than 0. Use this:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1,
ROUND (SUM (decode(d.maxbytes, 0, d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';

But it is possible to manually resize a datafile above maxbytes.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7    5242880   10485760

SQL> alter database datafile 7 resize 20M;

Database altered.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7   20971520   10485760

I updated the query:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1,
ROUND (SUM (greates(d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';


Rsync'ed postgres databases won't allow log-on, gets "cache lookup failed for relation" error Running postgres 9.4.4 on a SLES11 SP4 Machine.
I'm rsyncing an entire machine over the Internet to create a copy of the operating system and when I try running postgres (the service starts).  I stoped the postgresql service before running rsync.
Logging in to my database (that works on the machine I rsynced from), I get

$ psql -U [my_username] [my_database]
psql: FATAL:  cache lookup failed for relation 1259

Searching here and there shows me lots of info for this error, but most of them appear to be for people who can actually log in to their database.
I can log in as the postgres user, but I don't think that user can fix it.
I've tried rsyncing again, in case something was lost in translation, and got exactly the same error.  So something must be gummed up.   I've rebooted, restarted postgresql.  Ran vacuum (as postgres, probably doesn't touch the busted relation).
I'll probably have to go hack on the config files to give the postgres user access to the tables, but short of that, are there any ways I can fix this.  I really only need the schema to survive the rsync, the data can (and will) get blown away and repopulated by another machine.

Make sure that postgres is killed on the side you are rsyncing from as well as the side you rsynced to (you goofus).

Smart Peter: After you ran rsync what files did it copy?
Goofus Peter: Um almost everything, except the network stuff, I needed that
Smart Peter: so,  everything like all the pid's and cron and everything
Goofus Peter:  I guess so, but postgres was off on the machine I was copying from
Smart Peter: Well, was postgres on on the machine you were copying too?
Goofus Peter: Oh crap

... Later ...

Goofus Peter: Hey, it still didn't work, and now I can't even start postgres!
Smart Peter: Is it running
Goofus Peter: How could it be running if I couldn't even start it?
Smart Peter: Because it was running all along!
Goofus Peter: (gets cut off)
Smart Peter: Because you have a cron job that restarts it
Goofus Peter: But I killed cron
Smart Peter: Did you kill it, or did you stop the service
Goofus Peter: Stopped the service...
Smart Peter: So basically, you left the bodies and moved the headstones?
Goofus Peter:  OK I'll make sure everything is dead and call you back

... Time passes ...

Goofus Peter: OK everything worked
Smart Peter: Great, I'll go tell stackexchange!


--replicate-wild-ignore-table doesn't work? According to the document about the replication and temporary tables, I'm setting --replicate-wild-ignore-table option to the %.norep% to prevent temporary tables from being replicated:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000861
          Read_Master_Log_Pos: 332610533
               Relay_Log_File: mysqld-relay-bin.001578
                Relay_Log_Pos: 9061317
        Relay_Master_Log_File: mysql-bin.000861
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: %.norep%
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 330271548
              Relay_Log_Space: 13544924166
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 4

But as you can see from the above show slave status\G output, this statement is still replicated to the Slave and it causes the error.

Is there any problem with my query? Does it has only effect with the main tables (I mean ox_banners in this case)?

Reply to @Aaron Brown:

  This may be somewhat surprising, but this error is proof that your
  replication filter is working.

+1.

  The first question to ask yourself is why am I filtering replication
  at all?  There is rarely a good reason for this and it usually
  causes far more problems that it solves.

Due to the problem with temp tables: How to make MySQL replication reliable?

  You can  definitely work around the problem by using Row Based
  Replication (RBR) instead of Statement Based Replication (SBR).
  RBR replicates the changes to the data instead of the statement.

I will consider switching from mixed-based to row-based.

This may be somewhat surprising, but this error is proof that your replication filter is working.  The statement that has stopped working is an UPDATE to another table, but the update is using data from the table that you have said not to replicate in the SELECT clause.  Notice that the error is that the table norep_cp_banner_tvc_temp_ doesn't exist.  Replication filters only are honored on the tables being affected - it's not just a straight pattern match.

Replication filters are somewhat complicated - I suggest that you read up on How Servers Evaluate Replication Filter Rules and Evaluation of Table-Level Replication Options.

The first question to ask yourself is why am I filtering replication at all?  There is rarely a good reason for this and it usually causes far more problems that it solves.

You can  definitely work around the problem by using Row Based Replication (RBR) instead of Statement Based Replication (SBR).  RBR replicates the changes to the data instead of the statement.

I also suggest that you refactor your UPDATE as a join.  IN (SELECT...) is very optimized poorly in MySQL pre-5.6 and this will be much more performant:

UPDATE `ox_banners` A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1;

Another way that you might be able to work around this problem is by updating a dummy field in the norep table.  Something like this:

UPDATE ox_banners A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1, temp.dummy = 1;

That might trick MySQL into filtering this statement, but it's a hack to say the least.  RBR is likely your best bet.

UPDATE 2012-07-24

You mentioned that you are doing this to work around some of the problems with temporary tables being used with replication.  Filtering them out entirely is a terrible way to handle that problem.  Consider doing this instead, if you are worried about it.

Create a separate database to hold your "temporary" tables

mysql> create database temptables;
Query OK, 1 row affected (0.08 sec)

mysql> use temptables;
Database changed

Namespace the tables by using your CONNECTION_ID...

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              52 |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE 52_my_temp ( foo int ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)

Use the table as normal, then
DROP the table

mysql> DROP TABLE IF EXISTS 52_my_temp;
Query OK, 0 rows affected (0.05 sec)

Finally, create a process that cleans the tables up every so often, just incase the DROP TABLE didn't get executed.  Something like this (Ruby Sequel syntax here):

# get a list of all the tables in the temp database
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TABLE_SCHEMA=?"
db[query, 'temptables'].each do |row|
  # get the connection id
  id = row[:TABLE_NAME].split('_')[0].to_i
  # is the connection still active?
  query = "SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = ?"
  if db[query, id].first[:cnt] < 1
    db["DROP TABLE #{row[:TABLE_NAME]}"]
  end
end


Load balancing with Windows Clustering and AlwaysOn Availability Groups I have some questions regarding Clustering and AlwaysON.

If I need to build a 3 node cluster - 2 active and one passive. Each node will be 16 CPU and 128 GB RAM. Does this mean that I will have double resources. Will I get the max thread number (704) for each node?
For the 2 nodes accessing the same storage, will this lead to more
blocking?
For AlwaysOn, can I set 2 primary servers instead of one so I can
get some kind on load balancing. If yes how will the data be
synchronized?

QUICK ANSWERS TL/DR

Sort of, you will double the resources but every instance will only be active on one node at the same time.
Any SQL Server instance will have exclusive access to it's storage on the node where it's currently active - If all the storage for multiple instances comes from the same SAN one instance can affect the others
No you cant, sorry

You can create a 3 node cluster. But in SQL Server terminology active/active clustering means that you can run separate instances on any of the cluster nodes which then can fail back to any of the other nodes as clustering is for redundancy or high availability and nothing is shared between the database services.

You can use Always On the create read only replicas of the data which can be used to take some read only load of the server. All of this is explained in details in the High availability whitepaper

Now if you want or need to create a distributed load balanced setup you can try to implement peer to Peer transactional replication replication, you can create multiple distributed databases, using in memory objects in SQL Server to handle the transactional load and offload the data to other objects/databases. But the only transactional RDBMS that can, out of the box,  run a single database in shared everything mode on multiple hosts is Oracle RAC (or maybe ScaleDB) and that comes with it's own set of problems.


Confused about indexing for IS NOT NULL I have a PostgreSQL table with a timestamp field processed. I run a lot of queries like this:

SELECT blah1, blah2 FROM tbl WHERE processed IS NOT NULL

According to this question, I can create a partial index like this to speed this up:

CREATE INDEX idx_tbl_processed
    ON tbl (processed)
    WHERE processed IS NOT NULL;

My question is, with this partial index, wouldn't the database have to look up rows with processed not null (which is not fully indexed, so must be slow) before it gets to the indexed part of the table (which in this case is processed itself!)?

In general, does a partial index conditioned on some_column make queries on some_column itself faster?

My question is, with this partial index, wouldn't the database have to look up rows with processed not null (which is not fully indexed, so must be slow) before it gets to the indexed part of the table (which in this case is processed itself!)?

No, an index on IS NOT NULL has no need in itself to visit the table on queries restricted to IS NOT NULL or on queries which can be determined to be not-null. In fact, on queries restricted to the column where the value IS NOT NULL, you could potentially be doing an INDEX ONLY SCAN which I demonstrate here.

CREATE TABLE foo AS
  SELECT x::int, null::int AS bar
  FROM generate_series(1,1e6)
    AS gs(x);
UPDATE foo
  SET bar = 42
  WHERE x = 185093;

CREATE INDEX ON foo (bar)
  WHERE bar IS NOT NULL;

VACUUM ANALYZE foo;

EXPLAIN ANALYZE SELECT * FROM foo WHERE bar = 42;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using foo_bar_idx on foo  (cost=0.12..8.14 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)
   Index Cond: (bar = 42)
 Planning time: 0.295 ms
 Execution time: 0.047 ms
(4 rows)


I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this? I'm quite new to the process of importing/exporting databases.
I'm using Maria DB, and HeidiSQL to run queries, etc.

I exported an entire database from our dev server, and loaded it into our live server. The script ran without a hitch. No errors, everything appeared to go as planned.

However, when I open two instances of HeidiSQL and compare the details of the tables side by side there are some weird looking discrepancies.

For example, in some cases tables on the newly migrated version say they have more data than the database they were exported from. How is this possible? does HeidiSQL just 'estimate' the amount of rows?

For example, according to HeidiSQL table 1 on the dev server contains 177 rows of data, with a total size of 64.0 KiB.
Where as the same table 1 on the live database contains 219 rows of data, with a total size of 64.0 KiB.

Even more weird, table 2 contains 2,254,576 rows of data, with a total of 232.9 MiB, while the same table on the live database contains 2,334,922 with a total of 224.0 MiB.
There are also tables showing less data on the live database version than on the dev database version.

Why is this?

The SQL file creates the copy of the database from scratch on the live database server, it didn't delete or overwrite an older version or anything, and there were no errors on completion.

EDIT:

If I run SELECT COUNT(*) FROM table_2 both databases return 2,326,087 rows.
So it appears to be an issue with displaying the correct count in HeidiSQL.

From the symptoms, it seems that you are using InnoDB engine for your tables. If so, it is normal not to have accurate number of rows shown by clients (Like HeidiSQL or PHPMyAdmin).

It is also worth it to mention that SHOW TABLE STATUS LIKE 'table_name' does not provide accurate row count for InnoDB tables.

InnoDB estimates the number of rows in the table. If you want the exact count, you should run SELECT COUNT(*), as you just did.

MyISAM, on the other hand, has the information of its number of rows saved and 'always' up to date.

In regards to physical file size, there are many factors that leads to tables that have same date to have different file size on disks. Some are related to the operating system itself, and the file system used; some are related to the configs of MySQL; and some are related to the fragmentation of the data when doing deletes and updates. In short, this difference is very normal.


postgresql rename index I want to understand how safe is the rename command:

ALTER INDEX old_name RENAME TO old_name;

The index was created by command(for the foreign key column):

CREATE INDEX old_name ON table_t1 (user_id);

Does it mean simple update in the postgres system tables or do much more operations. I try to find it in the docs but unsuccess.

Quote from the manual

  The RENAME form changes the name of the index (...) There is no effect on the stored data

(emphasis mine)

So yes, this is a simple update to the system catalog.

The statement sill needs an exclusive lock on the table if I'm not mistaken, but the lock will only be held for a very short period.


Unable to remove trace from sys.traces We recently discovered one of our drives was being filled up by audittrace files. One of our DBAs turned off C2 auditing which should normally stop the files filling up the drive. However, it appears turning off C2 auditing has not had any affect.

I've tried looking at the existing traces to determine why the files are still being written.

SELECT * FROM sys.traces

ID    Status    Path
1     1         \\?\D:\MSSQL11.MSSQLSERVER\MSSQL\Data\audittrace20161113132900_260.trc
2     1         D:\MSSQL11.EPDM\MSSQL\Log\log_485.trc

ID 2 is currently set to the default but cycles after a max 5 files. This trace isn't the one filling up my hard drive.

ID 1 path and trace file are the ones filling up.

I've already turned off C2 auditing. What other troubleshooting can I do to see why the audittrace files are even being written to in the first place?

EDIT: I've restarted the services. It would seem the correct solution would be to turn off this audit trace by using the sp_trace_setstatus 1,0 but when I run that command I get the error:

Msg 8189, Level 14, State 32, Procedure sp_trace_setstatus, Line 6
You do not have permission to run 'SP_TRACE_SETSTATUS'.

I found the following information at this link which might be of some help:

SELECT * FROM sys.traces
WHERE path LIKE N'%audittrace_%'

Find the trace that is called audittrace.trc:
--Stop the trace
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 0

--Delete the trace definition from the server
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 2


Database replication from local Postgres to live I have a software whose data from its local Postgres server has to be synced to live database, so that I can query data from that live Postgres server via api. I am new to Postgres. So after a small research I came across Database replication concept such as hot standby, streamed replication, wal files, background processes etc.
My question is whether it is possible to replicate my local Postgres database to live Postgres database and also access or query the live Postgres database for CRUD operations and also I want both the databases to be in sync irrespective of the database on which the CRUD operations are being performed.
Found similar questions already answered.

How to PUSH data between a Local DB Server to Cloud DB Server?
Streaming replication Postgresql 9.3 using two different servers (can we write on the copy database?)

What you seem to be asking for is multi-master replication, and there is no built-in or open source solution for that. There are a couple of closed source solutions.
You should consider changing your requirements. Multi-master replication is conceptually difficult, and you usually end up with fewer headaches if you choose a different architecture.


Difference between fsync and synchronous_commit - postgresql What is the difference between fsync and synchronous_commit? I read the documentation, the only thing I understood is both of them are trying to make updates physically written to disk.

When fsync is OFF, the PostgreSQL server will never issue any fsync system call, leaving entirely to the operating system the decision as to which blocks to transfer from write cache to disk, and when. The database engine never knows what has been sync'ed or not. In case of a server crash, it has to be assumed that the database is in an inconsistent state.

When synchronous_commit is OFF, the server issues fsync call, but not necessarily immediately at commit time for each transaction. It may delay them after the commit, for a maximum of wal_writer_delay multiplied by 3. With a default configuration that would be 600ms. This allows to reduce the number of fsync calls, especially for workloads with lots of small transactions.
In case of a server crash, the transactions not yet sync'ed in the last 600ms before the crash may be rolled back, but the state of the database is always consistent.


Awful performance on joined queries while great separately I have a simple queries:

SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2]
     , t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208)
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208)
) t2
ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
--ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField

When the query is executed as you see now it runs in less than 1 second. If I run the last two line like this instead

--ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField

It takes more than 1 minute. The difference is obvious: joining a persisted value against joining a computed value. Running the queries with IO and time statistics will show exactly the same query plans and IO figures, but CPU is huge in the second case.
I've been thinking about how to reorder/rewrite the query to make it run in 1 second but I haven't succeed. The following works as fast as I expect but involves more code than I would like to:

declare @temp1 table (IntField int, TDateTimeField datetime2(7), FloatField1 float, FloatField2 float);
declare @temp2 table (IntField int, TDateTimeField datetime2(7), FloatField3 float);
insert into @temp1
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField1], t.[FloatField2]
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208);

insert into @temp2
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField3]
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208)

SELECT t1.[IntField], CONVERT(varchar,t1.[TDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2]
     , t2.[FloatField3]
FROM @temp1 t1 JOIN @temp2 t2 ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField

It's obvious that the improve comes because data is pre-calculated and pre-stored, hence reducing the amount of data, just before joining.
Does anybody have a better idea?

Thanks!!

EDIT: Actual query plans added (first and second query respectively). You'll see they are almost identical, and I've checked the CPU costs in them and didn't see anything weird...

A stretch but give this a try
Just deals with one t.[IntField] at a time and union
Now you have 1/3 only comparing to 1/3 so it may be 9 times as fast
And the query optimizer may miraculously decide not to do a loop join

SELECT t1.[IntField]
     , CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
     , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 206
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
     , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 206
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION
SELECT t1.[IntField]
     , CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
     , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 207
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
    , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 207
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION ... 208

If it works then you can just make two stored procedures where you pass in the 5 conditions for the base select and the syntax becomes pretty simple.

Materializing is the safe path.  But use #temp over table.  An index on [IntField] may help but with only 3 unique values it may not help.  Even with materializing make some stored procedures to reduce syntax.


MySQL on AIX operating system I could see any proper documentation which answer these questions

1.Is MySQL database and cluster supported on AIX operating system?

2.Is MySQL database and cluster free for commercial use in any private enterprise application ?

Referring to https://www.mysql.com/support/supportedplatforms/database.html the answer is no, AIX is not a supported operating system for MySQL database however you can install it through yum, for information on how to install yum on AIX refer to https://www.ibm.com/developerworks/aix/library/aix-toolbox/alpha.html

Concerning support you should address this with MySQL Technical Support (ref. https://www.mysql.com/support/) as they are the ones who would ultimately need to decide if they will provide support for your installation, which they may refuse on the basis that you are not operating their product on an officially supported / certified operating system. Concerning pricing, no, MySQL for commercial purposes is not free (ref. https://www.mysql.com/products/).