Is there a way to throttle index creation? When creating an index concurrently, I'm reaching IO limitations and it's impacting the production system. Is there a way to throttle the index creation?

I'm on 9.6, will hopefully be on 11 or 12 in the near future.

Previous question: Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database?

ionice -p PID ... on Linux can in theory change the I/O scheduling class and priority of a running process. In that case you can start the index build, find it's PID and "ionice it". Have the commands to find the PID and ionice ready to run in separate windows and the impact should be very short lived.


How can I find out which tables use reference partitioning from the Oracle data dictionary? I'm writing a generic drop-all-objects script for our Oracle databases.  It generates drop table TABLE_NAME cascade constraints purge lines for all tables, amongst other object types, by looping through user_objects.

The problem is that tables which are have reference-partitioned tables dependent on them can't be dropped in this way: ORA-14656: cannot drop the parent of a reference-partitioned table.

How can I detect which tables are the parents of reference partitioned tables from the data dictionary, so I can skip them in the first loop, and drop them in a second loop?

To get a list of partitioned tables that have at least one referencing partitioned child table the [dba][all][user]_part_tables and [dba][all][user]_constrains data dictionary views, depending on the privileges granted, can be queried:

create table tb_part_parent(
  col  number primary key,
  col2 number
)
partition by range (col2) (
  partition part_1 values less than (100),
  partition part_2 values less than (300),
  partition part_3 values less than (500)
)

create table tb_part_child(
  col  number not null,
  col2 number,
  constraint fk_parent_1 foreign key(col) references tb_part_parent(col)
)partition by reference (fk_parent_1)

The query:

select t.table_name
  from user_constraints t
 where t.constraint_name in ( select w.r_constraint_name
                                from user_constraints w
                                join user_part_tables q
                                  on (q.table_name = w.table_name and
                                      q.ref_ptn_constraint_name = w.constraint_name)
                              )

Result:

TABLE_NAME
-----------------
TB_PART_PARENT


coping with long natural string key I have inherited a (quite) big database (for my standards at least, since by no means I call myself a DBA or DB dev).
So, on this DB they used a unique natural string as the primary key on EVERY table. In my novice experience it seems that is slowing joins down.
Just to get some more info on this, our main table is having roughly 1.5 million rows, the key is a taxing office id number, which might have some leading 0s, so they went and declared this as a varchar(12), in order not to loose the leading 0.
One of our secondary table has more than 3 million rows, and an other one with roughly the same number of rows as the main table. Each of these tables (and many more of smaller size) are using the same varchar(12) as a foreign key to the main table mentioned before.

Questions

Would it make any difference using an int instead of this varchar as
key?
If 1=Y then : There are a few applications that are using this
database and they are all using this key for joining tables. Is
there a way to keep the old columns for joining the tables, but get
some advantage using some int column for the key? i.e. can i change the key, but keep the same old queries?

Edit:
One major limitation on changing the queries is that most are not stored procedures. They are hard coded inside the actual applications

INT is 4 bytes.  VARCHAR(12) is a little longer.  I would gladly use either for a PRIMARY KEY without worrying about performance.

A BTree is efficiently structured such that a longer key does not impact performance much.

If you switch that value to INT, perhaps with ZEROFILL, the speed up will be small.

If you add an INT AUTO_INCREMENT, then you are likely to slow down some uses of the table.  For more details, let's see some queries, including inserts and updates.


Maintainability of schema changes during runtime of an app We have an application which accepts JSON objects from X number of third party services and unfortunately we're locked into using SQL to store it. The objects must be flat (no nesting), but each third party can have a different set of properties.
I feel like it would be a better approach to have a static schema - for example, there would be a "Definitions" table that contained an entry for each unique property, then for each object there would be a row for each property that references the definition, for example - given:
{
  "prop_name": "John Doe",
  "prop_tel": "+15555555555"
}

There would be the following in SQL:
Definitions table -
  | Id | Ref       | ...
  | 2  | prop_name |
  | 3  | prop_tel  |

Entities table -
  | Id | ...
  | 6  |

Values table -
  | Id | FK_DEF | FK_ENTITY | Value        | ...
  | 9  | 2      | 6         | John Doe     |
  | 10 | 3      | 6         | +15555555555 |

This is slightly simplified from what might be a final design, but in practice this would let you store any format of data in SQL. The expense here is that lookups on this data is hard, you need to use a pivot or nested selects. (and we do need to do lookups on this data, sorting the entities by the values table, etc)
My colleague is convinced that this is ridiculous and we should just be dynamically modifying the schema as we go - either:

Adding a new table for each unique json object we need to store
Adding a new column to a single table for each unique property. Note that properties named the same coming from different third parties or data sources still need to be treated as separate columns.

We can never delete data and hope for this to be a very scalable application - containing hundreds of thousands or potentially millions of records.

Keeping in mind we know both of these options are crap but don't have a choice: Can anyone give me an idea of what the best practices are, and pros/cons of each approach? None of us have had to do this before and we are worried we will get locked into a path and hit road blocks later when trying to scale this. Off the top of my head having absolutely no experience, here's the list I'd make:
Static Schema
Pros:

Predictable
Maintainable
Not difficult to add new data format or perform inserts

Cons:

Slower / complicated lookups (pivot or nested select)
More complicated inserts / updates (for entities that exist)

Mutating Schema
Pros:

Fast lookups
Easy updates (for entities that exist)

Cons:

Schema out of our control, can get massive and hard to maintain over time
Database migrations to new schema gets harder and harder as time passes
Its not recommended for production app to have access to change schema

What am I missing and what would be the best path to take considering long-term scalability and maintenance?

If the data is going to have many different layouts, you might be overthinking it.

Many DBMSs have great support for JSON now. You can store it natively as JSON while defining a view on top of JSON data that exposes it to other users as a regular view with columns.

https://www.postgresql.org/docs/current/static/datatype-json.html

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server


Free Drive Space and SQL Server Agent Alerts I need to receive an email when free drive space on E: or H: becomes less than 20%
I know that can be achieved by setting up a job that executes PowerShell or T-SQL scripts on a schedule, that checks for free drive space and sends an alert email when drive space is low
But the question is - can same task be achieved using just an Alert ?
There is alert type SQL Server performance condition alert
Is there an Object / Counter that can help to check for drive space and fire this alert if its value "Falls Below" specified threshold ?

This is an OS information. I don't think there will be an Alert in SQL that will track the remaining space on the drives.
Pretty much all of the monitoring products monitor this and can send you alerts when you reach a specific threashold for disk space.
If you do not have any monitoring tool, then I think that a SQL job that check the free space and send an email seems like a good option.
Here's a script I use to see how much space available there is on the drive where I have a database file (it doesn't work for drive that doesn't host any database file)
SELECT Drive,PercentFree FROM (SELECT  VS.volume_mount_point AS Drive,
 CONVERT(DEC(4,2),CONVERT(DEC(18,0),VS.available_bytes)*100.00/VS.total_bytes) AS PercentFree
,ROW_NUMBER() OVER(PARTITION BY VS.volume_mount_point ORDER BY MF.database_id,MF.[file_id]) RN
FROM sys.master_files MF CROSS APPLY sys.dm_os_volume_stats(MF.database_id, MF.[file_id]) VS) as SP WHERE RN=1 ORDER BY SP.Drive


Reshape array in PostgreSQL I am using a PostgreSQL 10 server, in which I have some tables containing attributes of type double precision[] intended for storing 1D arrays of data, of length ~1000. My code interacting with the database contained a bug that inserted data as an array of length-1 arrays (see my psycopg bug report where I eventually worked out that this is what was happening), but this was permitted by the DBMS:

  The current implementation does not enforce the declared number of
  dimensions either. Arrays of a particular element type are all
  considered to be of the same type, regardless of size or number of
  dimensions. So, declaring the array size or number of dimensions in
  CREATE TABLE is simply documentation; it does not affect run-time
  behavior.

How can I fix these misshapen arrays within the DBMS (i.e., using SQL rather than updating from external code)? I essentially wish to just flatten each array into a 1D array as originally intended.

A snippet of the array data as returned by psql is:

{{2.20751909662576},{2.20679071024511},{2.20615506273571},{2.2055910715332},{2.
20507756148068},{2.20459435596551},{2.20412336646322},{2.20364958013081},{2.2031
618420891},{2.20265334228114},{2.20212174686242},{2.20156895755458},{2.201000532
12516},{2.20042484573123},{2.19985210697606},{2.19929335742568},{2.1987595765431
},{2.19826098783408},{2.19780662274576},{2.19740415504621},{2.19705997866427},{2
.19677947299938},{2.1965673849541},{2.19642825612809},{2.1963668334181},{2.19638 ...


MongoDB import bson files from a folder I am unable to import (.bson) file on my computer to MongoDB database .I have been trying below commands :

1)mongorestore -d demo -c dcoll C:\data\dump\twitter\tweets.bson

and

2)mongorestore -d demo -c dcoll /dump/twitter/tweets.bson

mongorestore -d demo -c dcoll C:\data\dump\twitter\tweets.bson

As i have gone through your MongoDB syntax query it seems to be correct.
As per your script code demo is database and dcoll is collection and you want to restore the (.bson) file of tweets.bson in your dcoll collection.
I would like to say that here before restore the (.bson) file in MongoDB. You have to make sure that your database name and collection name should be in MongoDB.
If you have don't have Collection in database then first create that Collection in database.
For example first connect with mongo shell and  show the database and collection . Which you are going to restore in MOngoDB, which is exist or not.
To show the database in MongoDB
For example in my environment i have checked and these databases are exist in MongoDB.
> show dbs
admin             0.000GB
blog              0.011GB
citibike          0.338GB
city              0.002GB
enron             0.213GB
local             0.000GB
ships             0.001GB
test              0.164GB
video             0.237GB
week6             0.006GB

Suppose that i want to restore (.bson) file in test database.
> use test
switched to db test
>

Then i shall create the collection data in test database.
> db.createCollection("data")
{ "ok" : 1 }   // ok : 1 means collection successfully created

Also confirm from the query , the data collection is in test database or not.
> show collections
airline
amwaj
cars
collection
data
movies
stuff
>

Here data collection is present then finally restore the (.bson) into the data collection.
Note: Make sure that you are restoring the (.bson) file from the mongo shell where mongorestore.exe is present. Basically this file is available in BIN folder of the MongoDB Server.   In my place it is C:\Program Files\MongoDB\Server\3.6\bin.
so, the finally mongorestore command will be like that
C:\Program Files\MongoDB\Server\3.6\bin>mongorestore -d test -c data C:\data\dump\100YWeatherSmall\data.bson
    2017-12-25T09:55:50.531+0300    checking for collection data in C:\data\dump\100YWeatherSmall\data.bson
    2017-12-25T09:55:50.561+0300    reading metadata for test.data from C:\data\dump\100YWeatherSmall\data.metadata.json
    2017-12-25T09:55:50.565+0300    restoring test.data from C:\data\dump\100YWeatherSmall\data.bson
    2017-12-25T09:55:52.662+0300    [##......................]  test.data  43.9MB/403MB  (10.9%)
    2017-12-25T09:55:55.565+0300    [#####...................]  test.data  87.6MB/403MB  (21.7%)
    2017-12-25T09:55:58.501+0300    [#########...............]  test.data  152MB/403MB  (37.6%)
    2017-12-25T09:56:01.501+0300    [#############...........]  test.data  230MB/403MB  (57.1%)
    2017-12-25T09:56:04.501+0300    [################........]  test.data  282MB/403MB  (70.0%)
    2017-12-25T09:56:07.501+0300    [######################..]  test.data  377MB/403MB  (93.6%)
    2017-12-25T09:56:09.349+0300    [########################]  test.data  403MB/403MB  (100.0%)
    2017-12-25T09:56:09.349+0300    no indexes to restore
    2017-12-25T09:56:09.350+0300    finished restoring test.data (250000 documents)
    2017-12-25T09:56:09.350+0300    done

Here in above code i have used
mongorestore command for restoring the (.bson) file.
-d  // for database
-c // for collection
C:\data\dump\100YWeatherSmall\data.bson  // location of the (.bson)
file.

I hope this will help out to you.
For further your ref mongorestore


Performance on same table vs 1:1 relationship in MySQL I have a two tables with 1:1 relationship, one table is called questions with 14 fields and the other is called links with 3 fields, the foreign key is in the question table. The tables are both MyISAM.

In the links table the fields are:
linkId, questionUrl and count

The reason for having the links table is that I can track clicks each time a user clicks on a link it increments the corresponding row.

Am I correct to leave this, or move the 3 fields from the links table to the questions table? My reasons for having a separate table is because the links table will be accessed and updated a lot more (each time any link is clicked).

Are there any performance issues or caveats for either?

1:1 is usually a 'wrong' design.

One exception is when some column (such as "Likes" or "click count") is very frequently incremented.  Putting that in a different table offloads the main table, and cuts back on the incrementation from interfering with other operations (and vice versa).

When does the recommendation kick in?  You would have to test it for yourself.

However, one thing can be said with a fair amount of certainty -- you really should switch from MyISAM to InnoDB.  UPDATE locks the entire table for MyISAM.  For Innodb it only locks the rows to be modified.  This can make a big difference when updating lots of different rows, or other high-volume activity.

Conversion tips .


MySQL change user login from one plugin type to another I'm in the process of testing MySQL 8.0.

I have set up a test database, and the first thing I want to do is import my user account from MySQL 5.7.

So to do this I run:

/*ON MySQL 5.7*/
SHOW CREATE USER `username`@`1.2.3.4`;

This gives me:

CREATE USER 'username'@'1.2.3.4' IDENTIFIED WITH 'mysql_native_password' AS '*1BDAB0F57D8247DF5EFE4927F144138B3B7F2C6B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

I run this on MySQL 8.0, and it creates a user. I check I can log in fine. However, the default in MySQL 8.0 is to use the caching_sha2_password plugin for authentication. So how can I upgrade my login to use the new plugin?

(n.b. this is my login, but at some point I will need to move all my colleagues logins, so assume I don't know what the pasword is).

I have looked all over the web, and found lots of docuemntation about the plugin, but none that say how to convert from one type to the other (apart from delete the user and start again, but that's no use if I don't know the password).

Without knowing the user passwords, you can:

Migrate the user accounts as you have described in your question (SHOW CREATE USER)
Set the passwords to 'expired'
ALTER USER user@host PASSWORD EXPIRE;
Ask the users to login and change the passwords. On an expired password, login is possible but the only action possible is to SET the password.


Stored procedure running for a long time We have a stored procedure which is running for ever, whereas when it is run as script it takes just couple of minutes to complete. When I just declare the params and assign values to them and run the script it's done in couple of mins. There is no application that is consuming the result set,it's a DW proc.

I tried couple of things:

I tried to flush the plan for that procedure  and run again but didn't run.
I tried to run the SP with recompile  but didn't run.

What can be the possible reasons that it runs as plain script and not as SP even after flushing the cache for that SP?

One possibility is that the stored procedure was created with settings for ANSI_NULLS and QUOTED_IDENTIFIER that differ from those set on your local connection.

These settings are stored with the procedure definition and override the session's current value. These two options can dramatically change an execution plan, and may prevent the use of indexes on computed columns and indexed views. Script the stored procedure from SSMS to check the stored settings values.

For a very comprehensive look at general issue see Slow in the Application, Fast in SSMS? by Erland Sommarskog.


How to alter database hour and date in Oracle? How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.

ALTER DATABASE

set_time_zone_clause::=

  set_time_zone_clause

  This clause has the same semantics in CREATE DATABASE and ALTER
  DATABASE statements. When used in with ALTER DATABASE, this clause
  resets the time zone of the database. To determine the time zone of
  the database, query the built-in function DBTIMEZONE. After setting or
  changing the time zone with this clause, you must restart the database
  for the new time zone to take effect.

  Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data
  to the time zone of the database when the data is stored on
  disk.Oracle Database does not automatically update existing data in
  the database to the new time zone. Therefore, you cannot reset the
  database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data
  in the database. You must first delete or export the TIMESTAMP WITH
  LOCAL TIME ZONE data and then reset the database time zone. For this
  reason, Oracle does not encourage you to change the time zone of a
  database that contains data.

  For a full description of this clause, refer to set_time_zone_clause
  in the documentation on CREATE DATABASE.

SQL> select dbtimezone from dual;

DBTIME
------
+02:00

SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';

Database altered.

SQL> select dbtimezone from dual;

DBTIME
------
+02:00

SQL> startup force
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             226492416 bytes
Database Buffers          293601280 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> select dbtimezone from dual;

DBTIME
------
-05:00


MySQL slave seems confused as to just how far out of date it is I have a slave that claims, in SHOW SLAVE STATUS, to be zero seconds behind the master.

However, it also says it's on Master_Log_File binlog.009636, which is 46.9 gig behind where the master is. I can look at the database and see the tables are all out of date.

No replication-related queries are running; the threads say "Waiting for master to send event" and "Has read all relay log; waiting for the slave I/O thread to update it." Yet the log IS moving, as it's now 46.7 gig behind.

What is going on here? Server is MySQL 5.0.95.

It means the thread has processed all events in the relay log files, and is now waiting for the I/O thread to write new events to the relay log.

Wait and watch whether size decreases as it seems to be updating because initially size was 46.9G and now it is 46.7G


How can I recover oracle database without new data? I have oracledb 11g. I setup archivelog mode on my server. There's my backup\recovery scenario:

rman TARGET SYS/pass@XE
RMAN> backup database plus archivelog;

After that I make changes to the data tables. Create new customer as example.
After that I execute in rman:

shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;

My server is started. I run my application and creater customer is there... Where's my mistake? I tried delete all dbf from db folder. And after restore database I saw my new customer. How can I restore a database without the data that was added to the database after the backup?

You performed a complete recovery. Recovery uses the available archivelogs and redo logs as well.

To perform an incomplete recovery until the time that's before the changes, use:

recover database until time "to_date('2018-02-24 15:05:20', 'YYYY-MM-DD HH24:MI:SS')";
alter database open resetlogs;

Set the point of time as needed.


Frequent or Infrequent Commit I'm trying to run an update query but still taking to long I have a commit in it.
Is it better to set the commit to a higher value (5000) or lower (1)?
I have to update 15 million records in a table of 21 million

edit here's my query for the update but I need to know how low/high I should set the commit

DECLARE
CURSOR c_itemloc
IS
  SELECT item, loc
  FROM dc_item_loc;

 l_item item_loc.item%TYPE;
 l_loc item_loc.loc%TYPE;
 i        NUMBER;
 l_commit VARCHAR2(1) := 'Y';

 BEGIN
   i:=0;
   FOR r_itemloc IN c_itemloc
   LOOP
     i := i+1;
     UPDATE item_loc il
     SET il.status             = 'D',
        il.last_update_datetime = get_vdate,
        IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
     WHERE item                = r_itemloc.item
          AND loc                   = r_itemloc.loc;

     IF l_commit = 'Y' AND mod(i, 5000) = 0  THEN
        COMMIT;
     END IF;

    END LOOP;

  EXCEPTION
  WHEN OTHERS THEN
   dbms_output.put_line('YOU FAIL');
  END;

Why are you doing this in PL/SQL in the first place?  The most efficient way to do anything in an Oracle database should be to do it in SQL

 UPDATE item_loc il
 SET il.status             = 'D',
    il.last_update_datetime = get_vdate,
    IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
 WHERE EXISTS( SELECT 1
                 FROM dc_item_loc d
                WHERE d.item = il.item
                  AND d.loc  = il.loc );


optimizing query with ip4r extension I need to do A LOT (millions) of lookups and I need them be as efficient as possible.

I'm using the ip4r extension for PostgreSQL:
https://github.com/RhodiumToad/ip4r/blob/master/README.ip4r

Running PostgreSQL 9.6.1

My tables:

CREATE TABLE test_cidr_ipv6
(
    id SERIAL PRIMARY KEY,
    cidr ip6r NOT NULL,
    domain INT NOT NULL,
    start TIMESTAMP NOT NULL,
    end TIMESTAMP,
    data JSON NOT NULL
);
CREATE TABLE test_ipv6
(
    id SERIAL PRIMARY KEY,
    ip ip6 NOT NULL,
    domain INT NOT NULL,
    timestamp_start TIMESTAMP NOT NULL,
    timestamp_end TIMESTAMP,
    data JSON NOT NULL
);

Two Indices:

CREATE INDEX test_ipv6_idx ON test_ipv6 (ip);
CREATE INDEX test_cidr_ipv6_idx ON test_cidr_ipv6 USING GIST (cidr);

Current query:

(SELECT
   test_ipv6.ip,
   test_ipv6.data,
   test_cidr_ipv6.cidr,
   test_cidr_ipv6.data,
   test_ipv6.start,
   test_ipv6.end,
   test_cidr_ipv6.start,
   test_cidr_ipv6.end
 FROM test_ipv6, test_cidr_ipv6
 WHERE
   test_ipv6.ip = ip6('2001:DB8::1')
   AND ip6('2001:DB8::1') <<= test_cidr_ipv6.cidr
   AND '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
   AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end)
UNION ALL
(SELECT ... )  # same as the query above, but with a different ip.
UNION ALL
...            # repeated 1000+ times

Question #1-
Is there a better way to repeat the same query like I'm doing in a single "batch" without UNION ALL?

Analyze on a single query:

db=# explain analyze SELECT
db-#    test_ipv6.ip,
db-#    test_ipv6.data,
db-#    test_cidr_ipv6.cidr,
db-#    test_cidr_ipv6.data,
db-#    test_ipv6.start,
db-#    test_ipv6.end,
db-#    test_cidr_ipv6.start,
db-#    test_cidr_ipv6.end
db-#  FROM test_ipv6, test_cidr_ipv6
db-#  WHERE
db-#    test_ipv6.ip = ip6('2001:DB8::1')
db-#    AND ip6('2001:DB8::1') <<= cidr
db-#    AND '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
db-#    AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end;
                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..16.34 rows=1 width=127) (actual time=0.023..0.026 rows=2 loops=1)
   ->  Index Scan using test_ipv6_idx on test_ipv6  (cost=0.14..8.17 rows=1 width=71) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: (ip = '2001:DB8::1'::ip6)
         Filter: (('2017-01-01 00:00:00.1'::timestamp without time zone >= start) AND ('2017-01-01 00:00:00.1'::timestamp without time zone <= end))
   ->  Index Scan using test_cidr_ipv6_idx on test_cidr_ipv6  (cost=0.14..8.17 rows=1 width=56) (actual time=0.005..0.008 rows=2 loops=1)
         Index Cond: ('2001:DB8::1'::ip6r <<= cidr)
         Filter: (('2017-01-01 00:00:00.1'::timestamp without time zone >= start) AND ('2017-01-01 00:00:00.1'::timestamp without time zone <= end))
 Planning time: 0.154 ms
 Execution time: 0.066 ms
(9 rows)

Question #2-
Is the "Nested Loop" concerning?
Is there a better way to structure this type of query to get the data I'm after?

When I do the "bulk" query by doing a UNION ALL a bunch of times with this same query block, the execution time is ~4sec for 5k IPs at a time. Ideally, I would like to get 5k of IPs down to 500ms.

If anyone has any pointers for how to make this type of query more efficient I would be forever thankful.

For the Nested Loop, there is nothing wrong with that.. You have less than five rows you're returning and you do it in one loop.
I'm not sure what you mean as batch. You'll have to show your other queries. Why are you running a UNION ALL a bunch of times to begin with?

Try something like this for your batch,

SELECT
  test_ipv6.ip,
  test_ipv6.data,
  test_cidr_ipv6.cidr,
  test_cidr_ipv6.data,
  test_ipv6.start,
  test_ipv6.end,
  test_cidr_ipv6.start,
  test_cidr_ipv6.end
FROM test_ipv6
INNER JOIN test_cidr_ipv6
  ON (test_ipv6.ip <<= cidr )
WHERE
  test_ipv6.ip = any(ARRAY[ip6('2001:DB8::1'),ip6('2001:DB8::1')]::ip6[])
  AND '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
  AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end;

You may have better luck with a CTE or inlining something like this...

WITH t AS (
  SELECT ipv6(iptext) AS ip
  FROM (VALUES
   ('fddf:c4c1:2573::/48') ,
   ('fd8d:d482:3c08::/48')
  ) AS v(iptext)
)
SELECT
  test_ipv6.ip,
  test_ipv6.data,
  test_cidr_ipv6.cidr,
  test_cidr_ipv6.data,
  test_ipv6.start,
  test_ipv6.end,
  test_cidr_ipv6.start,
  test_cidr_ipv6.end
FROM test_ipv6
INNER JOIN test_cidr_ipv6
  ON (test_ipv6.ip <<= cidr)
INNER JOIN t
  USING (ip)
WHERE
  '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
  AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end;


User in multiple windows groups mapped to sql logins I have two C# apps with Windows Auth to Microsoft SQL Server 2008 R2.

For every app:

SQL login based on Windows Group is created
SQL server login is mapped to SQL database user
user is member of a role
role has granted permissions to tables, views, procedures, etc.

Question is:

If I add a single windows user as a member to both windows groups (with are "mapped" to different SQL logins, which are mapped to different SQL users, which belongs to different roles and so on..) - could user run both apps without any problems or only one of them will work properly?

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login.  And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2.  And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2


Filtered index is not used when variable in WHERE condition Why MS SQL Server refuse using supporting filtered index in this scenario?

-- demo data
CREATE TABLE #Test (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
    ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID()
    ,Col2 NVARCHAR(20) NOT NULL DEFAULT N''  -- !!
    );

WITH
    L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
    L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
    L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
    L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
    L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
    L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
INSERT INTO #Test(Col2)
SELECT TOP 100000 N''
FROM Nums;

INSERT INTO #Test(Col2)
VALUES(N'ABC');

-- FILTERED index to support filter predicate of a query
CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N'';

-- just checking statistics
DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered')

-- condition on variable = index scan :-(
DECLARE @Filter NVARCHAR(20) = N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

Everything goes as expected when using literals.

-- condition on literal value - index seek + key lookup :-)
SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

Filtered indexes can't use variables/parameters, unless you're building your query with dynamic SQL so that the query ends up getting executed with a literal.

A good article on the topic is Filtered Indexes and Dynamic SQL by Jeremiah Peschka.

OR

As Martin suggested, you could add WITH (RECOMPILE) to the query, but that should not be used without understanding the potential repercussions (see RECOMPILE Hints and Execution Plan Caching).


PostgreSQL full text search words delimiter Please help me to understand how to change words delimiter when I use gin index. The problem is that "," ";" and " " signs are words delimiters but the "." isn't. So in phrase "Some 11.22.33.44 IP" I am able to find "Some" "11.22.33.44" "IP" words. When I am looking for "33" - unable to find it. Many thanks for the help.

The "natural" way to do this would be to change or write your own text search parser.  While it is possible to do that, it is unreasonably difficult.

Do you want both '11.22.33.44' and '33' to show up as individual tokes, or just '33'?  If the latter, then maybe just preprocess your data by replacing all '.' with ' ':

replace(doc,'.',' ')

Of course you should do the same replacement on the queries as well before passing them to plainto_tsquery.


AWS RDS - Changing Instance Class So I'm fairly new to AWS and I'm hoping you guys can reassure me as I couldn't find anything in the AWS documentation either way. I actually have two questions, the first:

I have an RDS MySql instance in AWS that wasn't set up by myself (a previous employee did this). This instance is more powerful than we need so I want to reduce it's instance class in order to reduce costs.

Can I simply change it and apply the changes at the next maintenance schedule without loosing data?

The current instance is a multi-AZ configuration.

The second:

How can I identify how much of the RDS storage is currently in use? As I'd like to decrease this if possible as we aren't even using 50% of it.

Thanks,

Changing Instance Class:

From the documentation "Modifying a DB Instance and Using the Apply Immediately Parameter", changing the instance class and setting "Apply Immediately" to false will result in:

The change being applied during the next maintenance window.
The change causing an outage to occur.

Since the instance is Multi-AZ and you're changing the instance class, your outage will be minimized to the amount of time it takes to failover to the standby replica. That isn't very long; likely worst-case a minute or two. You won't lose data that is already in the DB. Any existing connections will be dropped during failover. You should verify that applications connected to your database can withstand these conditions, or alternatively take them down for maintenance during this time.

Identifying RDS instance storage capacity:

You can identify how much of the RDS storage is currently in use by either:

1) Simply selecting your RDS instance from the RDS web console and viewing the "Storage" bar under the "Monitoring" header.

2) Viewing the "FreeStorageSpace" RDS cloudwatch metric for your RDS instance.

Further Reading:

RDS Documentation - Viewing DB Instance Metrics


Can sequential queries result in concurrent transactions? I'm using PostgreSQL 9.4.4 and the default configuration. I use a JDBC connection to run multiple queries in succession. Only one Java thread is using the connection and before a new query is executed the result set of the former one is fetched. Below a simplified sketch:

while(true) {
    PreparedStatement stmt = //...
    ResultSet rs = stmt.executeQuery();
    //process result
}

The query that is causing trouble invokes the following stored procedure:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id BIGINT
                                            , _source_client_id BIGINT)
 RETURNS TEXT AS
$$
   DECLARE rv TEXT;
   BEGIN
     DELETE FROM message m
     USING (
        SELECT * FROM message
        WHERE message.queue_id = _queue_id
        AND (message.receiver_client_id = _source_client_id OR
             message.receiver_client_id = -1)
        ORDER BY insertion_time ASC LIMIT 1) AS tmp
     WHERE m.id = tmp.id
     RETURNING m.content INTO rv;

     IF (SELECT rv IS NULL) THEN
       RAISE 'No message available.';
     END IF;
     RETURN rv;
   END;
$$ LANGUAGE plpgsql;

I am observing behavior as it is described here http://www.postgresql.org/docs/9.1/static/transaction-iso.html in the section 13.2.1. Read Committed Isolation Level.

What happens is that the exception in the procedure is triggered although when I abort everything when the exception happens and invoke the procedure manually the exception is not raised. I noticed further that if a transaction is already holding a row exclusive lock on the message table that then the exception is raised.

Also if I add

     LOCK TABLE message IN ROW EXCLUSIVE MODE;

at the beginning of the procedure the problem disappears.

I thus suspect that in the above loop that a query is returning a result before its transaction is actually committed such that the next transaction is conflicting with it. Can that happen? N.B. I did not find something that explains this in the PostgreSQL documentation. Also I don't have Async commit enabled.

To answer your question in the title: No.

Consecutive queries in the same session (same transaction or not) are never concurrent. And there can only be one transaction at a time in the same session. Concurrency issues are impossible that way, those only happen between transactions in multiple sessions.

One possible reason for your error message might be a glitch in your function, which can be simplified:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id bigint
                                            , _source_client_id bigint)
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY
   DELETE FROM message m
   USING (
      SELECT id
      FROM   message
      WHERE  queue_id = _queue_id
      AND    receiver_client_id IN (_source_client_id, -1)
      ORDER  BY insertion_time
      LIMIT  1
      ) m1
   WHERE  m.id = m1.id
   RETURNING m.content;

   IF NOT FOUND THEN
      RAISE 'No message available.';
   END IF;
 END
$func$ LANGUAGE plpgsql;

Your original check:

IF (SELECT rv IS NULL) THEN

could be simplified to:

IF rv IS NULL THEN

But that's still no good. It fires when message.content is NULL, not only when no row has been found. The table definition is missing in the question, so we don't know if that's possible.

Either way, the special variable FOUND is the better tool for the purpose. My audited version is also simpler and faster. Note that plpgsql functions do not return results until the end of the function or RETURN is reached. We can just return rows and still cancel the result later if need be. There is an example in the manual, which is a spin-off from this related question on SO:

FUNCTION syntax error


SQL Server statistics I have run the Database Tuning Advisor and it has suggested that some statistics are created.  Whilst this is great and I understand the purpose of statistics, when I have reviewed them it has created 5 or more with very similar columns included in them.  If I went ahead and created these statistics, if a column is replicated in more than one of these statistics and the first statistic is updated, how does this update affect the other statistics?

Thanks,

Including multiple columns in statistics adds a new dimension that cannot be estimated otherwise: correlation.

Query Predicate Contains Multiple Correlated Columns

  When a query predicate contains multiple columns that have cross-column relationships and dependencies, statistics on the multiple columns might improve the query plan. Statistics on multiple columns contain cross-column correlation statistics, called densities, that are not available in single-column statistics. Densities can improve cardinality estimates when query results depend on data relationships among multiple columns.

Optimizing Queries That Access Correlated datetime Columns
Exploring Column Correlation and Cardinality Estimates

  , if a column is replicated in more than one of these statistics and the first statistic is updated, how does this update affect the other statistics

All statistic need to be updated to be accurate.

Whether all stats recommended by the DTA are necessary, that is a different topic.


PostgreSQL: Generated Columns Does PostgreSQL support generated columns? Also know as virtual columns. I am not talking about IDENTITY columns.

I can’t find any information on this remarkable feature but I know that it is available on SQL Server, and in the latest versions of MariaDB & MySQL.

The feature is mentioned in the SQL:2003 standard, and there was some discussion on the PostgreSQL forums around 2006, but I can’t find anything substantial on the matter.

There is some discussion on SO, but it is quite old now, so it may well be out of date.

Not sure if this is what you want, but attribute notation row.full_name and function notation full_name(row) are equivalent in postgresql.

That means you take a table

CREATE TABLE people (
  first_name text,
  last_name text
);

and a function:

CREATE FUNCTION full_name(people) RETURNS text AS $$
  SELECT $1.first_name || ' ' || $1.last_name;
$$ LANGUAGE SQL;

and call it like this:

select full_name from people

Is that what you need?

To speed up things you can create an expression index:

CREATE INDEX people_full_name_idx ON people
USING GIN (to_tsvector('english', full_name(people)));

Or store everything in a materialised view.

Example taken from here: http://bernardoamc.github.io/sql/2015/05/11/postgres-virtual-columns/


Change Data Capture and the __$update_mask binary We're using CDC to capture changes made to a production table.  The changed rows are being exported out to a data warehouse (informatica).  I know that the __$update_mask column stores what columns were updated in a varbinary form.  I also know that I can use a variety of CDC functions to find out from that mask what those columns were.

My question is this.  Can anyone define for me the logic behind that mask so that we can identify the columns that were changed over in the warehouse?  Since we're processing outside of the server we don't have easy access to those MSSQL CDC functions.  I would rather just break down the mask myself in code.  Performance of the cdc functions on the SQL end is problematic for this solution.

In short, I'd like to identify changed columns by hand from the __$update_mask field.

Update:

As an alternate sending a human readable list of changed columns over to the warehouse was also accepatable.  We found this could be performed with performance far greater than our original approach.

The CLR answer to this question below meets this alternative and includes details of interpreting the mask for future visitors.  However the accepted answer using XML PATH is the fastest yet for the same final result.

And the moral of the story is... test, try other things, think big, then small, always assume there is a better way.

As scientifically interesting as my last answer was.  I decided to try one other approach.  I remembered I could do concat with the XML PATH('') trick.  Since I knew how to get the ordinal of each changed column from the captured_column list from the previous answer I thought it would be worth testing if the MS bit function would work better that way for what we needed.

SELECT __$update_mask ,
        ( SELECT    CC.column_name + ','
          FROM      cdc.captured_columns CC
                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
          WHERE     capture_instance = 'dbo_OurTableName'
                    AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
                                              PD.__$update_mask) = 1
        FOR
          XML PATH('')
        ) AS changedcolumns
FROM    cdc.dbo_MyTableName PD

It's way cleaner than (though not as fun as) all that CLR, returns the approach back to native SQL code only.  And, drum roll.... returns the same results in less than a second.  Since the production data is 100 times bigger every second counts.

I'm leaving the other answer up for scientific purposes - but for now, this is our correct answer.


Something along the following lines should do what you need (DEMO)

WITH DistinctCounts
     AS (SELECT EventCount = COUNT(DISTINCT EID),
                PID
         FROM   #EventsTBL
         GROUP  BY PID),
     T1([PID], EID, Date, Flag)
     AS (SELECT [PID],
                EID,
                StartDate,
                1
         FROM   #EventsTBL
         UNION ALL
         SELECT [PID],
                EID,
                EndDate,
                -1
         FROM   #EventsTBL),
     T2
     AS (SELECT *,
                ActiveCount = SUM(Flag) OVER (PARTITION BY [PID] ORDER BY Date ROWS UNBOUNDED PRECEDING)
         FROM   T1),
     T3
     AS (SELECT *,
                PrevActiveCount = LAG(ActiveCount, 1, -1) OVER (PARTITION BY [PID] ORDER BY Date)
         FROM   T2),
     T4
     AS (SELECT T3.*,
                RN = ROW_NUMBER() OVER (PARTITION BY T3.[PID] ORDER BY T3.Date) - 1
         FROM   T3
         WHERE  ActiveCount <> PrevActiveCount
                AND EXISTS (SELECT *
                            FROM   DistinctCounts DC
                            WHERE  DC.PID = T3.PID
                                   AND DC.EventCount IN ( T3.ActiveCount, T3.PrevActiveCount )))
SELECT PID,
       StartDate = MIN(Date),
       EndDate = MAX(Date)
FROM   T4
GROUP  BY PID,
          RN / 2


Postgresql 9.4: How to find out the stack level of nested function Here are the two cases:

Function fA fetches some rows and returns a table
Function fB calls function fA and returns a table

Within fA, how could I know whether it's been called from the program or by fB?

Is there some @@nestlevel that can count how deep in the stack are the functions running?

Big thank you in advance,

There's a different, simpler, solution for this. Add an argument to function fA with a default value, for instance: called_from_b BOOLEAN DEFAULT FALSE. Then, provide that argument when calling fA from fB.

I understand that this doesn't directly answer your question, but it does provide a solution for the example usecase that you provide.


Long running index rebuild I'm running an index rebuild using Ola Hallengren's script below.

EXECUTE dbo.IndexOptimize
 @Databases = 'insight',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @LogToTable = 'Y'

It has been running for almost 2 days. How bad would it be to kill the job? The index it is running against is 78% fragmented. The table at issue has 111,543,114 rows.

You should be able to see if it's doing a reorg or a rebuild by using sp_whoisactive or something similar and inspecting the sql_text column.

If you kill it, you will not lose the work it has done on previous indexes that were defragmented as part of this script.  Those are done and committed.

As for the current index it's defragmenting, if it's doing a reorg, the progress it has made will not be lost if you kill it.  If it's a rebuild, which seems to be the case given the data you've provided, it's still OK to kill it, but you will lose any progress that has been made.


not a GROUP BY expression error when using a function in a query I use a function in a query to select data but I have this error:

  not a GROUP BY expression

This is the query:

SELECT          PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE,
                         COUNT(DISTINCT ATT_EMPLOYEEATTENDANCE.ATTEND_DATE) AS WORKSDAYS
                         ,nvl(getEmployee_OffDays ('" + dtp_PrintManth.Text + "',PROF_EMPLOYEE_DATA.SHIFT_ID),0) - GETEMPLOYEE_OFFDAYSWork(extract(month from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),extract(year from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),PROF_EMPLOYEE_DATA.EMPLOYEE_ID)as OffDays
FROM            PROF_EMPLOYEE_DATA INNER JOIN
                         PROF_TREE_DETAILS ON PROF_EMPLOYEE_DATA.TREE_ID = PROF_TREE_DETAILS.TREE_ID INNER JOIN
                         PROF_JOBS_DETAILS ON PROF_EMPLOYEE_DATA.JOB_ID = PROF_JOBS_DETAILS.JOB_ID LEFT OUTER JOIN
                         ATT_EMPLOYEEATTENDANCE ON PROF_EMPLOYEE_DATA.EMPLOYEE_ID = ATT_EMPLOYEEATTENDANCE.EMPLOYEE_ID
WHERE        (PROF_EMPLOYEE_DATA.EMP_CODE = '12411') AND (ATT_EMPLOYEEATTENDANCE.ATTEND_DATE BETWEEN TO_DATE('10/1/2016', 'MM/DD/YYYY') AND TO_DATE('10/31/2016', 'MM/DD/YYYY'))
GROUP BY PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE

And this is the function that I created to display days number in month:

create or replace
FUNCTION "GETEMPLOYEE_OFFDAYS" (month_p nvarchar2,shift_id_p number)
  RETURN decimal
  IS VALUE_P decimal;
    VALUE1_P decimal;
   VALUE2_P decimal;
    i   NUMBER := 0;
BEGIN

for RC in
(SELECT LOK_DAYS.*
FROM ATT_SHIFTS
RIGHT JOIN ATT_SHIFTSDAYSOFF
ON ATT_SHIFTS.SHIFT_ID = ATT_SHIFTSDAYSOFF.SHIFT_ID
LEFT JOIN LOK_DAYS
ON LOK_DAYS.DAY_ID        = ATT_SHIFTSDAYSOFF.DAY_ID
WHERE ATT_SHIFTS.SHIFT_ID = shift_id_p
)
loop
i := i + 1;
IF i =1 THEN
      select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value_p
         from dual
         where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
         OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
         connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd')  ;

         else if i=2 then
         select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value1_p
         from dual
         where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
         OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
         connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd')  ;

    END IF;
     END IF;
END LOOP;
RETURN nvl(VALUE_P,0)+nvl(value1_p,0);
END;

You can not just use any function in the SELECT list when you use GROUP BY.

You need to use an aggregate function (MIN, MAX, SUM, etc) on columns not included in the GROUP BY list, or include the columns used by a regular function in the GROUP BY list.

You use PROF_EMPLOYEE_DATA.SHIFT_ID and PROF_EMPLOYEE_DATA.EMPLOYEE_ID with your function, but they are not included in the GROUP BY list.

Including those columns in the GROUP BY list should solve this, but that may not provide the result you want.

Another way is to write your own aggregate function:

Using User-Defined Aggregate Functions


Most likely, your user5 has database (i.e. schema) privileges, just not server-wide privileges.  So look here.

USE app_db;
SHOW GRANTS FOR user5;

or check in the data dictionary for the same:

SELECT *
FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
WHERE GRANTEE LIKE '%user5%';

if not anything, then maybe also check

SELECT *
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE LIKE '%user5%';


Postgres COPY command gets Error: out of memory When copying a large amount of data (around 9GB) from CSV file into table, I get following error:

ERROR:  out of memory
 DETAIL:  Cannot enlarge string buffer containing 1073741632 bytes by 349 more bytes.
 CONTEXT:  COPY column_name line 13275136

A server (postgresql 10) has 8GB of memory and database has shared_buffers set to 2GB.

Can somebody suggest a solution, please. Or problem is instead in column_name length ( according to CONTEXT:  COPY column_name line 13275136 message)

DETAIL:  Cannot enlarge string buffer containing 1073741632 bytes by
  349 more bytes.
  CONTEXT:  COPY column_name line 13275136

According to the error message, the offending row at this line number in the file is too large to be imported. COPY uses an internal buffer per row that can only grow to 1GB. It's impossible to import or export larger rows with COPY in existing versions of PostgreSQL (up to and including version 12)


SSPI Handshake failed I am facing a strange situation... On a SQL2016 on Windows 2019, I have an application running on windows2019 that connect to the SQL server using a domain service account (same domain as the
SQL Server).

In the SQL server Log, I can see those errors:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: x.x.x.x]
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The logon attempt failed   [CLIENT: x.x.x.x]

Well, the first error does not make sense as the login is in the same domain.

The weird thing is that when they reboot the app server, then suddently, those error goes away and are replaced by "login succeeded". And then, hours or days later, it start failling again.

If it was the spn, it would never work. If it was a TLS issue, I'm guessing it would never work as well.

What could it be that make it work for a certain time and then fail ?

Thank you.

maybe your account is being locked out... are you using it someplace else? Did you check in your DC for events for this account? You could use a SQL Server login instead of a domain account.


How is the MongoDB backup snapshot transferred from Ops Manager to file system store? I am using Ops Manager for backup and restore.

In docs, it is mentioned that the initial sync between deployment database and Ops manager happens via streaming 10 MB compressed files.
https://docs.opsmanager.mongodb.com/current/core/backup-overview/

however, I am looking for how the snapshot is being transferred from Ops Manager to file system store.

As per MongoDB documentation here Ops Manager creates backups of MongoDB replica sets and sharded clusters. After an initial sync, Ops Manager tails the operation log (oplog) to provide a continuous backup with point-in-time recovery of replica sets and consistent snapshots of sharded clusters.

Why doesn’t the Backup feature support standalone deployments?

After an initial sync of your data, Ops Manager copies data from the oplog to provide a continuous backup with point-in-time recovery. Ops Manager does not support backup of standalone servers, which do not have an oplog. To support backup with a single mongod instance, you can run a one-member replica set.

How can I use Backup if Backup jobs fail to bind?

The most common reason that jobs fail to bind to a Backup Daemon is because no daemon has space for a local copy of the backed up replica set.

To increase capacity so that the backup job can bind, you can:

add an additional backup daemon.
increase the size of the file system that holds the Head
directory.
move the Head database data to a new volume with more space, and
create a symlink or configure the file system mount points so that
the daemon can access the data using the original path.


MAX worker thread in SQL server 2012/14/16 I was just browsing the system health extended event to come across an event with diagnostic result for query processing showing status for worker threads:

Why max worker threads showing 2994 when I have 80 logical processors which as per msdn calculation here 512 + ((logical CPU's - 4) * 16) does not looks correct. However per the latest link here makes sense for logical processors greater than 64 where calculation for 16 changes to 32. But that link says starting SQL 2017, however the number 2994 I am getting is for SQL2012, 14 16 where no of logical processors is 80. Am I missing something here?

max worker setting within sp_configure is set to '0'

Coming back to that system health event status shows WARNING for below no:

  maxWorkers="2944" workersCreated="456" workersIdle="314"
  tasksCompletedWithinInterval="1021881" pendingTasks="1"

Should I be checking something else or can this be ignored?

why max worker threads showing 2994 when i have 80 logical processors […]

It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs. I'll make the required documentation changes. The documentation has been updated.

  Coming back to that system health event status shows WARNING for below no […]

The reason you have a warning is because you have pendingTasks="1". This could indicate an issue with a scheduler and should be investigated.

  I see pending task count varying sometimes 2, 4 , 8 or even higher.

In the same Docs article that you linked, there is a query that can be used to track tasks and their status. It'd be interesting to see if it's only tasks on a specific scheduler, only login tasks, etc.

SELECT  s.session_id, r.command, r.status,
   r.wait_type, r.scheduler_id, w.worker_address,
   w.is_preemptive, w.state, t.task_state,
   t.session_id, t.exec_context_id, t.request_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
   ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks AS t
   ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers AS w
   ON t.worker_address = w.worker_address
WHERE t.task_state = N'PENDING'


Stored Procedure slow after weekend When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on.
However, after running the procedures 10-20 times with different parameters, the execution time drops to less than one second.

I think that for some reason the data buffer expires over the weekend, and it takes a lot of time to reload the buffer on monday.

How do I make sure my application works fast even if it's not used regulary. Is there a way to persist the buffer, or should I rewrite the procedures in a way that doesn't rely on the buffer?

Most likely activity on the server over the weekend has caused your data to be evicted from memory. This is just the way life is in a shared environment with limited resources. It would be good to understand what's happening over the weekend and how your usage affect other users of this service.

You could schedule a job for early on Monday, before anyone is in the office.  It would run your SPs with the various parameter values to warm up the bufferpool with the data you will be using.  This will evict whatever is in memory currently, slowing the process that is using it.


Should a privileged port (<1024) be used to deploy Postgresql in production? I've never found a recommendation to run Postgresql on a privileged port in production. What type of port should be used in production regarding security and best practices?

Using a privileged port for Postgres would require the postgres daemon to run with root privileges, which in itself is a security vulnerability. So, no, you should not use a privileged port for Postgres.


database design question with Partition and Replication I have a main database that runs on a server with good resources and, let's say, a dozen satellites that replicate data. Satellites have less resources than main server.

In that database there is a BIG table, that I want partitioned in ranges because in fact each satellite cares about his own specific range.

PARTITION BY RANGE( ID ) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN ...
);

Let's say satellite 1 cares about table IDs that start with 1, satellite 2 cares about IDs that start with 2 and so on. (all IDs are numbered in millions).

Is it possible to replicate on each satellite only data that are relevant to the satellite? I mean I don't want p2 and p3 on satellite 1, only p1 is relevant for satellite 1. But I don't know how to say to the replication engine that I want only such a part of the data.

Is it only possible to replicate only one partition of the partition scheme?

Looks like you are looking for NoSQL DB with Data shard across nodes. Check the MySQL NDB Cluster.


Returning data where all categories exactly match I have a documents table that I'm rendering out in a ui.

This ui has a set of select2 dropdowns for different category types and the dropdowns are populated data from a categories table.

Each of these dropdowns allows for multiple selections and will post a named array of values to the server.

What I'm hoping to achieve is to only display the relevant documents based on matching the posted cat_id's within a cat_type

These are example tables

Documents

+----+------------+-------------+
| id | doc_title  | doc_version |
+----+------------+-------------+
|  1 | item 1     |           4 |
|  2 | document 2 |           3 |
+----+------------+-------------+

Categories

+----+--------+----------+
| id | cat_id | cat_type |
+----+--------+----------+
|  1 |      1 |        3 |
|  1 |      7 |        2 |
|  1 |      4 |        2 |
|  2 |      7 |        2 |
+----+--------+----------+

An example might be querying documents where the cat_id = 7 and the cat_type = 2.
This should to return row 1 and 2 of the documents table.

What I'm having difficulty with is query documents for a match where multiple cat_id's are declared for a cat_type.

An example might be querying documents where the cat_id = 7 and 4  and the cat_type = 2.
This should to return row 1 of the documents table.

I was thinking I could of used WHERE cat_id IN (4,7) AND cat_type = 2, however that returns results that match either cat_id.

How should I go about querying the data in order to return document entries that exactly match cat_id's for a cat_type?

Thanks

You cqan use the IN clause for that
SELECT
`id`, `doc_title`, `doc_version`
FROM Documents WHERE id in (SELECT `id`
                            FROM Categories
                            WHERE `cat_id` = 7  AND  `cat_type` = 2);

CREATE TABLE Documents (
  `id` INTEGER,
  `doc_title` VARCHAR(10),
  `doc_version` INTEGER
);

INSERT INTO Documents
  (`id`, `doc_title`, `doc_version`)
VALUES
  ('1', 'item 1', '4'),
  ('2', 'document 2', '3');

CREATE TABLE Categories (
  `id` INTEGER,
  `cat_id` INTEGER,
  `cat_type` INTEGER
);

INSERT INTO Categories
  (`id`, `cat_id`, `cat_type`)
VALUES
  ('1', '1', '3'),
  ('1', '7', '2'),
  ('1', '4', '2'),
  ('2', '7', '2');

✓

✓

✓

✓

SELECT
`id`, `doc_title`, `doc_version`
FROM Documents WHERE id in (SELECT `id`
                            FROM Categories
                            WHERE `cat_id` = 7  AND  `cat_type` = 2);

id | doc_title  | doc_version
-: | :--------- | ----------:
 1 | item 1     |           4
 2 | document 2 |           3

db<>fiddle here


Could we delete old database and grid version? We upgraded rac system and I want to ask, could I delete old grid and database version?

[oracle@orcl1 app]$ cd

11.2.0.3/     12.1.0.2/     Middleware/   oraInventory/

11.2.0.4/     grid/         oracle/       swlib/

I want to ask  can I delete manually "rm command line command"11.2.0.3/ and 11.2.0.4  grid files...  oracle/product/11.2.0.3 database ...

That's my inventory.xml

<?xml version="1.0" standalone="yes" ?>

<!-- Copyright (c) 1999, 2014, Oracle and/or its affiliates.

All rights reserved. -->

<!-- Do not modify the contents of this file by hand. -->

<INVENTORY>

<VERSION_INFO>

   <SAVED_WITH>12.1.0.2.0</SAVED_WITH>

   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<HOME NAME="OraDb11203_home1" LOC="/u01/app/oracle/product/11.2.0.3/dbhome_1" TYPE="O" IDX="2">

   <NODE_LIST>

      <NODE NAME="orcl1"/>

      <NODE NAME="orcl2"/>

   </NODE_LIST>

</HOME>

<HOME NAME="OraDb12102_home1" LOC="/u01/app/oracle/product/12.1.0.2/dbhome_1" TYPE="O" IDX="5">

   <NODE_LIST>

      <NODE NAME="orcl1"/>

      <NODE NAME="orcl2"/>

   </NODE_LIST>

</HOME>

<HOME NAME="OraGrid12102" LOC="/u01/app/12.1.0.2/grid" TYPE="O" IDX="4" CRS="true"/>

<HOME NAME="OraGrid11gR3" LOC="/u01/app/11.2.0.3/grid" TYPE="O" IDX="1" REMOVED="T"/>

<HOME NAME="OraGrid11gR204" LOC="/u01/app/11.2.0.4/grid" TYPE="O" IDX="3" REMOVED="T"/>

</HOME_LIST>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

</INVENTORY>

EDIT:  I started deinstall tool in 11.2.0.3 .. I don'T want to delete grid 12.1.0.2 but I think it can delete 12.1.0.2's grid. right?

######################### CHECK OPERATION START #########################
## [START] Install check configuration ##

Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0.3/dbhome_1
Oracle Home type selected for deinstall is: Oracle Real Application Cluster Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /u01/app/12.1.0.2/grid
The following nodes are part of this cluster: orcl1,orcl2
Checking for sufficient temp space availability on node(s) : 'orcl1,orcl2'

## [END] Install check configuration ##

Network Configuration check config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2015-11-02_08-53-27-AM.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2015-11-02_08-53-29-AM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home []:

Oracle provides a deinstallation tool starting with Oracle 11.

It's located in $ORACLE_HOME/deinstall/  (in your case the old Homes). It takes care of the inventory and remote hosts in case of a RAC.


First names database design I have a large amount of data on users (about 8 million users).

I want to mine this data to create a table with statistics about first names. It should also include as many variations of each name as possible. The variations of first names ("John" and "Johny") should be recognized and treated properly.

The purpose of this table is to quickly find variations for a name and select one, in order to use user's non-standard name when contacting him. The name to be searched for can be his standard/ official ("John") or any variation of it ("Jack, Jackie, Jacky").

So far, I came up with following table structure:

table name: first_name_variations
+---------+----------+-------------+-------+
|  name   | group_id | is_standard | count |
+---------+----------+-------------+-------+
| Jack    |        1 |           1 |    53 |
| Jackie  |        1 |           0 |     5 |
| Francis |        2 |           1 |    32 |
| Frank   |        2 |           0 |    52 |
+---------+----------+-------------+-------+

The "name" column should be primary index. Standard/ official names are to be marked with "is_standard". The variations of one name must be assigned the same group_id.

Should I include surrogate key (e.g. id)? Will it improve something?

Should I create a separate table for name groups, or maybe just marking names with group_id would be sufficient?

Since my database knowledge is rather shallow, can you please propose improvements to his approach.

The database is MySQL

UPD: I think I'll remove 'is_standard' column; it's an overkill.

Adding a surrogate id will not help in this case.  You already have a good PRIMARY KEY(name), correct?

The 4 columns look good for the purposes mentioned.  You probably need a composite INDEX(group_id, name) to facilitate finding all the related queries.

What do you do about nicknames that apply to two standard names?

What do you do about someone with a non-"standard" name as their 'official' name?


I need to merge two queries with condition I have the following query:

SELECT
URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE",
URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION",
URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_COA as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.DIFFERENCE_MN as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.DIFFERENCE_ME as " MONEDA EXTRANJERA"
FROM
  TNLRUY.URYRR_FORWARDS_REC_H

WHERE
1=1
<#if BOOK_DATE??&& BOOK_DATE?trim !=''>
AND BOOK_DATE=to_date(:BOOK_DATE,'yyyy-mm-dd')
</#if>

and another query:

SELECT
URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE",
URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION",
URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_COA as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.DIFFERENCE_MN as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.DIFFERENCE_ME as " MONEDA EXTRANJERA"
FROM
  TNLRUY.URYRR_FORWARDS_REC_H

My requirment is that if I enter the BOOK_DATE then it should execute the first query, and If I leave BOOK_DATE blank then the second query will execute.

Make your where clause operate conditionally based on your input value like this:

SELECT
URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE",
URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION",
URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_COA as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.DIFFERENCE_MN as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.DIFFERENCE_ME as " MONEDA EXTRANJERA"
FROM TNLRUY.URYRR_FORWARDS_REC_H
WHERE (:BOOK_DATE IS NULL OR
    (:BOOK_DATE IS NOT NULL AND BOOK_DATE=to_date(:BOOK_DATE,'yyyy-mm-dd'));


SQL Server Indexing Performance in Unique/Duplicate column It is said that if we create index on a column with more uniqueness than performance of
that index will be more. But I believe that whether it is unique or not it will occupy same no. of blocks than why due to uniqueness it will be faster.

The more unique the key is, the more you filter the data.
For example if you had an index on a field with only 2 unique values such as gender, the index would only split the search space in half or the number of records / 2.

Choosing to index a field with a greater amount of unique records (cardinality) will divide the search space further until you reach a unique key where every key maps to one record, which is the most efficient index.


Calculate ticket sales by using SUM in SELECT clause In this ticketing setup on dbfiddle, I am trying to calculate the ticket sales for a particular event by multiplying the ticket price and the total number of tickets sold for that event, based on their type i.e. standard, VIP.

So far I have this query,

SELECT tt.ticket_type,COUNT(tt.ticket_type) AS 'tickets_paid' , SUM((COUNT(tt.ticket_type)) *tc.price)
FROM tbl_tickets t INNER JOIN tbl_ticket_count tc ON t.ticket_count_id = tc.ticket_count_id
INNER JOIN tbl_events_new e ON tc.event_id = e.event_id
INNER JOIN tbl_ticket_type tt ON tt.ticket_type_id = tc.ticket_type_id
WHERE e.event_id = 164 AND t.pay_status ='paid' GROUP BY tt.ticket_type

but I keep getting 'invalid use of group function - 1111' error after adding the multiplication part of the clause. How can I carry out this operation with the SUM function using the ticket price of type decimal and number of tickets sold?

Try this:

SELECT
A.ticket_type_id,
A.tickets_paid,
A.tickets_paid*B.price as amount_paid
FROM
(SELECT tt.ticket_type_id,
COUNT(tt.ticket_type_id) AS tickets_paid
FROM tbl_tickets t
    INNER JOIN tbl_ticket_count tc
        ON t.ticket_count_id = tc.ticket_count_id
    INNER JOIN tbl_events_new e
        ON tc.event_id = e.event_id
    INNER JOIN tbl_ticket_type tt ON
        tt.ticket_type_id = tc.ticket_type_id
WHERE
    e.event_id = 164
    AND t.pay_status ='paid'
GROUP BY tt.ticket_type_id) as A
INNER JOIN tbl_ticket_count B ON
        A.ticket_type_id = B.ticket_type_id


Why does a fresh SQL Server instance with the same details as the original think that my DB backup is in the original location? So I'm working on a server application for a large government body and need to setup a development environment to test on. For this I'm using a Vagrant-controlled VM with Windows Server 2019 installed as the guest OS and SQL Server 2019 as the DB engine.

After creating and subsequently destroying the VM, then rebuilding it I am now trying to restore a DB backup to a fresh instance with the exact same instance name, logins etc. But I get this error from my PowerShell script:

PS C:\Users\vagrant> Invoke-SqlCmd -Query "RESTORE DATABASE DBName FROM DISK = '.\db-name.bak'"

Invoke-SqlCmd : Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL15.JIRADB\MSSQL\Backup\db-name.bak'. Operating system
error 2(The system cannot find the file specified.).
RESTORE DATABASE is terminating abnormally.
At line:1 char:1
+ Invoke-SqlCmd -Query "RESTORE DATABASE DBName FROM DISK = '.\db-name.bak'"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

What gives?! Clearly the backup file now exists at C:\Users\vagrant but SQL Server seems to think that it should be in the original, default backup location.

Obviously, I can simply move the backup file back to the original location, but I'd like to know why SQL Server is expecting that. MTIA! :D

The .\ is not interpreted in the Query parameter as 'current directory' and so SQL looks for the file in its default backup location. You have a couple of options

Change the Query value and specify the full path to the file
Pass a variable into the Query string (see below)

Option 1 is the simplest, but you may have need of using variables elsewhere, so here's an example:

$StrArray = "VAR1='C:\Temp\db-name.bak'"
invoke-sqlcmd -query "RESTORE HEADERONLY FROM DISK = `$(VAR1)" -Variable $StrArray

$StrArray is an array of strings that take the form Variable Name='Variable Value' and each is substituted in your query string at run time.

Invoke-SqlCmd


Ranking based off conditional field I am looking to select only 1 row based off the IMMS_ID field. Which row I select is based off this if statement (excuse the syntax)

if (status group based off IMMS_ID) status='DI' select the DI row
else if ... status='OI' ...
else if ... status='FI' ...
else if ... status='OV' ...
else if ... status='FV' ...

So this means if the group based off the IMMS_ID contains OI, OV and FV I want to select the row that has OI (as it's highest in the if/elseif statement).

Ex. Here is a dataset

ID  IMMS_ID  STATUS
 1      1       FV
 2      1       OI
 3      1       FI
 4      2       DI
 5      2       OV

So for this dataset I'd want the following results returned

ID  IMMS_ID  STATUS
 2      1       OI
 4      2       DI

Thanks for your time!

You can calculate a RANK/ROW_NUMBER based on this logic:

ROW_NUMBER()
OVER (PARTITION BY IMMS_ID
      ORDER BY CASE WHEN status='DI' THEN 1
                    WHEN status='OI' THEN 2
                    WHEN status='FI' THEN 3
                    WHEN status='OV' THEN 4
                    WHEN status='FV' THEN 5
                END)


Warehouse Database Design Question Apologies if this has been answered previously but I have been unable to get a specific model to work.

I have a database table that needs to store some very basic info for a reporting system. The information fed to me is Date - Pallets-in - Pallets-out - Pallet_Count - Man_Hours

The report when created needs to show Date - Pallets handled (Pallets-in + Pallets-out) - Man-Hours - Avg PLTS per hour - Overall Pallets in warehouse

EG yesterday report might shows 14-03-2015 PLT-Handled 400 (300 in 100 out) - Man-Hours 36 - Avg Plts p/Hour 11.11 - Overall Plts inW/House 7300 (day before there were 7100 so add 300 in and remove 100 out)

I currently just compose this report manually but I want to try and do it in a database so we have better historical reporting ability. Do i just have a table that stores every part or would it be better to calculate some parts in the report generation. (I am unsure how to mainly include the Total pallets in warehouse as it calculated from a starting figure then add plts in minus plts out.

Hope the above mmakes some sense

Regards

Paul

You are best to start with a transactional database that records the data in as much detail as you can get from the source.  You can always summarize and report from detailed data, but you can't go backwards from summary information to detailed information.

If you are getting data in the form:

  transaction_date  datetime
, pallets_taken_in  int
, pallets_sent_out  int
, total_work_effort decimal(19,2)

Then create a table with these columns.  If you get one record per day then make the transaction_date a unique index (or even primary key).

Calculating the values for your report is easy from these columns:

Pallets handled = pallets_taken_in + pallets_sent_out for a given transaction_date
Pallets/hour = (pallets_taken_in + pallets_sent_out) / total_work_effort for a given transaction_date
Pallets on hand = SUM(pallets_taken_in) - SUM(pallets_sent_out) over all records in your table. (i.e. over all dates)

Note that depending on how you key your table, you can also include physical inventory adjustment records to make allowance for correcting your balance on hand.

Depending on how much data you have and how much (and what kind of) reporting you need, it might make sense to pre-calculate some of these values and store them in a second table in a data warehouse type of scheme, but don't pre-calculate data unless you're pretty sure you need to.  Having two copies of the same data (raw transactions and data warehouse) means that you have potential discrepancies to manage.


Online rebuild consuming lot of log space why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched.
Details:

We have a table with primary key clustered index (bigint type) of
index size 7GB.
We have another non-clustered filtered index on same table (varchar(36) type) of index size nearly 1.5TB.
Online rebuild on primary key clustered index is consuming almost 1.6TB of transaction log size.
Options used for rebuild - (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON)
Another observation, the previous index was created without compression, not sure if that is what causing such a huge growth.
Could someone please shed some internals about it?

The clustered index is the table. It includes all columns. It is (basically) impossible for the clustered index to be smaller than any one non-clustered index. You probably looked at only the clustered key column size or only the non-leaf level when you read that 7GB size.
So if you rebuild the clustered index, then you rebuild all columns - including the compression setting. Rebuild of the clustered index in the end copies the data to a new location and after its done, it removes the data from the old location.
If you were on 2017, you could do resumable index rebuild, which allow you to pause the rebuild, empty the log and then resume the rebuild again.


Transfer microsecond timestamp into table using COPY I need to transfer a microsecond timestamp into a table by using the COPY command.

My approach using a normal INSERT, where X is a timestamp (since 1.1.1970) in microseconds, looked like this:

TO_TIMESTAMP( X ::double precision / 100000)

So, an X of 1484579365136360 would translate to  1/16/2017, 4:09:25.136360 PM.

How could I do the same with the COPY command?

I expect I need to convert the timestamp before issuing the COPY command, but in what format?

What you're trying to do is normally referred as an ETL (Extract, Transform, Load) process.

You want to do the transformation within the database. This is normally possible by using an intermediate table, where you store (as text, most probably) the data from STDIN via libpq and COPY.

COPY intermediate_table (orig_col1, orig_col2)
FROM stdin
WITH FORMAT CSV  ;  -- Choose your parameters

Then transfer it to the second table by means of

INSERT INTO
     dest(col1,                     col2)
SELECT
          f1(orig_col1, orig_col2), f2(orig_col1, orig_col2)
FROM
     intermediate_table ;

That is, you have some transforming functions on the original data to produce the data you want to store. For instance f1 could just be your TO_TIMESTAMP(orig_col_1 ::double precision / 100000).

After transforming the data from the intermediate table(s), it is common to truncate them (or drop them, and recreate them next time).


How to query cached plans that contain a specific missing index? I see a potential missing index for a table but would like to first query all cached plans that have that specific missing index (so I can later test those queries after the new index is built). How do you do build such a query?  Can sp_blitzcache help here?

This isn't foolproof by any means but as a rough sanity check you can use the following query that leverages the DMVs to filter on the cached plan text's table name and index name that you plan to index:
SELECT
    PlanText.[dbid] AS DatabaseId,
    DB_NAME(PlanText.[dbid]) AS DatabaseName,
    CachedPlans.plan_handle AS PlanHandle,
    OBJECT_NAME(PlanText.objectid) AS ObjectName,
    CachedPlans.objtype AS ObjectType,
    CachedPlans.refcounts AS CachedPlanReferenceCount,
    CachedPlans.usecounts AS CachedPlanUseCount,
    PlanText.[text] AS QueryText
FROM sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY sys.dm_exec_sql_text(CachedPlans.plan_handle) AS PlanText
WHERE PlanText.[text] LIKE '%YourTableName%'
    AND PlanText.[text] LIKE '%YourColumnToBeIndexed%'

This leverages the DMVs sys.dm_exec_cached_plans and sys.dm_exec_sql_text.
Just replace the wildcard filters above with your table name and index. This will give you the actual query that the cached plan is for, so then you can re-test it after you create the index. I also included some potentially helpful columns like DatabaseName, CachedPlanReferenceCount (how many objects reference this cached plan), and CachedPlanUseCount (how many times this plan was used). So you could theoretically add an ORDER BY CachedPlanUseCount DESC clause to get the most popularly used queries too and prioritize your list.
One reason why this isn't perfect is if the query in the QueryText column contains a different column coincidentally with the same name as the one you're indexing, but from a different table, and therefor will be a false positive match.


Keyword Search a single SQL Server table for multiple search terms and create a new table from results? I am not a database administrator. Most of my experience is with MySQL. However I think what I want to do is in the realm of possibility.

I want to text query a single large table in a SQL Server database for about 30 keyword terms. I then want the results to be dumped into a new table on the same database. I want the terms found to include entire field they were found in. (other items in row don't matter).

I am trying to produce data from the database to provide to a outside party. The table I want to search is to large to export to any format.

Is this possible?

Your search:

SELECT col
  FROM dbo.existing_table
  WHERE col LIKE '%keyword1%'
     OR col LIKE '%keyword2%'
     ...;

To put the results into a new table:

SELECT col
  INTO dbo.new_table_name
  FROM dbo.existing_table
  WHERE col LIKE '%keyword1%'
     OR col LIKE '%keyword2%'
     ...;


Is there any way you can tell MySQL to set value to 0 if value for unsigned column is < 0? I have a column in MySQL that is set as unsigned; however because I do calculations on this column within MySQL it's possible (though unlikely) that we could try and set this column to a value < 0.

In this case I just want it to set it to 0 rather than try and set it to a negative value and hence cause an error.

An example would be:

UPDATE my_table SET comments=comments-3 WHERE something=123;

Is there a simple way to do this?

Use the GREATEST() function (this needs a CAST() as well):

UPDATE my_table
SET comments = GREATEST(CAST(comments AS signed)-3, 0)
WHERE something = 123;

or the IF() function:

UPDATE my_table
SET comments = IF(comments>3, comments-3, 0)
WHERE something = 123;

or a CASE expression:

UPDATE my_table
SET comments = CASE WHEN comments>3 THEN comments-3 ELSE 0 END
WHERE something = 123;


Group by multiple columns, agregate others and select all in SQL Server I am using SQL server and I can't seem to construct the query I want.  I have a table with several columns, among them are

PARAMETER_NAME, GW_LOCATION_ID, Report_Result, DETECT_FLAG

I want a query to return

a row for each unique Parameter-Location combination
the maximum value of the Report_Result column for that unique Parameter-Location combination and
the DETECT_FLAG value associated with the maximum value.

There are other columns in addition to DETECT_FLAG that I want to return but I think that if I can get this part worked out, I should be able to return the other columns similarly.

The query works if I group by PARAMETER_NAME and GW_LOCATION_ID and aggregate the Report_Result column (see below).  However, when I add the DETECT_FLAG column, I get the error, "Column 'SLVs_Flagged.DETECT_FLAG' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."  What I want is the value of DETECT_FLAG for the row returned by MAX(Report_Result).

SELECT
PARAMETER_NAME, GW_LOCATION_ID, MAX(Report_Result)
FROM SLVs_Flagged
GROUP BY PARAMETER_NAME, GW_LOCATION_ID
ORDER BY PARAMETER_NAME, GW_LOCATION_ID;

I have tried to do a subselect to return the DETECT_FLAG value that corresponds to MAX(Report_Result) but I try to use a WHERE condition and I get another error.  Please advise how I can execute this query.

Here is a subset of the data for testing.

PARAMETER_NAME  GW_LOCATION_ID  Report_Result   DETECT_FLAG
Perchlorate CDBO-6  2.38    N
Perchlorate CDBO-6  1.45    N
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02655    0.537   Y
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02656    100 N
Perchlorate CDV-16-02656    0.394   Y
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    0.174   Y
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02658    4   Y
Perchlorate CDV-16-02658    0.126   Y
Perchlorate CDV-16-02658    0.0561  Y
Perchlorate CDV-16-02658    20  N
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02659    4   N
Nitrate as Nitrogen R-16 S4 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-27    0.003   N
Nitrate as Nitrogen R-31 S2 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen Test Well 1A    0.01    N
Nitrate as Nitrogen Test Well 1A    -0.01   N
Nitrate as Nitrogen Test Well 2 0.04    N
Nitrate as Nitrogen Test Well 2 0.01    N
Nitrate as Nitrogen Test Well 2A    0   N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N

This is a greatest-n-per-group problem and there are many ways to solve it (CROSS APPLY, window functions, subquery with GROUP BY, etc). Here's a method using window functions and a CTE:

WITH ct AS
  ( SELECT *,
           rn = RANK() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                             ORDER BY Report_Result DESC)
    FROM SLVs_Flagged
  )
SELECT PARAMETER_NAME, GW_LOCATION_ID,
       Max_Report_Result = Report_Result,
       DETECT_FLAG
       -- more columns
FROM ct
WHERE rn = 1
ORDER BY PARAMETER_NAME, GW_LOCATION_ID ;

The query will return all tied results (if there are ties). If you want a single result for every (PARAMETER_NAME, GW_LOCATION_ID) combination, you can resolve ties by using ROW_NUMBER() instead of RANK() and modifying the ORDER BY inside the OVER (..) clause. Eg. (prefer DETECT_FLAG with N over Y):

           rn = ROW_NUMBER() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                                   ORDER BY Report_Result DESC, DETECT_FLAG)


SQL - query multiplying records returned - Cartesian Product I am using the following query which is returning a huge number of multiplied records - i.e. a cartesian product:

SELECT * FROM specimen, topography_index, morphology, functions
WHERE
SUBSTRING(specimen.topography_index, 2, 2) = topography_index.topography_index_code
AND
if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code)
AND
specimen.topography_index = '_ORGAN_'
ORDER BY
(specimen.room = 'f') DESC,specimen.snop_code

The number of records being returned is around 59,000.  However, specimen table only has 469 records.  The morphology and functions tables have around 2000 records each, and the topography_index table has 29 records, so I'm guessing that the results are being multiplied by the number of records in 'topography_index' and 'morphology' or 'functions' tables (29 x 2000), whichever one that doesn't have a join in the if clause.

How should the query be written so that this doesn't happen?

Note that GROUP BY is NOT an option as the query is so slow with all the records being returned.

http://sqlfiddle.com/#!2/2bda8/1

Perhaps this could be handled by a sub select statement?

Any help appreciated!

Your cartesian product should be the product of # of rows in properly joined tables times the rows in tables that are not properly joined (i.e. don't have effective condition in the WHERE clause). For cases where specimen.snop_axis = 'M' morphology and specimen are propely joined but functions isn't, and when specimen.snop_axis <> 'M' functions and specimen are properly joined but morphology isn't. That's most likely why the resulting number of rows doesn't seem to be any direct product of rows in the tables.

To solve this I'd first try to join all the tables properly by replacing

AND if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code)

with

AND functions.functions_code = specimen.snop_code
AND morphology.morphology_code = specimen.snop_code

To me, this seems to give ok result with the sample data, but I wouldn't be too sure of how it behaves with your real data.

I'm not completely sure what you are trying to obtain by having that IF condition in WHERE clause. If the objective is to return morphology data and no functions data when specimen.snop_axis='M' and in other cases to return functions data and no morphology data, I'd suggest having the selection logic applied in the columns that are returned, somehow like this:

SELECT
IF(specimen.snop_axis = 'M', morphology_pk, functions_pk) matching_pk,
IF(specimen.snop_axis = 'M', morphology_code, functions_code) matching_code,
IF(specimen.snop_axis = 'M', morphology_nomen, functions_nomen) matching_nomen,
specimen.*,
topography_index.*

instead of just having columns from both tables returned always. But as I said, I'm not quite sure I understand what is required.


DBCC CHECKIDENT ('table name', RESEED, 20000) not working When I run the script

DBCC CHECKIDENT ('table name', RESEED, 20000)

the output of the message is

  Checking identity information: current identity value '200000', current
   column value '200000'. DBCC Execution completed. If DBCC printed error
   messages, contact your system administrator.

When I query the identity value by running

SELECT IDENT_CURRENT('table_name')

it is showing up as 200000 as expected, however, when the developer inserts records into the table, it's not using the new reseeded value.

Is there something obvious I'm missing?

It turns out after multiple conversations with the developers of this application using the table that there was a 'hidden' stored procedure that was grabbing the maximum value of the ID column in the table and adding an arbitrary number to it then issuing an IDENTITY_INSERT statement resulting in the newly reseeded value.


How do I turn off ALTER DATABASE script output in VS 2012? I am using SQL Server 2005 in conjunction with Visual Studio 2012 and a SSDT database project.

When I publish my VS project and generate the script to run against my database, VS includes the following in the script.  I really don't want the script to go changing database properties, particularly the PAGE_VERIFY setting.  I notice that the script doesn't set the properties back the way it found them.  How do I turn this off?  I have spent time in the Tools->Options dialog and I just don't see the setting.

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL
        WITH ROLLBACK IMMEDIATE;

END
GO

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET PAGE_VERIFY NONE
        WITH ROLLBACK IMMEDIATE;
END
GO

I am using Visual Studio 2010 and SQL 2008 database projects, but the idea remains the same.
In your solution go to the project and do the following steps:

right click the project -> Properties
Project Settings (1st tab in this setting)
go to section Catalog Properties file: make sure a settings file is already selected (by default there's a file \Properties\Database.sqlsettings if it wasn't previously changed)
click Edit for the sqlsettings file: there you'll see all the options that are deployed each time to your target database. It should look like the following snap:

If you'd like to NOT deploy any of the project default settings, you should do the following steps:

go to tab Deploy
go to section Deployment configuration file (default it is Properties\Database.sqldeployment)
deselect the option "Deploy database properties". It should look something like the following snap:

That should help you change/remove the current project settings that you're now deploying.


Eliminating duplicate records in data cleansing I have a database full of records of people with simple information like first name, last name, email, location, ... . I need to eliminate the duplicate records. As I've search the process is called "duplicate elimination in Data Cleansing". Does anyone know a good open source tool to do that?

Using T-SQL & PL/SQL, you can use ROW_NUMBER() OVER (PARTITION BY) to find and delete duplicate records in a Table.

Refer to some best examples Removing Duplicates from a Table in SQL Server
and How can I remove duplicate rows? as well as Oracle 'Partition By' and 'Row_Number' keyword


Oracle Database 12c: TNS:permission Denied (CentOS) (This post has a similar title to another post but the underlying cause and solution are totally different.)

Problem:

I can't login to sqlplus as sysdba but the listener seems to start

[John@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-AUG-2015 18:19:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /home/John/app/John/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /home/John/app/John/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/John/app/John/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                13-AUG-2015 18:19:03
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/John/app/John/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/John/app/John/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

The listener seems to start but TNS listener rejects the sysdba login.

[John@localhost ~]$  sqlplus -prelim / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 13 18:25:49 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: ^C
[John@localhost ~]$

Possible causes:

I was able to login a few days before this happened. The only thing I can think of that could have caused the issue is that I changed the all files and folders that were owned by John:John to John:Users and then made Users the primary group of John. I don't know why that would matter though. If it does matter it certainly shouldn't.

How would I be get more detailed information as to why / as sysdba is suddenly rejected by the TNS listener? If it is clearly related to the group change then what specific changes need to be made to leave the group setup how it is while correcting the authentication error?

This syntax does not use the listener at all:

sqlplus -prelim / as sysdba

But you faced an authentication error, because the OS group based SYSDBA authentication failed. Check the below:

cat $ORACLE_HOME/rdbms/lib/config.c

There you should see a line like this:

#define SS_DBA_GRP "John"

Only those users can use / as sysdba that are members of the OS group defined above. If you installed the database software with the OSDBA group set to John, and changed your group to Users, then you will not be able to use / as sysdba.

If you want to change the OSDBA group to your new group, edit the above file, set the desired value, shutdown everything running from the ORACLE_HOME (database, listener), then run

$ORACLE_HOME/bin/relink all