SQLPLUS Default Column Widths For almost all my databases, when I run a SELECT statement, the length of the column displayed is sized to the width of the data being queried.  However, I have two databases (Dev and Prod for the same application) that seem to default all column sizes to either 32 characters wide.  If the data is wider than 32 characters or a column alias is used, it defaults the column to 128 characters wide.

Here are some examples from one of many good systems:

>select '1234567890' XX_Identifier from dual;

XX_IDENTIF
----------
1234567890

>select '12345678901234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------
12345678901234567890

Here are some examples from one of the bad systems:

>select '1234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------------------
1234567890

>select '12345678901234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------------------
12345678901234567890

I've checked the sqlplus settings and they are all the same.  I didn't see any database parameters that looked out of the ordinary and they both have identical NLS parameters.  Both databases use the same character set.

I realize I could define a format for every column being queried, but this should not be necessary as it is not on any other DB.

The database having this problem is 11.2.0.1, while all our others are 11.2.0.4, so perhaps there is a bug that I haven't been able to find.  The sqlplus client I am using is 12.1.0.1 in both cases.

Update:
The symptoms don't match exactly, but Doc ID 330717.1 talks about column widths in sqlplus and basically says you can't guarantee anything without setting the format.  I can accept this, but since the behavior is consistent, it seems like a cause can be determined.

I found the issue.  It is documented an oracle note (Doc ID 1086519.1).  I used the first workaround listed which is to set CURSOR_SHARING to exact.

ALTER SESSION SET CURSOR_SHARING=EXACT;


The best practice for SQL + NoSQL database design I’m in process of designing database architecture for my diploma project and I have second thoughts about different solutions.

I know and understand all my objectives can be done within  relational database, but having in mind thousands of users using the app, where data changes dynamically, create the question: should I proceed with both sql and nosql database in parallel?

So the app is based on relational db (sql server), but there are parts of the software where user can change data very dynamically: notes, handbook, kanban board, invoicing. What I worry more about than only changing the data, is creating new and deleting old (in that case there is no need to store duplicates of notes or revisions of invoices) and not ending up having tables with millions of records.

Additional question: is it okay to store different users fragile data (like notes) in one table, based on FK(user_id)?

I would love to hear your comments on it based on your experience. I tried with good practices on google but with no big luck.

Please advise or provide good book title for that matter.

From comments:

This question is incredibly open-ended and has an entire branch of the industry devoted to it, namely data-architects and program-architects. The best thing to do is to pick ONE concept and stick with it. Thousands of users is a low-threshold for performance, so any solution will work. Are you expected to present a design or an implementation? Any database system can implement almost any sort of ERD, so just focus on a solid ERD.
There are plenty of options on what should happen here, and there are professional patterns that you may miss in your design (knowing how to build appropriate M:N relations based on experience) or things like audit tables that you may not be considering (we do a lot of this sort of thing post-academia, and know a lot of questions to ask stakeholders to ensure we build the right things). Just do the best work you can, and work with your advisor to know if it's enough for your diploma project. - jcolebrand

And one other suggestion from another comment:

You might like to try using PostgreSQL which has both classic RDBMS and a Document Store capability within the same database - indeed, you can join between classic tables and JSONB documents. Just a thought! – Vérace


What is a parse tree object? I have looked into sys.syscachedobjects table. I found some records have an object type of parsetree.

Can anyone tell me in detail what a parsetree is? Is this a data structure used by SQL Server, and can it help simplify my job as a DBA?

The breakdown of your code by by SQL server is checked by the engine for any issues.  If it does not find them it generates a parse tree and passes it to the algebrizer.  It is another way to represent the query.


When are procedural queries absolutely necessary? I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not give you the results?

I understand the difference between the two, I just have never come to a situation where I need to use a cursor.  I'm wondering if there are such situations.

In my experiences, I've run into a few times when procedural/iterative approaches were warranted.

API only allows for single-row operation

If I wanted to programmatically change the data type from real to decimal in a table that has 500 mis-typed columns like this SO question asks, a cursor is a fine approach as the DDL does not allow for altering multiple columns in a single statement.

Set based doesn't scale

If you have the SQL Server MVP Deep Dives book, chapter 4 "Set-based iteration: the third alternative" by Hugo Kornelis has some great use cases for combined cursor/set based operations. Two of classic problems that the chapter author references is the Running Totals and Bin Packing.

I used the set-based iteration approach with good success for a poorly designed process I inherited at the last job. In short, there was a process that once a year had to update 50-75M rows and attempting to do so in a single set would blow our logs. By chunking the updates into smaller batches of N rows, it allowed the log to keep up and actually finished faster than the previous year when they just allocated a metric ton more disk space.


How can I reference column aliases from a projection inside the projection? Calculations on the projection (select) values are  useful and common. Yet column aliases declared in the projection can't be used there.

select sellprice as x, cost as y, x/y as markup
from mytable

gives an error.

I can accomplish this with

select sellprice as x, cost as y, sellprice/cost as markup
from mytable

But that is a simple example: We usually declare column aliases names with as for complex cases. For example, instead of cost, I might have some  function built from average etc,  that I declare as a column alias.

How can I use these aliases inside the projection clause?

The usual way to cope with that is to use a derived table (aka sub-query):

select x, y, x/y as markup
from (
  select sellprice as x, cost as y
  from mytable
) as t;


How to calculate the sums of two columns, when that two columns brought by join queries? I ran two queries as shown bellow;

query 1: queried with JobRequiredProducts table
and Product table to list the jobId 1 required all product and unit prices

SELECT  j.jobId,
        j.productId,
        p.productUnitPrice
FROM    JobRequiredProducts J
JOIN    Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
ORDER BY j.jobId

query 2: queried with JobRequiredLabours table
and Labour table to list the jobId 1 required labours and worked hours

SELECT  j.jobId,
        j.labourId,
        j.hoursOfWork
FROM    JobRequiredLabours J
JOIN    Labour L
  ON    l.labourId = j.labourId
WHERE   j.jobId = 1
ORDER BY j.jobId

And I got bellowing results:

Query result:

 jobId | productId | productUnitPrice
__________________________
   1   |     4     | 1500
   1   |     5     | 800
   1   |     6     | 1200

 jobId | labourId | hoursOfWork
______________________________
   1   |     1     | 6
   1   |     2     | 8

Now I want to calculate the total job cost by total (productUnitPrices) + (hoursOfWork * 30). And display the total cost separately. Please help me to do it. Note: 30 added to multiply because of hourly rate is 30

What you are primarily looking for is a SUM command run on your queries where you will GROUP BY jobId.  That will allow you to get the SUM of your hours worked as well as the cost of the parts.

I can see two options that you can utilize.  Now there may be some variables I am missing but this should give you a framework to work with.

Basically what you need to do is get the SUM separated out which you should and then the math is fairly easy after that.  You are able to get SUM value by using the GROUP BY clause in a SELECT query.

Sub Queries As "Tables"

With this approach you create a sort of "table" which has your SUM pre calculated which is then added in the main SELECT.  (I am assuming you have some sort of a JOB table which is where the jobId column comes from).

SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
    LEFT OUTER JOIN
    (
        SELECT  j.jobId,
        SUM(p.productUnitPrice) AS SumOfProductUnitPrice --This becomes the name of this "column" in the main SELECT statement
        FROM    JobRequiredProducts J
        JOIN    Product p
          ON    p.productId = j.productId
        GROUP BY J.JobID
    ) A
        ON A.JobID = J.JobID
    LEFT OUTER JOIN
    (
        SELECT  j.jobId,
        SUM(j.hoursOfWork) AS SumOfHoursWorked --This becomes the name of this "column" in the main SELECT statement
        FROM    JobRequiredLabours J
        JOIN    Labour L
          ON    l.labourId = j.labourId
        GROUP BY j.jobId
    ) B
        ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE Command can change to return the same information for a different jobID

CTE's (Common Table Expression)

An alternative that also works is using a CTE (a Common Table Expression) which is almost like a VIEW that exists only for this query.  It allows you to pre-define a query and utilize it later in a different command.  In this particular instance we are using it to pre-generate the SUM so we can then use it later in a main SELECT.  The benefit of this is the logic of how the CTE's are created is not in the main SELECT which helps with readability.

--All CTE's need to be precided with a ";" if it is not the first statement in the batch
;
WITH ProductSum (JobID, SumOfProductUnitPrice)
AS
(
    SELECT  j.jobId,
    SUM(p.productUnitPrice) AS SumOfProductUnitPrice
    FROM    JobRequiredProducts J
    JOIN    Product p
      ON    p.productId = j.productId
    GROUP BY J.JobID
), --Comma seaparates the two CTE's that could be used in the next statement
HoursWorkSum (JobID, SumOfHoursWorked)
(
    SELECT  j.jobId,
    SUM(j.hoursOfWork) AS SumOfHoursWorked
    FROM    JobRequiredLabours J
    JOIN    Labour L
      ON    l.labourId = j.labourId
    GROUP BY j.jobId
) --No comma follows the last CTE in the chain

SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
    LEFT OUTER JOIN ProductSum A
        ON A.JobID = J.JobID
    LEFT OUTER JOIN HoursWorkSum B
        ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE command can change to return the same data for a different jobId

These queries will get slightly more complicated, if a job requires more than one instance of a single part AND if that is held through some kind of QUANTITY COLUMN or if the Hourly Rate for a given person is not 30 every time.  But hopefully this will give you something to work with as a start.


postgres jsonb return specific paths of json object Consider the following jsonb object:

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "startTime": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "startTime": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

I would like to query the object, to return only the following jsonb object, given the folloiwng paths:

track.segments.hr

{
  "track": {
    "segments": [
      {
        "HR": 73
      },
      {
        "HR": 135
      }
    ]
  }
}

How can i formulate the query, to retrieve this kind of data from the json, and still return the json object, with only the specified paths?

You almost certainly can't.

JSON doesn't [yet] have the equivalent of XML's XSLTransform, which could do what you describe, but [still] only after retrieving the entire blob of XML to work with.

Databases are really good at finding little bits of stuff and putting them together; they're far less good and taking large blobs of stuff and pulling them apart.

I assume what you're showing is a simplification but what you've shown could be done easily with ordinary, relational tables.


Import data from one Database to another script As part of a data migration I would like to script a data transfer from the existing to a new database.
I used to do this with DTS on older versions of MS SQL. I am currently using SQL 2012.
Note the tables have different some different fields.
It would be nice if the import/export data tool had a save script option?

Another option that could handle this and possibly allow for a bit more flexibility is to use a SQLCLR function or stored procedure that makes use of the SqlBulkCopy class (which is essentially the same framework used by BCP.EXE, BULK INSERT, etc). While writing such a function/procedure might not be worth the time/effort for a one-off project, there is fortunately such a stored procedure that already exists and is downloadable for free. It is called DB_BulkCopy and is part of the SQL# SQLCLR library (which I am the author of, but again, this stored procedure is in the Free version). It allows you to run any random query (that returns a result set, of course) and send the results to the local instance or a remote one (even to Oracle if need be). It also allows for:

mapping columns if the destination has different names and/or ordering of the fields.
setting a particular batch size.
specifying the following options: KeepIdentity, CheckConstraints, TableLock (allows the operation to happen in Bulk Logged mode), KeepNulls, FireTriggers, and UseInternalTransaction.
optionally reporting the total number of rows migrated.
executing a stored procedure as the source of the data instead of using a SELECT

I am mentioning this because, as compared to running an external package, it is very easy to:

update the parameters being sent to a stored procedure
call a stored procedure in an ad-hoc fashion, or as part of a larger migration script, or scheduled from SQL Agent, etc.
call a stored procedure in a loop to send multiple tables

Example:

EXEC SQL#.DB_BulkCopy
    @SourceQuery = N'SELECT t1.field1, t1.field5, t2.field12
                     FROM   dbo.Table1 t1
                     INNER JOIN dbo.Table2 t2
                             ON t2.FKfield = t1.PKfield
                     WHERe   t2.field8 > something;
                    ',
    @DestinationConnection =
        N'Server=ServerName\Instance; Trusted_Connection=true; Database=NewDB;',
    @DestinationTableName = N'MyNewTable',
    @BatchSize = 2000,
    @NotifyAfterRows = 100, -- print message per every 100 rows
    @TimeOut = 600, -- 10 minutes
    @ColumnMappings = N'field1,NewFieldA|field5,NewFieldB|field12,NewFieldC',
    @BulkCopyOptionsList = N'KeepIdentity|TableLock|KeepNulls';


What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field? My database is extremely large and growing at a rate of ~20m rows/day.  I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month comparisons.  Time is displayed in the result sets occasionally but never used as a criteria.  Given this, I'm thinking that I'd save considerable storage space with an index on date alone vs a combined datetime field.  I'm not sure if I would also see performance gains in my selects or if there are any disadvantages to splitting into 2 fields.

For reporting purposes splitting the field out into date and time has some benefits.  Some possible benefits you could realise include:

You can make a date reference table (much the same as a date dimension in a data warehouse) with your breakdown into weeks, months etc.  This can be keyed on the date and used with a join.
Analysis by time of day is easier with a separate time field.  You can also round the time to an appropriate grain and make a reference table.
The index would be slightly narrower although each leaf row still has a (IIRC) 6 byte page reference, so it's not such a great saving overall.

For your application you might get a win from a date reference table (make a clustered PK on date for efficient lookup), which will probably be more efficient than de-normalising the week and month onto your large table.


SQL Server Logical IO Block Sizes and Physical Reads I am trying to understand cache churn caused by SQL statements that are doing some table scans.

Assuming LRU caches, if something is doing 400K logical IOs, how much memory is streamed across (IO block size * logical IOs).

Also, I am trying to understand how to take the Report on top Logical IO SQL statements and get the physical IO that these incurred.

Given that, my questions are:

What is the IO block size that a SQL Server logical IO operation refers to.
How do I get Physical IOs for a particular instance of SQL execution?

if something is doing 400K logical IOs, how much memory is streamed across (IO block size * logical IOs)

In SQL Server, 1 "logical I/O" refers to reading 1 8 KB page from the buffer pool (memory).  It's possible that a physical I/O will be incurred first (to get the page from disk to memory).  So 400K logical reads is 400,000 * 8 KB = 3,200,000 KB of data being read (~3 GB).

A reference for this, with a lot more detailed information, can be found on the Microsoft Docs site: Pages and Extents Architecture Guide

You mention block size, which I imagine is in reference to the OS level block size.  While this matters at the OS level, and can potentially impact SQL Server performance, it's not relevant to logical I/O in terms of the number of pages - which is fixed at 8KB.

  Also, I am trying to understand how to take the Report on top Logical IO SQL statements and get the physical IO that these incurred

You can find this in sys.dm_exec_query_stats.  This includes columns for:

max_logical_reads,
total_logical_reads,
last_logical_reads,
max_physical_reads,
total_physical_reads,
last_physical_reads

...etc.


How to force MySQL/MariaDB to show numbers in scientific notation? I have a local MariaDB server 5.5.37 where I have some numeric data in it stored as DOUBLE. I need to perform some queries on this data (things like AVG(...) or STD(...)), and I also have a MySQL server 5.1.73, where I have similar data stored using the same schema.

Here's the problem: when I run the same query on the MySQL server, I get results in scientific notation (e.g. 1.267e-13), while the same query on the MariaDB server gives 0.000000000001267. I'd like to be able to explicitly determine the display format, and in particular, I want the scientific notation.

The query is something like:

SELECT AVG(some_field), STD(some_field) FROM some_table GROUP BY other_field;

(actually much more complicated than that)

This happens with both the mysql command line client, and with MySQL Workbench.

Update:

I ended up defining my own function to convert from DOUBLE to a string in scientific notation, based on the answers below, which should work correctly for negative numbers:

drop function if exists sci;
DELIMITER $$
create function sci (num DOUBLE) returns text deterministic language sql
begin
  set @exp = IF(num=0, 0, FLOOR(LOG10(ABS(num))));
  return CONCAT(FORMAT(num*POWER(10,-@exp),6),'e',@exp);
end$$
DELIMITER ;

Yes, this seems a known issue, which probably wasn't never acknowledged much because after all, in most cases, a float is displayed/processed by the application code and not represented directly by mysqld/mysql client (and seems that workbench suffers from the same problem).

Sheeri from Mozilla seems to have discovered this problem before when running pt-table-checksum and shows a workaround for its run, but not a general solution. However, on a further discussion, she seems to provide a possible option, although with the opposite format that you want (converted into decimal instead of scientific notation.

I have just arranged a quick calculation to generate the scientific format out of a decimal number, change @n for your column name in your case:

SET @n := 0.00123;

SELECT
   IF(@n=0, 0,
       CONCAT(IF(@n<0, '-', ''),
              FORMAT(abs(@n)/pow(10, floor(log10(abs(@n)))), 6),
              'E',
              FORMAT(floor(log10(abs(@n))), 0)
             )
     );

EDIT: This is the function:

CREATE FUNCTION sci(n DOUBLE)
RETURNS char(12) DETERMINISTIC
RETURN IF(n=0, '0.000000E0',
          CONCAT(IF(n<0, '-', ''),
                 FORMAT(abs(n)/pow(10, floor(log10(abs(n)))), 6),
                 'E',
                 FORMAT(floor(log10(abs(n))), 0)
                )
         )


How to find records where specific values are different I have two conditions:

first has a value of Null
second has a value of XX

In the database there should Not be one without the other and the Rates should match.

I want to find all records where the Rates are different for the Null and XX records.

How can I do this in PL/SQL?

The database is a work db of procedure codes and I'm looking for a query that will find all procedure codes that have a modifier = null and modifier = xx where the rates are not equal. All fields are within the same table.

I'm thinking a nested type query but can't seem to figure it out.

Table name Procedure Code Price

Procedure Code = T2015

Modifier = Null.  With a Rate of $32.50

Modifier = XX.    With a Rate of $23.70

The table Procedure Code Price holds many CPT & HCPS codes. Each code can have multiple lines with different modifiers and rates. The correct entry for the example above would be the rates for Null & XX modifier are the same. What I want to find are the entries where the rates are different for these two modifiers.

In principle, the solution should probably be coded as a single SQL statement.  However, you could also use PL/SQL and a CURSOR FOR LOOP.  The question text does not give us any test data.  Please let us know if the following solution needs major adjustments (Oracle version: 12c).

create table a_test(
  first number
, second varchar2(64)
, rates number
);

-- insert some test data
-- first column: range 1-100, every third row: NULL
-- second column: all XX
-- third column: values between 0 and 9
begin
  for i in 1 .. 100
  loop
    insert into a_test values (i, 'XX', mod(i,10));
  end loop;
  update a_test
  set first = null
  where mod(first,3) = 0;
end;

The test table contains 100 rows. Use SQL first ...

-- query1: only the rows where first IS NULL -> 33 rows
select count(*)
from a_test
where first is null
  and second = 'XX'

-- query2: no duplicates for rates -> 10 rows
select first, second, rates
from a_test
where first is null
  and second = 'XX'
group by rates, first, second
order by rates;

When you are using PL/SQL, there are various ways of coding this.  Maybe one of the following will be helpful in your situation:

-- query2 in a cursor for loop
begin
  for rec in (
    select first, second, rates from a_test
    where first is null
      and second = 'XX'
    group by rates, first, second
    order by rates )
  loop
     dbms_output.put_line (
       rec.first || ' ' || rec.second || ' ' || rec.rates  );
   end loop;
end;

-- IF in a cursor for loop (query2 without a WHERE clause)
begin
  for rec in (
    select * from a_test
    group by rates, first, second
    order by rates )
  loop
     if rec.first is null and rec.second = 'XX' then
       dbms_output.put_line (
         'null' || ' ' || rec.second || ' ' || rec.rates  );
       -- alternative: code rec.first instead of 'null'
     end if;
   end loop;
end;


Search NVARCHAR(MAX) string in other string I want to find the index of a string in other string. I try to use CHARINDEX and PATINDEX but nothing worked because the search string is NVARCHAR(MAX) and these functions are limited to 8000 chars only.

I am getting the following error:

Msg 8152, Level 16, State 10, Line 13
String or binary data would be truncated.

Are there any alternatives?

The goal is to replace the find string with other, but REPLACE is not working with NVARCHAR(MAX) either. That's why I am trying to use .WRITE clause but need to calculate the offset.

This is sample code. Basically, I am trying to replace the source of img HTML tags from BASE64 to relative url.

DECLARE @DataSource TABLE
(
    [Content] NVARCHAR(MAX)
)

DECLARE @defaultStartScreenOld nvarchar(max) = N'a NVARCHAR(MAX) big image in BASE64 format';
DECLARE @defaultStartScreenNew nvarchar(2000) = N'images/surveo/presetImages/Thumb_PointerStart.jpg';

INSERT INTO @DataSource ([Content])
SELECT CONCAT('<RUSLAN src="', @defaultStartScreenOld, '"/>')

SELECT [Content]
FROM @DataSource

UPDATE @DataSource
--SET [Content].WRITE(@defaultStartScreenNew, 10, LEN(@defaultStartScreenNew))
SET [Content] = REPLACE([Content], @defaultStartScreenOld, @defaultStartScreenNew)

SELECT [Content]
FROM @DataSource

Considering that:

REPLACE, PATINDEX, CHARINDEX and even LIKE have not been updated to fully and properly deal with LOB/MAX types; and,
you need to repeat this process across hundreds of databases and against multiple instances

I would strongly recommend you look at doing this outside of SQL Server, probably in PowerShell or C# (not CLR inside SQL Server, which will be prone to similar data type limitations and restrictions). You can write a simple C# command line program that pulls in each row, performs the replace inline, and then updates the row wholesale. Then you can point the C# app at each instance/database combination. PowerShell may have some limitations with handling huge chunks of data like this, but I recommend looking into that as well simply due to the automation factor (you can simply feed it a text file of instance/database names and it will churn through all of them in one shot). Even if you could trust the CLR approach to do this correctly, I think the debugging effort will be higher, and you'll also have to deploy the assemblies everywhere (vs. a single app or script that can just be pointed at each instance in turn).

I'm not sure I'm the one you want giving specific guidance on the code you would write using either of these approaches, I just want to steer you away from trying to solve this in T-SQL, since the deck is kind of stacked against you there.


Is it possible to backup a subset of a database in Microsoft SQL Server? Using Microsoft SQL Server, is there a way to backup a subset of a production database? I only want the database structure, and a very small amount of transaction data for a few selected tables.

I want to use this backup file to refresh development database.

For Oracle I see this is achieved using this Powershell script:

userid="/ as sysdba"
compression=all
directory=TEST_DIR
flashback_time=systimestamp
dumpfile=XX.dmp
logfile=xx.log
schemas=XXXXX
query=SCHEMA.TABLE:"where <condition>
exclude=TABLE:"IN (x)"

In SQL Server Management Studio (SSMS), you can generate a script that would create the structural components of your database (tables and views, as well as users, functions, and procedures). You could use the script(s) returned on your development DB.

To run this, right-click on your database in SSMS, and choose Tasks => Generate scripts.... This opens a wizard that will step you through the process, giving you the option of what you want to copy over.

For tables, there are advanced options you can set. This allows you to include indexes and triggers, and to include the data in the tables. However, this would give you all the data from the tables, not just a selected amount.

If you want all the data from selected tables, and no data from the rest, then two passes through this will let you generate two scripts.

In the first pass, select everything, script things as "DROP if exists" (assuming you want to replace anything that's already there), do not include data, and save that as a single script.

In the second pass, only select the tables that you want to copy over the data for; do not choose the "DROP if exists" option; and do include the data, saving this as a second single script (or, if you prefer, as one script per table).

NOTE: This creates scripts that you would run on your database, rather than true backups.

Among other things, this does mean that any objects that have been removed from the production database, will not be removed by the script; it just recreates what it knows about. If what you really want is a clean DB to start with, you may want to drop the current dev database and create a new one with the same name. make sure you've captured every object in the first script, though, or things will be missing.
And, if you need to populate some tables with just a part of the production data, then you would either need to manually remove extra rows from the script file, or use a different process to populate the tables.


error 1118: row size too large. utf8 innodb I have tried to find a solution guys, and I am guessing that someone has met this before.  But I cannot get hits down below 5000... most of which don't match my question.

Using MySQL 5.6 - windows (both XP and Win7 - same issue)

I have a utf8 mysql schema with the following structure (field names removed)
The schema was built just using standard tool - I did not choose compression nor anything else.

f1  varchar(5)
f2  varchar(255)
f3  varchar(255)
f4  varchar(255)
f5  varchar(255)
f6  datetime
f7  datetime
f8  longtext
f9  varchar(255)
f10 varchar(255)
f11 longtext
f12 varchar(255)
f13 varchar(255)
f14 varchar(255)
f15 longtext
f16 longtext
f17 longtext
f18 longtext
f19 longtext
f20 longtext
f21 longtext
f22 longtext
f23 varchar(255)
f24 varchar(255)
f25 varchar(255)
f26 longtext
f27 longtext
f28 varchar(255)
f29 varchar(255)
f30 longtext
f31 longtext
f32 longtext
f33 longtext
f34 longtext
f35 longtext
f36 longtext
f37 longtext
f38 longtext
f39 datetime
f40 datetime

This works fine, until I come to a record with a lot of text in a lot of the  longtext fields.  Say I have inserted values into 6 of the longtext fields, but when I try to paste in values into (say) the 7th, I get the 1118 Error.

Now, I am a bit puzzled as this data is going into a text field which should not contribute to the row length as it is stored outside the row (barring the 12 bytes used to define the entry).  Using my best guess for the row length of the structure above it comes to only 11009 bytes (give or take), well within the 64K limit.

This is proving frustrating as I can't really understand why it fails.  I could break the table down into multiple 1-to-1s, but that seems hackneyed, and I could take out the text fields and create a related table with foreign key and variable name and the associated text (using row modelling for this element) - but again, that is just rod for my back later.

There are two issues you need to look at

ISSUE #1 : Characters Vs Bytes

Since UTF-8 uses 3 bytes per characters, you may not have allocated enough for character fields.

ISSUE #2 : InnoDB Row Length

  By default, an index key for a single-column index can be up to 767
  bytes. The same length limit applies to any index key prefix. See
  Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit
  this limit with a column prefix index of more than 255 characters on a
  TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum
  of 3 bytes for each character. When the innodb_large_prefix
  configuration option is enabled, this length limit is raised to 3072
  bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row
  formats.

  When you attempt to specify an index prefix length longer than
  allowed, the length is silently reduced to the maximum length. This
  configuration option changes the error handling for some combinations
  of row format and prefix length longer than the maximum allowed. See
  innodb_large_prefix for details.

  The InnoDB internal maximum key length is 3500 bytes, but MySQL itself
  restricts this to 3072 bytes. This limit applies to the length of the
  combined index key in a multi-column index.

  The maximum row length, except for variable-length columns (VARBINARY,
  VARCHAR, BLOB and TEXT), is slightly less than half of a database
  page. That is, the maximum row length is about 8000 bytes. LONGBLOB
  and LONGTEXT columns must be less than 4GB, and the total row length,
  including BLOB and TEXT columns, must be less than 4GB.

  If a row is less than half a page long, all of it is stored locally
  within the page. If it exceeds half a page, variable-length columns
  are chosen for external off-page storage until the row fits within
  half a page, as described in Section 14.3.12.2, “File Space
  Management”.

  Although InnoDB supports row sizes larger than 65,535 bytes
  internally, MySQL itself imposes a row-size limit of 65,535 for the
  combined size of all columns:

  mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
      -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
      -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB; ERROR 1118 (42000): Row size too large. The maximum row size for the used table
  type, not counting BLOBs, is 65535. You have to change some columns to
  TEXT or BLOBs See Section E.10.4, “Table Column-Count and Row-Size
  Limits”.


Why student attendance is not considered a fact in data warehousing? In a KimballGroup example student attendance is not considered a fact. Hence it considers this example as forming factless fact table. I don't really understand what is the difference between a fact and measure. Why a sale, which is also an event, is considered a fact but the event of attendance is not. What is the difference with this two events?

thanks

Blame Kimball for the confusing term. Attendance is a fact (as in, "a thing that is known or proved to be true"). However, it is an event, and as such it does not have any extra information apart from references to the corresponding dimension keys. It probably should be called "measureless fact".

A sale, in addition to the dimension references, has measures: quantity and price.


Finding "one of" entries to a composite key/index Problem:
Assuming I have a table like this:
CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `a_id` bigint unsigned NOT NULL,
  `b_id` bigint unsigned NOT NULL,
  `c_id` bigint unsigned NOT NULL,
  `d_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_entry` (`a_id`,`b_id`,`c_id`,`d_id`),
  KEY `test_b_id` (`b_id`),
  KEY `test_c_id` (`c_id`),
  KEY `test_d_id` (`d_id`)
)

In this test table, every id can appear multiple times, but all combinations must be unique.
Now I want to write an application to compare them. I don't want to compare apples with peaches, so I have to find entries where all other keys are the same and only one of them is different.
How can I archive this and what indexes would be useful here? Let's say comparisons on column a_id would be significantly more common than on all other columns.
There is no need to find this for all 4 columns in one query. I would like to have a template query to find comparable entries for one of the columns.

Example:
Assume there are 10 different ids for each column and every combination of them is present in the database. (10^4 = 10000 entries).
Query 1:
Now I want to compare a_id = 1 with a_id = 2. The query result has to look something like this:
Test: [a_id, b_id, c_id, d_id]
1, 1, 7, 1
2, 1, 7, 1
1, 5, 5, 3
2, 5, 5, 3
1, 1, 7, 4
2, 1, 7, 4
⋮

Notice a_id is in (1, 2) but for each entry, there is a matching one, where only a_id is different.
Query 2:
Also, I would like to provide a way to query which comparisons could be made on a specific column. The result should look like Query 1 but for arbitrary ids. (id_a in (m, n))
In my case the database does only contain a subset of all possible combinations.

One idea:
From my understanding, unique composite keys are like an additional unique column where the value is a chain of the original columns in the specified order.
fname, lname, composite_key(lname, fname)
---------------------------
John, Miller, Miller-John
Peter, Jones, Jones-Peter
…

If it is possible to query on this composite_key index tree or something like that, my problem would be easy to solve.
Create a composite index for each column so every column is the last one once. E.g.
KEY `key_a` (`b_id`, `c_id`, `d_id`, `a_id`),
KEY `key_b` (`a_id`, `c_id`, `d_id`, `b_id`),
KEY `key_c` (`a_id`, `b_id`, `d_id`, `c_id`),
KEY `key_d` (`a_id`, `b_id`, `c_id`, `d_id`),

For a column a_id comparison just find all paths on the tree with two values on the last branch. This would be similar for the other columns.

Task 1 may be solved by
SELECT t1.*
FROM test t1
JOIN test t2 USING (b_id, c_id, d_id)
WHERE (t1.a_id, t2.a_id) IN ((1,2), (2,1))


Does a view need its own foreign key constraints? Disclaimer: I'm a programmer, not a DBA, so bear with me...

I have a view that I use to just map 2 entities together.  I have to do a join between a few different tables to get that:

CREATE OR REPLACE VIEW V_SCREENING_GROUP_SITES AS (
SELECT SG.SCREENING_GROUP_ID, V.SITE_ID
FROM SCREENING_GROUP SG, VISIT V, VISIT_DATE VD
WHERE VD.VISIT_ID = V.VISIT_ID
AND V.SCREENING_GROUP_ID = SG.SCREENING_GROUP_ID);

Above is just for context, don't worry too much about that.  What I need to know is how to make the fields in my new V_SCREENING_GROUP_SITES view (SCREENING_GROUP_ID and SITE_ID) behave as foreign keys to the SCREENING_GROUP and SITE tables.  Or does it even matter?

If it was a table I would do:

ALTER TABLE V_SCREENING_GROUP_SITES
ADD CONSTRAINT FK_SCREENING_GROUP_ID
FOREIGN KEY (SCREENING_GROUP_ID)
REFERENCES SCREENING_GROUP.SCREENING_GROUP_ID;
...

But since it's a view that obviously doesn't work.  I couldn't find an ALTER VIEW syntax that works for setting FKs.  What should I do?

(This is a MySQL Database)

A View is a logical table that is based on one or more physical tables.  If there are foreign key relationships in the underlying tables, then they will be manifested in the view.  Views are entirely dependent on the tables they are derived from, so trying to add foreign keys to them is not possible.


Why pad instead of trim when comparing strings? The ANSI/SQL-92 standard states that strings have to be padded to the same length when compared:

     3) The comparison of two character strings is determined as fol-
        lows:

        a) If the length in characters of X is not equal to the length
          in characters of Y, then the shorter string is effectively
          replaced, for the purposes of comparison, with a copy of
          itself that has been extended to the length of the longer
          string by concatenation on the right of one or more pad char-
          acters, where the pad character is chosen based on CS. If
          CS has the NO PAD attribute, then the pad character is an
          implementation-dependent character different from any char-
          acter in the character set of X and Y that collates less
          than any string under CS. Otherwise, the pad character is a
          <space>.

        b) The result of the comparison of X and Y is given by the col-
          lating sequence CS.

        c) Depending on the collating sequence, two strings may com-
          pare as equal even if they are of different lengths or con-
          tain different sequences of characters. When the operations
          MAX, MIN, DISTINCT, references to a grouping column, and the
          UNION, EXCEPT, and INTERSECT operators refer to character
          strings, the specific value selected by these operations from
          a set of such equal values is implementation-dependent.

        Note: If the coercibility attribute of the comparison is
        Coercible, then the collating sequence used is the default de-
        fined for the character repertoire. See also other Syntax Rules
        in this Subclause, Subclause 10.4, "<character set specifi-
        cation>", and Subclause 11.28, "<character set definition>".

Other than the obvious "it isn't in the standard", what would be the reasons against trimming the ends of the strings instead of padding them to the same length?

It is no problem to trim the strings before comparing them.

The SQL standard does not describe the implementation, it describes the semantics of the comparison. It just finds it simpler to describe the behavior using padding.

You are free to implement the comparison any way you want as long as the behavior is identical.


No tables found after running Expdp and Impdp in Oracle 12c I am trying to export a schema which only contains one table with one row on it.  It has a table called "product" and resides in a tablespace called "tbs_data".  Here's the expdp command I ran which completed successfully.

expdp dbauser/dbapasw@oradev schemas=myschema directory=my_data_pump_directory
dumpfile=myschema_exp.dmp logfile=myschema_exp.log

When I ran the impdp, it complained about the "myschema" missing.

impdp dbauser/dbapasw@oradev schemas=myschema directory=my_data_pump_directory
dumpfile=myschema_exp.dmp logfile=myschema_exp.log

So I created this user/schema manually and re-ran the impdp command.  It completed successfully.  However when I logged in as "myschema", I didn't see the product table.  Also, from what I have been reading, the Impdp command
was supposed to re-create the target user/schema when you run Impdp.

Could someone let me know what I am missing or how to resolve this?

Here's the actual log:

;;;
Export: Release 12.1.0.2.0 - Production on Fri Jul 29 14:22:46 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "dbauser"."SYS_EXPORT_SCHEMA_01":  dbauser/********@oradev schemas=myschema directory=my_data_pump_directory dumpfile=myschema_exp.dmp logfile=myschema_exp.log
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "dbauser"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for dbauser.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\ADMIN\ORADEV\DPDUMP\myschema_EXP.DMP
Job "dbauser"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jul 29 14:22:54 2016 elapsed 0 00:00:07

I couldn't find what went wrong in your procedure and reproduce the same scenario either. I can help by demonstrating how to export one schema to another or in to the same database. I created the following example for this purpose.

Disclaimer: I don't have 12c instance to test it right now, but same procedure should work for 12c as well.

SQL> create user myschema identified by myschema;

User created.

SQL> grant connect, resource to myschema;

Grant succeeded.

SQL> conn myschema/myschema
Connected.
SQL> create table product(id number, name varchar2(20));

Table created.

SQL> insert into product values(1, 'sample_product_name');

1 row created.

SQL> commit;

Commit complete.

SQL> create directory dp_dir as '/home/oracle/Desktop';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@nepal Desktop]$ clear

[oracle@nepal Desktop]$ expdp system/oracle schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp logfile=expdp_myschema.log

Export: Release 11.2.0.4.0 - Production on Sat Jul 30 07:02:07 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/password schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp logfile=expdp_myschema.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "MYSCHEMA"."PRODUCT"                        5.429 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/Desktop/expdp_myschema.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jul 30 07:04:51 2016 elapsed 0 00:02:21

[oracle@nepal Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 30 07:10:06 2016

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> drop user myschema cascade;

User dropped.

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

   [oracle@nepal Desktop]$ impdp system/oracle schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp

Import: Release 11.2.0.4.0 - Production on Sat Jul 30 07:11:44 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/password schemas=myschema directory=dp_dir dumpfile=expdp_myschema.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MYSCHEMA"."PRODUCT"                        5.429 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Sat Jul 30 07:12:14 2016 elapsed 0 00:00:19

[oracle@nepal Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 30 07:13:08 2016

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> conn myschema/myschema
Connected.

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
PRODUCT

SQL> select * from product;

    ID NAME
---------- --------------------
     1 sample_product_name

SQL>


Do PostgreSQL toasted fields use any per row, in-table space? I understand that PostgreSQL will store large fields in a separate storage space automatically. When this occurs, is there anything stored per row for that field in the table? A reference or hint of some kind?

Yes, see TOAST in Database Physical Storage

  TOAST usurps two bits of the varlena length word (the high-order bits
  on big-endian machines, the low-order bits on little-endian machines),
  thereby limiting the logical size of any value of a TOAST-able data
  type to 1 GB (230 - 1 bytes). When both bits are zero, the value is an
  ordinary un-TOASTed value of the data type, and the remaining bits of
  the length word give the total datum size (including length word) in
  bytes. When the highest-order or lowest-order bit is set, the value
  has only a single-byte header instead of the normal four-byte header,
  and the remaining bits give the total datum size (including length
  byte) in bytes. As a special case, if the remaining bits are all zero
  (which would be impossible for a self-inclusive length), the value
  is a pointer to out-of-line data stored in a separate TOAST table.
  (The size of a TOAST pointer is given in the second byte of the
  datum.)


Different ways to log in to MariaDB I'm trying to log in to a mariaDB database which is (at least the vendor of the product that it was installed for tells so) "protected" by SSL.

I tried several tools but none of them works fully.

MySQL Workbench exits silently when I try to connect but when I choose the "reverse engineering" menu entry it is able to extract and display the schema.
HeidiSQL fails with Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)
dBeaver shows the same: Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)
Database Workbench fails with the same error as the others: Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)

I am sure that the credentials are typed correctly because I copy them from a notepad window and they work with MySQL Workbench.
All tools are run with the current version available, community or free and not paid)

Are there different ways to authenticate with a MariaDB server ? Basically MySQL Workbench seems to work the best, but because it fails silently with a "full" connection this is also not feasible.

Yes, there are several ways to authenticate, but you have to specify this per database user. Before MariaDB 10.4 each user could only use one authentication method. You can change the authentication method for a user if you can log in as the root or other sufficiently privileged user. Use the ALTER USER statement if you're on MariaDB 10.2+, otherwise use UPDATE mysql.user SET plugin = '...' WHERE user = 'the_user' AND host = 'the_host';.

Authentication is done by authentication plugins. Run e.g. this to see which plugins you currently use for your database users:

SELECT user, host, plugin FROM mysql.user;

Some common authentication plugins are mysql_native_password and unix_socket. The former requires a password and works over TCP whereas the latter works over the MariaDB socket file (by default it's at /var/lib/mysql/mysql.sock in Linux) and doesn't require a password, but it assumes your OS user has the same name as your database user, and obviously only works if you're on the same host as the MariaDB server. Perhaps this is something you could try to avoid SSL?

Relevant MariaDB Knowledgebase documentation

ALTER USER - authentication options
Pluggable Authentication Overview
Authentication Plugin - Unix Socket
Authentication from MariaDB 10.4


Cannot install DB2 Express-C 10.1 on Mac OS X 10.7.5 I'm having troubles seting up DB2 Express-C 10.1 on my Mac OS X (v10.7.5). My initial install attempt failed, but then I found

https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014927797

That seemed to work up to the point where I tried to start the db:

$ ./db2start
dyld: Library not loaded: /db2unix/db2galileo/db2_galileo_darwinport/darwin64/s120905/engn/lib/libdb2e.dylib
Referenced from: /Users/home/sqllib/adm/./db2start
    Reason: image not foundTrace/BPT trap: 5
$

OTOH, if I try to run db2setup from the expc directory I get and error message that others have seen. Something like

/Applications/expc/db2/macos/install/db2setup: line 606:
/tmp/db2.tmp.22412/db2/macos/install/../java/jre/bin/java: No such file or  directory

Where the 5-digit number in the tmp directory name changes on each run.

That is followed by a nice DB2 10.1 intro screen which is suddenly replaced by a blank DB2JDKTester window that says "DB2 SETUP LAUNCHPAD" at the top.

Has anyone else tried to install DB2 on Mac OS X 10.7?

(And, yes, I've seen the other thread with the same title, but that was never actually answered.)

I got it working by completely uninstalling db2 express-c (the db2_deinstall script failed so I manually deleted all directories that involved db2. :P ) .

Then I followed
https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014927797

and it worked. I never ran db2_setup.


INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario? When you are in the position of inserting data into a table if the data does not already exist ...what is the best way to go with indexes?
I'm specifically thinking of SQL Server here but I assume the situation would be the same on other platforms.
On the one hand you are inserting new data into the table that will need to be added to the index at some performance cost.
On the other hand you are reading the table to see if the data already exists, so the index will help performance.
My theory is that having an index in this situation would probably be better than to not have one as (from my limited experience) I have always found un-indexed tables to perform terribly and insertions to indexed tables to be 'a bit slower' (obviously the type of index will impact on this).
My simple testing also suggests that indexing the table (a clustered index on 2 of the 7 columns) does improve performance, but then the table I tested on already contained 30M rows to start with (40-50M more are added by the insert).
I was just wondering if anyone can offer any insight into this scenario to help my learning.
Thanks

A clustered index IS the table - so if the rows you are adding are already ordered there is not much work to do. A non-clustered index is an extra structure that you will have to maintain - so Inserts, Updates & Deletes will all have an overhead, the more indexes the higher the overhead. Generally, there are some selects for every table (otherwise what is the point of storing the data?), The balance between the improved SELECT performance versus the maintenance overhead can really only be determined for your particular workload - so measure twice & cut once!


Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it? I understand that we can use '' to escape a single quote in Oracle queries. However, I want to know if it is feasible to escape a single quote in the ESCAPE clause.

set up:

create table test_t ( x varchar2(100));
insert into test_t values ('it''s a string');
commit;

Dummy Query:

select * from test_t
 where x like '%''%' escape '''';


The ordinal position for a column changed without my knowledge I have a column that is out of order (ordinally, not in terms of select statement), and I just can't figure out how it got that way.

I've been researching this for quite some time and am coming up empty.
I also could not reproduce it in testing.

I have informed the developers that they are not allowed to run queries that do not explicitly name columns, for exactly this reason.

In the mean time, though, it's driving me crazy that I have no idea how we came to this state of affairs.

Someone changed the table, simple as that. Maybe not on purpose, e.g. they could have been changing other things in the mickey mouse table designer and accidentally clicked and dragged a column up or down. Figuring out who and when will be guesswork unless you have auditing in place, or the ALTER or DROP/CREATE events are still in the default trace:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName, HostName, ApplicationName, StartTime, Action = CASE EventClass
    WHEN 46 THEN 'Create' WHEN 47 THEN 'Drop' ELSE 'Alter' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (46,47,164)
AND EventSubClass = 1
AND DatabaseName = N'your database name'
AND ObjectName = N'your table name'
ORDER BY EndTime DESC;


Return matches that have at least one message I have two tables, the match table keeps track of matches and the message_log table keeps track of conversations for a match. Right now I'm getting all the matches by the following query

SELECT * FROM match order by id DESC

But I want to modify it so that it would only return matches that have at least one message in the message_log and at least one message has own_account set to false.

my match table

CREATE TABLE public.match
(
    id bigint NOT NULL DEFAULT nextval('match_id_seq'::regclass),
    match_id character varying(500) COLLATE pg_catalog."default",
    name character varying(30) COLLATE pg_catalog."default",
    birthdate timestamp with time zone,
    bio text COLLATE pg_catalog."default",
    my_id character varying(500) COLLATE pg_catalog."default",
    username character varying(500) COLLATE pg_catalog."default",
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    CONSTRAINT match_pkey PRIMARY KEY (id),
    CONSTRAINT match_match_id_key UNIQUE (match_id),
    CONSTRAINT matcch_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

my message_log table

CREATE TABLE public.message_log
(
    id bigint NOT NULL DEFAULT nextval('message_log_id_seq'::regclass),
    message text COLLATE pg_catalog."default",
    from_id text COLLATE pg_catalog."default",
    to_id text COLLATE pg_catalog."default",
    match_id text COLLATE pg_catalog."default",
    unix_timestamp bigint,
    own_account boolean,
    reply_batch boolean DEFAULT false,
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    match_pk bigint,
    CONSTRAINT message_log_pkey PRIMARY KEY (id),
    CONSTRAINT message_log_message_from_id_to_id_match_id_unix_timestamp_key UNIQUE (message, from_id, to_id, match_id, unix_timestamp),
    CONSTRAINT message_log_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT message_log_match_id_fk FOREIGN KEY (match_pk)
        REFERENCES public.match (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

You can do this efficiently and concisely with an EXISTS clause.

SELECT *
FROM match
WHERE EXISTS (
  SELECT 1
  FROM message_log
  WHERE match_pk = match.id
  AND own_account IS FALSE
)
ORDER BY id DESC;

Btw it's really confusing to have match_id, and match_pk in the message_log table. Normally, the convention of _id means you're referencing something else in a different table.

Also, in PostgreSQL we never use character varying(x) unless there is a spec that imposes x you should just be using text.


Array to string sometimes concatenates fewer values array_to_string(array_agg(array_to_string(array[
            highlights,exceptions,inter_note,inter_status::text
           ,comp_note,comp_status::text,hard_note,hard_status::text]
        ,'|#|'::text))
     ,'|*|'::text) as status

By executing the above query the status should contain 8 values separated by |#| or |*|. But in some cases it returns only 3, 4 or 6 values. I need to get all 8 values weather the record is empty or not.

Example:

  original array: [|#||#||#||#|Complete|#||#|Undefined|#||#|Undefined]

  some cases: [|#||#||#|PASS|#|Complete|#|Undefined|#|Undefined]

  another case: [Complete|#|Undefined|#|Undefined]

Any idea why some array elements are missing?

Ensure that every field has a default value by using COALESCE.

create table t (highlights varchar(10),
                exceptions varchar(10),
                inter_note varchar(10),
                inter_status int,
                comp_note varchar(10),
                comp_status int,
                hard_note varchar(10),
                hard_status int);

insert into t values ('a','b','c',1,'d',2,'e',3),('',null,'f',4,null,5,'g',null);

✓

2 rows affected

select array_to_string(
         array_agg(
           array_to_string(
             array[coalesce(highlights, ''),
                   coalesce(exceptions, ''),
                   coalesce(inter_note,inter_status::text, ''),
                   coalesce(comp_note, ''),
                   coalesce(comp_status::text, ''),
                   coalesce(hard_note, ''),
                   coalesce(hard_status::text, '')],'|#|'::text)),'|*|'::text) as status
from   t;

| status                                            |
| :------------------------------------------------ |
| a|#|b|#|c|#|d|#|2|#|e|#|3|*||#||#|f|#||#|5|#|g|#| |

dbfiddle here


Database Design Schema I have a project which we have to make a database for a car dealership. We have to make a Salersperson be able to view the inventory, add sales people, add cars/ car details. A customer must be able to view inventory, add cars they would think about purchasing, and then purchase the car or have a salesperson purchase the car. Salesperson must have their sales saved . I feel like my design looks a bit clunky and could be broken down better in a few more tables, but I am just drawing a blank when thinking about how else to break this up. Any suggestions?

I don't really understand what you mean by "looks a bit clunky and could be broken down better in a few more tables"; something looking "clunky", however that applies to database design, doesn't seem like a good reason to breaking things up. Unlike fighting the dark force, you probably should use logic, not your feelings.
For a suggestion, may be you could answer (to yourself) to some questions about your model, such as:
Why do you think that "car make/model" is a separate entity and not simply attributes of a "car" entity?
Why car.car_lot varchar(255) references car_lot.lot_id (integer)?
Why for_sale_cars? What entity does that represent?
Is customer.customer_cars varchar(255) intended to store a list of cars? That would violate 1NF. And how is that then different from saved_cars?
Why do you think you need an extra key in saved_cars?
Which of the requirements you have listed cannot be addressed by your model? Are there any requirements that are missing?


Mysql Update Query is not Giving Desired Result In My Php Code I Have Query As

SELECT COUNT(id) AS todayleads
FROM `lead_data`
where `date` = '$todate' AND lead_status = 1
AND uid = '$userid' OR assign_to = '$userid'

When I echo Query it result me

SELECT COUNT(id) AS todayleads
FROM `lead_data`
where `date` = '2018-01-12' AND lead_status = 1
AND uid = '3' OR assign_to = '3'

And Output Gives Mea All Data From Start to End. But Expected O/p is Only Data That have lead_status = 1 and uid = 3 Or  assign_to = 3.

As far as I understand according to your comments you should add a parentheses to your WHERE clause:

SELECT COUNT(id) AS todayleads
FROM   `lead_data`
WHERE  `date` = '2018-01-12'
AND    lead_status = 1
AND    (uid = '3' OR assign_to = '3')


MySQL architecture for different word clouds I want to create a word cloud on my website based on the keywords people search.

I have one free text input field where something can be entered.
I have 3 different user types on my website (anonymous, users, organizations). And I want to keep create a wordcloud on the most searched keywords for all these 3 types separately.

I was wondering what is the best architecture for MySQL to accomodate this problem.

Is it better to save all user types in 1 table and keep a "user_type" field? Or is it better to create a different table for each user type (for performance reasons?)?

As well, longer term-wise, I would like to use the user's searching history to provide better results for him. So to avoid rework later, I think it's good that I already have it linked to a specific user_id already right now.

Can anyone suggest me a mysql architecture for this issue?
I am thinking of going the way as suggested by user @Derek Downey in this post. With adding a user_id in the DateofSearchTerm table.

CREATE TABLE words (
    user_type ENUM('anonymous', 'users', 'organizations') NOT NULL,
    group_id SMALLINT UNSIGNED NOT NULL,
    word VARCHAR(...) NOT NULL,
    ct MEDIUM INT UNSIGNED NOT NULL,   -- how often user used the word
    PRIMARY KEY(user_type, user_id, word)
) ENGINE=InnoDB;

This will let you build an overall cloud, or a cloud for an individual user/organization.

SELECT word, SUM(ct) FROM words;  -- overall

SELECT word, ct
    FROM words
    WHERE user_type = 'organization'
      AND group_id = 123   -- for a particular user

You have not really mentioned a requirement any date.


Transaction Log Backup Affecting Applications Performance We have a database around terabytes of data. We are taking FULL backup every week, differential backup daily and transaction log backup after every hour. Sometimes this transaction log backup takes some time to complete (around 10-15 minutes). In this period we find that application(s) performance affects a lot(normally very slow) that use the same database.

Does transaction log backup affect the performance of the application?
If yes, then how to make speedup the process of taking transaction log backup?

Does transaction log backup affect the performance of the application?

The transaction log backup reads from the transaction log file and writes to the backup location.  The application writes to the transaction log file when it changes the database.  If the log file or the log backup location and the database files or tempdb files are on the same disk (or even perhaps on the same SAN controller), then the log backup could impact access to those files too.

Capturing a snapshot of the wait stats during the application slowness should provide more details on what impact the backup is having.

Taking the backups more frequently will spread out the impact.  But you also may need more storage resources (more or faster disks) for this database.  With large databases you need to provide enough resources for both operating and maintaining the database, and maintenance tasks (like log backups, index maintenance, schema modification) can require significant additional resources.


How to troubleshoot SQL Server Merge Replication Conflict? I have SQL Server 2008 R2 Merge Replication setup.

I have 2 publications for the same database with 20+ subscribers.

The logic of our application prevents changing the same data from 2 subscribers at the same time.

Lately I started getting conflicts for some specific tables & almost all the time the contents of the 2 conflicting rows ( the publication row & the subscriber row ) are identical with no differences at all!!

My question is: What is the standard steps to troubleshoot conflicts & trace down to the point that is causing it?

You need to inspect the Conflict Viewer and observe the conflict types and the conflicting row values.  Something to note is that the Conflict Winner column will display the winning row values which are pulled from the base tables.  If you think about it, these values are volatile and can change, so the values you see in the Conflict Winner column may not be the values they were when the conflict occurred, keep this in mind.  The Conflict Loser column will display the losing row values which are pulled from the MSmerge_conflict_publication_article table and these values are static.

If you are unable to determine the root cause of the conflicts based on the Conflict Viewer, you may need to implement an auditing scheme.  I have provided an example of how to do this in Auditing Changes in Merge Replication.

There is a small chance you are getting false conflicts but auditing should reveal whether or not that is the case.


Update datatype of primary key without deleting the table I have created a table State which my other table, Addresses, references.

CREATE TABLE State
(
     StateId varchar(20) PRIMARY KEY NOT NULL,
     Text varchar(40) NOT NULL
)

I want to change its primary key to int and IDENTITY(1,1), however, I cannot delete it because it is a foreign key for the other table. How can I update it without having to recreate everything?

You cannot change datatypes of Key fields (Primary or Foreign). You can't even change a datatype if the field is used in an index. So...

If there is no data in these tables (obviously much easier):

Drop Foreign Key on [Addresses] that references [State]
Drop Primary Key on [State]
ALTER TABLE [State] DROP COLUMN [StateID];
ALTER TABLE [State] ADD [StateID] INT IDENTITY(1, 1) NOT NULL;
Recreate Primary Key on [State]
ALTER TABLE [Addresses] ALTER COLUMN [StateID] INT NOT NULL;
Recreate Foreign Key on [Addresses] that references [State]

If there is data in these tables (and you have a maintenance window):

ALTER TABLE [State] ADD [StateID2] INT IDENTITY(1, 1) NOT NULL;
ALTER TABLE [Addresses] ADD [StateID2] INT NOT NULL CONSTRAINT [DF_Addresses_StateID2] DEFAULT (0);
Update the [Addresses] table to match the new IDs in the [State] table

UPDATE addr
SET    addr.StateID2 = st.StateID2
FROM   Addresses addr
INNER JOIN [State] st
        ON st.StateID = addr.StateID

Drop Foreign Key on [Addresses] that references [State]
Drop Primary Key on [State]
sp_rename [State].[StateID] -> [StateIDold]
sp_rename [State].[StateID2] -> [StateID]
Recreate Primary Key on [State].[StateID]
ALTER TABLE [Addresses] DROP CONSTRAINT [DF_Addresses_StateID2];
sp_rename [Addresses].[StateID] -> [StateIDold]
sp_rename [Addresses].[StateID2] -> [StateID]
Recreate Foreign Key on [Addresses].[StateID] that references [State].[StateID]
Update Stored Procedure(s) or whatever processes/code insert (and possibly update if the StateID field can change) into the [Addresses] table
After you are sure that all data is there and matches, then you can drop the [StateIDold] field in both the [State] and [Address] tables.

If there is data in these tables (and you do not have a maintenance window):

If there is no maintenance window then you have to not only deal with contention on the table (and adding / removing fields requires a SCHEMA lock), but you also need to update the process that adds data to the [Addresses]

Build copies of both tables [Addresses2] and [State2] with the desired structure (but no FK)
Copy the data over to the new tables
make sure no data in [Addresses] can change (this is very temporary)
rename current tables (e.g. [Addresses] -> [AddressesOld])
rename new tables (e.g. [Addresses2] -> [Addresses])
Add FK
Be sure to update code that inserts into Addresses
Allow changes in [Addresses] again

For a more detailed explanation of this "no maintenance window" approach, please see the article I wrote about it on SQL Server Central (free registration required): Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!


What is effective spindle count The PostgresSQL wiki suggest a rule for find the optimal amount of DB connection pool size.

amount = ((core_count * 2) + effective_spindle_count)

But it does not explain what the effective spindle count is and how can I find this value.

This relates to rotating harddisks (not SSDs). One rotating harddisk is one spindle.

So if your server uses e.g. a RAID with 16 disks, then your effective_spindle_count is 16

It is essentially a measure of how many concurrent I/O request your server can manage. Rotating harddisks can (typically) only handle one I/O request at a time. If you have 16, your system can handle 16 I/O requests at the same time.

The mentioned rule of thumb is no longer valid for SSDs. As they can typically handle several parallel I/O requests at the same time. I don't know however how you could obtain that number.

Which is also mentioned in the Wiki page you linked to:

  There hasn't been any analysis so far regarding how well the formula works with SSDs.


One primary key in two tables I am first timer with ms access and also here

I am working on a database of library.  I have two different tables which stores details one for books and others for Periodicals.

How can make accession number primary key for both tables.

Are autonumber a accession number when a new entry made in books are periodicals.

The two options would be:

Model them in the same table with a property to distinguish their type (such fields are often called a discriminator) and all the properties of both types.
Model them with a third table holding their common properties (and usually a discriminator also) and one child table per type. This pattern is referred to as table inheritance.

The latter option would be preferable, but I'm not sure how one would implement it in Access (I've not used that in a decade and a half).


Why am I seeing OPTION (MAXDOP 1) in my Actual query plan even though I've not set it in the stored proc? SQL Server is set with MAXDOP 0 by default. In the stored procedure I'm testing I keep seeing WITH (MAXDOP 1) in the Actual Execution Plans even though I've not specified any hints other than SORT_IN_TEMPDB = 1. The main Stored Procedure calls another procedure which grabs the table, generates scripts on each disabled index, then executes that series of scripts (with the afformentioned query hint). The funny thing is I'm seeing a high CPU cost for the operation so I imagine there's benefits to parallelism.

The table has a few million rows and is very wide.

Unfortunately, the database is file and TempDB file are on the same datastore and the TempDB is just 1 file as opposed to several. This configuration is out of my control but perhaps it's related to the reason for no parallelism.

The server is on a VM with 4 cores and 64 GB RAM.

Any thoughts on why SQL Server insists on dropping parallelism on such a large operation on a large table?

EDIT: Added script for the Inner stored procedure that gets called during Upserts:

CREATE PROCEDURE etl_Index_EnableDisable
@DatabaseName NVARCHAR(50)
,@TableName NVARCHAR(50)
,@EnableDisable NVARCHAR(50)

AS
BEGIN

 IF @DatabaseName IS NULL
   RAISERROR('Null values not allowed for @DatabaseName', 16, 1)
 IF @TableName IS NULL
   RAISERROR('Null values not allowed for @TableName', 16, 1)
 IF @EnableDisable IS NULL
   RAISERROR('Null values not allowed for @EnableDisable', 16, 1)

 DECLARE @SQL NVARCHAR(4000)

 -- Interpret 1 as enable and 0 as disable. Also set to Upper case
 SET @EnableDisable = CASE UPPER(@EnableDisable) WHEN '1' THEN 'ENABLE'
                                                 WHEN 'REBUILD' THEN 'ENABLE'
                                                 WHEN '0' THEN 'DISABLE'
                                                 ELSE UPPER(@EnableDisable)
                      END

 SET @SQL = ' DECLARE @SQLIn NVARCHAR(4000)
              SELECT @SQLIn = COALESCE(@SQLIn,'' '')
              +  SQL1 FROM ( (SELECT '' ALTER INDEX '' + si.name + '' ON ' + @DatabaseName + ' .dbo.'' + st.name + ' + CASE @EnableDisable WHEN 'DISABLE' THEN ''' DISABLE '''
                                                                                WHEN 'ENABLE' THEN ''' REBUILD WITH (SORT_IN_TEMPDB = ON) ''' END + '
                 AS SQL1
                  FROM ' + @DatabaseName + '.Sys.indexes si
                       JOIN  ' + @DatabaseName + '.Sys.tables st ON si.object_id = st.object_id
                  WHERE st.name = ''' + @TableName + '''
                        AND (si.is_unique = 0
                             AND si.is_unique = 0
                             AND si.is_primary_key = 0
                            AND si.[type] = 2
                            )
                       AND si.is_disabled = ' + CASE @EnableDisable WHEN 'DISABLE' THEN '0'
                                                                    WHEN 'ENABLE' THEN '1'
                                                END + '
                       AND si.Name IS NOT NULL
               ) ) tb

             EXEC sp_sqlexec @SQLIn '

 EXEC sp_sqlexec @SQL

END`

You are performing an index rebuild on Standard edition.

As documented here you would need Enterprise or Developer edition to see parallelism for this.


SSRS error "something went wrong please try again later" in web portal I have a brand new installation of SQL Server Reporting Services 2019 on top of MSSQL 2019 Enterprise CU1. SSRS has been installed and configured using default settings, apart from the service account it should use. Both AD and MSSQL instance are configured in minimum permissions only (or close to this state). When I am accessing the Web Portal I receive an error saying "please try again later", which isn't much of help. I found many similar posts asking for help on the same issue, but wanted to see the answer here.

After some digging I found plenty of unconnected issues, but finally managed to solve them and wanted to share them all in one place so nobody else waste that much time if hits the same issue (or some of them).

First you want to access is the Reporting Services Error Catalog, depending on your installation directory, it will be something like this:

?:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles

As there will be multiple files I recommend you to stop the SSRS service (PS as admin):

Get-Service -Name SQLServerReportingServices | Stop-Service

Then remove all of the files there (still in the same PS session):

Remove-Item -Path ?:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles -Force

This might fail for the ReportingServicesWMI_YYYY_MM_DD_HH_MM_SS.log file as it is in use, but there is no need to stop the WmiPrvSE.exe here, you can leave it as it is.

One you have your log space cleared you can start the SSRS service again:

Get-Service -Name SQLServerReportingServices | Start-Service

And access the Web Portal again. Go as deep as you need to access the error again and stop the SSRS service again. At this point you should be able to find your error message in one of the error log files.

As your can differ I will just list all of the fixes I had to apply:

Give Full Control to the service user I used to run reporting services over the:

?:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\RSTempFiles
Give SSRS MSSQL Login permission to View any database

USE [master]
 GO
 GRANT VIEW ANY DATABASE TO [DOMAIN\SSRS_MSSQL_Login] AS [sa]
Allow Log on as a batch job to SSRS service user:

RUN --> secpol.msc
Security Settings --> Local Policies --> User Rights Assignment --> Log on as a batch job
Add User or Group

Reference Material

Reporting Services Log Files and Sources (Microsoft | SQL Docs)


MS Access Run Multiple Queries I have eight 'append' queries in MS Access 2010 that I want to be able to run.
Each query also needs to take one parameter (a project number).

I want to run all eight, entering the parameter only once.

I haven't programmed in MS Access before. There are a lot of posts out there.

Do I need to learn how to create a Macro/ a Routine/ a Sub Routine? Or is there an easier way?

Do I need to learn how to create a Macro/ a Routine/ a Sub Routine?

Yes. See the Stack Overflow question below for an example of how to do it using VBA code and QueryDef objects.

Is it possible to pass parameters programmatically in a Microsoft Access update query?

If you require further assistance please as a new question on Stack Overflow.


SQL Server returns "Arithmetic overflow error converting expression to data type int." When I run this command with SUM()

SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

I'm getting,

Arithmetic overflow error converting expression to data type int.

Any idea on what is the cause of it?

I'm just following the instructions in this answer.

For values larger than the INT max (2,147,483,647), you'll want to use COUNT_BIG(*).

SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

If it's happening in the SUM, you need to convert Amount to a BIGINT.

SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;


Postgres functional indexes that returns an array and used in joins I have a table that looks like this:

Value Table
(active), (version_no), (thing_id), (data)
------
true,       3,            7,          "active-value"
true,       2,            6,          "other-thing"
false,      2,            7,          "old-value"
false,      1,            7,          "first-value"

That is to say, each "thing" has one value. Every time the value is updated (only active values get updated), a trigger puts the old version into the table and marks it as inactive. I only want one version of each piece of data.

I would like to have another table reference the values. But it can reference it in one of two ways. It can either reference the "active" value for a thing or a fixed version.

Reference Table:
(version_no), (thing_id)
------
2,               7 -- Will always return "old-value"
3,               7 -- Will always return "active-value"
null,            7 -- Will return "active-value" until a new value is inserted...

I would like to have a single way to query the references based on the value they point to.

One way I thought of is to have a functional gist index f(active, thing_id, version) on the values table that would return a list of tuples that a reference could match on. For instance f(false, 2, 1) -> [(2, 1)] and f(true, 2, 3) -> [(2, 3), (2, null)]

Then check if

SELECT references.*
FROM references
INNER JOIN values
    ON (
        (reference.thing_id, reference.version) =
        ANY(f(values.active, values.thing_id, values.version)
       )
WHERE values.data = "abc"

Am I on the right track? Is there a better way of having "dual references" to another table? Is there a more efficient way than an array?

To me it seems that the requirements are not very clear. And we don't have a definition (CREATE TABLE) of the two tables, so I'll first write my assumptions of what the tables are what they should be:

    CREATE TABLE value
      ( active boolean not null,
        version_no int not null,
        thing_id int not null,
        data text,
        CONSTRAINT thing_version_UQ
            UNIQUE (thing_id, version_no)
      ) ;

Whether you have or not that UNIQUE constraint is not sure but from the text it seems that each time the value of data is changed for a "thing", a new version is inserted. And we can't have 2 rows with same thing and version so if the constraint isn't there, it should added.
There is only one active version per thing. It's not at all obvious if this restriction is enforced but it can be, with a partial index:

CREATE UNIQUE INDEX only_one_active_version_per_thing_PUQ
    ON value
        (thing_id)
    WHERE (active) ;

Then we have the consideration for a new references table.

First, references is a reserved keyword. I strongly suggest that reserved words should not be used in a database, table, column (or any other object) name. The same goes for VALUES and VALUE. These are not reserved in Postgres but they are in SQL. I would find an insert like the following confusing at first sight:

insert into values values
    (true,   3,  7,  'active-value'),
    (true,   2,  6,  'other-thing'),
    (false,  2,  7,  'old-value'),
    (false,  1,  7,  'first-value') ;

For the actual suggestion, I don't see why you can't use simple queries. To request a specific thing and version:

SELECT active, date
FROM value
WHERE (thing_id, version_no) = (2, 1) ;

and to request for the active version of a thing:

SELECT version, date
FROM value
WHERE active
  AND thing_id = 2 ;

You could easily write a query or a function which covers both cases, if you need to. But having an extra table doesn't serve any purpose in my opinion.


Oracle - impdp - import a partition I truncated a partition, and would like to import it from backup.

How can I import a partition from the dump file?

Export log:

. . exported "XY_PROD"."XY_LOG_PLSQL"              69.16 MB  320459 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P25129"  2.198 GB   67804 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P26385"  973.4 MB   31742 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P25395"  2.184 GB   88242 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P25891"  3.366 GB  106484 rows

Tried:

INCLUDE=PARTITION:\"IN \(\'SYS_P25129\'\)\"
INCLUDE=PARTITION:"XY_RESPONSE":"SYS_P25129"
INCLUDE=PARTITION:\"XY_RESPONSE\":\"SYS_P25129\"

Getting:

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00920: invalid relational operator

The whole table is several GB. If there is a way, I don't want to import whole table.

According to this example in the Oracle Docs:

TABLES=XY_RESPONSE:SYS_P25129

should be sufficient.

Unless you have a very strange collation in your database, no quoting is required for any of these arguments.


Creating an index in a temporary tablespace I am trying to create an index for a relatively large table (80M rows).

First I got an ORA-01652 error, then I searched in SO and found a related question.

However, trying to implement the steps mentioned there,I got an ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace error.

If index is a permanent object and cannot be created in a temporary tablespace, how should I understand this part of Oracle manual?

Thanks.

Tables and indexes are created in users tablespace, which is USERS by default. A temporary tablespace, TEMP by default, is used by oracle for sorting large amount of data on disk. Temporary tablespace is typically used for processing joins, order by or creating indexes.

Since the default tablespace TEMP is not large enough for creating a large index, your example creates a large temporary tablespace ts_tmp for the index creation purpose only and drops it afterwards.

When creating the index with CREATE UNIQUE INDEX ... TABLESPACE ... command, don't use ts_tmp  in the TABLESPACE clause. This will give you Attempt to create PERMANENT object in a TEMPORARY tablespace error.

Either don't use the TABLESPACE clause, then the defualt users tablespace will be used. Or give a tablespace name that was created with a CREATE TABLESPACE command.


Ideal frequency to gather table statistics in Oracle for database of 1GB in size I want to know what should be the ideal Frequency for Analyzing tables in a schema.
Database size 1GB.

Currently the frequency is set to 7 days, it takes around 40 mins to complete. Data volume is not huge.

Please let me know how can I optimize it?

What all options and to what values they should be set for DBMS_STATS.GATHER_TABLE_STATS.

Here are some issues to consider:

Are you using an appropriate level of estimation in the statistics gathering? People often manually set the estimation to a value without considering whether they can get a similar level of accuracy with a lower amount. A good test is to gather statistics at different estimation percents, starting very low and increasing: eg. 0.1%, 0.5%, 1%, 3%, 6%, 10%. Use the stattab argument to direct the stats results to different tables, and compare them.
Do you need histograms? If you are using an OLTP application then quite possibly not.
How frequently does the data change? Table monitoring can be used to help judge when statistics should be gathered -- if a table changes very rapidly and frequently (eg a temporary table used in data loading) then deleting the stats and locking them can be helpful, as dynamic sampling will do the job for you and you won't waste time in a scheduled job analysing data that you have no use for.

Or just use the automatic option, monitoring it to see if there are situations worth intervening in manually.

Given that these are DAC metadata tables, I'd recommend that you set monitoring on for them and use the automatic statistics gathering job to gather statistics when they're stale: http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41282


Rankings based on "versus" style matchups? Let's say I have an application that, from a pool of items, presents two random results to the user. The user selects one as the "winner" and the result would be recorded like this:

INSERT INTO results (winner_id, loser_id) VALUES (26, 45);

What would be the best way to use the results table to rank the items?

This question has a theoretical/subjective component, i.e., there's more than one way to think about ranking them.

For example, this isn't that much different from, say, a season of baseball. Baseball chooses a winner simply by comparing win percentages. But in baseball you could end up with Cleveland with a better winning percentage than Detroit, yet Detroit beat Cleveland every time they played this year. So from another point of view besides winning percentage, you could say that Detroit is better than Cleveland.

It's that other point of view I'm interested in. If you start with two items (A and B) and know from the results table that B beat A 30 times and lost 5 times, you know B should be ranked above A. Then you look at C, which was 8-10 against B and 12-6 against A, and place it between B and A. And so on.

It seems like I'm describing a fairly simple formula, but when you start extending it, it seems like it would get really computationally heavy for large sets of items, and it would be problematic for an item that doesn't fall between two items that it would be expected to fall between based on the rankings up to that point.

Do you think such a ranking system is feasible? Does this concept already have a name? Would it be possible to set up a system of queries that could reasonably provide results in real-time in a web application for, say, 10,000 items?

This will always have the problem where A beats B, B beats C and C beats A. I don't know of any deterministic ways to define who ranks top in this case. Rock, paper, scissors and similar games exist because of this.

You're likely to get some form of circular dependency like this for any real sport, particularly when looking at a small number of games. There may be some algorithm to resolve this, but I'm not aware of it.

Assuming there is some algorithm to do what you'd like, changing your data model slightly will make it easier to do:

RESULTS (team_a_id, team_b_id, team_a_wins, team_b_wins, ties);

This will reduce the number of records you have to inspect as there will only be one entry for each pair of teams, instead one for each game for each pair of teams.


Can application_name be accessed from a trigger? The PostgreSQL documentation lists a number of System Information Functions which provide state from the current connection, such as inet_client_addr() or pg_backend_pid(). I would like to create an audit trail for updates to a table which includes the application_name, but there does not seem to be a function for accessing this.

The application_name is visible from the pg_stat_activity view and is set by clients in the format of

username:progname@hostname

How can this information be accessed from a trigger?

You can use the function current_setting()[1], eg:

sebastian=# select current_setting('application_name');
 current_setting
-----------------
 psql
(1 row)

Testing a new value:

sebastian=# set application_name to db_overflow;
SET
sebastian=# select current_setting('application_name');
 current_setting
-----------------
 db_overflow
(1 row)

References:

https://www.postgresql.org/docs/current/static/functions-admin.html


Does PostgreSQL support ICU collation's options and settings? ICU Specifies different LDML Collation Settings. Some of them seem pretty interesting, especially the ones on case and accent,

  “Ignore accents”: strength=primary
  “Ignore accents” but take case into account: strength=primary caseLevel=on
  “Ignore case”: strength=secondary
  “Ignore punctuation” (completely): strength=tertiary alternate=shifted
  “Ignore punctuation” but distinguish among punctuation marks: strength=quaternary alternate=shifted potentially a better method of doing what

You can also see these documented here. Are these ICU options and settings possible with PostgreSQL 10 ICU collation support?

CREATE COLLATION special (provider = icu, locale = 'en@strength=primary');
SELECT 'Å' LIKE 'A' COLLATE "special"; # returns false

I've also tried the CLDR BCP47

  Starting with ICU 54, collation attributes can be specified via locale keywords as well, in the old locale extension syntax ("el@colCaseFirst=upper") or in language tag syntax ("el-u-kf-upper"). Keywords and values are case-insensitive. See the LDML Collation spec, Collation Settings, and the data file listing the valid collation keywords and their values. (The deprecated attributes kh/colHiraganaQuaternary and vt/variableTop are not supported.)

For that, this looked right

CREATE COLLATION special (provider = icu, locale = 'en-ks-level1');
SELECT 'Å' LIKE 'A' COLLATE "special"; # returns false

Also tried en-u-ks-level1 That method seems to be what the docs go for,

CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');

Case insensitive or accent-insensitive collations cannot be used prior to PostgreSQL 12, because internally PostgreSQL considers that strings with a different binary representation are not equal. When the collation-aware comparator says they are equal, it uses the non-collation-aware strcmp() function as a tie-breaker, to obtain what Unicode calls a "deterministic" comparison without normalization.

Starting with PostgreSQL 12, collations have a deterministic property, which must be set to false to benefit from equality of non-binary equal strings. From CREATE COLLATION:

  DETERMINISTIC

  Specifies whether the collation should use deterministic comparisons.
  The default is true. A deterministic comparison considers
  strings that are not byte-wise equal to be unequal even if they are
  considered logically equal by the comparison. PostgreSQL breaks ties
  using a byte-wise comparison. Comparison that is not deterministic can
  make the collation be, say, case- or accent-insensitive. For that, you
  need to choose an appropriate LC_COLLATE setting and set the collation
  to not deterministic here.

  Nondeterministic collations are only supported with the ICU provider.

The exact query in the question is not possible with nondeterministic collations because they don't support LIKE or any form of pattern matching (as of PostgreSQL 12).
Also the locale should have a -u- before the collation subtags, otherwise they're going to be silently ignored by the collator.

What does work as intended:

=# CREATE COLLATION special (
     provider = icu, locale = 'en-u-ks-level1', deterministic=false
   );

=# SELECT 'Å' = 'A' COLLATE "special";
 ?column?
----------
 t


SSIS execute task running wscript.exe hangs when started from SQL Server Agent I have a problem with sql server 2012 SSIS package which has execute step running wscript.exe utility.

When I run this package from BIDS everything works fine, when I start package from SSIS it also starts without any problem. But once I start this package as a step in sql agent job - it hangs, and do nothing. The problem is with execute step, once I disable it in package SQL agent executes package fine.

I've tried to add SQL Agent's credentials security rights on folder with wscript.exe utility, I've also tries to add sql server proxy account. But still nothing helped.

Can you please advise how can I fix it?

UPDATE:

Once I run package in Dev Tools everythings works just fine, but running package from sql server job causes wscript.exe process hang in process list in a taskmanager. Once I manually kill this process job stops working.

I assume that this behaviour is somehow related to lack of permissions, but even new credentials didn't work for me. Or maybe wscript utility asks for some promt, though running the same script from command line didn't show any.

Without some detail of error messages it is hard to know for sure what's going wrong, however it sounds like it may well be either a permissions issue or a problem running the script on that host.

When an agent job runs, it will run under the agent service account or the credential used to create the proxy that the step runs as.

This means that the account will need permissions on wscript.exe and the script that wscript.exe runs.  I think my next step would be to do the following test:

create a new credential using your login
use this credential to create a proxy
run the step of the agent job as this proxy

If this doesn't work, try running the script directly on the host where SQL Server is running - the outcome of this would indicate if there is an issue with the script being able to run from that host


When to choose an attribute vs. a new dimension? I am fully aware of what is a fact, attribute and dimension. However, I'm quite confused to which traits I should choose for dimensions vs attributes of that dimension. For instance, I'm building a hospital data warehouse and gender could be a dimension. However, it can also be an attribute of the "patient" dimension.

I am aware that ONE advantage of choosing gender as a dimension is that I can save on space (i.e. there could be millions of patients, and thus millions of attributes). But, what if gender changes? How can I track that? Same with other attributes: diagnoses can be a dimension AND it could be an attrbiute. If I choose it as a dimension, how can I track a patient's flow/change of diagnoses??

Thank you so much

To make gender a freestanding "dimension" is, more likely than not, an attempt to normalize your dimensional model. Unless you have attributes associated with gender then how is it a dimension? Is it possible to have a conformed gender dimension?

As for diagnosis are you modeling the process or the values? Very different requirements and issues to deal with. I would imagine a series of dimensions and facts to accurately keep track of the diagnosis process.

Disk space... considering our modern computing environment, space is your last optimization/design concern. If it truly is, you should know this for certain.

  what if gender changes? How can I track that?

SCD Type 2 Dimension

  how can I track a patient's flow/change of diagnoses?

Transactional or Accumulating Snapshot Fact table(s)