Filtering UNION ALL result is much slower than filtering each subquery (edit: see end for a simpler example)

I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), which means that for these records a mix of parent/child fields must be used for filtering and display.

I have identified four different parent-child relationships and created views for them:

caselist_no_specials: not a child record, use record data as is; 116106 rows total.
caselist_disputes_with_ipr: child record; 138 rows total.
caselist_mark_children: child record; 18132 rows total.
caselist_design_children: child record; 671 rows total.

The results from these views do not overlap and together cover 100% of the table.

When I select a union of all of them, and separately filter each view, the query takes about 9 ms. Selecting a union of all views and filtering the result of that takes about 500 ms.

I have also tested this without views, inlining the queries they contain, which did not produce a measurable improvement.

This is the fast query (explain):

  SELECT  c.*
    FROM  caselist_no_specials c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_disputes_with_ipr c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_mark_children c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_design_children c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

ORDER BY  sort_nr,
          id;

As you can see, the join and filter is duplicated for each view. Trying to avoid the duplication yielded this query, which takes a lot longer (explain):

  SELECT  x.*
    FROM  (
              SELECT * FROM caselist_no_specials
              UNION ALL
              SELECT * FROM caselist_disputes_with_ipr
              UNION ALL
              SELECT * FROM caselist_mark_children
              UNION ALL
              SELECT * FROM caselist_design_children
          ) x
    JOIN  case_clients cacl ON cacl.case_id = x.main_id
   WHERE  cacl.client_id = 12046
ORDER BY  x.sort_nr,
          x.id;

Is it possible to somehow let PostgreSQL know that the filter/join on the outer query can be applied to the inner subquery?

Or is there any other way to avoid filtering each view separately? The user-facing form for this query has over 20 filter fields, and there can be JOINs with up to 14 additional tables.

PostgreSQL is version 9.4.7 running on Linux.

EDIT: I've created a much simplified example, simply partitioning the original table using 3 views, but not involving other tables (and the query plans for both variants). In retrospect, this is what I should have used as an example in the first place.

What you're asking the DB to do in Query one is:
Give me ALL from table A FILTERED
Give me ALL from table B FILTERED
Give me ALL from table C FILTERED
Give me ALL from table D FILTERED
And then Union.

In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really enable you to index anything, obviously runs slower.
(It has nothing to do with the Server variant or the OS).


This sql select must be optimized...how? Here is my query

SELECT DISTINCT
    SM.sales_date,
    (SELECT
            SUM(gross_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
    (SELECT
            SUM(net_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TNET,
    (SELECT
            SUM(cash)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCASH,
    (SELECT
            SUM(cheque)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCHEQUE,
    (SELECT
            SUM(credit)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCREDIT,
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '4%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '4%'
            ,
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '6%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '6%',
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '10%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '10%',
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = 'M/R' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'M/R',
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = 'S/R' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'S/R'
FROM
    sales_main AS SM
WHERE
    (SM.sales_date >= '2013-06-01' AND SM.sales_date <= '2013-06-05') AND SM.vehicle_name='Lorry1'

and this is the result..

2013-06-01  59582.03  58336.59  32096.96  11141.02  15098.59  1245.44
2013-06-03  132334.45  126461.84  33138.93  11046.63  82276.28  1774.46 557.02  3541.13  2013-06-04  72184.69  71882.93  27840.74  12410.16  31632.03   152.16    149.6
2013-06-05  93478.20  86721.32  34461.47  24971.24  27288.6  934.48  3553.32  2269.08

But this if this queryextends to 31 days of month it takes 10 mins to return a results from 2000 rows table ....please help..

Why on earth all those inner queries?

Transform all (I just give example)

SELECT DISTINCT
    SM.sales_date,
    (SELECT
            SUM(gross_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
    (SELECT
            SUM(net_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TNET,

To:

SELECT
       sales_date,
       SUM(gross_sale) AS TGROSS,
       SUM(net_sale) AS TNET
FROM
            sales_main
        WHERE
            sales_date BETWEEN X AND Y
         AND
             vehicle_name=vehicle_name
GROUP BY YEAR(sales_date), MONTH(sales_date), DAY(sales_date)

Once you have that working, we can further optimize it.

The GROUP BY can also be just GROUP BY sales_date if sales_date is of DATE type, and DATE(sales_date) if it is DATETIME type.
I used that group by for the WITH ROLLUP summary of groups I am used to do in my reporting service...


How can can I get object definitions stripped of all comments? When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments.

Is this possible by a SQL query in an usual DBMS system?

Edit:

For SQL Server I found this solution using [Microsoft.Data.Schema.ScriptDom.Sql].  Here is the minimal PowerShell Code:

$sql = @'
select * from PowerShell -- a comment
where
 psRefnr = 1
'@
$options = new-object Microsoft.Data.Schema.ScriptDom.Sql.SqlScriptGeneratorOptions

$sr = new-Object System.IO.StringReader($sql)

$sg =     new-object Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator($options)
$parser = new-object Microsoft.Data.Schema.ScriptDom.Sql.TSQL100parser($true)

$errors = $null
$fragment = $parser.Parse($sr,([ref]$errors))

$out = $null
$sg.GenerateScript($fragment,([ref]$out))

$out

The advantage of this approach is that it uses the official Microsoft parser.

3rd Edit:

The basic methods to get the procedure definitions in T-SQL are as mentioned [here][1]
are

sp_help proc_name
SELECT [text] FROM sys.syscomments
WHERE OBJECT_NAME(id) =
'proc_name'
SELECT
OBJECT_DEFINITION(OBJECT_ID('proc_name'))

The second and third option return columns of type varchar(max) which can be used in LIKE clauses.

Historical Note:

When '*=' and '=*' became obsolete, syscomments returned the definition in chunks of 255 characters and the two could be placed into different rows.

But the second problem isn't resolved yet. Some faltering developers tended to keep old code as C - style comments, making scanning for code containing deprecated syntax difficult.

You could do this using T-SQL and various string functions, but you will need to account for comments using /**/ and --.

In other words, there is no easy way to get back a stored proc definition stripped of all comments. You would essentially need to build your own parsing tool.


Does it make sense to put a unique constraint on a nullable soft-delete flag? So we currently have the following:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: DELETED (INTEGER)
CHECK: DELETED = 0 OR DELETED = 1

Now, there's the requirement to allow infinite deleted records, but to only allow a single non-deleted record for each date+company.

I suggested to change the schema to:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: ACTIVE (Nullable INTEGER)
CHECK: ACTIVE = 1 OR ACTIVE IS NULL
UNIQUE: START, COMPANYID, ACTIVE

While my coworker is of the opinion that that is "going too far with constraints" and that we should just rely on the uniqueness checking in the application.

Is there a generally accepted best practice here?

You don't mention what DBMS you are referring to, but your suggested unique constraint won't work for several out there, because null is not equal to null

create table t (x int not null, y int, unique(x,y));
insert into t (x) values (1);
insert into t (x) values (1);

Postgres12
Does not violate the constraint

Oracle18c
Violates constraint

Db2 Developer C 11.1
Does not accept nullable columns in unique constraint

Firebird 3.0
Violates constraint

MariaDB 10.4
Does not violate the constraint

MySQL 8.0
Does not violate the constraint

SQLlite 3.27
Does not violate the constraint

SQLServer 2017
Violates constraint

Tested at DB<>Fiddle

So, generally speaking, your UNIQUE constraint does not work. Consider changing ACTIVE to

ACTIVE SMALLINT NOT NULL,
CHECK (ACTIVE BETWEEN 0 AND 1),
UNIQUE (START, COMPANYID, ACTIVE)

Though I'm not convinced that a certain COMPANYID at a particular START can be both ACTIVE and NOT ACTIVE, but then I don't know your business.

EDIT:
Given the new info in the question, something like this might be possible (I don't have access to the docs for system i right now, so I don't know whether generated columns, or constraints on expressions exists)

create table t
( a int not null generated always as identity
, x int not null
, y int
, z int not null generated always as (
    case when y is null then -1*a else y end
)
, unique(x,z)
, check (y is null or y = 1)
);

insert into t(x) values (1); -- ok
insert into t(x) values (1); -- ok
insert into t(x,y) values (1,1); -- ok
insert into t(x,y) values (1,1); -- fails because of constraint violation.


Why do I have to select from the dual table? This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL).

select 'abc' abc, 1 def;

It does not work on Oracle.  Why do we need to select from DUAL in Oracle?  Does the ISO/ANSI standard for SQL require a FROM clause for SELECT statements?

Edit:

Per Bacon Bit's answer, it does seem required by the SQL standard.

So in reality, because the name DUAL is such a misnomer, if I were to create a table and name it ATOM or ONE, e.g. create table one (atom int); .. select 'abc' abc, 1 def FROM one; - Is there a performance penalty compared to SELECT .. FROM DUAL?

Strictly, yes, the FROM clause of a SELECT statement is not optional.  The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it.  That indicates the standard considers it non-optional:

SELECT [ DISTINCT | ALL ]
{Column expression [ AS name ]} [ ,... ] | *
FROM <Table reference> [ {,<Table reference>} ... ]
[ WHERE search condition ]
[ GROUP BY Columns [ HAVING condition ] ]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options |
 INTO DUMPFILE 'file_name' |
 INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]

In actual use, programmers and DBAs often find it useful to do things other than manipulate data in tables or manipulate tables and data structures.  This type of thing is largely beyond the scope of the SQL standard, which is concerned with the data features more than the nuts and bolts of specific implementations.  Whether we want to run SELECT getdate() or SELECT 1 or SELECT DB_NAME() (or whatever your dialect prefers), we don't actually want data from a table.

Oracle chooses to solve the standard and implementation discrepancy using a dummy table with the following effective definition:

CREATE TABLE DUAL (
  DUMMY CHAR(1)
  )

INSERT INTO DUAL (DUMMY) VALUES ('X')

Other RDBMSes essentially assume that a dummy table is used if no FROM is specified.

The history of the DUAL table is on Wikipedia:

  The DUAL table was created by Charles Weiss of Oracle corporation to
  provide a table for joining in internal views:

    I created the DUAL table as an underlying object in the Oracle Data
    Dictionary. It was never meant to be seen itself, but instead used
    inside a view that was expected to be queried. The idea was that you
    could do a JOIN to the DUAL table and create two rows in the result
    for every one row in your table. Then, by using GROUP BY, the
    resulting join could be summarized to show the amount of storage for
    the DATA extent and for the INDEX extent(s). The name, DUAL, seemed
    apt for the process of creating a pair of rows from just one.

  The original DUAL table had two rows in it (hence its name), but
  subsequently it only had one row.


How to find similar (but different) names with the same soundex value? I got the output with the same name and same soundex value, but I want the result which has similar name and same soundex value.

Can anyone help me to do this?

You can use a window function for this:

select *
from (
   select name,
          count(*) over (partition by soundex(name)) as cnt
   from poi
) t
where cnt > 1;


Determining SQL Server Server Edition without database access Anyone have an idea how to get Microsoft SQL Server Edition (Express, Standard, Enterprise, Developer) without access to the database or registry?

I got the version like this:

$SQLFP = (Get-WmiObject -Class Win32_Service -ErrorAction Stop | Where-Object { $PSItem.Name -eq 'MSSQLSERVER' } | Select-Object -ExpandProperty PathName).Split('"')[1]
$SQLVer = ([System.Diagnostics.FileVersionInfo]::GetVersionInfo($SQLFP).ProductVersion).Split('.')[0]

Switch ($SQLVer) {
    "10" { "Version: Microsoft SQL Server 2008/R2" }
    "11" { "Version: Microsoft SQL Server 2012" }
    "12" { "Version: Microsoft SQL Server 2014" }
    "13" { "Version: Microsoft SQL Server 2016" }
    "14" { "Version: Microsoft SQL Server 2017" }
    Default { "Unsupported version." }
}

I found plenty of ways to determine the Edition, Patch Levels, Service Packs and stuff from Registry and via SSMS, but unfortunately I have access to neither of them. I do have permission to access the file system.

If you have access to file system, one way of getting information could be to check available folders under %PROGRAMFILES%\Microsoft SQL Server\xyz

where xyz translates to:

 80 = SQL Server 2000    =  8.00.xxxx
 90 = SQL Server 2005    =  9.00.xxxx
100 = SQL Server 2008    = 10.00.xxxx
105 = SQL Server 2008 R2 = 10.50.xxxx
110 = SQL Server 2012    = 11.00.xxxx
120 = SQL Server 2014    = 12.00.xxxx
130 = SQL Server 2016    = 13.00.xxxx

Further you can analyze the installation summary file located at
%PROGRAMFILES%\Microsoft SQL Server\xyz\Setup Bootstrap\Log\Summary.txt
which will give you package properties that contains information about Installation edition (Enterprise).


How can I make this query sargable? I have a query which joins two tables based on a timestamp value (don't ask).

One of these tables stores the data in a column with a Timestamp data type. The other stores it as a varchar.

Previously, the query converted the timestamp column to a varchar for the join. As I understand it this is not sargable, so I'd like to do the reverse. (The timestamp table is much larger and efficiency is more of an issue).

SELECT *
FROM TABLE1
INNER JOIN TABLE2
    ON UPPER(master.dbo.fn_sqlvarbasetostr(cast(TimestampColumn as binary(8))))
       = VARCHARTIMESTAMPCOLUMN

I've tried:

CONVERT(ROWVERSION, N'0x0000000003306BDD')

No error, but no match on the table (when a row definitely exists).

CONVERT(ROWVERSION, CAST(N'0x0000000003306BDD' as bigint))

This gives an error converting nvarchar to bigint.

How can I convert the varchar value into a timestamp rather than the other way around?

In SQL Server 2008, converting binary to a character representation became a lot faster and easier:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1)
);

Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to binary(8) (when not nullable, varbinary(8) otherwise).

We can now create an index on the computed column:

-- Create index on the computed column
-- Note PERSISTED is *not* required
CREATE UNIQUE INDEX i
ON dbo.X (rvc);

Add some sample data:

-- Some rows
INSERT dbo.X
    (pk, c1)
VALUES
    (1, 100),
    (2, 200),
    (3, 300);

And display the results:

-- Show the data
SELECT
    X.pk,
    X.c1,
    X.rv,
    X.rvc
FROM dbo.X AS X;

Output example:

The task of joining to the table with rowversions in character format is now trivial.

SQL Server 2005 version

This requires a helper function:

CREATE FUNCTION dbo.Bin8ToHexStr
    (@Hex binary(8))
RETURNS char(18)
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        '0x' +
        CONVERT(xml, N'').value('xs:hexBinary(sql:variable("@Hex"))', 'char(16)');
END;

The table definition becomes:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS dbo.Bin8ToHexStr(CONVERT(binary(8), rv))
);

Everything else proceeds as before, including the index.


performance issue caused by IO? Just curious to get an issue sanity checked  . . .

I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server.

Looking at perfmon I can see very high Avg. Disk Write Queue. Looking at SQL   ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance.

The presence of CXPACKET waittypes was high, this I believe is the result of parallelism being enabled and high fragmentation of the underlying indexes couple with out of date statistics.

What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.

Thanks !

There's a few different questions in here:

Q: "Looking at perfmon I can see very high Avg. Disk Write Queue."

That Perfmon counter isn't relevant for SQL Server anymore. SQL Server batches IO operations together, and it's normal to see big jumps here. Instead, check out the counters for Avg Disk Sec/Read and sec/Write. This tells you how fast the storage is responding to your requests. The downside is that it's only at the drive (volume, mount point) level. To get IO stats for specific files, query sys.dm_io_virtual_file_stats. David Pless has a great query here:

http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx

Q: "Looking at SQL ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance."

ASYNC_IO_COMPLETION is typically data file writes that happen in the background, asynchronously. When you insert/update/delete stuff, SQL Server has to get the data into the log file immediately - that's WRITELOG waits. It changes the data pages in memory, and then those get cached until later. ASYNC_IO_COMPLETION waits aren't holding up end users. It can indicate slow drive performance for data file writes, but that's a system bottleneck, not an end-user-facing bottleneck.

In my experience, OLEDB is usually caused by performance monitoring tools like Spotlight, SQL Sentry, Idera SQL DM, etc that are running traces and grabbing performance data over the wire.

Q: What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.

I've got a video on how to do that here:

http://www.brentozar.com/archive/2011/08/how-prove-its-san-problem-webcast-video/

Short story - IO is probably not your biggest problem based on what you described here.


10g to 11gr2 upgrade causes SYS.UTL_HTTP malfunction After a 10g to 11gr2 upgrade, we are getting ORA-29273 errors when we try to use the SYS.UTL_HTTP package. Our upgrade list requires ACL to be disabled.

How can we resolve this problem without enabling ACL?

I'm afraid your requirements cannot be met. If you want to use UTL_HTTP functions (and those of other network-related packages), you'll need to define ACLs in 11gR2. There is no alternative.

If you don't care about the security implications, the simplest thing to do would be to create an ACL that allows access to everything, and assign it to all the database users that need to use the functions.

The Fine-Grained Access to Network Services in Oracle Database 11g article has an example of how to do that:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'open_acl_file.xml',
    description  => 'Allow access to all hosts',
    principal    => 'TEST',  -- put your user there
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'open_acl_file.xml',
    host        => '*',      -- this allows all hosts
    lower_port  => 1,        -- on ports 1 to 9999
    upper_port  => 9999);

  COMMIT;
END;
/

If you need to assign that ACL to other users, use:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl         => 'open_acl_file.xml',
    principal   => 'TEST2',    -- your other user here
    is_grant    => TRUE,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);

  COMMIT;
END;
/


Can different tables have columns with the same name if those columns are not primary keys? I know it's a simple enough question but my textbook is very confusing about it.

SQL employs a concept known as domain name integrity which means that the names of objects have a scope given by their container.

Column names have to be unique, but only within the context of the table that contains the columns. Table names have to be unique, but only within the context of the schema that contains the tables, etc.

When you query columns you need to reference the schema, table and column that you are interested in, unless one or more of these can be inferred. When you write a query, you need to reference the tables in your query by name directly or by using an alias, e.g. Customer.ID or C.ID from Customer C, etc., unless your query is so simple that it only references one table.

There was a time when there was a technical requirement for uniqueness of all column names, which applied to old ISAM databases and to languages like COBOL in the 1960s and 70s. This got dragged along for no good reason into dBase in the 1980s and has stuck as a convention well into the relational and object DBMS eras. Resist this outdated convention.

When the shift from flat file and network databases to relational databases happened in the 1970s and 80s, the idea of joining tables was new.  So some people chose the convention that a unique name could be repeated if one column was a reference to another (i.e. foreign keys).  This concept is called a "natural join" and a lot of people still advocate for doing this.

I am not a fan of natural joins because it requires you, ultimately, to throw out the concept of domain name integrity and force the whole column reference into the column name.

The issue with natural joins is that you either have to be hypocritical or you have to make your column names long and unreadable.  Let me illustrate: It may sound like a good idea that the primary key of the Customer table is CustomerID.  Then in your Invoice table, your foreign key to Customer is also called CustomerID.  This would be a natural join and it all sounds good so far.  Here is the problem.  What if your convention is to have a column on every table called LastUpdatedDate?  So are you meant to join every table to every other table by LastUpdatedDate?  Of course not.  This is the absurdity of natural joins.  In order to avoid this absurdity you would need to jam the table name into the column name as a prefix.  However, if you have multiple schemas in your database, you can't stop there.  You also need to add the schema to the column name, and so it goes.

Another place where natural joins break down is when you have multiple relationships between the same two tables.  You if you need two references to Employee on your Invoice table (Sold By and Approved By, for example) you can't call them both EmployeeID.


I think the Optimizer works something like this:

It can find the first row:  MIN(name) WHERE foo=123
It can find the last row:  MAX(name) WHERE foo=123

Those are done via a drill-down in the BTree, and assumed to be reasonably cheap.  If there are a lot of rows for foo=123, then this is likely to skip over some blocks.  Note: fetching blocks potentially means a disk hit, and disk hits are the most costly primitive operation.  Note also that much of the Optimizer was designed in the days of small RAM and before SSDs.

Once it gets to the first (or last) row, then the B+Tree shines in that it can walk forward (or backward) efficiently.  (But this is not relevant to your Question.)

If, as you imply in your question, there are not many rows where foo=123, then there is not much savings, perhaps no savings.

(What I don't know here is whether and when the Optimizer decides that "loose scan" is likely to be inefficient.  I do know that the Optimizer will deliberately choose to do a table scan instead of using an INDEX when it estimates that the back and forth between the index BTree and the data BTree is too costly.)

A Rule of Thumb:  There are 100 items in each node of the BTree.  (This of course really varies between 1 and several hundred, but 100 is a handy number for estimating things.)  So, a 3-level BTree like you diagrammed may be holding 1 million items and have 10,000 leaf nodes.  Now, depending on the specifics of the schema and the query, a table scan would cost 10,000 disk hits versus only a few disk hits for a loose scan.  (Yeah, the table is likely to be completely cached in the buffer_pool; I do know that the Optimizer does not yet take that into account in its 'cost model'.)

See also: https://dev.mysql.com/doc/refman/8.0/en/cost-model.html


How crash recovery process works in SQL Server? I would like to ask a question regarding recovery process while restoring database operation. Let's say I restored a full backup with NORECOVERY followed by restoring two t-log backups with NORECOVERY. Lastly, I am going to restore the last 3rd t-log backup with RECOVERY keyword.
The question is: when I restore the last t-log backup with RECOVERY keyword, does SQL Server perform redo as well as undo phases or just does undo phase?
It seems to me that when we restore backup with NORECOVERY keyword, SQL Server also performs redo phase. The same thing with restoring t-log backups with standby keyword. In this case, SQL Server creates a separate file with the name that contains only "undo" not any "redo".

The Comparison of RECOVERY and NORECOVERY section of the RESTORE Statements doc should help you with that question:

Rollback is controlled by the RESTORE statement through the [ RECOVERY
| NORECOVERY ] options:

NORECOVERY specifies that rollback doesn't occur. This allows    rollforward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll    them forward.

RECOVERY (the default) indicates that rollback should be performed    after rollforward is completed for the current backup.
Recovering the database requires that the entire set of data being    restored (the rollforward set) is consistent with the
database. If    the rollforward set has not been rolled forward far
enough to be    consistent with the database and RECOVERY is
specified, the Database    Engine issues an error.


SQL database ODBC connection Multi-part identifier could not be bound We're using PADS Professional xDX Designer (from MentorGraphics) with a SQL parts database connected through ODBC using views. All seems to be working just fine, except for our Inductor parts where somehow I get an error assigning the Sat. Current to a part.

I get the following error:

An ODBC error occurred:
The multi-part identifier "Sat. Current" could not be bound.
The multi-part identifier "Sat. Current" could not be bound.
Statement(s) could not be prepared.
State:37000 Native:4104,Origin:[Microsoft[ODBC SQL Server Driver][SQL Server]
State:37000 Native:4104,Origin:[Microsoft[ODBC SQL Server Driver][SQL Server]
State:37000 Native:8180,Origin:[Microsoft[ODBC SQL Server Driver][SQL Server]
(1400)

My SQL query used to create the view:

--DROP view _ARV_Inductor
--CREATE VIEW _ARV_Inductor AS
SELECT [Number] as [Part Number],
[Description],
CONVERT(float,[Value]) as [Value],
[Case],
[Variant_Member] as [Variant],
CONVERT(float,[Rdc]) as [Rdc],
CONVERT(float,[Tolerance]) as [Tolerance],
CONVERT(float,[Nom. Current]) as [Nom. Current],
CONVERT(float,[Sat. Current]) as [Sat. Current],
[Temp Min],
[Temp Max],
[Datasheet],
[CostPerUnit] as Price,
[Manufacturer_member] as [Manufacturer],
[Part name],
[RS],
[Farnell],
[Supplier_member] as [Supplier],
[Order code],
[Location]
FROM [PDXpertDB].[viewer].[SourceItemMasterView] where ([Type]='Inductor' AND [ShowInDX]='true')

All other properties assign correctly, when I use this view in SQL Server Management Studio I also get no errors and it shows the correct list.

Any suggestions what I might be doing wrong? Is it even a fault in my SQL part or might it have something to do with xDX designer?

I would check if you have "Quoted Identifiers" set in the DSN, it looks like its interpreting [Sat. Current] as the column [Current] in the table [Sat]


Is it possible to create Dynamic SQL statements purely in SQLite? A lot of modern RDBMS have a feature called Dynamic SQL (MS SQL Server, for example) which allows you to build your SQL statements in strings and then execute that string of SQL.
Does such a feature exist purely in SQLite?
I'm aware I can build my statements dynamically using a functional language that uses SQLite as a database. But that's not applicable to the problem I'm trying to solve because the dynamic statements I'm trying to build are based off of relational logic and involve sqlite_master and PRAGMA, and translating that logic into a functional language makes trying to solve this problem not worth it.
For all intents and purposes, I have the flexibility to use any version of SQLite to solve this problem.

No.
SQLite is designed as a small, embedded database to be accessed from a 'real' programming language. Dynamic SQL is not needed for its intended purposes.


Sensible database backup retention policy? Scenario:

DB is replicated in two availability zones (for high-availability)
DB is backed-up daily in geographically separate location (for disaster recovery)

App-level user events are stored in the DB (for app-level audit/history). This means point-in-time restore is probably not going to happen on the DB-level but rather on the app-level, unless a rouge app user deliberately messed with the whole DB, making DB-level restore more practical.

My question is, what's a sensible retention policy for the daily database backups? E.g., Does it makes it sense to store 30-days worth of backups? What's the general best practice in this typical scenario?

There are three questions that drive answers here:

How much space is the business willing to pay for to store backups?
Are there legal requirements to store backups for a certain period of time?
How often are you checking for corruption (running DBCC CHECKDB)?

For larger amounts of data, the first question usually answers the question straight away. If there are legal requirements, that can also answer things pretty quickly.

If neither of those impose a limit, then the third question is a good starting place.

Say you run DBCC CHECKDB and find corruption -- you're going to want a full backup to use in case you can't fix things in-place. If you're running it once a week, then keeping two weeks worth of backups is probably a good idea.

If you're not running CHECKDB, well, go do that instead of this.


NOT IN statement or what to use in this case? I need to delete each row in fabricante_producto_solicitud (fps from now on) table if fps.id has not relation (NOT IN) fabricante_modelo_marca_producto (fmmp from now on).

Since my input will be an array of values (fps.id) as for example:

array(1,2,3,4,5)

How, if it's possible, using NOT IN I can know which fps.id is on fmmp table and which not? In this case fps.id aren't in fmmp can be deleted the existent not. Any advice or any other way to achieve this?

This is the model (just for info purposes)

Doing some tests:

Based on the answer by the user I modified a bit the original query and comes with this:

DELETE
FROM
    negocio.fabricante_producto_solicitud
WHERE
    negocio.fabricante_producto_solicitud.id NOT IN (
        SELECT DISTINCT
            negocio.fabricante_modelo_marca_producto.fabricante_producto_solicitud_id
        FROM
            negocio.fabricante_modelo_marca_producto
    )
AND negocio.fabricante_producto_solicitud.id = :someParameter;

Can I pass an array as :someParameter value or I need to iterate for the values and execute the query several times?

DELETE FROM fps WHERE fps.id NOT IN
    (SELECT DISTINCT fps_id FROM fmmp)

The sub query should find a list of fps ids that are being referred to, so you can delete any fps entries that are not in that list.


Run a PowerShell script on a different server - from inside a SQL Server job I have a procedure to backup SSAS Databases.

That is working as a charm.

Now my server is filling up with SSAS backups and I would like to delete the backup files that are older than 2 days.

to achieve this I am using the following POWERSHELL script:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
#
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------

#-- connect to the remote server -- SQLBILON1
#
ENTER-PSSESSION sqlbilon1

#-- set the Path where the backup files (.abf) are located
#
$path = 'H:\SQLBackups'

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item

The problem with this script is that I use MYSQLSERVER1 server to backup the databases that are on SQLBILON1 server. The backup files are on the folder H:\SQLBackups of SQLBILON1.

The job is failing with the following error message:

 The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 13 in a PowerShell script.
The corresponding line is 'ENTER-PSSESSION sqlbilon1'.
Correct the script and reschedule the job. The error information returned by PowerShell is:
'Connecting to remote server failed with the following error message :
Access is denied. For more information, see the about_Remote_Troubleshooting Help topic.'

Question, how can I run a PowerShell script on a remote server?
This script runs from a sql server job.

Can I create a proxy and use it to connect to the remote server?

If you are within a domain environment you can setup a proxy account that has permissions on SQLBILON1. However, using remote PowerShell is a bit of overkill and adds an unneeded layer to troubleshoot. If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues.

I would simply use UNC paths to the server. Whether you use the admin share like \\SQLBILON1\H$\SQLBackups or create share directly to \\SQLBILON1\SQLBackups. Unless you want to give the SQL Agent service on MYSQLSERVER1 permissions to that backup directory you would need to create a proxy account that has the appropriate permissions.

Your script will be much more simply if you just go this route:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
#
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------

#-- set the Path where the backup files (.abf) are located
#
$path = '\\SQLBILON1\H$\SQLBackups' # OR \\SQLBILON1\SQLBackups

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item


Large database backup files integrity check after or before copy I was trying to copy large database backup files(appx. 2TB each) to different region using robocopy. But I have noticed the speeds are drastically slow when using the switch /Z. So I have removed this switch and my copy is now faster. But now the challenge is,

how to verify the consistency of copied files.
what is the best possible way to skip the files if the files are already present in target location.
I have tried using powershell get-filehash command to verify file consistency but it’s too slow on large files especially on files located in different region.

My current command syntax,

robocopy source_directory destination_directory file_name /R:3 /W:10 /LOG+:log_file_location

Any best possible ways to address this?

SQL Server has a very handy command which checks the headers, checksums (if present) and that the backup set is complete. A data integrity check will be performed if a checksum is present but is optional. From the documentation here

RESTORE VERIFYONLY FROM DISK = 'D:\AdventureWorks.bak' WITH CHECKSUM;
GO


SQL Server Management Studio - connection with SQL Server database engine This question regards SQL Server 2005 and 2012.

I have a database server with two instances of SQL Server. Through SQL Server Management Studio I was able to connect to both instances by providing "IP-Address"\"InstanceName" and  user ID/password. But, I was not able to telnet the server on port 1433 from command prompt of the client PC.

Finally, when I specified TCP port 1433 through "SQL Server Configuration Manager" on the server and restart SQL Server services I was able to telnet to server on port 1433.

I want to know how "SQL Server Management Studio" was able to make a connection with SQL Server even if port 1433 was not active on SQL Server? And, the sequence of activities that takes place in background when Management Studio initiates connection with SQL Server?

The port 1433 is reserved for the default instance (MSSQLSERVER). When you have a named instance, where you can connect via "IP-Address"\"InstanceName", the port number is different. You can see the actual port number when you look into your SQL Server Logfile.


Where does Oracle Enterprise Manager derive the "User Logon Time" metrics from? Oracle's enterprise manager has a metric named "User Logon Time" and will show an alert if that exceeds a certain threshold.

I would like to retrieve the numbers stored for this metrics directly using a SQL query.

However, I cannot find any view where this is stored. I can find "Logons Per Sec", "Logons Per Txn", "Current Logons Count" in the DBA_HIST_SYSMETRIC_SUMMARY view and "logons cumulative" and "logons current" in the DBA_HIST_SYSSTAT  view.

The view DBA_HIST_METRIC_NAME also doesn't list any metric of that name.

So where does the Enterprise Manager get that information from?

I'm talking about this alert:

"Logon Per Sec" and "Logon Per Tx" can be found in OEM REPOSITORY (e.g. look in sysman.mgmt$metrics_daily view, the metric_name = 'instance_throughput' and metric_column like 'logons%'). The other metric will also be there, these are maintained in OEM REPO not in individual targets.


How to remove leaf member values? I have a dimension hierarchy that is related to multiple fact tables with different granularities. In the case of one fact table, it relates to the middle level of a three level hierarchy.

When you drill down that dimension, I would prefer to have values at that granularity and nothing if you drill down further. The default behaviour seems to be just duplicating the parent total for each of the leaf members.

I thought there was a property to control this but for the life of me I can't find it now.

In the cube-designer there is a tab "dimension usage" where you can set IgnoreUnrelatedDimensions for each measure group. When you set IgnoreUnrelatedDimensions to False for the measure group with the values you meant, the result should show null for levels below the granularity level.


Identifying Unused Indexes on SQL Azure I have a large SQL Azure database (P6 nearing 1TB in size). I want to do a cleanup/removal of any unused indexes. For the past 30 days, we've captured the 2 following sets of information.
See: https://gist.github.com/eoincampbell/3fe775d43e86ad342f9c6eba10f350f9

Index Stats gathered from sys.dm_db_index_physical_stats joined to sys.tables, sys.schemas and sys.indexes
Index Usage gathered from sys.dm_db_index_usage_stats

I have a small concern around the accuracy of sys.dm_db_index_usage_stats. It's not clear from the documentation when/if the following occurs in a SQL Azure environment (as compared to a single instance MSSQLServer.)

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=azuresqldb-current The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

Here's the query I'm using to subsequently identify the unused indexes. It

Gets the latest index information for all indexes in the database (812 records)
Gets the latest usage information for all indexes (558 records)
LEFT OUTER JOINs them together
Excludes any Clustered/PK Indexes
Returns anything with no usage stats OR anything with zero user-read stats.

The total returned row count is ~219 rows
Does this approach seem valid?
Query

WITH MostRecentStats (
    SchemaName, TableName, IndexName, IndexType, AllocUnitType, Pages, MostRecentAt
)
AS (
    SELECT      SchemaName, TableName, IndexName
                , IndexTypeDescription, AllocUnitTypeDescription
                , Max(PageCount) , Max(RecordDate)
    FROM        DBStats.IndexStats
    GROUP BY    SchemaName, TableName, IndexName
                , IndexTypeDescription, AllocUnitTypeDescription
    -- ****** Returns 812 Indexes across all tables ******
)
, AllCombinedUsage (
    SchemaName, TableName, IndexName
    ,user_seeks, user_scans, user_lookups, user_updates
    , system_seeks, system_scans, system_lookups, system_updates
)
AS (
    SELECT      SchemaName, TableName, IndexName
                , sum(user_seeks), sum(user_scans), sum(user_lookups), sum(user_updates)
                , sum(system_seeks), sum(system_scans), sum(system_lookups), sum(system_updates)
    FROM        DBStats.IndexUsage
    GROUP BY    SchemaName, TableName, IndexName
    -- Only Returns 558 Index with Usage Statistics...
)
SELECT      a.SchemaName, a.TableName, a.IndexName, a.Pages
            , b.*
FROM        MostRecentStats a
LEFT JOIN   AllCombinedUsage b
            ON a.SchemaName = b.SchemaName
            AND a.TableName = b.TableName
            AND a.IndexName = b.IndexName
WHERE       a.IndexName NOT LIKE 'PK_%' --Filter out all Primary Keys
AND         a.IndexType <> 'CLUSTERED INDEX' --And Clusted Indexes
AND (
            b.IndexName IS NULL --Include everything that has no index usage data
            OR
            (b.user_seeks + b.user_scans + b.user_lookups) = 0
            --Include everything with 0 User Reads on the data
)
ORDER BY    a.Pages DESC

You may also need to exclude unique indexes


Will mysql_secure_install script block master updates to slave? I had setup two virtual mysql database servers in two oracle linux 6 vm's, which come with mysql server ver 5.1.47

Master named prod_master & slave named prod_slave1.

I installed both the mysql servers in two virtual hosts machines with mysql_secure_installation script :

which removes test db
change root password
disable remote login

The bound ip-address for prod_master to 192.168.1.100 & prod_slave1 to 192.168.1.101

I had enabled the bin-log and index in my.cnf on master and relay log in slave. On master, I had created user repl_user and granted replication privileges and started master. The master has successfully started and created bin-log.
When i issued the commands show master status, show binary logs, the logs are visible.
Coming to the slave, after configuring the my.cnf, issued the change master to using repl_user with credentials and issued the command start slave.
slave started. Then issued the command show slave status : it shows slave status like "connecting to master" in the slave attribute connection i/o error : unable to connect to the master with port no 3306.

I had granted the replication privileges with @192.168.1.%, and also issued the port on slave side. Now, here the major issue is: I couldn't connect remotely to the master or even from slave. From master connecting to slave :

mysql master> mysql -h 192.168.1.101 -uroot -proot ( not able to connect )

I am not even getting connection refused error. And the same case even from slave to the master. After starting the slave i had viewed the slave mysql.log found that slave replication thread getting dead. due to connectivity issue. Is it problem , that i had installed using the script mysql_secure_install. Please guide me. I Want to know whether :

grant issue for replication problem (or)
because of the script mysql_secure_install

Note : Both the master and slave vm-machines are pinging on both sides.

If by disable remote login you mean it added skip-networking, you'll want to remove that from your my.cnf and restart mysql


Why doesn't `vacuum full` release space immediately? On Postgres 9.5 I had a table that grew to 30GB, but it was ultimately mostly dead rows. After cleaning it up with a vacuum full, postgres reports that the table size is now around 500MB. However, I see no space being released to the OS.

I found PostgreSQL - VACUUM FULL does not free space back to the OS which suggests the WAL files might be the culprit, but these are present on a different disk so it shouldn't affect my actual data disk at all.

I have confirmed that there are no other long running transactions or autovacuum's that might be locking the table.

What can I look at to figure out why my space isn't being released?

I traced this to a long-running autovacuum backend process. Even though the autovacuum was running on another table, it was hanging on to the file descriptors for the old files for the table on which I had just done a full vacuum. It was easy to confirm this with lsof.

Cancelling the autovacuum released the space immediately.


How get the sum of two tables value using inner join There are there tables the below image.

My question is retrieve from each customers and get the sum of Invoice total due, Invoice Total balance in invoice table and return cheque total due and return cheque total balance in return cheque table...

I wanted the answer like below image from the above 3 table using inner join

Please help me thank you.

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName, i.InvoiceTotalDue, i.InvoiceTotalBalance, r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance
FROM Client_TBL c
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue, SUM(TotalBalance) AS InvoiceTotalBalance
FROM Invoice_TBL
GROUP BY ClientNo
) AS i ON c.ClientNo = i.ClientNo
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue, SUM(TotalBalance) AS ReturnChequeTotalBalance
FROM ReturnCheque_TBL
GROUP BY ClientNo
) AS r ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs.  But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)


finding out the metadata and joins Suppose I have a database with a large number of tables.

Is there any command that I can type in PL/SQL which display only the field names of a given table?
Given any table is there any command that I can type that would show all the relations that it has with other tables?

For example , I have a table "TAB_1" , can I type a command that would show all the tables related to and for any table "TAB_2" that it is related to it would show which field in "TAB_1" corresponds to which field in "TAB_2 " .

You can query the system views and look at metadata.

SYS.ALL_OBJECTS gives you a list of all the objects that are accessible by the user
SYS.DBA_OBJECTS is similar and list all the object in the database
SYS.USER_OBJECTS lists all the object owned by the user

You will get a list of tables using this view:

SYS.ALL_TABLES = tables accessible by the user
SYS.DBA_TABLES = all tables in the database
SYS.USER_TABLES = tables owned by the user.

For a list of columns, you can join the TABLES list with the column views:

Table Columns SYS.ALL_TAB_COLUMNS, SYS.DBA_TAB_COLUMNS and SYS.USER_TAB_COLUMNS

There are similar views for:

Constraints = SYS.ALL_CONSTRAINTS, SYS.DBA_CONSTRAINTS and SYS.USER_CONSTRAINTS
Columns used by constraints = SYS.ALL_CONS_COLUMNS, SYS.DBA_CONS_COLUMNS and SYS.USER_CONS_COLUMNS

You can then join some of them and write such a query for a list of columns involved in referential integrity contraints:

SELECT ...
FROM SYS.ALL_CONS_COLUMNS col
JOIN SYS.ALL_CONSTRAINTS con con ON col.owner = con.owner AND col.constraint_name = con.constraint_name
JOIN SYS.ALL_CONS_COLUMNS fk on fk.owner = con.owner and fk.r_constraint_name = con.constraint_name
WHERE con.constraint_type = 'R' AND col.table_name = '...'

With R being (referential integrity) for constraint_type.


Best design for a many:many relationship issue We have three types of entity, each with their own table:

table:A(a_id), table:B(b_id), table:C(c_id)

Each of these entities can have any number of email addresses and linked Twitter accounts.

table:EmailAddresses, table:TwitterAccounts

What is the best way to relate these tables where the foreign key could be that of table A, B or C?

I considered this:

table:EmailAddresses(belongs_to_type,belongs_to_id), table:TwitterAccounts(belongs_to_type,belongs_to_id)

Where belongs_to_type would be A, B or C and belongs_to_id would be the respective foreign key.

So if A(1) has an email address, that entry would in the EmailAddresses table would look like EmailAddresses(A,1).

The solution you propose will work, though it requires you to get the value of belongs_to_type for a record to determine which table to join to. This could get messy.

I've never tried this, but I think something like this might work:

a
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

b
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

c
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

contact_refs
------------
  id (PK)

contact_ref_emails
------------------
  id (PK)
  contact_Ref_id (FK to contact_refs.id)
  email_id (FK to emailAddresses.id)

contact_ref_twitterAccts
------------------------
  id (PK)
  contact_Ref_id (FK to contact_refs.id)
  twitterAcct_id (FK to twitterAccounts.id)

emailAddresses
--------------
  id (PK)

twitterAccounts
---------------
  id (PK)

So now each entity has a reference to a contact_Reference. The contact reference is used in the many-to-many tables so that each entity references a single contact reference, and the contact references can refer to many email accounts or twitter accounts.


Oracle Patch Level Differences (Oracle Enterprise Manager and DBA_REGISTRY_HISTORY) There is a difference in the patch level that I am seeing on my Oracle database. On the Oracle Enterprise Manager, it shows that it is patched up to .7 but on DBA_REGISTRY_HISTORY it does not show any of the patches.

APPLY   SERVER  11.2.0.4    0   Patchset 11.2.0.2.0 PSU
APPLY   SERVER  11.2.0.4    0   Patchset 11.2.0.2.0 PSU

Is this a common occurrence?

Thank you

The installation of a PSU is not complete without the execution of the postinstall script. Opatch works at file level, it replaces/relinks files in the Oracle home. Changes inside the database are performed by running the postinstall script. Sometimes I see DBAs forget this step.

The postinstall script maintains the SYS.REGISTRY$ table (on which the DBA_SERVER_REGISTRY view is built) with the actual patch versions.

In 11.2, you run the postinstall script as (stated in the README):

sqlplus / as sysdba
@?/rdbms/admin/catbundle psu apply

This populates the above table (and so the view) with the actual data. When installing a PSU, this script must be executed in all databases running from the affected Oracle Home.

In most of the cases, skipping the postinstall script does not cause any problem, so mostly this goes on unnoticed. But there are exceptions, for example I have seen a database throwing ORA-07445 errors during the configuration of GoldenGate, because the postinstall script was not executed after the installation of the PSU.

Starting with 12c, the postinstall script is executed as:

export ORACLE_SID=orcl
cd $ORACLE_HOME/OPatch
./datapatch


This simple, single table query does a filesort, why? The table:

CREATE TABLE `test` (
  `status` tinyint(4) NOT NULL,
  `type` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  `created` int(11) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  KEY `status` (`status`,`type`,`created`,`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

the query

EXPLAIN
SELECT *
FROM test
WHERE status = 1 AND type = 'discussion'
ORDER BY created DESC, title ASC
LIMIT 10 OFFSET 0\G

the result

  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: status
          key: status
      key_len: 35
          ref: const,const
         rows: 1716
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

ORDER BY a  ASC, b  ASC and
ORDER BY a DESC, b DESC
can avoid "filesort" (if they can use INDEX(a,b)).  But if you mix ASC and DESC, it cannot avoid a sort pass.

And, no, you cannot say INDEX(a ASC, b DESC).  Or rather, you can say it, but DESC is ignored in the index definition.

Note that "filesort" may or may not actually touch disk.  In many cases, the sort can be done entirely in RAM.  What you are probably concerned about is the need to scan the entire table when you want only the first 'page'.

(This seems to be a watered down case.  If you would like to present the real schema, there might be other tips.)

Suggestion:  Use DESC for both fields and hint to the user that the dates were really different when he sees two titles in DESC order.

Since you seem to be doing "pagination", see how to remember where you left off as an optimization for subsequent pages.


table exists on 'show tables' - can't query Anybody know why this his happening?

 mysql> show tables;

 +-------------------+
 | Tables_in_photos  |
 +-------------------+
 | captions_images   |
 | image_meta        |
 | mentions_images   |
 | tags_images       |
 | words             |
 +-------------------+
 5 rows in set (0.00 sec)

 mysql> select * from captions_images;
 ERROR 1146 (42S02): Table 'ig_call.captions_images' doesn't exist
 mysql>

I tried repairing the table to no avail. What could help fix it?

Look at the question again. You ran

mysql> show tables;

What does the output header say ?

+-------------------+
| Tables_in_photos  |
+-------------------+

That means you were standing in the photos database.

If you had run

SELECT database();

or

SELECT schema();

It would have echoed photos.

Now, look at the error message

mysql> select * from captions_images;
 ERROR 1146 (42S02): Table 'ig_call.captions_images' doesn't exist

Since you were standing in the ig_call database and you issued select * from captions_images;, mysql assumed you want to select from captions_images tables in the ig_call database.

Try the following

select * from photos.captions_images limit 10;

If you get the same kind of message, then you have corrupt table issues in terms of the InnoDB data dictionary. If you get data, then you were simply in the wrong place at the wrong time. In your case, you were in the wrong database at the wrong time.


PostgreSQL IN query performance on a larger array I'm fairly new to PostgreSQL and have a question about query performance.
I get a set of values by parsing a file and want to count how many of them already exist in a specific column in my database (PostgreSQL). Currently I do this by parsing the whole file, storing its values in an array and then executing an IN query as follows:
SELECT COUNT(DISTINCT(id)) FROM "account"
WHERE name IN ('name1', 'name2', 'name3');

The column "name" which is being queried has a unique index on it.
I am wondering if this approach would be optimal if the array grows larger (20.000+ values) and/or I execute the query on a table with 40.000+ existing rows. Or is there a better approach?

Correctness
First things first.

I [...] want to count how many of the words already exist in a specific column

Your query does not do that if there can be duplicates in the input, and the misplaced DISTINCT keyword in the query suggests as much.
When passing ('name1', 'name1', 'name1'), with 'name1' in the table, the correct answer is: 3. But your query says 1.
The DISTINCT keyword makes sure of it, but it's a complete waste. The IN construct folds duplicates in the input implicitly, and there are no duplicates in your unique column. IN is the wrong tool to answer your question to begin with. One of these queries would do that:
SELECT count(*)
FROM   unnest(ARRAY['name1', 'name1', 'name1']) name
JOIN   account USING (name);

SELECT count(*)
FROM   unnest(ARRAY['name1', 'name1', 'name1']) i(name)
WHERE  EXISTS (SELECT FROM account a WHERE a.name = i.name);

The first one counts duplicates in the table (multiplied with duplicates on the same name in the input), the second one does not. Since there are no duplicates in the table, both do the same.
Adapted question
Let's assume you meant to ask:
How many of the words in my table are listed in the input (at least once)?
Now duplicates in the input don't matter (logically), and IN fits the job description. But we still don't need DISTINCT while the table column is unique (and rows are not multiplied by joins or similar).
Removing DISTINCT makes it faster already. Next, there is no need to involve the additional column id. Insignificant as though this may seem, it can force an index scan instead of an index only scan. count(name) is typically faster (and safer). But use count(*) instead. Faster, yet. And equivalent while id is the PK:
SELECT COUNT(*) FROM account WHERE name IN ('name1', 'name2', 'name3');

With modern Postgres that should be about the optimum for your case. You should see index-only scans on the unique index (if the table is vacuumed enough, read the manual), which is as fast as it gets, even with thousands of search terms. In particular do not rewrite with UNION or OR as has been suggested. That would get you a much more verbose and slower query.
Nor will it be faster to put the words in a (temporary) table first. That might be useful if you go on to do more with it, but not for this query alone.


MySQL Cluster: Problem Connecting the SQL node In a MySQL cluster I have three hosts, one with a manager node, and the other two are each both a data node and sql node. Connecting to the manager is possible, however only as [ndbd] and not [mysqld]... Meaning they connect to the manager as data nodes but not as sql nodes. Everything looks as it should other than the "not connected" part, and I receive no error messages. So my question is why is the manager node recognizing each machine as only a data node and not as mysql node as well?

Below you can see the ndb_mgm cluster configuration followed by the contents of config.ini:

    Cluster Configuration
    ---------------------
    [ndbd(NDB)]     2 node(s)
    id=2     @10.0.40.105    (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0, Master)
    id=3     @10.0.40.100    (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1     @10.0.40.119    (mysql-5.5.25 ndb-7.2.7)

    [mysqld(API)]   2 node(s)
    id=4 (not connected, accepting connect from any host)
    id=5 (not connected, accepting connect from any host)

config.ini:

    [ndbd default]
    # Options affecting ndbd processes on all data nodes:
    NoOfReplicas=2    # Number of replicas

    [tcp default]
    # TCP/IP options:

    [ndb_mgmd]
    # Management process options:
    hostname=10.0.40.119          # Hostname or IP address of MGM node
    datadir=/var/lib/mysql-cluster  # Directory for MGM node log files
    NodeId=1

    [ndbd]
    # Options for data node-1:
                                    # (one [ndbd] section per data node)
    hostname=10.0.40.105            # Hostname or IP address
    datadir=/usr/local/mysql/data   # Directory for this data node's data files
    NodeId=2

    [ndbd]
    # Options for data node-2:
    hostname=10.0.40.100           # Hostname or IP address
    datadir=/usr/local/mysql/data   # Directory for this data node's data files
    NodeId=3

    #one [mysqld] per storage node
    [mysqld]
    [mysqld]

/etc/my.cnf:

    [mysqld]
    ndbcluster
    ndb-connectstring=10.0.40.119

    [mysql_cluster]
    ndb-connectstring=10.0.40.119

ps -ef | grep mysqld | grep -v grep

So the solution was the conflicting myslqd. Apparently mysql has a mysqld and mysql cluster has a mysqld and they were conflicting or I was running the wrong one. In the end I uninstalled mysql and reinstalled mysql cluster and it worked perfectly.
Big thanks to RolandoMySQLDBA for helping me troubleshoot this issue.


Primary server 'XYZ' can not write to shared drive during availability group configuration I am in a process of adding databases to availability group. In one of the steps it was asking "data synchronization prefferences" as shown below

I have put the mapped drive location path, which was FILESHARE created in azure portal.

Now availability group validation fails as shown below, it says "Primary server '[MYNODENAME]' can not write to shared drive during availability group configuration"

Update

IP for listener was wrong . issue resolved .

New Error

  The Windows Server Failover Clustering (WSFC) resource control API returned error code 87.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
  The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.
  The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (.Net SqlClient Data Provider)

Here is screenshot

PORT 5022 was not added to secondary node's firewall

After adding rule, it worked like charm

This is how I found the solution

Open command prompt

     Telnet NODE2 5022

This failed


Script to Monitor MySQL Replication G'Day,

I am looking for a bash script (using Perl - As that is already installed on the machine and I don't want to have to install Ruby, Python etc. just for this requirement) that will perform some simple mySQL replication monitoring of a remote Master & Slave. The script will be able to:

Connect to a remote mySQL master and a remote mySQL slave (cron job is running on a different machine)
Using the output of SHOW SLAVE STATUS; and SHOW MASTER STATUS be able to email notify:

1) A general problem with replication ie. Not running, slave threads stopped etc.
2) Whether the slave is behind in transactions (Ideally this would be configurable)

Email should be configurable for delivery details as well as a cooling off period. So if the cron is run every minute emails for the same error kind are only sent after the cooling off period. So instead of 10 emails for the replication not running you get 1

I have looked online for a solution, but have been unable to find anything suitable.

So if anyone can point me in the right direction I would appreciate it.

The nagios monitoring scripts for mysql should be a good start.

If you want to monitor your database with this kind of email alerts, you should really try to setup nagios or some similar software as this is exactly the purpose of a monitoring software.


Backups failing for all databases Here is the error message in its entirety:

  Failed:(-1073548784) Executing the query "BACKUP DATABASE [MCM] TO  DISK = N'F:\Backup\MCM\M..." failed with the following error: "BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

There is no further information. No backups for any database on the server are working right now, but they did succeed the day before. There is plenty of backup space. One thing to note, though I'm not sure if it's relevant, is that there was a transaction stuck in a Killed/Rollback state the day before; it was blocking on a remote procedure call to a Sybase database. The service was restarted to try to "fix" this stuck transaction (by someone I warned beforehand against doing such a thing).

What do I need to do to fix this situation so that I can get the backups working again?

I should have written this long ago, but better late than never.

How did I resolve the issue? Each backup attempt created a dump file which I analyzed using WinDbg. These files indicated that the error originated in the sqlmin.dll assembly. I did a search for hotfixes for that assembly, found some, applied them, and after a reboot, the backups started working again.

For anyone who comes across weird SQL Server bugs like this, try looking at the dump files for clues.


Can I Avoid Joining The Same Table 3 Times? I have this database schema on a Oracle 12c database :

I'm trying to answer this question :

What is the game in which Dallas Mavericks had the biggest percentage of successful 3-point shots ?

I've managed to answer this question with this query:

SELECT HOME_TEAMS.TEAM_NAME                    "HOME TEAM",
       AWAY_TEAMS.TEAM_NAME                    "AWAY TEAM",
       GAMES.GAME_DATE                         "DATE",
       TEAMS.TEAM_ID                           "GENERIC ID",
       HOME_TEAMS.TEAM_ID                      "HOME_ID",
       AWAY_TEAMS.TEAM_ID                      "AWAY_ID",
       GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE "3 POINTS PERCENTAGE"
FROM GAMES_TEAMS_STATISTICS
         INNER JOIN GAMES ON GAMES_TEAMS_STATISTICS.GAME_ID = GAMES.GAME_ID
         INNER JOIN TEAMS ON GAMES_TEAMS_STATISTICS.TEAM_ID = TEAMS.TEAM_ID
         INNER JOIN TEAMS HOME_TEAMS ON GAMES.HOME_TEAM = HOME_TEAMS.TEAM_ID
         INNER JOIN TEAMS AWAY_TEAMS ON GAMES.AWAY_TEAM = AWAY_TEAMS.TEAM_ID
         INNER JOIN GAME_STATISTICS ON GAMES_TEAMS_STATISTICS.STATS_ID = GAME_STATISTICS.STATS_ID
WHERE TEAMS.TEAM_ACRONYM = 'DAL'
ORDER BY GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE DESC
    FETCH FIRST 1 ROWS WITH TIES;

However, to answer this question , I join the teams table 3 times. Is there any way I can write this query in a more efficient way , avoiding so many joins ?

Remove HOME_TEAMand AWAY_TEAM columns from the GAMES table and add a HOME_AWAY flag column to theGAME_TEAM_STATISTICS table.

Right now, you need to code for an ASSERTION (because they do not yet exist) to ensure the GAME_ID+TEAM_ID combo in GAME_TEAM_STATISTICS matches the GAMES.HOME_TEAM/GAMES.AWAY_TEAM information.


MySQL CAST vs. CONVERT Let's say that I have a VARCHAR (which contains numeric data) that I want to use for a simple computation (adding 10 to it).  According to the MySQL documentation on CAST functions, I could accomplish this with either a CAST or a CONVERT:

SELECT (CAST(field1 AS SIGNED)) + 10
FROM myTable;

or:

SELECT (CONVERT(field1,SIGNED)) + 10
FROM myTable;

What is the difference between CAST and CONVERT in this sense?  Are they both really accomplishing the same thing?

Yes, CAST and CONVERT do the same thing, except that CONVERT allows more options, such as changing character set with USING.


Attempting to compress an existing table results in no change to the table size There is an existing table in SQL 2012 Enterprise DB, which is ~120MB in size with ~2000 records. Its largest field is nvarchar(max) containing spongy XML (ranging from 20 to 300 kB) which on its own compresses roughly by 50-75% in 7Zip or RAR.

I tried the following commands which return instantaneously, even though simply querying * from the table takes quite a few seconds:

alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=ROW)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=NONE)

But the size is not changing after any of them, even though the properties of the table correctly show the compression type.

Is the server actually applying and removing compression to the rows/pages? It seemingly does not have time to accomplish that in the split second that it takes each command to execute.

Row compression and page compression have a number of limitations and won't always reduce the space used by your table. Let's walk through a simple example. Suppose I insert a bunch of strings of length 4030 into a table. Applying page compression results in a space reduction from 4000 KB to 24 KB:

CREATE TABLE dbo.ZZZZZ_CI (
ID INT NOT NULL IDENTITY (1, 1),
FLUFF VARCHAR(4050),
PRIMARY KEY (ID)
);

SET NOCOUNT ON;

GO
-- insert 1000 rows
INSERT INTO dbo.ZZZZZ_CI VALUES (REPLICATE('Z', 4030));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- 4000 KB for data

ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=PAGE);

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- now 24 KB for data

However, if I insert strings of length 4050 I get no space savings from page compression, even though the strings are all the same character!

-- remove data and compression
TRUNCATE TABLE dbo.ZZZZZ_CI;
ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=NONE);

GO
-- insert 1000 rows
INSERT INTO dbo.ZZZZZ_CI VALUES (REPLICATE('Z', 4050));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- 8000 KB for data

ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=PAGE);

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- still 8000 KB for data

For your table you're probably running into a different limitation. A table in SQL Server can only contain 8060 bytes per row. Any data that cannot fit is stored separately as ROW_OVERFLOW_DATA. Your average row size is around 64 KB and your minimum row size is 20 KB. That means that all of your XML data cannot be stored in row. Data compression does not work on data stored out of the row:

  SQL Server compresses in-row LOB data but not if the LOB data is stored out of row. With the result many LOB heavy applications are unable to take full advantage of data compression. However, there are two workaround available. First, LOB data can be compressed at the application tier but this means (a) the application needs to be modified (b) the application cannot take advantage of search and partial update capabilities provided in SQL Engine. Second, use filestream feature to store LOB data on a compressed volume.  This is our recommended solution where applicable. Please refer to Books-Online for details on filestream feature.

Both of those workarounds are available in SQL Server 2012. In SQL Server 2016 there is an additional option with the introduction of GZIP. That can compress data that otherwise would have been stored out of the row. The data from before could not be page compressed, but GZIP reduces space needed to 64 KB:

CREATE TABLE dbo.ZZZZZ_CI_2016 (
ID INT NOT NULL IDENTITY (1, 1),
FLUFF_COMPRESSED varbinary(max),
PRIMARY KEY (ID)
);

GO
-- insert 1000 compressed rows
INSERT INTO dbo.ZZZZZ_CI_2016 VALUES (COMPRESS(REPLICATE('Z', 4050)));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI_2016';  -- now 64 KB for data

SELECT COUNT(*) -- 1000 is returned
FROM ZZZZZ_CI_2016
WHERE DECOMPRESS(FLUFF_COMPRESSED) = REPLICATE('Z', 4050);

Even very long strings can be compressed nicely:

TRUNCATE TABLE dbo.ZZZZZ_CI_2016;

GO
-- insert 1000 compressed rows
INSERT INTO dbo.ZZZZZ_CI_2016 VALUES (COMPRESS(REPLICATE(CAST('Z' AS VARCHAR(MAX)), 99999)));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI_2016';  -- now 152 KB for for 99999 length strings


Database in transition I have a database that has been locked in transition for over 24 hours.  I found a similar thread that discusses how to find the commands that are running but I do not know how to unlock the database.  How can I resolve this transition state?

Query to get currently running commands:

use _MYDB_
go

select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('_MYDB_')
order by request_session_id;

Results:

Attempting to kill the offending process failed with the same error.  In the end I had to reboot the server to get by the issue.


Active directory automatic login to SSRS I have a server running SSRS 2012 at address server1.olddomain.com. This is working fine.

My users will be accessing this server from another address (the server is not physically moving). The new address is server2.newdomain.net. I modified the UriRoot option in the config file, and it works fine, except users are now prompted for their username and password through AD rather than being logged in automatically. (Once they enter their username and password in the form olddomain\username, they are logged in successfully).

How can I configure this to log users in automatically? The users' domain hasn't changed, nor has the server moved physical locations. Is this possible to do?

EDIT: I wasn't sure whether to post on here or ServerFault. Let me know if you think it would be better suited there.

This may be a simple matter of Internet Explorer not automatically detecting server2.newdomain.net as an "intranet" site. By default, IE will only do automatic Windows authentication in the intranet zone.

To see if this is the case, you can add "server2.newdomain.net" to the list of trusted intranet sites. Open IE, and do this:

Go to Tools, Internet Options.
Go to the Security tab, click Local intranet, and click the Sites button.
Click Advanced to open the list of sites.
Enter the full name ("server2.newdomain.net") into the appropriate box, and click Add.

If this gets automatic login working, then you'll have to decide how you want to solve the issue. You can specify a list of sites for the trusted intranet zone via Group Policy, but this prevents users from being able to add or remove anything on that particular list. If there's a cleaner way to do it, I'd be interested in knowing too, as we run into this a lot with intranet sites on various DNS domains.


Creating a staging table when the target table has autoincrement Is it possible to create a staging table when the target table has an autoincrement ID key, and then merge it back in?

I've attempted this:

 CREATE  TEMP TABLE stage (like target);

insert
into
    stage
    (colA, colB, colC)

Where the target table has an autoincrement column id.  When I run this script, I get back the following error:

[Amazon](500310) Invalid operation: Cannot insert a NULL value into column id
Details:
 -----------------------------------------------
  error:  Cannot insert a NULL value into column id
  code:      8007
  context:   query execution
  query:     896442
  location:  column:1
  process:   query1_51 [pid=15458]
  -----------------------------------------------;

What I ended up doing was creating a staging table without using LIKE TARGET, from there I created a few queries to see what values were in the staging table vs the target table.  I then inserted those values into the target table, and dropped the staging table.

Thanks to @SqlZim for the help in tuning the query.


Oracle DB export, possible charset conversion if NLS_LANG not set I need to export an Oracle database from one server and import it on another server. But I am getting the following information message at the beginning:

  Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit
  Production Export done in US7ASCII character set and AL16UTF16 NCHAR
  character set server uses UTF8 character set (possible charset
  conversion)

Although the export still terminates successfully without warning, I was wondering whether the data in the database is still correct. Is there a need to set the NLS_LANG variable to match the character set of the server (in this case UTF-8)?

If I set the NLS_LANG variable to UTF-8 I don't get this information message. But the target server where I import the database again shows the following:

  import done in UTF8 character set and AL16UTF16 NCHAR character set
  import server uses AL32UTF8 character set (possible charset
  conversion)

If I understand that correctly these two servers use (slightly) different character sets - UTF-8 and AL32UTF8.

I am confused on what I should set the NLS_LANG variable or even whether it is necessary to set. Should I use AL32UTF8 as NLS_LANG for the export and import?

EDIT: In case this information is important: I am using a SUSE Linux and for the export I use the command exp that comes with Oracle.

The NLS_LANG parameter is used by the Oracle network layer to do character translation between the client and the database. This way the client can display all the characters in the database in a 'correct' way.

Your NLS_LANG should match the character-set of your database when you do an export. If you export a UTF-8 database in US7ASCII then you risk to loose information when you import this data back into a database. Why? The UTF-8 character-set can hold much more different characters then US7ASCII. The 'unknown' characters are replaced by a single character. In the past I have seen this happening that we lost all characters with accents. They were replaced by a '?' if I remember well.

If you import then you must be sure that the NLS_LANG is set to the value of your import file. Oracle will then do the mapping to the character-set of the database in which you import.


SQL Server 2005: Integration Services Stored Packages is empty. Why? I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored packages folder is empty. What is going on?

And I have already checked MsDtsSrvr.ini.xml. It is set up with the standard, default settings.

The packages themselves are stored in the msdb sysdtspackaes90 table.

  It’s important to note that the SSIS
  server isn’t aware of packages stored
  in the File System until those
  packages have been imported to the
  File System folder in the SSIS
  service.

...

  While the SQL Server Management Studio
  is shipped using the default folder
  locations of MSDB and File System, you
  can freely add new folders to this
  structure using the Create New Folder
  option. When you create a new folder
  beneath the File System system folder,
  a new directory will be created in the
  file system. By default, these
  directories are located in the
  c:\Program Files\SQL
  Server\90\Packages directory.
  Importing packages to a File System
  folder will result in the package
  being copied to the like named
  directory in the file system. For
  folders that are created under the
  MSDB folder, a new entry is added to
  the sysdtspackackefolder90 table that
  tracks the folder structure. However,
  it’s important to realize that the
  packages themselves are still stored
  in the msdb sysdtspackaes90 table. The
  Folders option in the SQL Server
  Management Studio essentially gives
  you a way to apply and organization
  structure to your packages, enabling
  you to group like packages together.

Managing and Deploying SQL Server Integration Services

Anyway, I recommend to install all SPs.


SQL Server 2012: Users cannot see database properties I migrated a database from SQL Server 2008 to 2012 and also the users.

Now the weird thing is that if the user connects to SSMS on the same server as the SQL Server engine, he can display database properties but he cannot when he uses SSMS to access it remotely (from another machine). He can log, run SQL queries but not display the properties.

Here is the error message:

Even stranger, if I log remotely with sa, I CAN display database properties but not as a normal user.

I looked around, tried many things, created new users in the new server. But same problem.

I tried this Rights needed to see database files in Properties dialog? in the Securables but the parameters do not seem to be saved. When I reopen they are gone - see screenshot

Please help

I think the problem comes from the fact that all the remote SSMS were v 2008. When I logged in from another computer with SSMS 2012, I could display everything.

But we use the 2 versions: 2008 and 2012. Any one knows a quick fix to access a SQL Server 2012 with a SSMS 2008?

Thank you


Splitting or Distributing very large data on PostgresQL 9.6 I have a dedicated server with 10x1TB. I can do RAID and make them as single one. But this gave me 10TB data. What will happen if PostgreSQL data exceeds 10TB? Which will exceed soon (I have a special software that requires TBs of database and writes/reads always)

Is there any method/feature in PostgreSQL or pgpool-II to add more servers or hdds when needed. What should I do to prevent such scenario?

Also, I have GlusterFS infrastructure, Should I deploy my database in that? Or is there any filesystem exactly for this?

Note: I know there is replicate. But my situation is splitting very large database on multiple disks or servers or distribute on filesystems like GlusterFS. Like where server is full I add more and it continues to write...

As @a_vlad stated, you should do have a plan. Also, for later use I would like to recommend you guys two things.

Tablespace
Citus Data (distribute data across workers)

Citus is a distributed database that combines the low-latency requests that power real-time applications with high throughput, interactive analytics on billions of events.
Citus does this by extending PostgreSQL to distribute your workload across multiple hosts, leveraging the memory, storage, and processing power of multiple machines.

Read more


How to add a summary count to this query I am using Postgres 9.4 and I have a query that looks like the following:

SELECT attendees.event_id, attendees.user_id, users.tags
FROM users, attendees
WHERE 'crystal:promo' = ANY(users.tags)
AND users.id = attendees.user_id
AND attendees.status = 'paid'
GROUP BY attendees.event_id, attendees.user_id, users.tags
ORDER BY attendees.event_id desc;

Result:

 event_id | user_id | tags
----------+---------+------
 1        | 2       | ...
 1        | 4       | ...
 1        | 7       | ...

I'd like to add a summary column for each row that is the total number of people per event independent of user tags such that the result would be something like:

select count(*) from attendees where status='paid' group by event_id

and joined with the above results (assuming 11 paid):

 event_id | user_id | tags | total_event_paid
----------+---------+------+------------------
 1        | 2       | ...  | 11
 1        | 4       | ...  | 11
 1        | 7       | ...  | 11

You can just replace attendees with a subquery that pre-selects "paid" rows and add the count without aggregating - with a window function:

SELECT DISTINCT a.event_id, a.user_id, u.tags, a.total_event_paid
FROM   users u
JOIN  (
   SELECT *, count(*) OVER (PARTITION BY event_id) AS total_event_paid
   FROM   attendees
   WHERE  status = 'paid'
   ) a ON u.id = a.user_id
WHERE  'crystal:promo' = ANY(u.tags)
ORDER  BY a.event_id DESC;

DISTINCT (or GROUP BY) in the outer SELECT may or may not be needed.

Related:

Best way to get result count before LIMIT was applied


SQL Server 2014 Concurrent input issue In table Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are OrderID and OrderLineID
where OrderID can be duplicated but OrderLineID has to be unique within an order.

Since orders can be amended, the stored procedure first checks if the received order's OrderLineID is already present in the table, and then decides to insert or to update. To do this we:

build the insert and update statements dynamically from the XML input
insert into customers table
insert into shippingAddresses table

and then the main table:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID ......)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID

Or does the MERGE function offer better performances/control?

But the question is the following:

Due to line issue/server busy and so on, the Order message (or the modification), could be sent more than once, and we do not know in which sequence. Therefore, to avoid the Order arriving after the amendment, and therefore overwriting the amendment, we added a time column:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID AND LastModified<@CreatedTime

In this way if the later message is older than the previous, it has no effect on the table.

But, it could happen that the message and its modification are sent twice (or more) in so little timeframe that the latter message arrives before the former has been saved. Therefore the IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID) is TRUE for both executions of the stored procedure, and both times it generates an INSERT and we find duplicated rows.

Perhaps this can be avoided by simply setting OrderLineID as unique key?

I also read something about using:

set transaction isolation level serializable

but I'm not sure how this is handled. I not only want to avoid the duplicate rows but to be sure the later message executes the UPDATE query and not throw a unique key violation.

Community wiki answer:

MERGE has some issues you should be aware of, see Use Caution with SQL Server's MERGE Statement by Aaron Bertrand.

More specifically to your present situation, MERGE provides no additional protection against concurrency issues beyond an automatic containing transaction. You would need to add a SERIALIZABLE hint (or its synonym, HOLDLOCK) to the MERGE statement as well, see “UPSERT” Race Condition With MERGE by Dan Guzman. Note that an UPDLOCK hint is not necessary with MERGE.

For a more general overview of the issues and options, see the following by Michael J. Swart:

SQL Server UPSERT Patterns and Antipatterns
Mythbusting: Concurrent Update/Insert Solutions

A popular and robust solution is to add a WITH (UPDLOCK, SERIALIZABLE) hint to table access in the NOT EXISTS check, with a containing transaction around the whole UPSERT operation.

Using your example:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT *
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
)
    INSERT INTO Orders () VALUES ()
ELSE
    UPDATE Orders
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;

COMMIT TRANSACTION;

Or equally:

BEGIN TRANSACTION;

INSERT INTO Orders ()
SELECT ...
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
);

IF @@ROWCOUNT = 0
BEGIN
    UPDATE Orders
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

The order of the INSERT and UPDATE operations can be switched so that whichever you expect to succeed most often is tried first. The hints must always be applied to the first operation.

For example, if updates are expected to be most numerous:

BEGIN TRANSACTION;

UPDATE Orders WITH (UPDLOCK, SERIALIZABLE)
SET ...
WHERE OrderLineID=@OrderLineID
AND LastModified<@CreatedTime;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Orders ()
    SELECT ...
END;

COMMIT TRANSACTION;

The uniqueness constraint mentioned in the question is a good idea for consistency in general, but not strictly needed for the methods noted here. If you were to use Michael Swart's Just Do It pattern, a uniqueness guarantee would be required for correct operation.

As a reminder, if you prefer MERGE for whatever reason, you must use a SERIALIZABLE hint against the target table.

While it is true in general that locking hints should be avoided as far as possible, this is one example of a valid use case.


'mongod wiredTiger' on Ubuntu? I cannot use wiredTiger as a service for MongoDB.

I followed this step by step guide;
http://docs.mongodb.org/manual/release-notes/3.0-upgrade/

Step-1: Start 3.0 mongod.OK, I did.
Step-2: Export the data using mongodump. OK
Step-3: Create data directory for WiredTiger. OK - user permissions were adjusted.
Step-4: Restart the mongod with WiredTiger. OK

mongod --storageEngine wiredTiger --dbpath <newWiredTigerDBPath>
And yes, mongod worked. But the Step-5 says that "Upload the exported data using mongorestore." However my console remains busy and says "waiting for connections on port 27017" because the last command does not include a service like service mongod start.

Then I tried editing my /etc/mongod.conf file to run as a service. But I couldn't. I added wiredTiger directory as dbpath but I guess I need to edit /etc/init/mongod.conf file because it contains some codes that change the permissions of the db directory.

After spending 8 hours, I don't think there's sufficient documentation to help,
 so I'd like to ask;
What should I do in order to be able to use wiredTiger with MongoDB?
I'm wondering if there's anybody who has managed to achieve this?

console

Edit on 2015-04-01:
I tried change-storage-engine-to-wiredtiger section that is on the documentation.

I wasn't able to use wiredTiger upgrading MongoDB. However, at May 17 I uninstalled MongoDB 2.6 then installed MongoDB 3.0.3 . Immediate after the installation, I added storageEngine=wiredTiger on top of my mongod.conf file. Then I gave sudo service mongod start command and eventually I could.

Edit:

For fresh installed as directed by official documentation;

Open configuration file using sudo nano /etc/mongod.conf
Change the #  engine line to engine: wiredTiger like the below
Run mongod using the command sudo service mongod start

.

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
  engine: wiredTiger
#  mmapv1:
#  wiredTiger:

Edit:

If the current version is supported the wiredTiger;

Get the backup of the current database using mongodump command
Stop the mongod service using sudo service mongod stop command
Add storageEngine=wiredTiger text as the first line of mongod.conf file
Delete the all file on /var/lib/mongodb (or /data/db folder if used)
[This is important. Because MongoDB cannot convert the current MMAP db files to wiredTiger format]
Start the mongod service using sudo service mongod start command
Restore the database from the backup using [mongorestore][2] command
wiredTiger is being used...


Duplicate rows with primary key constraint in PostgreSQL 10.6 I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup.

However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table group by id having count(*) > 1) as t1 as per this old stack post. However, I'm running Postgres 10.6, which should be far after that bug.
Any ideas?

I have some suggestions based on problems I had before, not with PostgreSQL 10, but with 9.1:

Try to reindex
Try vacuum full analyze verbose

If first two option don't work, try a dump and restore.

This happens because of a bug in indexes.