Why is it considered that a set unnormalizes a database absolutely? In order for a relationship to be on 1NF it needs to have all values as atomic, if there's a set it's not even in that first normal form:

But intuitively I think that a table with that set would be more normalized than one that is not as long as the values of that set are just used as attributes of the entity.

For example, let's imagine this table about paintings:

Painting_name, Author, Used technique, Used colors

Now if we get to use a set of colors like {blue, Green, yellow, black, White, purple} we get a table that is not even in 1NF.

If we pass the table to 1NF, then we need to have 6 rows each one repeating the Painting_name, Author, and Used technique.

This looks way more unnormalized than the table that is not even in 1NF, and I don't see why having a set there would harm any possible normalization, as that sets would only be used in that table.

What is then the reason that atomic values are needed in order to have a normalized table?

The reference for this post is an amazing book called Database System Concepts 6th Edition which I recommend you read.

In the book, page 328, it states:

  A domain is atomic if elements of the domain are considered to be indivisible units. We say that a relation schema R is in first normal form (1NF) if
  the domains of all attributes of R are atomic.

You might be wondering "but why!?", it's best explained using a practical example.

Let's look at your example with colors. Lets say we have 2 scenarios, 1.) where the table is not in 1NF, 2.) where the table is in 1NF.

1.)

Id          Painting_name                  Author                         Used_colors
----------- ------------------------------ ------------------------------ ---------------
1           Some_Painting                  John                           Blue, red, Yellow
2           Monalisa                       Leonardo da Vinci              orange, black, White, red, Yellow

Although this might seem intuitive to you, consider what happens when you want to query this table. One, you have inconsistent upper and lower case (which you would have to check with your query for both cases) two, if used_colors isn't an array, you would either have to convert it to an array or use extra steps just to check the data you need (like using a string_split function in SQL Server 2014 and later).

This causes performance issues and ends up being a hassle every time you want to check for something. If you are wondering what stops a person from typing in black_white_yellow instead? That question is answered in 2NF and 3NF, foreign key constraints etc...

2.)

Id          Painting_name                  Author                         Used_colors
----------- ------------------------------ ------------------------------ ---------------
3           Some_Painting                  John                           Blue
4           Some_Painting                  John                           red
5           Some_Painting                  John                           Yellow
6           Monalisa                       Leonardo da Vinci              orange
7           Monalisa                       Leonardo da Vinci              black
8           Monalisa                       Leonardo da Vinci              White
9           Monalisa                       Leonardo da Vinci              red
10          Monalisa                       Leonardo da Vinci              Yellow

In this case, every single row is atomic and unique. What did we gain by doing this? You don't have to think about handling arrays, since every single row now is atomic you can clearly check for what you need quickly and efficiently.

And just to give you an idea of how big of a hassle it could become, here are some posts related to the issue of searching for values in a comma separated column:

How can I check whether a number is contained in a comma separated
list
Using Comma Separated String Value in a Select Statement

There are also many different approaches, none of which are really elegant to say the least (at least pertaining to the problem of 1NF). So, basically by using the 1NF you go from using code like the ones mentioned in those posts above to simple stuff like:

SELECT * FROM Paintings WHERE Used_colors LIKE 'BLUE'

Which helps in both readability and performance.

There is one thing you need to keep in mind, 1NF is only the starting point of the normalization process. The 1NF alone is practically never used in any DB out there, after the 1NF comes the 2NF where you would have to split this table into two separate tables. Used_colors will be made into its own table called Colors. At this point you will reach the cardinality issue which is also covered in the book mentioned above.

One last thing, there are many cases where you're going to come across databases that break the 1NF on one or more tables while adhering to the rules of 2NF, 3NF and even 4NF. For example PostgreSQL has json data type which immediately breaks the 1NF rule (as you can save multiple keys and values in json). The general rule of thumb goes like this: Always follow the normal forms unless you really know what you're doing. Since the moment you introduce such variables you could cause inconsistencies throughout the database and you will most likely lose on performance.

Also, as Paul said in the comment below, there is another point of view which Christopher. J. Date supports(who is a famous researcher and writer on relational DB theory as well as being one of the people with Ted Codd who helped push the relational model). This outlook stabs at the idea of atomic values in 1NF, saying that the whole term "atomic" is ambiguous. The idea behind this is simple, you can say that almost no data type is atomic under the current definition of 1NF. To explain this, lets take a look at an example:

Lets say you have a string Hello World. You have functions in every DBMS that decomposes this string into smaller chunks (like the SUBSTRING or LEFT / RIGHT functions) which means that string isn't really an atomic value as everything can be decomposed. This outlook is similar to data types such as json, you can decompose both string and json, so why is one considered atomic while the other not? That's why C. J. Date considers the current definition of 1NF as ambiguous, since almost all data types can be decomposed in one way or another. If you access json or xml data types as a whole unit without decomposing them, there is nothing to say that json or xml data type isn't atomic.

You can find an interesting paper on The Third Manifesto, they (C. J. Date and Hugh Darwin) made their paper on Databases, Types and the Relational Model The Third Manifesto publicly available. It's also an interesting read which will send you to interesting other articles and topics in general.


SQL database backups methods-tools-procedures Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having and if anyone has any recommendations.

Database 1: 80GB
Database 2: 74GB
Database 3: 5GB
Database 4: 30GB

Database 1 & 2 are still currently SQL Server 2005 with no plans on upgrading them right now

Database 3 & 4 are for new systems and will be growing rapidly over the next several months. Currently Mirrored.

The problem we are running into is that there are maintenance tasks on the servers that run for these databases for backups that take roughly 30 min for Database 1 & 2. Now these all run at the same time as several other environment backups that the Sys Eng have running.

After the backup, a schedules task using 7-zip is run to compress the backups. All of this is killing the CPU during that time frame and if for whatever reason a job/task gets locked up, it will affect the entire system.

Now due to the business we are in, we have a very limited timeframe we can run these backup jobs, and these backups need to be done. We also need to start encrypting the backups after the compression to further our HIPPA compliance... yep...

Another part of the problem that we are are aware of is we have 20 other databases doing the same thing at the same time, which is killing the SAN, all of which we know and believe don't need these backup tasks.

So aside from that which we are working on, we are trying new methods of backing up databases to try to take the CPU load off the database servers as much as possible and hopefully do the backups for the databases incrementally.

Any suggestions? Tools, methods? We've tested Red-Gates SQL backup pro tool, SQL backupandftp freeware and using SSIS but none have seemed to accomplish what we were hoping for (ability to quickly accomplish the task and/or run on another server taking the CPU off the DB server).

The first thing I generally recommend doing is introducing differential backups to the backup strategy. If the amount of data churn is moderate or low, this can work extremely well for saving space and time because you only back up changes rather than everything. This does add some complexity to restore scenarios, and you must make sure you know what COPY_ONLY backups are.

The second thing is leveraging native backup compression. SQL Server 2008 Enterprise and SQL Server 2008 R2 Standard editions support backup compression out of the box -- no need for 3rd-party tools. If you aren't taking advantage of this, you're really missing out. It is off by default for backwards compatibility. You can turn it on server-wide by default in the Server Properties | Database Settings page, or per-backup using the WITH COMPRESSION option. This will still have a small CPU hit, but the cost will be amortized over the duration of the backup instead of all at once (assuming backups are I/O bound, which they usually are).

Some other random thoughts that may or may not apply to your environment: stagger the backups to reduce I/O contention; back up to multiple locations simultaneously; back up to LUN(s) dedicated to backups; tweak the backup buffer settings.

For the 2005 backups, what you can consider doing is limiting the amount of CPU used by the compression utility by using Windows Server Resource Manager. Or, since the file has to be written somewhere before that step happens anyways, you could offload that process to another box.

Encrypting backups is not something I'm too familiar with, but it will depend on how you plan to encrypt the data and backups. If you use Transparent Data Encryption in SQL Server, you'll likely want to turn off backup compression because there won't be anything to gain because of the randomness of the encryption. If you use a 3rd-party encryption strategy, see the previous point about using WSRM or offloading the process to a different box, because encryption will also require CPU.


How to make a single row result query to return multiple rows I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography.

I also have a given route as a Linestring with the SRID 4326.

In the SQL statement I fetch 1 Point from the station and search the closest point in my routing network, but I want to do this for all stations from the WITH Query

WITH stations AS (SELECT id, geom FROM de_tt_stations AS s
  WHERE ST_DWithin(s.geom::geography, ST_FromEWKB(%(route)s)::geography, 1000))
SELECT 1378549, destination
FROM (
  SELECT id::integer AS destination
  FROM de_2po_vertex ORDER BY geom_vertex <-> (
    SELECT geom FROM stations LIMIT 1)
  LIMIT 1)
AS foo

From the resulting list then I want to calculate the length of each route so that I can choose the closest filling station. Optimal output would look like

start   | destination      | station_id  | length
--------------------------------------------------------------
1378549 | de_2po_vertex.id | stations.id | SUM(pgr_dijkstra())
...

I'm using PostgreSQL 9.5, Postgis 2.1.5 and pgrouting 2.0.0

Table definitions:

public.de_2po_4pgr
(
  id integer NOT NULL,
  osm_id bigint,
  osm_name character varying,
  osm_meta character varying,
  osm_source_id bigint,
  osm_target_id bigint,
  clazz integer,
  flags integer,
  source integer,
  target integer,
  km double precision,
  kmh integer,
  cost double precision,
  reverse_cost double precision,
  x1 double precision,
  y1 double precision,
  x2 double precision,
  y2 double precision,
  geom_way geometry(LineString,4326),
  CONSTRAINT pkey_de_2po_4pgr PRIMARY KEY (id)
)

public.de_2po_vertex
(
  id integer NOT NULL,
  clazz integer,
  osm_id bigint,
  osm_name character varying,
  ref_count integer,
  restrictions character varying,
  geom_vertex geometry(Point,4326),
  CONSTRAINT pkey_de_2po_vertex PRIMARY KEY (id)
)

public.de_tt_stations
(
  id character varying(255) NOT NULL,
  name character varying(255),
  brand character varying(255),
  street character varying(255),
  "number" character varying(255),
  zip character varying(255),
  city character varying(255),
  premium_e5_time timestamp without time zone,
  premium_e5_value double precision,
  premium_e10_time timestamp without time zone,
  premium_e10_value double precision,
  diesel_time timestamp without time zone,
  diesel_value double precision,
  holiday_identifier character varying(255),
  opening_times text,
  opening_times_extended text,
  override_opening_times text,
  geom geometry(Point,4326),
  CONSTRAINT de_tt_stations_id_pkey PRIMARY KEY (id)
)

A possible Solution to the Problem I found working is a step-by-step solution which looks like this:

BEGIN;

CREATE TEMP TABLE route ON COMMIT DROP AS
  SELECT seq, source, target, km, kmh, clazz, geom_way
  FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex
    WHERE id = 1362258 OR id = 1625523 LIMIT 1) as box WHERE geom_way && box.box',
    1362258, 1625523, FALSE, FALSE) AS route
  LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id
  ORDER BY seq;

CREATE TEMP TABLE filling (start integer, destination integer, station_id character varying(255), distance double precision) ON COMMIT DROP;
INSERT INTO filling (start, station_id)
  SELECT 1378549, id FROM de_tt_stations AS s WHERE ST_DWithin(s.geom::geography, (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route), 1000);
  UPDATE filling SET destination =
    (SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <->
      (SELECT geom FROM de_tt_stations WHERE id = filling.station_id)
    LIMIT 1);
WITH f AS (SELECT start, destination FROM filling)
UPDATE filling SET distance = (SELECT SUM(km) AS distance FROM (
  SELECT km FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr,
    (SELECT ST_Expand(ST_Extent(geom_vertex),0.05) as box FROM de_2po_vertex WHERE id = '|| filling.start ||' OR id = '|| filling.destination ||' LIMIT 1) as box
     WHERE geom_way && box.box', filling.start, filling.destination, FALSE, FALSE) AS route
     LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id) as dist);
SELECT * FROM filling ORDER BY distance;

COMMIT;

Execution Time is fair with around 500ms and less. Is there any possibility to further optimize the Querys?


Harden a public login to only let it retrieve a list of other logins, and do nothing else I'd like my app's login dialog to give users a drop-down-list of login names on the server. So I'm wondering about the most secure way to retrieve this.

The best I can come up with is a login with a hardcoded password that can only access a stored procedure that'll return a list of other logins. What's the best way to harden this? I would love to create a login trigger that'll return what I want and then force the connection to close, or a trigger that will run for all statements, but this doesn't look possible..

Thanks!

"Most secure" is an ambiguous statement that needs more explanation. You need to think about what kind of attack vectors you are trying to mitigate.

For example, what if a malicious user trys to brute-force your built-in account? It's going to be locked out. This creates a denial of service scenario, as normal users cannot receive a list of valid accounts either. If you were trying to mitigate a DoS scenario by using hard-to-guess logon names, you just shoot your own foot.

Hard-coding a password in your application will make it insecure. First, it can be extracted from the application, no matter how you encrypt it. That's exactly the way Blu-Ray encryption was broken. Second, should you need to change the password, you need to ship new binaries to all the users which is somewhat a hassle.

How about creating a SSIS package that publishes a list of valid logins on a network share? This moves some security configuration from the database to your AD group membership maintenance.


How to create trigger to update timestamp when record is updated or inserted in another table I have a table t_task and a table t_taskaction with a foreign key to t_task.
I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in action.

It doesn't get any udpate in t_task table, and I'm not getting any error.

USE `myTask`;
DELIMITER $$
CREATE TRIGGER `t_taskaction_AUPD` AFTER UPDATE ON `t_taskaction` FOR EACH ROW
BEGIN
    DECLARE idTask INT(11);
    DECLARE idTaskState INT(11);

    set @idTask = NEW.idTask;

    set @idTask = (SELECT idTaskState
    FROM t_task WHERE idTask = @idTask);

    UPDATE t_task
    SET idTaskState = @idTaskState
    WHERE idTask = @idTask;
END$$
DELIMITER ;

Tables

CREATE TABLE IF NOT EXISTS `myTask`.`t_task` (
  `idTask` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `task` VARCHAR(100) NOT NULL,
  `idTaskState' INT(11) UNSIGNED NOT NULL DEFAULT 1,
  `description` LONGTEXT NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTask`)
)

CREATE TABLE IF NOT EXISTS `myTask`.`t_taskAction` (
  `idTaskAction` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idTask` INT(11) NOT NULL,
  `fecha` DATETIME NOT NULL,
  `action` VARCHAR(45) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTaskAction`, `idTask`),
  CONSTRAINT `t_ifocTareaAccion2t_ifocTarea`
    FOREIGN KEY (`idTaskAction`)
    REFERENCES `myTask`.`t_task` (`idTask`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

CREATE TABLE IF NOT EXISTS `myTask`.`a_taskState` (
  `idTaskState` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order` INT(11) NULL DEFAULT NULL,
  `state` VARCHAR(45) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTaskState`))

Thanks Michael for your help!

Good to know that @variables are users variables and without @ are local variables.

The following snipped worked well

USE `myTask`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `t_taskaction_AUPD`
AFTER UPDATE ON `t_taskaction`
FOR EACH ROW
BEGIN
    DECLARE _idTask INT(11);
    DECLARE _idTaskState INT(11);

    set _idTask = NEW.idTask;

    set _idTask = (SELECT idTaskState
    FROM t_task WHERE idTask = _idTask);

    UPDATE t_task
    SET timestamp = now()
    WHERE idTask = _idTask;
END$$
DELIMITER ;


Type conversion in expression may affect "CardinalityEstimate" in query plan choice? I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data.

We constraint the events on each table with a check constraint on the datetime column. This allows the optimizer to limit the tables that are searched for queries that filter on the event datetime column.

The names of the check constraints were generated by SQL Server, so it's hard to know what they do by looking at their name.

I want the constraint names to have the form 'CK_TableName_Partition'.

I can generate a rename script using this query and copying data from from sql_text column. The WHERE clause matches check constraints whose names look like they were generated by SQL Server:

SELECT
  checks.name AS check_name,
  tabs.name AS table_name,
  skemas.name AS schema_name,
  cols.name AS column_name,
  N'
EXECUTE sys.sp_rename
  @objname = N''' + skemas.name + N'.' + checks.name + N''',
  @newname = N''CK_' + tabs.name + N'_Partition'',
  @objtype = ''OBJECT'';' AS sql_text
FROM sys.check_constraints AS checks
INNER JOIN sys.tables AS tabs ON
  tabs.object_id = checks.parent_object_id
INNER JOIN sys.schemas AS skemas ON
  skemas.schema_id = tabs.schema_id
INNER JOIN sys.columns AS cols ON
  tabs.object_id = cols.object_id AND
  cols.column_id = checks.parent_column_id
WHERE checks.name LIKE (
  N'CK__' + SUBSTRING(tabs.name, 1, 9) +
  N'__' + SUBSTRING(cols.name, 1, 5) +
  N'__' + REPLACE(N'xxxxxxxx', N'x', N'[0-9A-F]') COLLATE Latin1_General_BIN2
)
ORDER BY table_name;

The output looks like this:

check_name  table_name  schema_name column_name sql_text
CK__tbAcquisi__Acqui__5C4299A5  tbAcquisitions_201301   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__5C4299A5',  @newname = N'CK_tbAcquisitions_201301_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__76026BA8  tbAcquisitions_201302   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__76026BA8',  @newname = N'CK_tbAcquisitions_201302_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__7D6E8346  tbAcquisitions_201303   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__7D6E8346',  @newname = N'CK_tbAcquisitions_201303_Partition',  @objtype = 'OBJECT';
...
CK__tbRequest__Reque__60132A89  tbRequests_201301   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__60132A89',  @newname = N'CK_tbRequests_201301_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1392CE8F  tbRequests_201302   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1392CE8F',  @newname = N'CK_tbRequests_201302_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1AFEE62D  tbRequests_201303   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1AFEE62D',  @newname = N'CK_tbRequests_201303_Partition',  @objtype = 'OBJECT';

The result of the query seems to be correct and the server executes it quickly.

But the root node of the execution plan has a warning:

  Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

The result of the query seems to be correct and the server executes it quickly.
But the root node of the execution plan has a warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

The warning is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.
The warning is triggered by the implicit conversion used for the collation change. If using the collation is the easiest way to get correct results, feel free to leave it as it is. Alternatively, if you explain more about why it is needed, someone will advise you.
As an aside, the REPLACE could be replaced with:
REPLICATE(N'[0-9A-F]', 8);

(This answer is a summary of the comments to the question.)


How can I lock down my own database I collect some personal information from users of my website and according to German law, the storage of this data is only allowed with consent of the user. I programmed my site in a way, that the users needs to enter the data via a form, and confirm it via a double opt-in which is send to his email address. So far everything is still compliant with the law.
But the law also requires from me, that I am able, any time, to prove that the user is really the person who made the entry.
The double opt-in should be enough, but as I have access to my database, could make entries too. So the question is, how to turn off my own writing rights without being able to get them back. (And how can I prove that I did not have write access ...)

Option 1: Use a different account which have only sufficient permission. Don't use DBA rights account until that is required.

Option 2 : enable the auditing to track all the changes in databases and made by whom. I hope this will full fill the compliance requirement.


How to get efficient range index scans with multiple columns with different sort order? Let's say we have this table with a million rows:

CREATE TABLE stuff(
    foo integer,
    bar integer
);

INSERT INTO stuff(foo, bar) SELECT
    (random()*1000000) :: integer,
    (random()*1000000) :: integer
FROM generate_series(1, 1000000);

CREATE INDEX index1 ON stuff(foo ASC, bar ASC);
CREATE INDEX index2 ON stuff(foo ASC, bar DESC);

VACUUM ANALYZE stuff;

This table backs an API endpoint where the user can query the data specifying a custom sort order, possibly on multiple columns, and fetch the data in pages of 100. When fetching a page, the user gets a cursor that encodes the necessary data to get the next page.

When fetching rows with ORDER BY foo ASC, bar ASC, the first query would be:

postgres=# EXPLAIN ANALYZE SELECT * FROM stuff ORDER BY foo ASC, bar ASC LIMIT 100;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.79 rows=100 width=8) (actual time=0.104..3.611 rows=100 loops=1)
   ->  Index Only Scan using index1 on stuff  (cost=0.42..43679.92 rows=1000000 width=8) (actual time=0.068..1.297 rows=100 loops=1)
         Heap Fetches: 100
 Planning Time: 0.150 ms
 Execution Time: 4.915 ms
(5 rows)

and a query with a cursor pointing to the middle of the table would be:

postgres=# EXPLAIN ANALYZE SELECT * FROM stuff WHERE (foo, bar) > (543210, 1234) ORDER BY foo ASC, bar ASC LIMIT 100;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..7.14 rows=100 width=8) (actual time=0.142..3.625 rows=100 loops=1)
   ->  Index Only Scan using index1 on stuff  (cost=0.42..30730.05 rows=457498 width=8) (actual time=0.115..1.279 rows=100 loops=1)
         Index Cond: (ROW(foo, bar) > ROW(543210, 1234))
         Heap Fetches: 100
 Planning Time: 0.325 ms
 Execution Time: 4.857 ms
(6 rows)

This query is very fast because the optimizer knows how to use the WHERE clause to "jump" into the middle of the index, and only fetch 100 rows from there.

Now, if the user wants to sort by foo ASC, bar DESC, I can't think of any equivalent for WHERE (foo, bar) > (543210, 1234). We have to write the query like this:

postgres=# EXPLAIN ANALYZE SELECT * FROM stuff WHERE foo > 543210 OR (foo = 543210 AND bar < 1234) ORDER BY foo ASC, bar DESC LIMIT 100;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..11.61 rows=100 width=8) (actual time=226.836..230.294 rows=100 loops=1)
   ->  Index Only Scan using index2 on stuff  (cost=0.42..51179.92 rows=457498 width=8) (actual time=226.813..227.959 rows=100 loops=1)
         Filter: ((foo > 543210) OR ((foo = 543210) AND (bar < 1234)))
         Rows Removed by Filter: 543630
         Heap Fetches: 543730
 Planning Time: 0.275 ms
 Execution Time: 231.521 ms
(7 rows)

Now the query is too slow! Apparently, postgres isn't smart enough to figure out that it can "jump" into the middle of the index, and does a full index scan instead.

Is there any way to make the second query fast, like the first one? It seems to me that it should be theoretically possible.

This is a generic (and slightly academic) answer that shows how this problem can be solved in general by defining your own type and operator class.

I'll use strings (type text) as an example.

First, we have to define our own type invtext exactly like text:

/* create a shell type */
CREATE TYPE invtext;

/* copy type functions from "text" */
CREATE FUNCTION invtextin(cstring) RETURNS invtext
   LANGUAGE internal IMMUTABLE PARALLEL SAFE
   AS 'textin';

CREATE FUNCTION invtextout(invtext) RETURNS cstring
   LANGUAGE internal IMMUTABLE PARALLEL SAFE
   AS 'textout';

CREATE FUNCTION invtextrecv(internal) RETURNS invtext
   LANGUAGE internal STABLE PARALLEL SAFE
   AS 'textrecv';

CREATE FUNCTION invtextsend(invtext) RETURNS bytea
   LANGUAGE internal STABLE PARALLEL SAFE
   AS 'textsend';

/* create type like "text" */
CREATE TYPE invtext (
    INPUT = invtextin,
    OUTPUT = invtextout,
    RECEIVE = invtextrecv,
    SEND = invtextsend,
    LIKE = text,
    CATEGORY = 'S',
    DELIMITER = ',',
    COLLATABLE = TRUE
);

We need casts to and from text:

CREATE CAST (text AS invtext) WITHOUT FUNCTION;
CREATE CAST (invtext AS text) WITHOUT FUNCTION AS IMPLICIT;

We use the same comparison functions as text, except we turn around the order:

CREATE FUNCTION invtexteq(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE LEAKPROOF STRICT
   AS 'texteq';

CREATE FUNCTION invtextne(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE LEAKPROOF STRICT
   AS 'textne';

CREATE FUNCTION invtext_lt(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_gt';

CREATE FUNCTION invtext_gt(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_lt';

CREATE FUNCTION invtext_le(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_ge';

CREATE FUNCTION invtext_ge(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_le';

Now we can define the corresponding operators:

CREATE OPERATOR = (
   FUNCTION = invtexteq,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "=",
   NEGATOR = "<>",
   RESTRICT = eqsel,
   JOIN = eqjoinsel,
   HASHES,
   MERGES
);

CREATE OPERATOR <> (
   FUNCTION = invtextne,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "<>",
   NEGATOR = "=",
   RESTRICT = neqsel,
   JOIN = neqjoinsel
);

CREATE OPERATOR < (
   FUNCTION = invtext_lt,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = ">",
   NEGATOR = ">=",
   RESTRICT = scalargtsel,
   JOIN = scalargtjoinsel
);

CREATE OPERATOR > (
   FUNCTION = invtext_gt,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "<",
   NEGATOR = "<=",
   RESTRICT = scalarltsel,
   JOIN = scalarltjoinsel
);

CREATE OPERATOR <= (
   FUNCTION = invtext_le,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = ">=",
   NEGATOR = ">",
   RESTRICT = scalargesel,
   JOIN = scalargejoinsel
);

CREATE OPERATOR >= (
   FUNCTION = invtext_ge,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "<=",
   NEGATOR = "<",
   RESTRICT = scalarlesel,
   JOIN = scalarlejoinsel
);

The required B-tree support function is just the opposite of the one from text:

CREATE FUNCTION btinvtextcmp(invtext, invtext) RETURNS integer
   LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
   AS 'SELECT bttextcmp($2, $1)';

Now we can define a default operator class for invtext:

CREATE OPERATOR CLASS invtext_ops
   DEFAULT FOR TYPE invtext USING btree FAMILY text_ops AS
      OPERATOR 1 <,
      OPERATOR 2 <=,
      OPERATOR 3 =,
      OPERATOR 4 >=,
      OPERATOR 5 >,
      FUNCTION 1 btinvtextcmp(invtext, invtext);

Done! Let's test how it works.

Let's create a test table:

CREATE TABLE stuff(
    foo text,
    bar text
);

INSERT INTO stuff(foo, bar) SELECT
    substr(md5((random()*1000000)::text), 4, 4),
    substr(md5((random()*1000000)::text), 4, 4)
FROM generate_series(1, 1000000);

CREATE INDEX index1 ON stuff(foo ASC, (bar::invtext) ASC);

VACUUM ANALYZE stuff;

Sort foo in ascending order and bar in descending order (which is ascending order for invtext):

SELECT * FROM stuff
WHERE (foo, (bar::invtext)) > ('f000', '8000')
ORDER BY foo ASC, (bar::invtext) ASC
LIMIT 100;

 foo  | bar
------+------
 f000 | 75ab
 f000 | 6d17
 f000 | 6c78
 f000 | 51ed
 f000 | 2d84
 f000 | 2227
 f000 | 0f72
 f001 | f0bf
 f001 | eae8
 f001 | d56a
 f001 | c6dd
 f001 | ba4e
 f001 | 9606
 f001 | 8e2a
 f001 | 7d58
 f001 | 7aed
 f001 | 719c
 f001 | 5ce9
 f001 | 354a
 f001 | 137d
 f001 | 107d
 f002 | ee18
 f002 | e7a8
...

Test if the index is used:

EXPLAIN (COSTS off)
SELECT * FROM stuff
WHERE (foo, (bar::invtext)) > ('f000', '8000')
ORDER BY foo ASC, (bar::invtext) ASC
LIMIT 100;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Limit
   ->  Index Scan using index1 on stuff
         Index Cond: (ROW(foo, (bar)::invtext) > ROW('f'::text, '1'::invtext))
(3 rows)


How do I force a JOIN to use a specific index in MySQL? I have a query that JOINs 2 tables; lineitem and part,

select
        sum(l_extendedprice* (1 - l_discount)) as revenue
from
        lineitem force index for join (l_pk),
        part
where
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#12'
                and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
                and l_quantity >= 1 and l_quantity <= 1 + 10
                and p_size between 1 and 5
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        )
        or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#23'
                and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
                and l_quantity >= 10 and l_quantity <= 10 + 10
                and p_size between 1 and 10
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        )
        or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#34'
                and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
                and l_quantity >= 20 and l_quantity <= 20 + 10
                and p_size between 1 and 15
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        );

I have an index on lineitem and want the query to use this index for the join p_partkey = l_partkey

create index l_pk on tpch.lineitem(l_partkey);

MySQL explain shows:

| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows    | Extra       |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+
|  1 | SIMPLE      | lineitem | ALL    | l_pk          | NULL    | NULL    | NULL                    | 5982534 | Using where |
|  1 | SIMPLE      | part     | eq_ref | PRIMARY       | PRIMARY | 4       | tpch.lineitem.L_PARTKEY |       1 | Using where |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+

Why is the index l_pk not used?

Even though you are using FORCE INDEX the Query Optimizer took over as expected.

The pass through the lineitem table exceeded more than 5% of the total index entries. This is what lead to the Query Optimizer to dismiss using the index. Oracle, MSSQL and PostgreSQL would not have done any different.

Here is my sick attempt at refactoring the query

select sum(l_extendedprice* (1 - l_discount)) as revenue
from
(
    select p_partkey l_partkey,'S' contsize from part
    where p_brand = 'Brand#12'
    and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and p_size between 1 and 5
    union
    select p_partkey,'M' contsize from part
    where p_brand = 'Brand#23'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and p_size between 1 and 10
    union
    select p_partkey,'L' contsize from part
    where p_brand = 'Brand#34'
    and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and p_size between 1 and 15
) partkeys
left join listitem using (l_partkey)
where
    l_shipmode in ('AIR', 'AIR REG') and
    l_shipinstruct = 'DELIVER IN PERSON' and
    IF(contsize='S',IF(l_quantity >= 1 and l_quantity <= 1 + 10,1,0),
        IF(contsize='M',IF(l_quantity >= 10 and l_quantity <= 10 + 10,1,0),
            IF(contsize='L',IF(l_quantity >= 20 and l_quantity <= 20 + 10,1,0),0)
        )
    )
;

You may need to index the tables

ALTER TABLE parts ADD INDEX brand_container_size_ndx
(p_brand,p_container,p_size,p_partkey);
ALTER TABLE listitem ADD INDEX partkey_shipinstruct_shipmode_quantity_ndx
(l_partkey,l_shipinstruct,l_shipmode ,l_quantity);

Give it a Try and let us know if it is fast or if it even works.


How to predict new node allocation in a Cassandra cluster? I have a 5-node Cassandra cluster. Each node has disk space of 5TB. The real usage for each node is around 2.5TB depending on allocations and compactions (some are around 2TB some around 2.8).

I would like to add new nodes to the cluster with lower disk space (2TB for example).

How can I be sure that the new nodes will have enough disk space when Cassandra will redistribute data? How can I be sure that my new nodes are not too small?

I'm afraid that 2TB won't be enough, but what about 2.5TB?

Be  careful about decreasing the amount of free disk space. The recommendation for cassandra 2.1 is to maintain 50-80% free space. You can read about it on the Datastax site. The free space is needed during compaction as the SStables will be streamed to disk. Depending on your compaction strategy, you can determine your disk capacity.


View Design Best Practices When designing a view, would it be best to retrieve

the ID fields where FK relationships exist between the joined tables
the desired description field obtained through the FK id relationship
both?

The views I am writing will be primarily used for SSRS reports and I want the views to be adaptable and flexible for multiples uses.

Critiques/advice are encouraged!

Thank you!

I would add both as Adam points out.
I initially thought that views would only be useful as a base for reports but users end up asking for more detail.  The time to add a new column later is much more effort than adding it to start.


Query bytea column using prefix Assuming I have a table with a bytea column called data on a table called t, how can I:

Write a query that returns all rows which have a specific prefix on their data column.
Index the query.

I am using Postgresql 9.5.

For searching by prefix, you just do it.  The hard part (at least from the command line) is getting the binary data to be queried into query in a properly escaped way.

To query a table of file-blobs for files with the ELF header:

select....where data like (E'\\x7f')::bytea||'ELF%'

For indexing it, the main problem you will have is that PostgreSQL btree indexes can't index values longer than 1/3 of the page size (2712 bytes, usually) and a column of binary data is likely to have some records that exceed that.  You can build a functional index on only the first so many bytes of the data, and query that.

create index on thing (substring(data,0,100));

And you can then query it like this:

 select....where substring(data,0,100) like (E'\\x7f')::bytea||'ELF%';

PostgreSQL is not smart enough to realize that for a prefix to match the full column, it most also match a longer-than-the-query prefix of the column.  So that means you must write the match against the expression used to build the index, not against the column itself, or the index won't be used.


Oracle scheduler chain priority in data warehouse applications The scenario is as follows:
We have a data warehouse type application that relies on the Oracle job scheduler and a Chain.  This chain is responsible for generating materialized views (caches) that are to be queried against based on data that exists in the primary tables.  For the sake of discussion the materialized views are :
select * from Cats,
select * from Dogs,
 select * from Humans

During this cache creation process there are arbitrary tasks that are constantly populating the Cats, Dogs, and Humans tables.  My question is:

Given the above scenario is it possible for the creation of the materialized views (a read statement) to block during the insertion of new records from an abritrary program (these are bound by a transaction).  I believe that there should be zero conflict, but I want to bring this question to more experienced DBAs.

No,
the reading of your cats, dogs and humans tables wont block other processes that are inserting in them.


Is there an equivalent of 'FORMATFILE' in MySQL? I'm looking for a way to import data from a text file to a MySQL database,
the text file used to be imported in a SQL SERVER db via BCP and FORMATFILEs option for exemple :

BULK INSERT [MyTable] FROM 'myFolder\.txt'
WITH (
FORMATFILE = 'myFolder\MyTable.fmt',
ROWS_PER_BATCH = 5000,
MAXERRORS = 5000,
CODEPAGE = 'ACP',
ERRORFILE = 'myFolder\errors.txt'
  )

MyTable.fmt :

7.0
4
1 SQLCHAR 0 3 "" 1 Field1
2 SQLCHAR 0 7 "" 2 Field2
3 SQLCHAR 0 3 "" 3 Field3
4 SQLCHAR 0 2 "" 4 Field4

Fields (or columns) in the source file are not separated, the only thing to distinct data is the number of character by each column as mention in the  formatfile MyTable.fmt e.g. :

Field1 the first          3 characters form the left
Field2 the first (3+)     7 characters form the left
Field3 the first (3+7+)   3 characters form the left
Field4 the first (3+7+3+) 2 characters form the left

Any ideas how to proceed in MySQL ?

It seems to me this case cannot be handle in the same way used in SQL Server. However, I found another alternative to do that (i.e., importing data based-on the number of character by each column) :

LOAD DATA  INFILE 'xx.txt' INTO TABLE MyTable
 LINES TERMINATED BY '\n' (@L)
 SET
    Field1 = SUBSTR(@L, 1, 3), -- field1 : Position = 1 ,length = 3
    Field2=  SUBSTR(@L, 4, 7),  -- field2 : Position = (1+3) ,length = 7
    Field3=  SUBSTR(@L, 11, 3), -- field3 : Position = ((1+3)+7) ,length = 3
    Field4=  SUBSTR(@L, 14, 2) -- field4 : Position = (((1+3)+7)+3) ,length = 2
 ;


why can I only install MySQL 5.1 on cent OS 6.2? I had Ubuntu 10.10 on my server and MySQL 5.1 version. But since i wanted to update the system, I installed a fresh version of Cent OS 6 only so I could install the latest version of MySQL. But after installing cent OS 6 I am trying to install Mysql now, and it shows Mysql 5.1 as the only available package.

Please help

You asked this similar question before about getting MySQL 5.5 and I answered it : I am getting an error while trying to download binary for mysql 5.5.11 (Please upvote it if it helped before)

Here is a script I use to get the latest MySQL 5.5 for RHEL6

HOWMANYBITS=64
WHATOS=6
BINARY_VERSION=000
if [ ${HOWMANYBITS} -eq 32 ] ; then BINARY_VERSION=i386   ; fi
if [ ${HOWMANYBITS} -eq 64 ] ; then BINARY_VERSION=x86_64 ; fi
MINOR_RELEASE=22
MYSQL_RELEASE=5.5.${MINOR_RELEASE}
OS_VERSION=000
if [ ${WHATOS} -eq 0 ] ; then OS_VERSION=linux2.6 ; fi
if [ ${WHATOS} -eq 5 ] ; then OS_VERSION=rhel5    ; fi
if [ ${WHATOS} -eq 6 ] ; then OS_VERSION=el6    ; fi
RELEASE_TAG=1
DEVEL__RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARD2_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARED_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
CLIENT_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
PERL_URL=${PERL_LOCATION}/${PERL_FILE}
cd
rm -rf MySQL5.5_Install
mkdir MySQL5.5_Install
cd MySQL5.5_Install
wget ${PERL_URL}
wget ${DEVEL__RPM}
wget ${SHARD2_RPM}
wget ${SHARED_RPM}
wget ${CLIENT_RPM}
wget ${SERVER_RPM}
ls -l

When you are satisfied with the downloads, then install them:

for RPM in `ls -lSr *.rpm | awk '{print $9}'` ; do rpm -Uvh --force ${RPM} ; done

Give it a Try !!!

I found the problem. For OS6, change i386 to i686 and the download works ...

[redwards@lw-lts-155 MySQL_5.5.22]$ HOWMANYBITS=32
[redwards@lw-lts-155 MySQL_5.5.22]$ WHATOS=6

MYSQL_INSTALL_FOLDER=MySQL_${MYSQL_RELEASE}
OS_VERSION=000
[redwards@lw-lts-155 MySQL_5.5.22]$ BINARY_VERSION=000
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${HOWMANYBITS} -eq 32 ] ; then BINARY_VERSION=i686   ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${HOWMANYBITS} -eq 64 ] ; then BINARY_VERSION=x86_64 ; fi
SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ MINOR_RELEASE=22
[redwards@lw-lts-155 MySQL_5.5.22]$ MYSQL_RELEASE=5.5.${MINOR_RELEASE}
[redwards@lw-lts-155 MySQL_5.5.22]$ MYSQL_INSTALL_FOLDER=MySQL_${MYSQL_RELEASE}
[redwards@lw-lts-155 MySQL_5.5.22]$ OS_VERSION=000
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${WHATOS} -eq 0 ] ; then OS_VERSION=linux2.6 ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${WHATOS} -eq 5 ] ; then OS_VERSION=rhel5    ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${WHATOS} -eq 6 ] ; then OS_VERSION=el6    ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ RELEASE_TAG=1
[redwards@lw-lts-155 MySQL_5.5.22]$ DEVEL__RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ SHARD2_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ SHARED_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ CLIENT_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
[redwards@lw-lts-155 MySQL_5.5.22]$ PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
[redwards@lw-lts-155 MySQL_5.5.22]$ PERL_URL=${PERL_LOCATION}/${PERL_FILE}
[redwards@lw-lts-155 MySQL_5.5.22]$ cd
[redwards@lw-lts-155 ~]$ rm -rf ${MYSQL_INSTALL_FOLDER}
[redwards@lw-lts-155 ~]$ mkdir ${MYSQL_INSTALL_FOLDER}
[redwards@lw-lts-155 ~]$ cd ${MYSQL_INSTALL_FOLDER}
[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${PERL_URL}
--2012-04-10 15:40:23--  ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages/perl-DBI-1.53-2.fc7.x86_64.rpm
           => `perl-DBI-1.53-2.fc7.x86_64.rpm'
Resolving ftp.de.netclusive.de... 89.110.128.25
Connecting to ftp.de.netclusive.de|89.110.128.25|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done.    ==> PWD ... done.
==> TYPE I ... done.  ==> CWD /pub/parallels/Plesk/Expand/2.3.1/packages ... done.
==> SIZE perl-DBI-1.53-2.fc7.x86_64.rpm ... 625843
==> PASV ... done.    ==> RETR perl-DBI-1.53-2.fc7.x86_64.rpm ... done.
Length: 625843 (611K)

100%[===================================================================================================================================>] 625,843      702K/s   in 0.9s

2012-04-10 15:40:26 (702 KB/s) - `perl-DBI-1.53-2.fc7.x86_64.rpm' saved [625843]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${DEVEL__RPM}
--2012-04-10 15:40:26--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-devel-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:26--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-devel-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4909292 (4.7M) [text/plain]
Saving to: `MySQL-devel-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 4,909,292   3.01M/s   in 1.6s

2012-04-10 15:40:29 (3.01 MB/s) - `MySQL-devel-5.5.22-1.el6.i686.rpm' saved [4909292/4909292]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${SHARD2_RPM}
--2012-04-10 15:40:29--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 404 Not found
2012-04-10 15:40:29 ERROR 404: Not found.

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${SHARED_RPM}
--2012-04-10 15:40:29--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-shared-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:29--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-shared-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1685312 (1.6M) [text/plain]
Saving to: `MySQL-shared-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 1,685,312   2.92M/s   in 0.6s

2012-04-10 15:40:30 (2.92 MB/s) - `MySQL-shared-5.5.22-1.el6.i686.rpm' saved [1685312/1685312]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${CLIENT_RPM}
--2012-04-10 15:40:30--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-client-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:31--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-client-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14633936 (14M) [text/plain]
Saving to: `MySQL-client-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 14,633,936  3.52M/s   in 4.1s

2012-04-10 15:40:38 (3.44 MB/s) - `MySQL-client-5.5.22-1.el6.i686.rpm' saved [14633936/14633936]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${SERVER_RPM}
--2012-04-10 15:40:38--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-server-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:39--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-server-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 40659264 (39M) [text/plain]
Saving to: `MySQL-server-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 40,659,264  3.33M/s   in 11s

2012-04-10 15:40:58 (3.54 MB/s) - `MySQL-server-5.5.22-1.el6.i686.rpm' saved [40659264/40659264]

[redwards@lw-lts-155 MySQL_5.5.22]$ ls -l
total 61140
-rw-r--r-- 1 redwards users 14633936 Mar 13 19:55 MySQL-client-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users  4909292 Mar 13 19:55 MySQL-devel-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users 40659264 Mar 13 19:56 MySQL-server-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users  1685312 Mar 13 19:57 MySQL-shared-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users   625843 Apr 10 15:40 perl-DBI-1.53-2.fc7.x86_64.rpm
[redwards@lw-lts-155 MySQL_5.5.22]$

Just run this

for RPM in `ls -lSr *.rpm | awk '{print $9}'` ; do rpm -Uvh --force ${RPM} ; done

and you are good to go !!!


Should I disable filesytem journaling for MS SQL Server (Linux/Windows) For a PostgreSQL server installation on Linux, it is beneficial to disable filesystem journaling.

  Tip: Because WAL restores database file contents after a crash, journaled filesystems are not necessary for reliable storage of the data files or WAL files. In fact, journaling overhead can reduce performance, especially if journaling causes file system data to be flushed to disk. Fortunately, data flushing during journaling can often be disabled with a filesystem mount option, e.g. data=writeback on a Linux ext3 file system. Journaled file systems do improve boot speed after a crash.

from: Official documentation Write-Ahead Logging (WAL)

Unfortunately I did not found similar information about SQL Server on Windows or Linux, but...

Since SQL Server Sever as all modern relational database management systems do WAL, it seems logical that one also can and should  disable file-system journaling on a Ext4 filesystem which is dedicated for MS SQL Server data on a Linux installation , and perhaps the same statement stands for NTFS on a Window installation.

In what measure the previous statements are true?

Filesystem-level journaling is not listed as a requirements for the installation of SQL Server on Windows or Linux.  SQL Server goes to great lengths to make sure writes are durable (check out this mammoth article on some improvements made in SQL Server 2017 CU6 on Linux in that regard).

Since it's not required, I would conclude that it is safe to disable.

Since, as you noted, it will inevitably result in more load (writes) on the system, I think the same guidance would apply to SQL Server as it does to Postgres (that disabling the filesystem journaling behavior would be beneficial to system performance).

As there is no official guidance around this filesystem feature that I can find, I'm just sharing my personal thoughts on that matter.


Howto transfer tracefile into a trace table without loosing the sort order? I have traced some data into a trace table into Sql Server using the Profiler GUI. This table has the following structure:

It contains the SQL statements as well as execution plans in the TEXTDATA column. I figured out that usually the execution plan xml that belongs to a select statement has been placed into the row before the sql statement.

So for example the execution plan for the Select statement in row with [ROWNUMBER] = 60 usually can be found in the row with [ROWNUMBER] = 59.

This seems to be quite resilient (enough for my purpose) and I would like to keep this relation for later use. (So far I could not find another way of catching both, the query and the plan - ideally it should come as ONE merged result, for example the plan attached to the query inside of a comment... but I could not manage to create that.)

However, to avoid the massive overhead of using the Profiler GUI I created a trace script, stored the results into a trace file (because unfortunately using a script it's not possible to directly redirect the results into a table) and then imported the trace file into a table using the following statement:

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\traces\myTraceFile.trc', default)

Unfortunately this produces a different table structure and worst about that is, that the ROWNUMBER identity column is now missing, so the records are messed up and it's impossible to find the plans that belong to the queries.

Q 1:
Is it possible to create a table from the trace file that has the rows stored in an order similar to the trace table profiler had created?

Q 2:
Does anyone know another better way to get those information without using the profiler GUI?

During creation of the question process I figured out a way:

SELECT IDENTITY(int, 1, 1) AS RowNumber, EventClass, TextData, Duration, SPID, BinaryData, ServerName, DatabaseName INTO trace_table3
FROM fn_trace_gettable('c:\traces\myTraceFile.trc', default);

does the trick. The results are kept "together" same way as in the trace table.

http://zarez.net/?p=228

https://msdn.microsoft.com/de-de/library/ms188425(v=sql.120).aspx

I tried to trace the execution of the function fn_trace_gettable to get an idea how it orders the records to get them lined up again, but of course nothing showed up but the execution itself.

However, this answers question 1, I have the desired result.

EDIT:
As AaronBertrand suggested in the comments, the eventsequence delivers the events in an order that very reliably contains the PLANS (column/type 51) in advance of the SQL Statements. That's a better approach than identities.


Guid vs. integer ID for unique fields This is really a generic question.

Suppose I have a replication pattern of a single write DB and multiple read DB replication slaves - I do not have to have GUID's as my unique IDs fields. Is that a correct assumption?

If I have multiple DB instances where all are writeable and sync between themselves, GUID's in unique ID fields is mandatory. Is that also correct?

I am setting up PostgreSQL on Amazon RDS.

Thanks.

Suppose I have a replication pattern of a single write DB and multiple
  read DB replication slaves - I do not have to have GUID's as my unique
  IDs fields. Is that a correct assumption?

Correct. As Basil Bourque wrote it doesn't matter what you kind of IDs you use if you have a single master source of data.

  If I have multiple DB instances where all are writeable and sync
  between themselves, GUID's in unique ID fields is mandatory. Is that
  also correct?

No, it is not mandatory to use GUID in this case, but it may be convenient.

I assume a quite common scenario: You have N writable databases DB1 - DBn. They can "talk" to each other to exchange data, but connection between them is not 24/7 or not fast enough to be done "in real-time" as the data changes. So, each database accumulates its changes locally and then periodically the changes are transferred to other databases. Since, once in a while all databases have exactly same data, there must be a method to assign some identifier to each row that would be unique not just within one database, but within all databases.

There are many ways to achieve it, GUID is just one.
Another common method is to have ID that consists of two parts: ID of the database plus standard sequence of growing unique numbers.

Another method that I used for two-way sync of two databases: ID effectively consists of two numbers. First number is a standard unique sequence of the local database, second number is a sequence from remote database.


Getting the exact count of rows in Postgres database Is there any way to get the exact row count of all tables in Postgres in fast way?

Count(*) seems to run very slowly for larger tables.

Is there a way to get an exact count from Postgres statistics tables without running vacuum, which is also a costly operation?

It seems there is currently no built-in way to do what you require in PostgreSQL.

  People are working toward such capabilities.  While nobody can say
  with any certainty when such features will make it into a
  PostgreSQL release, I think it's safe to predict that it will not
  be before late 2017, and most probably later than that.

Meanwhile, you could manually implement a solution using triggers; for example as described in Postgresql General Bits by A. Elein Mustain.

The idea is to maintain an always-current row count in a separate table using triggers. Be aware that this may add significant overhead to data modifications.


How to update a point table when a polygon table is modified I am trying to create a function that update table A (point) when table B (polygon) is modified. The reference to B must be changed in A. I can't get it to work. There are no overlaps in the polygons. It really is just to update the polygon ID in the points table. I was thinking of ST_Within to do it.

CREATE OR REPLACE FUNCTION maj_A()
    RETURNS trigger AS $foo$
BEGIN
    UPDATE public.Points AS A
    SET A.a = B.a,
        A.b = B.b
    FROM public.Polygons AS B
    WHERE ST_WITHIN(Points.geom, Polygons.geom) AND NEW.geom <> OLD.geom;
    RETURN NEW;
END;

$foo$ LANGUAGE plpgsql;

I am using postgre 10 and postgis 2.4. Any working function is welcome. The function is linked to a trigger.

Would make a comment, but don't have enough reputation, sorry.

Shouldn't  NEW and OLD be B and ? B_new or something like this? Where is the new geometry stored to make the ST_Within check?

If I missed something and you want to compare geometries as in NEW.geom <> OLD.geom, consider ST_Equals or ST_OrderingEquals like ST_OrderingEquals(NEW.geom, OLD.geom) = 0


Mongodb get Effective Lock I´m trying to calculate the Effective Lock %, like MMS does. I think I could get it via MMS API, but most of our mongo instances currently are not monitored in MMS (and probably never will).

All I have is a very rudimentary globalLock calculation using the GlobalLock totalTime and lockTime, but these kind of metrics does not exist for the individual locks and Im not really sure how to interpret a calculation like that with the timeLockedMicros and timeAquiringMicros. Could you guys point me towards the light?

I dont realy try it or sure that is true but i read same info on webs

user_load+system_load+table as %    (how mush use)
,CPU                        as %    (how mush use)
,network_processor_load     as %    (how mush use)
memory                      as %    (how mush use)
combine will give you the % that you wont...

at lest im hope so XD...

sorry... if it not ture ^o.0^ but still my brain was work hard for this...

sooo easy on me...

and dont - star pliz


Storing html in mysql text fields, or include files? (html formatted articles) I'm wondering if I should store long html formatted articles in my MySQL database, or if it would be better to store a reference to a file, and include that instead. It is not just paragraphs, but lists, tables, images (<img src="">'s), and other containers containing side notes and such.
The articles are usually somewhere between 150 000 and 250 000 characters (including spaces). But that is excluding html tags.

It works fine storing it in database. But was wondering if there was a better approach?
There is no search options on this site.

I would recommend you put as little HTML in your database as possible. One very popular design pattern for websites is the Model-View-Controller pattern.

Using that pattern your data (author, title, categories, article content, etc.) would be stored in the database without HTML tags and be called the Model.  Then the structure of the HTML page would be stored in a file called the View.  The Controller would be in charge of accepting the HTTP requests, requesting the appropriate data from the database, and merging/sending that data with/to the view.

Any common web programming platform should be able to accommodate this pattern.


INSERT takes over 5 hours, when inserting more than specific number of rows When inserting less then about 1,350,000 rows to the table it all takes about 2 minutes, however when number of inserted rows is bigger, then the time needed to insert data grows to about 5 hours.

The problem is not connected with query or indexes, because everything has been working fine for a long time and nothing has changed in the structure of query, tables or indexes.

Problem appeared for the first time about 2 weeks ago and it appears repeatedly on days, when number of inserted rows is bigger than +-1,350,000. For example on one day number of inserted rows is 1,200,000 and the process takes 2 minutes, on the other day number of rows is 1,450,000 and it takes 5-6 hours to insert data.

I tried to rebuild indexes, but it has not helped.

My guess would be - if you are not actually being blocked - that you are hitting a threshold above which the data file (and/or log file) has to grow, and that your configuration is not optimized to support this growth. Ensure that:

Your data file growth rate is reasonable, and is a fixed size (not %!) large enough to accommodate this transaction and anything else that may be going on concurrently.
Same for log file.
Instant file initialization is enabled. This will help speed up data file growths but not the often more troublesome log file growths.
You aren't inserting 1.35 million individual rows in a loop or otherwise all as one big transaction. There is something to be said for breaking your transactions into chunks.


Excessive replication snapshot size causing failure I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting

Following a replication failure we are attempting to reinitialise subscriptions with a new snapshot. The size of the database is 135GB, one table within the database is 60GB - we have excluded this article from the publication. This meant we had an expectation of snapshot size being ~75GB.

We've had several attempts at creating the snapshot and these have failed due to lack of disk space. Last night the snapshot filled a disk with 340GB of free space.

I 'd welcome any explanation for this huge size of snapshot.

In my examination of possible causes I note that the snapshot format is set to "Character - Required if a publisher or subscriber is not running SQL Server". This despite the fact that at both ends of this transnational replication  is native SQL Server. Is there a different in size between formats?

Thanks in advance.

Native mode file Size:105 MB
C:>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventNative.dat -T -n
218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)
Total : 7878 Average : (27796.01 rows per sec.)

Character mode file Size:66 MB
C:\>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventChar.dat -T -c
218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)
Total : 1654 Average : (132392.38 rows per sec.)

Note that Character representation of any data will almost always exceed the original size. Depending on type, it can exceed by a large margin. Eg. an int column takes 4 bytes, but the character representation of 1000000 takes ~16 bytes as Unicode, including delimiters. That is a x4 increase right there. Dates, floats, numerics all will usually increase, and by even bigger factors than x4.

The native format alternative is the bcp utility native format (since snapshots are applied using bcp). This format is only slightly longer than the native SQL storage when comparing individual values, but it can result in actually smaller files than SQL Server required storage because the bcp file is dense: there is not page header overhead, nor partially filled pages (fragmentation, fill-factor). See Use Native Format to Import or Export Data.

Additionally you should also compress the snapshots. See Enhance General Replication Performance:

  Use a native mode snapshot unless a character mode snapshot is required.
  Use the default native mode snapshot for all Subscribers except non-SQL Server Subscribers and Subscribers running SQL Server Compact, which require a character mode snapshot.
  Use a single snapshot folder for a publication.
  When specifying the publication properties related to snapshot location, you can choose to generate snapshot files to the default snapshot folder, to an alternate snapshot folder, or to both. Generating snapshot files in both locations requires additional disk space and more processing when the Snapshot Agent runs.
  Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files.
  The Snapshot Agent performs a sequential write of data to the snapshot folder. Placing the snapshot folder on a separate drive from any database or log files reduces contention among the disks and helps the snapshot process complete faster.
  When you create the subscription database at the Subscriber, consider specifying a recovery model of simple or bulk-logged. This allows minimal logging of the bulk inserts performed during the application of the snapshot at the Subscriber. After the snapshot has been applied to the subscription database, you can change to a different recovery model if necessary (replicated databases can use any of the recovery models). For more information about selecting a recovery model, see Restore and Recovery Overview (SQL Server).
  Consider using the alternate snapshot folder and compressed snapshots on removable media for low-bandwidth networks.
  Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and make it easier to transfer snapshot files on removable media.
  Compressed snapshots can, in some cases, improve the performance of transferring snapshot files across the network. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files, and by the Distribution Agent or Merge Agent when applying the snapshot files. This may slow down snapshot generation and increase the time it takes to apply a snapshot in some cases. Additionally, compressed snapshots cannot be resumed if a network failure occurs; therefore they are not suitable for unreliable networks. Consider these tradeoffs carefully when using compressed snapshots across a network. For more information, see Alternate Snapshot Folder Locations and Compressed Snapshots.


Cannot create foreign key on 2 VARCHAR(45) columns in MySQL Workbench 6.1 I'm trying to create a foreign key. The columns in both tables are VARCHAR(45) and neither is a PK, however I keep getting this error message:

It is saying the PK of reference table is INT, which is true, but I am not referencing this column, I am referencing another column in this table that is VARCHAR(45).

I have already made to FK in this same table where both columns are VARCHAR(45), which is why I am wondering what I am doing wrong this time.

UPDATE:

Just figured it out.

The reference table column needed to be a Unique Index, then the foreign key worked.

Not a DB guy, but that sounds like a rookie mistake on my part.

From the error message, what i can deduce is that a foreign key is trying to reference a table with a primary key of int, while it also has a primary key of int set on it's table, a bit confusing.

But try removing the primary  key on the referencing table like table two below, it should work fine.
so you could do something like

TABLE 1                   TABLE 2
Id(int)(pk)                   id(int)
name(varchar) ------ frontname(varchar)(fk)

or

TABLE 1                   TABLE 2
Id(int)(pk)                   id(int)(pk)
name(varchar)(pk2) ------ frontname(varchar)(pk2)(fk)

Not sure of the second one though


Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction? The default isolation level of my database is "read committed snapshot". Please see the below screenshot.

I have the table "Tickets' with the following data in my database.

Now in connection 1, I am starting a new transaction with isolation level as "read committed". And I'm going to update the priority to 'medium' for the #TicketID "2"

From Connection 2, I am trying to access the details of #TicketID "2". And its still taking the value from the version store which is created by the read committed snapshot isolation, which is not expected behavior.

As per documentation, if a transaction using "read committed" isolation level, the row can't be accessed by any other transaction until the transaction performs a commit/Rollback.

Can anyone tell me why it is not happening in the given scenario? I am also curious to know, why a transaction level isolation is not able to override the default isolation level (here read committed snapshot) which is set at the database level?

To give more clarity on the isolation override on the transaction scope, I'm providing the below information.

I have set my database isolation to read committed.

Now in connection 1, I am initiating my transaction to update the Tickets priority.

In connection 2, I am trying to access the record. But this will not complete since the transaction isolation level is in "read committed" mode.

In connection 3, I am trying to access the record with isolation level as "read uncommitted". Here we can see the record as since it will override the default isolation which is set at the database level.

I am expecting the same behavior when I set "Snapshot Isolation" at the database level, and overide it in another transaction scope. For me, it always returns the last committed value from the version store irrespective of the isolation level which is mentioned at the transaction scope. I hope I conveyed my concerns clearly.. :)

There is no way to override the database-level setting of read committed snapshot (RCSI) for a whole transaction.

When RCSI is set, all read committed transactions will use row versions, delivering the last committed version of a row.

A session-level override option has been asked for. You can vote for it on the product feedback site at Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED LOCK.

You can override the setting per-statement and per-table using the READCOMMITTEDLOCK table hint.

Alternatively, you could use snapshot isolation (SI) and turn read committed snapshot isolation (RCSI) off. Each transaction would then either SET TRANSACTION ISOLATION SNAPSHOT or SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

More information in my series of articles on isolation levels.


SQL Server TDE - Database backups and post restore behaviour Below is my SQL Server details:

SQL Server 2014 Enterprise SP2 GDR with Availability Group configured.
List item

NOTE: The Databases are FileStream Enabled.

Below are the series of actions I did recently and need your help to understand and correct myself:

Enabled TDE (Structured Data .mdf & .ldf)
Enabled Encryption File System (EFS) for Unstructured data
After both the configurations , now the Database is on TDE Enabled and EFS Enabled in AOAG .
I took a backup of the Database (From what I have learnt  post TDE & EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)

I removed the Database from AOAG. Later deleted the database from all AG Replicas and restored it using encrypted file taken above and joined them to AG again.

My Actual question is :

Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE & EFS , Can some explain me what happened here ?
Why the Database is not encrypted when I used a encrypted Database Backup file !
Does the backup file post restoration gives decrypted files automatically by default behavior ?

Current the Is_encrypted value for the database is now "1" earlier before retore it was "3".

How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ? Is my thinking wrong , kindly share some insights on this point?

I believe there is more to the story than we're getting, here.

  I took a backup of the Database (From what I have learnt post TDE & EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)

In this case, the database is still encrypted using TDE - however if you've changed the volume that the backup is on which isn't protected by EFS then EFS isn't in play. EFS is like TDE, but for the filesystem - so once you leave the protection boundary, it's no longer protected.

  Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE & EFS , Can some explain me what happened here ?

I don't follow and there should be more to this that we're missing. Just restoring the database on the same server would require that the server certificate for TDE be available (which, since it's the same server it should be available) so the restore can take place. If you've chosen to MOVE the file location to a location not protected by EFS then EFS would no longer be in the picture. If you restored over the same database in the same location, then both TDE and EFS should bein effect. Regardless, though, TDE will still be in effect.

  Why the Database is not encrypted when I used a encrypted Database Backup file !

It is, so we're missing part of the story. If it's saying it isn't, then something else occurred after the RESTORE DATABASE command to disable TDE.

  Does the backup file post restoration gives decrypted files automatically by default behavior ?

No, that's why something else had to have happened. The database should still be TDE encrypted, whether or not EFS depends on the where the files were restored.

  How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ?

I'm not sure I follow, again, as a backup of a TDE database is encrypted. If you restore it, it's still encrypted. Period. If this happens each and every time just running RESTORE DATABASE and nothing else, then more data capture will be needed. If you're using a 3rd party tool, you'll want to ask what they do and their process or grab some tracing during the restore to look into what they are doing.


Creating big shared database I wish to create DB in Firestore. My program will have multiple user and each user will have calendar and can create an event. The user can share this event with any other user (Similar to Google calendar) but each user can add comment only visible to self on that event. So my question is how to manage that. Options:

Create table of users and each user will have collection of events and in case the user shares and event it duplicates to other users in their own collection and each user can input personal comment. But it resolves in duplication in the DB.

Same as 1, but instead of duplicating, each user will have collection to show what shred with him and only edit the comment so self.

Preferred option. Have all events in one table, and each user will have connection table to know to which event he is connected. So my question is so: if I have lets say 10M users, and each user will have 1K events, I would have table of Events with size of 10M*1K rows. Is this acceptable to have such huge table and is this acceptable to store all events of every users in one table. And what is the time retrieval for 1 row in this case?

There is possibility that the DB will be moved from Firestore to SQL, so I need more generic approach.

You should avoid duplicating rows in a database design.  You have a many-to-many relationship between events and users, so you will need the "connection" table between them to do that. There will be a one event to many connection relationship and one user to many connection relationship.
The connection table will have FK columns to both event and user, plus any attribute that is useful about that connection (e.g. visibility indicator, date accepted). Make the two FK columns be a unique constraint to make sure there are no duplicates.
You will need another table, perhaps named USER_EVENT_COMMENT, that will also FK to the user and event tables. You can either use an incrementing as a PK or combine the two FK columns with something else (perhaps a date) to make a composite PK. I think the auto increment approach is cleaner.
As to restricting the visibility of the comments, that depends on how you access the database from your application. If all access to the database is the same identity, then your application will need to enforce visibility. If you access with different identities, you can enforce visibility in the database.
As for size, a few billion rows is no big deal.  The proper indexes and thoughtful query construction (use bind variables) and you should be fine.


SQLServer - treat table as index I have table that is more or less as follows:
table Sample (
    location bigint,
    device bigint,
    timestamp datetime,
    type bigint,
    value bigint,
    )

For performance reasons we also have one mighty index with same columns in identical order, except that value is included rather than used in index.
This works as expected, except that AFAIU data is kept twice in database - once in the table and once in an index.
Question: is there some way to, conceptually, keep data only in the index? I believe that would require keeping table organized in the same way index is organized, effectively removing need for the index. I tried to create composed primary key that would emulate aforementioned index, but it boiled down to automatic creation of identical index.

What you ask for is what we call a "clustered index" in the SQL Server world. The clustered index is the data. I.e., the b-tree for the index is you actual table data.
A clustered index don't have the concept of included columns since all columns are in the clustered index - the clustered index being the data. So you have the key columns (what you use to "drive the query", like the WHERE clause, so to speak); and the rest of the columns are in the leaf.
You can think of the non-key columns in a clustered index just like included columns in a non-clustered index.
Don't confuse the PK with the clustered index. You decide whether the PK should come with a clustered index or a non-clustered index.
You might have a better candidate for the clustered index than the PK. Fine! Just make the PK non-clustered and create the clustered index on that better candidate.


Problems on filegroup size -- SQL Server 2005 consider FG_january_2011 file group contains 100000 records. and FG_january_2012 file group contains 2000 records.

update Table_Name set date=dateadd(yyyy,+1,date) where Year(date)=2011

if i execute this query, what will happen.?? my thought is FG_january_2011 file group 100000 records will move to FG_january_2012 file group. and records too incremented as 102000.

Is it right things?? else what???

Removing data from a filegroup never reduces its size. It just frees space to be used for other purposes in the filegroup. In a monthly partitioned table you should rotate the filegroups. You will need 14 partitions for 12 months (you always need an empty partition in the beginning and an empty one in the end) and you do the rotation by switching out the oldest month partition, truncating the switched out table, then switching in a new partition for the next month which reuses the filegroup freed by switching out the oldest month.

If you ever find yourself merging or splitting partitions with data, you're doing it wrong. See How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005.

And, to answer the actual question in the title, an empty filegroup can be 1) shrunk or 2) dropped to reclaim the disk size. Not that I would ever bother with a tiny 200mb size filegroup. But again, you never have to do it if you implement partitioning correctly.


Using a subquery in a LIKE statement I need to write a subquery inside a LIKE statement. This subquery would return me a word that I need to find in the middle of a sentence (that belongs to another field). Is it possible? This is how I'm trying (and failing):

select nm_linha from linhas where cidades_origem like '%SUBQUERY HERE WOULD GO HERE%'

Something like this should work :

select nm_linha from linhas where cidades_origem like CONCAT('%', (subquery_goes_here) ,'%')

For example :

SELECT *
FROM films
WHERE titre LIKE CONCAT('%', (SELECT  'fella'),  '%')

+---------+------------+
| id      | titre      |
+---------+------------+
|       1 | Goodfellas |
+---------+------------+
1 row in set (0.01 sec)


Why is SSMS showing red squigglies(sp?) even though the table exists? I'm creating a new table and in the process creating two foreign keys. The tables with the primary keys referenced exist but Intellisense is giving the error message: "Foreign key. . .references invalid table 'ROLES'."

Why does SQL Server Management Studio not recognize these tables?

In short, the tables are not recognized because the IntelliSense local cache is not up to date.

While you are in the Query Editor window you can press CTRL + SHIFT + R to refresh the cache. This should update IntelliSense's cache and the red squiggles should disappear.

Please see this link for more information.


What is the difference between xact_start and query_start in postgresql? I am reviewing over the pg_stat_activity view. And there are two fields that are indistinguishable to me:

  xact_start:The time at which the transaction started to run which
  could be different than the query_start but should always have
  occurred before the query_start time.

  query_start: The time at which the query started to run. This can be
  useful to determine if a query has been running for a long time.

It says they both determine the start time of the query. It also says that xact_start can be different than query_start, but doesn't explain why - only that it should start prior to query_start. But what is the actual difference between the two? Why would I care about xact_start?

It also says that xact_start can be different than query_start, but
  doesn't explain why

It differs when the transaction encapsulates several queries, for example:

BEGIN;  -- starts at t0, beginning of transaction
query1; -- starts at t1
query2; -- starts at t2
COMMIT; -- starts at t4, end of transaction

If we look at the pg_stat_activity entry for this session while query2 is running, xact_starts will be t0 and query_start will be t2, with t2>t0, the difference being essentially the duration of query1.

xact_start helps discovering long running transactions, which is useful when troubleshooting or tuning a live database.


MySql concurrency with COUNT I Have a table with user_id and product_id.

For my purpose, a particular user is allowed up to 5 rows only.

To enforce this, I created such a SQL statement:

INSERT INTO the_table (product_id, user_id)
SELECT p, u FROM (SELECT 121 AS p, 40987 AS u)
WHERE (SELECT COUNT(*) FROM the_table WHERE user_id = 40987) < 5;

Now My Question:

without explicit locking\transaction,
Is in the case of two simultaneous command run,
When the number of rows before their run is 4,
It is possible that the count will be calculated for both commands before making the insertion - Then there will be 6 rows for same user_id?

Just as SELECT ... FOR UPDATE is sometimes needed, I think you must implement your action in a transaction:

BEGIN;
SELECT COUNT(*) FROM the_table WHERE user_id = 40987 FOR UPDATE;
if the count >= 5, ROLLBACK and exit.
INSERT INTO the_table (product_id, user_id) VALUES (121, 40987);
COMMIT;

Or...

BEGIN;
INSERT INTO the_table (product_id, user_id) VALUES (121, 40987);
SELECT COUNT(*) FROM the_table WHERE user_id = 40987;
if the count > 5, ROLLBACK and exit.
COMMIT;

(Three diffs:  order of statements; FOR UPDATE; >= vs >.)

In your original code, see if you can add FOR UPDATE to the subquery in the WHERE clause.


database not restoring from dump file I'm running the following command to create a dump of all database on my production server:

prodserver:/# pg_dumpall -U postgres -h 1.1.1.1 > prod.sql

on my development box, I also take a backup by doing:

devserver:/# pg_dumpall -U postgres -h 1.1.1.1 > dev.sql

Then I try to restore the production database on to development like so:

devserver:/# psql -f prod.sql -U postgres

It runs with no errors. And it appears to be creating tables /import data. ( I know because if I run it a second time, I get messages that certain data already exists / relationships already exist. )

But when I log in like so:

psql -U postgres -d databasename

the data looks the same as the old dev data.
I tried the following with the production data supposedly restored:

select count(*) from widgets;

I compare that with the count returned when I'm using the development database and they are the same.

I've also tried restarting postgresql after attempting the restore...it's a no go.

Any suggestions on what I might be doing wrong would be appreciated.

Thanks.

EDIT 1

I also checked the following:

I opened up the prod.sql file and found insert statements like:

INSERT INTO widgets_to_wiget_groups VALUES (363, 15);

And then I did a select statement on the database to see if I had this entry in the table but I don't... maybe it's restoring it but under the context of another user?

EDIT 2

I've also tried

 psql -f prod.sql -U postgres -h 1.1.1.1

but it still didn't work
As a test I tried restoring the dump file on another new server... same results.  So I checked and saw that in the dump file, there is no CREATE DATABASE command.
I'm thinking that the original command to dump the file is incorrect...

I had to run

CREATE DATABASE databasename;

to manually create the db, and then changed the restore command to:

devserver:/# psql -f prod.sql -U postgres -d databasename


Change MySQL 4.1 Windows to MySQL 4.1 Linux- Problem with the key Sensitive in query I need help to resolve this problem.

change the server MySQL 4.1 run on Windows to MySQL 4.1 run on Linux(debian).

This all working, but same query not return the correct data because key sensitive<\h1>.

In windows server i type:

  selete * from acao;

or

  Selete * from ACAO;

this two query return data.

But on Mysql 4.1 on linux is difference.

Is key sensitive<\h1>,
if the table is difine on server "acao",

the query

  selete * from acao;

the query returns data, but if the query is:

  Selete * from ACAO;

I have more than 200 files with this query, so I do not have time to change all of this.

bests,
jgois

There is a system variable that needs to be set in your my.cnf file.  You will need to add:
lower_case_table_names=1 in the [mysqld] section of your cnf file.

Kevin


Only join to latest entry in subselect in a given range determined by the main select I have one table Haul and one table ChargeCarrier_Storage. The latter contains movements of charge carriers between storage locations. Haul has an arrival and a departure time. I want to join together these two tables to get a list of all charge carriers which have been loaded onto a haul (=trailer).

My select works fine in most cases. There are some rare cases, though, when a worker books a piece onto the trailer, then back into the storage and then back into the trailer again (in between that time span between arrival and departure). In such a case, the same charge carrier will appear multiple times in the list with all data identical except for the StoredOn date.

This is not wrong per se, but the view we're talking about is used to create loading lists. And on there, only the latest entry should appear. So if you load it on, off and on again, it should be in the list once. If you load it on and off again, it should not be in the list at all.

Here's my current statement:

SELECT
    h.ID AS ID_Haul,
    h.DescriptiveID,
    ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY ccs.StoredOn ASC) AS Pos,
    cc.PickingNo,
    pin.PickingStorage,
    pin.TargetCountry,
    cc.[ObjID],
    cc.Customer,
    cc.Article,
    cc.Quantity,
    cc.QuantityUoM,
    cc.PackageLength,
    cc.PackageWidth,
    cc.PackageHeight,
    cc.PackageWeight,
    ccs.StoredOn,
    CAST(CASE WHEN h.Depature IS NULL THEN 0 ELSE 1 END AS BIT) AS Departured,
    ROW_NUMBER() OVER (PARTITION BY ccs.ID_ChargeCarrier ORDER BY ccs.StoredOn DESC) AS ORD -- TEST
FROM dbo.Haul h
INNER JOIN dbo.ChargeCarrier_Storage ccs ON
    ccs.ID_Storage = h.ID_Storage
    AND (
        h.Depature IS NULL AND ccs.StoredOn >= h.Arrival
        OR ccs.StoredOn BETWEEN h.Arrival AND h.Depature
    )
INNER JOIN dbo.ChargeCarrier cc ON cc.ID = ccs.ID_ChargeCarrier
INNER JOIN dbo.PickingInfo pin ON pin.ID_ChargeCarrier = ccs.ID_ChargeCarrier
WHERE
    h.ID = 1167
order by PickingNo

How can I make it work properly? Right now, it displays the same charge carrier multiple times, and I also don't know how it responds, when you unload the piece again.

The tables look like this:

CREATE TABLE dbo.Haul
(
    ID INT NOT NULL PRIMARY KEY IDENTITY,
    ID_Trailer INT NOT NULL,
    ID_LoadingBay INT NOT NULL,
    ID_Storage INT NOT NULL,
    ID_DestinationAddress INT NOT NULL DEFAULT 4,
    DescriptiveID CHAR(18) NULL,
    Arrival DATETIME2(2) NOT NULL DEFAULT SYSDATETIME(),
    Depature DATETIME2(2) NULL,

    CONSTRAINT FK_Haul_to_Trailer FOREIGN KEY (ID_Trailer) REFERENCES Trailer(ID),
    CONSTRAINT FK_Haul_to_LoadingBay FOREIGN KEY (ID_LoadingBay) REFERENCES LoadingBay(ID),
    CONSTRAINT FK_Haul_to_Storage FOREIGN KEY (ID_Storage) REFERENCES Storage(ID),
    CONSTRAINT FK_Haul_to_DestinationAddress FOREIGN KEY (ID_DestinationAddress) REFERENCES [Address](ID)
)
GO

CREATE TABLE dbo.ChargeCarrier_Storage
(
    ID_ChargeCarrier INT NOT NULL,
    ID_Storage INT NOT NULL,
    ID_User INT NULL,
    StoredOn DATETIME2(2) NOT NULL DEFAULT GetDate(),

    CONSTRAINT PK_ChargeCarrier_Storage PRIMARY KEY (ID_ChargeCarrier, ID_Storage, StoredOn),
    CONSTRAINT FK_ChargeCarrier_Storage_to_Storage FOREIGN KEY (ID_Storage) REFERENCES dbo.Storage(ID),
    CONSTRAINT FK_ChargeCarrier_Storage_to_ChargeCarrier FOREIGN KEY (ID_ChargeCarrier) REFERENCES dbo.ChargeCarrier(ID) ON DELETE CASCADE,
    CONSTRAINT FK_ChargeCarrier_Storage_to_User FOREIGN KEY (ID_User) REFERENCES dbo.[User](ID)
)
GO

As a possible answer, I've added a table value function to my view. But there might be much better solutions. I would be happy, if you could post your alternative ideas.

CREATE VIEW rpt.LoadingList WITH SCHEMABINDING AS
SELECT
    h.ID AS ID_Haul,
    h.DescriptiveID,
    ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY ccs.StoredOn ASC) AS Pos,
    cc.PickingNo,
    pin.PickingStorage,
    pin.TargetCountry,
    cc.[ObjID],
    cc.Customer,
    cc.Article,
    cc.Quantity,
    cc.QuantityUoM,
    cc.PackageLength,
    cc.PackageWidth,
    cc.PackageHeight,
    cc.PackageWeight,
    ccs.StoredOn,
    CAST(CASE WHEN h.Depature IS NULL THEN 0 ELSE 1 END AS BIT) AS Departured
FROM dbo.Haul h
CROSS APPLY dbo.GetAllChargeCarriersOnHaul (h.ID) ccs
INNER JOIN dbo.ChargeCarrier cc ON cc.ID = ccs.ID_ChargeCarrier
INNER JOIN dbo.PickingInfo pin ON pin.ID_ChargeCarrier = ccs.ID_ChargeCarrier

----------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[GetAllChargeCarriersOnHaul]
(
    @ID_Haul INT
)
RETURNS @returntable TABLE
(
    ID_ChargeCarrier INT NOT NULL,
    StoredOn DATETIME2(2) NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    -- Declare local variables
    DECLARE @ID_Storage INT;
    DECLARE @Arrival DATETIME2(2);
    DECLARE @Depature DATETIME2(2);

    -- Load haul data
    SELECT
        @ID_Storage = ID_Storage,
        @Arrival = Arrival,
        @Depature = Depature
    FROM dbo.Haul
    WHERE ID = @ID_Haul;

    -- Exit, if no data was found
    IF @ID_Storage IS NULL
    BEGIN;
        RETURN;
    END;

    -- Select actual data
    INSERT INTO @returntable
    SELECT
        ID_ChargeCarrier,
        MAX(StoredOn)
    FROM dbo.ChargeCarrier_Storage
    WHERE
        ID_Storage = @ID_Storage
        AND (
            @Depature IS NULL AND StoredOn >= @Depature OR
            StoredOn BETWEEN @Arrival AND @Depature
        )
        AND dbo.GetLatestStorageLocationInDateRange (ID_ChargeCarrier, @Arrival, @Depature) = @ID_Storage
    GROUP BY ID_ChargeCarrier;

    RETURN;
END;

----------------------------------------------------------------------------------

CREATE FUNCTION dbo.GetLatestStorageLocationInDateRange
(
    @ID_ChargeCarrier INT,
    @FromDate DATETIME2(2),
    @TillDate DATETIME2(2)
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN;
    RETURN (
        SELECT TOP 1 ID_Storage
        FROM dbo.ChargeCarrier_Storage
        WHERE
            ID_ChargeCarrier = @ID_ChargeCarrier
            AND (
                @TillDate IS NULL AND StoredOn >= @FromDate OR
                StoredOn BETWEEN @FromDate AND @TillDate
            )
        ORDER BY StoredOn DESC
    );
END;