Is it good practice to assign a system account for database owner? I can see the owners of my databases usign this query

SELECT  name ,
        SUSER_SNAME(owner_sid)
FROM    sys.databases

What are the things I should consider when choosing an account for database owner (account fro dbo user)? I'm thinking about a scenario when I assing normal admin account for database owner and when the time passes this account is disabled from AD when the ownwer of this account leaves his job. Is it good practice to assign a system account for database owner?

I advocate having databases owned by a Windows account (login).

If using Mixed mode, sa could be used, but sa is a bit anonymous for my comfort.  Users owning databases (sometimes seen on dev database servers) doesn't work in production.

Like @nopol I prefer using Windows groups to reduce the number of logins under SQL Server, but groups can't own SQL Server databases.


Postgresql: How to take incremental backup with wal-e I am new to postgresql. I've connected to my demo database with psycopg2 and inserted data with python faker. I want to take incremental backup with the tool WAL-E and want to see how it actually works. But the few tutorials aren't helping that much as they're not for naive users.

Can someone help explaining the steps simply how to backup data with WAL-E? That would be a great help ! I'm using psql 10.4 in Ubuntu 18.04.

Thanks

If you understand the base backup and WAL archives in Postgresql, then you would have understood the WAL-E.

Base Backup:

Its also called as the Hot backup, this will not interrupt any other transaction inside your PostgreSQL. Once you initiated the basebackup it'll wait for a checkpoint. If the checkpoint is entered, then it'll copy the entire data directory to a file. And these backups are the starting points for replication or POINT IN TIME recovery. Generally, basebackups are not consistent and may have corrupted data. Here the WAL files have come into the picture.

WAL:

WAL files will get all data changes which are happening inside the database. These changes are made in the WAL files first and then actual database after that. Thats why its called as Write Ahead Log.

What is consistent backup:

  consistent backup = basebackup + WAL Files.

Once the bacebackup may or may not have the committed data. So WAL files are will redo or undo the particular transaction to make the data consistent. WAL files must be applied after restoring the basebackup. This WAL files should be mentioned in restore_command in the recovery file.

WAL-E:

Its an OpenSource PostgreSQL database backup tool which will upload your backups to S3, GCP storage and Azure (+ some other places).
Here also it'll perform the basebackup first.

I'll explain this functions with S3.

backup-push:

This will initiate the basebackup and upload the backup file to s3.

backup-fetch:

Restore the basebackup from the S3. LATEST will restore the most recent backup.

wal-push:

It'll get the WAL file using the archive_command and store into the S3.

wal-fetch:

It'll restore your WAL files from S3.

Recovery:

If your primary database is corrupted and you want to restore it from WAL-E,

envdir /etc/wal-e.d/fetch-env wal-e               \
--s3-prefix=s3://some-bucket/directory/or/whatever  \
backup-fetch /var/lib/my/database LATEST

Now use recovery.conf file to restore your WAL files.

restore_command = 'envdir /etc/wal-e.d/env wal-e wal-fetch %f %p'
standby_mode = on

Or, if you want to stop the restore on a particular time, use

recovery_target_time = '2017-02-01 19:58:55'

Then start the PostgreSQL and it'll restore your databases to a consistent state.

How to backup using WAL-E:

Schedule a cronjob for this. This blog post explains this clearly.

Don't wrongly understand the WAL-E with Replication:

WAL-E will push the log files when Postgresql kicks out the WAL logs from the pg_XLOG folder. This will archive based on keep_wal_segments. So this will not restore upto the last commit, but it'll so upto last archived WAL file. Its mainly used for DR not for HA.


How to handle reporting of an extreme amount of databases in SQL Server 2005? I'm looking for some advice on how to handle reporting for our environment.  We currently have 16 servers with 20 instances of SQL Server 2005.  We have over 6,600 databases and growing across these instances (1 database per customer).  The majority of our databases run in size from 200mb to 7gb with about 60 databases running in size from 11GB to 110 gb at the largest.

We are using a SAN for storage and we are running into issues with the running of the reports affecting IO.

One idea we had was to pull the 60 larger databases and then use transactional replication to replicate these databases and run reporting on the replicas.

That would then leave all the smaller databases to run without the stress of the larger databases.  In the future we don't believe there will be any more of the the larger databases based on our company goals.

Any thoughts?

In our shop at my previous job, we had a secondary set of servers where we tested our restores. For our busiest customers we would restore tonight's backup, mark it as read_only, and their reporting tomorrow would connect to that copy of the database for all reports from yesterday back. This offloaded about 90% of the reporting workload and doubled as a backup/restore validation method. So if most reports don't need today's data, you could consider alleviating some of the production workload this way with some cheaper hardware - if you're not using Enterprise features you could even use Express for all the databases that are < 10GB. (Well, I see it's 2005, which had a lower DB size limitation, but you could always restore your copy forward into 2008/R2.) This would allow you to really distribute the databases to many low-end commodity servers (VMs or pizza boxes).


Archiving fga_log$: sys.dbms_audit_mgmt.clean_audit_trail doesn't delete anything I am having a problem to archive and then delete portions of the fga_log$ table.

Here's what a script basically does

insert into fga_log_archive
select
  ...
from
  sys.fga_log$;

begin
   sys.dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type     => sys.dbms_audit_mgmt.audit_trail_fga_std,
     last_archive_time    => systimestamp
  );

  sys.dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        => sys.dbms_audit_mgmt.audit_trail_fga_std,
   use_last_arch_timestamp => true
  );
end;
/

The documentation for dbms_audit_mgmt.clean_audit_trail says:

  This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.

So, I have expected the records that were archived with the insert statement to be deleted
from the fga_log$ table by the clean_audit_trail call. That's not the case however. Am I missing something?

Edit

The audit configuration parameters as selected with

select
  substrb(parameter_name ,1,25) name,
  substrb(parameter_value,1,20) value,
  substrb(audit_trail    ,1,20) trail
from
  dba_audit_mgmt_config_params
;

is

NAME                      VALUE                TRAIL
------------------------- -------------------- --------------------
DB AUDIT TABLESPACE       SYSAUX               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE       AUDIT_LOG_TABLESPACE FGA AUDIT TRAIL
AUDIT FILE MAX SIZE       10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE       10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE        5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE        5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000                 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 999                  FGA AUDIT TRAIL

Edit II

select * from v$version returns

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

As Mat has pointed out, I hit Bug 9164488 (DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL does not delete sys.aud$ and sys.fga_log$ tables [ID 9164488.8]) which should be fixed in Release 11.2.0.2.

In the meantime, running on 11.2.0.1, I was able to circumvent the bug with a purge job in order to do the work. As soon as I ran

begin
  sys.dbms_audit_mgmt.create_purge_job (
     audit_trail_type           => sys.dbms_audit_mgmt.audit_trail_fga_std,
     audit_trail_purge_interval => 999,
     audit_trail_purge_name     =>'Purge_Test',
     use_last_arch_timestamp    => true
  );
end;
/

clean_audit_trail would do the work, as previously expected.


Monitor database every hour to prevent data loss Here is my requirement, though I'm aware what could possibly be done to fulfill it but still looking out for the best solution to work out with.

Few days ago someone(some hacker or some previous developer) had truncated my important table causing inconvenience to my customers. I need to change my database password, server's password and other application's password in order to avoid such obnoxious actions and prevent my application.

Additionally first I need to know if this kind of activity is being performed with my data before my customers so that I can find out any work around for them. This is what I thought till now-

I should create a window service which will run in every 1 hour and check in all tables or most importantly in main tables that # of records are as expected or not. If not then an alert will be sent to me, so that I can quickly fix that.
I should create an SQL job which will perform same steps that I mentioned in first point.

These workarounds seems fine and workable. but I'm really curious to know what else can I do to prevent my data or alert me in case of data lost. I'm sure there are other efficient and more feasible way to achieve what I want. It would be great if anyone can help me around.

This is a web application and only authenticated users can login. My SQL Server edition is Enterprise.

First and foremost this is an issue of access control. Eliminate all redundant logins, and reduce permissions on the required logins to the strictly necessary minimum. Do not use 'well known' logins shared by every person with DB access, make sure every user uses its own login (eg. use Integrated Auth).

It is not clear from your post whether this server is accessed directly from the internet or not. Ideally you should set up a VPN between your app(s) and the server and avoid exposing the server to the internet. If not possible, there are some defenses you need to set up. The default SQL Server ports are subject to constant scans and brute force attacks. You should use a non-default port to, at least, avoid the overhead of rejecting these constant brute force scan attacks. Next, you should restrict the internet client addresses that can connect. This can be done, ideally, at IP level using Firewall rules. SQL Server also can enforce this, using logon triggers, as Alex suggested. If possible, deploy both firewall rules and a logon trigger.

Enable SQL Server logon audit. Advertise it that is enabled, so that employees are deterred from trying to hack into the system.

Establish backup policies, with frequent log backups, and keep backups off-site. Have restore plans, and do restore drills to validate the backups and the restore procedures. If someone penetrates your defenses and manages to erase data, you should be able to quickly restore it.

And lets not forget the app itself. You may well be barking up the wrong tree and the erase may had been done via the app itself, via the usual SQL injection exploits. You must audit the app and ensure this is not the case, fix any issue you find.

Right now, you are in an impossible situation. You have lost data, and don't even know how. Contact professional help to help you find how the data was erased. If you don't know where the ship is leaking, is very hard to patch the hole...

My 2c on the idea of 'monitor the database see when someone deleted my data' is ... a waste of time right now. W/o understanding how the data was erased, it will just turn into an amusing game for somebody that will periodically erase your data, just to see how fast you are reacting today... Really, focus on mitigation and deterrence. Detection is nice to have, but it cannot be your first line of defense.

For structural database changes (eg. modify a table, alter a procedure etc) my company DBHistory.com offers a service that audits all server changes and keeps an off-site record. Unfortunately, at this moment I cannot offer data changes audit.


SSAS Dimension Help - Finding Values Not In the Dimension using Role Playing I have a Fact / Fact Less table that has a list of Products likley to be purchased by client.

Client ID  Product ID
1          1
1          2
2          2
2          5
etc...

I need to to allow the users to find clients that have , from the example, the 2 product ID that does not have a 1 Product listed.    I get a file from the source system that is very simialr to the table above and I know I could create a table that looks like this below (assumign I only have a few products, and I do in this case...):

Client ID  Product ID  Listed
1          1           Y
1          2           Y
2          2           Y
2          5           Y
1          3           N
1          4           N
1          5           N
2          1           N
2          3           N
2          4           N

I could have two Role Playing Dimension based on the Listed flag that would all them to say show me all the clients that are Listed in 2 but not Listed in 1.  However, I'm sure there is a much better way.

In order to do what you're thinking of, you would have to make a factless fact table with the cartesian product of all possible combinations of products and the customers.  If you have a big portfolio of products this could get quite large.

In fact, you're asking a question roughly equivalent to WHERE NOT EXISTS in SQL, which you won't get out of a cube through straight slice-and-dice operations.  However, you can query it with MDX.  If you make up a little database like the one shown below and build a cube over it:

create table DimCustomer (
       CustomerID   int not null
      ,Customer     varchar (50)
)
go

create table DimProduct (
       ProductID    int not null
      ,Product      varchar (50)
)
go

create table FactSalesLeads (
       CustomerID   int not null
      ,ProductID    int not null
      ,PlaceHolder  int not null default 1
)
go

insert DimCustomer (CustomerID, Customer) values (1, 'BloggsJ')
insert DimCustomer (CustomerID, Customer) values (2, 'SmithF')
insert DimCustomer (CustomerID, Customer) values (3, 'JonesB')
go

insert DimProduct (ProductID, Product) values (1, 'Widget')
insert DimProduct (ProductID, Product) values (2, 'Dohickey')
insert DimProduct (ProductID, Product) values (3, 'Whoflungdung')
go

-- BlogsJ wants to buy a Widget and a Dohickey
insert FactSalesLeads (CustomerID, ProductID) values (1, 1)
insert FactSalesLeads (CustomerID, ProductID) values (1, 2)

-- SmithF wants to buy a Dohickey but no Widget
insert FactSalesLeads (CustomerID, ProductID) values (2, 2)

-- JonesB wants to buy a Widget and a Whoflungdung
insert FactSalesLeads (CustomerID, ProductID) values (3, 1)
insert FactSalesLeads (CustomerID, ProductID) values (3, 3)
go

BloggsJ and JonesB want to buy Widgets
SmithF wants to buy a Dohickey but not a Widget

Now, put up a cube over the database with Customer and Product dimensions and a measure group for the sales leads.  A MDX query like the following would show you which customers wanted to buy Widgets but not Dohickeys:

// Doing a 'where not exists' in MDX with set operations
//
with

 // Customers likely to buy a Widget
 set [WidgetLeads]
  as {filter ([Customer].[Customer].Children
             ,[Product].[Widget])}

 // All customers except those likely to buy a Dohickey
 set [NotInterestedInDoHickey]
  as {except ([Customer].[Customer].Children
             ,{filter ([Customer].[Customer].Children
                      ,[Product].[Dohickey])})}

// Just a bit of fluff so the list goes down  the columns
select [Measures].[Place Holder]
    on Columns

        // Intersection of the sets gives leads for a Widget who are
        // not expected to purchase a Dohickey
       ,intersect ([WidgetLeads], [NotInterestedInDoHickey])
    on Rows
  from [Leads]

The process is slightly roundabout in MDX because it does not have a 'WHERE EXISTS` operator.  You can do it in terms of set operations though, as demonstrated in the snippet of MDX.

Some OLAP front-end tools, such as the late and much lamented Proclarity would allow the user to define sets in the tool and then do the operation manually.  If you don't have access to that I can't easily think of a way that materially improves on MDX queries like this one.

One option would be to build a report that takes the combination of products you want as parameters and returns the list.  You could also implement that in SQL.

You could also possibly do something with a dynamic named set in SSAS2008+ where you calculated the set of products not present in the customers' preferences.  This would be computed as the total set of all products minus the total set of products filtered by the preferences in the set of customers.

You can calculate the sets of customers by selecting those with a particular preference or set of preferences.  Showing the non-empty members of the non-preferred list, perhaps filtered by some specific subset of interest would allow you to show them along a different axis.

This would adjust to producing a list of products where no customers in the selected set were considered to be leads.  Unfortunately I don't have a running SSAS2008 instance to hand to demo this, so it's left as an exercise to the reader.


Setting NLS_LENGTH_SEMANTICS for session via trigger I have the following problem with my Oracle 12c database.

I have to install a third party application, which needs an Oracle database instance. The supplier demands to set the NLS_LENGTH_SEMANTICS parameter to CHAR. However, Oracle strongly recommends not to change it globally from BYTE to CHAR.

My solution was to create a trigger, that changes the parameter for the session. The trigger looks like follows:

create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
    IF ( user like 'SAPIENT%' ) THEN
        execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
    END IF;
END;

Unfortunately, the trigger does not change the session parameter. I suspect the trigger doesn't fire up, so I ensured it by writing a file onto disk. It's been definitely triggered.

If I change the session parameter on e.g. SQL developer from client side, the parameter is set.

ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR";
select * from NLS_SESSION_PARAMETERS where parameter='NLS_LENGTH_SEMANTICS';

Does anyone know why it's not possible to set the NLS_LENGTH_SEMANTICS parameter from server side?

System information

It is possible.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 29 08:29:35 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             343933032 bytes
Database Buffers          721420288 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> create user sapient identified by sapient;

User created.

SQL> grant create session to sapient;

Grant succeeded.

SQL> show parameter seman

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE

SQL> create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
    IF ( user like 'SAPIENT%' ) THEN
        execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
    END IF;
END;  2    3    4    5    6    7
  8  /

Trigger created.

SQL> grant select_catalog_role to sapient;

Grant succeeded.
SQL> connect sapient/sapient
Connected.
SQL> show parameter seman

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
SQL>


How to Substitute Variables in PostgreSQL The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL?

INSERT INTO control_threshold (
    threshold_id, group_name, description, sql, low_value, high_value)
VALUES(
    threshold_seq.nextval, '&2', '&1',  TRIM('&5' || '&6' || '&7' || '&8' || '&9'),
    trim('&3'), trim('&4'));

These variables are a feature of SQL*Plus.

psql, the equivalent program in the PostgreSQL world, also has variables.

Use

\set variable 'value'

and

insert into mytable(mycolumn)
values (:variable);


File creation dates of archived WAL files not in line with archive_timeout in PostgreSQL 9.6 I have configured archive_timeout = 5 (i.e. 5 seconds) for a PostgreSQL 9.6 database in order to conduct tests around point-in-time recovery. My archive_command invokes cp into a local archive directory.

If I check the file modification dates in that local archive directory I see that WAL files are typically created in there only approx. every 20 seconds apart, i.e. far more than the configured 5 seconds. This is for PostgreSQL 9.6 running inside a Docker container on a lightly loaded system.

What could explain this large discrepancy?

The documentation has an answer to this:

  When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint (checkpoints are skipped if there is no database activity).

Based on your observation and the above quote, I guess you have a checkpoint every 20 seconds or so, and a very low rate of changes.


PL/SQL : query numeric data type by scale? Wish to select rows with number fields having a certain amount of data after the decimal

eg

select number from table where number.scale > 4;

Is this possible to do directly, or must I cast to string and parse character wise

If I understand the question correctly you want to find numbers that have, for example, more than 4 digits of precision after the decimal? E.g., you want 3.0671, 3.06713, etc. but not 3.067 or 3.06 or 3. If that is the case then you could do something like:

select number from table where number - trunc(number,4) <> 0;

trunc here will simply remove everything ater the fourth decimal digi

(I'm not taking into consideration what Nick said regarding 2 and 2.00.)


postgresql custom TEXT SEARCH CONFIGURATION with tsvector_update_trigger I would like to use a custom text search configuration with tsvector_update_trigger to be able to update tsv field with french dictionary and unaccent.

So I did that :

CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

and then created trigger :

CREATE TRIGGER TS_tsv BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(tsv, 'pg_catalog.fr', title, authors, descriptionText);

but unfortunately when I want to create or update a row I get an error telling me that fr does not exist.

How I can use this configuration with a trigger ?

When creating the new configuration with

CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );

it does not appear in pg_catalog, but in the current schema (public by default).

This is made apparent by the \dF command in psql:

test=# \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
 public     | fr         |
(17 rows)

In that case, the trigger should be created with:

CREATE TRIGGER TS_tsv BEFORE INSERT OR UPDATE ON books
  FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(tsv, 'public.fr', title, authors, descriptionText);


Update Statistics full scan hung up? We have a weekly maintenance plan that does your typical Integrity Checks and then goes and does an Update Statistics on all the databases on our server.

Over the weekend it appears that our job was hung up on the Update Statistics (with full scan) job. Looking at the Activity Monitor it appeared to have a Task State of SUSPENDED for the command Update Statistics. And for a Wait Type was LCK_M_X.

My Question: Why would it be suspended? A lock on a table in the database it was doing statistics on couldn't have stopped it? What would hang an update statistics?

We were able to determine that only one table in the database in question had not had it's statistics updated. We did that by running the following

select object_name (i.id)as objectname,i.name as indexname,i.origfillfactor,i.rowcnt,i.rowmodctr ,STATS_DATE(i.id, i.indid) as ix_Statistics_Date,o.instrig,o.updtrig,o.deltrig,o.seltrig
from sysindexes i INNER JOIN dbo.sysobjects o ON i.id = o.id
where rowcnt >1000 and i.name not like'sys%'and object_name(i.id)not like'sys%'
order by objectname desc
go

So we ran the update statistics on just the table in question. And we were able to replicate the scenario in question as it was still waiting and not finishing. So then knowing the session ID of my waiting process I took a look at the locks table

select * from sys.dm_tran_locks

Looking at the results I found a different session ID associated with the Object_ID of my table in question. I was able to then go kill that session ID and the update statistics finished no problem.

What I think happened here was that a user left a program open that had the exclusive lock on my table, and perhaps that along with auto statistics in sync mode may have caused a log jam when doing our full scan update stats.


Oracle 11g - DROP database command on ACFS In Oracle 11g and higher (on RHEL 6) issuing the 'drop database' command in mount restrict mode will delete all the DB's ASM datafiles. Can somebody tell me if this command also drops all ACFS datafiles? The documentation only says it's doesn't drop datafiles on raw devices.

And ACFS is not raw device, so the files will be deleted.

My ACFS:

[oracle@o6ca1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@o6ca1 ~]$ acfsutil info fs /oradata
/oradata
    ACFS Version: 11.2.0.4.0
    flags:        MountPoint,Available
    mount time:   Wed Oct 31 15:21:46 2018
    volumes:      1
    total size:   10737418240
    total free:   8938848256
    primary volume: /dev/asm/oradata-379
        label:
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          247, 194049
        size:                  10737418240
        free:                  8938848256
        ADVM diskgroup         DATA
        ADVM resize increment: 33554432
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
        compatible.advm:       11.2.0.0.0
    number of snapshots:  0
    snapshot space usage: 0
    replication status: DISABLED

My database:

[oracle@o6ca1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? ORCL
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@o6ca1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 31 15:27:32 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oradata/ORCL/users01.dbf
/oradata/ORCL/undotbs01.dbf
/oradata/ORCL/sysaux01.dbf
/oradata/ORCL/system01.dbf

Dropping the database:

SQL> startup force mount exclusive restrict
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             335545224 bytes
Database Buffers          725614592 bytes
Redo Buffers                5517312 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> exit

Files are deleted:

[oracle@o6ca1 ~]$ ls -l /oradata/ORCL/
total 0

And the free space is also greater than it was before:

[oracle@o6ca1 ~]$ acfsutil info fs /oradata
/oradata
    ACFS Version: 11.2.0.4.0
    flags:        MountPoint,Available
    mount time:   Wed Oct 31 15:21:46 2018
    volumes:      1
    total size:   10737418240
    total free:   10579529728
    primary volume: /dev/asm/oradata-379
        label:
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          247, 194049
        size:                  10737418240
        free:                  10579529728
        ADVM diskgroup         DATA
        ADVM resize increment: 33554432
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
        compatible.advm:       11.2.0.0.0
    number of snapshots:  0
    snapshot space usage: 0
    replication status: DISABLED


Is ROLLBACK a fast operation? Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?

For SQL Server, you could argue that a commit operation is nothing more than writing LOP_COMMIT_XACT to the log file and releasing locks, which is of course going to be faster than the ROLLBACK of every action your transaction performed since BEGIN TRAN.

If you are considering every action of a transaction, not just the commit, I'd still argue your statement is not true. Excluding external factors, speed of log disk compared to data disk speed for example, it's likely the rollback of any work done by a transaction will be faster than doing the work in the first place.

A rollback is reading a sequential file of changes and applying them to in-memory data pages. The original "work" had to generate an execution plan, acquire pages, join rows etc.

Edit: The it depends bit...

@JackDouglas pointed to this article which describes one of the situations where rollback can take significantly longer than the original operation. The example being a 14 hour transaction, inevitably using parallelism, that takes 48+ hours to rollback because rollback is mostly single threaded. You would most likely also be churning the buffer pool repeatedly, so no longer are you reversing changes to in-memory pages.

So, a revised version of my earlier answer. How much slower is rollback? All other things considered, for a typical OLTP transaction it isn't. Outside the bounds of typical, it can take longer to "undo" than "do" but (is this a potential tongue twister?) why will depend on how the "do" was done.

Edit2: Following on from discussion in the comments, here is a very contrived example to demonstrate that the work being done is the major factor in determining the relative expense of commit vs rollback as operations.

Create two tables and pack them inefficiently (wasted space per page):

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET NOCOUNT ON;
GO

CREATE TABLE dbo.Foo
(
    col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , col2 CHAR(4000) NOT NULL DEFAULT REPLICATE('A', 4000)
)

CREATE TABLE dbo.Bar
(
    col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , col2 CHAR(4000) NOT NULL DEFAULT REPLICATE('A', 4000)
)
GO

INSERT dbo.Foo DEFAULT VALUES
GO 100000

INSERT dbo.Bar DEFAULT VALUES
GO 100000

Run a "bad" update query, measuring time taken to do work and the time taken to issue the commit.

DECLARE
    @StartTime DATETIME2
    , @Rows INT

SET @Rows = 1

CHECKPOINT
DBCC DROPCLEANBUFFERS

BEGIN TRANSACTION

SET @StartTime = SYSDATETIME()

UPDATE
    dbo.bar
SET
    col2 = REPLICATE('B', 4000)
FROM
    dbo.bar b
INNER JOIN
    (
    SELECT TOP(@Rows)
        col1
    FROM
        dbo.foo
    ORDER BY
        NEWID()
    ) f
ON  f.col1 = b.col1
OPTION (MAXDOP 1)

SELECT 'Find and update row', DATEDIFF(ms, @StartTime, SYSDATETIME())

SET @StartTime = SYSDATETIME()

COMMIT TRANSACTION

SELECT 'Commit', DATEDIFF(ms, @StartTime, SYSDATETIME())
GO

Do the same again but issue and measure rollback.

    DECLARE
    @StartTime DATETIME2
    , @Rows INT

SET @Rows = 1

CHECKPOINT
DBCC DROPCLEANBUFFERS

BEGIN TRANSACTION

SET @StartTime = SYSDATETIME()

UPDATE
    dbo.bar
SET
    col2 = REPLICATE('B', 4000)
FROM
    dbo.bar b
INNER JOIN
    (
    SELECT TOP(@Rows)
        col1
    FROM
        dbo.foo
    ORDER BY
        NEWID()
    ) f
ON  f.col1 = b.col1
OPTION (MAXDOP 1)

SELECT 'Find and update row', DATEDIFF(ms, @StartTime, SYSDATETIME())

SET @StartTime = SYSDATETIME()

ROLLBACK TRANSACTION

SELECT 'Rollback', DATEDIFF(ms, @StartTime, SYSDATETIME())
GO

With @Rows=1 I get a reasonably consistent:

5500ms for the find/update
3ms commit
1ms rollback

With @Rows=100:

8500ms find/update
15ms commit
15ms rollback

With @Rows=1000:

15000ms find/update
10ms commit
500ms rollback

Back to the original question. If you're measuring time taken to do work plus the commit, rollback is winning hands down because the majority of that work is spent finding the row to update, not actually modifying data. If you're looking at the commit operation in isolation, it should be clear that commit does very little "work" as such. Commit is "I'm done".


How to delist subset strings if it is a substring of another string I have a list of strings in my database let say in a column

understand
understan
understa
underst
unders
under

I'm trying to find out How to delist subset strings if it is a substring of another string with sql?

So I if we pretend that this is a column of my table, the end result must be only

understand

If you want to delete all records except shown then try something similar to

DELETE dataset.table t1
WHERE EXISTS ( SELECT 1
               FROM dataset.table t2
               WHERE t2.word LIKE CONCAT(t1.word, '_%') )

If you want to select only shown record use

SELECT *
FROM dataset.table t1
WHERE NOT EXISTS ( SELECT 1
                   FROM dataset.table t2
                   WHERE t2.word LIKE CONCAT(t1.word, '_%') )


How many Read IOPS are too many? (Should I upgrade my RDS instance memory?) Amazon's best practices indicate you should have enough memory on your RDS instance to hold most of your dataset in memory, and that not having enough will result in more Read IOPS, as information is swapped into memory.

Our instance (SQL Server m3.medium) generally shows 150MB of "freeable memory," which doesn't seem like much of a margin. Our read iops ranges from 6-8 on average with momentary spikes to as much as 120 (or in one isolated case, 224).

We are not unhappy with our performance right now (coming from a far-inferior hosting environment), but as we try to decide whether to purchase a reserved instance, we'd like to know we're "ok" on resources for the term of the lease.

Thanks for any insight!

we'd like to know we're "ok" on resources for the term of the lease.

This is a "feeling" now you'll have to put this feeling into tangible, can be tracked and trended. This is how you can make predictions about your workload.

What would this look like? There are three areas you'll need to document:

What are the limits of my hosted solution?

There may be more than this, it's just to get you started thinking.

How much CPU (total) does the hosted environment have? (1 CPU = 100%)
How much RAM (total)?
How many Disks, Total Space, IOPS/Disk, IOPs Total
What is my networking bandwidth (Per card and totaled)

What should I monitor to trend my workload appropriately?

Whatever the limits of the hosted environment are, you'll definitely want to be gathering those metrics and statistics. There may be additional items that are Application defined which should also be included.

For example, if your application is a website that takes orders from users and the fulfills them there may some metrics you could add at the application layer:

Orders/Hour
Milliseconds to accept/place an order

Then the environment metrics based off of the first paragraph of information (not exhaustive, just an example):

CPU usage
Page file/Memory
Per disk IOPs, average seconds per IOP (transfer), etc.
Network Bandwidth

What are acceptable SLAs?

If an order takes 50 ms to place, is that bad? Is 2.5 seconds good? How many orders per hour must be accepted, minimally? Are there stored procedures associated with these orders that should be watched and have certain thresholds put on them?

Putting all of this information together will allow for the forecasting of expected volume, current volume, and potential needs in order to meet your SLAs for the business.

How many places do I know that actually do this? You'd be surprised. It's taken a few of them years to put it in place but many have these statistics readily viewable and available at a moments notice. Is it easy to do? Yeah, it's just time consuming. If you don't want to put in the time, just buy a bigger/better hosted solution - if you want to create a repeatable diagnostic infrastructure in order to provide this information then be willing to customize it to your environment.

Summary

I can't speak for RDS (amazon) and in all transparency, I work for Microsoft - but there are other hosted solutions such as Azure that already monitor many of these systems based items (CPU, Memory, IO, etc) for you. This puts the onus on you to grab the information and create your own application based metrics.

Each environment is different and while the system based attributes are a great start for monitoring, the whole picture is needed in order to make appropriate business decisions.


Display multiple IP address values for one system in a single row I cannot for the life of me get the systems with multiple IPs to display under one row. Below is the current SQL statement I have. systems with more than one IP show up as duplicated on the report.

SELECT SYS.Netbios_Name0 AS [Computer Name],
       SYS.User_Name0 AS [Last Login User],
       SYS.User_Domain0 AS [User Domain],
       Sys.Resource_Domain_OR_Workgr0 AS [System Domain],
       v_GS_Computer_System. Manufacturer0 AS [Manufacturer Name],
       v_GS_COMPUTER_SYSTEM.Model0 AS [Model Name],
       v_GS_COMPUTER_SYSTEM.SystemType0 AS [System Type],
       v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [Bios Version],
       v_GS_PC_BIOS.SerialNumber0 AS [Service Tag],
       v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Active Processors],
       v_RA_System_IPAddresses.IP_Addresses0 AS [IP Addresses]

FROM v_R_SYSTEM SYS
LEFT JOIN  v_GS_COMPUTER_SYSTEM on SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
LEFT JOIN v_GS_PC_BIOS on  SYS.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_RA_System_IPAddresses on SYS.ResourceID = v_RA_System_IPAddresses.ResourceID

If you're okay with a single column showing the IP addresses as a comma-separated list:

SELECT SYS.Netbios_Name0 AS [Computer Name],
       SYS.User_Name0 AS [Last Login User],
       SYS.User_Domain0 AS [User Domain],
       Sys.Resource_Domain_OR_Workgr0 AS [System Domain],
       v_GS_Computer_System. Manufacturer0 AS [Manufacturer Name],
       v_GS_COMPUTER_SYSTEM.Model0 AS [Model Name],
       v_GS_COMPUTER_SYSTEM.SystemType0 AS [System Type],
       v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [Bios Version],
       v_GS_PC_BIOS.SerialNumber0 AS [Service Tag],
       v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Active Processors],
       [IP Addresses] = STUFF((SELECT ',' + rasi.IP_Addresses0
         FROM dbo.v_RA_System_IPAddresses AS rasi
         WHERE rasi.ResourceID = SYS.ResourceID
         FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
FROM v_R_SYSTEM SYS
LEFT JOIN  v_GS_COMPUTER_SYSTEM on SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
LEFT JOIN v_GS_PC_BIOS on  SYS.ResourceID = v_GS_PC_BIOS.ResourceID;

(If you're still getting duplicates here, it's because of the other joins, not the IP addresses.)

If you need each IP address represented under its own column, you'll need to figure out the highest number of IP addresses that exist for any given system, and build a PIVOT statement using dynamic SQL. Not something I can invest in at 5:00 PM and when I don't even know you need it.


How to Ensure of Data MariaDB Galera Cluster's Safety? MariaDB will send a copy of SST to every server which request for it even without checking whether it is in the gcomm:// list. So I can easily get other people's data by simply install a fresh copy of cluster and add the target server's address in gcomm:// list. Then how I can ensure my data's safety?

Well... It seems that there is no way to implement auth unless I use mysqldump but finally I found an alternative way to secure my SST using certificate and private key.

Just self-sign a CA, and create a server private key and certificate, add them to wsrep_provider_options and everything is done. The attackers cannot retrieve a copy of SST as he cannot provide the same key pair for data signature and encryption.

The configuration file should be something like this:

[mysqld]
...
wsrep_provider_options="socket.ssl_key=/path/to/private.key; socket.ssl_cert=/path/to/certificate/file.crt; socket.ssl_ca=/path/to/CA.crt; socket.ssl_compression=YES";

I have written an article about how to implement this idea in my blog, though it is in Simplified Chinese.


SQL server on VMWare restart loses five days of data I am just now in the midst of a strange issue. A client moved their SQL database server from Hyper-V to VMWare on Saturday. Yesterday they called me complaining about performance. In the brief time and limited access I had I was able to see a lot of indicators of poor I/O (and many other problems) so I made a few recommendations.

They restarted their SQL server to add on the VMWare tools and take care of a few other tasks. Upon restart their data reverted (from their perspective) to the moment they brought it up on the new VM. They found that the new database was pointed to the old iSCSI drives instead of the new vmdk files and wasn't writing anything at all to disk. They tried restoring their backup but it fails every time before completion.

Did the database just fill up the dirty cache, never writing to disk? How in the world did the database continue to function for five days without presenting anything more than slow I/O to the end users? A complicating factor is they didn't have any alerts set up on the database that might have warned them of these kinds of problems. The event logs on the server itself show that there were numerous write errors.

The problem was never definitively resolved. What was found was that at some point their database was completely truncated by an unknown command. Every time they tried to restore the database and roll forward, they were re-truncating their DB.

They started rolling forward a little bit at a time until they found the moment when it went bad. At that point they cut me out of the troubleshooting loop so I was not able to participate in the post-mortem. My guess is that someone did something really stupid on the database and they didn't want me to dig too deeply.


How should I model a binary-tree like data using Adjacency List? I'm making a system where a user can recruit others, resulting in a binary tree model of users. I have decided to use Adjacency List to model the data but I have some doubts about the number of fields in a table.

I made the users table, there are 24 fields including the node fields of a binary tree:

parent_id, side(left child or right child of its parent), indicated_id(the guy who recruited the user may be different of its parent in the binary tree) + 21 fields(email, name, password, tokens, phone, mobile, etc)

So my doubts are:

1)Should I make a "nodes" table to wrap the user attributes that are not related to the binary-tree like this:

parent_id, indicated_id, side, user_id(pointing to the users table)

Or put everything in the users table? I think if I put everything in the users table I will avoid JOINS and the performance would be better. But if I wrap in a nodes table I will be able to query for a sub-tree without read unrelated fields and Join only the resulting query, so the perfomance lost would be negligible?

Besides that, is there any problem to have a table with 20-30 fields that justify the use of another table for a 1-1 relationship?

2) Is there any better way to model an Adjacency List?

If you set up an index over the binary-tree related fields, leaving the fields in the table should have more or less the same performance as if you had them into their own table with a full covering index (as PostgreSQL supports index-only scans as of v9.2). It probably isn't a bad idea to set up some tables with filler data and do some test cases, though.

In regards to 2), there is a slightly different way you can represent this kind of data, and it really depends on the way you expect to be querying it. This might not be useful, but might give you some food for thought:

For my organization I had to come up with a way to represent organization structure in such a way that it facilitated very fast queries of the kind "give me every person who reports up to X but has direct reports", or "give me the list of persons who are within Z reporting levels to  this person". The solution is a slightly modified adjacency table of the form:

 h_ID, emp_ID, m_ID, lvlsAbv

where h_ID is an autogenerated key, emp_ID is the employeeID, m_ID is the managerID, and lvlsAbove is the # of reporting lvls difference between the 2 people. This means that each employee has multiple rows (1 for each manager above them).

Example:

h_ID    emp_ID    m_ID   lvlsAbv
42530   211432  254192  1
42531   211432  197829  2
42532   211432  256373  3
42533   211432  255628  4
42534   211432  256978  5
42535   211432  3735    6

The result is a slightly larger table, but is still small enough (size wise) to easily justify a covering index over the whole thing.

The advantage of this kind of structure is the ability to write very simple queries against relational properties of the tree (ex: "select everybody that is downtree of person X"). The downside is that it requires more work to construct and maintain (a lot more).


Reducing Log Impact During Re-Indexing We use Ola's maintenance solution and its great.

Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process.  For a 1TB DB upwards of 300 GB of log can be generated.  This causes mirroring backlogs/delays and also cause Data Protection Manager to take a long time to sync up with its off-site DPM partner server (sometimes several Days!).  As we approach a time where we will have a Second site on warm standby we know that this delay in having off-site backups available during the vulnerable period after Index maintenance could be the Achilles heal. We are considering a larger pipe between the sites for Avail Group but to generate less burst Log activity would be great.

To mitigate this we have done 2 things with only minimal impact.  First we spread out the weekly re-indexing by introducing Delays, purposely slowing a 3 hour process to about 8 hours or so.  Secondly "some" key tables are maintained by a process that runs hourly resulting in Just in Time re-indexing.

In a large and active OLTP DB with some LOB what are the rule of thumbs for re-indexing frequency, % of database affected, # indexes that should be rebuilt less frequently?  Is weekly rebuild overkill?

For a 1TB DB ... Is weekly rebuild overkill?

I found it hard to believe that your OLTP system goes over the course of a single week and reshuffles 1TB of data so bad as to require a rebuild. Very few use cases would ever call for something so dramatic. Unless you have an explanation why do you need such high frequency I will say no, one week is way way too often.

  are the rule of thumbs for re-indexing frequency, % of database affected, # indexes that should be rebuilt

Yes, the same rule of thumb that applies to almost every performance related action: you measure impact. You establish a performance baseline, you measure the deviation from it over time, and you measure the impact of reindex actions. W/o basic measurements is always going to be a shot in the dark.

As for the original question: how do you reduce the amount of log generated during reindex? There are only two viable solutions:

use minimally logged operations
rebuild less, which require partitioning

As you write causes mirroring backlogs/delays it means option one is off the table as minimally logging and mirroring don't mix. That leaves only the option to rebuild less. Partition rebuild can come in handy, but only offline partition rebuild is supported. You can minimize the offline time by using fast partition switch (ie. you rebuild a copy of the data and then you switch the optimized rebuilt data).

Ultimately, for 1TB, you should never rebuild it. Your old data, which never changes, should lay at rest, compacted and archived. Current data is subject to churn and changes and you should only have to rebuild what has changed.


CPU cost in SQL Availability Group for synchronous and asynchronous DB's We are looking at setting up a new SQL Always On Availability Group environment for SQL Server.
We are wondering if there's a difference in CPU usage of a synchronous or an asynchronous node?

Also one of my colleagues is saying there's a limitation in the number of Listeners (and so also AG's since it's adviced to have a listener per AG) but i'm not able to find anything that says that you shouldn't create more than 10 listeners in an environment. The only thing I'm able to find is that there's an AD-setting that you have to change to be able to have more then 10 listeners.

Thanks in advance!

We are wondering if there's a difference in CPU usage of a synchronous or an asynchronous node?

Yes, there will be a difference but only because these modes talk to each other in vastly different frequencies and ways. The difference will entirely depend on workload and there is no way to "guesstimate" what that will be.

  Also one of my colleagues is saying there's a limitation in the number of Listeners …

There technically isn't and you're correct, generally at least 1 listener per availability group so that you can always connect to the primary. Maybe you have some other type of network appliance or device that can do probing and update an internal object to always point to the primary - that's possible and a listener may not be needed. Sometimes there are company requirements where all applications have to use a specific endpoint/dns for each application. So, generally, this will depend on your requirements but there isn't anything really stopping you from creating listeners except for IP addresses in an available range (if using IPv4).


To have PostgreSQL-like Partial Index in MySQL 5.5 I have big data where I only select a small interval of data at a time such that the selection is always in a sequence.
I am trying to implement PostgreSQL like Partial index in MySQL which is targeted for such purposes.
I am not sure if the partial unique constraint is the same as the one which I want.

Code in PostgreSQL 9.4

CREATE UNIQUE INDEX dir_events
    ON events (measurement_id)
    USING btree
    (eventBody)
    WHERE is_active;

Attempt on ypercube's partial index in MySQL

CREATE UNIQUE INDEX dir_events
    [index_type] -- TODO what here?
    ON events (measurement_id, is_active)
    [index_type] -- TODO what here?

How can you create PostgreSQL-like partial index in MySQL 5.5 or similar?

Neither MySQL nor the siblings (MariaDB, Drizzle, etc) have implemented partial indexes.

What you can do, with this restriction in mind:

a) make a simple (not partial) index on (is_active, measurement_id). It will be used in queries where the partial index would. Of course if the is_active column is 3% True and 97% false, this index will be much bigger (than a partial index). But still smaller than the table and useful for these queries.
Another limitation is the index cannot be UNIQUE with this solution so the constraint is not enforced. If the index is created with UNIQUE, the uniqueness will be enforced for rows with is_active = FALSE as well. I assume you don't want that:

CREATE INDEX dir_events
    ON events (is_active, measurement_id)
    USING btree ;

b1) (the simple variation of b):  add another table in your design, with only the primary key columns of events and a foreign key to events. This table should only have rows where the is_active is true in the original table (this will be enforced by your application/procedures). Queries with is_active = TRUE would be changed to join to that table (instead of the WHERE condition.)
The UNIQUE is not enforced either with this solution but the queries would only do a simple join (to a much smaller index) and should be quite efficient:

CREATE TABLE events_active
( event_id INT NOT NULL,         -- assuming an INT primary key on events
  PRIMARY KEY (event_id),
  FOREIGN KEY (event_id)
    REFERENCES events (event_id)
) ;

INSERT INTO events_active
  (event_id)
SELECT event_id
FROM events
WHERE is_active = TRUE ;

b2) a more complex solution: add another table in your design, with only the primary key columns of the table and measurement_id. As in previous suggestion, this table should only have rows where the is_active is true in the original table (this will be enforced by your application/procedures, too). Then use this table only instead for queries that have WHERE is_active = TRUE and need only the measurement_id column. If more columns are needed from events, you'll have to join, as before.
The UNIQUE constraint can be enforced with this solution. The duplication of measurement_id column can also be secured to be consistent (with an extra unique constraint on events and a composite foreign key):

ALTER TABLE events
  ADD UNIQUE (event_id, measurement_id) ;

CREATE TABLE events_active
( event_id INT NOT NULL,
  measurement_id INT NOT NULL.
  PRIMARY KEY (event_id, measurement_id),
  UNIQUE (measurement_id),
  FOREIGN KEY (event_id, measurement_id)
    REFERENCES events (event_id, measurement_id)
) ;

INSERT INTO events_active
  (event_id, measurement_id)
SELECT event_id, measurement_id
FROM events
WHERE is_active = TRUE ;

c) maybe the simplest of all: use PostgreSQL. I'm sure there are packages for your Linux distribution. They may not be the latest version of Postgres but partial indexes were added in 7.0 (or earlier?) so you shouldn't have a problem. Plus, I'm confident that you could install the latest version in almost any Linux distribution - even with a little hassle. You only need to install it once.


Converting CHAR to BIT I have a column with char(16) data type and it was mistakenly set to char(16) actually it should be bit(1) because it contains only three distinct values as follows:

16 characters long string
10 characters long string
blank value

Now, I want to convert blank and 10 characters long string to 0 and 16 characters string to 1. But table is quite large so I think I should avoid any experiment and pursue the best approach.

First, I thought to create and index then update the column and then change data type.

Can you be more specific on "quite large"?  In general you're right, you shouldn't just 'do it live' in production with any change.  Do you have any kind of QA or test environment you can do a run on first?

This isn't the most sexy ninja one line approach but you could

Add a new bit column
Issue updates setting the new column = if( length(old_column)=16,1,0)
Alter table to drop old column and rename the new column


SQL server always ON patching Always on(SQL server 2016) is configured between four standalone servers Server1,Server2,Server3,Server4. These servers contains one availability Group(AG01) which contains 3 databases. How to apply SQL server patch for these servers? Do we need to apply patch for all servers at same day? These servers are production servers.

The process to upgrade/patch Always On Availability Groups is documented extensively in the official SQL Server docs. Typically this is done as a rolling upgrade, where you move from secondaries to the primary SQL Server. Depending on your setup you would do the following:

Patch remote secondaries
Patch local secondaries
Perform a failover of the primary
Patch former primary

You don't typically do them all the same time, you roll through the servers. When I patch our servers, I normally do the secondaries one day and then the following, I'll perform the failover and finish patching the former primary.


Grouping results based on link table I have three tables:

users

+----+-------+
| id | name  |
+----+-------+
| 1  | dave  |
| 2  | cher  |
| 3  | meg   |
| 4  | sarah |
+----+-------+

groups

+----+-------+
| id | name  |
+----+-------+
| 1  | admin |
| 2  | super |
+----+-------+

user_groups

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1  | 1       | 1        |
| 1  | 2       | 1        |
+----+---------+----------+

I want to create a query so that I return an array of group names which contains an array of users. I don't want to return groups which have no users, but I would like to return users which have no group.

admin
    - dave
    - cher
- meg
- sarah

At the moment I am getting the groups which have items first.

SELECT name FROM groups
INNER JOIN user_groups ON groups.id = user_groups.group_id

Then iterating through the groups and adding users.

SELECT id, name FROM users
INNER JOIN user_groups ON users.id = user_groups.user_id
WHERE user_groups.group_id = $group_id

Is there a way to combine these queries?

You should be able to easily combine your current queries to get the group and users by using the following:

SELECT g.name group_name, u.id, u.name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id;

See SQL Fiddle with Demo. This will give you multiple rows for each group if there is more than one user per group, similar to this:

| GROUP_NAME | ID | NAME |
--------------------------
|      admin |  1 | dave |
|      admin |  2 | cher |

You could also use the GROUP_CONCAT() function to return a comma separated list of the users with each group:

SELECT g.name group_name,
  GROUP_CONCAT(u.name SEPARATOR ', ') name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id
GROUP BY g.name;

See SQL Fiddle with Demo. This returns a result:

| GROUP_NAME |       NAME |
---------------------------
|      admin | cher, dave |

Then if you want to also include the users that have no group associated with them, you could use a UNION ALL query:

SELECT g.name group_name, u.id, u.name
FROM groups g
INNER JOIN user_groups ug
  ON g.id = ug.group_id
INNER JOIN users u
  on ug.user_id  = u.id
UNION ALL
SELECT '' group_name, u.id, u.name
FROM users u
WHERE id not in (select user_id
                 from user_groups);

See SQL Fiddle with Demo. This returns:

| GROUP_NAME | ID |  NAME |
---------------------------
|      admin |  1 |  dave |
|      admin |  2 |  cher |
|            |  3 |   meg |
|            |  4 | sarah |


Normalized Data Store - Confused with prefixes to use I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from [Staging] and load it into [NDS], which will hold all history.

I've pretty much finished the T-SQL script that will create the tables and constraints in the [NDS] database, which contains Master and Transactional tables, that will respectively feed [DDS] Dimension and Fact tables in what I'm intending to be a star schema.

I'm given myself the following rules to follow:

Tables sourcing [DDS] dimensions are prefixed with DWD_
Tables sourcing [DDS] facts are prefixed with DWF_
Foreign key columns are prefixed with DWK_
Surrogate key column is prefixed with the same prefix as the table. Which means the surrogate key is always either:

DWD_Key for a DWD_ table, or
DWF_Key for a DWF_ table.

Control columns are prefixed with the same prefix as the table. For example...

The DWD_Customers table has control columns:

DWD_IsLastImage
DWD_EffectiveFrom
DWD_EffectiveTo
DWD_DateInserted
DWD_DateUpdated
DWD_DateDeleted

The DWF_InvoiceHeaders table has control columns:

DWF_DateInserted
DWF_DateUpdated
DWF_DateDeleted

Primary keys (/surrogate keys) are always prefixed with PK_ followed by the table name (including the table prefix) - e.g. PK_DWD_Customers and PK_DWF_InvoiceHeaders.
I also added a unique constraint on natural keys, and those are always prefixed with NK_ followed by the table name (including the table prefix) - e.g. NK_DWD_Customers and NK_DWF_InvoiceHeaders.
Foreign key columns are always prefixed with DWK_ followed by the name of the referenced table (without its prefix) and the word "Key" - e.g. DWK_CustomerKey.
Foreign key constraints are always named FK_[ParentTableNameWithPrefix]_[ChildTableNameWithPrefix].
When a table has multiple FK's to the same table, the name of the FK column is appended to the constraint's name, e.g. FK_DWD_FiscalCalendar_DWF_OrderDetails_DeliveryDate.

All prefixed columns have no business meaning and should never appear in views; this leaves me with, I find, a pretty clean and consistent design, and create table scripts looking like this:

create table DWD_SubCategories (
     DWD_Key int not null identity(1,1)
    ,DWD_DateInserted datetime not null
    ,DWD_DateUpdated datetime null
    ,DWK_CategoryKey int not null
    ,Code nvarchar(5) not null
    ,Name nvarchar(50) not null
    ,constraint PK_DWD_SubCategories primary key clustered (DWD_Key asc)
    ,constraint NK_DWD_SubCategories unique (Code)
);

So, my question is, is there anything I should know (or unlearn) before I continue and implement the ETL to load data into this database? Would anyone inheriting this database want to chase me down and rip my head off in the future? What should I change to avoid this? The reason I'm asking about prefixes, is because I'm using DWD and DWF, but the tables are technically not "dimension" and "fact" tables. Is that confusing?

Also, I'm unsure about the concept of natural key - am I correct to presume it should be a unique combination of columns that the source system might consider its "key" columns, that I can use in the ETL process to locate, say, a specific record to update?

There is always at least something else you should also know and almost equally, always something else you should be consciously putting a stop to.  Specifically in the context of data warehousing, which is a relatively fledgling sector, leveraging relatively new technologies.

In regards to what I've seen in the real world, walking into a company for the first time and seeing what I'm understanding about your design would be genuinely tear-inducing:  Tears of joy and relief.  From the outset, you are well on your way to beginning what appears to be a well thought-out ( well engineered ) ETL / data warehousing system.  As with the implementation of any software product, your mileage may vary as the solution grows and is consumed by the business, but fundamentally, you are on The Right Track™ ( and yes, you know what a natural key is ).

I've found there to be a number of challenges with these type of solutions, which I will touch upon to reinforce some of your decisions and perhaps lend some insight into the road ahead of you.  Firstly, the number of times I've found myself in a predicament on account of a developer ( even fellow database administrators / data professionals ) misunderstanding the context of a control column ( using, for example running a process against the DateInserted column, a mere time stamp of insertion, over the DateReceived or similarly named column, intending to relate a row to a particular date of occurance ), that while I agree completely with the cautions @Aaron Bertrand raises, I feel that the prefixes for your control columns could actually be leveraged as a sort of flag to help prevent their misuse.  Obvious should be obvious of course, but much like writing code in general, explicit is preferable.  That said, I would almost certainly leave such prefixes out of the indexes and such ( probably even keys - PK types can and should stay in my opinion, but unless there's a real threat of DWD_SubCategories and DWF_SubCategories existing in the same schema, they really are just fluff ).  I think the concern about the DWD and DWF prefixes is valid, but they'll be living in the [NDS] catalog and would serve to indicate intent, making it completely fine to use the nomenclature in that manner.

The second ( and perhaps most infuriating ) challenge is one of cross-training your coworkers.  All of the software engineering, usage flags and design practice rules are completely for naught if your striving-for-paycheque-over-excellence colleagues get involved and do their less than very best ( or to be fair, are even just simply having a bad day ).  Do keep in mind that large projects generally have many fingers in the pot, so it is imperative that those fingers are behaving well.

The last thing I'll touch on here is to always keep in mind the actual value of any ETL system to a business.  Of the Extract, Transform and Load paradigm, the first and final letters have absolutely no business value, so you will want to work on making the development and maintenance of both the Extract and Load processes as minimal as possible - the "real" work will be done in the Transform phase, so you will want to automate the E and L steps as much as possible so that you can focus on making ( and keeping ) your solution valuable to the business unit by actively working on the transforms.

All of that said, I've only had the opportunity to work on a handful of different warehousing solutions so perhaps a more knowledgeable user could step in and remove my foot from my mouth if I need correcting.  As I said initially, this is one of those areas where one can always learn or unlearn something, and I am absolutely no exception.

Oh, one more thing ( and probably the most important ) - Unit Test!  Once your E and L are working as intended and you've had the opportunity to put a few domains through your T solution, get somebody to vet the results.  If they're good, save the result set somewhere, so that when you make changes ( and you will, without a doubt ) you can ensure you haven't broken something, somewhere else.  Again, automate this process as much as you possibly can ( it's another 0-value process to the business, until they go without it at least ;) ).  I generally set up a separate schema or catalog for this purpose.

Hopefully some of what I've said will be useful to you!

As an update, @Aaron Bertrand's schema separation seems like it would be quite a good way to avoid unnecessary prefixing as well, so certainly consider that ( I know I will haha ).


db2-AIX multiple admin accounts I'd need to give (aside the instance owner and the administration server) the user rights as database administrator to a group of people already created on the AIX server. Is it possible? what will happen in case of someone will modify something to the db2 config for example?

Thanks

If you only need them to be an administrator of the database, you can grant them DBADM authority.

grant DBADM on database to (user or group) <name>

Set up an AIX or LDAP group for your admins and then assign them to that group. Then run the above command to assign the admin authority to that group.

Some further information for you:
If you have 9.7 or higher, then you can separate out other authorities such as granting DBADM WITH or WITHOUT DATAACCESS and WITH or WITHOUT ACCESSCTRL (WITH is the default). There is also the SECADM authority.

To grant everything under 9.7 or higher to mimic what you would have received in lower versions of DB2 then

grant DBADM,SECADM,DATAACCESS,ACCESSCTRL on database to (user or group) <name>

If you are talking about instance authorities, then you can add them to the groups that are assigned to the SYSADM,SYSCTRL,SYSMAINT, or SYSMON DB2 groups. (Although it sounds like you don't want that. I am merely mentioning it for holistics.)

For a good overview of DB2 priveleges, I would recommend checking out the Authorities Overview page in Information Center.


Embedding vs foreign table in one-to-one relations? I'm currently taking a university course about databases.

Consider the following modeling scenario where a car exists only in the context of its owning student:

Student (0,1) <---> (1,1) Car

As I'm reviewing the lecture material, there are two transformations available for this:

1) Put car into it's own entity type and create a student-car relation (store car's primary key in student).

2) "Embed" car's attributes into student entity type with a boolean flag has_car.

With 1) the student table and over all database size is smaller because only a NULL is saved to car_id. However, getting student with a car requires the car's attributes to be fetched from a foreign table.

With 2) there's no need for reaching into foreign tables. However, the database and student table is now bigger because we are saving all of the car's attributes even for student's with no car.

If there are very few students with a car, 1) makes sense.

If there are only very few attributes we store about car, 2) might make sense.

In a more general situation, how does one decide which transformation to use? Could car have a huge amount of attributes and 2) make still sense? Is it possible to calculate the correct solution by calculating the space the car's attributes take?

Option 1 is close...

However:

If a student can own zero to many cars:

you'd have ownerstudentid in the car table
car is a child of student

If a car can exist without a student, I'd consider a many-many table carstudent with a constraint on carid to allow zero or one owning student

Note:

This is where tools like NORMA come in useful to capture these relationships and constraints in plain English. See Object Role Modelling


query all audit entries defined with 'audit' command what is the command to list all audit entries defined with audit command? Is there any command for it or maybe tableName to query from?

Depending on the method you use for auditing, you can find that information in the below views:

DBA_OBJ_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
AUDIT_UNIFIED_POLICIES
AUDIT_UNIFIED_ENABLED_POLICIES

The content of the audit trail, also depending on configuration and version, can be found in the below views:

DBA_AUDIT_TRAIL
UNIFIED_AUDIT_TRAIL


Is the syntax of Oracle 10g and MySQL the same? Is the syntax of Oracle10g and mysql are the same? For example the select query? Thanks, I'm using MySQL and want to try Oracle 10g for my java.

They're vastly different when it comes to other features, such as security, programming languages, data types, tools, end user support, requirements, etc.

However, they're both compliant with basic SQL92 (and perhaps other SQL standards), so one can do the same basic schema creation and data querying and manipulation. You should be able to do the basics from java applications without noticing where you're connected to (and this applies to several other databases).


Database design implementing support for different languages Today I was practicing database design, for example I was trying to build a database design where I would like to store my favorite shows. I have normlaised this database to the third form (at least I think I have). But I have faced a problem with this design, actually, how can I preform a such query:

Is there any show that has language (let's say Italian) and status Emitting and there are more than 5 episodes available?

I think I have made a mistake with the language and show_internation_data table, but I am not sure...
Also if you could tell me how bad this design is and what to improve, or just throw an awesome article, that would be awesome!!
Diagram:
Here is the image:  http://i.stack.imgur.com/abhwx.png

By my reckoning, then you should not have show_languages between show and show_international_data because it's not an association table between the two, and because you do not want the language column to be filled out for each association between a show and show_international_data.
Basically if you have 10.000 shows and 20 languages, you'll need 20 x 10.000 versions of the 'language' field filled - lots of redundant data and chance for mistakes.
Also associations tables like that are usually used for n:m relationships and not 1:m and because one shows international data cannot belong to another, it shouldn't be mapped as n:m.

Instead show_international_data should be connected directly to show with show_id being the FK in it.
Then you can make a language table (language_id, language) and have that language_id being the second FK in show_international_data, which combined makes up it's PK.
So something like this:

show -> show_international_data <- language.

In this manner you can reuse your language table on everything which needs to be localized with a translation/details (international_data) type structure without.

I don't know what recommendations is going to contain, but that might also need to be localized?

show_title will most likely also need to be localized, because titles differ between countries/languages as some countries dub or completely reworks titles of shows. So that title properly needs to be put into international data and thereby removing that table.
Also you can put an original_title on the show base table.

If you do need to have alternative titles on top of this, then a secondary title table will start to make sense, and then you should - most likely - localize it by having a language_id FK enabling multiple alternative titles per language.


Duplicating some rows of a data flow in SSIS I have a data flow to migrate data from an old database to a new one. The old design had all the data and historical information (changes) stored in a single table with a "version" (incrementing integer) against the row.

The new design has two tables, one for the "current" state of the data and an audit (or history) table which records changes using a trigger. Therefore only one row exists for the "current" data and there are many history rows.

In my SSIS package I am using the following components to copy the current data to one table but then send all the data to the audit table.

The Multicast is used to split the stream of data and the Conditional Split identifies the "current" row and sends that to the Order table (the table is not actually called Order, before anyone comments about using a reserved word for a table name).

I created this flow because I could not see a way to use the Conditional Split to send all the data to the Audit destination and only the current row to the other.

I'm assuming that creating all the duplicate data and then discarding it to a Trash Destination is not very efficient and since I have about 52m rows to migrate I'm worried the transformation is going to take days.

Is there a better (more efficient) way to achieve the data split?

Note on data: I have applied a row_number() to the data which allows me to identify the "current" row as number 1, all the rows including "current" need to go to the Audit table destination.

EDIT:
I have found an alternative to the Multicast and Conditional Split suggested by this blog post from SSIS Junkie: Multiple outputs from a synchronous script transform

It uses a Script Component to send data to one or more output. I'm trying that method to see if it's any faster but after seeing Kenneth's answer and suggestion about removing the Trash Destination I'm not sure it will be.

I see no glaring issue with that data flow. I always suggest to do as much work as possible in your source queries, so if you can create a dataset at the outset that allows you to populate both tables via a simple split, it would certainly use less memory. But things like that are not always possible depending on the data source and data format.

Also, the trash destination is good for development/debugging but does no good in production. Remove it. Let the 'Unwanted Data'expire at the split. SSIS can figure out the rest.

As long as you avoid blocking components (UNION, MERGE, etc.) there isn't any reason this process would take days. I frequently process many millions of rows in SSIS without issue. SSIS is only as slow as the person who designed the process.

Is it currently having performance issues?


How to get pg_locks related to a specific database in Postgres? This question is related to my previous one here
In SQL Server, I could get the lock type from a specific database using the code below:
SELECT request_mode FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars');

In Postgres 11, how to filter the pg_locks result to a specific database like how SQL Server's resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars') works?
Thanks

As documented in the manual pg_locks contains a column database which you can join to the pg_database view:
select l.*
from pg_locks l
  join pg_database d on d.oid = l.database
where d.datname = current_database();


Oracle XE Fedora 15 Installation problem I got a problem installing Oracle XE on Fedora 15. Hope you can help me.
I used this tutorial(that worked great for F14) but didn't got lucky with F15.
The problem is that during installation goes ok,but then I try to connect to the XE web without success.
So I tried this
su - oracle
./app/oracle/product/10.2.0/server/bin/sqlplus /nolog

and got this error
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found

So I want to know what am I missing.
I thank your help in advance

Some thoughts:

First, I also have tried to get Oracle XE up and running under F15. Finally managed it, but it wasn't as simple as I would have thought it should be -- It's easier to get the silly thing configured under Ubuntu. IIRC, I just ended up installing EE instead (under F15).
Second, your environment needs to be set up correctly in your .bash_profile (in your home directory). Make sure to export ORACLE_HOME, ORACLE_SID (=XE), and make sure $ORACLE_HOME/bin is exported in your PATH. Once done, log out and back in and you should be able run sqlplus fine. (If you don't want to log out, just run ". ~/.bash_profile", and it should also work.)
Third, I don't know why Oracle doesn't set this up during the install process. And of all users not to have the environment set up (when XE usually has to create the user from scratch)... the oracle user should simply be set up with it from the start. But that's a rant and not really related to your question.

Hope that helps...


Permissions on SQL Server Sequences The AD Group to which I belong has db_owner permissions on database LandingZone. The application I am debugging, that is running under my ID, is doing a SELECT NEXT VALUE FOR ThisSchema.ThatSequence. I am getting the following error:

Msg 229, Level 14, State 5, Line 6
The UPDATE permission was denied on the object 'ThatSequence', database 'LandingZone', schema 'ThisSchema'.

I think I'm running into a Least Privilege issue but the production DBAs disagree.

Any suggestions?

db_owner is supposed to have all permissions within given db, so you should be able to update that sequence without any problem - if your app really runs under your UserID

1) Check the connection string that app is using

If connection string does not contain Integrated Security = True or Trusted Connection = True, then you have to add those to connection string

2) if app is running from within some web server (IIS Web server etc.), and its included into some Application Pool, you need to make sure this Application Pool runs under security context of your Windows User...

3) to verify that your application is really connecting under your Windows credentials to SQL Server, while the app is connected to DB, check out following DMVs at SQL Server:

  select * from sys.dm_exec_sessions
  select * from sys.dm_exec_connections

(those can be linked by session_id, find session/connection of your application and make sure it runs under your Windows credentials (login_name "YourDomain\YourUserName")

4) if App is running and connecting to DB using your Windows creds, and you still have this permission issue, then you have to make sure your Windows user if really is in AD Group, and AD Group is really added to db_owner role at given database...


How to use a recursive CTE to get ancestors in a hierarchy create table division (
  id serial primary key,
  name varchar not null
);

-- nested sets table

create table location (
  id serial primary key,
  name varchar not null,
  division_id integer not null references division(id),
  tree_id integer not null,
  parent_id integer references location(id),
  lft integer not null,
  rgt integer not null,
  level integer not null
);

create table report (
  id serial primary key,
  somevar_1 integer not null,
  somevar_2 integer not null,
  tyme timestamptz not null,
  -- other stuff
  location_id integer not null references location(id)
);

-- division hierarchy is Division0 > Division1 > Division2 > Division3
-- reports are generated at Division3, and aggregated at
-- either Division2 or Division1 (usually the latter)

-- this means that to get my data for Division1, i have to
-- do two self-joins on the location table
-- plus one for the report table

given the above schema, i'm experimenting with different ways to speed up querying for the sum of each somevar aggregated at either Division2 or Division1. right now, what i do is

select
  sum(somevar_1) as somevar_1, sum(somevar_2) as somevar_2,
  div1.name, div1.id
from
  report join location as div3 on report.location_id = div3.id
join
  location as div2 on div3.parent_id = div2.id
join
  location as div1 on div2.parent_id = div1.id
where
  -- for example, all reports this year
  date_trunc('year', tyme) = '2019-01-01'
group by div1.id, div1.name

the above query runs in about 4.8s locally with a row count a bit shy of 55k reports for 2019. this is just the base data, without any further processing. the processing step also does something similar, and it's in a web application, so the user is waiting for a not-insignificant amount of time.

for my question: can i use recursive CTEs to speed this up? i haven't yet grokked recursive CTEs, so writing one is still a bit beyond me at this point.

I would stick with one model. If you store both nested set information and parent information, you will have to maintain both, and you may end up with inconsistent information about your tree. If your main focus is on reporting both for div1 and div1, div2, you may consider GROUPING SETS. They are a UNION in disguise:

select sum(somevar_1) as somevar_1
     , sum(somevar_2) as somevar_2
     , div1.name as d1name
     , div1.id as d1id
     , COALESCE(div2.name, 'TOTAL') as d2name
     , div2.id as d2id
from report
join location as div3
    on report.location_id = div3.id
join location as div2
    on div3.parent_id = div2.id
join location as div1
    on div2.parent_id = div1.id
-- for example, all reports this year
where date_trunc('year', tyme) = '2019-01-01'
group by
    GROUPING SETS((div1.id, div1.name, div2.id, div2.name)
                 ,(div1.id, div1.name))

For the second grouping set (div1.id, div1.name) you will get null instead of div2.id, div2.name. That's why I used COALESCE to map the name to TOTAL.

You may also add a level of a GRAND TOTAL like:

    GROUPING SETS((div1.id, div1.name, div2.id, div2.name)
                 ,(div1.id, div1.name)
                 ,())

Grouping sets are the most general construction, but under certain situations (not yours), you can use GROUP BY ROLLUP and GROUP BY CUBE which are a bit shorter. See:

Grouping sets, rollup, and cube

If I get it right, you will always traverse div1, div2 and div3 levels in your queries and I therefor strongly doubt that a recursive CTE will perform better than 3 static JOINS. The power of RCTE is that it can traverse a variable number of joins. For a fixed number of levels, there is no performance gain.


composite index on bigints I have a User Relations table, which basically stores all the one-directional relations of users.
For example , User A follows user B ; User X blocks User Y

User ids:

the id of users are bigints.
It's not like I think that using ints would really end up in a bottleneck(unless my app grows to more than 2 billion users that is :) ), but as far as a I understand, if I integrate social networks signups, Facebook uses bigints for their user ids, hence I have to keep the id of the users as bigint.

Indexing :

At first I thought about making a composite index of both user (the relating and the related), but since both indexes are bigints, meaning 8 bytes each, wouldn't it be a waste to store such a monstrous index?
I ended up leaving only one column index, but not sure whether it's the wisest decision in this case.

I also thought of a variant with covering index - but in this case the relation_type(follow, block) will not be part of indexing/filtering, so maybe I should include that inside the composite index, but then I would have 8+8+4 bytes of index.
The tradeoffs between space and time are not clear to me in this case.
Would appreciate any thoughts on the matter.

First variant (one-column index):

CREATE TABLE user_relations (
    relating_user_id bigint NOT NULL,
    related_user_id bigint NOT NULL,
    relation_type smallint NOT NULL,
    created_at default current_timestamp,

    PRIMARY KEY (relating_user_id),
    FOREIGN KEY (relation_type) references user_relations_types (id)

    -- will having only one index affect the performance really ?
    -- should I include the type into the composite type ?
);

Second variant (using covering index and omitting PK) :

    CREATE TABLE user_relations (
        relating_user_id bigint NOT NULL,
        related_user_id bigint NOT NULL,
        relation_type smallint NOT NULL,
        created_at default current_timestamp,

        FOREIGN KEY (relation_type) references user_relations_types (id),
        CREATE UNIQUE INDEX relating_user_related_user_relation_type_idx ON user_relations
                    (relating_user_id, related_user_id) INCLUDE (relation_type);

        -- should I include the type into the composite type ?
    );

Indexes use space, and a 16 byte key is nothing to worry about.

So you should define the primary key on user_relations on (relating_user_id, related_user_id). If you need to search by  relation_type, it won't help to put the column into the INCLUDE list, since such columns cannot be used as filter for an index scan.

I see two options:

In addition to the primary key suggested above, have additional indexes on (relating_user_id, relation_type) and (related_user_id, relation_type).
If you want to have fewer indexes, define the primary key on (relating_user_id, relation_type, related_user_id) and define only one additional index. Perhaps you can live with it if the database allows several different relationships between the same users.

The deciding factor which indexes to define should be the queries you will have to serve frequently and efficiently. Design these queries, and the question will become easier.


Meaning of "The database X needs to be upgraded" in ClearTrace I'm trying out ClearTrace from http://www.scalesql.com/cleartrace/download.html , but it isn't very user friendly from my first impression.  I have a database in SQL Server 2008 that I'm adding in the ClearTrace - Options dialogue, but it says "The database mydatabase needs to be upgraded.  Would you like to upgrade it?".  If I click No, I can't continue.  What does it mean to upgrade the database in this context?  The support forums don't seem to answer this.

It seems to be that the Options dialogue is for the database that ClearTrace uses to store its own application data.  It created a dedicated database for it and it added its own tables to that database, during the 'upgrade'.


Do I need to rebuild heap table after rebuilding a non-clustered index? Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe someone had tested it before.

Do I need to rebuild a heap table after rebuilding a non-clustered index?

Do I need to rebuild a heap table after rebuilding a non-clustered index?

Rebuilding a heap automatically rebuilds any non-clustered indexes on the table (because the pointers to where the rows are changes during this process).

I would not rebuild a heap after rebuilding the NC index, because this would mean the NC index is rebuilt twice (which is just wasteful).

Rebuilding the NC index won't fix fragmentation in the heap.  So if you need to fix the heap fragmentation, rebuild that first (or put a clustered index on it), and both will be fixed.


SQL Server dates - can I get date and hour? In SQL Server, can I do the equivalent of to_char(date_field, 'YYYY-MM-DD:HH24') in Oracle to get 2012-01-30:23?

In SQL Server <= 2012 you can say:

SELECT CONVERT(CHAR(10), date_field, 120)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, date_field)), 2)
  FROM dbo.table ...;

Or

SELECT STUFF(CONVERT(CHAR(13), date_field, 120), 11, 1, ':')
  FROM dbo.table ...;

In SQL Server 2012 this is much easier; you can say:

SELECT FORMAT(date_field, 'yyyy-MM-dd:HH')
  FROM dbo.table ...;

Though FORMAT is considerably slower, so if you are doing this at scale, it isn't the one I would use (and in fact I would highly recommend you consider formatting your date output at the presentation layer, not in SQL Server).

SQL fiddle: http://sqlfiddle.com/#!6/72276/5


Replication Snapshot folder not cleaning up The job that I thought was supposed to clean up old snapshots isn't removing the old files.  The retention is left at default yet there are snapshots months old which remain.  The job runs as the agent account (maybe I should try a proxy account?)  and this account has full permissions to the folder.

I have checked sp_configure to ensure xp_cmdshell is enabled and it is.

The cleanup job runs the proc sp_MSsubscription_cleanup, however I can't see anywhere in that which would delete the old snapshot files.

Can anyone enlighten me?
Thanks

The MSDN Blog "SQL Server SSIS and Replication" has a post from a few years ago.
http://blogs.msdn.com/b/mangeshd/archive/2009/05/20/replication-snapshot-folder-cleanup.aspx

How these old snapshot folders are purged depends on the type of replication you are using:

For snapshot and transactional replication: Distribution cleanup agent purges the non-latest folders
For Merge replication: the snapshot agent itself will purge older folders.

Chris Skorlinski, one of the developers on replication, had (from a few years ago) the following to say:  "For merge replication, the snapshot agent ... is responsible for cleaning up the last generated snapshot. The snapshot agent will only try once to remove the previous snapshot files so if those files were locked at that moment (by the merge agent e.g.), it is possible that snapshot files will be left over."

He recommends that you check the snapshot agent history to see if errors were reported.

If this is a continuing problem perhaps you could create a PowerShell script to remove the older snapshot folders. And, of course, you can manually delete them, though that is not a useful long-term solution.


How to create a read-only server role on SQL Server 2012? I am granting the "View any database" permission when creating a new server role, but realized this permission only allows the user to view the system databases.

I am trying to create a server role that is read-only and can read any database.

Is there a way to create a user-defined server role to read user databases? Or do I have to do this through user mapping per user?

Posting this as an answer just because it is too long for a comment, and because it will be relevant to other users pretty soon.

SQL Server 2014 adds some new server-level permissions that will assist with exactly this type of scenario - they were designed with auditing in mind, but this type of requirement seems to fit that bill as well. You could simply add the following two permissions to a server-level login:

CONNECT ANY DATABASE

SELECT ALL USER SECURABLES

The former, like it sounds, allows the login to connect to any database. The nice thing about this is that it will allow this even for databases that are created in the future (provided you don't set explicit deny, which is how you can protect certain user databases from logins that have this permission). The latter allows the login to perform read operations on any database they have access to - so they can SELECT from tables, views, UDFs etc. but they will not be able to perform any UPDATE operations (I haven't tested if this permission understands when a stored procedure performs DML). These work great in combination if you want to give a login wide-open read access to the entire server, or to be more fine-grained you can grant traditional CONNECT privileges to certain databases, and the SELECT ALL USER SECURABLES right will only function for those databases where the login has explicit access to.

The 2014 security changes are documented here - well, partially; they forgot about the database-level permission ALTER ANY DATABASE EVENT SESSION - though that's not relevant here.


SQL File Upload to MySQL Server I'm fairly new to databases. I want to upload a .sql file to a mysql server. However the database I want to update contains many .frm .myi .myd files. Should I delete those files and replace with the .sql?

Any suggestions/tips would be greatly appreciated.

Back up the old database files to a dump using mysqldump -u username -p database_name > dump.sql then import the new sql file using mysql -u username -p database_name < new_sql_file.sql.


Inserting few millions of rows: Does indexing happens along with the insert operation I am inserting 50 million records to an existing table with a primary key , and a non clustered key.
My question is whether indexing of those will existing indexes will happen along with the insert operation or not.?
Or do i have to do an index rebuild/reorganize after insert to keep index updated.

My assumptions is when new records comes in data pages will get updated and so will index pages also.

May be this is a silly question,but i have to know this for some other activities for like bulk insert/update i perform in my tables.

Everything will be populated on insert, it is also one of the reasons why having too many indexes is considered bad practice on tables that have a lot of CRUD operations.

In your case, you only have a CLUSTERED and a NON CLUSTERED index (SQL Server creates a CLUSTERED index for a PK by default even if you don't specify it). Non clustered indexes have something called a "clustering key" which is basically a pointer to the clustered index.

A clustering key consists of the values of the clustered index keys from the row being pointed at and the Uniquifier (if present). With every insert, the clustered key has to sort the values in memory, and in turn each one of those pointers have to move.
Also keep in mind that clustered indexes in SQL Server immediately make the table a B-Tree structure (unless it's a column store index), and this is one of the biggest causes for fragmentation. As the rules of the B-Tree data structure could force pages to split and therefor change the entire structure of the B-Tree (it's like a cascading effect).

In order to avoid some fragmentation on insert, you should sort the data with a query before doing so. This post explains how it works and why.

Fun fact, a nonclustered index will point at something called an RID (Row Identifier) when no clustered index is present. This is then called a heap table.


Trigger after insert, insert new row into another table I'm developing a Wordpress site.
I'm trying to insert a new row into a table when new row with specific value in usermeta table is added.
This is what I got for now:

DELIMITER |
CREATE TRIGGER user_sub AFTER INSERT ON wp_usermeta
FOR EACH ROW
BEGIN
IF meta_value='subscribe' THEN
INSERT INTO newsletter_user_list SET list_id = 1,user_id=NEW.user_id;
END IF;
END;
|
DELIMITER ;

But this breaks the usermeta table. After new user register usermeta table is not populated.Thank you very much for any help because I have no experience with triggers.

Thank you Akina!

DELIMITER |
CREATE TRIGGER user_sub AFTER INSERT ON wp_usermeta
FOR EACH ROW
BEGIN
IF NEW.meta_value='subscribe' THEN
INSERT INTO newsletter_user_list SET list_id = 1,user_id=NEW.user_id;
END IF;
END;
|
DELIMITER ;