Schema design for products with multiple variants/attributes? I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes.

From all the research I've done this seems the most likely solution for me and I'm just wondering if there's anything wrong with the following schema and what are the upsides/downsides?

Thank you

Table: products
------------------------------
| ID | ProductName           |
|----------------------------|
| 1  | Leather Wallet Case   |
| 2  | Jeans                 |
| 3  | Power Bank            |

Table: products_variants
-------------------------------
| ID | ProductId | ParentId | Variant  | VariantName | SKU  | StockTotal | WholeSalePrice | BuyPrice | OnSale | OnSalePrice |
|---------------------------------------------------------------------------------------------------------------------------|
| 1  | 1         | null     | model    | iPhone5     | SKU  | 10         | 3              | 10       | null   | null        |
|---------------------------------------------------------------------------------------------------------------------------|
| 2  | 1         | null     | model    | iPhone4     | null | null       | null           | null     | null   | null        |
| 3  | 1         | 2        | color    | Red         | SKU  | 10         | 3              | 10       | null   | null        |
| 4  | 1         | 2        | color    | Blue        | SKU  | 10         | 3              | 10       | null   | null        |
|---------------------------------------------------------------------------------------------------------------------------|
| 5  | 2         | null     | size     | M           | null | null       | null           | null     | null   | null        |
| 8  | 2         | 5        | color    | Black       | SKU  | 10         | 3              | 10       | null   | null        |
| 9  | 2         | null     | size     | XXL         | SKU  | 10         | 3              | 10       | null   | null        |
| 10 | 2         | 9        | material | Cotton      | null | null       | null           | null     | null   | null        |
| 11 | 2         | 10       | color    | Red         | SKU  | 10         | 3              | 10       | null   | null        |
| 12 | 2         | 10       | color    | Blue        | SKU  | 10         | 3              | 10       | null   | null        |
| 13 | 2         | 9        | material | Casmir      | null | null       | null           | null     | null   | null        |
| 14 | 2         | 13       | color    | Green       | SKU  | 10         | 3              | 10       | null   | null        |
| 15 | 2         | 13       | color    | Brown       | SKU  | 10         | 3              | 10       | null   | null        |
|---------------------------------------------------------------------------------------------------------------------------|
| 13 | 3         | null     | null     | null        | SKU  | 10         | 3              | 10       | null   | null        |

This is just the info from @lesandru reply, I really find it very useful, so credits to him and @sahalMoidu

Applying normalization to your problem the solution is as given. Run and see it on Fiddle

Fiddle

CREATE TABLE products
    (
     product_id int auto_increment primary key,
     name varchar(20),
     description varchar(30)

    );

INSERT INTO products
(name, description)
VALUES
('Rug', 'A cool rug'  ),
('Cup', 'A coffee cup');

create table variants (variant_id int auto_increment primary key,
                       variant varchar(50)
                       );
insert into variants (variant)
values ('color'),('material'),('size') ;
create table variant_value(value_id int auto_increment primary key,
                           variant_id int ,
                           value varchar(50)
                           );

insert into variant_value (variant_id,value)
values (1 ,'red'),(1 ,'blue'),(1 ,'green'),
        (2 ,'wool'),(2 ,'polyester'),
        (3 ,'small'),(3 ,'medium'),(3 ,'large');

create table product_Variants( product_Variants_id int  auto_increment primary key,
                            product_id int,
                            productVariantName varchar(50),
                            sku varchar(50),
                            price float
                            );

create table product_details(product_detail_id int auto_increment primary key,
                             product_Variants_id int,

                             value_id int
                             );

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-wool' ,'a121',50);

insert into product_details(product_Variants_id , value_id)
values( 1,1),(1,4);

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-polyester' ,'a122',50);

insert into product_details(product_Variants_id , value_id)
values( 2,1),(2,5);


How to determine how much storage space a column takes in Oracle? How to determine how much storage space on the disk a column takes in a table in Oracle?

I'm running Oracle 11gR2 11.2.0.3.

You can use the VSIZE to get the number of bytes for the internal representation of a value.

If you want the total space used by a column, you'll need to sum over all the rows (unless the type used has a fixed length).

The DUMP function will also give you the size and raw representation of a value, but not in a format amenable to easy summation.


Postgres JSON output using specific column as JSON key I have data in Postgres (10.4) in the form of

id | my_json
-----------------------------------
1  | {"a": 1, "b": 2, "c": [1,2,3]}
2  | {"a": 3, "b": 5, "c": [2,3,4]}

I can output the data as JSON like this:

WITH l AS (SELECT id, my_json as data FROM table)
SELECT to_json(l) FROM l;

and i get:

{"id": 1, "data": {"a": 1, "b": 2, "c": [1,2,3]}}
{"id": 2, "data": {"a": 3, "b": 5, "c": [2,3,4]}}

But is there a way to output the data, keyed by the id?

Like this:

{"1": {"a": 1, "b": 2, "c": [1,2,3]}}
{"2": {"a": 3, "b": 5, "c": [2,3,4]}}

You can use json_build_object function which allows you to calculate keys or values.

WITH l AS (SELECT id, my_json as data FROM table)
SELECT json_build_object(id, data) FROM l;

The result will be like;

|                        json_build_object |
|------------------------------------------|
| {"1" : {"a": 1, "b": 2, "c": [1, 2, 3]}} |
| {"2" : {"a": 1, "b": 2, "c": [1, 2, 3]}} |

Here is sample fiddle


a query that listing all rows and sizes of tables in a Database I am using SQL Server 2005.My question is;I am looking for a query that  listing all rows and sizes of tables in a Database.


What is Fastest way to shrink a datafile? I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server > 2012, but that is not the subject of my question).
Before that, I'm going to move almost all objects from the main datafile of 4To to multiple datafiles on the same filegroup so I can get back the free space on the 4Tb file.
After moving objects, free space take like forever to be reclaimed with a dbcc shrink file with blocs of 256Mo.
What's the fastest way to get that space back to the OS?
Is there a way to use a backup restore to shrink the file?

DBCC SHRINKDATABASE
The fastest way I find to shrink a data file is to first use the SHRINK DATABASE command to see what it takes off the top. It will usually do this quickly--but not always. Keep an eye on blocking locks while you're doing this.
caveat
SHRINKDATABASE is impartial and will attempt indiscriminately attempt to shrink any file in the database. If you don't appreciate this behavior then don't run this one.
DBCC SHRINKFILE
After you've used SHRINKDATABASE you'll need to use SHRINKFILE. Here I find it's important to "chunk" the shrinks in small sizes. Also, I use a T-SQL DELAY to allow the database to gain back some resources to do other activities as needed. Using the script below you'll need to enter 1.> your database name, 2.> your logical file name of the file, 3.> and the upper and lower bounds, in MB, for your upper database size in MB and 4.> your lower database size in MB. You'll notice that it shrinks in 100 MB increments but, again, you'll need to find the size that's right for you. Warning: larger increments aren't necessarily more speedy. Don't worry "too much" if your lower bounds is too low--it won't/can't shrink past it. (You'll have to forgive my code, it's over 10 years old but still get's the job done).
--Cut and paste the code that this T-SQL generates and run THAT code against your DB
 declare @next int, @last int, @DELAY VARCHAR(255), @SHRINK VARCHAR(1000)

    SELECT @DELAY='WAITFOR DELAY ''000:00:01'''
    select @next=102000, @last=50000
    while(@next >=@last)
    begin
    --print @next
    SELECT @SHRINK ='use [<your DB name>] DBCC SHRINKFILE (N''<enter logical file name>'','+convert(varchar(255),@next)+ ')'
    print @shrink
    print @DELAY
    print 'go'
    select @next=@next-100
    end


SQL (Postgres): Subquery in WHERE clause with IN operator is slow I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
    select tp.task_id
    from task_participant tp
    where tp.person_id = p.id )
and p.id = 1234

The EXPLAIN (ANALYZE, COSTS) generates the following result:
Aggregate  (cost=15850181.01..15850181.02 rows=1 width=8) (actual time=25155.942..25155.943 rows=1 loops=1)
  ->  Nested Loop  (cost=0.28..15849814.75 rows=146504 width=0) (actual time=973.619..25155.778 rows=143 loops=1)
        Join Filter: (SubPlan 1)
        Rows Removed by Join Filter: 286635
        ->  Index Only Scan using personxpk on person p  (cost=0.28..4.30 rows=1 width=8) (actual time=0.018..0.021 rows=1 loops=1)
              Index Cond: (id = 1234)
              Heap Fetches: 1
        ->  Seq Scan on task t  (cost=0.00..13952.08 rows=293008 width=8) (actual time=0.006..53.361 rows=286778 loops=1)
        SubPlan 1
          ->  Bitmap Heap Scan on task_participant tp  (cost=4.63..103.38 rows=26 width=8) (actual time=0.013..0.078 rows=143 loops=286778)
                Recheck Cond: (person_id = p.id)
                Heap Blocks: exact=38705207
                ->  Bitmap Index Scan on task_participantx1  (cost=0.00..4.62 rows=26 width=0) (actual time=0.007..0.007 rows=143 loops=286778)
                      Index Cond: (person_id = p.id)
Planning Time: 0.186 ms
Execution Time: 25155.994 ms

I don't understand why the sub select is being executed 286778 times. I think that Postgres should execute the subselect only 143 times, because the condition is clear based on the outer query. What am I missing?
EDIT:
It is a generated query. The desired result (and performance) could also be achieved with:
select count(*)
from person p
join task_participant tp on tp.person_id = p.id
join task t on t.id = tp.task_id
where p.id = 1234

However, I cannot easily rewrite the current query to this better approach. I'm hoping to find a way to achieve a similar execution time without completely changing the query.

Test
select count(*)
from person p
cross join task t
where EXISTS (
    select NULL
    from task_participant tp
    where tp.person_id = p.id
      AND t.id = tp.task_id )
  and p.id = 1234


REORGANIZE seems to work as well as REBUILD all the time On this MSDN Page it says if you should reorganize or rebuild based on the amount of fragmentation:

 5% to 30%   ->   ALTER INDEX REORGANIZE
 Over 30%    ->   ALTER INDEX REBUILD WITH (ONLINE = ON)*

However, we have noticed that even with really high fragmentation (over 95%) on large and small tables, REORGANIZE works fine.  (The fragmentation goes down to less that 1%).

Why does the MSDN page say this?  Is it not supposed to work like it is for me?

Or is there a drawback I am missing?  (A hidden problem if I don't rebuild?)

Let me quote another part of that same page, which basically says IT DEPENDS - emphasis mine:

  These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment.

There's not really a good answer for this except "it depends"... those are just general guidelines and may or may not be appropriate depending on the number of rows, the columns in your index, their data types, how wide the index is, what else is getting written to disk in between writes to those tables, etc.

Like how big should I size tempdb, what is a good threshold for PLE, and what type of car should I buy, the answer is the same: IT DEPENDS.


What is an example of an extproc.ora file? What do the contents of extproc file look like? I'm aware that my extproc.ora file is missing, and I'd like to create one in it's absence.

The instructions here tell me to:

  Configure the Oracle extproc.ora to point to the location of the
  ST_Geometry library file.

  Make a backup copy of the extproc.ora file on the Oracle server. Open
  the extproc.ora file and alter it to point to the location of the
  st_shapelib.dll (Windows) or libst_shapelib.so (Linux or UNIX)
  library.

  The following line is added
  to extrproc.ora to point to the ST_Geometry library in that directory:

  SET EXTPROC_DLLS=ONLY:C:\mylibraries\st_shapelib.dll

  Save and close the extproc.ora file.

I've tried creating an extproc.ora file from scratch, but I don't really know what I'm doing: SET EXTPROC_DLLS=ONLY:C:\mylibraries\st_shapelib.dll.

Not surprisingly, the file fails.

Rather than just dream up what I think the file should contain, I'd like to copy an existing example. I've searched the web, but have not come up with any complete examples of extproc.ora. Not even in the Oracle documentation: https://docs.oracle.com/database/121/NETAG/advcfg.htm#NETAG0133

What is an example of an extproc.ora file?

Why do you expect that "there must be more to it"? extproc.ora simply sets environment variables for the extproc daemon, which is used to execute code for external stored procedures, and for procedures themselves. There is some documentation in the application development guide.

The one environment variable that seems to be recognized by extproc itself is EXTPROC_DLLS that you mention, which determines the locations where the daemon looks for shared libraries. If set to ONLY:<path> it won't look in $ORACLE_HOME/bin or $ORACLE_HOME/lib.

Any other variables set in extproc.ora are simply passed on to the environment where the external code runs, so they would be specific to each library.


Article table has a lot of null. What is the best way to prevent null in MySQL I'm optimizing table schema.

My case is like.

User can generate an article.

It has a lot of item. like name, age, sex, address, hobby...

name, sex is a required entry.

but age, address, hobby is not required entry.

Table has a lot of null currently.

Sometimes null is changed attribute domain value when user edit article.

  name   |   age   |   sex   |   address   |   hobby
  aa         null      male        null        soccer
  bb         17        female      null        null

So I'm thinking 2 case.

1.

Table: article
Column: id, name, sex
Primary key: id

Table: article_sub
Column: article_id, type(0:age, 1:address, 2:hobby), value
Primary key: article_id, type

This case have to set varchar data type to value.

Because age is integer, address is string... so size is demerit.

2.

Table: article
Column: id, name, sex
Primary key: id

Table: article_sub_integer
Column: article_id, value
Primary key: article_id

Table: article_sub_string
Column: article_id, type(0:address, 1:hobby), value
Primary key: article_id, type

This case I have to refer 3 table.

Which way is the better?

If you have other idea Please tell me.

Thank you.

If you are short of storage and database is big enough you can reduce the size ot table but the price is significantly higher CPU and RAM consumption. Just split the table

PERSON
+----+------+------+-------------+
| ID | name | attr | description |
+----+------+------+-------------+

into the two tables:

PERSON
+----+------+------+
| ID | name | attr |
+----+------+------+

P_DESCR
+------+-------------+
| P_ID | description |
+------+-------------+

Each time you need the full info do a JOIN..ON

SELECT *
  FROM      person  AS w
  LEFT JOIN p_descr AS z ON z.p_id = w.id
 WHERE w.id = 1234
;

LEFT JOIN fill description column by NULL if no according row in the P_DESCR table for certain PERSON

Both approaches - with NULLs and JOINs are valid but you can choose what is more suitable for your needs.


What is the optimal data type for an MD5 field? We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute).

There is a table names that serves as a sort of central registry. Each row has a text field representation and a unique key that is an MD5 hash of that representation.1 This table currently has tens of millions of records and is expected to grow into the billions over the lifetime of the application.
There are dozens of other tables (of highly varying schemas and record counts) that make reference to the names table. Any given record in one of these tables is guaranteed to have a name_key, which is functionally a foreign key to the names table.

1: Incidentally, as you might expect, records in this table are immutable once written.

For any given table other than the names table, the most common query will follow this pattern:

SELECT list, of, fields
FROM table
WHERE name_key IN (md5a, md5b, md5c...);

I would like to optimize for read performance. I suspect that my first stop should be to minimize the size of the indices (though I wouldn't mind being proven wrong there).

The Question:
What is/are the optimal data types for the key and name_key columns?
Is there a reason to use hex(32) over bit(128)? BTREE or GIN?

The data type uuid is perfectly suited for the task. It only occupies 16 bytes as opposed to 37 bytes in RAM for the varchar or text representation. (Or 33 bytes on disk, but the odd number would require padding in many cases to make it 40 bytes effectively.) And the uuid type has some more advantages.
Example:
SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;

See:

Convert hex in text representation to decimal number
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

You might consider other (cheaper) hashing functions if you don't need the cryptographic component of md5, but I would go with md5 for your use case (mostly read-only).
A word of warning: For your case (immutable once written) a functionally dependent (pseudo-natural) PK is fine. But the same would be a pain where updates on text are possible. Think of correcting a typo: the PK and all depending indexes, FK columns in "dozens of other tables" and other references would have to change as well. Table and index bloat, locking issues, slow updates, lost references, ...
If text can change in normal operation, a surrogate PK would be a better choice. I suggest a bigserial column (range -9223372036854775808 to +9223372036854775807 - that's nine quintillion two hundred twenty-three quadrillion three hundred seventy-two trillion thirty-six something billion) distinct values for billions of rows. Might be a good idea in any case: 8 instead of 16 bytes for dozens of FK columns and indexes!). Or a random UUID for much bigger cardinalities or distributed systems. You can always store said md5 (as uuid) additionally to find rows in the main table from the original text quickly. Related:

Default value for UUID column in Postgres

As for your query:

Optimizing a Postgres query with a large IN

To address @Daniel's comment: If you prefer a representation without hyphens, remove the hyphens for display:
SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')

But I wouldn't bother. The default representation is just fine. And the problem's really not the representation here.
If other parties should have a different approach and throw strings without hyphens into the mix, that's no problem, either. Postgres accepts several reasonable text representations as input for a uuid. The manual:

PostgreSQL also accepts the following alternative forms for input: use
of upper-case digits, the standard format surrounded by braces,
omitting some or all hyphens, adding a hyphen after any group of four
digits. Examples are:
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

What's more, the md5() function returns text, you would use decode() to convert to bytea and the default representation of that is:
SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')

\220\267R^\204\366HP\302\357\264\007\372\343\362q

You would have to encode() again to get the original text representation:
SELECT encode(my_md5_as_bytea, 'hex');

To top it off, values stored as bytea would occupy 20 bytes in RAM (and 17 bytes on disk, 24 with padding) due to the internal varlena overhead, which is particularly unfavorable for size and performance of simple indexes.
Everything works in favor of a uuid here.


What is the best query to use to monitor a SQL Server database's status? I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state.

This is the query that I inherited for this check:

SELECT name AS [SuspectDB],
  DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],
  DATABASEPROPERTY(name, N'IsOffline') AS [Offline],
  DATABASEPROPERTY(name, N'IsEmergencyMode') AS [Emergency],
  has_dbaccess(name) AS [HasDBAccess]
FROM sysdatabases
WHERE (DATABASEPROPERTY(name, N'IsSuspect') = 1)
   OR (DATABASEPROPERTY(name, N'IsOffline') = 1)
   OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)
   OR (has_dbaccess(name) = 0)

If that query returns any results, the assumption being made is that the database is in a suspect or potentially bad state.

Is there a better way to do this?

If you're using SQL 2005+ and only want to return the DB name where the DB isn't in the "ONLINE" state I'd use this:

SELECT
    name
FROM sys.databases
WHERE state != 0;

Remember that databases participating in mirroring or log shipping will not be online or may change state regularly. For more info about the sys.databases DMV see documentation here: http://msdn.microsoft.com/en-us/library/ms178534.aspx


Discarding results from CTE based on another CTE WITH R1 AS
(
    // some complex query
),
R2 AS
(
    // uses R1
    // another complex query
),
// here I already have dataset with Id, Name, ProductReference and other columns
R3 AS
(
    select  Name, ProductReference, Count(*) as Count
    from Products
    group by Name, ProductReference
    having Count(*) > 1
)

Now I need to filter R2 results based on R3, so that if Name and Reference combinations ARE present in R2 then only select where ID is minimum, discard rest of them

R2
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
2       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data
5       n1          someguid1           some data
6       n1          someguid2           some data

R3
Id      Name        ProductReference    Count
1       n1          someguid1           4

Expected Output
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data

if im not mistaken, you can get the result you want in R3,
simply use

select min(id) as Id,Name,ProductReference
from Products
group by Name,ProductReference

the result will return exactly what you are looking for
in your example, n1 will be grouped to its minimum id, thus the result set will be

  Id,Name,PR,OC
  1,n1,...,... 3,n2,...,... 4,n3,...,...


Different execution plans depending on columns selected from CTE I have the following table

CREATE TABLEA(
ID NUMERIC(19,0) IDENTITY(1,1),
COLUMNA varchar(256) NOT NULL,
COLUMNB varchar(256) NOT NULL,
COLUMNC varchar(256)  NOT NULL,
COLUMND varchar(max)  NULL,
COLUMNE bigint  NOT NULL,
COLUMNF numberic(19,0),
CONSTRAINT PK_ID PRIMARY KEY CLUSTERED
(
 COLUMNA ASC
)
) ON PRIMARY

CREATE NONCLUSTERED INDEX IDX_COLUMNE_COLUMNF ON TABLEA(COLUMNE, COLUMNF)

I am running the following CTE.

WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY COLUMNE ORDER BY COLUMNE) AS rownum,
COLUMNA,
COLUMNB,
COLUMNC,
COLUMND,
COLUMNE,
COLUMNF
WHERE COLUMNF = 1 AND COLUMNE >= 1472738400000 AND COLUMNE <= 1475244000000
)

Now the question. When I run

SELECT TOP 30000 * FROM CTE

SQL does an index seek on the nonclustered index with a key lookup on the clustered and takes 3 to 4 seconds to complete.

However, if I run the below SQL does a clustered index scan and completes in about 15 seconds.

SELECT TOP 30000 COLUMNA, COLUMNB, COLUMNC, COLUMND, COLUMNE, COLUMNF FROM CTE

To give some idea of distribution of values:

The table contains approx. ~13.7 million rows.
COLUMNF always has a value of 1.
Excluding the TOP 30000 the query returns 474296 rows, of which there are 92683 have a rownum value of 2 or higher.

Can anyone provide any insight as to why SQL would pick a different execution plan for the two queries?

Execution Plans

SELECT by column name
https://www.brentozar.com/pastetheplan/?id=HyJZeZlC

SELECT *
https://www.brentozar.com/pastetheplan/?id=r1BQgZl0

The plan without row number is below.

This is assigned a cost of 44.866.

You have a TOP without ORDER BY so SQL Server just needs to scan the clustered index and as soon as it finds the first 30,000 rows matching the predicate it can stop.

The table has 13,283,300 rows. A full clustered index scan is costed at 730.467 + 14.6118 = 745.0788 but this gets scaled down to 43.9392 because of the TOP.

Applying the same scaling of 5.9% to the number of rows in the table this would imply that SQL Server estimates that it will only have to scan 783,350 rows before it finds 30,000 matching the WHERE and can stop scanning.

NB: You say that only 474,296 rows match this predicate in the whole table but 508,747 are estimated to. That means that on average one in every 26.1 (13283300/508747) rows is assumed to match the filter. So it is estimated that 30,000 * 26.1 rows ( = 783K) will be read.

When you select * that means that the rownum column must be calculated. the plan for this is below. It is costed at 69.1185

You have an index on COLUMNE that can be seeked into. This satisfies the range predicate on COLUMNE >= 1472738400000 AND COLUMNE <= 1475244000000 and also supplies the required ordering for your row numbering.

However it does not cover the query and lookups are needed to return the missing columns. The plan estimates that there will be 30,000 such lookups. There may in fact be more as the predicate on COLUMNF = 1 may mean some rows are discarded after being looked up (though not in this case as you say COLUMNF always has a value of 1).

If the row numbering plan was to use a clustered index scan it would need to be a full scan followed by a sort of all rows matching the predicate. 69.1185 is considerably cheaper than the 745.0788 + sort cost so the plan with lookups is chosen.

You say that the plan with lookups is in fact 5 times faster than the clustered index scan. Likely a much greater proportion of the clustered index needed to be read to find 30,000 matching rows than was assumed in the costings. You are on SQL Server 2014 SP1 CU5. On SQL Server 2014 SP2 the actual execution plan now has a new attribute Actual Rows Read which would tell you how many rows it did actually read. On previous versions you can use OPTION (QUERYTRACEON 9130) to see the same information.


Generic or better yet custom message of the day in Oracle sql plus I'm trying to create a message of the day type notification when users login to sqlplus.

Currently I use the login.sql to add a dbms_application_info and set a module and action for the message.  This has limitations so I was wondering if Oracle has anything like a MOTD feature. Or maybe someone has created their own solution.

Thanks in advance.

Anything like this?

$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
set serveroutput on feedback off
exec dbms_output.put_line('******************');
exec dbms_output.put_line('MESSAGE OF THE DAY');
exec dbms_output.put_line('******************');
set serveroutput off feedback on

Logging in:

$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 26 00:49:29 2014

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

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

******************
MESSAGE OF THE DAY
******************
SQL>


Missing entries in join I've got a question about a query.

I've got two tables. The first table has to columns, a start-date and an end-date.
For example start: 00:00:00 and end: 00:15:00

The first table period:

|---start---|----end----|
| 00:00:00  | 00:15:00  |
| 00:15:01  | 00:30:00  |
| 00:30:01  | 00:45:00  |
| 00:45:01  | 01:00:00  |
|-----------|-----------|

The seconds table tt contains following information: name,zeit,points

|---name---|----zeit----|---points---|
|   AAAA   |  00:11:00  |     5      |
|   AAAA   |  00:12:00  |     5      |
|   BBBB   |  00:08:00  |     9      |
|   BBBB   |  00:10:00  |     9      |
|   CCCC   |  00:13:00  |     8      |
|   AAAA   |  00:31:00  |     1      |
|   BBBB   |  00:37:00  |     3      |
|   AAAA   |  00:47:00  |     7      |
|   BBBB   |  00:50:00  |     2      |
|   CCCC   |  00:55:00  |     4      |
|----------|------------|------------|

I've used the following query to summarize the points of each team grouped by the period:

SELECT
   period.end,
   IFNULL(sum(tt.punkte), 0) punkte,
   tt.name
FROM period
   LEFT JOIN tt ON (tt.zeit < period.end AND tt.zeit >= period.start)
GROUP BY period.end,tt.name

With this query I get this result:

|---end----|---punkte---|----name----|
| 00:15:00 |     10     |    AAAA    |
| 00:15:00 |     18     |    BBBB    |
| 00:15:00 |      8     |    CCCC    |
| 00:45:00 |      1     |    AAAA    |
| 00:45:00 |      3     |    BBBB    |
| 01:00:00 |      7     |    AAAA    |
| 01:00:00 |      2     |    BBBB    |
| 01:00:00 |      4     |    CCCC    |
|----------|------------|------------|

As you can see the results table is missing the rows with end 00:30:00 and there are only two rows with end 00:45:00.

So I've tried everything to add these rows to the result:

| 00:30:00 |      0     |    AAAA    |
| 00:30:00 |      0     |    BBBB    |
| 00:30:00 |      0     |    CCCC    |
| 00:45:00 |      0     |    CCCC    |

How can I change the query to get these missing rows to my result?

Another way, that doesn't use dynamic SQL would be to first CROSS JOIN the period with a (derived) table that holds DISTINCT name values from tt and then LEFT JOIN tt:

SELECT
    period.end,
    n.name,
    IFNULL(SUM(tt.punkte), 0) AS punkte
FROM
        period
    CROSS JOIN
        ( SELECT DISTINCT name FROM tt ) AS n
    LEFT JOIN
        tt
    ON
       tt.name = n.name
       AND (tt.zeit < period.end AND tt.zeit >= period.start)
GROUP BY
    period.end,
    n.name ;


Order of composite index Suppose I have a query like:

SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

I have an index on id and an index on column but often I add a composite index with both that can improve efficiency of queries like this.  My question is concerning the order of the columns in the index.  By trial and error I've found that sometimes the DBMS prefers the joined index first and sometimes it prefers the WHERE index first.

In the above query is there a hard, fast rule I can abide by to know which key order will work best?

Typically I just add both indexes, run EXPLAIN on the query and check to see which is preferred, then remove the other one.  But this process feels like it could be improved by a better understanding of the logic involved in determining index order.

For this query

SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

The optimal way is execute it is

The WHERE clause provides some filtering, so let's make use of it.  That is, have an index on table_b starting with column.  (Later we'll discuss whether to make it composite.)  So, the Optimizer will use that index to find row(s) of table_b.
For each of those rows, JOIN to table_a.  (Note that JOIN, not LEFT JOIN is being used; LEFT JOIN is a different story.)
To reach into table_a, an index starting with id is needed.  (Note:  USING(id) means table_a.id = table_b.id.)

So far, we have

b:  INDEX(column)
a:  INDEX(id)   -- though it probably exists as PRIMARY KEY(id)

Covering?

We don't know what other columns there are in the two tables.  If there are very few columns, then it might be tempting to build a "covering" index.  This is an index that contains all the columns needed anywhere in the SELECT.  The benefit is some performance speedup by looking only in the index's BTree and not having to touch the data BTree.

For table_b, it would be tempting to say INDEX(column, id).  That would be good (and 'covering') if there were only those two columns.  But there are probably more columns.  So, probably INDEX(column) is all that is worth doing.

For table_a, I assume that id is the PRIMARY KEY (which is, by definition, unique and an index).  So nothing more is needed there.

Bottom line:  Use the two single-column indexes listed above.

And this example does not exemplify anything about "composite" indexes.  For more on that, see

Cardinality & Range
Cardinality and Composite
Single-column index
Indexing cookbook

  but often I add a composite index with both that can improve efficiency of queries like this...

Better Example

As I said, your example does not exemplify the question.  So, I'll try to answer "When should I use a composite index"?  There are many cases (see the links); I'll give you a simple case.

WHERE x = 1
  AND y > 2

The relevant characteristics are:

x and y are in the same table.  (Can't build an index across two tables.)
AND is used.  (OR can't be optimized.)
One of the tests is with =.  (Composite won't help if both are ranges.)
y is a "range" (examples:  y>2, y LIKE 'm%', y BETWEEN ... AND ...).

The general rule is:

Put all the = columns first (x in my example)
Put one range column last (y)

That is, you must order it INDEX(x,y).

For WHERE x = 1 AND y = 2 (both =), it does not matter whether you have INDEX(x,y) or INDEX(y,x) .

Another tidbit:  With ENGINE=InnoDB, the PRIMARY KEY column(s) are implicitly tacked onto each secondary key.  Hence, your INDEX(column) is the same as INDEX(column, id).  But this fact does not play a role in this discussion.

I realize that I am disagreeing with other Answers here (and elsewhere), but I stand my ground.


What can cause higher CPU time and duration for a given set of queries in trace(s) ran on two separate environments? I'm troubleshooting a performance issue in a SQL Server DR environment for a customer. They are running queries that consistently take longer in their environment than our QA environment. After analyzing traces that were performed in both environments with the same parameters/filters and with the same version of SQL Server (2016 SP2) and the exact same database, we observed that both environment were picking the same execution plan(s) for the queries in question, and the number of reads/writes were close in both environments, however the total duration of the process in question and the CPU time logged in the trace were significantly higher in the customer environment. Duration of all processes in our QA environment was around 18 seconds, the customer was over 80 seconds, our CPU time was close to 10 seconds, theirs was also over 80 seconds. Also worth mentioning, both environments are currently configured to MAXDOP 1.
The customer has less memory (~100GB vs 120GB), and slower disks (10k HHD vs SSD) than our QA environment, but but more CPUs. Both environments are dedicated to this activity and should have little/no external load that wouldn't match. I don't have all the details on CPU architecture they are using, waiting for some of that information now. The customer has confirmed they have excluded SQL Server and the data/log files from their virus scanning. Obviously there could be a ton of issues in the hardware configuration.
I'm currently waiting to see a recent snapshot of their wait stats and system DMVs, the data we originally received, didn't appear to have any major CPU, memory or Disk latency pressure. I recently asked them to check to see if the windows power setting was in performance or balanced mode, however I'm not certain that would have the impact we're seeing or not if the CPUs were being throttled.
My question is, what factors can affect CPU time and ultimately total duration? Is CPU time, as shown in a sql trace, based primarily on the speed of the processors or are their other factors I should be taking in to consideration. The fact that both are generating the same query plans and all other things being as close as possible to equal, makes me think it's related to the hardware SQL is installed on.

That production has more CPUs does not matter when you use MaxDOP 1. Then only CPU Clock cycle matters. With many CPUs, you often get lower 1.x GHz clock cycle. A higher clock cycle on your QA could explain some of the difference.
If the database cannot fit into memory, then your hard disk speed difference sounds as if it alone can explain the different duration time.
May I recommend that you install the First Responder Kit from Brent Ozar?
https://www.brentozar.com/first-aid/
This will alert you to the most obvious problems.


How to get index last modified time in Oracle? Given an index name, I want to obtain its last modification time and the author of modification in Oracle 11g. Is there any query to achieve this?

I will elaborate on the @Jeffrey Kemp's comment. Since the information about authors of modifications is not collected in the objects' metadata, you will want to use database auditing mechanisms.

As per Verifying Security Access with Auditing chapter of Database Security Guide, there are several types of auditing, but Standard Auditing is probably the only type of auditing you will need according to the requirements you mentioned in the question.

Before you can use standard auditing, you should enable it (if it already isn't) setting the AUDIT_TRAIL initialization parameter to the appropriate value (refer to the link). I evaluated standard auditing with AUDIT_TRAIL set to DB,EXTENDED which allows to capture SQL statements.

SQL> alter system set audit_trail = db,extended scope=spfile;
System altered.

SQL> startup force;
ORACLE instance started.

Once you have enabled standard auditing, all the auditing policies existing in your database will start to capture the auditing details.

There are four levels in standard auditing: Statement, Privilege, Object, Network. (See the table in the same chapter of Security Guide.)

With the following standard auditing statements of different levels I was able to collect some useful auditing information regarding index creation, index rebuild, index drop, and making index unusable.

audit index by access;
audit create any index;
audit index on hr.employees by access;

Sample output based on my activity is below.

SQL> select username, sql_text from dba_audit_object order by timestamp;

USERNAME          SQL_TEXT
--------          ------------------------------------------------------
hr                create index audidx on employees(last_name)
hr                alter index audidx rebuild
scott             drop index hr.audidx
scott             create index hr.audidx on hr.employees(last_name)
scott             alter index hr.audidx rebuild
scott             alter index hr.audidx unusable

Make sure you understand what's written in the relevant chapter of Security Guide I referred to, and do some experiments before using your newly implemented audit policies in production.

For the other types of auditing and useful information regarding auditing check my previous answers.


Split SSIS project in to multiple files to avoid merge hell Since SSIS does not like being merged nicely I was wondering how to have a big SSIS package split up. With the idea of having multiple devs working on this simultaneously, and a minimal chance of having merge conflicts.

In SSIS 2016 I found these options:

package parts, but apparently they don't share connection managers. I don't want to have 100 different connection managers.
subpackages, still this doesn't look very clean and I also wonder if this is what it is intended for. Also, the debugger goes crazy too opening the subpackages while running. Any other drawbacks I should know of?

I can't be the only person with this problem. Is there an other way to achieve this?

I've been using subpackages successfully on multiple projects. The typical scope is one sub-package per target table.  I also have a few utility packages eg Disable Indexes, Rebuild Indexes (on a table, passed as a parameter). A single "Master Control Package" is mostly Execute Package tasks.

With the Project Deployment method, devs need to be aware that prematurely checking in their changes to a single package can block the deploy (it wont deploy a project until all the packages validate cleanly).


How do I add values to partition function and scheme? We have a partition function defined as

CREATE PARTITION FUNCTION [PartitionByPeriodFunction](int)
AS RANGE LEFT
FOR VALUES (20101228, ... , 20161227)

where each date is the first date of our fiscal period.

We have a partition scheme defined as

CREATE PARTITION SCHEME [PartitionByPeriodScheme]
AS PARTITION [PartitionByPeriodFunction]
TO ([FactsBefore2011], ..., [FactsP201612], [FactsAfter2016])

I want to add more periods to the partition function and more filegroups to the partition scheme - what is the best way to do this?

I have read to drop and recreate the function and scheme - will the dependent objects allow that? I am unsure how to use split since I am not wanting to split 20161227 but rather continue the pattern.

You can modify the partition scheme and function to effectively add a new partition.

ALTER PARTITION SCHEME [PartitionByPeriodScheme] NEXT USED [FactsAfter2016_2];

Follow this up with:

ALTER PARTITION FUNCTION [PartitionByPeriodFunction]() SPLIT RANGE 20171231;

This will add a range between 20161227 and 20171231, which will be placed on  [FactsAfter2016_2].

As always, try this on a non-production system first.


MongoDB ixscan seems to be scanning more objects than expected I'm using MongoDB 2.6.12 and a collection has the following index {a: 1, d: -1}. In case the types matter a is a Number and d is a Timestamp.

From what I can see in the code we are limiting this query to 21 documents. However, the query log below suggests that the index was used but it still scanned 903483 documents.

2016-05-27T22:13:05.722-0700 [conn890] query my.collection query: { $query: { a: 12345 }, $orderby: { d: -1 } } planSummary: IXSCAN { a: 1, d: -1 } cursorid:312034207299 ntoreturn:21 ntoskip:903462 nscanned:903483 nscannedObjects:903483 keyUpdates:0 numYields:89 locks(micros) r:3550704 nreturned:21 reslen:1028 1820ms

We are using pymongo 2.8.1 and mongengine 0.7.9

Can anyone explain why and if we are actually scanning that large number of objects in this query and what I should look for to solve the problem?

The large number of nscanned key comparisons is explainable by the skip value: the query is skipping 903,462 documents (ntoskip) in order to return 21 (ntoreturn). The  nscanned value in your output is the sum of ntoskip and ntoreturn.

The number of nscannedObjects (identical to nscanned) is because the skip stage in MongoDB 2.6.x query processing happens after the document fetch and projection stages.

There are a few suggested optimizations for skip performance you can watch and upvote in the MongoDB issue tracker:

SERVER-13946: Consider putting skip stages below fetch stages
SERVER-14663: Put skip stage below projection

In general, large values of skip are discouraged (see: cursor.skip() documentation).

Depending on your use case for skip(), alternative approaches to consider include:

use range-based queries for pagination, if possible
limit pagination for your API/UI (a skip value of 903,462 documents with 21 per page implies this query might be for page number 43,023 of results)
use $sample (MongoDB 3.2+) or an alternative approach for fetching/sampling random documents


High Availability for MariaDB and Postgres : replication or active-passive (SAN) During the last weeks I did a lot of research on High Availability for Postgres and MariaDB. Both by my own research and also recommended by vendor's, replications seems to have more advantages.

Some benefits as using a standby node for backup; scaling; the fact it is simpler (no need for storage replication) and the flexibility...

However; our own DBA's don't like replication. Our Oracle DBA mentions replication is only useful in addition to clustering.

Is replication a valid (or even better) alternative for active-passive clustering? What do most new HA implementations use?

-- a Linux architect

(Relating to MySQL only...)

In my opinion, a Galera-based solution is the best HA available today.  MariaDB includes Galera in the latest releases; PXC also has it; or you can tack Galera onto Oracle's MySQL.

"Replication" is an integral part of Galera, but there are low-level differences
Automatically recovers from single-point-of-failure
3 node (server) minimum
Uses quorum voting to discover what has failed, and avoid "split-brain"
If the 3 nodes are in 3 different datacenters, no single natural disaster can take out the 'system'
Read scaling (multiple nodes providing such)

Load balancing, proxying requests, etc, are related topics.


Get all orders that are "ordered" but not "paid" Let's say I have two tables.  Order and OrderState.

Order
ID|Description|
1 | tv order
2 | computer order

OrderState
ID|Description|OrderID
1 |ordered    | 1
2 |paid       | 1
3 |received   | 1
4 |ordered    | 2

How would one get all orders that are "ordered" but not "paid"?

SELECT * FROM Order as o
    INNER JOIN OrderState as os
        on o.ID = os.OrderID
WHERE os.Description='Ordered' AND NOT EXISTS(
    SELECT * FROM Order as o
        INNER JOIN OrderState as os
            on o.ID = os.OrderID
    WHERE os.Description='paid');

I tried the query above but it does not seem to work.

For a different method to obtain the orders that are only in a given state, such as "Ordered", but not "Recieved", you could use something like the following:

use tempdb;

CREATE TABLE dbo.OrderStatus
(
    OrderStatusID INT NOT NULL
        CONSTRAINT PK_OrderStatus
        PRIMARY KEY CLUSTERED
    , OrderStatusDesc VARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Orders
(
    OrderID INT NOT NULL
        CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , OrderDesc VARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Orders_OrderStatus
(
    OrderID INT NOT NULL
        CONSTRAINT FK_Orders
        FOREIGN KEY REFERENCES dbo.Orders(OrderID)
    , OrderStatusID INT NOT NULL
        CONSTRAINT FK_OrderStatusID
        FOREIGN KEY REFERENCES dbo.OrderStatus(OrderStatusID)
    , CONSTRAINT PK_Orders_OrderStatus
        PRIMARY KEY CLUSTERED
        (OrderID, OrderStatusID)
);

Populate some sample data:

INSERT INTO dbo.OrderStatus (OrderStatusID, OrderStatusDesc)
VALUES (1, 'Ordered')
    , (2, 'Paid')
    , (4, 'Received');

INSERT INTO dbo.Orders (OrderDesc)
VALUES ('TV')
    , ('Computer');

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
VALUES (1, 1) -- TV, Ordered
    , (1, 2)  -- TV, Paid
    , (1, 4)  -- TV, Received
    , (2, 1); -- Computer, Ordered

If you want to see the description of the order, you can do a simple join and an aggregate to get the list:

SELECT o.OrderDesc
FROM dbo.Orders o
    INNER JOIN dbo.Orders_OrderStatus oos ON o.OrderID = oos.OrderID
GROUP BY o.OrderID, o.OrderDesc
HAVING SUM(oos.OrderStatusID) = 1;

If, however, you only need a list of OrderIDs, you can simplify further to just:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 1;

If you wanted to see a list of orders that were both "Ordered" and "Received", you could change the above query to:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 3;

Indeed, if you need to see a list of orders that have been "Ordered", "Received", and "Paid", you would do:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 7;

The above uses SQL Server syntax, although it would be straight-forward enough to convert this to other RDBMSs that use SQL syntax.

The advantage of using this type structure is scalability.  Time required for searching orders with any given status is a linear, or O(n) operation.

Let's try it with a tiny bit more data.  In order to run this you'll need to drop the tables above, then recreate them.

Create 100,000 orders:

INSERT INTO dbo.Orders (OrderDesc)
SELECT TOP(100000) o.name + '.' + o1.name + '.' + o2.name
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark all of them as "Ordered":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 1
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark 60,000 of them as "Paid":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(60000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 2
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark 10,000 of them as "Recieved":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 4
FROM sys.objects o, sys.objects o1, sys.objects o2;

Select all orders that have been "Ordered", and "Paid":

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 3;

On my desktop machine, this results in the following stats:

(50000 row(s) affected)
Table 'Orders_OrderStatus'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 286 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

With an execution plan:

Nice and efficient!


How to resolve this 'XML Validation: Declaration not found for element' error? I'm new to SQL Server and I have this task to complete (for learning purposes), yet I'm unable to figure out this error due to inexperience with XML Schemas (I think)

Here's what I'm trying to do -

Create a table with 2 columns - ID, XML info for some product (non-typed).
Add a row entry, that is, XML data for a product.
Create an XML Schema for the XML-product-data.
Bind this schema to the one (column) in the table.

I did this similar to what is mentioned here.

-- Create table ProductDocs
CREATE TABLE ProductDocs
(
    ID INT IDENTITY PRIMARY KEY,
    ProductDoc XML NOT NULL
);

-- Insert data into ProductDocs
INSERT INTO ProductDocs
(ProductDoc)
VALUES ('<Product>
            <ProductID>1</ProductID>
            <ProductName>Chai</ProductName>
            <SupplierID>1</SupplierID>
            <CategoryID>1</CategoryID>
            <QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>\
            <UnitPrice>18.0000</UnitPrice>
            <UnitsInStock>39</UnitsInStock>
            <UnitsOnOrder>0</UnitsOnOrder>
            <ReorderLevel>10</ReorderLevel>
            <Discontinued>0</Discontinued>
        </Product>');

-- Create XML Schema
CREATE XML SCHEMA COLLECTION XS_ProductDoc
AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
               targetNamespace="http://www.microsoft.com/schemas/adventure-works/products"
               xmlns:prod="http://www.microsoft.com/schemas/adventure-works/products">

        <xs:element name="Product">
            <xs:complexType>
                <xs:sequence>
                    <xs:element ref="prod:ProductID" />
                    <xs:element ref="prod:ProductName" />
                    <xs:element ref="prod:SupplierID" />
                    <xs:element ref="prod:CategoryID" />
                    <xs:element ref="prod:QuantityPerUnit" />
                    <xs:element ref="prod:UnitPrice" />
                    <xs:element ref="prod:UnitsInStock" />
                    <xs:element ref="prod:UnitsOnOrder" />
                    <xs:element ref="prod:ReorderLevel" />
                    <xs:element ref="prod:Discontinued" />
                </xs:sequence>
            </xs:complexType>
        </xs:element>

        <xs:element name="ProductID" type="xs:integer" />
        <xs:element name="ProductName" type="xs:string" />
        <xs:element name="SupplierID" type="xs:integer" />
        <xs:element name="CategoryID" type="xs:integer" />
        <xs:element name="QuantityPerUnit" type="xs:string" />
        <xs:element name="UnitPrice" type="xs:double" />
        <xs:element name="UnitsInStock" type="xs:integer" />
        <xs:element name="UnitsOnOrder" type="xs:integer" />
        <xs:element name="ReorderLevel" type="xs:integer" />
        <xs:element name="Discontinued" type="xs:boolean" />

    </xs:schema>';

-- Bind the schema
ALTER TABLE ProductDocs
ALTER COLUMN ProductDoc xml (XS_ProductDoc);

But I'm getting this error -

  XML Validation: Declaration not found for element 'Product'. Location:
  /*:Product[1] The statement has been terminated.

Why is this error occurring?
And, how can I resolve it?

An XML Schema Collection for that particular piece of XML would look more like this:

CREATE XML SCHEMA COLLECTION XS_ProductDoc
AS
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Product">
        <xs:complexType mixed="true">
            <xs:sequence>
                <xs:element name="ProductID" type="xs:unsignedByte" />
                <xs:element name="ProductName" type="xs:string" />
                <xs:element name="SupplierID" type="xs:unsignedByte" />
                <xs:element name="CategoryID" type="xs:unsignedByte" />
                <xs:element name="QuantityPerUnit" type="xs:string" />
                <xs:element name="UnitPrice" type="xs:decimal" />
                <xs:element name="UnitsInStock" type="xs:unsignedByte" />
                <xs:element name="UnitsOnOrder" type="xs:unsignedByte" />
                <xs:element name="ReorderLevel" type="xs:unsignedByte" />
                <xs:element name="Discontinued" type="xs:unsignedByte" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'

The reason your code does not work is because your XML does not contain any namespaces, whereas the original AdventureWorks XML does.  Learn more about XML Namespaces here.


Row inserted to DB in main app is not available in the different process. Can MySQL defer some queries? I have such problem. My main app process some request and insert the new row into DB, then send an Event to the queue. I have the different process that listens on that queue and when he get details about an inserted object and try to get it from DB, DB returns empty row. I wrote an algorithm with backoff and usually after 3 or 4 times, I manage to get that row.
And my question is, are they some default caching mechanism in MySql that cause it.

I am using Laravel 5.0 as app framework, MySQL 5.6.38 as DB and

Use Innodb.

Do not use LOCK TABLES.

Do

BEGIN;
-- some SQL statements that need to be treated atomically:
INSERT INTO my_table ...;
INSERT INTO my_queue ...;
COMMIT;

By using BEGIN and COMMIT, you can ignore the setting of autocommit, which may be tripping you up.

There may be some tricky business going on in the queue; please show us the SQL for enqueuing and dequeuing, pluse SHOW CREATE TABLE.


Clarification on MySQL innodb_flush_method variable Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in layman's terms on the difference in O_DSYNC and O_DIRECT, and how to know if it's a performance issue on a database server.

Some stats on my setup: Mac OSX 10.6 (32-bit kernel, since architecture is out of date) running MySQL 5.1.49-64bit (hoping it would allow me to use the memory). 8GB RAM, ~6GB of innodb data/indexes.

Here is an explanation on how fdatasync() works vs how fsync() works
fdatasync() flushes all data buffers of a file to disk (before the system call returns). It resembles fsync() but is not required to update the metadata, such as access time. Applications that access databases or log files often write a tiny data fragment (e.g., one line in a log file) and then call fsync() immediately in order to ensure that the written data is physically stored on the harddisk. Unfortunately, fsync() will always initiate two write operations

one write operation for the newly written data
one write operation in order to update the modification time stored in the inode

If the modification time is not a part of the transaction concept, then fdatasync() can be used to avoid unnecessary inode disk write operations.
In English, O_DSYNC is faster than O_DIRECT since O_DIRECT calls fsync() twice (one for logs and one for data) and fsync() verifies data writes via two write operations. Using O_DSYNC calls fdatasync() and fsync(). You can think of fdatasync() as doing an asynchronous fsync() (not verifying data).
Looking at the numbers, O_DSYNC does four write ops, two of which are verified, while fsync() does four write operations, all being verified afterwards.
CONCLUSION
O_DSYNC

faster than O_DIRECT
Data may/may not be consistent due to latency or an outright crash

O_DIRECT

more stable
data consistent
naturally slower

I hope this answer helps, and I hope I didn't make things worse for you.


MYSQL How do I query to show data in separate columns from one field? sorry if my question is worded poorly, my SQL vocabulary is very poor.

So basically, suppose I had a table as so:

CREATE TABLE MYTABLE
{
item_id   int   AUTO_INCREMENT  PRIMARY KEY,
item_name VARCHAR(20)
};
INSERT INTO MYTABLE (item_name) VALUES
('Fred'),
('Arthur'),
('Jane');

Now i want to fetch data that lists "Fred", "Arthur", and "Jane" into three separate columns. Is there a way to do this?

ORACLE, NOT MYSQL

CREATE TABLE MYTABLE ( item_id int PRIMARY KEY, item_name VARCHAR(20) );

INSERT INTO MYTABLE (item_id,item_name) VALUES (1,'Fred');

INSERT INTO MYTABLE (item_id,item_name) VALUES (2,'Arthur');

INSERT INTO MYTABLE (item_id,item_name) VALUES (3,'Jane');

select * from mytable;

**ITEM_ID ITEM_NAME**
---------- --------------------
         1 Fred
         2 Arthur
         3 Jane

3 rows selected.

select
  item_id,
  (select unique item_name
     from MYTABLE
     where item_name='Fred') as fred,
  (select unique item_name
     from MYTABLE
     where item_name='Arthur') as Arthur,
  (select unique item_name
     from MYTABLE
     where item_name='Jane') as Jane
from MYTABLE;
/

   ITEM_ID FRED                 ARTHUR               JANE
---------- -------------------- -------------------- --------------------
         1 Fred                 Arthur               Jane
         2 Fred                 Arthur               Jane
         3 Fred                 Arthur               Jane

3 rows selected.

I am only learning but this works, remember subquery must return only one row, otherwise there will be error.

One more, this should work faster:

select
 item_id,
 decode(item_name,'Fred',1,0) as Fred,
 decode(item_name,'Arthur',1,0) as arthur,
 decode(item_name,'Jane',1,0) as jane
from MYTABLE;

  ITEM_ID       FRED      ARTHUR        JANE
---------- ---------- ---------- ----------
         1          1          0          0
         2          0          1          0
         3          0          0          1

3 rows selected.

Hope this helps.


Does leaving a connection open for a matter of seconds cause deadlocks? I have been testing a system application and have noticed that it is slow when it connects to the database overseas.
This is to be expected to some extent, but there is a big difference with the domestic version, and after investigation realized that after each function the connection is closed.
So, for one operation the connection is opened and closed around 30 times causing considerable lag.
I asked why can't the connection be left open and closed at the end.
The reply was, "It causes deadlocks".

How true is this, and if there is any advice please provide some.
(System application is used by few hundred people but not constantly in use by everyone.)

I can only speak toward SQL Server.

A database connection itself is not going to cause deadlocks. If it did connection pooling would be useless.

How they are handling and managing transactions against the database can have an impact on deadlocks. There are plenty of scripts online that can be found on how to force a deadlock using SSMS, most are with two query windows that are separate sessions over one connection.


Compress files and directories into zip file and put it into blob field What is the best way to do the following using SQL Server?

Compress all the files, folders and subfolders from a specific location (specified in a parameter in a stored procedure) - the server side
In the blob return the zipped file as bulkcolumn - client side

Is it even possible?

Does it need to be done in real time?  I would engineer the solution differently.

The client uses a stored procedure to write to a queue table with what needs to be compressed.
A script running on the server periodically checks the queue table and zips up everything that needs to be zipped up, marks it as complete and does whatever else needs to be done.


Read-only replica of RDS Postrgres instance with modified schema We have a Postgres instance hosted in Amazon RDS. The idea is to create some kind of a read-only replica for a separate application. This application requires some modifications of the DB schema - additional indexes, denormalization, etc.

What is the common practice to achieve such goals?

You can't do this in RDS at the moment. You can spin a replica as a mirrored database but it does not allow the advanced features for logical decoding or partial replication. Unfortunately this is the caveat on the AWS services. The best option will be to move out RDS to EC2.


Setup [master] - [master-slave] - [slave] replication I am trying to set up a MySQL replication architecture between 3 MySQL servers by following a tutorial.

Server A as a master to Server B
Server B as a slave of server A and a master for server C
Server C as a slave of server B.

Using this architecture, the goal is if any data will be changed on Server A, both the server B and C must be updated.

After doing some tests, I got the following results:

Replication between server B and A is working well.
Any change on server A is mirrored on B but not in server C.
Testing if replication is working between server B and C (by manually editing some information in server B data), I was surprised that everything is working well, data edited in server B was edited in server C.

I can't understand why if Server A is edited and B is following it server C in that case is not following?

First of all, I dislike your setup. When Server B fails, the Server C will not be in replication anymore. If you want to be safe, that the data is on every server in sync, you should try to use a cluster. A cluster is minimum 3 servers and would fit your environment.

Next question for me is, why don't you use just one master (Server A) and two slaves (B+C)?

To answer your question, you need to activate the relay log, so that all changes to the slave are stored for the other slaves:

# REPLICATION slave to other slaves #
log-slave-updates              = 1
relay-log                      = /var/lib/mysql/relay-bin
slave-net-timeout              = 60


How do I name the thing that is done in the FROM part of the query? So, I have recently started studying data bases in Uni, MySQL.
I am practicing some queries with different stuff that the professor gave us.
One of the questions i have found is as follows:
Show the Name and Wage of the employee supervisor and the average wage of the employees that he is supervising. Arrange the data by name and the average wage in an ascending order.
(I'm working on the newest version of the MySQL Workbench with the accompanying local host server)
There is only one table, the employee table. With a foreign key supervisor ID that references the employee IDs which are primary keys.
This is the solution to the query:
SELECT SUP.name, SUP.wage, AVG(EMP.wage) AS AVG_WAGE
FROM employee EMP, employee SUP
WHERE SUP.employee_ID = EMP.supervisor_ID
GROUP BY SUP.name
ORDER BY AVG(EMP.wage) ASC;

I'm wondering, what is the name of the thing that he used here where he like... split the employee table into 2 virtual tables for the sake of the query?
I didn't know this is possible at first so I was doing this:
SELECT name, wage, AVG(wage) AS AVG_WAGE
FROM employee
WHERE emp_ID IN (SELECT sup_ID FROM employee)
GROUP BY name, wage, sup_ID IN (SELECT emp_ID FROM employee)
ORDER BY AVG(wage);

This shows the supervisors and their wages but doesn't show the average wage of the employees that each supervisor supervises and just displays the wage of the supervisors again with a bunch of zeroes at the end.
Thank you!
I'm sorry if I'm not very clear in my wording since I'm not studying databases in English and English is not my first language. Thus it isn't easy to find info on google about a bit specific things like this where I don't know how to word questions.

I just read your examples closer and understand your question better now. Your second example that you were doing uses a subquery but the first example your professor was doing is called an implicit inner join. It's just a shorthand way to write FROM employee EMP INNER JOIN employee SUP ON SUP.employee_ID = EMP.supervisor_ID like @stickybit commented.


Oracle 10g using collection with sql I have a stored procedure that takes collection of objects. (TABLE OF MyCustomType). Inside the procedure I'm trying to join this parameter with real tables.

For example, something like

create or replace TYPE MYTYPE AS OBJECT (....);
CREATE OR REPLACE TYPE LIST_OF_MYTYPE AS TABLE OF MYTYPE;

CREATE PROCEDURE FOO(my_table LIST_OF_MYTYPE ,....) AS
  CURSOR cur_read_data IS
  SELECT a.col1, b.col2, b.col3
  FROM
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id
  ;

BEGIN
     FOR record_info in cur_read_data
     LOOP
        ......
     END LOOP;
END;

It works, but I have a performance issue. Collection parameter passed to the procedure doesn't have many elements; even if it has just one element, execution plan involves full table scan of existing_table.

In case of 1 element, changing INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id) to INNER JOIN existing_table b ON (b.existing_table_id =FOO.my_table(1).existing_table_id ) makes a huge difference - the query uses "INDEX UNIQUE SCAN" as I expected it for the initial query. I have tried even query hints (ordered, leading) with no results...

Even though the number of elements in collection in my application is between 1 and 5, and it's possible to write 5 different versions of one procedure, I wonder if it's possible to make it work as expected.

For testing, I also did

CURSOR cur_read_data IS
  SELECT a.col1, b.col2, b.col3
  FROM
   (
     SELECT 1 as existing_table_id, 'test 1' as col1 FROM DUAL
      UNION
     SELECT 2 as existing_table_id, 'test 2' as col1 FROM DUAL
      UNION
     SELECT 3 as existing_table_id, 'test 3' as col1 FROM DUAL
   )
  a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id

That way it also works as expected, INDEX_UNIQUE_SCAN, not full scan...

Thanks for your answers.

UPDATE
Quite surprisingly, but rewriting it to

CURSOR cur_read_data IS
  WITH CTE1 AS (SELECT * FROM TABLE(FOO.my_table))
  SELECT a.col1, b.col2, b.col3
  FROM
  CTE1 a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id
  AND b.existing_table_id IN (SELECT existing_table_id FROM CTE1))
  ORDER BY a.existing_table_id

reduced cost about 30 times (1200 original version vs 38 with WITH) which is
 acceptable, but I still have no idea why that helped...

Another update

Analyzing V$SQLSTATS reveals that for the first version of the cursor it avoids DISK READS at any cost (0), DIRECT WRITES is also 0; using WITH somehow changes execution plan resulting in huge improvement in CPU TIME which outweighs increased DISK READS (1)...

One of the problems with using collections in SQL is that the optimizer isn't able to guess how many elements the collection has.  It defaults to assuming that the collection has a few thousand elements (I want to say 4k elements but I wouldn't wager on that).  If that is roughly 1,000 times more elements than you have in your actual collection, that's will certainly tend to cause the optimizer to make poor choices about the query plan.

The best way to alleviate that problem is to use the CARDINALITY hint.

SELECT /*+ cardinality(a 4) */ a.col1, b.col2, b.col3
  FROM
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id

tells the optimizer to assume that the collection aliased to a has only 4 elements which should cause it to pick a more appropriate query plan.


MySQL verify backups There's standalone MySQL server 5.5 with no slaves. Daily dump is created using mydumper tool.

Is there any way to verify created dump (all tables are created, integrity, etc.) without restoring dump somewhere, using slaves, etc..?

This is one of the best posts I've seen on this (regular enough here) topic. Seems to be the quickest way to test a backup.

What you've got to remember is that no process is 100% cast-iron guaranteed to restore your databases - there's the thorny issue of money.

Basically, every extra 9 (3, 4, 5 nines) that you put on to your procedures will (my own rule of thumb here) put the price of your backup and restore procedures up by a 0 - i.e. a factor of 10! Are you going to be checking every backup you make? You can follow industry best practice and make it crystal clear that that is what you are doing (to your bosses/shareholders/stakeholders) and that if they want more guarantees, they'll have to pay and pay lots!

You can talk about clusters, master-slave replication, master-master, geo-replication, RAID10 SSD's - the number of solutions for HA MySQL is huge, but the prices get huge pretty fast also!

Best practice is testing backups ~ 6 weeks - a role for a junior DBA - you should take a full backup once a week with incremental backups every night when traffic is low (for MySQL I strongly recommend Percona's Open Source XtraBackup).

Motto of the story, formulate a backup and restore plan with the other stakeholders (explaining the pros and cons - risk, cost &c.) and follow industry best practice and you won't go far wrong!


Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table I'm using a PostgreSQL table to link taxonomic names with the respective taxon concept as follows:

create type name_status as enum ('accepted','basionym','synonym');
create table temp (
  concept_id integer not null,
  name_id integer not null,
  status name_status,
  primary key (name_id),
  unique (name_id,concept_id)
);
insert into temp values
  (101,1,'accepted'),
  (101,2,'basionym'),
  (101,3,'synonym'),
  (101,4,'synonym'),
  (102,5,'accepted'),
  (102,6,'synonym');

The following conditions have to be fulfilled by this table:

The value 'accepted' have to appear once and only once per 'concept_id'.
The value 'basionym' may not appear in each 'concept_id', but if occurring, then only once per 'concept_id'.
The value 'synonym' may occur many times or none per 'concept_id'.

Is there a way to define such constraints without the necessity of inserting a new table in the database?

I think you can solve this with two UNIQUE partial indexes:

CREATE UNIQUE INDEX concept_status_accepted_upx
ON temp (concept_id)
WHERE status = 'accepted' ;

CREATE UNIQUE INDEX concept_status_basionym_upx
ON temp (concept_id)
WHERE status = 'basionym' ;

This will enforce that there is only one 'accepted' per concept and only one 'basionym' per conecpt.

Not sure if you want the extra but tt will NOT enforce that every concept has an 'accepted' taxonomy. This is much harder to do with DRI and the restriction of a single table.


missing database from datname running select datname FROM pg_database gives me a partial list. I'm missing there the db aa.
How is that be? How can I sort it?
all=# \dt aa.*
                List of relations
 schema |   name     | type  | owner
--------+------------+-------+--------
 aa     | aamember   | table | dkuser
 aa     | aaspmember | table | dkuser
 aa     | aamaster   | table | dkuser
(3 rows)

all=#
all=# select datname FROM pg_database;
   datname
--------------
 all
 dev
 padb_harvest
 template1
 template0
(5 rows)

SVV_TABLE_INFO is the table I was looking for!


Storing CREATE EXTERNAL DATA SOURCE in a SSDT project without exposing the DATABASE SCOPED CREDENTIAL or MASTER KEY secret; I have a SSDT project that deploys to Azure SQL DB. Recently I added an external table. This required an external data source as follows:

CREATE EXTERNAL DATA SOURCE [data_warehouse]
    WITH (
    TYPE = RDBMS,
    LOCATION = N'mydb.database.windows.net',
    DATABASE_NAME = N'MainDW',
    CREDENTIAL = [dw_reader]
    );

When I import this into my .sqlproj, it complains the credential isn't there, and then when I add that it complains the master key isn't there. The problems is I don't want to store those secrets in the SSDT project. How do I store dummys, or defaults that can be overridden and retrieved from the azure keyvault or other method of storing secrets in Azure Decops?

After experimenting, it seems like any kind of secret in SSDT only gets used when the object is created, not updated.

Therefore if a sqlproj contains CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password A';, and no master key exists, then the master key will be created with that password. However, if one later executes ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'; on the database, and then redeploys the sqlproj to the database, it will not regenerate the master key.


PostgreSQL - insert/update violates foreign key constraints I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't insert data into the Table3. See the code below :

DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
  "DataID" bigint NOT NULL DEFAULT '0',
  "AdData" integer DEFAULT NULL,
  PRIMARY KEY ("DataID")
);

DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
  "Address" numeric(20) NOT NULL DEFAULT '0',
  "Value" numeric(20) DEFAULT NULL,
  PRIMARY KEY ("Address")
);

DROP TABLE IF EXISTS Table3 CASCADE;
CREATE TABLE table3 (
  "ID" bigint NOT NULL DEFAULT '0',
  "DataID" bigint DEFAULT NULL,
  "Address" numeric(20) DEFAULT NULL,
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

  ERROR:  insert or update on table "Table3" violates foreign key
  constraint "Table3_DataID_fkey" DETAIL:  Key (DataID)=(27856) is not
  present in table "Table1".

When I tried to insert data into the 3 tables, an error occured. I referred the postgreSQL documentation and changed my code as follows: (Unfortunately it showed another error)

DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
  "DataID" bigint NOT NULL DEFAULT '0',
  "AdData" integer DEFAULT NULL,
  PRIMARY KEY ("DataID")
);

DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
  "Address" numeric(20) NOT NULL DEFAULT '0',
  "Value" numeric(20) DEFAULT NULL,
  PRIMARY KEY ("Address")
);

DROP TABLE IF EXISTS Table3 CASCADE;
CREATE TABLE table3 (
  "ID" bigint NOT NULL DEFAULT '0',
  "DataID" bigint DEFAULT NULL REFERENCES Table1 ON DELETE RESTRICT,
  "Address" numeric(20) DEFAULT NULL REFERENCES Table2 ON DELETE CASCADE,
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),
   PRIMARY KEY("DataID", "Address")
);

  ERROR:  multiple primary keys for table "Table3" are not allowed LINE
  65:   PRIMARY KEY("DataID", "Address")

Please help me... How I can create the reference?

I changed the ID as UNIQUE and removed the line PRIMARY KEY ("ID"). At that time it shows another error like :

  ERROR:  duplicate key value violates unique constraint "Table3_pkey"

There are a few problems with your tables.  I'll try to address the foreign keys first, since you question asked about them :)

But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same.  Of course, the definition of Table3 in your second attempt has syntax and logical errors, but the basic idea is:

CREATE TABLE table3 (
  "ID" bigint NOT NULL DEFAULT '0',
  "DataID" bigint DEFAULT NULL,
  "Address" numeric(20) DEFAULT NULL,
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

This definition tell PostgreSQL roughly the following: "Create a table with four columns, one will be the primary key (PK), the others can be NULL.  If a new row is inserted, check DataID and Address: if they contain a non-NULL value (say 27856), then check Table1 for DataID˙and Table2 for Address.  If there is no such value in those tables, then return an error."  This last point which you've seen first:

ERROR: insert or update on table "Table3" violates foreign key constraint
    "Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".

So simple: if there is no row in Table1 where DataID = 27856, then you can't insert that row into Table3.

If you need that row, you should first insert a row into Table1 with DataID = 27856, and only then try to insert into Table3.  If this seems to you not what you want, please describe in a few sentences what you want to achieve, and we can help with a good design.

And now about the other problems.

You define your PKs as

CREATE all_your_tables (
    first_column NOT NULL DEFAULT '0',
    [...]
    PRIMARY KEY ("ID"),

A primary key means that all the items in it are different from each other, that is, the values are UNIQUE.  If you give a static DEFAULT (like '0') to a UNIQUE column, you will experience bad surprises all the time.  This is what you got in your third error message.

Furthermore, '0' means a text string, but not a number (bigint or numeric in your case).  Use simply 0 instead (or don't use it at all, as I written above).

And a last point (I may be wrong here): in Table2, your Address field is set to numeric(20).  At the same time, it is the PK of the table.  The column name and the data type suggests that this address can change in the future.  If this is true, than it is a very bad choice for a PK.  Think about the following scenario: you have an address '1234567890454', which has a child in Table3 like

ID        DataID           Address             Data
123       3216547          1234567890454       654897564134569

Now that address happens to change to something other.  How do you make your child row in Table3 follow its parent to the new address?  (There are solutions for this, but can cause much confusion.)  If this is your case, add an ID column to your table, which will not contain any information from the real world, it will simply serve as an identification value (that is, ID) for an address.


What does WE8DEC mean? It is one of the commonly used character sets. What does it stand for?

Is it an acronym for something?

WE8DEC is the old DEC MCS character set which stood for Digital Equipment Corporation Multinational Character Set.  The WE8 prefix identifies this as a Western European 8-bit character set.


how to create and insert into a table in an anonymous block I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table.

When the block compiles, it gives me an ORA-00942: table or view does not exist error.

Well, no, it doesn't exist, but it will be created before the insert occurs.  If I create the table before compiling, it will work.

How can I handle this?

Generally, it doesn't make sense to create tables in anonymous blocks let alone to then insert data into those tables in the same anonymous block.  Creating new objects at runtime is a bad idea (hopefully you're not trying to create a temporary table like you would with other databases).

If you really do need to create a new table at runtime, you can insert data in the same anonymous block only by using dynamic SQL for the INSERT operation as well.  You would need to use dynamic SQL every time you referenced the newly created table in the same anonymous block.  You could also create the table in one anonymous PL/SQL block and then insert the data in a second anonymous PL/SQL block.  That would not require that the INSERT statement use dynamic SQL.

Since it sounds from the comments like you are trying to create a temporary table like you would in other databases, you probably want to take a look at this StackOverflow thread that discusses alternatives to creating temporary tables in Oracle.


AWS - MySQL server "Service CPU Usage" stuck at 50% / being throttled? I have a c4.large EC2 instance which is running Windows Server 2012 and MySQL.

When I look at the CPU usage on the machine it seems that MySQL is capping out at 50% CPU usage when the system is under load, it also looks like this is relative to some kind of throttling but I am not sure why this is happening.

The c4.large instance comes with 2 vCPUs and dedicated ECU and I can see from the screenshot below that these are both being used but maybe the combined total of this is being capped?

I am not sure if this is a MySQL issue or relative to Amazon Web Services like a potential systems issue or if it's some kind of misconfiguration on my side.

Does anyone have any idea why this is happening?

This is probably neither throttling nor a misconfiguration, nor anything related to AWS. Consider these points:

A c4.large instance has 2 cores.
Any given query running in MySQL runs entirely within a single thread, and
a single thread within a process can only run on 1 core at any moment in time (though presumably the Windows process scheduler can move it between the cores at will), and
100% utilization of 1 core by 1 thread would show up as 50% CPU utilization.

What you appear to have here, rather than the server really being "under load," is a single CPU-bound query running.

Any user with the PROCESS privilege can run the SHOW FULL PROCESSLIST; query in MySQL and see what all the session (connection) threads in MySQL Server are doing at the moment.  Run this and you should find your CPU hog.  If you run another instance of the same query, you should find that the CPU utilization hits 100% with two CPU expensive queries running concurrently.  (Then you'll want to fix/optimize that query.) The TIME column tells you how long the connection has been in its current state.


What is the proper way to store a full table of data outside of a database? It needs to be able to be restored as well I have a large SQL Server 2005 database that hasn't had the best maintenance over the years (I just took it over recently).  There are around 100 tables that can be completely removed from the database, as well as some that just need data after a certain point removed.

My employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises.

The smaller tables could of course be exported as Excel files, but many are larger than xls or xlsx would allow.  I also thought of storing the data as .txt, .sql or .csv files, but I want to be certain that I'm doing this properly and won't end up with corrupted tables that can't be restored.

As far as querying the tables for the data, I can handle that. So my main question is, how do you properly store a table of data outside of a database?

Thanks,
Jamie

My employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises.

If you want the data to be restored later, then best is to

Script out schema of the tables that you wish to drop and save it as a sql script.
BCP out the data (without using -n switch as -n is for native format which is not HUMAN readable) and store it in a safe place. If you want to save some space, you can also ZIP it.

  how do you properly store a table of data outside of a database?

If you want to really store the data outside of database, then option 1 and 2 are the ones that I will go for.

Now, what I will still do is -

Make a copy of the current database (using backup / restore method).
Restore it with a database_Archive name
Remove the tables that you don't need to archive i.e. Keep only tables that you will be eventually dropping from the main database (as per your Boss's requirement).
Then do a one time shrink (since you will be getting rid of lot of data)
Backup the final archive database.

The benefit of this is that

whenever you need to archive the data, it will be easy.
You dont have to manage individual files.
the database will be backed up, just in case you need it for a later time.
You can query the archive database anytime you need.

Check out the hyperlinks as it has method, scripts and best practice to help your out.


Usage of ExecSQL in UDF? Problem:
is it possible to use sp_executesql or EXEC inside of a user defined function?

Thanks!

Not in a T-SQL function, but executing stored procedures can be done in SQLCLR functions.

An example of using a .NET / C# SQLCLR user-defined function to execute a stored procedure is shown in the following article (which I wrote):

Stairway to SQLCLR Level 2: Sample Stored Procedure and Function


SQL Server - Create Additional Database File for New Schema I'm trying to create a change script for some upcoming changes. We're adding a new schema to the database and our client wants the new schema to make use of a new database file.

How do I, in T-SQL, create the new file and assign the new objects within the schema to use the new file?

We're using SQL Sever 2012.

You can create a new filegroup, make it DEFUALT, create all schema objects and switch DEFAULT filegroup back. If at any later point you would need to add objects to that schema, you can explicitly specify filegroup through ON filegroup clause for each object in create statement or temporarily switch DEFUALT filegroup.

Alternatively you can explicitly specify ON filegroup for each object of this client's schema.


What effect will reducing the size of a varchar column have on the database file? We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes down to more reasonable levels. The 'how' to do this I understand: my question is what will altering these columns do to the pages and extants on disk? (There's lot of info out there about what happens when you grow a column, but having trouble finding info on what happens when you shrink one.)

Some of the tables have a very small row count, so I'm not worried about the cost of the change, but some are quite large, and I'm worried about them potentially being reorganized and causing a lot of blocking/downtime. In practical terms, I just want a way to estimate a maintenance window. In general, I'd like to understand better how the database engine behaves in this case.

Thanks in advance!

EDIT:

I have 20 tables I'm looking at, though only half of them have row counts greater than 1,000. The biggest has almost a million rows. The worst offender is a table with 350,000 rows and four VARCHAR(MAX) columns that can shrunk to the VARCHAR(500) level.

First things first: How much data is there in the table? Number of rows and size of the table?

Second: Can you back up and restore this table to a test server and run the alter statement to see the impact (assuming it is not unfeasible due to the table being too large to fit on a non-Production system)? I always find that testing in my environment is more accurate than advice from the interwebs since there are several factors that can influence the outcome that might not be provided in the question simply due to not knowing that those factors could affect the outcome.

Third: increasing the size of a variable-length field is (assuming you don't go over the 8060 byte limit) a simple meta-data operation since no actual data would be changing for such an operation. BUT, on the other hand, reducing the size of a variable-length field, even to something that will more than obviously work, is not a simple meta-data change because SQL Server doesn't know, prior to scanning all of the rows, that the newly requested size is valid.

Hence: Yes, this will lock the table for a period of time. How much time? Well, here is the test that I just did:

I had, from some other testing, a table with a single INT NOT NULL field and 1 million rows. I copied it to a new table for the purpose of doing this test via:

SELECT *, CONVERT(NVARCHAR(MAX), NEWID()) AS [StringField]
INTO dbo.ResizeTest
FROM dbo.ClusteredUnique;

This way I was starting with a similar scenario of having a MAX field (I just realized that you have VARCHAR and I am using NVARCHAR, but that shouldn't alter the behavior I am seeing) that I could then change to 500. And it has data in it that can easily fit within 500 characters. That took a few minutes.

I then ran:

ALTER TABLE dbo.ResizeTest ALTER COLUMN [StringField] NVARCHAR(500) NULL;

And that took just over 11 minutes.

I just re-ran the test again, this time dropping the [ResizeTest] table and changing both NVARCHARs to be just VARCHAR, just to be super-sure that I am comparing apples to something that at least looks like an apple ;-).

The initial table creation took 20 seconds while the ALTER TABLE took 2 minutes.

So, in terms of estimating downtime, that is really hard to do as it is based on disk I/O speeds, whether or not any auto-growth operations need to happen on the data file and/or the transaction log, etc. That is probably a large part of why my first test took 11 minutes to alter and the second, even with VARCHAR being half the size of the NVARCHAR data, took only 2 minutes (i.e. the files were pre-grown at that point). But still, you should keep in mind that my test is running on my laptop which is not the fastest disk, but it was also just 1 million rows of 2 small columns (22 or so bytes per row).

And since you asked what will it do to the data pages, here is your answer. I did an sp_spaceused after creating the table, after doing the ALTER COLUMN, and after doing ALTER TABLE dbo.ResizeTest REBUILD;. The results (the following numbers are based on the second test using VARCHAR, not the first test using NVARCHAR):

After initial table creation:        526,344 KB
After ALTER COLUMN VARCHAR(500):   1,031,688 KB  <--- !! Yikes!!
After ALTER REBUILD:                 526,472 KB

If you are concerned about needing to keep the operation to the shortest time possible, check out an article that I wrote about doing just that: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY! (free registration required).


Postgres 9.4, replication slots, doesn't work failover master I've installed postgres master and slave nodes by this manual:

At the master (10.0.0.1):

sudo -u postgres initdb -D /data/postgres/main

Change postgresql.conf:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 2
logging_collector = on
max_wal_senders = 5

Add record for replication to pg_hba.conf:

host replication replica 10.0.0.2/32 md5

Then start postgres

service postgresql start

Create replication user:

CREATE USER replica WITH REPLICATION ENCRYPTED PASSWORD '123456';

And add replication slot:

SELECT pg_create_physical_replication_slot('standby_slot');

At the slave (10.0.0.2):

pg_basebackup -h 10.0.0.1 -U replica -D /db/postgres/main -X s -P

Change state to standby in postgresql.conf:

hot_standby = on

Add recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.1 port=5432 user=replica password=123456'
primary_slot_name = 'standby_slot'
trigger_file = 'trigger'

Start standby:

service postgresql start

So, replication is working fine. If I create database, it will appear both at the master and the slave.

If slave is going down and then starting, all changes come there. It all fine too.

But I have problem if master is down. When it stops, I create trigger file at the slave. Then I create another database at the slave, which now is primary. After that I start master, trigger file remove automatically, recovery.conf rename to recovery.done automatically too. Ok. But how to synchronize changes from slave (new primary) to master (old primary)?

I found that I've got to do these four steps:

At the master:

Stop postgres:

service postgresql stop

At the slave:

Switch backup mode on:

SELECT pg_backup_start('backup', true);

Then rsync new data from slave to master.

And then switch backup mode off:

SELECT pg_backup_stop();

After last step I get message

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

And then if I start master it doesn't start with log message:

FATAL: could not locate required checkpoint record

I don't understand, how to get new data at the master and make it primary again.

So at the future I plan to install pgpool2 for balancing and failover, but test installing did not work because I don't know, how this mechanism should work.

You cannot just jump back to the old master, as it is said here:

  Once failover to the standby occurs, there is only a single server in
  operation. This is known as a degenerate state. The former standby is
  now the primary, but the former primary is down and might stay down.
  To return to normal operation, a standby server must be recreated,
  either on the former primary system when it comes up, or on a third,
  possibly new, system.

For this, you need a recovery.conf on the old master.  This should be nearly the same as the present one, only the connection has to point elsewhere.


Designing a database for Reading Comprehension So I am building an RC app that will require me to fetch data.
I have data in Excel sheet and I would like to load the data in sqlite database. But I am having trouble designing the db.

A little background on the raw_data:

Passage Sheet
P_id,Passage

Questions Sheet
P_id, Q1,Q2,Q3....Q10

Options Sheet
P_id, ?????

Now here in the options sheet I am confused because I have like 200 passages and each passage has around 6-10 questions.

All questions are multiple choice with 5 options.

Can anybody help me in organising my questions_sheet and options_sheet so that I can create a database.

1st things first:

Use TEXT for all character fields including small ones! In SQLite, VARCHAR is just TEXT behind the scenes anyway (same is true for PostgreSQL if ever you want to upgrade to a multi-user RDBMS).

Note on style (others may differ - but I know best! :-) ):

Use lower case for your identifiers (table and column names) and UPPER CASE for your SQL KEYWORDS (SELECT, FROM, ORDER BY... &c.). Use underscores_to_separate_identifier_parts! Use singular names for identifiers - a table is a set anyway, so why pluralise it? If you have a problem with any of this, there are plenty of style guides (this one is good) out there, but if you choose a convention, then stick to it! This will make life much easier. Finally, make use of the comments facility - in SQLite, it's not great, but it's there! It's easier to just issue a CLI client command or SQL statement than go searching for docco!

This schema should get you started (you could add fields to these tables as appropriate for  your particular situation).

The Passage table:

CREATE TABLE passage
(
  passage_id INTEGER PRIMARY KEY, -- this will automatically auto increment
  passage_text TEXT NOT NULL, -- could check for a minimum length here!
  source_name TEXT NOT NULL, --
  ISBN TEXT, -- note, no NOT NULL - a URL doesn't have an ISBN for example
  author TEXT -- maybe author not known or it's an institutional website?
  passage_date -- if known/applicable.. other stuff...
);

If you have multiple authors, you could have an author table and then a passage_author table as an associative entity (or joing table - there are other names - see Wiki).

Question (each one refers to its own passage - that's what the FOREIGN KEYs are for!

CREATE TABLE question -- or quiz, but I'm keeping it singular!
(
  question_id INTEGER PRIMARY KEY,
  q_passage_id INTEGER NOT NULL,
  question_text TEXT,
  CONSTRAINT question_passage_fk FOREIGN KEY (q_passage_id)
    REFERENCES passage (passage_id) -- meaningful names are very helpful in error messages!
);

Replies to the questions (each option refers to its own question and passage):

CREATE TABLE option
(
  option_id INTEGER PRIMARY KEY,
  o_passage_id INTEGER NOT NULL,
  o_question_id INTEGER NOT NULL,
  option_number TINYINT, -- CHECK here for < 10?
  option_text TEXT NOT NULL,
  CONSTRAINT option_passage_fk FOREIGN KEY (o_passage_id)
    REFERENCES passage (passage_id),
  CONSTRAINT option_question_fk FOREIGN KEY (o_question_id)
    REFERENCES question (question_id),
);

All this is available here on a fiddle!