texts: Migrating to Oracle Flashback for Historization on Oracle 11g

texts:
Our application built using Oracle 11g  requires that we keep historic data.

The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically we need to save some stuff 10 years, and we dig for old data a few times each year. The historical data is also used frequently during development and testing for debugging purposes.

Or homemade solution is to add an extra sequence number to the primary key, inserting a copy with a new seq-no when an existing row is "modified". We are using the latest seq-no as the actual "active" data in all queries.  Effectively we are doing a subselect or join on max(seq_no) + the other primkeys whenever we access a table.

id           varchar[8],  --pk
seq_no       number,      --pk
omg_ponies    varchar[42],

The performance is ...almost adequate, but we are seeing bigger data volumes, and a lot of queries are getting unbelievably complex as our application grows. It also hinders us from using JPA or Hibernate efficiently as the model gets messed up. We are solving that with views, but it has it's complications.

So how to handle historization? Oracle seems to recommend using Flashback and it looks like a Good Thing.

But how do we migrate, keeping our (10 year long) history? Ideally, only one row should be left for each id, with the old variants (lower seq_no:s) available in the flashback logs. Preferably with an proper "old" timestamp.

Furthermore, how do you handle refactoring? We see a lot of new business requirements, and occasionally we need to do major changes, like splitting a table into two, or other larger modifications caused by new requirements and features. Until now, we've been doing larger changes once or twice each year. How do we handle changes, while retaining or repopulating the history, again preferably with the proper "older" dates.

  Footnotes: We do not believe that we must leave historic entries "intact". It's OK to modify the logical model as long as we are not loosing track of changes and certain events.


texts: Sql get results only containing all keywords

texts:
Let's say I have mysql table called SONGS, with columns:

Artist | Featuredartist | Songname | Year

What I need is to get Artist and Songname, only containing my keywords (and no matter how they are sorted):

For example:

ACDC Metallica 2000 (should get me only results of Metallica featured ACDC (or reversed) and only of year 2000.

The same results I should get using query sorted another way, i.e: 2000 ACDC Metallica.

The same with 2 or one keywords, "Metallica ACDC", for example. It should get me results of songs by metallica feat. ACDC and ACDC feat Metallica, of all years.

I have tried to use UNION, but it gets me results containing one of keywords.

$search = "ACDC Metallica 2000"; // or Metallica ACDC or ACDC 2000, etc.
$sql = '';
$count = 0;
foreach($search as $text)
{
  if($count > 0){

     $sql = $sql."UNION Select * From songs WHERE artist LIKE '%$text%' and featuredartist LIKE '%$text%' and year MATCH '%$text%'";

  }else{

     $sql = $sql."Select * From songs WHERE artist LIKE '%$text%' or featuredartist LIKE '%$text%' and year MATCH '%$text%'";

  }
  $count++;
}

How could I achieve this?


texts: Does the sql server compare strings by hash values?

texts:
If two columns are in the same collation, it would make sense for big strings just to use a hash value in order to compare them and only if they are equal to compare them characterwise. Does the sql server proceed this way?
My guess would be rather not, because that would mean storing hash values that are usable for comparison within the same collation and that would use additional space. However Microsoft states

For single-byte encoding character sets such as Latin, the storage size is n bytes + 2 bytes

which negates the storage of any hash values.


texts: Trying to restore SQL file from mysqldump, results in syntax error

texts:
I ran the following sqldump call in a MySQL 5.7 instance (Windows OS):

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 --routines -A > ~/dump.sql

I copied the result in a MySQL 5.7 instance (Linux OS), and executed it via:

mysql -uroot -pMyPwd < dump.sql

But I got the following error:

  ERROR 1064 (42000) at line 813: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '917,0.31000000,267.21000000,'2015-07-08 17:44:39',88758,'BK1','BK1',49208,4921' at line 1

There are some characters in the error message that I haven't been able to copy, so I paste an image here:

So I open the (big) file in nano, and I find the fragment which has this weird characters:

...(49171,0.87000000,267.28000000,'2015-07-08 17:42:48',88671,'BK1','BK1',49167,49170,63,64,'BK11432145524','BK11432145533','xxxxxx',0,0.00000000,0.00000000,0.00000000,0.00000000),(^T9^R17,0.31000000,267.21000000,'2015-07-08 17:44:39',88758,'BK1','BK1',49208,49216,63,64,'BK11432145637','BK11432145661','xxxxxx',0,0.00000000,0.00^P00000,0.0000000^P,0.00000000),(4^Y^R^W6,0.19000000,267.14000000,'2015-07-08 17:47:02',88875,'BK1','BK1'...

As you can see, it seems the first value of a row seems to have a weird element ^T9^R17 instead of a normal integer like the row before (49171).

Tried to run dos2unix on the file as recommended by @Verace, and this is the result:

$ dos2unix dump.sql
dos2unix: Binary symbol 0x14 found at line 813
dos2unix: Skipping binary file dump.sql

Why is this happening? The type of the first column is bigint(20) NOT NULL.


texts:
I am using SQL Server 2014. I have the following statement:

SELECT CHARINDEX('C', 'ABCDEFGC', 3);

The result I get is 3 instead of 8. I observe the same behavior with different strings. Is this expected behavior of this function?

It seems fine if the last parameter is any other number.

Thanks


texts: Postgres: Merge database backup with development database

texts:
My database is quite large 50GB+ what I've always had to do when I want to sync my development database with production is download a backup of production, drop my development database then restore the production database on my development machine.

Is there any way of doing a (Sync/merge) where only new data is added?


texts: 150 External flat files used to create internal DB

texts:
I have a situation in which I must create an entire DB off of ~150 external txt files (pipe delimited) which all represent unique tables. Furthermore these txt files come in daily and need to be loaded (with correct types) into this internal DB. The files do have filenames that correspond with the table names and they also have header rows.

Creating 150 different flat file connection strings, data conversions, and data flow tasks in SSIS does not sound plausible. Thus I am considering doing a dynamic TVP load (a hybrid of this: What's the most efficient way to load a csv file into SQL Server 2016?) via c#, where I would dynamically set the table name and columns based on the metadata provided in the txt files, then I would pass these parameters along with the data (one row at a time leveraging a TVP parameter) and insert the data into the dynamically set table.

My question is, has anyone dealt with the task of ingesting an entire DB export via flat files and recreating it locally in their environment? If so, how did you do it?


texts: Is the max length configuration for nvarchar 0-indexed, e.g. If I declare column x as type nvarchar and set the max length to (2): (X nvarchar(2)) does that mean that the column takes 3 nvarchars? or only 2?


texts: Memory Grant Warning on what appears to be a better query?

texts:
OK, I was looking at potentially reducing this (as more of an exercise than a need mind you) and it seems that Im getting this warning on what appears to be a better performing query and not on the query that requires more memory to execute? Is this weird or am I just over thinking?

Anyone want to educate me here (sorry for the large images)?

1st query and hover over:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c
    INNER JOIN SCS.scs_auto_sra.scs_contracts cc ON c.icontract_id = cc.icontract_id ;

second query and hover over:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c
    INNER JOIN
        (
            SELECT icontract_id, scontract_holder_hphone
            FROM SCS.scs_auto_sra.scs_contracts cc
            WHERE EXISTS (SELECT icontract_id FROM #Claims x WHERE x.icontract_id = cc.icontract_id )
        ) cc ON c.icontract_id = cc.icontract_id ;


texts: How do I reduce the logging of my procedure?

texts:
I have a process that I run every night in our database to take some snapshots of various tables (not full table snapshots, just select record types).

Lately, it has been filling up the redo log and failing.
The statement is below.
Is there anything I can do to reduce the amount of logging this does?

BEGIN

  DELETE FROM DWH_STOCK_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SORD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SORDD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SOZD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_PORD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_PORDD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_POZD_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SPRICE_STYLE_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SPRICE_CLRSKU_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;
  DELETE FROM DWH_SKU_COSTS_SNAPSHOT WHERE CAPTURE_DATE < SYSDATE - 5;

  DWH_SNAPSHOT.TAKE_STOCK_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_SALES_ORDER_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_PURCHASE_ORDER_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_PRICES_SNAPSHOT();
  PDWH_SNAPSHOT.TAKE_COSTS_SNAPSHOT();
END;


texts: how to change wsrep_cluster_size in Percona XtraDB Cluste?

texts:
I am trying to setup Percona XtraDB Cluster according to their doc
When I run show status like 'wsrep%'; it's show wsrep_cluster_size is 1 in every node(2 node for me). Actually it should be 1 for node1 and 2 for node2. How can change it to work?


texts: What should I monitor on the SQL Server machine?

texts:
I need to create some alerts, logging jobs and monitors on a machine that is going to run SQL Server 2012 Standard for an ERP system.

The OS will be Windows Server 2008 R2 Standard/Datacenter (if it matters).

I am planning to implement: server performance monitors (processor time, load, memory used, reads/writes to disk, disk space).

I don't know what should I be logging in terms of the database/SQL Server instance. I thought about autogrowth operations on the database and transaction log files.
I guess I should be monitoring locks, transactions, connections and a few more things. But what exactly? Which activities should be logged only and which should be emailed to me ASAP?

I will greatly appreciate your advice as I'm a beginner in database administration.


texts: MySQL WHERE if NULL select another column


texts:
In SQL Server Extended Events, event = perfobject_logicaldisk, what is the actual unit of time for average_disk_seconds_per_read and average_disk_seconds_per_write?

All of the documentation I've found says "seconds", but my sample data includes values like these:  87708875, 1573358215, etc.

It's probably microseconds. I exported the target data to Excel and divided it by 1,000,000,000,000, which gives numbers that seem to be correct for microseconds, but I want confirmation before use such an xEvent query.


texts: Should I use Log Shipping, Replication, SSIS or something else

texts:
I have a BizTalk server Environment running and now the time has come that we've all experienced. People are asking for reports of the data flows and statistics on processing quantities. The data resides in a SQL server database where the tables have minimal indexing and in order not to incur any heavier costs for inserts and updates (which is the primary activity in those tables) and in order for us to customize the data a bit we've decided to have a separate application database to be the base of a web application we're setting up.

However, looking into the possibilities of copying the data from our prod. server to the applications db server gives quite a lot of options.

So, what should I opt for? Log shipping, replication, using SSIS to create some form of scheduled ETL or something else?

The basic requirements are:

Data should be in the application DB within minutes (real time not required)
The data in the application DB might not be identical in structure to the source table (i.e some columns might be missing, some others might be added)
The data should ONLY be added, no updates or deletions in the target even if the source database is purged.

I'm a bit at a loss here for a clear path forward, so your input is greatly appreciated.


texts: PostgreSQL installation error, how to fix?

texts:
help, i searched almost everywhere in the internet for this error, but i didn't find anyone with the same type that i got in my computer, i already tried 3 or 4 diferent versions, all with the same error, i'm running in Administrator mode AND i tried to use the Administrator user.
here's the error image: https://imgur.com/a/TbdJnte
and here's what i belive to be  the error log: https://pastebin.com/5phRm5Jp

Script stderr:

Configuring PostgreSQL as auto start
Executing C:\PostgreSQL/pgc config pg96 --autostart on
Script exit code: 1

Script output:
 Error running config-pg96

Script stderr:
 usage: usage: config-pg96.py [-h] [--port PORT] [--autostart={on,off}]
                  [--datadir DATADIR] [--logdir LOGDIR]
                  [--svcname SVCNAME]
config-pg96.py: error: argument --autostart: expected one argument

Error running C:\PostgreSQL/pgc config pg96 --autostart on: usage: usage:
config-pg96.py [-h] [--port PORT] [--autostart={on,off}]
                  [--datadir DATADIR] [--logdir LOGDIR]
                  [--svcname SVCNAME]
config-pg96.py: error: argument --autostart: expected one argument
Problem running post-install step. Installation may not complete correctly
 Error running C:\PostgreSQL/pgc config pg96 --autostart on: usage: usage:
config-pg96.py [-h] [--port PORT] [--autostart={on,off}]
                  [--datadir DATADIR] [--logdir LOGDIR]
                  [--svcname SVCNAME]
config-pg96.py: error: argument --autostart: expected one argument


texts: will replication from 5.5.20 to 5.0.XX server work?

texts:
I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?

Because I remember when I transferred data from 5.0 to 5.5.20, I had to run the command upgrade table to work on on 5.5.20?

Please suggest what the best possible way, if replication is not possible.


texts: mysql 5.1 -- unknown variable 'basedir=c:/mysql/'

texts:
I recently changed the default port(3306) to another for mysql.
I did it by changing the my.ini file and did restart of the server.
No other changes were made whatsoever.
When connecting using a GUI no problem.
When I try using command prompt (mysql -u root -p) I get the following error.

mysql: unknown variable 'basedir=c:/mysql/'

Running on
Windows Server 2008 R2
MySQLServer 5.1

my.ini FILE info (removed some commented lines)

[client]
port= new_port

##The MySQL server
Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/MySQL/"
#Path to the database root
datadir="C:/MySQL/"
#Set the default character set.
default-character-set=latin1

[mysqld]
#password=your_password
port = new_port
#socket = /tmp/mysql.sock
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size=4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size=201M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

#Path to installation directory. All paths are usually resolved relative to this.
basedir = "C:/MySQL/"

#Path to the database root
datadir = "C:/MySQL/Data/"

#The default storage engine that will be used when create new tables when
default-storage-engine=MyISAM

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=160

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=320

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=205M

#Enable this option only if you would like to use InnoDB tables.
skip-innodb
#Don't allow new user creation by the user who has no write privileges to the mysql.user   table.
safe-user-create
#Start without grant tables. This gives all users FULL ACCESS to all tables!
skip-grant-tables
#Set the default character set.
default-character-set=LATIN1

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

#If no specific storage engine/table type is defined in an SQL-Create statement the   default type will be used.
default-storage-engine=myisam
#Enable this option only if you would like to use InnoDB tables.
skip-innodb
#The number of simultaneous clients allowed.
max_connections=160
#The number of open tables for all threads.
table_cache=320
#If an in-memory temporary table exceeds this size, MySQL will automatically convert it  to an on-disk MyISAM table.
tmp_table_size=205M

#Don't allow new user creation by the user who has no write privileges to the mysql.user   table.
safe-user-create
#Start without grant tables. This gives all users FULL ACCESS to all tables!
skip-grant-tables

#Set the default character set.
default-character-set=LATIN1


texts: Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc

texts:
I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc.

There are a number of functions out there but the ones I found all have limitations, e.g. truncate the string to 8000 characters. e.g. (according to some of the comments):

SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why.

CREATE FUNCTION dbo.SuperTrimLeft(@str varchar(MAX)) RETURNS varchar(MAX)
    AS
    BEGIN
    IF (ASCII(LEFT(@str, 1)) < 33) BEGIN
        SET @str = STUFF(@str, 1, PATINDEX('%[^'+CHAR(0)+'-'+CHAR(32)+']%', @str) – 1, ' ');
    END;

    RETURN @str;
END;

So my question is, what is the best approach to accomplish the task above?


texts: How to force MySQL Workbench to use constraints in my file?

texts:
I'm currently working on a project that requires I write my own constraints for my database (to show I know how they work). I'm attempting to test them in MySQL Workbench however the programs settings keep kicking in and overriding the SQL in my document. As a test I am setting all DELETE's and UPDATE's to CASCADE however in the settings it is set to NO ACTION and I am getting error messages.

I am under the assumption that the above is the correct settings page to alter but there is no "DO NOTHING I WANT TO USE MY OWN SETTINGS" option. Is there a way around this? I am trying to emulate another environment which honours no constraints at all to ensure that mine as working.


texts: Query runs fast but occasionally never completes

texts:
I have messed things up at work (oh no!). Everything was running fine until about two days ago; queries executed instantly, and the database tended to sit around 20% load. Then I got told that some of our historical data in the database was entered incorrectly, and needed to be removed and re-entered from the following tables:

CREATE TABLE TrackerStates(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TrackerId] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Latitude] [float] NOT NULL,
    [Longitude] [float] NOT NULL,
    [Altitude] [float] NOT NULL,
    -- Some other fields
    CONSTRAINT [PK_dbo.TrackerStates] PRIMARY KEY NONCLUSTERED  (Id)
)

CREATE CLUSTERED INDEX IX_TrackerId_DateRecorded_Processed ON TrackerStates (TrackerId, DateRecorded, Processed)
CREATE NONCLUSTERED INDEX IX_TrackerId_Processed ON TrackerStates (TrackerId) WHERE (Processed = 0)

CREATE TABLE TrackingPoints(
    [DatabaseId] [int] NOT NULL,
    [TrackerStateId] [int] NOT NULL,
    [RoadId] [int] NOT NULL,
    [Distance] [float] NOT NULL,
    PRIMARY KEY CLUSTERED (DatabaseId, TrackerStateId),
    FOREIGN KEY (DatabaseId) REFERENCES RammDatabases (Id),
    FOREIGN KEY (TrackerStateId) REFERENCES TrackerStates (Id)
)
CREATE NONCLUSTERED INDEX IX_DatabaseId ON TrackingPoints (DatabaseId)
CREATE NONCLUSTERED INDEX IX_TrackerStateId ON TrackingPoints (TrackerStateId)

CREATE TABLE TrackingSegments(
    [DatabaseId] [int] NOT NULL,
    [StartingStateId] [int] NOT NULL,
    [EndingStateId] [int] NOT NULL,
    [EntityId] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED (DatabaseId, StartingStateId, EndingStateId),
    FOREIGN KEY (DatabaseId) REFERENCES RammDatabases (Id),
    FOREIGN KEY (StartingStateId) REFERENCES TrackerStates (Id),
    FOREIGN KEY (EndingStateId) REFERENCES TrackerStates (Id),
    FOREIGN KEY (DatabaseId, StartingStateId) REFERENCES TrackingPoints (DatabaseId, TrackerStateId),
    FOREIGN KEY (DatabaseId, EndingStateId) REFERENCES TrackingPoints (DatabaseId, TrackerStateId)
)
CREATE NONCLUSTERED INDEX IX_DatabaseId ON TrackingSegments (DatabaseId)
CREATE NONCLUSTERED INDEX IX_DatabaseId_StartingStateId ON TrackingSegments (DatabaseId, StartingStateId)
CREATE NONCLUSTERED INDEX IX_DatabaseId_EndingStateId ON TrackingSegments (DatabaseId, EndingStateId)

My first attempt to delete the data simply took far too long - it's historical data so there are a few million rows. After that failed, I asked around and someone suggested disabling check constraints, deleting, and then re-enabling the constraints for those specific tables (which I will NEVER do again, terrible idea). The disable and delete ran fairly quickly, I had to leave the enable running all night but it did succeed.

From then on, the database has been at 100% CPU, and one of the queries occasionally never completes. It's the same query every time:

SELECT TOP 500 * FROM TrackerStates WHERE TrackerId = @TrackerId AND Processed = 0 ORDER BY DateRecorded

This query uses the index IX_TrackerId_DateRecorded_Processed, and usually takes no time at all to execute. Occasionally though, a query will just sit there executing until it times out (after 30 seconds).

So far, I have tried:

Scaling the database up to 5 times the size (less timeouts, but still 100% CPU usage)
Rebuilding the indexes, as they were fragmented to about 80% (no difference)
Changing the query to isolation level READ UNCOMMITTED (no difference)

What could I possibly do to fix this?


texts: SQLNCLI11 for linked server "my linked server" returned message "Query timeout expired."

texts:
We have two separate sql servers. On one server we have a datawarehouse DWH, on the other we have sales information.

Now on the DWH server there is an ETL job that collects the information from the sales server. The job runs daily after midnight. The DWH collects the information via linked server from the sales database.

Now, the most time the ETL job runs without any problems. But sometimes it failes because of query timeout. We have found out, that there is a specific pattern: The failure happens every 11th day. So on the 11th day the ETL job fails to collect information.

We have searched everthing and could not found out what this issue causes. Also we know that the amout of data is everytime, almost the same.

Does anyone have a clue or any idea, where i can search for such a problem?


texts: msdb.dbo.sysjobhistory data gets deleted whenever SQL Server services are restarted

texts:
I am facing an issue where my msdb.dbo.sysjobhistory data is getting deleted whenever my SQL Server services are restarted or the instance fails over.

I am using SQL Server 2014 SP2 in a cluster environment, with below SQL Agent History setting:

Can anyone please suggest what the issue is?


texts: Will cleaning data cache help to resolve compiler memory pressure?

texts:
I currently experience RESOURCE_SEMAPHORE_QUERY_COMPILE  waits causing CPU spike 100% that lasts 10-20 mins and disappears only by completely stopping traffic.
I figured it could be a memory pressure and wondering if reducing indexes size will help with memory pressure.


texts: What accounts for a large single index size (other than bloat?)

texts:
I've been doing some digging into the size of our production PostgreSQL 9.6 database and found some results I thought were surprising.

We have a table (let's call it foos) with about 10 million records. The primary key is an integer. We have a B-tree index on this table for an optional foreign key into another table (let's call it bars). Let's call the index index_foos_on_bar_id. bars.id is just an integer data type column.

When I look at the index size using the \di+ meta command I see that it occupies somewhere in the neighborhood of 1GB of space. Some back-of-the-envelope math would imply that each entry in the index thus takes about 1GB / 10 million = 100 bytes of space per-row.

There's almost no deletion taking place on the foos table, so the bloat is non-existent.

In my mind, an index would effectively contain something like sorted pairs of numbers mapping the indexed column to the primary key of the relevant table. However, since it's only integer types, that would only use about 4 + 4 = 8 bytes per row, which is way off from the 100 bytes per row that's actually occupied. I guess the fact that it's a tree structure could bump that up slightly, but the over 10x difference was a bit of an eyebrow-raiser for me.

What accounts for all of this "extra" space being used by the index?


texts: Query only for the first n results of like

texts:
What I'm trying to accomplish is a huge speedup (read: incremental results) when searching a database with a like operator which is kind of a last resort, if everything else already failed, to come up with a solution to the given search.

What I'm doing right now is that I query mysql with jooq like:

dsl.select(re.ID).from(re).where(re.EXPLANATION.likeIgnoreCase("%" + contains + "%")).fetch()

which is equivalent to

SELECT ID from EXPLANATIONS where EXPLANATIONS.EXPLANATION like "%" + contains + "%"

but I really don't need the whole result (or well, not in the first step). The reason for the slow speed is that this database-table contains some 2 million records, and the EXPLANATION field is varchar(2000) which makes it kinda slow to query (there is however an FULLTEXT INDEX defined for this field).

My questions would be

how to, if possible, query only for the first n results of my query
If there is any possible INDEX I could define to make querys like this faster for the given circumstances.


texts: How to install oracle 11g XE and use the data files of another instance (like Postgres)?

texts: I installed Oracle 11g XE in Windows 7 x64, then Windows 8.1 (install new OS, not perform an upgrade of the previously Windows 7 x64 installation). Can be possible to install Oracle 11g XE over the existing directory to use the database that was created with Windows 7 like Postgres do when the Data Directory exists?


texts:
I have a full Oracle dump, I know I have to imp the dump with the fromuser touser clause.

However, I don't know the names of the schemas included in the dump.  How can I open the dump created with Oracle 10g just to check and analyze the content?


texts: Difference between inline view and WITH clause?

texts:
Inline views allow you to select from a subquery as if it were a different table:

SELECT
    *
FROM /* Selecting from a query instead of table */
    (
        SELECT
            c1
        FROM
            t1
        WHERE
            c1 > 0
    ) a
WHERE
    a.c1 < 50;

I've seen this referred to using different terms: inline views, WITH clause, CTE and derived tables. To me it seems they are different vendor specific syntax for the same thing.

Is this a wrong assumption? Are there any technical/performance differences between these?


texts:
From doing select * from pg_stat_activity;, I can see that some process is using a cursor.  The query column look like this:

FETCH FORWARD 50000 FROM "db"

Is there any way to see the SQL that was used to define "db"?


texts: If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why?

texts:
I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database:

create or replace function tail_f return varchar2_tab pipelined as
   n number;
begin

    loop
      exit when ...

      select count(*) into n from ... where ...;

      pipe row(sysdate || ' n= ' || n);
      dbms_lock.sleep(60);
    end loop;

    return;
end tail_f;

And then I'd like to select * from table(tail_f) in SQL*Plus.

In order to fetch the rows one by one, I SET ARRAYSIZE 1. Yet, the records (except the first one) are fetched in pairs.

Is there an explanation for this and how can I get the records as soon as one is piped?


texts: How much does an ordered query of an attribute costs in terms of operations when the attribute is a key or an attribute?

texts:
How much does the following query costs in terms of operations when the attribute is a key or an attribute ?

SELECT Attribute
  FROM R
    WHERE Attribute = 50
      ORDER BY Attribute

We assume we have R(K,A,B,other) a relation with

Nrec(R)=100000,
Lr=100 byte
K, a key in    [1;100000]
A with integer values uniformly distributed in the range (1,1000)

R is stored with heap organization with data unsorted with both respect to K and A, in pages of size Dpag=1024 bytes each.

I have an array giving me the following costs for heap organizations :

My attempt :

I know that

SELECT Attribute
  FROM R
    WHERE Attribute = 50

Is an equality search. As far as there can be several records with this attribute, with an attribute search it costs Npage(R). Yet with the Key search I've been told to follow the given array (without understanding why...)...

But I don't know what does ORDER BY induces

Tips from my teacher

1. Attribute search

It should be the same costs for the attribute search as far as

RC = Csearch + Cwrite + Csort + Cread

Yet I merely understand this equation...

2. key search

"we need to estimate the number of bytes to store Erec" said he. where Erec = Nrec(R)/Lr= 100000/100 = 100. (I think there is a mistake here as far as first 100000/100 = 1000 and second it shouldn't have been Lr as the denominator but the number of files that actually have the attribute we are looking for => 100000/1000 = 100)

Furthermore, the size of Erec = 100 x 4 = 400 <1024. We would only need one page to order Erec. (Yet, I don't understand why is it multiplied by 4.)

References

This question comes more or less from a book written by Dario Colazzo and some others from Pisa University Relational DBMS Internals available in the link.


texts: Permissions in triggers when using cross database certificates

texts:
I use cross database certificates (as explained by Erland Sommarskog) to control access to a certain database in my environment ( SQL Server 2008 R2 ).

I have stored procedures in database A that update tables in database B.  This has always worked for a variety of stored procedures in db A and tables in db B until now.  I am trying to update a table in db B, but the table has a trigger on it.  This trigger is inserting additional data in another table in db B.  I am getting the error:

  Msg 916, Level 14, State 1, Procedure table_trigger, Line 11 The server
  principal "sql\login" is not able to access the database
  "B" under the current security context.

I tried granting insert permissions for database B user that is tied to the certificate to insert into that other table, but it did not resolve the error.  Do I have any options other than changing the trigger so it uses WITH EXECUTE AS OWNER?

Here is DDL to replicate the issue:

CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE DATABASE A;
CREATE DATABASE B;

USE A;

CREATE TABLE dbo.SPtoUpdate
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    );
INSERT INTO dbo.SPtoUpdate
        ( ID , ILoveFishing )
VALUES  ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch');
CREATE TABLE dbo.TriggerToInsert
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    , ChangeDate DATETIME2
    );
GO

CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate
    FOR UPDATE
AS
    DECLARE @datetime DATETIME2;
    SELECT  @datetime = GETDATE()

    INSERT  INTO dbo.TriggerToInsert
            ( ID , ILoveFishing , ChangeDate )
    VALUES  ( 1 , 'Yes' , @datetime );
GO

CREATE CERTIFICATE BExecutor
   ENCRYPTION BY PASSWORD = 'Obfuscated'
   WITH SUBJECT = 'Execute sp from B to A',
   START_DATE = '20140101', EXPIRY_DATE = '20300101'
GO

BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

CREATE USER BExecutor FROM CERTIFICATE BExecutor

GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor
GRANT SELECT ON dbo.SPtoUpdate TO BExecutor
--Also give insert on dbo.TriggerToInsert
GRANT INSERT ON dbo.TriggerToInsert TO BExecutor

USE B
GO

CREATE USER [GuggTest] FOR LOGIN [GuggTest];
EXEC sp_addrolemember N'db_owner', N'GuggTest'
GO

CREATE PROCEDURE dbo.UpdateTableInA
AS
    BEGIN
        UPDATE  A.dbo.SPtoUpdate
        SET     ILoveFishing = 'Walleye'
        WHERE   ID = 2;
    END

GO

CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
GO

ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor
    WITH PASSWORD = 'Obfuscated'
GO

--Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA


texts: SQL Server 2012 installation silently fails

texts:
I am trying to install SQL Server 2012 running setup.exe on 64-bit windows 8. After the "please wait" dialog, the installation center comes up. I chose "New SQL Server stand-alone installation", getting a new "please wait" dialog and then this window:

I press ok and comes to a dialog where the product key is input, I press next and accept the license terms, next again, leaves as it is, press next, the installation seems to start.

This should happen according to the text in the window: Scan for product updates, download setup files, extract setup files, install setup files.

After a while, the window dissapears and I get no message.

Nothing seems to be installed.

What really happened?


texts: Hide column values for some rows in auto-updatable views while retaining the "write" capability for them in PostgreSQL

texts:
Suppose i have a view defined like this

create or replace view clients as
    select
    id, name,
    case app_user_type() -- hide column content for everyone except the administrator
        when 'administrator' then address
        else null
    end as address
from data.clients

The purpose is to hide the contents of the address column in some cases (current_user <> 'administrator').

The problem is that the "address" column becomes readonly.
Is there a way to have the same effect (hide the column value) while retaing the updatable property?


texts:
According to documentation, Amazon Redshift is derived from PostgreSQL 8.

PosgreSQL 8 supports Multiversion Concurrency Control (MVCC) which is in my understanding achieved on physical level by appending new row versions rather than updating in place or marking rows for deleting rather than deleting rows in place. Advantages of MVCC are:

  MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading. PostgreSQL maintains this guarantee even when providing the strictest level of transaction isolation through the use of an innovative Serializable Snapshot Isolation (SSI) level.

Redshift documentation states that

  the specialized data storage schema and query execution engine that Amazon Redshift uses are completely different from the PostgreSQL implementation

Also, unlike Postgres which has row-based storage, Redshift is column-based store.

At the same time, Redshift has vacuuming which makes it similar to PostgreSQL. From Redshift documentation:

  Amazon Redshift does not automatically reclaim and reuse space that is freed when you delete rows and update rows. To perform an update, Amazon Redshift deletes the original row and appends the updated row, so every update is effectively a delete followed by an insert. When you perform a delete, the rows are marked for deletion, but not removed. The query processor needs to scan the deleted rows as well as undeleted rows, so too many deleted rows can cost unnecessary processing. You should vacuum following a significant number of deletes or updates to reclaim space and improve query performance.

In essence, this is similar to underlying MVCC mechanisms in Postgres. However, I wasn't able to find confirmation that Redshift supports MVCC. What I am looking for is confirmation that parallel read and write transactions on Redshift do not interfere because they operate on independent versions of rows, therefore the question:

Does Amazon Redshift support MVCC?


texts: Migrating SQL Server tables data from one application to another totally different application tables

texts:
We are migrating a few tables' data from one application to another application. These two applications are totally different tables and schema.

We have created general T-sql scripts and temporary tables to store the required data to transform the data in an acceptable format of destination application tables.

Checking here, is there any different approach that we can adapt to migrate the data between the different applications?

Thanks in advance,
Venkat


texts: Postgres How to securely initialize the database?

texts:
I'm initializing a Postgres DB and would like to know what is the best way to do it.
Objectives:

We would like to use default database (postgres) unless there's a strong reason not to do.
We would like to use default schema (public) unless there's a strong reason not to do.
We would like to apply the Principle of Least Privilege.
No one (apart from those created below) should be able to anything in the DB.

One thing I know, public is public, fix it:
REVOKE ALL ON DATABASE postgres FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;

Is it enough? Did I miss anything? Do I need to revoke anything else?
I read somewhere, creating some object automatically grants some privilege to PUBLIC (sorry for too many somes, I hardly remember the exact information). Should I be worried about this?

And then I create some roles and grants appropriate privileges:
-- Creating DB admin user to be used only by Flyway
CREATE USER db_admin WITH PASSWORD passwd CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE postgres TO db_admin;
GRANT ALL ON SCHEMA public TO db_admin;
GRANT ALL ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO db_admin;

-- Creating DB application users with read/write permissions
CREATE ROLE db_writers;
GRANT CONNECT ON DATABASE postgres TO db_writers;
GRANT USAGE ON SCHEMA public TO db_writers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_writers;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_writers;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_writers;

-- Must run it as db_admin
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_writers;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO db_writers;

CREATE USER db_writer WITH PASSWORD passwd IN ROLE db_writers;

-- Creating DB application users with read-only permissions
CREATE ROLE db_readers;
GRANT CONNECT ON DATABASE postgres TO db_readers;
GRANT USAGE ON SCHEMA public TO db_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_readers;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO db_readers;

ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT ON TABLES TO db_readers;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT ON SEQUENCES TO db_readers;

CREATE USER db_reader WITH PASSWORD passwd IN ROLE db_readers;

Any comment on the above?
I couldn't make it working with db_admins role and db_admin user. Everytime db_admin creates some object, I have to change the object owner to db_admins. I don't like it. (If there's a better way to do it, it'd be awesome. If not, we're good with one user without any role.)


texts: Most efficient way to update a column where Id exists in another table

texts:
I have several tables which correspond to fiscal_years (2017, 2018, 2019, 2020, etc). Each of these tables contain a record_id and an owner_id - with the record_id being unique within a given year. Each of the fiscal_year tables contain 6-8 million rows, and the owners table contains 1 million rows. Each of these fiscal_year tables contain a process_date field meant to represent the date when the owner_id was added/found/moved.
I have created a dynamic sql function that can retrieve the owner_ids unique among all of these tables and put them into an owner table - this part works fine and takes appx. 5 min to complete.
However, I am struggling to update the source table's (fiscal_years) process_date field if its owner_id exists in the owner table.
I have tried this:
update fiscal_year y
set process_date = now()
where exists (select owner_id from owners o where o.owner_id = y.owner_id)
and process_date is null;

But predictably, this query ran for 2 hours before I stopped it.
I also tried to use an etl tool to update the fiscal_year table using owners.owner_id as a key. But this method never finished either - presumably because there are many owner_ids that don't exist in each fiscal_year table.
How can I update the fiscal_years table when the owner_id is present in the owners table?
Any help on this would be greatly appreciated.


texts: SQL Schema Change; Modification of Existing Table vs Creation of New

texts:
We are planning the data schema for our project. Currently we have a table with classifications, but there is the possibility of a new classification in the future which would require a new field to store associated data.

Is it better to extend an existing table or create a child table with a FK reference to the existing table? I assume creating a child table with a FK to the existing table is the better option because the existing database schema remains stable.

We're committed to a relational database so an advice on choosing NoSQL is not what we're looking for.
Keep in mind that the changes aren't expected to be frequent.


texts: Does innodb_flush_log_at_trx_commit = 1 wear out SSDs more than innodb_flush_log_at_trx_commit = 2?

texts:
I am running a Nextcloud instance and I suffer from a slow sync when a lot of small files are synchronized. I found out that innodb_flush_log_at_trx_commit = 1 is responsible for this so I was forced to set innodb_flush_log_at_trx_commit = 2. Now I am thinking about moving my database to an SSD. And I wanted to confirm that I am indeed running into an access time issue.

Is the same amount of data written to the disk when innodb_flush_log_at_trx_commit is set to 1 as if it was set to 2?


texts: How to find the actual space being consumed by indexes on a table in Oracle?

texts:
I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be considered.) I want the used bytes not the allocated bytes.

Can you help me in going ahead?

Also, is there a way to find the actual size of a long field in a table.

PS: vsize() and dbms_lob.getlength() do not work.


texts:
I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view isn't possible when other objects depend on it.

  ERROR:  cannot drop materialized view latest_charges because other objects depend on it

It also appears from the documentation that the REPLACE keyword isn't valid for a materialized view.  Is there any shortcut aside from dropping all dependent objects and rebuilding each one?


texts: Failure to log in using just the server name

texts:
I have more than one PC connected by LAN that I access from Server Management Studio.

When I try to connect with the DB server, if I place the server IP instead of the server name in the serverName field, it works fine, and I can log in.

If I use the server name, why does logging in fail? How can I resolve this issue?


texts: sp_msforeachdb in a stored procedure does not work well

texts:
I'm facing strange behavior.

Here's how to reproduce: in an instance with multiple databases (SQL Server 2016 in my case), create that stored procedure (in master in my case):

create procedure dbo.Strange WITH EXECUTE AS owner
as
begin
    exec sp_MSforeachdb 'select''?''';
end;

Then call that stored procedure with:

Exec master.dbo.Strange

As you will see, it will only return data for some of the databases but not all (3 out of 11 in my case).

What is happening?


texts: Importing tablespace files - Length mismatch error for DATETIME field

texts:
We are currently trying to import some old historic data tables. Unfortunately, the data is in exported tablespace files (.ibd and .cfg) - there are no dump files available and the server they came from no longer exists.

There are separate tables for each month of data, but they should all have exactly the same format. We have successfully imported the more recent files (with e.g. ALTER TABLE sqlt_data_1_2017_01 IMPORT TABLESPACE), but all the remaining idb files are failing with the following error:

SQL Error [1808] [HY000]: Schema mismatch (Column datevalue length mismatch.)

The datevalue column is a DATETIME.

It's possible that different versions of MySQL were used for creating the various tables and also for exporting the table spaces, but we are 99% sure that the starting version was 5.7.

Some of the tables have .frm files, so we compared a few with mysqlfrm --vvv -diagnostic and noticed that some of the datevalue columns have field_length 26 and some have field_length 19. Also, the stringvalue is VARCHAR(255) but the field_length is 762, although I suspect this is because it's in UTF8.

All the tables we can import have MYSQL_VERSION_ID 50722 and datevalue field_length 19
All the tables we can't import have MYSQL_VERSION_ID 50714 and datevalue field_length 26

Did something change with the DATETIME storage format at some point? What can we do to import these tables?

Thanks.


texts: Should SQL Server 2000 throw an error if update statement fails within a SP?

texts:
I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was committed.

Below is a condensed version of the code. I've removed a lot of details and proprietary information, but this is the gist of it. This code has been running daily for several months and producing the expected result, except for once last week, where the statement:

UPDATE /*app table*/
   set dateprocessed = @currentDate
 where jobname = 'Job_'
       + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '')
       + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4)
       + '_'
       + (SELECT Description FROM #tempTable1 WHERE id = @i)

...apparently did not actually update the referenced table, however the rest of the code executed and the transaction was committed.

If the update statement didn't complete sucessfully, should it have been caught by my @@ERROR check?

DECLARE @startTime DATETIME,
        @procedureName NVARCHAR(50),
        @i INT,
        @currentDate DATETIME,
        @minimumLetterCount INT;

SET @startTime = GETDATE();
SET @procedureName = (SELECT OBJECT_NAME(@@PROCID));
SET @i = 1;
SET @currentDate = GETDATE();

BEGIN TRANSACTION
    IF (--constraints are met)
    BEGIN
        IF OBJECT_ID('tempdb.dbo.#tempTable1') IS NOT NULL
            DROP TABLE #tempTable1;

        IF OBJECT_ID('tempdb.dbo.##globalTempTable1') IS NOT NULL
            DROP TABLE ##globalTempTable1;

        CREATE TABLE #tempTable1 (
            --columns defined here
        );

        CREATE TABLE ##globalTempTable1 (
            --columns defined here
        );

        INSERT INTO #tempTable1
            EXEC /*SP name*/

        --Process pending items by ID
        WHILE (@i <= (SELECT COUNT(*) FROM #tempTable1))
        BEGIN
            DECLARE @CurrentItemID VARCHAR(3);
            DECLARE @CurrentJobName VARCHAR(100);
            SELECT @CurrentItemID = (SELECT LetterID FROM #tempTable1 WHERE id = @i);
            SELECT @CurrentJobName = 'Job_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) + '_' + (SELECT Description FROM #tempTable1 WHERE id = @i);
            EXEC /*SP name*/ @TableID = @CurrentItemID, @ThroughDate = @currentDate, @JobName = @CurrentJobName, @SeedItem = 1;

            INSERT INTO ##globalTempTable1
            SELECT --some data from other tables into this temp table

            UPDATE /*app table*/ set dateprocessed = @currentDate where jobname = 'Job_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) + '_' + (SELECT Description FROM #tempTable1 WHERE id = @i)

            UPDATE l
                SET l.LinkedValue1 = lb.LinkedValue2
                FROM ##globalTempTable1 l
                INNER JOIN (SELECT UniqueID, SUM(CAST(REPLACE(REPLACE(REPLACE(REPLACE(value, '$', ''), ',', ''), '(', ''), ')', '') AS DECIMAL(12,2))) AS LinkedValue2 FROM ##globalTempTable1 GROUP BY UniqueID) lb ON l.UniqueID = lb.UniqueID;

            SET @i = @i + 1;

        END

    END

IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION

        --log failure
        INSERT INTO JobLog (jobName, success, startTime, endTime) VALUES (@procedureName, 0, @startTime, GETDATE());
    END
ELSE
    BEGIN
        COMMIT TRANSACTION

        --log success
        INSERT INTO JobLog (jobName, success, startTime, endTime) VALUES (@procedureName, 1, @startTime, GETDATE());
    END

--Cleanup temp tables
IF OBJECT_ID('tempdb.dbo.#tempTable1') IS NOT NULL
    DROP TABLE #tempTable1;

IF OBJECT_ID('tempdb.dbo.##globalTempTable1') IS NOT NULL
    DROP TABLE ##globalTempTable1;


texts: How to reduce table duplication, while ensuring integrity of the database?

texts:
For example, in my app I have some class called Contact:

case class Contact(name: String, email: String, phone: String)

And several other classes have one-to-many relation to that contact (for example, there are Companies and Houses).

Currently, I have database table for Contacts set up as something like this:

CREATE TABLE `Contacts` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `entityId` bigint(20) NOT NULL,
 `entityType` tinyint(4) NOT NULL,
 `name` varchar(300),
 `phone` varchar(300),
 `email` varchar(300),
  PRIMARY KEY (`id`)
);

where entityType is storing the "code" that determines the entity type this contact belongs to (1 for Company, 2 for House, etc), and entityId points to the row in Companies or Houses table. Companies and Houses tables are defined as follows:

CREATE TABLE `Companies` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `Houses` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `address` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

So, when I need to get all contacts for some Company (which has id = 1234 in this example), I execute the following query:

select * from Contacts c where c.entityType = 1 and c.entityId = 1234;

Now, I understand that this is awful - at the very least, I can't create a foreign key on entityId, thus I can't ensure that my data is consistent.

The alternative will be to create a separate Contacts table for each entity type (CompanyContacts, HouseContacts, etc) but that will result in lots of duplication in both database and application code.

Is there a better way to do this?


texts: How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?

texts:
I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro.

I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into Visio, but it seems that Access understands an SQL dialect which is different from Oracle, so I would need to tweak every Create Table command to change the data types.

Is there a simple way to create an ERD using DDL in Visio Pro 2007?


texts: How can I write an "on-update" trigger which takes a table-name as argument?

texts:
NOTE: The following example is a strongly simplified code, but it illustrates the task in a reproducible example. Looking at this example may make you think "why do it like this"? In reality, the full task is to store an audit-trail for multiple tables, but only if certain conditions are met. The condition is the same for each table (they each share some columns like inserted, updated and so on). So the code when to store an audit-trail is the same for each table. But the actual columns to be copied are different each time. I weant to create a trigger which handles this dynamically such that I don't need to touch it each time the schema changes.

Consider the following working example (question below). This demonstrates a simple schema where every update in table data causes the old values to be moved into data2:

DROP TABLE IF EXISTS data CASCADE;
DROP TABLE IF EXISTS data2 CASCADE;
CREATE TABLE data (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);
CREATE TABLE data2 (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated = NOW();
        INSERT INTO data2 VALUES (OLD.*);
        RETURN NEW;
    END;
    $$ language 'plpgsql';

CREATE TRIGGER update_trigger
    BEFORE UPDATE ON data
    FOR EACH ROW
    EXECUTE PROCEDURE update_trigger_func();

SET client_min_messages TO 'debug';
INSERT INTO data (name) VALUES ('foo');
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'bar';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'baz';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

Notice that the function update_trigger_func has the "history" table name hard-coded as data2 on the line which reads:

INSERT INTO data2 VALUES (OLD.*);

If data2 was an argument, this function could be reusable for other tables as well. But so far I failed to find the right incantation. I've tried the following two versions so far:

INSERT INTO TG_ARGV[0] VALUES (OLD.*);

But this causes a syntax error:

psql:temptable.sql:28: ERROR:  syntax error at or near "VALUES"
LINE 11:         INSERT INTO TG_ARGV[0] VALUES (OLD.*);

So alternatively I tried with dynamic SQL:

sql := 'INSERT INTO' || TG_ARGV[0] || 'VALUES (OLD.*)';
EXECUTE sql;

But that fails because the OLD variable is not available in the execution context:

psql:temptable.sql:58: ERROR:  missing FROM-clause entry for table "old"
LINE 1: INSERT INTO data2 VALUES (OLD.*)
                                    ^
QUERY:  INSERT INTO data2 VALUES (OLD.*)
CONTEXT:  PL/pgSQL function versioned_update() line 11 at EXECUTE

Given that I would like to use this trigger-function on other tables I can't hard-code the column names. How could I achieve this?


texts: Select the latest changed row in a table

texts:
I have a Mysql table test with the fields id, changedate, name.

some names have multiple changedates.

I can select all rows with

SELECT id, changedate, name FROM `test` ORDER BY changedate;

How can I select only the newest rows of that table for each name?


texts: Selecting values from one table which don't exist in the other

texts:
I have two tables, one containing a list of all orders and another containing a list of orders that have been invoiced.

I have the following query which I'm testing in SQL Server Management Studio, but it's not returning the correct data. I just want to see all orders that haven't been invoiced. E.g. Any order that's not in the invoicing table. Both the Job Number and Sub Job Number are crucial. These are known as VixJobSite and VixJobSub in the invoicing table.

SELECT
VixJobSite,
VixJobSub,
dbo.JOBCOSTING_JOBS.JOB_NUMBER,
dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER,
SummaryRef,
dbo.SERVICERECORDS_CALL_OUTS.DATE_REQUIRED

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    '\\nemesis\O\X\Invoicing Database\BackEnd\TestInvoicing.mdb';
    'admin';'',tblSummaryInfo),dbo.SERVICERECORDS_CALL_OUTS,dbo.JOBCOSTING_JOBS,dbo.SALES_ACCOUNTS
WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO =       dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
and not exists
( SELECT *
  FROM JOBCOSTING_JOBS,SERVICERECORDS_CALL_OUTS,SALES_ACCOUNTS
  WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
)


texts:
I have a table with hundreds of thousands of records. How to partition the table, week-wise?

A few million rows insertion will be taking place in my table in a day.So i need to partition it weekly. There is a datetime column which I want to partition by weekly


texts:
Im trying to create referals system. I am doing that by passing into during database every registaration all referals (first, second and third "stage").

to clarify, if user ID 3 registers from ref. of user ID 2, and user ID 2 was registered from ref of ID 1, the values for user ID 3 would look like:

`r1` = 2, `r2` = 1, `r3` = NULL

To read from database who is registered using some id's ref link with whole path, I figured out this query:

SELECT `sub`.`id` AS first,`sub2`.`id` AS `second`,`sub3`.`id` AS `third`

FROM `users` AS `usr`

JOIN `users` AS `sub` ON `sub`.`r1` = `usr`.`id`
JOIN `users` AS `sub2` ON `sub2`.`r2` = `usr`.`id`
JOIN `users` AS `sub3` ON `sub3`.`r3` = `usr`.`id`

WHERE `usr`.`id` = :CurrentUserId ;

(:CurrentUserId is PDO placeholder for PHP for int value)

Although, its problematic, as it works only for accounts with all 3 refers into past, so people with only first line or first and second line, but without third will not be listed. In other words, that would mean, if there is user, that has following values

r1 = 2, r2 = 1, r3 = NULL

is not listed. It works if r3 is equal to something.

I could work it around by sending 3 queries to database, but I am sure, that there is better way.

(Im not very expirienced with databases)


texts: Problems with Differential Backup CheckpointLSN

texts:
I’m trying to get my head around how LSNs and Differential Backups work. I’ve read a few articles on here that explain some things that have helped but there is still a missing gap for me.

I get that a differential backup is slightly different to the logs in that all it holds are the extents that have changed since the last full backup and a tiny bit of log to ensure the data is consistent. No problems, I get that. I’m trying to work out how I identify the subsequent logs that need to be applied after that differential.

Below is a cut-down table of some backup entries. The checkpoint_lsn of the diff and transaction log do not match so that doesn’t seem to be the same, neither do the first and last lsn values. My current thinking is the next log to apply after a diff is one where the last_lsn of the diff is contained with the first_lsn and last_lsn value of the log. Is this correct or have I totally pulled that out of my bottom and got the wrong end of the stick here?
I totally understand lots of people use time stamps on their backup files to do this, yup that can work, but… I just need to understand what is going on and why to help me get a good nights sleep.


texts: Count days in date range but only for a specific year within the date range

texts:
I have several date spans in a SQL Server table.
I am trying to count the number of days in the date span for a given year in the date span.
For all my examples I am trying count the numbers of days in 2019 that the date span covers.
I have the below code which calculates the total days in the date range but is there a way I can just get a count of the days within that date range for 2019 only?
The first date span below covers all of 2019 so I'm hopping to return a number around 365.
The second date span covers only 30 days in 2019 so I would like to have the query return 30.
SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20191231 AS VARCHAR(30)) AS DATE)) AS [Days]

SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20190130 AS VARCHAR(30)) AS DATE)) AS [Days]


texts: SQL Azure "hot" table query slowdown over time

texts:
I have an Azure SQL Database table that is acting as a "prioritized queue". As such, it gets heavily written to and deleted from over time. What's happening is that after a week or two, the DB size increases. CPU increases and performance reading from this slows down almost to a halt.

If I check the count, it's zero. If I check the table size, there are a lot of pages assigned to this table.

If I TRUNCATE the table, the count is still zero but the table size is back to minimal levels and performance is acceptable again.

Obviously, to me this indicates that the table has a lot of pages due to the high insert/delete activity and this is why the DB size increases. This is fine as when Azure cleanup occurs (presumably transaction log backups), the DB size drops back to normal levels.

What I don't understand is why the performance drops off so drastically when there are still zero rows in the table.

Can someone provide some suggestions as to what may be going on?

NB: The logic dictates that the overall write is atomic; i.e. update business data and write to the queue. Additional logic dictates that if an item (identified via business key) exists then overwrite queue item.

The Table is defined thus:

CREATE TABLE [dbo].[PrioritizedQueue](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Region] varchar(10) NOT NULL,
    [ItemId] varchar(10) NOT NULL,
    [Priority] tinyint NOT NULL,
    [ReceivedTimestamp] datetime2(7) NOT NULL,
    [Operation] varchar(7168) NOT NULL,
    [Content] varchar(max) NOT NULL,
 CONSTRAINT [PK_IndexerQueue] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
CREATE UNIQUE INDEX [IX_PrioritizedQueue_ItemId] ON [dbo].[PrioritizedQueue]
(
    [ItemId] ASC,
    [Region] ASC
)
INCLUDE ([Priority])
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
CREATE CLUSTERED INDEX [IX_IndexerQueue_Priority] ON [dbo].[PrioritizedQueue]
(
    [Region] ASC,
    [Priority] ASC,
    [ReceivedTimestamp] ASC
)
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

FYI: The "content" field is a varchar(max) due to history, it now only contains a file pointer so AFAIK the row should never need to use data pages off the current page

The Stored proc that reads from the table, and appears to have all the problems is:

CREATE PROCEDURE [dbo].[usp_indexer_dequeue]
    @batchSize AS int,
    @region varchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    DECLARE @deletedbatch table
    (
        [Operation] varchar(7168) NOT NULL,
        [Content] varchar(max) NOT NULL,
        [ItemId] varchar(10) NOT NULL
    );
    WITH cte AS
        (
            SELECT TOP(@batchSize) [Operation], [Content], [ItemId]
            FROM [PrioritizedQueue] WITH (rowlock, updlock, readpast)
            WHERE [Region] = @region
            ORDER BY [Priority], [ReceivedTimestamp] ASC
        )
    DELETE FROM cte
    OUTPUT deleted.*
    INTO @deletedbatch;
    SELECT * FROM @deletedbatch
END
GO

This proc is designed to fetch the top available N rows (typically this 25 rows) return them to the app and then delete from the table. As it runs as one of many processes calling this at once, it uses (rowlock, updlock, readpast) to bypass anything currently locked.


texts: Counting number of visitors who did NOT log in

texts:
I'm trying to calculate the number of visitors to the homepage of my site who did NOT then log in to the member's section of the site, while also filtering bots and admin users. Bots and admins are marked as such in the table by a separate script.

The way I'm currently doing it is counting all distinct sessions (accessid) without a logged in user (userid = 0), then counting all the distinct sessions with a logged in user (userid > 0), and finally excluding the logged in sessions from the non-logged in sessions and counting that result.

Here's the query that does the above:

SELECT Count(DISTINCT accessid) AS c
FROM   accesslog
WHERE  date = '2014-01-21'
   AND userid = 0
   AND is_robot = 0
   AND is_admin = 0
   AND url = 'www.example.com/'
   AND accessid NOT IN (SELECT DISTINCT( accessid )
                        FROM   accesslog
                        WHERE  DATE = '2014-01-21'
                           AND userid > 0
                           AND is_robot = 0
                           AND is_admin = 0)
ORDER  BY c DESC

This query can take from 1-7 seconds depending on the traffic on a given day. Is there any way I can speed up this query?

Here's the explain output for the above query:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY accesslog   ref date,userid,dateaff,url,dateaccessid,userid+accessid,is_robot,is_admin  date    3   const   6113    100.00  Using where; Using temporary
2   DEPENDENT SUBQUERY  accesslog   index_subquery  accessid,date,userid,dateaff,dateaccessid,userid+accessid,is_robot,is_admin accessid    32  func    2   100.00  Using where


texts:
Suppose I have following table -

CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER);

I want to aggregate the data by calculating average of every 10 points in the table.

i.e. If table has 20 data points the result is two aggregate points. 1st aggregate point the average of 1-10 data points, and 2nd of 11-20.

Is this possible using a SQL query?


texts: Performance about this simplified where-condition query

texts:
I have a query in SQL Server 2008 like this:

SELECT
        a.type,
        a.name,
        a.startDate,
        a.endDate,
        b.key          AS key,
        b.is_locked    AS is_locked,
        NULL           AS idx,
        NULL           AS page_count
FROM
    jobs a LEFT JOIN gates b ON a.pk_job = b.fk_job

WHERE
    a.status IN (0,1,7)
    AND a.startDate > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
    AND a.type in (15,17,19)

UNION ALL

SELECT
        a.type,
        a.name,
        a.startDate,
        a.endDate,
        NULL           AS key,
        NULL           AS is_locked,
        c.idx          AS idx,
        c.page_count   AS page_count
FROM
    jobs a LEFT JOIN pages c ON a.pk_job = c.fk_job

WHERE
    a.status = 5
    AND a.startDate = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
    AND a.type in (15,17,19)

I simplified the query into like this:

SELECT * FROM
(
    SELECT
            a.type,
            a.name,
            a.startDate,
            a.endDate,
            b.key          AS key,
            b.is_locked    AS is_locked,
            NULL           AS idx,
            NULL           AS page_count
    FROM
        jobs a LEFT JOIN gates b ON a.pk_job = b.fk_job

    WHERE
        a.status IN (0,1,7)
        AND a.startDate > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

    UNION ALL

    SELECT
            a.status,
            a.name,
            a.startDate,
            a.endDate,
            NULL           AS key,
            NULL           AS is_locked,
            c.idx          AS idx,
            c.page_count   AS page_count
    FROM
        jobs a LEFT JOIN pages c ON a.pk_job = c.fk_job

    WHERE
        a.status = 5
        AND a.startDate = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
) t
WHERE t.type IN (15,17,19)

I would like to know if the simplified query has better performance or not than the original one?

Note: if there is syntax error, please let me know in the comment section because I typed this query by my hand.

A bit of additional OOT question:
What is the english term for the process of simplifying an expresion or logic like above which by reducing the the two same where condition (t.type IN (15,17,19)) into one ?

Another example is like this:

R = (x+2)/2 + (x+3)/2

you can simplify it by removing the duplicate "/2" become one like this:

R = ( (x+2) + (x+3) ) / 2

You can answer the OOT question in the comment section.

Thanks in advance.


texts:
I'm trying to import data from a CSV file on a PostgreSQL 9.2 server, but I get errors when doing that.

Pgadmin:

ERROR: Cannot execute COPY FROM in a read-only transaction

Why am I getting that error if the DB is not read-only?

psql console:

ERROR:  invalid input syntax for type numeric: "unit_cost"
CONTEXT:  COPY billables, line 1, column unit_cost: "unit_cost"

Command used:

COPY dm.billables(code, info, unit_cost, unit_price) FROM '/var/lib/pgsql/sql/charge.csv' DELIMITER ',' QUOTE '"' csv;

How can I import the file?

UPDATE:

table dm.billables:

    CREATE TABLE dm.billables
    (
      billable_id bigint NOT NULL DEFAULT "nextval"('"dm"."billables_billable_id_seq"'::"regclass"),
      acco

unt_id bigint NOT NULL,
  code character varying(64) NOT NULL,
  info "text",
  m_unit "measurement_unit",
  m_unit_custom character varying(64),
  unit_cost numeric(16,4),
  tax_aggregate_id_cost bigint,
  unit_price numeric(16,4),
  tax_enabled_price boolean DEFAULT true,
  tax_aggregate_id_price bigint,
  ts_created timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_modified timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_last_used timestamp with time zone,
  is_demo boolean NOT NULL DEFAULT false,
  CONSTRAINT pk_billables PRIMARY KEY ("billable_id"),
  CONSTRAINT fk_cost_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_cost)
      REFERENCES dm.tax_aggregates (tax_aggregate_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_price_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_price)
      REFERENCES dm.tax_aggregates (tax_aggregate_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
  CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY["m_unit" IS NOT NULL, "m_unit_custom" IS NOT NULL] <> ARRAY[true, true])
)

CSV file:

Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) non-SLIAN,41,Alisha Davidson]",46.5,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) SLIANZ,92,Angela Murray]",59,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) non-SLIANZ,60,Anthony Swindale]",56.5,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) SLIANZ,142,Bernadette  Cutelli]",49,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) SLIANZ,11,Beryl Harrison(Harri)]",59,95


texts:
I am an aspiring DBA (I'd say in ~3 years I would be ready to apply for a job) and I currently am a programmer. I relayed my future goals to a friend of mine who is part of an Agile SCRUM team in a very heavy and fast-paced software development company. (SDLC to the core).

He expressed his concerns for my career path goals - stating that DBAs for most software dev companies is a dying position due to the increase and development of ORM in the last ~ 5 - 7 years. He said that the company trusts the programmers and most of them are capable of doing most of the work DBAs do. (Stored procedures, encryption, SQL, backups, ddl, profiling, etc.)

Can anyone shed light on their experiences or if they have the same concerns/beliefs with the DBA position?

I am referring to NHibernate and those kinds of systems.

To stay aligned with the Q&A formatting of this site - What are problems that will be faced in the future if everything is converted to and uses ORM and DBA positions are no longer weighing in with the same importance they have been the past decade or two?

I will be honest and admit, aside from basic research I have no first hand experience with ORM. I know this site is supposed to be used for professionals, which is why I feel those who know exponentially more about this circumstance would be able to better identify concerns/problems.


texts: Is there any way to disable MongoDB's cache?

texts:
I'm not getting recent data when querying, and I believe it has to do with cache.
Probably, MongoDB's cache invalidation fails somewhere. I'd like to disable it completely and I don't care about performance.
Getting recent data using a query like this:
await Sessions.find({ ip: ip}).sort({ receive_time: -1 }).lean().limit(1).exec();


texts:
I have PG server (9.0.4) installed on Debian x86.
In one of my tables I use numeric(6,1):

CREATE TABLE mytable(name text, numfield numeric(6,1))

Also index created for this field:

CREATE INDEX mytable_numfield_idx ON mytable USING btree (numfield);

Everything is going fine if I use this field with real numeric value. As I see the query uses the index:

EXPLAIN ANALYZE SELECT * FROM kodiall WHERE kodgo=123.0

  Index Scan using mytable_numfield_idx on mytable  (cost=0.00..8.27 rows=1
  width=193) (actual time=0.085..0.087 rows=1 loops=1)

  Index Cond: (numfield = 123.0)

  Total runtime: 0.131 ms

But the index is ignored for some reason if I use 0.0 or NULL as condition value:

EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=0.0
--EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=NULL

  Seq Scan on mytable  (cost=0.00..57.80 rows=1080 width=193) (actual
  time=0.033..1.853 rows=1088 loops=1)

  Filter: (numfield = 0.0)

What wrong here? Is 0.0 not a numeric value?

In real query I use LEFT JOIN with numfield and values in joined table can be NULL.


texts: When defining a foreign key in this Oracle example, what is the significance of the CONSTRAINT keyword?

texts:
In the code below, The first column I create for the table courses is a column called department_code. I then define fk_courses_department_code and specify that department_code in this table will be a foreign key that will reference department_code from the departments table. What is the relevance, or significance of the CONSTRAINT fk_courses_department_code line? As far as I'm concerned, it is understood that department_code is a foreign key in this table. What exactly is the role of fk_courses_department_code then? Does it even exist as a real column? Is it just an alias for department_code? What is it, exactly?

CREATE TABLE courses
(
  department_code    VARCHAR2(2)   NOT NULL,
  course_number      NUMBER(3,0)   NOT NULL,
  course_title       VARCHAR2(64)  NOT NULL,
  course_description VARCHAR2(512) NOT NULL,
  credits            NUMBER(3,1)   NOT NULL,
  CONSTRAINT pk_courses
    PRIMARY KEY (department_code, course_number),
  CONSTRAINT fk_courses_department_code
      FOREIGN KEY (department_code)
      REFERENCES departments (department_code),
);


texts: Restore of copy_only backup of encrypted database made without FORMAT but with INIT fails

texts:
I have a task of making periodical copy of the database, twice a day, and I should not break any backup chain niether accumulate these temporary backups. So I tried to take a copy_only backup with init in order to overwrite the single backup file.
My database in encrypted by using TDE.
I use backup compression at the server level, "backup compression default" is 1 in my sys.configurations. I don't use explicit with compression option in my command.
Here my full @@version:

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)
Sep 23 2020 16:03:08    Copyright (C) 2019 Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0
 (Build 14393: ) (Hypervisor)

My problem is this error below:

Msg 3241, Level 16, State 40, Line 8 The media family on device
'W:\SQL_backup\copy_only\billing_prod2_test.bak' is incorrectly
formed. SQL Server cannot process this media family. Msg 3013, Level
16, State 1, Line 8 RESTORE DATABASE is terminating abnormally.

Here is the full code I executed:
backup database billing_prod2
to disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with init, copy_only--, format;
go

restore database billing_1
from disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with move 'billing_prod2' to 'E:\SQL_data\billing_1.mdf',
move 'billing_prod2_log' to 'E:\SQL_data\billing_1_log.ldf',
replace

The first time it executes without errors, the size of full backup is about 35Mb and it restores perfectly.
The second time the backup executes without error but strangely has a size of 260Mb. This is the size I see on the disk and the same size is written in the output of restore headeronly that still executes without errors. But when I do restore database the error mentioned above appears.
I tried to execute the same code using another db and another disk to save the backup but the result is always the same: the first time it works, the second time the file is overwritten but has different size that is not just a double size, it's almost 10 times of initial size. restore headeronly never fails and always reports only one file within the backup file but the size is multipticated and restore database fails.

Only when I added format option the size of backup became normal and restore succeeded.

My question is: why should I use format with my backup in this case to be able to restore?


texts:
I am newbie to SSAS 2012 and here are my questions:

Can we have measures of complex types (table)?
Is it possible to order the results by a specific measure or attribute?
My cube can be modeled as one dimensional hierarchy, is this generally good?
Can I shard cubes using remote partitions on a range of members?
Do we have remote table partitions with SQL Server 2012 databases?

Thank you!


texts:
Using Oracle database, suppose I have a parent table parent defined in schema A, and a child table child defined in schema B. To achieve this, schema A granted references privilege on parent to schema B.

Now, suppose I want to disable all foreign key constraints which reference parent, then I encounter the following issue: since child is in a different schema and schema A has no select privilege on child, child's foreign key which references parent does not appear in schema A's view of all_constraints and so schema A does not know to disable it.

Is there a way to work around this within schema A or must such a program be run with DBA privileges?


texts:
I have a table with 3 fields and one is a Qty. If the Qty is > than a set amount I would like to split the row into a second row and/or third row

Row   Order      Product     qty
1     123        AAA         25

Based on a max Qty of 10 I would like it to become

Row   Order      Product     qty
1     123        AAA         10
2     123        AAA         10
3     123        AAA         5


texts: What are the MariaDB/MySQL servers types, and how do they differ?, e.g., 'standalone' vs 'embedded', vs ...?

texts:
When configuring a MySQL or MariaDB server, the terms 'standalone' and 'embedded' are referred to, but without reference to what they are, or what they do that is different from each other.

What does the term 'embedded' refer to?  Is this like on an IP camera or something?
Also are these the only the two types of MariaDB/MySQL servers?  Or are there other similar servers, like for example are 'galera', or enterprise other types of servers?
What is the difference between these two or other server types?  In other words, what are they used for that is different, or how are they installed that are different, etc.

Note: When installing, these groups seem to apply:

[server]     apply to both mysqld and embedded   (note: 'mysqld' = 'standalone')
[mysqld]     apply to only mysqld
[embedded]   apply to only            embedded

From context: in Debian desktop, I think I'm running a 'standalone' server only.


texts: Optimal table with N:1 foreign key column

texts:
I have an MS SQL table like this:

TABLE myLog(
  [LogID] [int] IDENTITY(1,1) NOT NULL,
  [ThingID] [int] NOT NULL,
  [Action] [varchar](100) NOT NULL,
CONSTRAINT [PK_myLog] PRIMARY KEY CLUSTERED
(
  [LogID] ASC
)

ThingID is a foreign key for a myThings table identity column.

myLog can contain many rows for any ThingID.

What are the best ways to index or key myLog given that a typical query will request all rows for a particular ThingID?


texts: Fastest way to sync (or keep import) 3.5TB data from hadoop to sharded mongodb cluster

texts:
There are 3.5TB data in our hadoop cluster(yes on hdfs). And we have newly built a sharded mongodb cluster(the latest 3.x) with 3 mongos, 3 configdb and 3 shards(each shard has 1 primary and 2 secondary nodes)

We are looking for the best/fastest way to import these data from hadoop/hdfs to our newly built sharded mongodb cluster.

All these data will be into sharded collections in mongodb cluster.

We don't have much experience on this and no clue how to do this with the fastest way in our environment.

Appreciate if anyone can give a clue or the tools we can leverage. open source tools or commercial are both ok to us.

Joe


texts: How to reclaim back space after deleting objects on SQL Server?

texts:
I have a database runnin on SQL Server 2008 that is now out of space, errors like "Could not allocate a new page for database 'database' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup." are common.

If I query the FILEPROPERTY('SPACEUSED') for the only file on the PRIMARY filegroup I get   less than 10MB which is barely 0.1% space free left.
So, system has some reason in complaining.

I investigated the space occupied by tables by right clicking on database on SSMS, running some reports on disk usage and detected that there was a table consuming lots of space. I fail to understand why, after TRUNCATE ing the table, FILEPROPERTY('SPACEUSED') returns precisely the same (very small) amount. Unless I am mistaken, TRUNCATE works by, under the hood, dropping and recreating the table

The standard reports on disk usage report that table ceased to occupy a big amount of space, though. I expected the FREE space to increase and the ugly "Could not allocate a new page..." message to go away, but not so.

What am I missing here?


texts:
When I run the upsizing module it goes through the motions but the report at the end says

  "table was skipped or export failed"

for each of the tables.

I can't find a solution for this.

I can't figure out why this is?


texts: Predicates: in the WHERE clause or the JOIN clause?

texts:
Looking at "SQL Server 2008 Internals"1 by Kalen Delaney, on page 13, it states the following:

  "The first step in producing such a plan is to normalize each query, which potentially breaks down a single query into multiple, fine-grained queries.  After the Query Optimizer normalizes a query, it optimizes it, which means that it determines a plan for executing that query."

It's been suggested to me by another DBA that performance can be improved for certain queries by moving WHERE clause predicates into the FROM clause, such that:

SELECT *
FROM dbo.table1 t1
    INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID
    LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID
WHERE t1.CreateDate >= '2015-07-31 00:00:00';

would become:

SELECT *
FROM dbo.table1 t1
    INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID
        AND t1.CreateDate >= '2015-07-31 00:00:00'
    LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID;

Apparently the implication for the first example is the query optimizer will perform the JOIN first, then apply the WHERE clause, which for complicated queries with large tables will not perform as well as if we manually re-write the query into the form presented in the second example.  Apparently this only applies to outer joins, i.e. if this were an inner join, the normalizer would in fact move the WHERE predicate into the FROM clause.

Can someone confirm this behavior?  Is there some documentation somewhere I can look at?  Can I see this in the execution plan?

1 - yes, I know this is ancient.


texts: SQL Server - Select parent row or child row if no parent

texts:
I have a single table, Issue. This table has Id and ChildId columns.

=============
Id | ChildId
1  | NULL
2  | 3
3  | NULL
4  | 1
5  | NULL
6  | NULL
=============

I need to select from this table so that the parent row is returned if it exists else return the child row. So selecting from every row would get:

=============
Id | ChildId
4  | 1
2  | 3
2  | 3
4  | 1
5  | NULL
6  | NULL
=============

I know that this table layout is not good (using ChildId instead of ParentId) but I cannot change the scheme unfortunately.

If you know how to return this data that would be great. Thanks in advance.


texts: Mysql synchronization databases on one machine

texts:
I have MySQL databases. Database A and Database B. Running on Mysql 5.5 and Ubuntu 12.04.

Into Database A i am doing INSERTs, UPDATEs every minute (cron) (The whole database has over 15 millions rows and growing).

I want to synchronize Database B with Database A use it for SELECT queries (for performance).

So I want to know: How can I synchronize new rows with Database B every 4 hours. I looked into MySQL replication. Is it good for this?

I am thinking think about SQLite3 DB.
All tables are InnoDB
I'm looking for a headless command, not a GUI tool.
Everything is on one machine.
I need a fast utility. I tried Mysqldump, but I am trying to sync, not get the entire database.


texts: Do entries in double-entry bookkeeping need to reflect the actual flow of money?

texts:
I'm developing a new double-entry accounting system for my company, which has Customers and Workers.  Customers pay the company for services rendered, and in turn the company pays Workers for providing these services (sort of an Uber model).

The idea is to model an Account for each Customer, Worker, and the company itself.  Each transaction therefore, is either a transfer of funds between two of these accounts, or a transfer of money in/out of the system as a whole.  The latter case would be, for example, when a Customer makes a payment or when a Worker receives a paycheck.

For example, consider the following accounts:

| id | type     | name    |
| -- | -------- | ------- |
| 1  | internal | Company |
| 2  | customer | Emma    |
| 3  | worker   | Alex    |

Suppose Alex provides a service to Emma.  Emma is charged for it, and Alex is paid some percentage.  In the transactions table, this would look like:

| id | from_account_id | to_account_id | amount | type    |
| -- | --------------- | ------------- | ------ | ------- |
| 1  | 2               | 1             | 50.00  | service |
| 2  | 1               | 3             | 40.00  | service |

Negative account balances are allowed, but transaction amounts must always be positive.  So at this point in time Emma has an account balance of -50, Alex has an account balance of 40, and the company has an account balance of 10.

For the system as a whole to become solvent, Emma must deposit (make a payment) and Alex must withdraw (be given a paycheck).  As these occur, we add additional transactions:

| id | from_account_id | to_account_id | amount | type       |
| -- | --------------- | ------------- | ------ | ---------- |
| 3  | NULL            | 2             | 50.00  | payment    |
| 4  | 3               | NULL          | 40.00  | withdrawal |

Transaction 3 is essentially "Emma funding her account", while transaction 4 is "Alex withdrawing from his account".  Any account's current balance can be derived by adding all of the amounts where their account appears in the to_account_id, and subtracting all of the amounts where their account appears in the from_account_id.  Our "accounts receivable" would simply be the sum of all negative balances, and "accounts payable" would be the sum of all positive balances.

This seems like a good approach, but it doesn't really reflect the actual flow of money.  When Emma pays us from her credit card, from the banks' perspective there is no "Emma's internal account" - the funds are simply transferred directly from Emma's bank account to the Company's bank account.  Is it a problem for us to model "Emma's internal account" anyway?

There is also the possibility that Emma will pay $50 to Alex directly when services are rendered, and then Alex needs to pay the Company their share ($10).  Under my proposed system I would model this as follows:

| id | from_account_id | to_account_id | amount | type       |
| -- | --------------- | ------------- | ------ | ---------- |
| 1  | 2               | 1             | 50.00  | service    |
| 2  | 1               | 3             | 40.00  | service    |
| 3  | NULL            | 2             | 50.00  | payment    |
| 4  | 3               | NULL          | 50.00  | withdrawal |

So under this model Emma has fully funded the cost of the service, and Alex has "withdrawn" $50 from his account (though he only gets to keep 40 of it).  Thus, Alex ends up with a -$10 account balance.  Alex's account will become solvent again when he does more work, or pays his balance to the company.

Is this a good idea, even though the reality is just that Emma handed Alex $50?


texts: Improve Performance -Update query with OR in WHERE

texts:
How do we improve this Update query Performance as currently it is taking 1 Minute to update 17643 Rows.

Query ->

  Declare @date datetime = '09-Dec-2013'
    update  #tmp_sp_abc
    set     test = rtrim(xyz_test)
    from    #tmp_sp_abc t1,
            t_h_bg_pc_it t2
    where   (t2.id_i = t1.i or t2.id_s1 = t1.s)
    and     t1.r_type = 1
    and     t2.[date] = @date

Tables Row Count: -
    #tmp_sp_abc -> 125352
        t_h_bg_pc_it -> 14798 Rows

t_h_bg_pc_it table has 300 columns with primary key on id_i column
and #tmp_sp_abc has 11 columns with no primary key and no indexes.

found that "OR" condition is the root cause of this much time consumption but, can't change it.

tried to add indexes on: -

Table: - t_h_bg_pc_it
Columns: - [xyz_test], [id_i], [id_s1], [date]

Table: - #tmp_sp_abc
Columns: - [i], [s], [r_type] include [test]

but, by doing this saved only 5 seconds.

Attaching the Execution Plan Snaps (Without above indexes and with indexes).

Please advice.


texts: If an INNER JOIN in a query returns no rows, do additional INNER JOINS add a significant performance overhead?

texts:
If I make a query as follows:

SELECT *
FROM TableA A INNER JOIN TableB B ON A.AID = B.AID
  INNER JOIN TableC C ON B.BID = C.BID

Does the INNER JOIN with TableC add significantly to the performance of the query? Will it attempt to do the join, or will it note the empty set of records and move on?


texts:
I'm getting this message:

  Msg 8624, Level 16, State 17, Line 1
  Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

This is what I run

;WITH TableA AS
(
   SELECT 101 as A_ID
)
,TableB AS
(
   SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses
   UNION ALL
   SELECT 2 , 101  , 'YYY'
   UNION ALL
   SELECT 3 , 101  , 'ZZZ'
   UNION ALL
   SELECT 4 , 102  , 'AAA'
)
SELECT
    A_id
    ,x.x.value('(./text())[1]','varchar(500)') AS fieldX
FROM
    TableA AS A
OUTER APPLY
    -- CROSS APPLY
    (SELECT
         ',' + B_Courses
     FROM
         TableB AS B
     WHERE
            1=1
            AND B.B_A_ID  = A.A_ID
     FOR XML PATH(''),TYPE
    ) x(x)

With OUTER APPLY, I get the message shown above.

With CROSS APPLY, everything works nicely.

I resolve by adding in select this :

STUFF((SELECT ','+B_Courses
    FROM
         TableB AS B
    WHERE
        B.B_A_ID  = A.A_ID
    FOR XML PATH(''),TYPE).value('(./text())[1]' ,'VARCHAR(500)'),1,1,'')

Tested on:

Edition           ProductVersion    ProductLevel
Express Edition     9.00.3042.00    SP2

and

Edition                     ProductVersion  ProductLevel
Developer Edition (64-bit)  11.0.2100.60    RTM

Any idea way is  this happening?

Thanks


texts: How to export a single huge table from SQL Server?

texts:
I'm given the task to export the content of a huge table from a production database and to import the data into a database at a remote location.

The Table has about 45,000,000 rows. using about 4 GB space in the database.

Its 10 Columns are of type int, datetime and varchar(n) <= 255, but some of the varchar fields contain newlines and the usual field separators used by bulkcopy.

I guess export to Excel is no option due to the number of rows.
Export with SSIS to Flatfile is possible (took about 30 minutes), but reimport is not automatic, since some fields where spitted.

My current idea is

create a new database on the production server (using same
collations as the production database)
Set its recovery mode to simple
copy the data with
Select * into copy_of_table from prod_db..big_table
Detach mdf file from this new database and ship it to the other
location
Drop adhoc database

I assume that inserting into a database with simple recovery model generates only minimal logging on the new database and this way is one of the fastest possible to export the data and the impact to the production database is the least possible.