How to write a procedure /job to kill all session running for more than 5mins in Oracle? How to write a procedure /job to kill all session running for more than 5mins in Oracle?

So, I query v$session and find out that for a particular user, program pair if the session has been active for more than 5mins kill it.

Also, how to write a job to run this procedure for that pair every minute until 5mins and then kill the process (by sid) ..

create or replace procedure kill_session( p_sid in number, p_serial# in number )
as
begin
    for x in ( select *
                 from v$session
                where username = USER
                  and sid = p_sid
                  and serial# = p_serial# )
    loop
        execute immediate 'alter system kill session ''' ||
                 p_sid || ',' || p_serial# || '''';
        dbms_output.put_line( 'Alter session done' );
    end loop;
end;

How to check the time if the session has been active for more than 5mins and how to make it run till 5min for a particular user, program pair?

Generally, aborting database queries and DML is not a good idea, and as @Raj already pointed out, killing sessions is also bad idea – actually it's even worse.

Your sessions may be running some transactions which consume undo space (in the undo tablespace of the user), hold locks on tables and occupy space (e.g. for row sorting) in the temporary tablespace. When you kill the session, all the work done by the transaction in-flight should be rolled back, all the undo segments and locks should be released. And while "light" sessions can terminate pretty quickly, others can take time before Oracle engine cleans everything up.

To answer your question we should know why you are killing your sessions.

Is it because the user sessions consume a lot of resources (CPU) if you allow them more than 5 minutes to run?
Is it because the users establish too much of sessions and idling?
Is it because the newly established session starves for resources already used by other sessions and should have them available to run?
Is it because you developed some cool software and you want to limit users to evaluate it only a few minutes before they are disconnected?

Probably you could improve your business logic so as to avoid killing sessions – doing computations on the server side, for example.

Or you could develop some RESTful web application which is run in the middle tier (application server) and performs queries on behalf of users while keeping a reasonable small number of connections to the database using connection pool. For example, you could write Java web application with servlets and JSP using Apache Tomcat JDBC Connection Pool (official documentation for Tomcat 8). You could also switch from dedicated server architecture in favor of shared server architecture.

If your software doesn't support connection pooling you could employ Database Resident Connection Pooling via OCI.

But if nothing I suggested to you meet your needs, you can implement your resource plan using Database Resource Manager, define directives which abort running queries or kill sessions which run or idle too long, and everything you need to implement this is described in chapter Managing Resources with Oracle Database Resource Manager in Oracle Database Administrator's Guide. The keywords are CANCEL_SQL, KILL_SESSION, MAX_EST_EXEC_TIME, MAX_IDLE_TIME.


does xml element exist, regardless of value? EDIT: I demonstrated my ignorance by confusing attribute with element, I updated my question. Sorry. To clarify, I'm looking for the existence of the element code in:

<conditionmessage:code>MYCODE</conditionmessage:code>

I have a bunch of schemas where there is a table that contains an XML column. There are a handful XML indexes on each column. I'm pretty sure that the XML-schemas differs between the SQL-schemas, nevertheless, the same XML-indexes exists in each schema. I suspect copy/paste and I can't seem to find any documentation for the XML-schemas. So I thought of a more pragmatic approach namely to loop through the XML columns and check whether each element exists or not. The tables have been around for a while, so every possible element should be there by now.

Now to the question, what's the best way to check for the existence of an element, regardless of value? Unfortunately, my brain is incompatible with XPATH/XQUERY, and I have to struggle every time to just convince my self that the query does what it is supposed to do. My current approach seems to work, but I'm curious if there is a better way. The cardinality of the smallest table is ~100.000 rows and the largest ~100.000.000 rows:

import ibm_db
import ibm_db_dbi
...
conn = ibm_db_dbi.connect("...")
c1 = conn.cursor()

for schema in [ "S1", "S2", ... ]:
    for attribute in [ "a1", "s2", ...]:
        sql = f"""
            select * from {schema}.ATOM_ENTRY
            WHERE XMLEXISTS('$d//*:{attribute}[fn:matches(text(), $p, "i")]'
                PASSING ENTRY_XML AS "d", CAST('\S*' AS VARCHAR(128)) as "p")
            fetch first 1 rows only
            optimize for 1 rows"""
        c1.execute(sql)
        exists = False
        for row in c1.fetchall():
            exists = True
        print(f"{schema}.ATOM_ENTRY -> {attribute} : {exists}")

Try this:

For attributes

SELECT COUNT(x.a2) cnt_a2, COUNT(x.a4) cnt_a4
FROM
(
VALUES
  '<doc><c a2=""/></doc>'
, '<doc></doc>'
) T (D)
, XMLTABLE
(
  '$doc' PASSING XMLPARSE(DOCUMENT T.D) AS "doc"
  COLUMNS
    a2 XML PATH '//*[fn:exists(@a2)]'
  , a4 XML PATH '//*[fn:exists(@a4)]'
) X
WHERE
--XMLEXISTS('$doc//*[@a4 or @a5]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc")
XMLEXISTS('$doc//*[@a2 or @a4]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc")
;

The result is:

|CNT_A2     |CNT_A4     |
|-----------|-----------|
|1          |0          |

For elements with namespaces

SELECT COUNT(x.code1) cnt_code1, COUNT(x.code3) cnt_code3
FROM
(
VALUES
  '<doc xmlns:conditionmessage="http://xyz.com"><conditionmessage:code1>MYCODE</conditionmessage:code1></doc>'
, '<doc xmlns:conditionmessage="http://xyz.com"><conditionmessage:code2>MYCODE</conditionmessage:code2></doc>'
) T (D)
, XMLTABLE
(
  XMLNAMESPACES('http://xyz.com' AS "conditionmessage"), '$doc' PASSING XMLPARSE(DOCUMENT T.D) AS "doc"
  COLUMNS
    code1 XML PATH '//*[fn:exists(conditionmessage:code1)]'
  , code3 XML PATH '//*[fn:exists(conditionmessage:code3)]'
) X
WHERE XMLEXISTS('declare namespace conditionmessage = "http://xyz.com"; $doc//*[conditionmessage:code1 or conditionmessage:code3]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc");

The result is:

|CNT_CODE1  |CNT_CODE3  |
|-----------|-----------|
|1          |0          |


Using a second table for unique key? This is a fairly noob question. I work with an old-school PowerBuilder software (PowerCampus) that has some strange design decisions from the 80s and 90s.

What's perhaps the most important table in the system, dbo.ACADEMIC, has only a clustered primary key made up of multiple columns. I need to be able to SELECT and UPDATE rows on dbo.ACADEMIC via an immutable key such as a GUID, but I also can't alter the table.

There are many reasons I can't modify the structure of this table, but chief among them is that one school tried it and broke the desktop client software.

Here's a simplified sample of ACADEMIC's primary key:

PEOPLE_CODE_ID ACADEMIC_YEAR ACADEMIC_TERM CURRICULUM
-------------- ------------- ------------- ----------
P000011111     1996          SPRING        URBAN
P000022222     2012          SPRING        HCSMGT
P000033333     2002          SUMMER        CHEMIS
P000044444     2015          FALL          SEDUCA
P000055555     1983          SUMMER        POLSCI

Some columns that are part of the key change a lot -- when a student changes curriculum, for example. Integrating with other systems is very hard because of this lack of an immutable id.

Can this be solved by creating another table with a unique primary key plus the identifying columns from ACADEMIC? Then a view could be constructed that would show all the data from ACADEMIC plus the id column; integration projects could use this view.

Proposed custom.AcademicKey table:

id                                   PEOPLE_CODE_ID ACADEMIC_YEAR ACADEMIC_TERM CURRICULUM
------------------------------------ -------------- ------------- ------------- ----------
956EB195-C7C3-4E47-9A1B-0000191B95A2 P000011111     1996          SPRING        URBAN
FE00D2F4-5F5E-45D2-AB63-00002CC92F40 P000022222     2012          SPRING        HCSMGT
C1AF44CD-DDD1-4D46-A19E-0000A841B70F P000033333     2002          SUMMER        CHEMIS
D4E8FC3F-CECC-4195-9924-0000B9BC33A7 P000044444     2015          FALL          SEDUCA
456B16ED-C504-456B-A2E5-00019F244A95 P000055555     1983          SUMMER        POLSCI

Would a foreign key tied to the clustered key on ACADEMIC accomplish this?

Would triggers have to be added to ACADEMIC in order to populate new rows into AcademicKey?

Can this be solved by creating another table with a unique primary key plus the identifying columns from ACADEMIC?

Yes.  Minimally you need a Foreign Key and an AFTER INSERT trigger.

eg

use tempdb
go
--create schema custom
go

--drop table if exists custom.AcademicKey
--drop table if exists dbo.Academic
go
create table dbo.Academic
(
   PEOPLE_CODE_ID varchar(20),
   ACADEMIC_YEAR int,
   ACADEMIC_TERM varchar(10),
   CURRICULUM varchar(20),
   constraint pk_Academic
     primary key (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
)
create table custom.AcademicKey
(
   Id uniqueidentifier default (newsequentialid()),
   PEOPLE_CODE_ID varchar(20),
   ACADEMIC_YEAR int,
   ACADEMIC_TERM varchar(10),
   CURRICULUM varchar(20),
   constraint pk_Academic
     primary key nonclustered (id),
   constraint ak_AcademicKey
     unique clustered (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM),
   constraint fk_AcademicKey_Adademic
     foreign key (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
     references Academic (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
     on update cascade
     on delete cascade
)

go

create or alter trigger Academic_CreateAcademicKey
  on dbo.Academic after insert
as
begin
    set nocount on;

    insert into custom.AcademicKey(PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
    select PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM
    from inserted;
end

go

insert into Academic(PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
values ('P000011111',     1996,          'SPRING',      ' URBAN')

select * from custom.AcademicKey

update Academic set CURRICULUM = 'CHEMIS' where PEOPLE_CODE_ID = 'P000011111'

select * from custom.AcademicKey

delete from Academic where PEOPLE_CODE_ID = 'P000011111'

select * from custom.AcademicKey


Using wildcards in NEAR in a Full Text Search Let's say I have the following text somewhere in a document stored in an FT indexed table in SQL Server

minsitry of education and science

I want the query to ensure the words are NEAR, but the query won't contain the exact words (instead I would like to use wildcars) - something like

CONTAINS(FileContent, 'NEAR(minist*, educ*, 10, TRUE)')

If I try this query, no results will be returned as NEAR seems to work only with exact words.

My question - is there a way to complete a search like this using SQL Server queries?

Have you tried CONTAINSTABLE?  This includes a Rank column so will give you more visibility of the ranking process.  This worked for me:

SELECT *
FROM dbo.yourTable t
    INNER JOIN CONTAINSTABLE(dbo.yourTable, FileContent, '"minis*" NEAR "educ*"') ct ON t.yourKey = ct.[Key]
ORDER BY RANK DESC

Update: found the following paragraph in the book Pro Full-Text Search in SQL Server 2008 by Michael Coles with Hilary Cotter

  "...the NEAR operator is basically useless with the iFTS CONTAINS and
  FREETEXT predicates (it’s no different from using the AND operator).
  To get a true proximity search, you need to use CONTAINSTABLE or
  FREETEXTTABLE and restrict the results with a WHERE clause."

Ch3, p67


Why do subsequent unmet conditions in my CASE statement affect my return data type? I have the following variables and CASE statement:
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

There's nothing special about it. It returns 8 (int) as I expect it to.
However, as soon as I add to my CASE statement a conditional that returns a datetime data type, I no longer get 8 (int), despite continuing to use "I" as the @CONVERT_CODE value.
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
                          WHEN 'D' THEN GETDATE()
       END AS ConvertedValue

Instead, I get the datetime value of "1900-01-09 00:00:00.000"
I would think "D" shouldn't get evaluated at all because, in SQL Server, once a condition evaluates to true ("I" in this case), it should break out of the CASE statement--so I'm not sure why "D"s data type is affecting the overall return data type. Also, it doesn't matter if I reverse the order of the conditions and still use "I":
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'D' THEN GETDATE()
                          WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

Am I missing something?

The column defined by your CASE expression has a data type, as all columns do.
SQL Server determines the resulting type in its usual way, making use of data type precedence. The type datetime has a higher precedence than integer.
You might be expecting the CASE expression to return different types on different paths, but that is not possible (sql_variant aside).


MySQL illegal mix of collations, ASCII to UTF-8 I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn't need UTF-8 overhead:
CREATE TABLE `things` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `things_uuid_unique` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and I'm getting query failures like this:
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations
(ascii_bin,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='
(SQL: select * from `things` where (`uuid` = 1abb9e11-4f00-4904-988e-233a3c0ce411) limit 1)

My connection is using utf8mb4 and utf8mb4_unicode_ci, the other fields, table, and database are using the same collation, and my scripts themselves are also UTF-8, though that makes no practical difference here since it's just ASCII. This particular query uses a string literal containing only 7-bit ASCII chars, not a field name or variable.
I don't understand why this fails, since (unlike ISO-8859 charsets), ASCII is UTF-8 compatible; there is no ASCII string that is not also a valid UTF-8 string, so why wouldn't this be coercible? Is it something to do with ci vs bin? I can't solve this by switching the connection to ASCII because the real queries use multiple fields, some of which are UTF-8.
I know that I can force the comparison using:
select * from `things` where (`uuid` = BINARY '1abb9e11-4f00-4904-988e-233a3c0ce411')

But that also seems unnecessary, and not something I've needed to do before, in the same circumstances. I guess worst case I could switch the UUID field to UTF-8, but that offends my developer sensibilities!
It's doubly frustrating as I know I've used this pattern successfully many times before!

It turns out that I am doing everything correctly, and I'm not insane! This problem is a bug in MySQL 8.0.22 (also this) that is apparently fixed in 8.0.23. Unfortunately I'm using Percona server which has not yet released its 8.0.23 derivative.


MySQL locking tables / rows on select statement I have a web app that does an addition to a field based on a user input.

So say for example User-A inputs 7, my web app would first query the contents of the field for the current number in there, which is say '10' in this case, then adds 7 to it and updates the filed with the total. In this case 17. Then User-B inputs 3, the same routine happens and now the filed contains 20 etc...

My concern is that if 2 or 2000 users query the table at the same time and get the same number back, for example 10, then it is going to go all sorts of wrong when it comes to the addition and writing the new total back.

Should I be explicitly locking the table / row on the SELECT statement? Is that normal practice in this situation and will it solve this issue.

This is basically a aggregate table for a survey system so I can quickly run and display reports on the daily totals etc...

I think that a simple update statement will work just fine:

UPDATE TableName
SET col = col + @user_input
WHERE key_col = @key ;

The update will take the necessarily lock (probably on one row only) and any other update (by other users) will either wait for the lock to be released or fail.

If you want the user to see the result of the update, you can enclose in an transaction, something like this:

START TRANSACTION;
    UPDATE table_name
    SET col = col + @user_input
    WHERE key_col = @key ;

    SET @r =
        ( SELECT col
          FROM table_name
          WHERE key_col = @key
        ) ;
COMMIT ;
SELECT @r AS col_updated ;


How to find a specific column name in table design view? I have a table in my database in SQL server 2014 with a lot of columns, and when I want to find a column for modify properties, it is difficult to find that column, how can I find that column (ex: column name = "Birthdate"), has SSMS have any tools (such as find tool) for finding that column.

If you start by looking at the table definition in the Object Explorer and expand the columns properties, you can simply type the first letter of the column you are looking for.  If you keep hitting the same letter, SSMS will cycle through all the columns that start with that letter...

if you then right click the column and and choose modify, it will open the designer at the right column...

(using ssms 17.9.1 - but worth a try)


How to do a recursive sum of children in mysql? I've searched and tried for long hours, and can't seem to get what I need.
I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT have debits and credits itself.
My fiddle of data is here
What I need is to show sum of "Debits of children" and sum of "Credits of children" in their parent's row, like this picture:

How can I do it in MySql?
Thank you

With your example, you can run
CREATE PROCEDURE procedure_name()
BEGIN
        DECLARE finished INTEGER DEFAULT 0;
        DECLARE _accoujntno INTEGER DEFAULT 0;
        DECLARE _uacn varchar(191) DEFAULT "";
    DEClARE curaccount
        CURSOR FOR
            Select account_no ,user_account_number FROM TBL1 WHERE length(user_account_number)-length(replace(user_account_number,'-',''))+1  = 2 order by user_account_number;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

        CREATE TEMPORARY TABLE credits(
        user_account_number VARCHAR(191),
        `Debit_Before_Period` DECIMAL(20,2),
         `Credit_Before_Period` DECIMAL(20,2),
         `Debit_In_Period` DECIMAL(20,2),
         `Credit_In_Period` DECIMAL(20,2)
        );

        OPEN curaccount;

    getaccount: LOOP
        FETCH curaccount INTO _accoujntno,_uacn;
        IF finished = 1 THEN
            LEAVE getaccount;
        END IF;
                INSERT INTO credits SELECT _uacn ,SUM(`Debit Before Period`),SUM(`Credit Before Period`) ,SUM(`Debit In Period`) ,SUM(`Credit In Period`)
                FROM TBL1
                WHERE LEFT(user_account_number,7) = _uacn AND account_no <> _accoujntno;
    END LOOP getaccount;
    CLOSE curaccount;

   SELECT * FROM credits;
END

The resulting temporary table credits you can use to run  further analysis
simply add the procedure and CALL procedure_name()
In a query tab in phmyadmin wor mysql Workbench you need to add DELIMITER to the procedure, but that doesn't work in dbfiddle
example
It is like in the comment
CREATE PROCEDURE procedure_name()
BEGIN
        DECLARE finished INTEGER DEFAULT 0;
        DECLARE _accoujntno INTEGER DEFAULT 0;
        DECLARE _uacn varchar(191) DEFAULT "";
    DEClARE curaccount
        CURSOR FOR
            Select account_no ,user_account_number FROM TBL1 WHERE length(user_account_number)-length(replace(user_account_number,'-',''))+1  = 1 order by user_account_number;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

        CREATE TEMPORARY TABLE credits(
        user_account_number VARCHAR(191),
        `Debit_Before_Period` DECIMAL(20,2),
         `Credit_Before_Period` DECIMAL(20,2),
         `Debit_In_Period` DECIMAL(20,2),
         `Credit_In_Period` DECIMAL(20,2)
        );

        OPEN curaccount;

    getaccount: LOOP
        FETCH curaccount INTO _accoujntno,_uacn;
        IF finished = 1 THEN
            LEAVE getaccount;
        END IF;
                INSERT INTO credits SELECT _uacn ,SUM(`Debit Before Period`),SUM(`Credit Before Period`) ,SUM(`Debit In Period`) ,SUM(`Credit In Period`)
                FROM TBL1
                WHERE LEFT(user_account_number,3) = _uacn;
    END LOOP getaccount;
    CLOSE curaccount;

   SELECT * FROM credits;
END

new example


Oracle 12c in-memory option I've been told that there's an oracle in-memory option for 12c and that it uses columnar compression to get some great query speeds. I'd like to leverage it for some business intelligence stuff. I've been searching google and the only thing I can find are announcements and news articles. Does anyone have any code snippets or any directions on where I should look to implement this or learn more about it?

It's still in beta.  It won't be released till the 12.1.0.2 patchset.  As such, unless you're in the beta program, not much information is available yet.

I think the NDA has been lifted, as I saw Tom Kyte speak about it at Collaborate 14 in Las Vegas a couple of weeks ago, but, you can't actually use it yet.

Hope that helps.


MySQL Correlated Subquery (with multiple columns) per row MySQL documentation states that:

  Subqueries in the FROM clause cannot be correlated subqueries. They
  are materialized in whole (evaluated to produce a result set) during
  query execution, so they cannot be evaluated per row of the outer
  query.

Okay, fine, I understand why this is. However, what if you want to repeat a specific query on multiple values?

SELECT COUNT( returning_visitors.per_ip ) AS count, AVG( returning_visitors.per_ip ) AS num_visits
FROM (
    SELECT COUNT( * ) AS per_ip
    FROM site_hits_unique
    WHERE site_hits_unique.site_id = ___INPUT___
    AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
    GROUP BY site_hits_unique.site_id, site_hits_unique.ip
    HAVING per_ip >1
) AS returning_visitors

Let's say I want to retrieve the result of this query on multiple values in the form of a result table, using the values from SELECT id FROM sites. Is there a way to do this in a single query without using a stored procedure? Something like:

SELECT id, COUNT( returning_visitors.per_ip ) as readers, AVG( returning_visitors.per_ip ) as avg_visits_pr
FROM sites
SUBQUERY-PER-ROW (
    SELECT COUNT( * ) AS per_ip
    FROM site_hits_unique
    WHERE site_hits_unique.site_id = sites.id
    AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
    GROUP BY site_hits_unique.site_id, site_hits_unique.ip
    HAVING per_ip > 1
) AS returning_visitors
WHERE sites.id IN (162888, 42705, 11412)

SUBQUERY-PER-ROW here is pseudo-code. JOIN will not work because of the above quoted restriction.

EDIT:

Another attempt at pseudo-code using info from http://dev.mysql.com/doc/refman/5.7/en/row-subqueries.html:

SELECT id, (
    SELECT COUNT( per_ip ) AS count, AVG( per_ip ) AS num_visits
    FROM (
        SELECT COUNT( * ) AS per_ip
        FROM site_hits_unique
        WHERE site_hits_unique.site_id = sites.id
        AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
        GROUP BY site_hits_unique.site_id, site_hits_unique.ip
        HAVING per_ip >1
    ) AS returning_visitors
) as (readers, avg_visits_pr)
FROM sites
WHERE sites.id IN (162888, 42705, 11412)

(it doesn't work, this isn't actual valid MySQL syntax, just a pseudo-code example)

Is this what you are looking for?

SELECT  s.id,
        COUNT( rv.per_ip ) as readers,
        AVG( rv.per_ip ) as avg_visits_pr
    FROM  sites AS s
    JOIN
    (
        SELECT  u.site_id,
                COUNT( * ) AS per_ip
            FROM  site_hits_unique AS u
            WHERE  u.date >= CURDATE( ) - INTERVAL 30 DAY
            GROUP BY  u.site_id, u.ip
            HAVING  per_ip > 1
    )  AS rv ON rv.site_id = s.id
    WHERE  s.id IN (162888, 42705, 11412)

Indexes:

site_hits_unique: INDEX(site_id, date, ip)

Or maybe this:

SELECT  site_id,
        COUNT(DISTINCT ip) as readers,
        COUNT(*) / COUNT(DISTINCT ip) as avg_visits_pr
    FROM
    (
        SELECT  site_id, ip, COUNT(*) AS per_ip
            FROM  site_hits_unique
            WHERE  date >= CURDATE( ) - INTERVAL 30 DAY
              AND  site_id IN (162888, 42705, 11412)
            GROUP BY  site_id, ip
            HAVING  per_ip > 1
    ) AS rv
    GROUP BY  site_id;


Postgres date_trunc quarter with a custom start month I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01.
Is there any way possible or workaround I can do to say that the first month of first quarter is for instance September? So instead of the traditional: Q1: 1-3, Q2: 4-6, Q3: 7-9, Q4: 10-12
I want to be able to specify which month is the start of Q1, so if I say September it should become: Q1: 9-11, Q2: 12-2, Q3: 3-5, Q4: 6-8
Here is how I make a standard quarterly score average with default quarter.
SELECT id,
       name,
       date_trunc('quarter', date) AS date,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

I'm open for all suggestions to make this work.

If you don't really need the start date of the quarter, you can easily do this with a CASE expression:
SELECT id,
       name,
       case
         when extract(month from date) in (9,10,11) then 'Q1'
         when extract(month from date) in (12,1,2) then 'Q2'
         when extract(month from date) in (3,4,5) then 'Q3'
         when extract(month from date) in (6,7,8) then 'Q4'
       end as quarter,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

Of course you can put that into a stored function to make your queries simpler.


media family on device is incorrectly formed. SQL Server cannot process this media family I am trying to restore a .BAK in SQL server but get the following error:

  Msg 3241, Level 16, State 7, Line 1 The media family on device 'c:\glyn\JA.bak' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I have tried restoring using 2012, 2008 and even 2005 but nothing works, I have used the following query and through the Tasks >> Back up but nothing works, here's my SQL:

RESTORE DATABASE JA FROM DISK='c:\glyn\JA.bak'

Does anyone have any suggestions? I've read that the bak could be corrupt, it was sent to my by another developer on DVD who is working on the project.

If you are attempting to restore newer database to older SQL version you can always simply manually create the database in the older SQL version and utilize the task - generate scripts and include the data.

Save to File --> Advanced--> Types of data to script --> Schema and data.

Also make sure to Script for Server Version.

This might be the only supported option if you're trying to go from newer SQL Server to older version.

Related: Restore SQL Server 2012 backup to a SQL Server 2008 database?


Help me about syntax STUFF (SELECT... for XML PATH) Here is script of 3 table:

create table products(
pid varchar(10),
pname varchar(20));

create table [Order Details](
oid varchar(10),
pid varchar(10),
quantity int);

create table orders (
oid varchar(10),
oname varchar(10));

insert into [Order Details]
select 'o1', 'p1', 10
union all
select 'o1', 'p2', 20
union all
select 'o1', 'p3', 20
union all
select 'o2', 'p1', 10
union all
select 'o2', 'p2', 10
union all
select 'o3', 'p1', 10

insert into products
select 'p1', 'Product A'
union all
select 'p2', 'Product B'
union all
select 'p3', 'Product C'

insert into orders
select 'o1', 'Order #1'
union all
select 'o2', 'Order #2'
union all
select 'o3', 'Order #3'

select * from products;
select * from orders;
select * from [Order Details];

And here is my query:

select oname, pname, quantity
from orders o1 inner join [Order Details] o2 on o1.oid=o2.oid
inner join products p on o2.pid=p.pid
group by oname, pname, quantity

The output is like:

Order Name  Product Name Quantity
Order #1    Product A    10
Order #1    Product B    20
Order #2    Product A    10

I need to use syntax stuff (select... for XML PATH) for reporting likes that:

Order Name  Product Name           Quantity
Order #1    Product A, Product B   30
Order #2    Product A              10

Because there are 3 tables (I have searched but the examples just have 2 table only). So I have tried many times but the result is always failed. I'm embarrassed with the rest table#3... Please help me, thanks so much!

Based off of your examples queries, this should work out for you (Comments Included):

-- works
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT
                            ', ' + p.pname
                FROM        dbo.products AS p
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid --Join the products to the Order Details...
                WHERE od1.oid = o1.oid  -- Only for the "main" orders
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name], -- remove the first comma and space.
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid
GROUP BY    o1.oname, o1.oid;

However, your expected output doesn't have any Product C so maybe you don't want to show them. In which case, you would need to join back and restrict it away:

-- without Product C?
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT
                            ', ' + p.pname
                FROM        dbo.products AS p
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid
                WHERE od1.oid = o1.oid
                AND p.pid <> 'p3'
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name],
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid

-- Add this join to remove Product C.
INNER JOIN  dbo.products AS [p1] ON p1.pid =o2.pid
                AND p1.pid <> 'p3'

GROUP BY    o1.oname, o1.oid;

But then you're expected output doesn't have Order 3, so you'd need to restrict that away as well. However I'm not going to do that as you should have the info you need to do that yourself.

Best of luck!


Oracle cannot connect to SQL developer as sys account I am trying to connect to SQL developer as sys account.  It is always giving the error invalid username/password. When I try to connect it through SQLPLUS, it gets connect with any wrong password also.

How can I connect to SQL Developer using sys account?

sqlplus user/password as sysdba uses OS authentication by default, and bypasses the username and password check, so as you said, you can provide any password or username.

Logging in from SQL Developer requires a remote session. For a remote SYSDBA connection, you need the following:

set remote_login_passwordfile to EXCLUSIVE or SHARED, but not NONE
create a password file for the database if it doesn't already exist:

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID

After this you should be able to log in with SYS and the password you provided.


Combining 2 MySQL queries with IN clause does not work I have a 2-step query that I am trying to combine into one.

This returns the ids:

select  id
from    (select * from comments
        where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
         order by commentable_id, id) products_sorted,
        (select @pv := '26') initialisation
where   find_in_set(commentable_id, @pv)
and     length(@pv := concat(@pv, ',', id))
;

-- Then I take the results for parent id=26 (from prior query) and put them in an IN clause.

SELECT * FROM reactions
WHERE deleted_at is NULL AND is_removed=0 AND reactable_type LIKE "App%Comment"
AND
reactable_id IN
(
30,
31,
33,
34,
50,
51,
52,
53,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
5819,
6083,
5921,
6390,
54,
56,
57,
58,
59,
60,
61,
62,
5779
)
;

However when I combine the above 2 into one query, this does NOT work and returns a much shorter set of results:

----------------------
SELECT * FROM reactions r
WHERE r.deleted_at is NULL AND r.is_removed=0 AND r.reactable_type LIKE "App%Comment"
AND
r.reactable_id IN
(
select  id
from    (select * from comments
        where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
         order by commentable_id, id) products_sorted,
        (select @pv := '26') initialisation
where   find_in_set(commentable_id, @pv)
and     length(@pv := concat(@pv, ',', id))
)
;

What am I doing wrong?

You have basically two problems here,

Your sub-query is returning row-based results and you need comma-separated results.
You could fix this issue by using the GROUP_CONCAT(value SEPARATOR ',') like this:
...r.reactable_id IN
(
select  GROUP_CONCAT(id SEPARATOR ',')
from    (select * from comments...

But you won't see any result due to the next problem...

Different types (reactable_id is an INT value and the GROUP_CONCAT result is a STRING value. So your final solution is to rewrite your query using JOINS instead of sub-queries.
You can see this post to guide your steps:
https://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why


pg_restore: error: could not read from input file: end of file I do try to create a dump of a table, that is stored inside of my database.
The table is called

webapp_product

In order to create the dump, I do use following commands:

docker-compose exec db1 pg_dump -F tar -d db -U user -t webapp_product > /home/backup.tar

Then, on the other machine:

sudo mv backup.tar ../pgdata
docker-compose exec db1 sh
pg_restore -Ft -C -U user -d db /var/lib/postgresql/data/pgdata/backup.tar

Unfortunately, it always ends up with the same scenario. I do receive following error log:

pg_restore: error: could not read from input file: end of file

I have also tried the other way. After moving the backup file, I have been trying to use:

cat /var/lib/postgresql/data/pgdata/backup.sql | psql -d db -U user

After invoking that command, I have been receiving an error saying that I am unable to create duplicates (IDs of the records were the same.)
In order to go further, I have tried to turn off all of the constrains, but without any expected effects.

@Update1

The backup.tar file consists of a following lines:

-- NOTE:
--
-- File paths need to be edited. Search for $$PATH$$ and
-- replace it with the path to the directory containing
-- the extracted data files.
--
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.1 (Debian 12.1-1.pgdg100+1)
-- Dumped by pg_dump version 12.1 (Debian 12.1-1.pgdg100+1)

Can it affect the process of restoring the data?

and has a following ending:

--
-- PostgreSQL database dump complete
--

You really have two questions here.  First you have a corrupted tar file.  I'd blame that on your usage of docker.  If you run tar -tf ... on the docker side before moving the file, was it corrupted there?

Second, you don't want to restore data, you want to merge it.  pg_dump/pg_restore doesn't do that.  You will have to figure out what you want to do (overwrite, aggregate, assign new PKs and just insert them as new rows) and then implement that. You likely want to use COPY or \copy to get the data out of the first system so the second one can see it.  After that, it is really up to you what you do with it.  You can implement the merging in python or Perl or SQL (after \copy into staging tables) or whatever your favorite tool is.


Order By First Condtition and If multiple matches, order by second condition I was practicing SQL problems at hackerrank.com when I came across the following question:

  Query the Name of any student in STUDENTS who scored higher than
  75. The Marks field in the table stores the marks for student. Order your output by the last three characters of each name. If
  two or more students both have names ending in the same last three
  characters (i.e.: Bobby, Robby, etc.), secondary sort them by
  ascending ID.

To solve this, I put together a query with the help of this post:

select Name
from STUDENTS
where Marks > 75
order by case when substring(Name from -1 for 3) then 1 else 0 end, ID asc;

Now I am not sure that it actually does what the question is asking since, I have no idea idea of using case in queries. Any help will be greatly appreciated.

Your query is wrong.

case when substring(Name from -1 for 3)
     then 1
     else 0 end

The expression substring(Name from -1 for 3) returns one last symbol from Name (if exists). Not 3 last as you want...

So the whole expression returns 0, when Name is NULL, empty string, or when the last symbol in Name is not a digit rather than 0. And 1 otherwise (when last symbol exists, and it is a digit from 1 to 9).

Correct query (looking on your query) is

SELECT Name
FROM Students
WHERE Marks > 75
ORDER BY RIGHT(Name, 3) ASC,
         id ASC;


Database can be accessed by all I have installed Oracle 10g Lite on an administrative system with 32-bit Windows XP as the OS. I have N client systems with a mix of 32/64-bit Windows 7/8 OSes.

How can I configure the server as well as the clients so that the database can be accessed by all? With it we have a wireless router, IB-WRX300-N , providing the internet to all.

When you say Oracle 10g Lite I assume you mean Express Edition.  I recommend you read through the documentation, particularly the section on Connecting to the Database.  If you have a more specific question after reading that, we would be glad to help.


How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL? I would like to say that i had configured "Automatic DB Backup" as well as "Automatic DB Cleanup Task" through "Maintenance Plan" in MS SQL Server 2012 on my Production Database. And that is working properly. I had set up my "Maintenance Clean up" task "Older than 4 Weeks".
Using the following T-SQL Statement:-

use msdb
select * from
dbo.backupmediafamily;

I am able to view the backup history of my DB. That is working properly through maintenance plan schedule.
I setup Automatic Backup of 3 Databases through the "Maintenance Plan", But I had configured Maintenance Cleanup Task to delete files older than 4 weeks. During the last month there are 90 (Backup) files . I configured this plan on 26-03-2015, so now my Maintenance Cleanup Task is working now and I also delete (backup files, .bak) from the specified location.

Now I am able to check out deleted backup file from specified location with Maintenance Plan. But after few days or few months I may be not able to collect exact deleted backup file records as well as not recognize exact days delete backup file records.
So, I want to know exact deleted (.bak) records from through T-SQL.

If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) or run the following to view the backup history:

First create this function - I'll just have it in msdb - which is not a good idea.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Then you can run the following

SELECT
 sd.name AS database_name,
 ISNULL(bs.backup_finish_date, 0) AS backup_date,
 ISNULL(CASE bs.[type]
  WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END, 'NEVER') as type ,
 bm.physical_device_name,
 dbo.fc_FileExists(bm.physical_device_name) as DoesFileExists,
 bs.backup_size / 1024 / 1024 AS backup_size,
 DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS duration
 FROM master.dbo.sysdatabases AS sd
 LEFT OUTER JOIN msdb..backupset AS bs ON sd.name = bs.database_name
 LEFT OUTER JOIN msdb..backupmediafamily AS bm ON bm.media_set_id =     bs.media_set_id
  GROUP BY
   sd.name,
   bm.physical_device_name,
   bs.backup_size / 1024 / 1024,
   DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date),
   bs.backup_finish_date,
   bs.[type],
   dbo.fc_FileExists(filename)
 order by bs.backup_finish_date desc


Reduce the allocated size of a DB file Is it possible to reduce the initial log-file size of a DB.
See the screen-shoot below, the log-file shouldn't be more than 500 MB with an autogrowth of 100 MB.
I dont want to shrink the file, i want to reduce the allocated space to 500 MB.
How do i do it?

Depend on your database activity and database recovery mode you can manage TLOG size in different ways.

First of all to reduce TLOG usage you have to make transactions smaller.

If the database is in FULL recovery mode, do TLOG backups often.

As lots of guru's say - don't touch TLOG and TempDB they grew as they need to.


Writing SQL query equivalent to relational division of relational algebra? These are the list of 3 tables

EMPLOYEE (Ssn#, Fname, Lname, Bdate, Address, Sex, Salary, Super_ssn)

WORKS_ON (Essn#, Pno, Hours)

PROJECT (Pnumber#, Pname, Plocation)

Question 1: Write SQL query (equivalent to relational division of relational algebra) to retrieve the project name which is contributed by all employees

Question 2: Write SQL query (equivalent to relational division of relational algebra) to retrieve the first name and last name of all employees who work on every project

Then there are list of 6 tables

ITEM (Item#, Item_Name, Unit_Price)

ORDER (Order#, Ord_Date, Cust#, Ord_Amt)

ORDER_ITEM (Order#, Item#, Qty)

WAREHOUSE (Warehouse#, Warehouse_City)

SHIPMENT (Order#, Warehouse#, Ship_Date)

WAREHOUSE_ITEM (Item#, Warehouse#, Qty)

Question 3: Write SQL query (equivalent to relational division of relational algebra) to list Item_Name and Quantity that are stored in all warehouses in Sydney

All these 3 questions are assignment questions.
However, I have tried and answered them and which is shown below:

Answer for Question 1:

SELECT Pname
FROM PROJECT
WHERE EXISTS (SELECT Pname
              FROM PROJECT, EMPLOYEE
              WHERE PROJECT.Pnumber = WORKS_ON.Pno);

Answer for Question 2:

SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS (SELECT Fname, Lname
                            FROM EMPLOYEE, WORKS_ON, PROJECT
                            WHERE EMPLOYEE.Ssn = WORKS_ON.Essn
                              AND WORKS_ON.Pno = PROJECT.Pnumber);

Answer for Question 3:

SELECT Item_Name, Qty
FROM ITEM, ORDER_ITEM
WHERE ITEM.Item# = ORDER_ITEM.Item#
  AND EXISTS(SELECT *
             FROM WAREHOUSE
             WHERE Warehouse_City = 'Sydney');

I just want to confirm that if my SQL query logic for these 3 questions are correct or not.

If it is correct, then I will able to proceed and convert them in relational algebra with relational division which I can do it by myself if my SQL query logic is correct.

If it is not correct, then kindly correct the mistakes in my SQL query logic wherever applicable which would be very helpful for me.

The third seems correct.  The first and second strike me as wrong.  You are looking for projects where any employee contributed, not where all contributed.  In essence you are doing a division on an ANY rather than an ALL criteria.

In these cases you need to change your queries to use NOT EXISTS and look for mismatches.


Side by Side upgrade and migrate SQL Here is the story: We want to do side by side SQL upgrade from 2016 to 2019 with a new windows of 2016. My SA wants to robocopy all drives except drive C from the old server to the new server. Do I get any issues with live or offline databases (data and log files) after transfer? will they get corrupted?  Please provide me your ideas.

If the SA is building a new server then you best option is to do a migration not an upgrade (upgrades also do not have a good rollback plan and therefore is not recommended to do in production).

Here is a decent article with migration steps to start with but there may be adjustments for your environment. https://www.mssqltips.com/sqlservertip/1936/sql-server-database-migration-checklist/

Migrations should not be scary but they need everyone's involvement to make sure that if you do run into snags that they get fixed asap and hopefully documented for future reference.


Programmatically finding minimum set of fields necessary to create a unique composite key I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for every row.

The way I do it now is I just start with 1 field and then I keep concatenating fields until I find a key that is unique for all records. This can be a little time consuming or I might end up concatenating more columns than I really needed to in order to obtain the unique key.

Is there some sort of SQL script I can run on a table that will give me the minimum number of fields (names) I would need to concatenate in order to obtain a unique key? So if there is 1 field in the table that is unique for all records then that 1 field name would be returned. If I needed to concatenate [memberid], [claimid], and [date of service] in order to obtain a unique key then those 3 field names would be the result of the script.

While comments and srutzky offer some great advice, there is a tool made exactly for your situation. The SSIS Data Profiling Task is intended for the purpose of identifying potential Primary Keys (for multiple columns) along with providing a lot of other useful insights into your data.

Simply create a new SSIS package, add the task then use the wizard like interface to profile your data. Create a new output file somewhere you can access it, select Quick Profile... then profile the appropriate information from the database and table desired.

Once you're done run the package and return to the component to select Open Profile Viwer... and look at all the exciting data! The tool gave me a 96% match for one of my fact tables for a three column PK when up to 7 columns were requested for the candidate key consideration (not shown).

Just to be clear I definitely agree that the business rules should determine data uniqueness... just because you find a combination of columns that fits over your data for uniqueness doesn't necessarily mean it makes any sense. =)


Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000] I am trying the following insert query on mysql 8.

Insert Into fence Set
            fenceName='aa',
            radius=2,
            fenceGeometry=ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326)

It works perfectly on mysql 5.7 but here in mysql 8 I get this error Latitude 102.189331 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].

Based on some suggestion I also did this.

ALTER TABLE fence MODIFY fenceGeometry geometry NOT NULL SRID 4326;

But yet the it gives me the same results.

That's because on MySQL 8, they did the unthinkable and implemented the stupid method of doing geography coordinates (like MySQL). This broke backwards compatability.

When you use Geometry on MySQL (any SRID) except 4326 you use (long,lat). That's (x,y) on a Cartesian system.
When you use Geography on MySQL (SRID = 4326) you use (lat, long). That's (y,x) typically. This is done because English speakers tend to say "latitude and longitude" rather than "longitude, latitude"

Amusingly Oracle which owns MySQL does not do this on Oracle Spatial. Oracle Spatial and PostGIS are consistently (long,lat)`. To resolve this switch the order, not

ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326)

But, this,

ST_GeomFromText('POINT(3.880696482497261 102.1893310546875)', 4326)

In MySQL 5.x there was no real geographical coordinate system.

Side note

You shouldn't be using WKT for this if you have the points you can just use Point(), and ST_SRID(pt,SRID)

ST_SRID(Point(3.880696482497261, 102.1893310546875), 4326)

More information

For more information see also,

Does MySQL store point datatypes as LAT LNG or LNG LAT?


How to include the value of the query parameter in the email body using sp_send_dbmail I am calling sp_send_dbmail and setting the query parameter. The resulting email includes the results of the query. I want the actual query (the value of the query parameter) to be appended to the email, so that the email will include the results and then the query itself at the end.

This will aid debugging.

You can set the body of the email to be the query that you want and send the results as an attachment. For example:

DECLARE @sql_cmd NVARCHAR(max) =
'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
              WHERE DueDate > ''2004-04-30''
              AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Adventure Works Administrator',
@recipients = 'danw@Adventure-Works.com',
@query =  @sql_cmd,
@body = @sql_cmd,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;

Sources:

http://msdn.microsoft.com/en-us/library/ms190307.aspx


Why do so many queries have aliases? I've spent a lot of time searching for, researching and implementing various complex MySQL queries over the last several years. Many of them use lots of aliases for tables, even when none are necessary.

Why is this? I've seen a little discussion about using aliases to improve readability, but other than for aggregate/calculated columns, I find that it's majorly detrimental to readability. Your brain has to translate between aliases and actual tables.

For example:

SELECT orders.id, COUNT(*)
FROM orders, order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING COUNT(*) > 1

vs

SELECT o.id, COUNT(*)
FROM orders o, order_items i
WHERE i.order_id = o.id
GROUP BY o.id
HAVING COUNT(*) > 1

To me, the first version is so much easier to read, and this is just a simple example. But I see it used in simple examples a lot. Is it just to type less? Because that's a horrible reason.

I understand it makes sense if you're joining the same table one or more times, but totally not the case much of the time.

Or is there just something I'm missing?

In some cases, MySQL requires an alias, for example, a self join:

mysql> CREATE TABLE test (id SERIAL, name text);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT name FROM test JOIN test on test.id=test.id;
ERROR 1066 (42000): Not unique table/alias: 'test'

mysql> SELECT T1.name FROM test T1 JOIN test T2 on T1.id=T2.id;
Empty set (0.00 sec)

Also when using certain subqueries:

mysql> SELECT name FROM (SELECT * FROM test) JOIN test;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> SELECT T1.name FROM (SELECT * FROM test) T1 JOIN test T2;
Empty set (0.00 sec)

I think this could be the origin of "Always using an alias".

I would agree that the second example is more legible, but that is orthogonal to the use of aliases - nobody is forcing you to use 1-letter aliases, you can name the alias with the same name as the table itself:

SELECT orders.id, COUNT(*)
FROM orders orders, order_items order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING COUNT(*) > 1

If that makes sense, but it is out of the scope of the original question. However, many people do not do that, as the other main reason to use aliases is actually to type less, which it is true that in most contexts decreases legibility - although as it is very extended in the SQL community. However, please take into account that in some cases, conventions makes things more legible (for reference, the i, j, k widely-accepted standard for counters in programming languages).

However, here is my preferred formatting for your query, in which the slight different syntax makes it better to read for me:

SELECT   O.id, count(*) AS `times`
FROM     orders O
JOIN     order_items OI
ON       OI.order_id = O.id
GROUP BY O.id
HAVING   `times` > 1

For larger queries you may want to go for something like this (exaggerated, in my opinion, for such a small query):

SELECT
    O.id,
    count(*) as `times`
FROM
    orders O
JOIN
    order_items OI
ON
    OI.order_id = O.id
GROUP BY
    O.id
HAVING
    `times` > 1

The JOIN syntax is preferred. I also, personally, prefer uppercase for aliases, either with or without a _ sign. The alias of the column is because count(*) as an identifier is ugly (in a practical way, as it has non-ascii characters).

The important part is sticking to a style guide or convention, that could be different for each project you are in.


SQL Server not giving back memory - staying at Max Server Memory setting I'm the only one working on this server and followed documentation about optimizing SQL memory usage when I saw that SQL was using all of the memory available of the server.

One of the steps I did is to fix the max memory size to 20 Gb (server for test purposes with 32 Gb memory).

After tuning, yesterday, SQL Server was only using 3-4 Gb but today, it has again reached the max memory usage I allocated.

Why is SQL Server keeping all 20 Gb? What will happen when users will begin to use the server if the max memory size is already used?

It is holding that memory because you used it once so, obviously, you will probably use it again. This is the way SQL Server works: it will take the memory it needs (up to the max you've allowed), and will only give it back to the OS if the OS demands it. If you're not demanding memory back from SQL Server, why do you expect it to give it up? Allocating and deallocating memory is inefficient, and SQL Server isn't designed to give you memory back so you can let it sit idle. For similar reasons, it doesn't automatically shrink files for you after you delete or reorganize data: grow/shrink operations are expensive, and if the file is just going to grow again tomorrow...

If you are running SQL Server on your laptop where you need the memory for other tasks, then you should consider setting a lower max server memory setting. This way SQL Server will never encroach on the memory you think you need.

If you are running SQL Server on a server, then it likes to be the only thing on the box. If the server is hosting other applications etc. that need memory, then you can either reduce max server memory further, or move those applications to another server. If there are no other applications that need the memory, then why do you care whether it's using 20 GB, or 16 GB, or 12 GB, or 100 MB? Are you going to lease out the remaining memory temporarily to other servers until SQL Server needs it again? Didn't you give it 20 GB of memory so it will use 20 GB of memory?

There is absolutely no reason you should be worried that SQL Server is using the amount of memory that you've told it is ok to use. This is exactly how it's supposed to work. When users run queries against the server, presumably, they will likely be accessing data that's already in memory (a large portion of SQL Server's memory allocation is buffer pool). If your database is a lot bigger than 20 GB, then you may consider actually raising the max server memory setting to get the best use out of the memory and how much I/O comes from there rather than disk (which is a lot slower). Artificially lowering the max server memory because you think Windows needs 12 GB of memory to operate is like paging to disk - you're forcing more I/O through slow disk subsystems that could be performed in much faster memory.

Again, if nothing else is running on the server, there's no reason you need more than 3 or 4 GB for the OS, leaving everything else to SQL Server. And again, you shouldn't be worried if you have set SQL Server's max memory to 28 GB, and it keeps "using" 28 GB even after all the users have gone home. This just means that in the morning it won't take any work to get back up to 28 GB.


Is there a setting to suppress C-Style comments appearing as DOC in Oracle Output? Just again looking at some log from an oracle script, where the developer intended to comment out some part not to add documentation.

I don't want that a script containing code like this

/*
select getWochentag(SYSDATE) from DUAL;
select
    getWochentag (SYSDATE    ) || isWeekend (SYSDATE    ),
    getWochentag (SYSDATE + 1) || isWeekend (SYSDATE + 1),
    getWochentag (SYSDATE + 2) || isWeekend (SYSDATE + 2),
    getWochentag (SYSDATE + 3) || isWeekend (SYSDATE + 3),
    getWochentag (SYSDATE + 4) || isWeekend (SYSDATE + 4),
    getWochentag (SYSDATE + 5) || isWeekend (SYSDATE + 5),
    getWochentag (SYSDATE + 6) || isWeekend (SYSDATE + 6)
FROM DUAL;

*/

produces output like

DOC>select getWochentag(SYSDATE) from DUAL;
DOC>select
DOC>        getWochentag (SYSDATE    ) || isWeekend (SYSDATE    ),
DOC>        getWochentag (SYSDATE + 1) || isWeekend (SYSDATE + 1),
DOC>        getWochentag (SYSDATE + 2) || isWeekend (SYSDATE + 2),
DOC>        getWochentag (SYSDATE + 3) || isWeekend (SYSDATE + 3),
DOC>        getWochentag (SYSDATE + 4) || isWeekend (SYSDATE + 4),
DOC>        getWochentag (SYSDATE + 5) || isWeekend (SYSDATE + 5),
DOC>        getWochentag (SYSDATE + 6) || isWeekend (SYSDATE + 6)
DOC>FROM DUAL;
DOC>
DOC>*/

I just want to see nothing in the log. (Only empty logs are good logs)

SET DOC[UMENT] OFF

At least in 9i. I can't make these comments display at all in 10g, so I think they've made this option even more obsolete - it still controls explicit DOC blocks, but apparently not comment blocks...


Problem creating database using default mdf and ldf path I'm trying to create a database using the default paths on several SQL Servers at once.
To achieve this I'm trying to use a script that should create the database on each server, however I've run into some difficulties trying to use a variable for the file paths.

Declare @logpath nvarchar(256),
        @datapath nvarchar(256),
        @filenameLog nvarchar(256),
        @filenamedata nvarchar(256);

SET @logpath = (select
                   LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
                from sys.master_files
                where name = 'modeldev')

SET @datapath = (select
                    LEFT(physical_name, LEN(physical_name)  - CHARINDEX('\', REVERSE(physical_name)) + 1)
                 from sys.master_files
                 where name = 'modellog')

set @filenamelog = @logpath + 'test.ldf'
set @filenamedata = @datapath + 'test.mdf'

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY
  ( NAME = test, FILENAME = @filenamedata , SIZE = 51200KB , FILEGROWTH = 10240KB )
 LOG ON
  ( NAME = test_log, FILENAME = @filenamelog , SIZE = 5120KB , FILEGROWTH = 5120KB )
GO

This gives:

  Msg 102, Level 15, State 1, Line 14
  Incorrect syntax near '@filenamedata'.

What am I missing? I feel like it's something obvious staring right at me but I can't seem to find it.

My source for this script was an msdn article here!

Try using dynamic sql:

SET @createDatabase = 'CREATE DATABASE [test] ON PRIMARY CONTAINMENT = NONE
( NAME = N''test'', FILENAME = ''' + @filenamedata + ''', SIZE = 51200KB , FILEGROWTH = 10240KB
)
LOG ON
( NAME = N''test_log'', FILENAME = ''' + @filenamelog + ''' , SIZE = 5120KB , FILEGROWTH = 5120KB
)'

EXEC sp_executesql @createDatabase

Another member had a similar problem a while back.
https://stackoverflow.com/questions/5202257/create-a-database-with-dynamic-database-name-in-sql-server-2005


How to resynchronize replica in Postgresql I have a server with Centos6 and Postgresql 9.2, I have set up a replica in postgresql to another server of the same characteristics, today in the morning I have an error in the archiver process on the primary server, the replica does not configure it I, I really do not have a lot of knowledge about this topic, yesterday I had to delete some archived files in the primary for lack of space but later I checked the replica creating some tables and they replicated to the secondary, I thought it was all right ... I would like to know how to verify that the replica is working and if not how I can resynchronize the secondary. Thank you very much in advance

Since you deleted from pg_xlog, not from the archive, your first concern should be that you might lose the master, not just the replica.

Cross your fingers, and immediately perform a checkpoint on the master.

If you get into the situation again where the archive process can't keep up and is threatening to fill up all space on the pg_xlog partition, rather than manually deleting from pg_xlog you should instead change archive_command to 'true'.  (Or change it to copy to somewhere else which is faster than its current setting is, but still preserves the files.  Make sure to then manually copy those files to their final, slow, destination once the emergency is over.)

As for saving the replica, please answer my other two questions, preferably by editing the question to incorporate that information.


What precautions to take when moving a mirrored server I have a SQL Server 2008 R2 mirror setup running in high safety with automatic failover (synchronous, witness).  Over two different nights, the mirror and the primary will be shut down and re-racked.  (Mirror on the first night, Primary on the second night)

My original thought is that maybe I should remove the witness prior to shut down so failover would be avoided during/after this process unless I initiated it.  But, I’m wondering whether there are any other precautions I should take.  The server move should take ~1 hour which is the frequency of log backups as well, so I’m not worried about the transaction log filling on the primary during this time.

Is there any reason to switch from high safety to high performance mode during them move?  I’m a little bit unsure of how these modes might behave differently when the mirroring state is DISCONNECTED.

I would remove the witness from the session. The reason being that if the 1 online partner (principal) loses communication with the witness, the principal will take itself offline because it has no way to know for sure that the mirror isn't online.

There is no need to change the operating mode. When the principal becomes disconnected, it operates asynchronously. When the mirror reconnects, it will continue to operate asynchronously until they are synchronized, and then it will return automatically to operating synchronously.


Teradata to drive responsive web application Does anyone have experience using Teradata to drive a typical, responsive client-side web application? What is your evaluation? Specifically, how well does it perform in single-read and single-write?

Single-read: The total time-to-first-byte between a request being sent and a response being received to read a single document or row that contains meaningful information for a webapp. For example, consider a table called profiles with 100,000 records in which various attributes are stored on each row. How much time does it take to get a single profile up to a client-side webapp by querying on a primary key? How much time does it take to do the same operation, querying on a non-indexed field?

Single-write: How much time does it take to insert a new row or update an existing row. Assume that all indexes that are needed to perform the single-read quickly need to be written during a single-write operation.

If you have built a client-side web application with a Teradata backend, what sort of challenges did you face? I have seen Teradata as primarily an analytical and data warehousing tool rather than an operational database, but I am being asked to investigate Teradata as the backend for an operational web application.

I would like to find out if anyone has tried this, and whether it could keep up with more commonly used databases for webapps. I'm not looking for opinions, but for honest evaluations of how the technology works in this unusual use-case by people who have tested it, and for people's experiences using Teradata to drive a responsive webapp.

I understand there are many variables here, but I'm looking for real-world examples of Teradata being used as a backend for a client-side webapp, and how it performs in practice. Thus I'm more interested in TTFB than I am in DBS time, though a thorough breakdown of how long each piece of a transaction takes would be appreciated.

My previous question, Teradata REST API performance metrics, was specifically about Teradata REST performance. However, I've realized that almost no one uses Teradata's REST capabilities in this community (questions have gone unanswered for years).

My particular use case

Insac requested that I share some details of my specific use-case to help focus answers. With the caveat that I'm looking for an answer about how Teradata performs in general for webapps, here is what my app will need to do:

Client-side webapp with RESTful interface
Estimated ~5,000 users
Would be driven by ~25 Terdata views and a handful of materialized tables
Teradata instance would be shared with a data warehouse
Some views would perform joins across several underlying views
The underlying tables for these views are structured for analytics
Almost all queries are read-only operations; certainly all intensive queries.
A typical page refresh would send off ~100 queries to Teradata after receiving a response from a few initial queries to dynamically generate SQL on the client side.
At a minimum, any user action would send off about 20 queries.
The web application needs to be responsive and fluid. Responses in less than 2 seconds are a requirement, with sub-second response-times greatly preferred.

A very high-level overview of the purpose is accessing data from a social-network-like timeline feed history for a particular profile. The initial queries would return a chronologically sorted list of events that match basic filters such as category and date. This result set then would drive dynamically generated SQL queries for the first results, returning the content of each event on the feed.

I am evaluating Teradata for this application, but would like to know in general if Teradata is suited to client-side webapps so that I can support comparative analysis of database technologies for future projects as well.

Edit: The scope of my project is shifting to include more web-2.0 functionality, meaning the application needs to write to several views, and writes will be a common part of webapp use. With the read-only constraint removed, what would a webapp on Teradata look like?

as I said in my answer to this question, Teradata wouldn't be my first choice for a web backend:
it really shines on moving/elaborating large quantities of data, but the typical workload of an application (frequent small queries) is not the best scenario for it.

Obviously there are other voices (see here) but there is quite an agreement about some critical points:

  Teradata locking behavior is the primary challenge for an OLTP style
  application.

and:

  use as much cache as you can: the Teradata connection is a precious resource
  you'd better not use for information that doesn't need to be realtime

It is clearly not impossible to implement such a solution on Teradata, but it requires your application to adapt fully to Teradata peculiarities (e.g. you could have to create a non-trivial application data model, to strive for single-AMP queries)

An example of the challenges you're going to face:

in an application I had to update a flag field on a set of rows in a table, chosen with a condition that was not the Teradata "primary index". However, there was an index on the columns used in the condition.

Usually, I would have used a simple "update": however, in Teradata, this would have created a "table-level lock".
At the end it turned out it was "better" to select all the rows in the application and do all the updates (in batch) one by one by primary index, to use the "hash-level" locks.

=========================================================

Edited: after the additional info from OP

First the good news:

Mostly read-only
since it is a mostly "read only" application, you can setup an efficient caching system. You could also "pre-cache" some common content so that the hit of first querying it won't be apparent to the user.

Why that's important in this case:
 connections in Teradata can't be created over a specific number for appliance. That's a pool wìthat will be shared between users in the application, the ETL and the normal OLAP users.
 In addition, queries that are not single-AMP won't have a very good latency, and they'll be more subject to the instan load of the system

Fine-grained queries

It might be a good thing if in this way you're able to do mostly single-AMP queries.

Why that's important in this case:
single-AMP queries (the ones querying/insert/deleting using the primary key as criteria) have lower lock impact and can be managed without paying the cost of coordination between the AMPs. On the other side, having such a "chatty" protocol wil occupy more the connections.

Materialized tables

It's good that you are already thinking about materializing some data. You might even find out that it will be useful have the same data materialized in two or more tables with different primary indexes, to satisfy your user interface (e.g. if you have different selection criteria on the same entity). However this will have a cost in terms of ETL time and disk space.

Now, the bad news:

More than 1000 users
As the application is not "completely" read-only, this number of users will probably create access conflict in the cases when they have to write.

Why that's a problem:
Forget multi-versioning: here readers block writers and viceversa. Your select might stop the update of another user from completing. If you are not able to use primary index for every update you do, you'll be locking the full table, and even if you use primary index, you are not going to have a row-level lock (you have an "hash-level".. normally it works ok, but it can have some issues with batch inserts). There's a workaround this conflict betwen readers and writers (LOCK ROW FOR ACCESS) but it opens the door to dirty reads, and you should carefully think if you can accept this in your application

Web application and OLAP on the same machine

Independently from the underlying technology, it's always a challenge to accomodate these two different workload on the same platform.

Why that's a problem:
Teradata has some workload management rules that can help in this field, however they need a careful tuning from DBAs and, however, extraordinary load (e.g. end of month batches) will likely have an impact on the user experience of the application

Hourly ETL

The hourly ETL will be a stress point for the application, since you'll be loading the same tables that you are querying. Since you cannot afford to lock the readers, you'll probably resort to "dirty reads", but you'll have to find some policy to manage the fact that the application could have temporarily inconsistent data.

Why that's a problem:
Already said: table locks, dirty reads and the presence of two different workloads

Number of queries

I know I said that "finer grained queries" can be a good thing, but here we're talking of 100 queries per user for a page, and a minimum of 20 queries per user action. Now, if you execute them serially, you're increasing latency (and you're looking for sub-second response time); but if you parallelize them, you're occupying the equivalent of the maximum sessions of a Parsing Engine just for an user (I'm not considering caching here)

Disclaimer: I've been working last two years in more traditional Teradata projects,  but when OLTP and raw latency counted I don't remember figures to go a lot less than the 150ms you mention. Maybe 130ms. But that was  after optimizations and configuring the system to accommodate our workload, and just on the simpler queries/macros.

But, then, if you already have an instance, and you want to verify Teradata performance on this scenario,  I'd suggest you to ask Teradata Sales support for a "Proof of Concept" on your  environment. I think they'll gladly accept (the more load you have,  the more likely you'll need additional nodes).

The reasons I wanted to know so many things about your application is that,  at certain conditions (mostly read data,  few active users,  clearly defined batch and online windows), it is easy to create applications on Teradata (as on every other system).  When some of these constraints are lifted,  the lock architecture and the necessity to target single-AMP queries,  makes it really difficult (not impossible, but really difficult) to achieve satisfying performances.


gin_trgm_ops index creation fails I'm trying to create a trigram index on a text filed containing names. I've added the pg_trgm extenion to the schema. Running \dx shows it is enabled:

List of installed extensions
   Name    | Version |   Schema   |                            Description
-----------+---------+------------+-------------------------------------------------------------------
 btree_gin | 1.3     | pg_catalog | support for indexing common datatypes in GIN
 dblink    | 1.2     | wos_core   | connect to other PostgreSQL databases from within a database
 pg_trgm   | 1.4     | wos_core   | text similarity measurement and index searching based on trigrams
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

However when I run the following:

CREATE INDEX authors_full_name_idx ON wos_core.interface_table USING GIN (authors_full_name gin_trgm_ops);

I get the following:

ERROR:  operator class "gin_trgm_ops" does not exist for access method "gin"

Can anyone tell me what I'm doing wrong?

                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

The schema wos_core is probably not in your search_path.

In that case, you need to prefix the operator with the schema name:

CREATE INDEX authors_full_name_idx
       ON wos_core.interface_table USING GIN (authors_full_name wos_core.gin_trgm_ops);

Alternatively you can change the search path of your user, to include wos_core:

alter user your_username set search_path = 'public,wos_core,...';


What's Scalar Operator in the plan? I have a doubt. What means this Scalar Operator in the print below?

I've been investigating our database cause we have severals RESOURCE_SEMAPHORE_QUERY_COMPILE . I've seeing a lot of queries using this in the joins which costs more. Is it normal or it's not a coincidence?

Thanks,

Armando

When you're looking to track down the root cause of RESOURCE_SEMAPHORE waits, the plan operators you want to pay attention to are most commonly

Sorts
Hash Joins
Hash Match Aggregate

That's because it's a memory wait. Specifically, queries asking for memory, and SQL Server not having it available for them to start running.

If you're on a more recent version of SQL Server (2012 SP3, 2014 SP2, 2016+), you can use a free tool like sp_BlitzCache (full disclosure, I'm a contributor) and run it to sort your plan cache by memory grant.

EXEC sp_BlitzCache @SortOrder = 'memory grant'

But fair warning, if you're hitting a bunch of that wait, there's a good chance your plan cache is wiped out from the memory pressure.


MYSQL - Using simple inventory number convention as Primary Key I have an inventory with three numbered groups like so:

Group 1: 0-999
Group 2: e0-e999
Group 3: v0-v999

Can I simply make a one-column table to store this information and make that column the primary index (needed?), or should I make it two columns.

example:

code (primary)
000
016
760
433
e221
e011
v999
v003

vs.

(primary)
 code_id    code
    1        000
    2        016
    3        760
    4        433
    5        e221
    6        e011
    7        v999
    8        v003

You can use the one-column approach for simplicity.  However, what happens when a code is changed?  Will you have to update all related tables to have the new code?

Using an integer ID is the best choice for a primary key because it's small and efficient (strings have to be reduced/hashed to an integer before they are used as an index).  Also, updating a code will happen in only one place and be reflected in all related tables automatically.

So I would recommend your second option for the sake of speed and to avoid a cascading update.


How to view data in pgAdmin III I can't believe they make it this hard.  I am at a loss about how to view the data in my database.

Is there an easy way to see what data is in my tables with pgAdmin III?  Alternatively, is there a program that I could use that does not suck?

Or, to retrieve all rows, just press the blue data grid.

The tooltip "View data in the selected object" is almost making it too easy.
Try the button with the funnel next to it to retrieve only a selection (for big tables).


Cancelling the rebuilding of a partition with compression Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already).

What is the impact of cancelling the operation and letting it roll back?

Trying to determine if we should power through and let it finish or kill it and let it roll back.  However if rolling back a 6.5 hr operation will take just as long, then we may want to just let it finish.

We tested it in QA, which has slower disks, it completed in 4 hours, so we are not entirely sure why this is taking so long.

Things to know:

Table size is 271 GB with approx. 30 million rows
At the 3 hr mark, the drive got full, we expanded it about 40 min later
Prevailing wait type is IO_Completion
sp_whoisactive shows CPU and physical_reads increasing, while reads/writes have not moved for quite some time
This is on SQL Server 2017 Standard Edition, Windows Server 2016

Here is our statement:

ALTER INDEX [PK_t1] ON [dbo].[t1] REBUILD PARTITION = ALL
WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF
    , MAXDOP = 3, DATA_COMPRESSION = PAGE );

Rollback could be 2x, 4x, etc., since rollback is single-threaded even for operations that were parallel.

My advice would be to let it finish. What's your alternative? Not do it ever? If you can't rely on the estimates your QA system gave, I don't know how you could ever have the confidence to start over and expect a different result.


Why is my database 12 times bigger than inserted data? I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done.

The point is: from 50 mb plain data (~700,000 lines), this results in 600 mb db size. This is a factor 12! I am sure I am doing something wrong here. Could you help me to reduze the size of my db? Source of database size is the web postgres admin interface.

Here's the insert:

CREATE TYPE CUSTOMER_TYPE AS ENUM
('enum1', 'enum2', 'enum3', '...', 'enum15');       ## max lenght of enum names ~15

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
   ATTRIBUTE_TWO   TEXT UNIQUE,                     ## max 51 char String
   ATTRIBUTE_THREE TEXT UNIQUE,                     ## max 52 char String
   ATTRIBUTE_FOUR  TEXT UNIQUE,                     ## max 64 char String
   ATTRIBUTE_FIFE  TEXT UNIQUE,                     ## 1-80 char String
   CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

I don't really need that enum since I can insert it without, too. Does a enum have an effect on the database size?

Is there a way to reduce the size? Is it possible to reach factor x4 (instead of x12)? If not, I could delete some of the columns, if necessary.

Maybe there are other Postgres data types for character data?

After feedback here, my updated table looks now like this:

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,       ## max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                     ## max 35 char String
   ATTRIBUTE_TWO   TEXT,                            ## max 51 char String
   ATTRIBUTE_THREE TEXT,                            ## max 52 char String
   ATTRIBUTE_FOUR  TEXT,                            ## max 64 char String
   ATTRIBUTE_FIFE  TEXT,                            ## 1-80 char String
   CUSTOMER_TYPE   PRIVATEKEYTYPE                   ## see enum
);

Before: 12x

Now: 7x :)

Are there any more possible optimizations? (Except deleting columns?) Maybe other data types using less space?

text is the best type for plain text. Text values alone occupy about the same internally (depends on encoding details, too of course: UTF-8?). A small overhead of 1 byte per field in your case. See:

What is the overhead for varchar(n)?

The enum column contributes very little to the total size. 4 bytes per row (a real quantity internally). The length of enum names is practically irrelevant, stored onceas data type name, so with a maximum of NAMEDATALEN (normally 63 characters). Minimal overhead for the type declaration. So the enum column actually occupies less space inside the DB than in your text file.
There are various types of overhead in Postgres storage, it has to be like that. Some of them one-time costs, some other scale with the number of rows or relations etc. There are catalog tables (system data), indexes, relationships, table statistics, bookkeeping data etc.

12x the size seems more than average for 50 MB of raw text data. But that really depends on details. Many small rows have a lot of overhead, few large rows not so much, can even be compressed and smaller. a_horse already pointed to a major reason: 6 indexes - some of which obviously unnecessary, too.

Each row in a table has a tuple header occupying 23 bytes (plus alignment padding, typically 24 bytes), and possibly alignment padding between columns and tuples, 4 bytes for the item identifier, some overhead per data page (typically 8kb page size). Roughly ~ 35 bytes per row in your case.

About as much for every index tuple (header has only 8 bytes, but indexes bloat more). 6 indexes, ~ 150 bytes per table row minimum. Plus every text column is saved twice, 1x table, 1x index. And btree indexes start with a FILLFACTOR of 90, so ~ + 10 % on everything.

Your average row size is ~ 75 bytes (50MB / 700000 - assuming "lines" correspond to rows). Your table and indexes occupy about 6 times the size of the raw text. The rest is other overhead mentioned above.

Measure the size of a PostgreSQL table row

That's all assuming no table bloat (yet). Once you work with the DB (update, delete, transactions rolled back etc.) there will be dead rows and such, possibly multiplying total space. That very much depends on write patterns.

You can save a lot by removing unnecessary indexes.

And a couple of bytes per row if you move the enum column to the top of the table: less alignment padding, should average at 1.5 bytes per row, roughly 1 MB total, hardly worth it. See:

Calculating and saving space in PostgreSQL


Maintenance plan to delete backup and restore history not working I have a maintenance plan set up on our SQL Server 2012 that contains only a "History Cleanup Task", which is configured to delete the following data if older than 4 weeks:

Backup and restore history
SQL Server Agent job history
Maintenance plan history

It runs every day at 3:00 a.m. without error, however I've only just noticed that it doesn't appear to be deleting records from the backupset table and the job completes in 0 seconds.

How can I fix it, or get more info about what's going wrong?

UPDATE

Thanks for the response but from some of the comments it appears people think I am having trouble deleting old backup files. I am actually trying to clean up the history data of old backup job execution, which I understand lives in the msdb database.

The reason I want to is that we've encountered long delays and ballooning server resource usage just when trying to drop a database, as a consequence of many years of backup history sitting in msdb.

Here is a good analysis of the problem:
https://sqlserver-help.com/2014/07/10/troubleshooting-slow-delete-database-from-management-studio/

The last line of the blog post is:

  If you clean msdb backup history regularly, you might not face the
  issue though. There is maintenance plan to do that. Try it out!

And this is what I'm attempting to do now!

In the Maintenance plan job, create an output file to see what exactly is going on during the job run. Mostly there is something missing.
Drop and recreate the plan and test.


AlwaysOn availability group and Replication - expected behaviour I'm currently doing a bit of testing with alwayson and replication. I currently have the below:

Fully working cluster and Always on environment; DCTEST1, DCTEST2 and the listener name DCTEST_L. The cluster is healthy.

Fully working transactional replication to another server - DCREPL
All is SQL2017 Enterprise

Replication to DCREPL works regardless of which node is the primary. I make data changes on DCTEST_L and they are successfully passed to the subscriber.

The issue I'm having is when I shut down the primary (DCTEST1). The availability group fails over successfully. However, when I make data changes on the listener - DCTEST_L; they are not replicated to DCREPL.
When DCTEST1 is powered back on, then the changes are successfully replicated.
Is this expected behaviour? I would like the replication to continue should the primary ever go down, and not have to stall until the node is powered back on.

Is there a step I have missed? I have followed the microsoft documentation:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-2017

Actually according to Microsoft this is expected behaviour:

When the publisher has only two availability replicas (one primary and one secondary) and a failover happens, the original primary replica remains down because the logreader does not move forward until all secondary databases are brought back online or until the failing secondary replicas are removed from the availability group. The logreader, now running against the secondary database, will not proceed forward since Always On cannot harden any changes to any secondary database. To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP  REMOVE REPLICA. Then add a new secondary replica to the availability group.

Quoted from:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-2017


Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008 I have two SQL Server Instances on the same machine, one 2008 and one 2016. Both instances have a linked server connection to the same Oracle 12 Server.

If I run the following query 2008 returns 1.23 as a VARCHAR but 2016 returns it as a float

SELECT * INTO #tst FROM OPENQUERY(JOHPROD,'SELECT 1.23 FROM dual')
SELECT t.Name,* FROM tempdb.sys.columns c
    INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE [object_id] = OBJECT_ID(N'tempdb..#tst');

Both instances are pointing to the same Oracle server and both using the same Oracle ODBC driver.

Both linked servers have this same definition...

EXEC master.dbo.sp_addlinkedserver @server = N'ORAPROD', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORAPROD'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAPROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'ORAUSER',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Any idea why these would return different types?

This behaviour has changed in recent updates. See
FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider

  After you apply this fix, NUMBER values with unknown precision/scale
  are treated as double values with OLE DB provider. If the precision is
  important and the range of the values is not large enough, you can
  enable the new Trace Flag 7314 that would start treating such values
  as numeric (38, 10).

However I'm a bit surprised you are getting a float instead of a double \ numeric since that seems to contradict the article somewhat, so I'm not sure it explains your exact situation.


Dynamically changing an order number to end in _ERR I'm trying to update multiple orders at once instead of doing them one at a time. Is there a way to dynamically update them all at once?

For example: I have 5 orders ABC, BCD, CDE, DEF, EFG.

I need to update them to look like this ABC_ERR, BCD_ERR, CDE_ERR, etc.

It's going to be a different concatenation operator depending on the RDMS you're using. For instance:

Oracle uses ||
SQL Server uses +
MySQL uses concat

...but everything else looks like:

UPDATE table1 SET order = order ||'_ERR' WHERE [your condition]

Since the question is tagged for SQL Server, the applicable syntax is:

UPDATE table1 SET order = order +'_ERR' WHERE [your condition]


Oracle SQL: Why are column headers missing? I want to understand why my columns are missing their headers.

Consider this table:

SQL> DESC customer;

Name         Null?    Type
------------ -------- ------------
CUST_ID      NOT NULL NUMBER(5)
CUST_NAME             VARCHAR2(15)
ACCOUNT_ID            VARCHAR2(10)
ACCOUNT_TYPE          VARCHAR2(2)
STATE                 VARCHAR2(2)

When I select * from the table, the result is missing the column headers.

SQL> SELECT * FROM customer;

     90001 B and B         A-11101    PR AK
     90002 XYZ             A-11102    CM NJ
     90003 JJ Sons         A-11103    CM NJ
     90004 Exxon           A-11104    PR NY
     90005 ABC             A-11105    CM NY
     90006 Smith Co.       A-11106    CM MD
     90007 Brown Co.       A-11107    CM MD
     90008 Cooper Inc.     A-11108    PR MD

8 rows selected.

Why?

My goal is to display the tables with the headers. If there are other details I need to add, let me know.

Heading may be disabled in your environment. Enable it (it is enabled by default).

SQL> select banner from v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> show heading
heading OFF
SQL> set heading on
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Or pagesize is set 0:

SQL> show heading pagesize
heading ON
pagesize 14
SQL> set pagesize 0
SQL> select banner from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> set pagesize 14
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production


Displaying query plans in Server Management Studio Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset:

select top 10
    sum(qs.total_worker_time) as total_cpu_time,
    sum(qs.execution_count) as total_execution_count,

    qs.plan_handle, st.text
from
    sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as st
group by qs.plan_handle, st.text
order by sum(qs.total_worker_time) desc

Question 1: What exactly is the plan_handle? It doesn't appear to be a hash of the plan, like it is in Oracle. I ask because I want to be able to detect the situation in which the plan of a statement has changes.

Question 2: Once I have a plan_handle, I am interested in the actual plan. So I do, for example:

select * from sys.dm_exec_query_plan (0x060006001F176406B8413043000000000000000000000000)

In the query_plan column I get a link that when I click displays an XML document. If I save it on disk as whatever.sqlplan, I can double-click it in Windows and it displays correctly in Management Studio. Surely there must be a way to avoid this step?!

Question 3: Is there a way to convert the XML back into a textual format, like in the old days of SET SHOWPLAN_TEXT? I want to be able to view them graphically, but also automate diffing them in some meaningful way.

Thanks!

A1: The plan_handle is a hash for a group of statements, or batch.

A2: No, the dm_exec_query_plan returns the query plan in XML format, so you need to click on that in order to see the graph.

A3. Try this:

SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);


How do I access a user's application username when that application connects to the DB? I'd like to audit changes to a table in SQLServer using a trigger and an audit table to do so. End-users connect to the database through an application. If I use SYSTEM_USER to obtain the end user's username, then it returns the name of the application that is connecting to the database, and not the user's unique login name for that application.

What would be a standard approach to capturing the user's unique application login name in this scenario?

Although I noted a potential duplicate question in my comments above, Microsoft has introduced new features to make the passing of (essentially "session") information from a client application to the database easier, with the introduction of new features in SQL Server 2016.

As noted before, CONTEXT_INFO was the solution prior to 2016 - but it is fraught with difficulties.

From 2016 onward, SESSION_CONTEXT has been introduced.

Rather than try and summarise this myself, there is an excellent article describing both methods, their shortcomings and providing SQL examples on their usage. I'm sure there's ample information there to work through a solution to this problem.


What's the equivalent of --safe-updates of mysql in MS SQL Server? In MySQL, you can use the feature called --safe-updates (--i-am-a-dummy) to limit the number of rows updated per query.

http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html#safe-updates

Is there such a thing in MS SQL Server?

There may or may not be such a thing in MS SQL, but why would you want this?

There is already a way to limit what UPDATE affects (the WHERE clause).
There is already a mechanism to safeguard your data when making changes (Transactions).

Combining the two, we get the general-case solution: "Do your updates in a transaction, with appropriate WHERE clauses to limit what they touch, and make sure the results look right before you commit":

> BEGIN;
> UPDATE mytable SET foostring='NewValue' WHERE id > 16 AND id < 32;
15 Rows Updated

> SELECT * FROM mytable;

  [Omitted -- Make sure it looks right]

> COMMIT;  --- Or ROLLBACK if the SELECT doesn't look right