Multiple column concatenation How to concatenate multiple columns in to a single row? For example:

id   name    car
1    sam     dodge
1    ram     maserati
1    john    benz
1    NULL    mazda
2    kirk    lexus
2    Jim     rolls
1            GMC

The expected result set is:

ID  name               car
1   sam,ram,john       dodge,maserati,benz,mazda,GMC
2   kirk,jim           lexus,rolls

Using a solution I found on Stack Overflow:

SELECT * FROM (
SELECT t.id,stuff([m].query('/name').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined1],
stuff([m].query('/car').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined2]
FROM dbo.test t
OUTER apply(SELECT (

SELECT id, ','+name AS name
,','+car AS car
FROM test WHERE test.id=t.id
FOR XML PATH('') ,type)
             AS  M) A)S
GROUP BY id,somefield_combined1,somefield_combined2

Are there any better solutions? The inner select comes from an expensive multi-table join (not the single table 'test' shown above). The query is in an in-line TVF, so I cannot use a temporary table.

Also, if there is a blank column the results will yield extra commas
like

ID  name                car
1   sam,ram,john,,      dodge,maserati,benz,mazda,GMC
2   kirk,jim           lexus,rolls

Is there any way to prevent this?

A CLR aggregate will almost certainly be the fastest way of doing this. But perhaps you don't want to use one for whatever reason...

You say that the source for this is an expensive query.

I would materialise this into a #temp table first to ensure it is only evaluated once.

CREATE TABLE #test
(
ID INT,
name NVARCHAR(128),
car  NVARCHAR(128)
);

CREATE CLUSTERED INDEX ix ON #test(ID);

The execution plan I get for the query in the question first does the concatenation for every row in the outer query and then removes the duplicates by id, SomeField_Combined1, SomeField_Combined2.

This is incredibly wasteful. The following rewrite avoids this.

SELECT t.id,
       stuff([M].query('/name').value('/', 'varchar(max)'), 1, 1, '') AS [SomeField_Combined1],
       stuff([M].query('/car').value('/', 'varchar(max)'), 1, 1, '')  AS [SomeField_Combined2]
FROM   (SELECT DISTINCT id
        FROM   #test) t
       OUTER APPLY(SELECT (SELECT id,
                                  ',' + name AS name,
                                  ',' + car  AS car
                           FROM   #test
                           WHERE  #test.id = t.id
                           FOR XML PATH(''), type) AS M) M

However for the following test data (1000 ids with 2156 rows per id for me)

INSERT INTO #test
SELECT v.number, o.name, o.type_desc
FROM   sys.all_objects o
       INNER JOIN master..spt_values v
         ON v.type = 'P' AND v.number BETWEEN 1 AND 1000

I still found Kenneth's solution with two XML PATH calls much faster and less resource intensive.

+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
|                 | CPU Time (Seconds) | Elapsed Time (Seconds) | #test Scan Count | #test Logical Reads | Worktable logical reads | Worktable lob logical reads |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
| Single XML PATH | 51.077             | 15.521                 | 1,005            | 60,165              | 51,161                  | 1,329,207                   |
| Double XML PATH | 3.1720             | 3.010                  | 2,005            | 92,088              | 14,951                  |   233,681                   |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+

For each distinct id in #test it performs two operations instead of one but this operation is significantly cheaper than constructing the XML and then reparsing it.


Replication between SQL Server 2005 and Azure database Is it possible to set up transitional replication between a SQL Server 2005 and a SQL Azure database?

My server is nearing end of life and I would rather migrate to a cloud infrastructure instead of buying new hardware.

I’ve been unable to find any documentation stating whether or not SQL Azure Data Sync can co-exist with SQL Server Replication.

I'm not sure about the core of your question ("Can you set up replication between SQL Server 2005 and SQL Azure Database?"), but my assumption is a resounding "NO".

There are tools to migrate from SQL Server to Azure though - it's been discussed on Stack Overflow here and here.
The consensus seems to be that This SQL Azure Migration Wizard (CodePlex hosted project) is the way to go. It doesn't appear to be a live-sync migration though, so you will probably need to take an outage window unless I'm missing something...


Diagnose slow script executions on specific server I work with an unmanaged server in a remote datacenter to host web based applications. When we run the SQL scripts to create and populate an application database the whole process is painfully slow. A set of scripts that in my underpowered local PC runs in 5 seconds, on the remote server can take up to 20 minutes.
When I check the running processes I can see it isn't stuck in any specific statement, it's simply that each one takes several seconds, specially ALTER TABLE statements:
ALTER TABLE foo_bar
ADD CONSTRAINT FK_foo_bar_foo_id
FOREIGN KEY (foo_id)
REFERENCES foo (id)

It's a brand new app, so most tables are empty or have at most a few hundred rows. But slowness already happens much before the INSERT INTO statements. All tables are InnoDB.
I run the scripts through command-line PHP if that matters. There's no antivirus I'm aware of. MySQL/8.0 was installed with Windows MySQL Installer and runs as service with this configuration:
[client]
port=3306
[mysql]
no-beep
[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="FOO.log"
slow-query-log=1
slow_query_log_file="FOO-slow.log"
long_query_time=10
log-error="FOO.err"
server-id=1
lower_case_table_names=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
max_connections=151
table_open_cache=2000
tmp_table_size=16M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=0
read_rnd_buffer_size=0
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060
character-set-server = utf8
collation-server = utf8_unicode_ci
log-bin = OFF
skip-bin-log

Where can I start looking at this? What diagnostics can I try?

Which version is each server using?
ALTER TABLE has become significantly slower in 8.0.  It probably has to do with the ability to rollback DDLs.
It has never been wise to depend on a lot of DDLs, but this makes it worse.
If this ALTER was being done to a brand new table, it probably would have been better to include the CONSTRAINT as part of the table definition.  Of course, if the ALTER was generated by mysqldump`, this is not practical.  Furthermore, FKs are very picky about order, so you may be stuck.


Replacing composite key with surrogate I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current composite 6 field primary, then make the current key a unique key for referential integrity and recreate the indexes.

    CREATE TABLE [dbo].[Autocompleter](
        [CountryId] [int] NOT NULL,
        [ProvinceId] [int] NOT NULL,
        [LocationId] [int] NOT NULL,
        [PlaceId] [int] NOT NULL,
        [EstabId] [int] NOT NULL,
        [LocaleId] [int] NOT NULL,
        [Title] [varchar](400) NOT NULL,
        [Hotels] [int] NULL,
        [AlternateTitles] [varchar](4000) NULL,
        [EnableHotels] [bit] NOT NULL,
        [EnableHolidays] [bit] NOT NULL,
        [DisplayPriority] [int] NOT NULL,
     CONSTRAINT [PK_autocompleter_1] PRIMARY KEY CLUSTERED
    (
        [CountryId] ASC,
        [ProvinceId] ASC,
        [LocationId] ASC,
        [PlaceId] ASC,
        [EstabId] ASC,
        [LocaleId] ASC
    )

Any gotchas I should be looking out for ? if it is an identity field I am thinking this should not break code that inserts into the table (as long as it specifies the columns explicitly)

I plan to create a new clustered index on the surrogate key and then make the current clustered index on the 6 fields a NC index.

Reading up on Kimball dimensional modeling, use of a surrogate key, in particular an IDENTITY, will help reduce fragmentation caused by page splits as you'll be appending rows at the end of leaf pages vs. attempting to insert them in the middle if the keys are not in the order of the index (ascending or descending depending on how the index was defined).

But, if the surrogate key will not be used in joins with other tables or in WHERE criteria, use of the surrogate key as a clustered index might not provide any additional benefit after the initial data import. If you keep the composite clustered key, sorting the source data in the order of the six-column composite key prior to import should avoid the page split fragmentation you're witnessing.

As for a choice of clustered index, the best candidate may be the column(s) that would be used in your most common/critical queries for WHERE criteria or joins. Use of a NCI will require a bookmark lookup to obtain the remaining values in the table if needed in the resultset.


Data Model: Parent, Child, Grandchild with Child Being Optional I posted the question in another forum and I was suggested to re-post it here which may be more appropriate. Thank you in advance.

My organization structure:
   Department  -- has many sections ( and employees)
      Section   -- has many employees
         Employee  -- most of the employees belong to Section directly.

However, there are two departments which have no medium Sections and employees of these Sections report to Department Director directly.

I do not know what can be done to better reflect the business structure using an Entity Relationship model.

I learned of a similar posting which is still different than what I need.
  https://stackoverflow.com/questions/2530215/model-a-zero-or-one-to-many-relationship?rq=1

John

Ask yourself what is the difference between a Department and a Section?  Are they just organizational objects that may contain either employees or other, smaller organizational objects?  Do they have any other differences in terms of their relationships and attributes?

If the answer is no, then you might want to consider replacing both with a single organization table containing an unleveled hierarchy.  This can be represented with an involuted (self-referencing) foreign key (i.e. part_of or reports_to etc.)

Of course, in a relational database, unleveled hierarchies can be awkward to work with.  If you decide to go this route, you should do some research on hierarchical data management techniques, like visitation numbers or adjacency lists.

If you really need to keep Department and Section separate because they have markedly different definitions, then you will need to consider implementing a dummy or null Section for each Department to handle the situation where there is no analogue in reality.  If you go this route, you could consider adding a flag or some other indicator to differentiate between real Sections and pro-forma Sections.


How do I recreate the XE database in Oracle 11g Express? My Oracle XE database was  useless mess of unused tables etc. Using SqlPlus and several google results, I finally managed to drop the XE database, but now when I connect with SqlPLus IO cannot create a database. For any CREATE DATABASE command issue, I get back the error ORACLE not available.

I assume this is as I deleted the only db on that server, which only has room for one db, SqlPlus now gets confused as to how to connect, still trying to find XE. How can I connect as admin without connection to a db. so that I am connected to Oracle but not to a db? This seems to new the most likely solution.

You should have dropped the owners (AKA schemas AKA users that created the tables) of the "mess of unused tables."  If you dropped the database, no way to restore it (unless you have a backup).  Just recreate the database.  And in the future you can drop just the non-system users, like to drop the Oracle Sample schema SCOTT:

drop user SCOTT cascade;


Model to handle music release artwork I’m designing a FAN web page for one artist, more specifically, the discography section. The discography includes this entire artist CD’s, Vinyls, 8-tracks, cassette, etc. So far, I’ve build something pretty good, but I got stuck when it came time to handle all of the artwork of a release, not just the front cover. Let me explain:

The model I had was handling only the front cover. The logic was as following:

One release contains 1 front cover, and 1 front cover could be in many releases (this happens a lot for editions of a release where the front cover of an edition is pretty much the same as the primary release.)

For that, I’ve build 2 tables: release and cover

release (release_id, title, cover_id,...)
cover (cover_id, filename, width, height,...)

The model worked perfectly until I’ve decided to add more artwork for releases. Since then, I’m stuck and I can't get my mind to think clearly.

Here’s what I’m trying to do:

I’m trying to add more artwork to a release, like the back cover, inlay, side a, side b, booklet pages, etc. The artwork needs to be in a specific order (front cover first, then back cover, etc).

I’m struggling on how to achieve that. Should I keep the one-to-many model and add one more table for the rest of the artwork? Should I mix all of the images in one single table (let’s call it Artwork)? If yes, how do I handle front covers so there’s only 1 front cover for each release. Should the front cover remain a 1:m relation since the rest of the artwork would probably end up being a 1:1 relation? If all of the images should go in one table, then how to position images and how to make the front cover unique and the primary cover of the release? So many questions that led me to build so many models, so many that at this point I’m totally lost!

Here’s the basics :

One artist discography (cd, cassettes, 8-track, singles,vinyl,etc...)
Some releases have up to 12 editions, and a lot of times the front cover of the main release is the same as the front cover of the edition.
I need to make one artwork the main artwork of the release (most of the time that would end up being the front cover)
I need to display all of the artwork in a specific order... (front covers are always the first displayed)

How would you approach this?

This is how I'd model it:

release (release_id, title, ...)

artwork (artwork_id, filename, width, height,...)

release_artwork(release_id, artwork_id, display_order)

All releases belong in the releases table, all artwork in the artwork table.

The release_artwork table joins the two, with the display_order column enforcing the display order. Insert the covers with display_order=1, then the rest (back cover, inlay etc) with 2, 3, 4... etc as needed - A simple ORDER BY display_order ASC will then deal with the ordering for you. If you want to doubly ensure the cover is first, you could always add a is_cover column to release_artwork and set it to 1 for covers.


Can a Database request automatically remove itself from the queue after a delay? I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again.  If many instances are run and requests are made simultaneously, the table locks will be queue, like updates or other table modifications would be.

In a positive spirit of bug prevention, I'm concerned that if the Database crashes or suddenly becomes unavailable for any reason, while the table is locked, the program instances in queue would be stuck there, waiting for an answer that will never come.

Obviously, getting my program frozen is undesirable. Is there any way a request can remove itself from the queue after a set time?  It there maybe a better way to do this?

If this proves to be a problem, I do know that I can use the NO WAIT parameter in my table lock, and then repeat requests for a certain time before giving up, but that seems quite inefficient and I lose the benefit of the database queue.

What do you think?

From Oracle 11gR1 you can use the WAIT instruction in DDL:

lock table t in exclusive mode wait 10;

This will wait 10 seconds before giving up.

The WAIT instruction has been available for a long time (9i) for DML:

SELECT * FROM t FOR UPDATE WAIT 10;


Access denied error in trying to connect to a MySQL db in Python I am trying to write a script that connects to a server, then connects to a MySQL db (which I currently can do via Navicat - so I know my username and password for the MySQL connection are correct).

Here is what I’ve written so far:

import socket
from ssh2.session import Session
import mysql.connector
host = 'servername.logserverlog.net'
user = 'username'

sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((host, 22))

session = Session()
session.handshake(sock)
session.userauth_publickey_fromfile(user, r'C:\Users\user\Docs\ssh-key')

cnx = mysql.connector.connect(user='username', password='$gHj1aFaVFRfhl*C', database='analyst_db')

The error I am getting reads:

  File “C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site- packages\mysql\connector\connection.py”, line 176, in _auth_switch_request raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user ‘username’@‘localhost’ (using password: YES)

I have already confirmed my user and password are valid.
I tried passing the password string as a raw string, to see if somehow the Python string syntax wasn’t being received by the MySQL db correctly, and still received the same error.
User privileges for the user on the database have been confirmed as GRANT ALL.
adding host='localhost' or host='127.0.0.1' to the parameters for the "cnx" object produces the exact same error.

So, I’m not sure where to go from here.

You seem to be trying to make an SSH tunnel, but I don't think you're actually using that tunnel for the mysql connection?

I also suspect the mysql error you receive is from a local mysql server, not the remote one you're trying to connect to.

I think you need to do port forwarding and use that port for the mysql connection. See e.g. Enable Python to Connect to MySQL via SSH Tunnelling (stackoverflow.com) or research making SSH tunnels in Python for MySQL connections.


What's the most efficient UUID column type For storing a 128 bits UUID there are multiple storage options:

a byte[16] column
two bigint/long(64 bits) columns
a CHAR(36) column - 32 hex digits + 4 dashes.
a UUID database specific column, if db supports it

From an indexing point of view which of those are the most efficient? If the db doesn't support a dedicated uuid type which of 1, 2, 3 are the best candidates?

A dedicated uuid type is your best bet for PostgreSQL. Hard to say with other DBs - it's not impossible for someone to impliment a uuid type that's stored less efficiently than a simple byte type.

Again in PostgreSQL, bytea would be a reasonable way to store UUIDs if you didn't have the uuid type. For other DBs it depends on how they store binary data.

Where possible I'd strongly avoid using hex-with-dashes. It's way less efficient to compare, sort, and store.

So really, "not (2) or (3)". Ever. Use (4) where supported, (1) otherwise.


How much could sequential scan gain by GPU? I am considering hiring someone to implement GPU processing in Postgres to speed up sequential scans on a database located on a ramdisk. How much do you believe I could gain using this approach? I could buy any type of graphic card. I know that this question is really hard, but I know very little about GPU, so a very rough guess would help me a lot.

EDIT: Even though I my interest is specifically for Postgres, I could ask this question in a different way: "How large is the potential performance increase by using GPU for sequential scans on an in-memory database". If the answer is that it has a potential of 10-50 times the performance, then I can start investigating more specifically if this could be realized also in Postgres.

You'd better ask this question on the hackers mailing list, there has been some debate about this subject in the past. The PostgreSQL developers can tell you much more about it.

http://archives.postgresql.org/pgsql-hackers/

To get good performance using a GPU for PostgreSQL, you need some form of parallel processing in PostgreSQL. This is something PostgreSQL doesn't have at the moment (for CPU nor GPU), but Greenplum already implemented this in their PostgreSQL-product for CPU's. Parallel processing is on the ToDo list, but it looks like nobody is working on it at the moment. Ask the PostgreSQL hackers about the current status.


Latest MySQL version for Windows 7 How can find out which are the latest GA verions of MySQL Server & Workbench  supported under Windows 7?

On the 'MySQL Product Support EOL Announcements' page it states:

  Per the MySQL Support Lifecycle policy regarding ending support for
  OS versions that are obsolete or have reached end of life, we plan to
  discontinue building all MySQL binaries for Microsoft Windows 7 as of
  June 23, 2016

But I am unable to find information on which versions DO support Windows 7.

I tried using the installer:
MySQL Installer-Web seems to be the same for versions 5.6.45, 5.7.27 and 8.17.0 and each of these installers offers me the upgrade of my x64 system to 5.7.27 and my x86 system to 5.7.26.  I would usually take that at face value, and assume the installer was suggesting the latest GA version for my system BUT it also offers me MySQL Workbench 8.0.17 x64, which I know to be incorrect because

it results in an 'You are running an unsupported operating system' message
Supported Platforms: MySQL Workbench  clearly states that current Workbench only supports Windows 10

From the code repository I see that "Windows 7" was removed from the supported OS list on Oct 3rd, 2018.

   static std::vector<std::string> supportedOsList{
-    "Ubuntu 18.04", "Ubuntu 16.04", "Debian 8",
+    "Ubuntu 18.04", "Ubuntu 18.10", "Debian 9",

     "Red Hat Enterprise Linux Server release 7", // Oracle 7.1 looks like this: "Red Hat Enterprise Linux Server release
                                                  // 7.1 (Maipo)"
-    "Fedora release 28", "Fedora release 27", "CentOS release 7",
+    "Fedora release 28", "Fedora release 29", "CentOS release 7",

-    "Windows 7", "Windows 8", "Windows 10", "Windows 2012 Server", "Windows Server 2012 R2", "Windows Server 2016",
+    "Windows 10", "Windows Server 2016",

-    "OS X 10.11", "macOS 10.12", "macOS 10.13"
+    "macOS 10.13", "macOS 10.14"
   };

The release after that (8.0.13) was the first one without official Win 7 support.


Show that functional dependency pseudotransivity holds using closure test I'm currently studying a basic database course with the course literature Database Systems - The Complete Book1.

In one of the book's exercises you are asked to

  Show that the following rules hold, by using the closure test.

This is the closure test described in the book:

And the question I'm having trouble with is as follows:

How can I show that the rules hold? Can I do it by computing the closures of the relevant attributes somehow prove that?

Thank you in advance!

1 Garcia-Molina, H. & Ullman, J. D. & Widom, J. (2009). Database Systems - The Complete Book, Second Edition. Pearson Education Inc., Upper Saddle River, NJ, USA.

The definition of Pseudotransitivity given above is equivalent to say the following.

From:

(1) A1 A2 ... An -> B1 B2 ... Bm
(2) B1 B2 ... Bm E1 E2 ... Ej -> D

we can derive:

(3) A1 A2 ... An E1 E2 ... Ej -> D

Here is a possible simple proof.

Let’s try to compute the closure of {A1 A2 ... An E1 E2 ... Ej} from the first two functional dependencies. If this closure contains D then we have proved the hypothesis.

{A1 A2 ... An E1 E2 ... Ej}+ = A1 A2 ... An E1 E2 ... Ej    (starting point)
{A1 A2 ... An E1 E2 ... Ej}+ = A1 A2 ... An B1 B2 ... Bm E1 E2 ... Ej (by using 1)
{A1 A2 ... An E1 E2 ... Ej}+ = A1 A2 ... An B1 B2 ... Bm D E1 E2 ... Ej (by using 2)
No other derivation is possible.

So, since D is included in the closure of {A1 A2 ... An E1 E2 ... Ej}, we have shown that A1 A2 ... An E1 E2 ... Ej -> D.


How to replace to_tsquery by phraseto_tsquery in PostgreSQL? For word suggestion when do search.
I can query like
select title from test where ts_filter(tsv, '{A}') @@ to_tsquery('english_nostop', 'god<->o' || ':*')

# tsv is something like to_tsvector('english_nostop', test.title)

The <-> was something like pattern = pattern.replace(' ', '<->'). That may have some problem when pattern contain <.  Thought we can remove such char.
Combine tsquery don't work here. I can't find away to do to_tsquery(':*')
But how to use phraseto_tsquery to replace  to_tsquery ? And still can have :* work?

After @Laurenz Albe answer.  phraseto_tsquery can't do this.
May be I need give more detail. Why I want to use phraseto_tsquery.
I use the
CREATE TEXT SEARCH DICTIONARY english_stem_nostop (
    Template = snowball
    , Language = english
);

CREATE TEXT SEARCH CONFIGURATION public.english_nostop ( COPY = pg_catalog.english );
ALTER TEXT SEARCH CONFIGURATION public.english_nostop
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, hword, hword_part, word WITH english_stem_nostop;

To keep stop words can be searched.
But I need to change pattern like this in python:
pattern = info["pattern"]
replace_punctuation = str.maketrans(string.punctuation, ' ' * len(string.punctuation))
pattern = pattern.translate(replace_punctuation)
replace_space = pattern.replace(" ", '<->')

You can see I replace punctuation by ' '. Then replace ' ' with <->.
I came up this question. Because I wonder can I don't transform pattern in python and just use psql to transform pattern.
Why need both ':*' and <->. here is a example:
select title from book where ts_filter(tsv_nostop, '{A}') @@ to_tsquery('english_nostop', 'how<->t'||':*')
except
select title from book where ts_filter(tsv_nostop, '{A}') @@ to_tsquery('english_nostop', 'how<->t');

This will show  title contain how to. When user input how t. We want pop up some title.

You cannot do that with phraseto_tsquery.
The lower-level functions provide more features that you cannot get with phraseto_tsquery or websearch_to_tsquery.


How do I connect to localDB from SQL Server 2014 Express I downloaded both the LocalDB and Management Studio for MS SQL Server 2014 Express so that I can practice on my local machine.  However, when I open Management Studio and try to browse to localDB to connect to to start created databases I can't locate the localDB.

May someone please assist me with this?

SQL Server Express LocalDB (commonly referred to as just "LocalDB") is not a service-based application: it does not show up in "Services", nor does it start up when the machine starts or even stay running until manually shut-down.

LocalDB is user-mode background process. It starts either when the first request to connect comes in (assuming the request is using the "(localdb)\InstanceName" syntax) or when explicitly started via the SqlLocalDB.exe command-line utility.

So, if you know the instance name, just try to connect and the first time will take a moment while it starts up, but then subsequent connections (assuming consistent activity) will be quick.

If you don't know the name of the instance, you can get the list by using the SqlLocalDB.exe utility as follows:

C:\>SqlLocalDB i

That will return a list of instances that your Windows login has access to. If an instance name starts with .\ then it is a shared instance that other users can access as well (the default is that instances are private to the login that created them).

From there, you can get more info (including whether the LocalDB instance is "Running" or "Stopped") by doing:

C:\>SqlLocalDB i {InstanceName}

And you can start it by doing:

C:\>SqlLocalDB s {InstanceName}

Once the last connection to a LocalDB instance is closed, the instance will automatically stop after about 5 minutes (this is the default setting but can be configured to be longer). The delayed shutdown is to prevent needing to wait for the startup again for connections that come in shortly after the last connection ended.

LocalDB always has a "default" instance that is created when installing, even if you never use it. In SQL Server 2012 Express LocalDB, the default instance name is v11.0. In the 2014 version of LocalDB, the default instance name is MSSQLLocalDB. And, just as a period "." can be used to connect to a non-LocalDB default instance:

C:\>SQLCMD -S . -E -Q "SELECT @@SERVERNAME;"

it can also be used with the "(LocalDB)\InstanceName" syntax to connect to the default instance:

C:\>SQLCMD -S (LocalDB)\. -E -Q "SELECT @@SERVERNAME;"

Please note that you can only have one default instance of LocalDB specified as being the default instance. Meaning, if you have both the 2012 and 2014 versions of LocalDB installed, using (LocalDB)\. will refer to the MSSQLLocalDB instance and not the v11.0 instance.


Fast local backup - Very slow backup via Network Since last week, i'm having this problem.

Backups are taking forever to be done if I make them via Network.

If I backup the database to a local disk ( or N'nul' ) it's very fast.

It's strange because even with my personal PC I can't make network backups ( there's nothing with the cluster, or configuration that we have in the production instances ).

In This connect item  they say something about a SQL 2005 issue.

What can we track to find the issue? What perfmon counter can we use?

We have already restarded the servers. Restarted instances. restarted clusters. Disabled Anti-virus. Nothing helped us.

This is some logs:

Via NUL:

  backup database My_DB to
  disk =N'nul'
  --Processed 542896 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 382456 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 1 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --BACKUP DATABASE successfully processed 925353 pages in 19.911 seconds (
  363.081 MB/sec).

Via Network:

  backup database My_DB to
  disk ='\Server\D\BACKUPS_TEST\teste.My_DB .bkp'

  --Processed 542896 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 382456 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 1 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --BACKUP DATABASE successfully processed 925353 pages in 129.223 seconds
  (15.944 MB/sec).

I can copy files through servers with no problems. I really have no Idea what to do. I would be grateful to share more information.

EDIT1:

Something I notice is this query below ( in the destination server ):

WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),    TIMEOUT 5000

There are a lot of websites saying different things about it, so, I don't know for sure what it is.

I can see this query running via trace or with sp_whoisactive. It runs, then it restarts itself. Could this be the problem ??

You could attempt to backup up the database using compression instead. As long as you have the CPU avaliable, It will definately reduce the time (around 15%-20%) that it takes for your backups to complete. These backups also take less time to restore as well.

Good Luck,

Hope this helps.

Good resource: http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/testing-backup-compression-in-sql-server-2008/


Using a mysqldump export to create data with a different database name I am using the following to import database using a mysqldump export:

mysql -u root -p example_db < path/to/export.sql

I would like to import the database to a database with a different name to the one referenced in export.sql. Is there a way to do this other than manually editing the export.sql file?

Use the option --no-create-db when you execute your mysqldump, this option avoid inserting CREATE DATABASE IF NOT EXISTS db_name; in dump file.

Max.


DB2 Express User ID Authority Error I'm trying to create a sample database in first steps after installing db2 Express V10.5
I've looked at the answers provided below, but I'm not able to resolve my issue.
How to grant rights for a user in DB2 Express?
DB2 Express on Windows 7 - Disable security?

Here is the exact error message

Creating database "SAMPLE" on path "C:"...
  Attempt to create the database "SAMPLE" failed.
  SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User ID:
"I32129
  'db2sampl' processing complete.

I'm logging on to my computer with a username on a domain managed by my company. My username is actually i321290, not sure if that error is accurate or if its hitting some character limit. While I was installing db2 I created a username and password for db2admin
which is in the PROD_EXPC.rsp file.
The DB2ADMNS and DB2USERS groups are created in my local groups. I created local users db2admin, i321290, and I32129 and added each of them to the above groups. The user names aren't case sensitive.
DOMAIN\db2admin and DOMAIN\I321290 are also members of these groups.
I've run the following commands

db2set DB2_GRP_LOOKUP=local
db2stop
db2start

but still receive the same error.

I had the same problem and solved it by adding domain admin user into the local Administrators group on local computer (where I installed DB2 DB).


View the output of Adam Machanic's sp_WhoIsActive I'm attempting to look at the XML output by Adam Mechanic's sp_WhoIsActive stored procedure.

When I use a typical XML editor, all I see is the raw XML.  I'd like to be able to view the graphical execution plan the way it's shown in SQL Server Management Studio when you run a query including the actual execution plan.

I'm looking at the sample actual query plan demonstrated by Brent Ozar and want to see that style of plan on my own queries.

You need to use a product which understands that the XML represents a SQL Server execution plan.

Make sure you're using the latest version of SQL Server Management Studio (v17.5 as of this post) and, when you click the XML for the plan returned by sp_whoisactive, you should get an extra window showing the execution plan in graphical representation.

I'd strongly suggest downloading the latest version of SentryOne Plan Explorer which is now completely free.  The latest version has SSMS add-in support such that, when you see the graphical plan in SSMS, you can right-click and choose 'View with SentryOne Plan Explorer' from the context menu and the execution plan will be displayed in SentryOne Plan Explorer. See the SentryOne post Announcing Plan Explorer Add-In Support for SSMS 2016.


Will creating a view on a MySQL replication slave break the replication? We need to run some very ugly queries on a MySQL replication cluster.

Does anyone know if creating a view directly on the slave will break the replication of the other, otherwise untouched data synced with the master?

This will work fine.  The only caveat is that if you, in the future, create a view on the master with the same view name, in the same schema, replication will stop due to the conflict.

Another potentially useful thing you can do -- quite safely -- on a slave server is adding indexes to base tables to help your reporting queries, even if the same index wouldn't be needed or justified on the master.  Of course, you should not add unique indexes or foreign keys directly to the slave, since these could cause conflicts and don't really make sense when not on the master ...but ordinary single and multi-column indexes are also safe to add directly to a slave.


Restore SQL Server database backup to other directory than root I have a database backup file (db.bak) which I want to restore and import in my SQL Server 2008 R2 instance. However, the database takes approx 1 GB of space, and my C: partition is really strained at the moment - I don't have that much space left. (Yes, I know this is bad, but there's nothing I can do about it at the moment...)

I do have some space on my D:\ partition - can I somehow restore the backup and use a location on D:\ instead? If so, how?

restore database YourDatabase
from disk = 'C:\db.bak'
with
    move 'YourDataFile' to 'D:\YourNewDir\YourNewDataFileName.mdf',
    move 'YourLogFile' to 'D:\YourNewDir\YourNewLogFileName.ldf'

The above command will restore your database but move your database files to a different location as per your specifications.  To get a list of database files contained in the backup (I just used the logical names of YourDataFile and YourLogFile), you can run the following command:

restore filelistonly
from disk = 'C:\db.bak'

If you want do to this through the GUI, then in the Restore Database dialog you can click on the item Files on the left, and you will see your database files listed.  There are elipses buttons next to those file names, and you can click that to change the path.  Likewise, you can just double click on the text box that has the file names themselves and change that to reflect the new path.  Below is a screenshot of what I explained:


Work queue with complex select and long processing: Ensuring concurrency I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process.

Normally this kind of thing is done with select top (1) ... with (updlock, rowlock, readpast).
This works as expected and provides great concurrency when there is a covering index, but for this particular query the where condition is complex, depends on the time of the day and on presence of records in other tables (where exists(...)), and is followed by an order by of similar nastiness, so it cannot be covered by a single index or an indexed view.

In this situation the select top (1) puts the updlock on all rows that match the where, not just the first one, which leads to one worker winning the job and all the others waiting (or falsely reporting that there were no more rows to process).

While this does not lead to any errors, the concurrency is not great. The problem is that record selection is quite cheap, but record processing is very expensive. So the other workers sit there doing nothing for seconds.

In an attempt to fix this concurrency issue I came up with the following solution for the job selection (error handling removed for clarity):

exec sp_getapplock N'Pick a Job', 'Update';

declare @will_lock_that int;
select top (1) @will_lock_that = row_id from the_table with(readpast) where... order by...;

declare @locked int;
select @locked = row_id from the_table with(updlock, rowlock, holdlock) where row_id = @will_lock_that;

exec sp_releaseapplock N'Pick a Job';

The idea was that:

The applock makes sure only one process at a time will be picking a job. This is okay because picking a job is cheap.
The applock will be released very quickly, letting the other workers pick their jobs, while the original worker will be processing the item, having released the applock.
The first select statement will have to fetch all rows matching the where condition, as described above, but this time it will not put any persistent locks on them. The shared locks will be released as soon as the select returns.
The readpast in that select will skip over any rows already being processed, because they are locked with updlocks.
The locking hints in the second select will hide the row from the first, readpast-enabled select performed by others.

Of course this did not work. readpast did not try to skip over the updlocked rows. Instead it would wait for the first encountered locked row to be released. Changing the updlock to xlock in the second query does not change this, neither does making an actual update to the row.
The only way to make the readpast skip over rows seems to be adding a locking hint to the readpast query itself (e.g. with(readpast, updlock)) - but that fails the original purpose, because now all rows will again be locked with updlock.

Is there a way to solve this concurrency problem without using manual locks? (By manual locks I mean updating a special field in the row to 'Locked', outside of a transaction for others to see, and then updating it back when processing is finished.)

I cannot use the locking by deletion, too, because the row must stay in the same table, and only few of its fields will change after processing.

You mentioned you could not use 'locking by deletion' because the row must stay in the table. The code example below does use it, but only after creating a global queue first and populating it. It then uses the 'locking by deletion' for the ##Queue table rather than the table you're working with.

USE master
GO

    -- 1. Create a Stored Procedure that creates the Queue and populates it based on the criteria you have.
    --      (this doesn't have to be a Stored Procedure, but can also be run via some other method to populate the queue.)
    --      (also update 'DatabaseName.SchemaName.the_table' to be the name of your table.)

    IF EXISTS (SELECT * FROM master.dbo.sysobjects o WHERE o.xtype IN ('P') AND o.id = object_id('master.dbo.CreateQueue'))
    DROP PROC CreateQueue
    GO

    CREATE PROCEDURE dbo.CreateQueue
    AS
    BEGIN
        IF OBJECT_ID('Tempdb.dbo.##Queue') IS NOT NULL
        DROP TABLE ##Queue

        SELECT * INTO ##Queue FROM DatabaseName.SchemaName.the_table WHERE <replace with your criteria>
    END
    GO

    -- 2. Execute the SP above or use the code some other way to create the ##Queue

    -- 3. Copy the code below and run it via multiple processes if you like, as there should not be concurrency issues.
    WHILE 1 = 1
        BEGIN
            DELETE TOP ( 1 )
                    ##Queue WITH ( READPAST )
            OUTPUT  Deleted.*
                    INTO #RowToProcess
            IF @@ROWCOUNT > 0
                BEGIN
                    --Place logic here to work with the row...
                    DELETE  FROM #RowToProcess
                END
            ELSE
                BREAK
        END


Is it possible to make a high availability PostgreSQL database cluster by open source softwares? If use these tools can make a great modern database cluster with full features:

pgbouncer (with HAProxy for HA)
repmgr
barman

The main database will run on the repmgr servers. It has primary and standby. If primary down, standby can up. But will take some seconds. At that time, user can't access the database by pending? Isn't there has a good way to make realtime response?

With all high-availability solutions you will always have some down-time during a failover, typically less than a minute.

The reason is that the clusterware has to determine reliably that a member is down before it can fail over. That means that failover is only initiated after several attempt to communicate with the cluster member have failed.

You can reduce failover time by making these tests try less often, but that increases the risk of an unnecessary failover (for example, caused by a one-time network glitch). Unnecessary failovers reduce the total uptime of your database, so you don't win anything that way.


help in the initial configuration of postgreSQL9.6 I recently installed postgreSQL9.6 version on windows 7 64 bit machine. During the installation process, I was asked to key in the super user password and the locale of the database (I chose default locale). Now during installation, there was a step by the name "server clusterinitialization". This step failed and I got the error message for the same. However, the server got installed successfully.

Now when I launched the postgreSQL9.6 command line application, I am required to provide some information. I am not sure what are they exactly asking for. So, I am giving a snapshot of the fields required.

As evident, the prompt asks for the Server [localhost]:, Database [postgres]:, Port [PG_PORT]: and username [PG_USERNAME]:

The installation instructions said that for these fields I am supposed to enter the default values. What are the default values for these fields. I only know the port number and superuser password.

Basically, I do not know what information needs to be entered in each of these fields.

Also, when I tried to add a server using pgadmin III, I get the following error

**

Server doesn't listen
    The server doesn't accept connections: the connection library reports
    could not connect to server: Connection refuse (0x0000274D/10061)     Is   the server running on host "localhost" (::1) and accepting TCP/IP     connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?
If you encounter this message, please check if the server you're trying to contact is actually running PostgreSQL on the given port. Test if you have network connectivity from your client to the server host using ping or equivalent tools. Is your network / VPN / SSH tunnel / firewall configured correctly?
For security reasons, PostgreSQL does not listen on all available IP addresses on the server machine initially. In order to access the server over the network, you need to enable listening on the address first.
For PostgreSQL servers starting with version 8.0, this is controlled using the "listen_addresses" parameter in the postgresql.conf file. Here, you can enter a list of IP addresses the server should listen on, or simply use '*' to listen on all available IP addresses. For earlier servers (Version 7.3 or 7.4), you'll need to set the "tcpip_socket" parameter to 'true'.
You can use the postgresql.conf editor that is built into pgAdmin III to edit the postgresql.conf configuration file. After changing this file, you need to restart the server process to make the setting effective.
If you double-checked your configuration but still get this error message, it's still unlikely that you encounter a fatal PostgreSQL misbehaviour. You probably have some low level network connectivity problems (e.g. firewall configuration). Please check this thoroughly before reporting a bug to the PostgreSQL community.

**

I am trying to create a local server on my machine so that I can learn postgreSQL.

As suggested in the comments section, I tried to find the postgresql.conf file to which I got postgresql.conf.sample file. I am posting the contents of this file in the form of a snapshot.

Do I need to remove the # to make the configuration work?

Basically your pg_hba.conf files need to be updated to work properly. To find out the location of the conf file, look for the main postgresql.conf file which is typically in the /Data directory.

Also check your postgresql.conf file to see what port # it is listening on, you might have setup a non default port.  Running a netstat -an would help you verify that it is currently listening.  If it is not ensure the service or app is running.

After you validate that and you edit the pg_hba.conf you should restart the service in Windows then test again locally.  If you're running IPv6 the 'open to all' notation would be ::0/0, that's a common implementation but a poor security practice.  I would recommend only testing with that, then once it works, lock it down to specific IPs since you know everything is up and running.

Try this on your conf file and try to connect locally.  If you're using IPV6 use IPV6 addresses in CIDR-ADDRESS:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust

Here is the documentation for PostgreSQL hba_conf.

The most recent common issues I've seen were when the server or client were using IPv6 or it was a default install and a client was trying to connect from a different IP than the server.  Otherwise you conf file could be setup wrong.


MySQL trigger if new value is not equal to varchar StackExchange, I'm trying to create a trigger in MySQL (before insert) that gives an error if the new value of "sifra" is not S, P, K, A or Z, but it I'm getting an error even if the said criteria is met.

BEGIN
DECLARE msg VARCHAR(40);
DECLARE sifra VARCHAR(1);
SET sifra = NEW.sifra;
IF (sifra <> 'S' OR sifra <> 'P' OR sifra <> 'K' OR sifra <> 'A' OR sifra <> 'Z' ) THEN
    set msg = "Sifra mora biti S, P, K, A ili Z.";
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;
END

Any ideas?

Sure you get an error. If for example sifra is S then the sifra <> 'P' will cause the error to appear. Change the OR into AND.


Implementing a degenerate dimension with an alphanumeric varchar(30) at the transaction level? I am currently working on designing my first data model and I am confused about this particular implementation of degenerate dimensions. According to Kimball's design tip 46, a degenerate dimension that's unwieldy (alphanumeric) can be implemented using a surrogate key. I have a two column degenerate key requirement, char(6) and varchar(30) on a fact table that has a grain of one row per transaction line. Accordingly, the DD would consume a large amount of space when compared to a simple integer; however, there is no additional context to add here. Would it be best to leave it as is and afford it the extra space,
or is it worth putting this into a separate dimension while knowing that it would grow proportionally to the fact table (roughly 25%, as there's an average of 4 lines per transaction).

Kimball's guidance touching on performance and space use needs to be taken with a grain of salt when using modern, columnar storage. And for SQL Server data warehouses, fact tables should typically be stored as Clustered Columnstores. Low-cardinality attributes in columnar storage are much cheaper than in uncompressed row stores.

And wide and high-cardinality attributes in columnstores do have a large impact on storage size, but not on query performance, as columns are only fetched and scanned when needed.  You don't have to fetch, cache, and read the whole row.


Linked Server throws metadata error I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, so I've updated the insert function to include this new column.
Now, whenever I modify the function, it will execute successfully one time and one time only. If I purge the query cache, it also executes once and then starts throwing the error from then on. ALL execution attempts after the first time throw this error:

Msg 7353, Level 16, State 1, Procedure COD.Interface_Upload, Line
29 [Batch Start Line 20] The OLE DB provider "OraOLEDB.Oracle" for
linked server "ORADEV-LINK" supplied inconsistent metadata. An extra
column was supplied during execution that was not found at compile
time.

The only change to this procedure from the previous, working, version is adding the DATE_SUBMITTED field and the "select min([Sample_date])  ..." part to supply that column.
Everything I can find says that if you're getting metadata errors, go to the OPENQUERY syntax, which is what I'm using.
The function is:
ALTER Procedure [COD].[Interface_Upload]
  As
    SET NOCOUNT ON;

    INSERT OPENQUERY([ORADEV-LINK], 'SELECT ACCOUNT_NUMBER, FROM_DATE, TO_DATE,
            COD_TO_BOD_FACTOR, CONSUMPTION, SAMPLE_DATE, COD_CONTENT, SS_CONTENT,
            DATE_SUBMITTED from cbd.WS_CBD_PACS')
        Select [Account_Number]
            ,[From_Date]        ,[To_Date]
            ,[Factor]           ,[Consumption]
            ,[Sample_date]      ,[COD]
            ,[SS]
            , ( select min([Sample_date])
                from dbo.ORA_Interface
                where Account_Number = p.Account_Number )
        from dbo.ORA_Interface p ;

    Return @@rowcount;
GO

The Oracle destination table definition is:
                         Null?    Type
 ----------------------- -------- -------
 ACCOUNT_NUMBER          NOT NULL NUMBER(13)
 FROM_DATE               NOT NULL DATE
 TO_DATE                 NOT NULL DATE
 COD_TO_BOD_FACTOR       NOT NULL NUMBER(6,2)
 CONSUMPTION             NOT NULL NUMBER(15)
 SAMPLE_DATE             NOT NULL DATE
 COD_CONTENT             NOT NULL NUMBER(10)
 SS_CONTENT              NOT NULL NUMBER(10)
 DATE_SUBMITTED                   DATE

The SQL Server source table definition is:
[Account_Number] [varchar](30) NULL,
[From_Date] [datetime] NULL,
[To_Date] [datetime] NULL,
[Factor] [numeric](15, 2) NULL,
[Consumption] [numeric](15, 2) NULL,
[Sample_date] [datetime] NULL,
[COD] [numeric](15, 2) NULL,
[SS] [numeric](15, 2) NULL


Will this SQL snippet REINDEX my GIN index? Why is it renaming an old index onto another? I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However it would not reindex that large index, since it's a GIN index. It suggested this SQL snippet as a way to reindex this index. (mytable is the table, myindex is the index. myindex is just a GIN index on the nodes (bigint) column of mytable).

CREATE INDEX CONCURRENTLY pgcompact_index_17791 ON mytable USING gin (nodes) WITH (fastupdate=off);
BEGIN;
SET LOCAL statement_timeout TO 1000;
ALTER INDEX public.myindex RENAME TO pgcompact_temp_index_17791;
ALTER INDEX public.pgcompact_index_17791 RENAME TO myindex;
END;
DROP INDEX CONCURRENTLY public.pgcompact_temp_index_17791;

Will this snippet actually reindex the myindex index and reduce table index bloat?

What is the point of renaming the old index (myindex) to the name of the temporary index (pgcompact_temp_index_17791), and then renaming it back again? Won't that result in overwriting the currently being created temp index and then just renaming it back, meaning that myindex isn't rebuilt at all? The author of pgtoolkit appears quite knowledgeable, but I don't understand how this works, and I don't want to run SQL on my database without understanding it.

If this approach works, what's the difference between this and doing REINDEX INDEX myindex;?

Look at the names more carefully.  pgcompact_temp_index_17791 and pgcompact_index_17791 are not the same thing.

The reason to rename the old index is that you can't do "drop index concurrently" inside of a transaction.


Find Underlying Data Type (int - integer) of Oracle Table instead of number I can create an example table using following syntax.

CREATE TABLE "TABLE2" (
  "COLUMN_INT" INT NOT NULL,
  "COLUMN_INTEGER" INTEGER NOT NULL,
  "COLUMN_SMALLINT" SMALLINT NOT NULL,
  "COLUMN_NUMBER" NUMBER NOT NULL,
  "COLUMN_DECIMAL" DECIMAL NOT NULL);

After that I query USER_TAB_COLUMNS table using following query.

SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE2';

I get following result.

COLUMN_NAME                    DATA_TYPE
------------------------------ -------------
COLUMN_INT                     NUMBER
COLUMN_INTEGER                 NUMBER
COLUMN_SMALLINT                NUMBER
COLUMN_NUMBER                  NUMBER
COLUMN_DECIMAL                 NUMBER

If I use VS.NET add-in for oracle to get DDL for this table. I get following.

-- ****** Object: Table DENEME.TABLE2 Script Date: 05.05.2013 21:43:11 ******
CREATE TABLE "TABLE2" (
  "COLUMN_INT" NUMBER NOT NULL,
  "COLUMN_INTEGER" NUMBER NOT NULL,
  "COLUMN_SMALLINT" NUMBER NOT NULL,
  "COLUMN_NUMBER" NUMBER NOT NULL,
  "COLUMN_DECIMAL" NUMBER NOT NULL,
    CHECK ("COLUMN_INT" IS NOT NULL),
    CHECK ("COLUMN_INTEGER" IS NOT NULL),
    CHECK ("COLUMN_SMALLINT" IS NOT NULL),
    CHECK ("COLUMN_NUMBER" IS NOT NULL),
    CHECK ("COLUMN_DECIMAL" IS NOT NULL))
/

If I use SQL Developer

--------------------------------------------------------
--  DDL for Table TABLE2
--------------------------------------------------------

  CREATE TABLE "DENEME"."TABLE2"
   (    "COLUMN_INT" NUMBER(*,0),
    "COLUMN_INTEGER" NUMBER(*,0),
    "COLUMN_SMALLINT" NUMBER(*,0),
    "COLUMN_NUMBER" NUMBER,
    "COLUMN_DECIMAL" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table TABLE2
--------------------------------------------------------

  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_DECIMAL" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_NUMBER" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_SMALLINT" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INTEGER" NOT NULL ENABLE);
  ALTER TABLE "DENEME"."TABLE2" MODIFY ("COLUMN_INT" NOT NULL ENABLE);

If I use TOAD.

Toad give me following DDL.

CREATE TABLE TABLE2
(
  COLUMN_INT       INTEGER                      NOT NULL,
  COLUMN_INTEGER   INTEGER                      NOT NULL,
  COLUMN_SMALLINT  INTEGER                      NOT NULL,
  COLUMN_NUMBER    NUMBER                       NOT NULL,
  COLUMN_DECIMAL   INTEGER                      NOT NULL
)
;

It seems that I can not get INT, INTEGER values, I used in my create table syntax?
Int, Integer, small int seems to be a syntactic sugar only. But Toad at least holds information about INTEGER part even though he considers smallint,int,decimal and integer same.

Is there any way to find that if a column is INT/INTEGER/SMALLINT using SQL?

According the ANSI, DB2, and SQL/DS Data Types:

  SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow.

INT, INTEGER and SMALLINT are all mapped to the Oracle datatype NUMBER(38). Since that's what is stored in the dictionary, there's no way to recover the type as specified in the original query.

If you're not using comments on your columns for other purposes, you could store that information there when you create your tables, and retrieve it via the ALL_COL_COMMENTS (or similar) view.


How to maintain fixed number of rows per foreign key? I would like to keep a record of the last N (say, 10) objects a user has viewed. I'm thinking a basic way to store this information might be the following table:

CREATE TABLE history (
  id            SERIAL PRIMARY KEY,
  user_id       integer REFERENCES users (id),
  object_id     integer REFERENCES objects (id),
  view_date     timestamp DEFAULT current_timestamp
);

Since I only want to record the last 10 object views, I never need more than 10 rows for a given user_id. Is there an efficient way to maintain this limit whenever a user views an object and a new entry is created?

Or am I going about this the wrong way? Perhaps I should just limit to 10 results in during SELECT and periodically purge extra entries in some other manner?

It will be much more efficient to limit the SELECT and purge periodically.

If you had to maintain a hard limit of 10 for business requirements, what you'd do is define a trigger on history before insert or update or delete for each row that:

does a SELECT ... FOR UPDATE on the parent object_id to lock it, so it serves as a lock to prevent concurrent insert/delete of other history entries for the same object;
If this is an insert, counts rows for this object and gets the id of the oldest
If the number of entries will be increased to 11 by this insert, deletes the oldest row

Deadlocks can occur, so you need to be able to retry the whole transaction if there's a deadlock.


Commit vs Fast Commit vs Commit Cleanout in Oracle Database I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases.

Many sources confuse these terms and do not explain them in detail so it was a bit of a challenge to find info.

From what I gather:

Commit and fast commit are the exact same thing, all commits are fast commits.
A fast commit essentially only updates the flag in the transaction table of the undo/rollback segment header to indicate that the transaction has committed. However, the actual block is not revisited meaning that the undo byte address (UBA) in the interested transaction list (ITL) which is located in the header of the data block still points to the transaction table of the corresponding undo segment. Furthermore, the lock bytes of the corresponding rows are not released and the lock count in the ITL is unchanged (rows still locked).
In a commit cleanout, the block is revisited and the ITL is updated with the commit SCN. However, the lock count in the ITL and the lock byte stored with each row is still not updated (row still locked just like in fast commit), this does not generate redo even though the block is changed.
Blocks which were committed normally (== fast committed) will undergo Delayed Block Cleanout when they are next touched (and generate redo).
Blocks which were underwent commit cleanout will undergo Delayed Logging Block Cleanout
when they are next touched (and generate redo).

Hopefully someone can verify these points!
Thanks!

You have the basics right. There is only one type of commit (no normal, fast...).

from the concepts doc:

  When a transaction commits, the following actions occur:

  A system change number (SCN) is generated for the COMMIT.

  The internal transaction table for the associated undo tablespace
  records that the transaction has committed. The corresponding unique
  SCN of the transaction is assigned and recorded in the transaction
  table. See "Serializable Isolation Level".
  The log writer (LGWR) process writes remaining redo log entries in the
  redo log buffers to the online redo log and writes the transaction SCN
  to the online redo log. This atomic event constitutes the commit of
  the transaction.
  Oracle Database releases locks held on rows and tables.

  Users who were enqueued waiting on locks held by the uncommitted
  transaction are allowed to proceed with their work.
  Oracle Database deletes savepoints.
  Oracle Database performs a commit cleanout.

  If modified blocks containing data from the committed transaction are
  still in the SGA, and if no other session is modifying them, then the
  database removes lock-related transaction information from the blocks.
  Ideally, the COMMIT cleans out the blocks so that a subsequent SELECT
  does not have to perform this task.

So cleanout (full with redo) will be performed during commit if the blocks are still in the SGA.

In active systems, it is common for blocks with uncommited transactions to be written to disk and flushed from the SGA. In this case, the block is left as is and the next query that touches the block will perform delayed block cleanout (your point 5 doesn't happen in all cases).


SQL: Join on multiple tables I am trying to join the following tables:

Table 1: users (id, name)
Table 2: pivot_table (user_id, user_id2)

This table hold the relationship between the users. One user can be related to many other users.

Table 3: contacts (id, user_id, name)

I am trying to select a user and then find its related users and contacts.
I have tried to use the join, i am successfully able to get either the contacts or the users but not the both.

I used the following query:

select
    contacts.id as contact_id,
    users.id as user_id,
    pivot.user_id2 as user2_id
from
    `contacts`
    inner join `users` on  `contacts`.`user_id` = `users`.`id`
    inner join `pivot` as `pivot` on `pivot`.`user_id` = `contacts`.`user_id`

Suppose i have the following data in the tables
Users:

id, name
1, Sahil
2, Shubham
3, Xyz

Pivot table data

id, user_id, user_id2
1, 1, 2

Contacts table data

id, user_id, name
1, 1, Abc
2, 1, XYZ

Now what is get from the query is

contact_id, user_id, user_id2
1, 1, 2
2, 1, 2

but what i need is

contact_id, user_id, user_id2
1, 1, null
2, 1, null
null, 1, 2

I am not able to figure out what wrong i am doing.

Thanks

You need to look into the different JOIN types.

With an INNER JOIN as you have here the result will only include rows for cases where a match is found in all the sources.

With a LEFT OUTER JOIN or RIGHT OUTER JOIN the result will include rows for all data in one side of the join even if there is no match at the other side (columns SELECTed from the other side will come out as NULL).

With a FULL OUTER JOIN you will get rows output for all three conditions (match both sides, data on left with no match on right, and data on right with no match on left). I think this is what you want for the join between contacts and users.

Note that the word "outer" is usually optional so you could write LEFT JOIN instead of LEFT OUTER JOIN

This page has a useful diagram of join types: http://stevestedman.com/2015/03/mysql-join-types-poster/

It suggests that mySQL doesn't support FULL OUTER joins (which I didn't know was the case) so you will need to UNION two selects, one with a RIGHT join and one with a LEFT to get the sort of result you are looking for.


SQL Server Management Studio 2012 Application Cannot Start I'm trying to install SQL Server Management Studio on my laptop.

Operating System: Windows 7 Service Pack 1

But I got an error message saying, "The application cannot start.":

What shall I do? I'm a student and I need this for my thesis / school project.

When I had this issue, I was able to resolve the issue by deleting the folder C:\Users\<User account>\AppData\Roaming\Microsoft\SQL Server Management Studio\.

This may remove any personalized settings, etc., returning you to how SSMS works on a fresh install.


The leading zeroes before decimal point is missing in string formatting of decimal data in XML in db2 11.1 When generating XML that includes decimal values in  Db2 11.1, the leading zero right before the decimal point isremoved.

In the following query, the decimal value   .00000000000000 is published even it should come as 0.00000000000000 from the XMLELEMENT function.

SELECT XMLElement(
  NAME "keytable",
  XMLAGG(
    XMLELEMENT(
      NAME "tab",
      XMLAttributes( interp as "interp")
    )
  ) OPTION null ON NULL
)
FROM abc where wkstn_oid_sh = 100 AND wkstn_oid_lng = 4211"

The correct result should be :

<KEYTABLE><TAB INTERP="0.00000000000000"/><TAB INTERP="0.00000000000000"/> </KEYTABLE>.

But currently db2 returns :

<KEYTABLE><TAB INTERP=".00000000000000"/><TAB INTERP=".00000000000000"/></KEYTABLE>.

How can I get it to format decimals as 0.00000000000000?

If your consumer is so sensitive to the number format and cannot interpret values without leading zeroes, you can format your numeric values explicitly:

...xmlattributes(varchar_format(interp,'S00.00000000') as "interp")


Mysql Alter table add column with ALGORITHM=INPLACE, LOCK=NONE Am running below query

Query

Alter table dbname.tablename
add column column1 varchar(50),
add column column2 text ,
ALGORITHM=INPLACE, LOCK=NONE;

It takes more time to execute.

Ideally running this without ALGORITHM=INPLACE, LOCK=NONE takes 30mins to execute.

Query am running for table size=24.4G and records-count=97lkahs.

Any suggestions on why does it take more time to execute?

Is there any way that i could achieve better on the same?

Version= 5.6.40
, Engine = Innodb
, Buffer pool size = 44.8 GB

Found out the issue. Ran out of storage space on RDS while running Alter table query.

For some reason it did not throw error disk space issue.

But when i tried without ALGORITHM=INPLACE like the query below, it throws out error of disk space issue.

Alter table dbname.tablename
add column column1 varchar(50),
add column column2 text ,
LOCK=NONE;

I increased 100G to see if that suffices the problem, it worked.

But i still did not understand the fact why does the alter query take 60mins to add two columns when i run the Alter statement above.

While Alter statement to add single column takes 30mins to complete.

How can we improvise the execution time for Alter statement, any suggestions would help?


How do I search to see if a MySQL user exists on the system? I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I do this?

update: for clarification, as I'm thinking maybe I've not been 100% clear. by wildcard I meant I wanted to be able to search for only part of the users name, or for all users who's name matches pattern. On the system I needed this on there were 5k+ users, and although that's not performance loss huge, I don't want to look through them one at a time either.

Thanks to @matthewh's answer I managed to compose a query that would work when searching through a large number of users:

use mysql;
select  User, Host from  user where User like 'user%';

Note: <user> is whatever component of the username you need to search for, you may need a % on both sides if you don't know the start of the username, also <> are merely to note the variable, don't include them in an actual query, unless the username includes < or >.


Why do I get leading zeros when converting from decimal to char in a db2 query? I have a column named length defined as:

TYPE_NAME: DECIMAL
DATA_TYPE: 3
COLUMN_SIZE: 9
BUFFER_LENGTH: 11
DECIMAL_DIGITS: 5
NUM_PREC_RADIX: 10

When I run the following select:

select char(length) st_length, length from atable

I get leading zeros on the st_length column:

st_length   length
0043.00000  43.00000
0043.00000  43.00000
0045.00000  45.00000
0044.00000  44.00000
0044.00000  44.00000
0046.00000  46.00000

Why am I getting the leading zeros and how do I get rid of them?

Edit: I meant to say this earlier but forgot. I'm using db2 v9.5.8 LUW.

Resolution: My original goal was to get the left most character of the length. I ended up with this:

select left(char(cast(length as int)),1) st_length, length from atable

which yielded,

st_length  length
4          43.00000
4          43.00000
4          45.00000
4          44.00000
4          44.00000
4          46.00000

Improvements are welcome.

It appears by design. See behaviour changes for v9.7 for more.

I suggest you'd need VARCHAR inside LPAD with leading spaces


How much Tablespace we need to create for Oracle schema How is the optimum number of tablespace we need to create in an Oracle schema ?
One for data and one for index ?
Thanks

I don't think there is a "rule of thumb" but it really depends upon your hardware configuration.

With different tablespace for every Schema or Index and Data you can differentiate based on the cost or the speed of the disk space.


I am counfused about when Oracle database won't do parsing I am confused about when Oracle database won't do parsing?
In the AWR report, there is a metrics called "execute to parse", which means more SQL just execute without parsing when it increases.
But as the Oracle document describe:
"When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. "
It seems that everytime a SQL statement is issued, parsing will be called.
So I wandering when Oracle won't do parsing and make the "execute to parse" become a larger number?
Or I just misunderstood?
What Oracle document said is:
SQL Parsing
The first stage of SQL processing is parsing.
The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application, and not the database itself, can reduce the number of parses.
When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution.
https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL178
So if "an application issues a SQL statement, the application makes a parse call", then how applications "can reduce the number of parses"?

Execute To Parse is "a ratio between the number of times a sql [statement] is executed versus the number of times it is parsed" as noted in this reference. In this context, parsed means hard parsed (which is the full parsing process that's inclusive of generating an execution plan).
The reasoning this metric exists is because whenever a new query is ran in Oracle, it needs to be parsed so that the Oracle SQL Engine can determine the best execution plan to serve the data for the query. After it has been parsed, and an execution plan is generated, that execution plan is typically cached and therefore the next time that same exact query is executed, it doesn't need to be parsed again, rather it will use the existing cached execution plan.
You can read more information on execution plans and Oracle's cache in the Oracle documentation. You can also find more information on the Execute To Parse metric here.
To answer your question "how applications "can reduce the number of parses"?": The application can do so in two somewhat obvious ways (which is why that statement in the Oracle documentation may be confusing). The first way is the application can reduce the number of parses by reducing the number of queries it executes in the database (e.g. if a query is converted to application code instead). Obviously this is a silly answer, but it's true. The second, more relevant way, is it can reduce the number of unique queries it executes in the database.
Because a cached execution plan is based on the exact query text that was sent to the database server, even the most minor change such as a comment or a different value in the WHERE clause predicates could cause Oracle to parse and re-generate a new execution plan for a very similar query to one that was previously ran. If the application is building unique query strings for the WHERE clause predicates instead of passing them as actual parameters to the database server, then that query will be parsed every time. If instead, parameters were used, then every subsequent execution of that query would result in the cached execution plan to be used and that query wouldn't get re-parsed, therefore resulting in a positive number for the metric Execute To Parse ratio.


It's a good practice always restore controlfile before restore the database? I'm beginner in Oracle, but I'd like know if it's a good practice always restore the control-file of a backup-set before restore the database. I ask this because always when I restore the control-file I need open the database with reset-logs option and I don't know if it's a good practice to do this.

If the answer is no, so when I need restore the control-file?

It depends entirely on the nature of the failure and the need of recovery.  The ends-and-outs are too complex to put in a single answer here, but the Database Backup and Recovery User Guide has a whole section of scenarios on "Diagnosing and Responding to Failures". You would do well to study it.  And build yourself a private VM under VirtualBox, to practice on.

Recovering a database is the Prime Directive of any DBA.


mongo cluster collection count showing different every time I had a replica set earlier which I have converted to cluster.

Current configuration is:

3 shard
4 router
4 config server

When I am trying to get count of one of collection sometime it is showing null and some time some value , why this is happening?

Please help.

First, of course you are always connecting to your cluster thru mongoS, so query (count) goes to cluster and not directly to one individual shard.

Second, you are connected to right DB when you do count, I have seen situations where user has accidentally changed/connected to wrong DB and count (of course) return NULL, because that collection don't exists on that current DB.

You can always use

  db.getSisterDB("yourDB").collection.count()

where ever you are...

Actually count() returns "cached" (metadata) count from collection, if you want to have absolute value, use

  db.getSisterDB("yourDB").collection.find({_id:{ $gt: MinKey,
  $lt:MaxKey} }, {_id:1}).itcount()

what is "slow" operation because it really goes in the collection and count how many documents is there.


The 24 is the storage overhead per row in a table is fixed for all tables ot it varies? I wanted to know whether the storage overhead per row in atable is fixed for all tables created irrespective of given datatypes or it varies from one table to another?

The table row layout describes a header of 23 bytes for each table row, and that is independent of the data types.
One item that is variable is the “null bitmap” that is technically part of the row data: it is only present if there is a nullable column in the table. Yet another reason to define most columns as NOT NULL!


PostgreSQL: How can I list the tables to which a sequence belongs? I know I can list all sequences with this:

SELECT * FROM information_schema.sequences;

But I need to know by which table this sequence is used.

The reason I want to do this is to find out which sequences of PRIMARY KEYs are not in the default format of table_name_id_seq

Is this possible?

Note: My PostgreSQL version is 11.5.

The information is stored in pg_depend:

SELECT t.oid::regclass AS table_name,
       a.attname AS column_name,
       s.relname AS sequence_name
FROM pg_class AS t
   JOIN pg_attribute AS a
      ON a.attrelid = t.oid
   JOIN pg_depend AS d
      ON d.refobjid = t.oid
         AND d.refobjsubid = a.attnum
   JOIN pg_class AS s
      ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
  AND d.refclassid = 'pg_catalog.pg_class'::regclass
  AND d.deptype = 'i'
  AND t.relkind IN ('r', 'P')
  AND s.relkind = 'S';


Why would SQL Server ignore an index? I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement:

SELECT * FROM dbo.CustPassMaster WHERE CustNum = '12345678'

It ignores the index completely. This confuses me as I have another table CustDataMaster with way more columns (55), one of which is CustNum varchar(8). I created an index on this column (IX_dbo_CustDataMaster_CustNum) in this table, and use practically the same query:

SELECT * FROM dbo.CustDataMaster WHERE CustNum = '12345678'

And it uses the index I created.

Is there any specific reasoning behind this? Why would it use the index from CustDataMaster, but not the one from CustPassMaster? Is it due to the low column count?

The first query returns 66 rows. For the second, 1 row is returned.

Also, additional note: CustPassMaster has 4991 records, and CustDataMaster has 5376 records. Could this be the reasoning behind ignoring the index? CustPassMaster also has duplicate records that have the same CustNum values as well. Is this another factor?

I am basing this claim on the actual execution plan results of both queries.

Here is the DDL for CustPassMaster (the one with the unused index):

CREATE TABLE dbo.CustPassMaster(
    [CustNum] [varchar](8) NOT NULL,
    [Username] [char](15) NOT NULL,
    [Password] [char](15) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustPassMaster_CustNum] ON dbo.CustPassMaster
(
    [CustNum] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

And the DDL for CustDataMaster (I've omitted a lot of irrelevant fields):

CREATE TABLE dbo.CustDataMaster(
    [CustNum] [varchar](8) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustDataMaster_CustNum] ON dbo.CustDataMaster
(
    [CustNum] ASC
)WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I don't have a clustered index on either of those tables, only one nonclustered index.

Ignore the fact that the datatypes don't entirely match the type of data being stored. These fields are a backup from an IBM AS/400 DB2 database, and these are the compatible datatypes for it. (I have to be able to query this backup database with the exact same queries, and get the exact same results.)

This data is only used for SELECT statements. I don't do any INSERT/UPDATE/DELETE statements on it, except for when the backup application is copying data from the AS/400.

Typically indexes will be used by SQL Server if it deems it more expedient to use the index than to directly use the underlying table.

It would seem likely the cost-based optimizer thinks it would be more expensive to actually use the index in question.  You may see it use the index if instead of doing SELECT *, you simply SELECT T1Col1.

When you SELECT * you are telling SQL Server to return all columns in the table.  To return those columns SQL Server must read the pages for the rows that match the WHERE statement criteria from the table itself (clustered index or heap).  SQL Server is probably thinking the amount of reads required to get the rest of the columns from the table means it might as well scan the table directly.  It would be useful to see the actual query and the actual execution plan used by the query.


Oracle change initialization parameter with "deferred" keyword The description of deferred and scope keyword is listed below (extracted from Oracle documentation). The meaning of them gives me an indication that deferred can just use the memory scope. Is my understanding right? Thanks.

DEFERRED
The DEFERRED keyword sets or modifies the value of the
parameter for future sessions that connect to the database. Current
sessions retain the old value.
SCOPE
The SCOPE clause lets you specify when the change takes effect.
Scope depends on whether you started up the database using a
traditional plain-text parameter file (pfile) or server parameter file
(spfile).
MEMORY indicates that the change is made in memory, takes effect
immediately, and persists until the database is shut down. If you
started up the database using a parameter file (pfile), then this is
the only scope you can specify.
SPFILE indicates that the change is made in the server parameter
file. The new setting takes effect when the database is next shut down
and started up again. You must specify SPFILE when changing the value
of a static parameter that is described as not modifiable in Oracle
Database Reference.
BOTH indicates that the change is made in memory and in the server
parameter file. The new setting takes effect immediately and persists
after the database is shut down and started up again.
If a server parameter file was used to start up the database, then
BOTH is the default. If a parameter file was used to start up the
database, then MEMORY is the default, as well as the only scope you
can specify.

I've checked experimentally (Oracle 11.1) that actually ALTER SYSTEM ... DEFERRED SCOPE=SPFILE is allowed, although it seems to me that DEFERRED is ignored in this context. Also SCOPE=BOTH and SCOPE=MEMORY are allowed, obviously.

What you cannot do (the manual that you quote overlooked it) is ALTER SYSTEM ... DEFERRED on the parameter that has (V$PARAMETER.ISSYS_MODIFIABLE, V$PARAMETER.ISSES_MODIFIABLE) = (IMMEDIATE, FALSE). This results in:

ORA-02096: specified initialization parameter is not modifiable with this option


Child records under the parent record in SQLite I have two tables for Billing, one Bill_Master and other is Bill_Detail. The records in both tables are as follows...

**BILL_MASTER**
id    party    bill_amount
1      abc      500
2      def      600

**BILL_DETAILS**
mstr_id    sr_no    perticular    amount
 1          1        lunch box     100
 1          2        water bag     400
 2          1        pencil boxes  300
 2          2        a4 papers     100
 2          3        staple pins   200

Now I want to make a result table as per below

**RESULT_TABLE**
mstr_id    party      billamount
 1         abc           500
           lunch box     100
           water bag     400
 2         def           600
           pencil boxes  300
           a4 papers     100
           staple pins   200

How can I do it?

The following query creates these rows:

mstr_id    party      billamount  sort1  sort2
 1         abc           500        1     -1     from bill_master
 2         def           600        2     -1
           lunch box     100        1      1     from bill_details
           water bag     400        1      2
           pencil boxes  300        2      1
           a4 papers     100        2      2
           staple pins   200        2      3

These sort* values cause the rows to be ordered correctly after sorting.

SELECT id AS mstr_id,
       party,
       bill_amount AS billamount,
       id AS sort1,
       -1 AS sort2
FROM BILL_MASTER
UNION_ALL
SELECT NULL,
       perticular,
       amount,
       mstr_id,
       sr_no
FROM BILL_DETAILS
ORDER BY sort1,
         sort2


Selecting only overlapping elements from array of ranges The table I need to search contains an array of numrange values illustrated by:

CREATE TABLE data ( sensor varchar(25), ranges numrange[] );
INSERT INTO data VALUES
  ('sensor0','{"[872985609.0,873017999.0]","[873021600.0,873035999.0]","[873039600.0,873072070.0]"}'::numrange[])
, ('sensor1','{"[872929250.000000,872985609.000000]"}'::numrange[]);

Searching for rows with elements in ranges that overlap a specified range is easy with ANY:

SELECT * FROM data WHERE '[873021700,873021800]'::numrange && ANY (ranges)

I would like a SELECT statement that returns fields from the table along with only elements from the array that overlap a specified range, aka:

SELECT sensor,[array of overlapping numranges] FROM data WHERE '[873021700,873021800]'::numrange && ANY (ranges)

resulting in:

sensor      ranges
sensor0     {"[873021600.0,873035999.0]"}

Is this possible without unnest()ing the array in a subselect?  If not, what would be an efficient way to unnest() when the arrays are potentially very large?

This works as desired:

SELECT d.sensor, r.overlapping_ranges
FROM   data d
JOIN   LATERAL (
   SELECT array_agg(range) AS overlapping_ranges
   FROM   unnest(d.ranges) range
   WHERE  range && '[873021700,873021800]'::numrange
   ) r ON overlapping_ranges IS NOT NULL;

About LATERAL:

What is the difference between LATERAL and a subquery in PostgreSQL?

For big tables, it would be much more efficient to normalize your design with a separate ranges table (one range per row) instead of the ranges array. You can use a GiST index for that:

PostgreSQL: how do I query over a column with a custom range?
2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

Solution for huge table

For a huge table like you mention in the comments (1 billion rows) I would consider a separate ranges table, optimized for size and a BRIN index to go along with it.

Assuming:

Read only (or mostly) data.
A maximum of 6 fractional digits (scale) and a maximum of 18 digits total (precision). Scaled by 1000000, this fits into a bigint without loss, which is considerably cheaper to store. See below.
Postgres 9.5 or later.

The operator class for range types shipped with Postgres 9.5 is range_inclusion_ops, which supports the overlaps operator &&.

To optimize disk space some more I would just save two bigint numbers (your numeric values multiplied by 1000000) and make it a functional BRIN index. Basically like this:

CREATE TABLE sensors (
  sensor_id serial PRIMARY KEY
, sensor text NOT NULL);

CREATE TABLE ranges (
  sensor_id int NOT NULL REFERENCES sensors
, range_low bigint NOT NULL
, range_hi  bigint NOT NULL
);

INSERT INTO sensors (sensor) VALUES ('sensor1');

INSERT INTO ranges (sensor_id, range_low, range_hi) VALUES
   (1, 872985609.0 * 1000000, 873017999.0 * 1000000)  -- scaled
 , (1, 872929250.000000 * 1000000, 872985609.000000 * 1000000);

CREATE INDEX ranges_brin_idx ON ranges USING BRIN (int8range(range_low, range_hi, '[]'));

Query to get the same result as before:

SELECT s.sensor, r.ranges
FROM  (
   SELECT sensor_id
        , array_agg(numrange(range_low * .000001, range_hi * .000001, '[]')) AS ranges
   FROM   ranges
   WHERE  int8range(range_low, range_hi, '[]')
       && '[873021700000000,873021800000000]'::int8range  -- scaled as well
   GROUP  BY sensor_id
   ) r
JOIN   sensors s  USING (sensor_id);

Storage size of bigint vs. numrange

A numrange with numbers of 15 precision occupies 32 bytes on disk,
resulting in 64 bytes per row (plus int column, tuple header and item identifier).

While the same with two bigint columns (2 x 8 bytes) results in 52 bytes total. Makes the table around 12 GB smaller. Index size is the same.

See for yourself:

SELECT pg_column_size((1::bigint, '[873021700.123456,873021800.123456]'::numrange))
     , pg_column_size((1::bigint, 873021700123456::bigint, 873021700123456::bigint));

Detailed explanation for row size:

Configuring PostgreSQL for read performance


Parameter Sniffing: Why Does This Become An Issue? Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds).

After suspecting parameter sniffing as the culprit, I masked the input parameters, and the query executed faster.

My question is: why did this happen?

This system has been in production for more than 5 years, and this is the first time we've seen anything like this on our stored procedures. Is this "Database wear and tear"?

We've resolved the issue, so it isn't a big deal, but I'm just curious as to why this was happening.

Basically what happens is, when SQL Server sees a query that it needs to compile, it is going to use the first-time-called parameters to generate the execution plan.  This may or may not be a good thing, but it is what happens.

For instance, say you have a table of fruit (100 rows).  There are 98 rows that are Apple, and only 2 rows that contain the fruit Orange.  If you query that table for Apple, then the plan will most likely compile with a Scan.  This is a good thing, as it is optimized for that Apple query.  But then when you want to query for the Orange, that Scan is inefficient.  But it is the stored plan that is being used.

The fact of the matter is that it happens.  It happens all the time.  It's typically not an outstanding issue, but in some cases it can be a pretty big problem.  A solution to an ongoing bad parameter sniffing problem is that you can utilize the OPTIMIZE FOR query hint to force SQL Server to use certain parameter values when generating the execution plan upon initial compilation.


pg_xlog not recycling WAL files on slave server I've setup streaming replication with Postgres 9.3
My problem is that on the slave server the pg_xlog folder just gets fuller and fuller and WAL files are not getting recycled.
The slave server has the following (relevant) values in postgresql.conf:
wal_keep_segments = 150
hot_standby = on
checkpoint_segments = 32
checkpoint_completion_target = 0.9
archive_mode = off
#archive_command = ''

My initial replication command was:
pg_basebackup  --xlog-method=stream -h <master-ip> -D .  --username=replication --password

So I guess my WAL files are OK.
Here is my slave server startup log:
2017-05-08 09:55:31 IDT LOG:  database system was shut down in recovery at 2017-05-08 09:55:19 IDT
2017-05-08 09:55:31 IDT LOG:  entering standby mode
2017-05-08 09:55:31 IDT LOG:  redo starts at 361/C76DD3E8
2017-05-08 09:55:31 IDT LOG:  consistent recovery state reached at 361/C89A8278
2017-05-08 09:55:31 IDT LOG:  database system is ready to accept read only connections
2017-05-08 09:55:31 IDT LOG:  record with zero length at 361/C89A8278
2017-05-08 09:55:31 IDT LOG:  started streaming WAL from primary at 361/C8000000 on timeline 1
2017-05-08 09:55:32 IDT LOG:  incomplete startup packet
2017-05-08 09:58:34 IDT LOG:  received SIGHUP, reloading configuration files
2017-05-08 09:58:34 IDT LOG:  parameter "checkpoint_completion_target" changed to "0.9"

I even tried to copy older WAL files from master server manually to slave but that also didn't help.
What am I doing wrong? How can I stop the pg_xlog folder from growing indefinitely?
Is it related to the "incomplete startup packet" log message?
One last thing: under the pg_xlog\archive_status folder all the WAL files are with .done suffix.
Appreciate any help I can get on this.
Edit:
I enabled log_checkpoints in postgresql.conf.
Here are the relevant log entries since I enabled it:
2017-05-12 08:43:11 IDT LOG:  parameter "log_checkpoints" changed to "on"
2017-05-12 08:43:24 IDT LOG:  checkpoint complete: wrote 2128 buffers (0.9%); 0 transaction log file(s) added, 0 removed, 9 recycled; write=189.240 s, sync=0.167 s, total=189.549 s; sync files=745, longest=0.010 s, average=0.000 s
2017-05-12 08:45:15 IDT LOG:  checkpoint starting: time
2017-05-12 08:48:46 IDT LOG:  checkpoint complete: wrote 15175 buffers (6.6%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=209.078 s, sync=1.454 s, total=210.617 s; sync files=769, longest=0.032 s, average=0.001 s
2017-05-12 08:50:15 IDT LOG:  checkpoint starting: time
2017-05-12 08:53:45 IDT LOG:  checkpoint complete: wrote 2480 buffers (1.1%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=209.162 s, sync=0.991 s, total=210.253 s; sync files=663, longest=0.076 s, average=0.001 s

The problem seems to have been resolved.

Apparently I had hardware issues on the master server.
I was able to perform full pg_dump and re-index my DB so I was pretty sure I did not have any data integrity issues.

But when looking at the master server logs after I've enabled log_checkpoints in the config - a few minutes before the slave server stopped performing checkpoints I saw the following message:

IDT ERROR:  failed to re-find parent key in index "<table_name>_id_udx" for split pages 17/18

After seeing that - I decided to switch hosting provider and moved my DB to a new server.
Since then (almost a week now) - everything has been running smoothly replication and checkpoints are running as expected.

I really hope this will help other people - but when something like this is happening - always be advised that this issue might be caused by data integrity/hardware issues.


Why is the padding on months in Oracle 9 characters? I ran two simple sql statements

select length(to_char(sysdate, 'fmMonth')) from dual;
select length(to_char(sysdate, 'Month')) from dual;

The length of the first statement was 4 (It's June) and the length of the second statement was 9.  Why does it pad 5 extra characters unless you specifically ask it not to?

The only reason I could come up with was that the month with the most amount of characters is september (nls_date_language set 'English') which has 9 character.

Second question is if you changed your nls_date_language variable would you also change the amount of padding?

The "why" goes back to the fact that when Oracle was originally built, output almost always went to some sort of fixed-width medium (either a terminal window or a physical printer).  That meant that everyone's general preference that output widths be fixed since it made sure that everything was in alignment without the programmer or the application having to do any work.  Today, of course, things aren't rendered in fixed width fonts all that often, the output of SQL statements is rarely sent to a printer, and applications have controls that make it very easy to present data in grids without anyone having to worry about blank padding output to make it align.  But the behavior of functions like TO_CHAR was defined back in the olden days so when the preferred behavior changes, the only way to handle that without breaking backwards compatibility is to provide new format masks that provide the newly desired behavior.