Questions Concerning the Chase Test [5 Marks] Let R(A,B,C,D,E) be decomposed into relations with the following three set of attributes {A,B,C}, {B,C,D}, and {A,C,E}. For each of the following sets of FD's, use the chase test to tell whether the decomposition of R is lossless. For those that are not lossless, give an example of an instance of R that returns more than R when projected onto the decomposed relation and rejoined.

a. A→D, CD→E and E→D.
b. A→D, D→E and C→D.

For the question above, my work for each of the questions is below the concerns. Here are my main concerns:

Does the order in which you use the relations matter?
Can you end up with less tuples with the chase test?
Is my approach correct?

-----------------------Part A below----------------------------------
InitialTableau = T₁ ⋈ T₂ ⋈ T₂
+----+----+----+----+----+
| A  | B  | C  | D₁ | E₁ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₂ | E  |
+----+----+----+----+----+

-----------------------ANSWER TO QUESTIONS START NOW-----------------------
a) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.
+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₁ | E  |
+----+----+----+----+----+

Since we do not have an unsubscribed row, this relation is lossy/not lossless.

Example of an instance R (Were going to use the final tableau):
R₁(A,B,C)
+----+----+----+
| A  | B  | C  |
+----+----+----+
| A₂ | B  | C  |
+----+----+----+
| A  | B₂ | C  |
+----+----+----+

R₂(B,C,D)
+----+----+----+
| B  | C  | D₁ |
+----+----+----+
| B  | C  | D  |
+----+----+----+
| B₂ | C  | D₁ |
+----+----+----+

R₃(A,C,E)
+----+----+----+
| A  | C  | E₂ |
+----+----+----+
| A₂ | C  | E₂ |
+----+----+----+
| A  | C  | E  |
+----+----+----+

After Joining the above relations, we get:
+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D₁ | E₂ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

since we have 2 more rows than the original tableau, this decomposition is not lossless.

b) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.
+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E  |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

Since we have an unsubscribed row, this decomposition is lossless.


SSIS package deployed to SQL Server hangs when running a Powershell script We have a SSIS package that tidies up a few files and folders, and want to automate it to run every night via the SQL Server Agent.
When running the package via Visual Studio (2015 and 2017) the package completes successfully. When the package is deployed to SQL Server 2012, it never gets past the element that runs a Powershell script - it simply hangs forever (3 days is the record so far).
SSIS data flow:

"Run Compression PowerShell Script" task:

The Powershell script is very basic - two lines of code:
$filePathName = $args[0];

Compress-Archive -Path $filePathName -DestinationPath $filePathName

We think it may be a permissions issue, but we're not sure where to progress this further.
Any help is gratefully received.
Thank you

The SSIS element was a red herring. The issue was the execution policy being passed to PowerShell when running the script.

Changing from

  -ExecutionPolicy Unrestricted

to

  -ExecutionPolicy ByPass

has resolved the issue.


Oracle startup error: "end-of-file on communication channel" The Oracle instance I am working with is not coming up after issuing a startup. All I get is the following:

SQL> startup
ORACLE instance started.

Total System Global Area 1.2300E+10 bytes
Fixed Size                  2263088 bytes
Variable Size            1.002E+10 bytes
Database Buffers         3018334336 bytes
Redo Buffers               48056256 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5038
Session ID: 221 Serial number: 3

For ORA-03113: there shouldn't be any network issues, as this session is logged as sysdba on the machine. And this instance has been working for over two months with no problems.

The oracle service is also running and was able to restart.

Environment: Windows 2008 Enterprise SP1, Oracle 11g R2.

I'd really appreciate any direction and help you could offer to troubleshoot and resolve this issue.

Here are the details from alerts.log:

KCF: read, write or open error, block=0xf0412 online=1
        file=25 'C:\ORADATAFILES\ORAPROD03\USERS03.DBF'
        error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 665) The requested operation could not be completed due to a file system limitation'
Errors in file C:\ORACLE\diag\rdbms\ORAPROD03\ORAPROD03\trace\ORAPROD03_dbw1_224.trc:
Errors in file C:\ORACLE\diag\rdbms\ORAPROD03\ORAPROD03\trace\ORAPROD03_dbw1_224.trc:
ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 25 (block # 984082)
ORA-01110: data file 25: 'C:\ORADATAFILES\ORAPROD03\USERS03.DBF'
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 665) The requested operation could not be completed due to a file system limitation
Sun Aug 19 10:26:42 2012
KCF: read, write or open error, block=0xf04e2 online=1
        file=25 'C:\ORADATAFILES\ORAPROD03\USERS03.DBF'
        error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 665) The requested operation could not be completed due to a file system limitation'
Errors in file C:\ORACLE\diag\rdbms\ORAPROD03\ORAPROD03\trace\ORAPROD03_dbw0_5624.trc:
DBW1 (ospiC: 224): terminating the instance due to error 63999

trace file has this error message:

ORA-00449: background process 'DBW1' unexpectedly terminated with error 63999

Now, I understand that the USER03.DBF data file is
somehow inaccessable. But the permissions on the file are "Full Modify" and nothing else is accessing this specific file, like a backup software or antivirus; this is a dedicated oracle box.

Given the the file is not being able to be read or write from, what are my options?
The file size is 27GB, but size on disk is 4GB; I am not sure if this is of any significance. And disk is definatly not out of space.

Autoextend is set at 100MB. And database is about 150GB. Within alerts.log, there were over 50 autoextend notes before the corrupt block error showed up.

O/S-Error: (OS 665) The requested operation could
  not be completed due to a file system limitation

So the windows error 665 is the reason for the error an the error refers to file C:\ORADATAFILES\ORAPROD03\USERS03.DBF.What is the problem with the file? The following article discusses this error message:  What factors can lead to Win32 error 665 (file system limitation)?. If the reason is file fragmentation a patch is available. I know it is to late but maybe a restore will not be necessary in this situation.


What is the best way to unpack JSON objects in SQL Server? Part 2 of my foray into JSON, I'd like to add a user to multiple groups: insert an JSON array into a table.

Ideally, the JSON would look like this:

    '{
    "Email": "WMogh@starfleet.gov",
    "Prefix":null,
    "FirstName": "Worf",
    "MiddleInitial": "",
    "LastName": "Mogh",
    "Suffix": "Son Of",
    "Title" :"Commander",
    "Groups": [{"0", "1", "5"}]
    }'

Currently, I can do it with JSON like this:

    '{
    "Email": "WMogh@starfleet.gov",
    "Prefix":null,
    "FirstName": "Worf",
    "MiddleInitial": "",
    "LastName": "Mogh",
    "Suffix": "Son Of",
    "Title" :"Commander",
    "Groups": "1,2,3,4"
    }'

then "unpack" it with the following ditty:

    declare @groups varchar(1000)

    select @groups = Groups from openjson(@json)
                    WITH
            (

            Groups nvarchar(100)        '$.Groups'
            )

            print @groups

    select value from string_split(@groups, ',')

which returns a nice little table like so:

Value
1
2
3
4

Problem This is bad JSON and the Web developer will make fun of me.

Question How do you propely unpack a JSON array in SQL Server?

I think your JSON definition for arrays might be a little odd, you should be able to unpack an array like so:

DECLARE @json                   VARCHAR( 4000 ) =  '
    {
        "Email": "WMogh@starfleet.gov",
        "Prefix":null,
        "FirstName": "Worf",
        "MiddleInitial": "",
        "LastName": "Mogh",
        "Suffix": "Son Of",
        "Title" :"Commander",
        "Groups": ["0", "1", "5"]
    }';

SELECT  ISJSON( @json );

SELECT  j.value
FROM    OPENJSON( @json, 'strict $.Groups' ) j;

  value

  0

  1

  5

The notation "Groups":[{"0", "1", "5"}] is saying that within an object, there is a key "Groups" which has a value of an array containing a single element.  That  element is also an object, but then - "0", "1", "5" is not meaningful in a JSON context.  For instance:

DECLARE @json                   VARCHAR( 4000 ) =  '
    {
        "Email": "WMogh@starfleet.gov",
        "Prefix":null,
        "FirstName": "Worf",
        "MiddleInitial": "",
        "LastName": "Mogh",
        "Suffix": "Son Of",
        "Title" :"Commander",
        "Groups": [{"A": "0", "B": "1", "C": "5"}]
    }';

SELECT  ISJSON( @json );

SELECT  A = JSON_VALUE( j.value, '$.A' ),
        B = JSON_VALUE( j.value, '$.B' ),
        C = JSON_VALUE( j.value, '$.C' )
FROM    OPENJSON( @json, 'strict $.Groups' ) j

  A B   C

  0 1   5


Transactions within a Transaction What behaviour would PostgreSQL display if for example the script below were called

BEGIN;
SELECT * FROM foo;
INSERT INTO foo(name) VALUES ('bar');
BEGIN; <- The point of interest
END;

Would PostgreSQL discard the second BEGIN or would a commit be implicitly decided on and then run the BEGIN END block at the end as a separate transaction?

What you would need is a so called "autonomous transaction" (a feature provided by oracle). At this point this is not possible in PostgreSQL yet.
However, you can use SAVEPOINTs:

BEGIN;
INSERT ...
SAVEPOINT a;
some error;
ROLLBACK TO SAVEPOINT a;
COMMIT;

It is not entirely an autonomous transaction - but, it allows you get "every transaction" right. You can use it to achieve the thing you expect from autonomous transactions.

Otherwise there are no other reasonable solution at this point.


Reusing covering index for fields in FROM, from another query's index For the following query I have a covering index as so:

SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent;

ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id);

Now I have another query that I would like to add a covering index for:

SELECT user_id FROM foo WHERE bar_id=:bar_id AND date_sent=:date_sent;

Does the previous covering index also cover this new query? When I run EXPLAIN for the new query the following output is returned:

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | ref  | bar_id        | bar_id | 4       | const |    5 | NULL  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+

I see that the key mentioned is bar_id, but that seems to me to show that this is not a covering index. Furthermore, the Extra field does not show the expected Using index output that would assure me that a covering index is in use.

The index IS covering for the second query. However, it is not USEFUL to support the seek. Because neither bar_id nor date_sent is leading the index, the optimiser cannot seek for it.

What you have achieved with the covering index is to make table scan faster for the second query. But you have not supported a better seek strategy.

This covering index would help both queries:

ALTER TABLE foo ADD INDEX (date_sent, user_id, bar_id)

Or if you prioritise the second query:

ALTER TABLE foo ADD INDEX (date_sent, bar_id, user_id)


Revision Tracking & Source Control for Azure SQL Data Warehouse What is a good approaching for tracking incremental changes to database tables, stored procedures, etc for Azure SQL Data Warehouse?

I am in the process of moving a large database over to Azure SQL Data Warehouse. The prior approach for change tracking was using a 'Database Project' in Visual Studio 2015. This allows easy source control integration with TFS or Git or whatever. When you want to publish, you just target the destination database and it generates a change script.

This functionality does not at all work for Azure SQL Data Warehouse. Visual Studio (and the latest SSDT) simply can't target SQL DW. This means the process of publishing is extremely tedious, entirely manual and extremely error prone.

Is there another comparable approach you are using for this type of project?

Microsoft has started work on this and there is a signup page for a preview version. In the meantime you may want to look at DBUp and FiveTran, although I haven't worked with either of these.

Update: As of Aug 2019, it is publicly available in preview.
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-install-visual-studio

You'll want to vote for this thread:

https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/13313247--in-preview-database-project-from-visual-studio-t


Advice on diagnosing a "sometimes" slow query I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds.

Here's an example random execution (note: this isn't a slow one, but the query text is the same apart from the value passed through):

declare @p2 dbo.IdentityType
insert into @p2 values(5710955)
insert into @p2 values(5710896)
insert into @p2 values(5710678)
insert into @p2 values(5710871)
insert into @p2 values(5711103)
insert into @p2 values(6215197)
insert into @p2 values(5710780)

exec ListingSearch_ByLocationAndStatus @statusType=1,@locationIds=@p2

Here's the SPROC:

ALTER PROCEDURE [dbo].[ListingSearch_ByLocationAndStatus]
    @LocationIds IdentityType READONLY,
    @StatusType TINYINT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      -- lots of fields
    FROM        [dbo].[ListingSearchView][a] WITH (NOEXPAND)
    INNER JOIN  @LocationIds [b] ON [a].[LocationId] = [b].[Id]
    WHERE       [a].[StatusType] = @statusType
    OPTION (RECOMPILE);

(note: i added the OPTION (RECOMPILE) hint recently after some advice, but it hasn't helped.

Here's the covering index (note: the view also has a clustered index on ListingId, which is unique)

CREATE NONCLUSTERED INDEX [IX_ListingSearchView_ForAPI] ON [dbo].[ListingSearchView]
(
    [LocationId] ASC,
    [StatusType] ASC
)
INCLUDE ( -- all the fields in the query) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

I put a profiler trace on, with showplan XML statistics.

Here's a slow one (6 seconds), and the relevant plan:

Looks exactly as i'd expect, and is the same plan when the query is fast.

Here's the zoom in on the costly part of the plan, if that helps:

Here is the full schema of the view/backing tables, if that helps: https://pastebin.com/wh1sRcbQ

Notes:

Indexes have been defrag'd, statistics up to date.
Originally query was inline against the view, but i moved to SPROC to try and help stabilize. Hasn't helped.
Adding WITH OPTION (RECOMPILE); hint (didn't work, so can't be parameter sniffing?)
Other queries in the system also sometimes run slow, and they also have no obvious issues in their plan.
Could be locking? Not sure how to confirm.

Any ideas on what i could try next?

Thanks

I really don't think using the OPTION (RECOMPILE) is an effective way to eliminate the possibility of parameter sniffing.

Parameter sniffing happens when SQL is confused about a particular query and thinks its new because it sees new parameters.  It's slow because it's taking extra time to generate a new execution plan.

All that option does is force SQL to produce a new plan every time which is pretty much the same thing. Instead, you might want to consider adding default parameters using this hint:

OPTION(OPTIMIZE FOR(@LocationIds='xx',@StatusType='xx'))

When choosing parameters for the default make sure to use a statistically representative set.
That will force the same plan to be used every time and eliminate the possibility of parameter sniffing.  Once you do that, and determine it didn't help, then its probably safe to dismiss parameter sniffing as a possibility.


Oracle datafile extension .dat or .dbf I have seen these 2 extensions used for datafiles, .dat and .dbf while creating and/or altering a tablespace. I'm not sure what the difference between the 2 extensions is, or if .dat is incorrect.

Here are 2 examples from Oracle Database SQL Reference 10g Release 2 (10.2)

.dat

CREATE TABLESPACE tbs_01
   DATAFILE 'tbs_f2.dat' SIZE 40M
   ONLINE;

.dbf

CREATE TABLESPACE tbs_03
   DATAFILE 'tbs_f03.dbf' SIZE 20M
   LOGGING;

The database engine won't care about the extension, it is just a logical name for humans to understand what they are seeing.


Not able to start mysqld as windows service as well as from command line in windows 8 I recently downloaded mysql-5.7.9-winx64.zip and tried to run mysqld from command line but it does not starts gives me following error.

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

I have updated system path variable to have "D:\MySQL\mysql-5.7.9-winx64\bin"
and added mysql as system service

"D:\MySQL\mysql-5.7.9-winx64\bin" --install

It's still does not start when I restart my system.

Also when I tries to run from services.msc ==> MySQL ==> Start It gives me following error:

MySQL Services on Local Computer started and then stopped. Some services stops automatically if they are not in use by other service or programs.

Connecting from the localhost shouldn't be an issue, but edit your "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" file and change the bind-adress from 127.0.0.1 to the external IP address. Also, set mysql skip-networking to off. Finally, set a rule on windows firewall to allow connections on port 3306.


MYSQL Out of Memory and Table Crashing A specific table in my database keeps crashing. I suspect it to be a bad buffer setting in the my.conf file, because it shows this error before crashing (most of the time it needs to be repaired):

18:26:59 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './myDB/myTable.MYI'; try to repair it
18:54:57 [ERROR] /usr/libexec/mysqld: Out of memory (Needed 59542644 bytes) 141020
18:54:57 [ERROR] /usr/libexec/mysqld: Sort aborted

I don't think it is hard disk related, since tmpdir is set to /tmp/ and has around 48 GB of free disk space.

Input

mysql> SHOW VARIABLES LIKE 'tmpdir';

Output

tmpdir         /tmp/

Here is a look at the my.conf settings. Take special note of the parameter sort_buffer_size which has been set way to high. However, I'm unsure if it will produce this kind of error when set too high. This machine runs Ubuntu with 8 GB RAM.

[mysqld]
max_connections         = 1500
back_log                = 50
skip-locking
key_buffer              = 819M
max_allowed_packet      = 16M
table_cache             = 512
sort_buffer_size        = 2048M
read_buffer_size        = 16M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 64M
thread_cache_size       = 16
query_cache_size        = 16M
thread_concurrency      = 8
max_heap_table_size     = 512M
wait_timeout            = 60
interactive_timeout     = 60

Any thoughts on this issue?

Set in config file this line

max_sort_length=8M

And happy day!


Transform all columns records to lowercase I'm using PostgreSQL 9.1 and I have a users table with a login column.

login names are case-sensitive, for example Bob, MikE, john.  I would like to transform all these records into lowercase.   How can I do that?

You can do this:

UPDATE table_name SET column=lower(column)

Refer to www.postgresql.org/docs/9.1/static/functions-string.html


SSIS Slow Data Transfer between Oracle and SQL Server 2016 I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS.
It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the transfer had been realized with a Linked Server.

I do have the logic done and used the "Native OLE DB\Microsoft OLE DB Provider for Oracle" as well as the "Native OLE DB\Microsoft OLE DB Provider for Oracle" OLEDB connection.

When I run the package, the transfer of just 9,934 rows takes about a minute. Which for a table containing 28,228472 records is to long.

I tried using the "Oracle Source" component with an Oracle Connection Manager.
While I can test the connection, as well as design the process and even preview the result set, as soon as I run it in my Visual Studio, it states the error:
"The component metadata for could not be upgraded to the newer version of the component. The PerformUpgrade method failed."

The Visual Studio is:
Visual Studio 2019 16.2.0

The Visual Studio project compatibility mode is:
SQL Server 2016

The targeted SQL Server is:
SQL Server 2016 (13.0.5233.0)

The package is supposed to be run on an SQL Server:
SQL Server 2016 (13.0.5264.1)

The Oracle server version is:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0  Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Thank you for your help.

My suggestion is don't use SSIS. I have found it to be notoriously slow on transfers from Oracle to MSSQL. I was once presented with an SSIS job that was taking in excess of 4 hours to move less than 1 million rows.  I did an Oracle 10046 trace on the process and found it was doing row-by-row, slow-by-slow processing over a cursor.  I wrote a PL/SQL procedure to do a simple INSERT INTO tablename@sqllvr VALUES (SELECT  FROM oracle_table).  It completed in less than 4 minutes, vs. the 4+ hours for the SSIS method.  It's still running daily, several years later.


Optimize MySQL query_cache_min_res_unit My query cache settings are the following:

mysql> SHOW GLOBAL STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2270     |
| Qcache_free_memory      | 6580864  |
| Qcache_hits             | 12802676 |
| Qcache_inserts          | 2094054  |
| Qcache_lowmem_prunes    | 111676   |
| Qcache_not_cached       | 137257   |
| Qcache_queries_in_cache | 7559     |
| Qcache_total_blocks     | 18240    |
| Queries                 | 15571718 |
| Questions               | 15571715 |
+-------------------------+----------+

As you can see, I have quite high in the value of Qcache_lowmem_prunes, but I still have quite some free memory (6MB out of total 32MB query cache size)

I am looking to see if anyway can further reduce the Qcache_lowmem_prunes.

Should I use a lower value of query_cache_min_res_unit? (Which is 4096 for now)

Your query_cache_min_res_unit is 4KB and you have 6MB of query cache free? Look at this:

6MB = 6144 KB
6144 KB divided by 4KB/query = 1536 queries.

You can potentially fit up 1536 small query results in that 6MB of free space in the query cache. That 6MB is most likely being viewed as memory fragmentation. Why?

According to the MySQL Documentation on Query Cache Configuration

  If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

That being the case, you may as well go for the minimum value for query_cache_min_res_unit: 512. This will save you the step of lowering it multiple times until you get better status results. At the very least, it should reduce the fragmentation.


Which IN-LIST values are not in the table I'm looking for a simple way of seeing which items in my IN-LIST are not actually in the table. It seemed really simple but I think I'm probably over-thinking it.

I have a long list of item codes supplied by a customer and they would like an attribute in the table updating for each of those item codes.
However, I need to let the customer know if any of the codes he has provided don't actually exist in the table.
I've tried doing a COUNT(*) and a GROUP BY and I've tried some variations using MINUS but it always comes down to the fact that if the value isn't in the IN-LIST I can't refer to it.

Example:
Item table has 3 entries

Item_code
12345
98765
65436

User supplies list of

12345
98765
65436
81764

I want to highlight to the customer that the code '81764' is not a valid item code.
I can do this easily with PL/SQL but I would like a SQL solution if possible.

You need to create a list of values which is unfortunately quite cumbersome in Oracle (due to the missing values() clause).

But the following will work:

with id_list as (
  select 12345 as id from dual union all
  select 98765 from dual union all
  select 65436 from dual union all
  select 81764 from dual
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = il.id
where i.item_code is null;

But given the nature of the query this is a bit complicated to parameterize though. The best solution is probably to create a (global) temporary table, then put the list of IDs you receive into that table and use that instead of the common table expression (the with (...) part)

Edit

There is a (cumbersome) way to "explode" a comma separated list of values, which might be "easier" in this case:

with base_value(id_string) as (
  select '12345,98765,65436,81764' from dual
), id_list(id) as (
  select regexp_substr (id_string, '[^,]+', 1, level)
  from base_value
  connect by regexp_substr (id_string, '[^,]+', 1, level) is not null
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = to_number(il.id)
where i.item_code is null;

But that might quickly hit Oracle's limit of 4000 bytes for a string literal....


Why does the CBO not chose a lower cost skip scan without stats? In the script below, the CBO chooses an INDEX FAST FULL SCAN over an INDEX SKIP SCAN (this is illustrated by the queries with the UNION ALL but you can demonstrate it is true with just explain plan for select * from foo where baz=1). This choice is made despite the skip scan being the lower cost choice.

Once the table is analyzed, the skip scan is preferred (despite the relative cost actually rising). It seems the CBO does not consider a skip scan at all with dynamic sampling, is this true? If not, why is the skip scan not chosen before the stats are gathered?

schema:

create table foo( bar integer,
                  baz integer,
                  qux char(99),
                  constraint pk_foo primary key (bar, baz) )
             organization index compress;
--table FOO created.
insert into foo(bar,baz) select mod(level,1000), level from dual connect by level<1000000;
--999,999 rows inserted.
commit;
--committed.

pre-analyze:

explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;
--plan FOR succeeded.

select * from table(dbms_xplan.display);
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   132 | 16764 |  8480  (53)| 00:01:34 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |    44 |  5588 |  4034   (1)| 00:00:45 | <-- why full?
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    44 |  5588 |  4034   (1)| 00:00:45 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    44 |  5588 |   411   (0)| 00:00:05 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
*/

analyze:

analyze table foo compute statistics;
--table FOO analyzed.

post-analyze:

explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;
--plan FOR succeeded.

select * from table(dbms_xplan.display);
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     3 |    30 |  6038  (84)| 00:01:07 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1003   (1)| 00:00:12 | <-- makes sense
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |  4033   (1)| 00:00:45 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1003   (1)| 00:00:12 |
--------------------------------------------------------------------------------
*/

something similar on SQLFiddle here and here

It seems the CBO does not consider a skip scan at all with dynamic
  sampling, is this true?

Actually this is really easy to verify, you can do this by enabling 10053 trace. You will see that the optimizer does not even consider skip scan at all. The reason for this, is the "_optimizer_skip_scan_guess" parameter. The default value for this parameter is FALSE, meaning the optimizer will not consider skip scan when all it has is "guessed" selectivity, which is the case with dynamic sampling.

If you set "_optimizer_skip_scan_guess" to TRUE, skip scan will be considered, this can be also confirmed with the 10053 trace again.

PS: your db_file_multiblock_read_count parameter seems to be lower than the default value. On my 11.2.0.4 sandbox, with the default value of 128, after collecting statistics on the table, index FFS had about one-third of the cost of index SS.

Edit: added output

SQL> create table foo( bar integer, baz integer, qux char(99), constraint pk_foo primary key (bar, baz) ) organization index compress;

Table created.

SQL> insert into foo(bar,baz) select mod(level,1000), level from dual connect by level<1000000;

999999 rows created.

SQL> commit;

Commit complete.

Original:

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1715140356

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    66 |  8382 |  1302   (1)| 00:00:16 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   546   (1)| 00:00:07 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   546   (1)| 00:00:07 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

Enable skip scan for guessed selectivity:

SQL> alter session set "_optimizer_skip_scan_guess"=true;

Session altered.

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3033162421

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    66 |  8382 |   772   (1)| 00:00:10 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   350   (1)| 00:00:05 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BAZ"=1)
       filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement (level=2)

24 rows selected.

Just for completeness:

SQL> analyze table foo compute statistics;

Table analyzed.

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1715140356

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     3 |    30 |  1717   (2)| 00:00:21 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |   357   (3)| 00:00:05 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |   357   (3)| 00:00:05 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1002   (0)| 00:00:13 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

19 rows selected.


Fetching records using some unknown join I have two tables:

1.  Users
2.  Ratings

Users table have columns i.e. id, name etc.

Ratings table have columns i.e. - id, rate_by_id, rate_to_id etc.

I want to display all the ratings in a table. I can do this very easily with a join but the challenge here is that 2 columns of Ratings table point to Users table and according to me join with only one is possible.

By using which JOIN I can join Ratings table to Users table in a single join query with both the columns (rate_by_id and rate_to_id) have the join with Users table.

Please add comment if you need any clarification.

Thanks & Regards

I got my answer. Actually I was looking for JOIN SAME TABLE MULTIPLE TIMES which I resolved from here :

  https://stackoverflow.com/a/12562027/9573341

And for my specific question here is the answer:

SELECT
    ratings.*,
    by_user.id,
    by_user.name,
    by_user.id,
    to_user.name,
FROM
    `rating_table` ratings
LEFT JOIN `users` by_user ON
    ratings.rt_rateBy = by_user.id
LEFT JOIN `users` to_user ON
    ratings.rt_rateTo = to_user.id


How to configure MS SQL log on so that it can be used by another program? Problem on MS SQL Server

Background:
I am about to install an application that asks already in the installation phase about MS SQL database to create its DB inside it. There are options of Windows Authentication and Server Authentication as methods to log on.

Question:
My question is that what settings should I have on Sql Server Configuration Manager to be able to connect my installed program into it?

Those options equal to:

windows authentication -> use a Windows (or domain) user account to connect to the database. Here you need to ensure that the user that runs the application (or the user that is configured to start the service) has access to the SQL Server (if you installed SQL Server and you're also installing the application, than most likely you already have access);
sql authentication -> create a SQL user that has access to the SQL Server and use it inside the application's connection strings. It is not dependent on the Windows/domain users, it's local only to that SQL installation.

If you're installing a new application that you're not familiar with, and it requests SQL Server access, then I suppose you should use Windows Authentication and check that the SQL Server service is started and accepts connections from that user. In SQL Server Configuration Manager you should check that the main SQL service is started and that it has enabled the main client protocols (TCP-IP and shared memory).


Sql Where statement dynamic with case I have a Stored Procedure where I pass a value of 0,1,2.
0= no where
1= Duration >0
2= Duration =0

  select * from Table where case  when @duration =1 then Duration else 1
  end >0

this works however i can't get the other values to work. I've tried 3 case statements but only the last one works.

How can I get a dynamic where statement to work for 3 values?

I wouldn't use a case statement.  Just use boolean logic

select *
  from table
 where (@duration = 0)
    or (@duration = 1 and duration > 0)
    or (@duration = 2 and duration = 0)


MySQL Partitioning: why it's not taking appropriate partition DROP TABLE temp;
          CREATE TABLE `temp` (
         `CallID` bigint(8) unsigned NOT NULL,
         `InfoID` bigint(8) unsigned NOT NULL,
         `CallStartTime` datetime NOT NULL,
         `PartitionID` int(4) unsigned NOT NULL,
         KEY `CallStartTime`(`CallStartTime`)
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
         PARTITION BY HASH (PartitionID)
          PARTITIONS 366

I use EXPLAIN in a sample query I get the next result:

EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = 1

or

 EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = DAYOFYEAR('2013-01-01 10:24:00')

result:

   id   select_type table   partitions  type    possible_keys   key key_len ref rows    Extra
  1         SIMPLE          temp    p1  ALL                 2   Using where

I don't know why it's using the p1 partition. Here parititon start with p0

The HASH partitioning scheme means MySQL translates your arbitrary numerical value into its own hash value. You have defined 366 partitions. What do you think would happen if your query were:

EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = 400

?

Your PartitionID cannot mean in this case the real partition's ID/name, since there is no partition 400.

Now, just between the two of us, you might be interested to learn that MySQL's HASHing function is a simple modulus. Thus, 0 maps to partition p0, 1 maps to partition p1, and 400 maps to partition 34 (== 400-366).

Generally speaking you should not be too interested in the identify of the particular partition being used. You should be more interested to know that there is a good balance of partitions.

If the balance doesn't seem right to you, you might consider using the KEY partitioning scheme, where the hashing algorithm is based on a checksum function, which means values are more "randomly" spread as compared to the HASH modulus partitioning scheme.


How to generate an SQL file with DBMS_DATAPUMP I am trying to generate an SQL file with the DBMS_DATAPUMP api. This tasks is specifically adressed in Oracle support Note 1519981.1 (How To Generate A SQL File Using The DBMS_DATAPUMP API).

When I copy/paste the code found in the mentioned document, it fails on line 5 with a rather obscure

ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3444
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3693
ORA-06512: at line 5

The line in question, that is line 5, is DBMS_DATAPUMP.add_file(...) with the parameter filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE.

I have run the script with a user that has DBA privileges.

I have also created an Oracle directory named MYDIR. The OS directory it points to exists and is writeable.

The complete code that fails is (copied from the note):

DECLARE
  h1 NUMBER;
BEGIN
  h1 := DBMS_DATAPUMP.open(operation => 'SQL_FILE', job_mode => 'SCHEMA', job_name => 'j2');
 DBMS_DATAPUMP.add_file(handle => h1,
   filename => 'scott.dmp',
   directory => 'MYDIR',
   filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 DBMS_DATAPUMP.add_file(handle => h1,
   filename => 'scott.sql',
   directory => 'MYDIR',
   filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE );
 DBMS_DATAPUMP.start_job(handle => h1, skip_current => 0, abort_step => 0);
 DBMS_DATAPUMP.detach(handle => h1);
END;
/

The version is 11.2.0.1.

Ok, I didn't realise that creating such an export file consists of two steps. First, an ordinary dump file must be created, then this dump file is used to create an sql file.

Since I didn't have such a dump file, the script as posted would not work.

Here are the two steps, so that an sql file can be created

declare

  datapump_job number;
  job_state    varchar2(20);

begin

  datapump_job := dbms_datapump.open(
    operation    => 'EXPORT',
    job_mode     => 'SCHEMA',
    remote_link  =>  null,
    job_name     => 'Export dump file',
    version      => 'LATEST'
  );

  dbms_output.put_line('datapump_job: ' || datapump_job);

  dbms_datapump.add_file(
    handle    =>  datapump_job,
    filename  => 'export.dmp',
    directory => 'DATAPUMP_DIR',
    filetype  =>  dbms_datapump.ku$_file_type_dump_file);

  dbms_datapump.start_job(
    handle       => datapump_job,
    skip_current => 0,
    abort_step   => 0);

  dbms_datapump.wait_for_job(datapump_job, job_state);

  dbms_output.put_line('Job state: ' || job_state);

  dbms_datapump.detach(datapump_job);

end;
/

and the script that creates the sql file:

declare

  datapump_job number;
  job_state    varchar2(20);

begin

  datapump_job := dbms_datapump.open(
    operation    => 'SQL_FILE',
    job_mode     => 'SCHEMA',
    remote_link  =>  null,
    job_name     => 'Export SQL file',
    version      => 'LATEST'
  );

  dbms_output.put_line('datapump_job: ' || datapump_job);

  dbms_datapump.add_file(
    handle    =>  datapump_job,
    filename  => 'export.dmp',
    directory => 'DATAPUMP_DIR',
    filetype  =>  dbms_datapump.ku$_file_type_dump_file);

  dbms_datapump.add_file(
    handle    =>  datapump_job,
    filename  => 'schema.sql',
    directory => 'DATAPUMP_DIR',
    filetype  =>  dbms_datapump.ku$_file_type_sql_file);

  dbms_datapump.start_job(
    handle       => datapump_job,
    skip_current => 0,
    abort_step   => 0);

  dbms_datapump.wait_for_job(datapump_job, job_state);

  dbms_output.put_line('Job state: ' || job_state);

  dbms_datapump.detach(datapump_job);

end;


Explain output difference between this inner and natural join A NATURAL JOIN is a JOIN operation that creates an implicit join
  clause for you based on the common columns in the two tables being
  joined. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a
  RIGHT OUTER join. The default is INNER join. Source

So a natural join can be a shorthand way of implementing inner join if both tables have a common column.

Consider following table:

SELECT * FROM t1;
/*
        ID PLANET
---------- --------
         1 jupiter
         2 earth
*/

We join the table to itself:

SELECT first.id, first.planet FROM t1 first INNER JOIN t1 second ON first.id=second.id;
/*
        ID PLANET
---------- --------
         1 jupiter
         2 earth
*/

Try to do the same with natural join notation:

SELECT id, planet FROM t1 NATURAL JOIN t1;
/*
        ID PLANET
---------- --------
         1 jupiter
         1 jupiter
         2 earth
         2 earth
*/

Given that natural join here is implemented using inner join why the multiple rows?

Update:

Running same join with table aliases has different output:

SELECT id, planet FROM t1 first NATURAL JOIN t1 second;
/*
        ID PLANET
---------- --------
         1 jupiter
         2 earth
*/

Oracle database does not understand ANSI join syntax internally (except FULL OUTER JOIN), it rewrites such queries to its own, old join syntax. If you enable optimizer trace, for example:

alter session set events '10053 trace name context forever, level 1';

And run the queries, in the generated trace file, you can view the final form of the query that is executed.

select id, planet from t1 ta natural join t1 tb

is transformed into (BP is my schema):

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TB"."ID" "ID","TB"."PLANET" "PLANET" FROM "BP"."T1" "TA","BP"."T1" "TB"
WHERE "TA"."PLANET"="TB"."PLANET" AND "TA"."ID"="TB"."ID"

And

select id, planet from t1 natural join t1

is transformed into:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."PLANET" "PLANET" FROM "BP"."T1" "T1","BP"."T1" "T1"
WHERE "T1"."PLANET"="T1"."PLANET" AND "T1"."ID"="T1"."ID"

And this is the problem, "T1"."PLANET"="T1"."PLANET" AND "T1"."ID"="T1"."ID".

But don't ask me why, it is just one of those "unexplainable" things in Oracle.

I have seen a case, where the transformation resulted in a similarly ambigiuous final query, and there was an actual error message in the trace file, still, the database returned wrong results and no error to the client.


Postgresql Deadlock - what does "while rechecking updated tuple" mean? Question: Can someone please decipher while rechecking updated tuple for me in the following:

2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:ERROR:  deadlock detected
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:DETAIL:
    Process 31391 waits for ShareLock on transaction 770573675; blocked by process 25788.
    Process 25788 waits for ShareLock on transaction 770573695; blocked by process 31391.
    Process 31391: update my_table row_to_update set col_to_update = now() from (select id from my_table where col_to_update is null order by id limit $4 for no key update) matched_row_to_update where row_to_update.id = matched_row_to_update.id returning row_to_update.id as id, row_to_update.col_to_update as col_to_update, row_to_update.other_col as other_col
    Process 25788: update my_table set col_to_update=$1 other_col=$2 where id=$3
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:HINT:  See server log for query details.
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:CONTEXT:  while rechecking updated tuple (36328,33) in relation "my_table"
2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:STATEMENT:  update my_table row_to_update set col_to_update = now() from (select id from my_table where col_to_update is null order by id limit $4 for no key update) matched_row_to_update where row_to_update.id = matched_row_to_update.id returning row_to_update.id as id, row_to_update.col_to_update as col_to_update, row_to_update.other_col as other_col

I found the it here but couldn't figure it out what exactly is happening there. what is the lock? and how can I debug/resolve it?

To be clear I understand what a deadlock is. I'm asking, based on the error message what is happening, or very simply what the error message means. For example, https://bit.ly/2TaWh6c or https://bit.ly/2Sk0lg0 or https://bit.ly/2E2v9g9 describe different situations with different error message. But I couldn't find anything about the error I'm getting.

Full story:
We have a service (multiple instances working on single DB) which does the following:

Thread #1
1. Fetch some rows from DB to process in transaction #1 (Process 31391).
2. Process fetched rows, update them.
3. Save all processed/updated rows back to DB in transaction #2 (Process 25788).

Thread #2.
1. Periodically check rows in DB which are in process mode for too long and release them by checking and setting col_to_update to null in transaction #3 (since we're using JPA/Hibernate, effectively the query is the same as Process 25788).

Previously we had deadlock errors with the message: while updating tuple (xxx,xxx) in relation my_table between two transactions with the same query (query of Process 31391). We managed to solve it by adding order by id for no key update.

Now we're getting the deadlock above which I don't understand. Even an answer to the following question would help: what's the difference between updating tuple and rechecking updated tuple?

Even an answer to the following question would help: what's the difference between updating tuple and rechecking updated tuple?

I will take you up on this part.  When PostgreSQL finds a row that meets the WHERE clause to be updated, but that tuple has already been modified by a not-yet-committed transaction, it will wait for that transaction to commit and then recheck that the new version of the row still meets the WHERE clause.

So 31391 was wanting to update a tuple that 25788 had already updated (presumably in a prior statement in 25788's transaction), and was waiting for 25788 to finish so it could recheck that it still wanted to update it.

25788 was waiting or 31391 to finish, but we don't know why it was waiting for as it didn't log a CONTEXT line.

Can you tell if 25788 was implementing the 3rd step of thread #1, or is it implementing thread #2?

Does your thread #2 issue its 25788-like query each in its own transaction?


Sequential scan with large sub-query filter "never" ends I'm using postgres 9.2.4, and run the following query:

explain analyze select * from bubu where id not in
     (select bubu_id from kuku limit 33554431);
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on bubu  (cost=913175.15..1041265.77 rows=1761465 width=160) (actual time=37565.575..42569.266 rows=596 loops=1)
   Filter: (NOT (hashed SubPlan 1))
   Rows Removed by Filter: 3511745
   SubPlan 1
     ->  Limit  (cost=0.00..829289.07 rows=33554431 width=8) (actual time=20.528..22363.943 rows=33554431 loops=1)
           ->  Seq Scan on kuku  (cost=0.00..830246.84 rows=33593184 width=8) (actual time=20.528..18741.263 rows=33554431 loops=1)
 Total runtime: 42579.485 ms
(7 rows)

And then the second query, which never ends:

explain analyze select * from bubu where id not in
   (select bubu_id from kuku limit 33554433)

The query without limit also gets stuck

explain analyze select * from bubu where id not in
   (select bubu_id from kuku)

Explain of the stuck query:

explain select * from bubu where id not in
   (select bubu_id from kuku)

                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on bubu  (cost=0.00..2137396495180.43 rows=1761465 width=160)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..1129436.76 rows=33593184 width=8)
           ->  Seq Scan on kuku  (cost=0.00..830246.84 rows=33593184 width=8)
   (5 rows)

The query I actually need is

delete from bubu where id not in
     (select bubu_id from kuku)

Is there any PostgreSQL parameter I can tune to avoid this issue?

Your queries are slow because they in fact do a lot. In your

explain analyze
    select * from bubu where id not in
    (select bubu_id from kuku limit 33554431);

example, the database is fetching up to 33,554,431 rows in undefined order from kuku, doing another scan on bubu to select rows that are not among the fetched 33m+ rows, and then finally fetching and returning rows that fit such a condition.

If you wanna do

delete from bubu where id not in
(select bubu_id from kuku)

then your options are:

Just run it and wait.
Do it in chunks:

Fetch all the IDs that you are going to compare against into a separate table:

CREATE TABLE temp_bubu_ids AS SELECT bubu_id FROM kuku;

Fetch 1000 or so IDs from the IDs table, delete them and then delete rows from bubu that match your condition:

WITH deleted_ids AS (
    DELETE FROM temp_bubu_ids

    -- DELETE doesn't support LIMIT
    WHERE ctid IN (
        SELECT ctid
        FROM temp_bubu_ids
        LIMIT 1000
    )
    RETURNING id
)
DELETE FROM bubu
WHERE id NOT IN (
    SELECT id
    FROM deleted_ids
)

Rinse and repeat until all the appropriate rows get deleted.


SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION') I upgrade Oracle from 11.1.0.6.0 (32bit version) to 11.2.0.1.0 (64bit version) using RMAN on the same Windows computer (64bit).

After RMAN restored and recovered, I then try to run

SQL> startup upgrade;
SQL> @?/admin/catupgrd.sql

I got the following errors:

SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
ERROR at line 1: ORA-01722: invalid number

My timezone version is

SELECT version FROM v$timezone_file;
timezlrg_11.dat VERSION: 11

select * from sys.registry$database;
Microsoft Windows IA (32-bit)
TZ_VERSION: 4

I finally resolve this error by using DBUA but I got errors in this step, then I returned to restore from RMAN again then everything just works


How do I shrink all files quickly for all databases? In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, but I'm looking for something faster.

I scripted some "Create database" scripts and forgot they had ballooned sizes for defaults, and don't need quite that much space reserved for these files on this project.

When you do "Tasks -> Shrink" from the GUI it actually issues a DBCC SHRINKDATABASE command behind the scenes. Try it. When the dialog box comes up, don't click the "OK" button. Instead, click the "Script" button. You'll see the command in a query window. Combine that with a query on sys.databases (leave out master and msdb), and you can make a script to shrink all of the databases.

For example (taken from jcolebrand's comment):

SELECT
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
         sys.master_files mf
    JOIN sys.databases d
        ON mf.database_id = d.database_id
WHERE d.database_id > 4;

Copy the output of that query and run it to shrink all your files.


powershell to connect to SQL and display a friendly message when it cannot connect We check our SQL servers using powershell. The script goes through all servers in a text file and sends the output to a text file when it can connect. But when it cannot connect, it displays error in the PS window and jumps to the second server in list. Problem with this is,when we schedule this script to run (unattended), we don't get a report of any failure in the text file.
What I really want it to do is....try to connect to a SQL server, if it can, then send output to file and if it can't then write something like "Failed to connect to servername" to the file.
Following is the script I am using as an example

function checkSQLRestart([string] $Hostname )
{

    $con="server=$Hostname;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("

            declare @ServerRestartDate datetime
            set @ServerRestartDate = (select crdate from master..sysdatabases where dbid=2)
            if(@ServerRestartDate>GETDATE()-1)
                print @@servername +' was restarted in last 24 hours. Restart date/time = '+cast(@ServerRestartDate as varchar(20))

                                        ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null
    $svr
    $dt|Format-table -AutoSize |Out-File $OutputFile -Append

}

I tried using things like

if($da -eq -1)
{
     write-host "failed to connect"
}

But it didn't work.

Any help please?

You need to try/catch the exception.

$OutputFile="C:\output.txt"
$HostName="MyServer"

try
{
    $con="server=$HostName;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("

            declare @ServerRestartDate datetime
            set @ServerRestartDate = (select crdate from master..sysdatabases where dbid=2)
            if(@ServerRestartDate>GETDATE()-1)
                print @@servername +' was restarted in last 24 hours. Restart date/time = '+cast(@ServerRestartDate as varchar(20))

                                        ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null
    $svr
    $dt|Format-table -AutoSize |Out-File $OutputFile -Append
}
catch [Exception]
{
    // Do something with the exception
    Write-Host $_.Exception.ToString()
}


Why does SQlite return incorrect sum? CREATE TABLE "Test" ("ItemQty" DOUBLE NOT NULL );

Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);

Select Sum(ItemQty)
From Test;

Result: 15.400000000000002

Should be 15.4

The type DOUBLE is a floating point type represented in binary internally. Binary floating point numbers will produce "rounding errors" when converted to base 10. If you want precise numeric calculations with a decimal point, you'll have to use the NUMERIC or DECIMAL type.

To help you understand why floating point types have rounding errors when converting to base 10, please see this article:

https://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples

Also, just for your reference, the NUMERIC or DECIMAL types are going to be orders of magnitude slower at calculations compared to the DOUBLE type.

Also see Data Types in SQLite.


How to split a table in SQL Server into two different related tables inserting foreign key? I have a table OriginTable with the following structure

EventID | Date       | Value1     | Value2     | Value3
-----------------------------------------------------------
5       | 2019-10-07 | MyValue1_1 | MyValue2_1 | MyValue3_1
5       | 2019-10-07 | MyValue1_2 | MyValue2_2 | MyValue3_2
5       | 2019-10-07 | MyValue1_3 | MyValue2_3 | MyValue3_3
6       | 2019-10-07 | MyValue1_4 | MyValue2_4 | MyValue3_4
6       | 2019-10-07 | MyValue1_5 | MyValue2_5 | MyValue3_5
6       | 2019-10-07 | MyValue1_6 | MyValue2_6 | MyValue3_6

where EventID is reinitiated every day, but the combination of EventID and Date values provides a suitable unique identifier.

I want to split this table into two tables FirstDestinationTable and SecondDestinationTable like

ID | EventID | Date
-------------------------
1  | 5       | 2019-10-07
2  | 6       | 2019-10-07

and

FirstDstTblID | Value1     | Value2     | Value3
----------------------------------------------------
1             | MyValue1_1 | MyValue2_1 | MyValue3_1
1             | MyValue1_2 | MyValue2_2 | MyValue3_2
1             | MyValue1_3 | MyValue2_3 | MyValue3_3
2             | MyValue1_4 | MyValue2_4 | MyValue3_4
2             | MyValue1_5 | MyValue2_5 | MyValue3_5
2             | MyValue1_6 | MyValue2_6 | MyValue3_6

where FirstDstTblID references FirstDestinationTable(ID).

The purpose of this task is to save storage space since OriginTable is 120MB each day and I want to execute this on a daily basis.

Thanks.

I do not believe you will save any storage space by splitting the attributes into two tables. INT uses 4 bytes of storage space.  A date uses 3 bytes. Say you compress down a million rows to just one value. ( Ex There are 1 Million rows that have the combination of Event ID 5 and date 2019-10-07). You will save a total of 3 million bytes. That's only 3 megabytes.

You could technically do the same thing you want on the other side of the table; split off every combination of MyValue, create a look up table of them, and then join each on a third table. But don't!

Readability and maintenance are things to consider along side just storage cost. So is your time developing this solution. The current 10/7/2019 cost for Azure SQL database storage (past your 32gb of free data) is $0.138/GB/Month. After 3 years of every week gaining a full GB of data(Total storage is now 156gb), you will have only spent a TOTAL of $1,679.18.


How to start running queries in Oracle? I've just installed the last version of free Oracle (11 XE) and I would like to run some queries. I've also installed the DreamCoder IDE to connect to the Oracle but I cannot run any query. Already trying for 2 hours with no success.
The oracle service is already running.

In the new connection menu in DreamCoder asks for:

username: admin
password: ***

Now I have 2 options:

First: TNS
Database: XE (it's already set by default)
Connect as: [normal, SysDba, SysOper, SysASM] I select the SysOper option.
Instaled clientes - Connect using: XE (the only option available)

Second Direct
Host name: localhost
Port: 1521
Database name: XE (from the TNS)

When I test the connection with the TNS I get connection successful; Then when I really connect I get:

  "NET: Invalid Host";

When I test the connection with the Direct:

  "ORA-01017: Invalid username/password; logon denied"

I really don't know what to do more.

Because probably you haven't created an 'admin' user in Oracle yet. There's no built-in 'admin' user in Oracle DB. Try to connect as SYS "as sysdba", or maybe you need to run some prerequisite install procedure in your DreamCoder IDE.


Using the correct database when calling a system stored procedure in SQL Server 2008 I think there's something I'm missing when it comes to which database is being used when running a custom system stored procedure. I have the following in my stored procedure (edited for brevity):

ALTER PROCEDURE sp_mysp
AS
IF (EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'mytable'))
BEGIN
    --Do stuff to the table
END
ELSE
BEGIN
    PRINT 'Table mytable does not exist'
END

The problem is that when I call the procedure from mydb, the Master table is used for the initial check, rather than the current database. If I write mydb.INFORMATION_SCHEMA.TABLES it works, but that's not really a solution, as it's defeating the whole point of maintaining this as a single SP rather than one SP for each DB.

Any ideas? I tried passing the DB name as a parameter and starting the SP with 'use @db_name', but apparently stored procedures don't allow use statements.

Thanks in advance for all help.

You need to mark your stored procedure as a system object to get the behaviour that you want (full example below tested as working on 2008 SP3)

USE master;
GO

CREATE PROCEDURE dbo.sp_mysp
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from master*/

GO

USE master;
EXEC sys.sp_MS_marksystemobject sp_mysp

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from tempdb*/

This is an undocumented approach and may not work in future versions.


What does "InnoDB stores its tables and indexes in a tablespace" mean? Was reading the mysql engine innoDB tutorial, I came across this sentence

  "InnoDB stores its tables and indexes in a tablespace"

Can any body explain me this what does it means.

Thank you :)

A tablespace is essentially a black box that InnoDB manages allby itself

  tablespace which is essentially a virtual filesystem spanning one or more files on disk.InnoDB uses tablespace for many purpose It keeps its undo log,insert buffer,doublewrite buffer,and other internal structure in the table space

  By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

  Advantages of Per-Table Tablespaces

  You can reclaim disk space when truncating or dropping a table. For tables created when file-per-table mode is turned off, truncating or dropping them creates free space internally in the ibdata files. That free space can only be used for new InnoDB data.

InnoDB File-Per-Table Mode

The InnoDB tablespace


Mariadb is giving warning for this query and table Please create the following table,

CREATE TABLE test3 (

  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test3 (meta_key, meta_value) VALUES
('utm_source', 'facebook'),
('lead_date', '20180915'),
('utm_source', 'facebook'),
('lead_date', '20180905');

then run the following query

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date') as q0
where date(meta_value)<CURDATE()   ;

you will get following warning while running them (use adminer to see them)

Level   Code    Message
Warning 1292    Incorrect datetime value: 'facebook'
Warning 1292    Incorrect datetime value: 'facebook'

Ideally, this should not have happened. Can someone explain what's wrong here?

Update
This query is actually part of a bigger set of queries. It has been reduced to this level to highlight the warnings.
The issue is not with CURDATE. if you write date(meta_value) is NOT NULL it will give warnings.
In fact, it will give warnings with all date related functions in where clause.

It does not seem that it could be an optimization issue.
Try the following queries with a join. At this point, the optimizer cannot optimize since I am comparing with another table.
It will still give a warning.

drop table if exists test3;
CREATE TABLE test3 (

  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test3 (meta_key, meta_value) VALUES
('utm_source', 'facebook'),
('lead_date', '20180915'),
('utm_source', 'facebook'),
('lead_date', '20180905');

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date') as q0 , (select date('20200101') as
compare) as d
where date(meta_value)<compare  ;

Now try another query:

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date' limit 1000000) as q0
where date(meta_value)<CURDATE()  ;

This will not give an error.

You expect the subselect (select meta_value from test3 where meta_key='lead_date') to materialize only rows that contain values in meta_value that can be converted to the DATE data type, but this is not happening: the optimizer inlines the subselect and simultaneously applies two predicates: meta_key='lead_date' and date(meta_value)<CURDATE() to all rows in the table, which obviously fails for rows containing 'facebook' in meta_value.


Enabling mySQL replication using GTID after purging binlog I am currently working to get GTID based replication established between mySQL 5.7.33 on 2 servers. Amidst this work, I ran into a disk space issue and purged the binary logs on the master.
Now I have run into the problem where I cannot get the replication started due to the missing binary logs.
What can I do, without the bin logs, to get replication going?
Thanks for any advice!

Whether you use GTID or not, any replica needs a continuous stream of binlogs to keep in sync.
If any of the binlogs needed for the replica to catch up are gone, then it's trash. It can never catch up.
You need to reinitialize the replica. As if you were creating a new replica: make a new backup from the master, and use it to overwrite the replica. Then the replica can catch up from the point you created the backup.


Which Is the Used Size Unit In DiskSpace Column at Storage Table In Zoneminder Database Trying here, without success change the storage location of Zoneminder events and images, but having trouble with denied access of www-data user, despite have already changed both Zoneminder config and destination directory.

Now seeking here in phpMyAdmin if it could be some sort of database error, once Zoneminder error log acuses zero disk space, even with something about 400Gb free.

My tryout now is change manualy the DiskSpace column in phpMyAdmin, once it could be had been determinated during installation and now it kind of stuck.

Need to know which is the unit used in DiskSpace column, once it acuses the value of 59289474877 and Storage tab in Zoneminder shows 310.4GB of 365.68GB value.

59289474877 Bytes => 55.21 GByte .. pretty much the difference between 365.68 and 310.4

so the unit is Bytes if that helps..


Postgresql: Return Unique Rows I'm using postgresql and I have a large database.  A simplified version of the datastructure for my table looks like this:

id (primary unique key), data, userID
1, foo, 4
2, foobar, 4
3, lala, 5
4, boo, 6
etc...

I want to find all the rows where the userID value is unique in the entire table structure.  So in the above example, it would return the rows for id's 3 and 4.  If the userID is listed in two or more rows, I just want to discard it.

Is this possible in one query?

Thanks in advance.

select *
from the_table
where userid in (select userid
                 from the_table
                 group by userid
                 having count(*) = 1)

Alternatively:

select *
from the_table
   inner join (select userid
               from the_table
               group by userid
               having count(*) = 1) t on t.userid = the_table.userid;

or (possibly more efficient because only a single scan is needed):

select id, data, userid
from (
  select id,
         data,
         userid,
         count(*) over (partition by userid) as cnt
  from the_table
) t
where cnt = 1;


Why decrease the wait_timeout configuration parameter in MySQL? Question
I'm working on an application that uses a connection pool in Apache Tomcat to communicate with MySQL. I was wondering why would you want to use a smaller wait_timeout than the default of 28,800 seconds? I see plenty of downsides to decreasing the wait timeout but what are the upsides?
Background
The parameter wait_timeout is defined as

The number of seconds the server waits for activity on a
noninteractive connection before closing it.

Problem
If I make the wait_timeout too small then MySQL kills connections that are still valid in the connection pool my web application is using. In my situation I am the only person currently using the application at any time so naturally the db connection goes idle when I'm not testing it. I could solve this problem by simply extending the wait_timeout on MySQL to something very large so that even if I'm away from my desk for 2 weeks the pool never returns a dead connection.
QUESTIONS

Why would anyone ever want a shorter wait_time?
Is the overhead of storing connections that bad?
Although I understand connection pools alleviate the overhead of CREATING connections, is it  such an issue to keep idle connections in memory?

You should be just fine extending wait_timeout

Notice the maximum value for wait_timeout for MySQL 5.0, 5.1, 5.5, 5.6

Linux : 31536000 seconds (365 days, one year)
Windows : 2147483 seconds (2^31 milliseconds, 24 days 20 hours 31 min 23 seconds)

These maximums would not exist of mysqld could not handle them.

Connection pooling only saves on overhead in terms the alternative: destroying and reallocating memory for new DB Connections. This could cause the OS on the DB Server to engage in a lot more swapping to juggle memory.

I wrote about this over a year ago : How costly is opening and closing of a DB connection?

Lowering the wait_timeout could definitely throw Apache into confusion because there and PHP and Apache objects for DB Connections still instantiated while the DB Connection decides to terminate early and without warning. In that event, you may have to scan your netstat for any TIME_WAIT on connections over port 3306 or whatever port MySQL is using.

May 20, 2011 : Why is MySQL caching data on our dynamic PHP site?
Aug 17, 2011 : Too Many Connections
Oct 26, 2011 : What could be causing strange query timeouts between PHP and MySQL?
Feb 27, 2012 : MySQL connection count much larger than query count

If such exist and impedes Apache from opening a new connection, you would have to kill the TIME_WAITs yourself. See my Feb 01, 2012 ServerFault post MySQL lowering wait_timeout value to lower number of open connections on how to do so.


Anyone understand why this query no longer works? On Localhost this query works correctly, running normal MySQL:

SELECT COUNT(*) as rows, customers. *
FROM customers
WHERE user_id = 6
      AND age = 10
ORDER BY timestamp DESC LIMIT 0

However on the latest mariadb it throws up this:

SELECT COUNT(*) as rows, customers. *
FROM customers
WHERE user_id = 6
      AND age = 10
ORDER BY timestamp DESC LIMIT 0

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows, customers. * FROM customers WHERE user_id = 6 AND age' at line 1
  Time: 0.011s

Anyone know why this would be?

rows is a reserved keyword in MariaDB.

https://mariadb.com/kb/en/library/reserved-words/

Unless you single- or double- or backquote it:

select id as 'rows' from tbl;
select id as "rows" from tbl;
select id as `rows` from tbl;

db<>fiddle here

As commented by dbdemon, rows is now a reserved word in MySQL too, specifically starting from version 8.0.2. See another db<>fiddle demo here.


Report-builder: How can I show the labels in a List when the dataset is empty? An equivalent question was answered for a tablix, but I would like to do this for a list:
"how to keep the structure of a tablix where there is no data".

I am showing about half a dozen fields in the list, each with their own label (which I want to repeat for each row).
If there are no rows, the user would like to use the report as a "template" that can be filled in manually so I would like to print the labels at least once.

How to keep the structure of the Tablix when there is no data to show

Occurs to me that I can do this by adding an empty record if the select returns no row; is that the best option?


What are the primary differences between NuoDB and MySQL? Since NuoDB can be used as a storage engine for MySQL, what are the primary differences between using the NuoDB as a server and using MySQL as the server?

In addition, recently I've received a recommendation about this new sql database that looks intended to become a very good solution for more flexible scenarios, cloud, etc. The thing is I can't find any information about real tests or any comparison with other database systems. I'm not sure how it differs from the existing uses for MySQL and how it is different from MySQL when used as a standalone database server.

NuoDB is a completely new database system written from the ground up to exploit distributed shared-nothing architectures.  NuoDB's "Emergent" architecture is the opposite of the monolithic approaches to SQL/ACID relational database systems that have dominated the last 3 decades.  In NuoDB it is the aggregate effect of many nodes that electively opt-in and contribute to the system that results in it being a SQL/ACID database.  It behaves like a flock of birds that fly in an organized fashion but without a central point of control or a single point of failure.

The product is not offered as a MySQL storage engine, partly because the MySQL storage engine API is a table-level API and does not allow the flexibility that a database system needs to do what NuoDB does.  You can see a good video overview of the product at http://www.nuodb.com/blog.

On availability and pricing:

The product is designed specifically for web scale applications, supports PHP, Ruby, Hibernate and the usual range of SQL tools/APIs, and runs either on your own hardware or on public clouds.

NuoDB is in late Beta (Beta 6 is due in the next few weeks) with about 250 customers. The product is due to ship in the Spring.

Pricing depends on whether you download it or run it on public clouds. For download it will be free forever for small installations, and will have a subscription fee as you scale out. On public clouds it will be based on the pay-per-use model of the cloud.

Disclaimer: I am the CEO of NuoDB Inc


Schema qualification necessary for query plan reuse? While reading this article about Plan Caching in SQL server, I came across a tidbit I was unaware of:

  ... for reuse it is necessary that the objects that the batch references do not require name resolutions. For example, Sales.SalesOrderDetail does not require name resolution, whereas SalesOrderDetail does because there could be tables named SalesOrderDetail in multiple schemas. In general, two-part object names (that is, schema.object) provide more opportunities for plan reuse.

I'm looking for some clarification about how important it is to use two-part object names, since "In general, two-part object names provide more opportunities for plan reuse.", yet it starts by saying it's necessary.

More specifically, most of the stored procedures I deal with are in the dbo schema and reference only dbo objects, without specifying the dbo prefix.  Are these prevented from reusing cached query plans, even with everything using the default schema?

For a plan to be reused all of the attributes in sys.dm_exec_plan_attributes where is_cache_key=1 must be the same. A list of these is below.

acceptable_cursor_options
compat_level
date_first
date_format
dbid
dbid_execute
is_replication_specific
language_id
merge_action_type
objectid
optional_clr_trigger_dbid
optional_clr_trigger_objid
optional_spid
required_cursor_options
set_options
status
user_id

The one affected by using two part names is user_id

If you try the following under the credentials of a user with default schema dbo

DBCC FREEPROCCACHE;

CREATE TABLE dbo.FooBar(X int);

EXEC('SELECT * FROM FooBar');
EXEC('SELECT * FROM FooBar');

EXEC('SELECT * FROM dbo.FooBar');
EXEC('SELECT * FROM dbo.FooBar');

And then execute the following query

SELECT usecounts,
       text,
       value AS [user_id]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE text LIKE 'SELECT * FROM %FooBar'  and attribute='user_id'

You will see the following results

usecounts   text                                user_id
----------- ----------------------------------- -------
2           SELECT * FROM dbo.FooBar            -2
2           SELECT * FROM FooBar                1

This shows that both plans got re-used when the identical statement got run for the second time. The docs for sys.dm_exec_plan_attributes explain for the user_id

  Value of -2 indicates that the batch submitted does not depend on
  implicit name resolution and can be shared among different users. This
  is the preferred method. Any other value represents the user ID of the
  user submitting the query in the database.

This appears to be incorrect! It seems from my testing that the value it actually uses for the user_id in the second case is the schema_id of the default schema for the executing user rather than an identifier for that specific user. Running the four EXEC statements again under a different login with default schema "dbo" gives.

usecounts   text                                user_id
----------- ----------------------------------- -------
4           SELECT * FROM dbo.FooBar            -2
4           SELECT * FROM FooBar                1

Showing the plans for both versions of the query were able to be re-used between users. Finally running the four EXEC statements again under a third login with default schema "guest" gives.

usecounts   text                                user_id
----------- ----------------------------------- -------
6           SELECT * FROM dbo.FooBar            -2
4           SELECT * FROM FooBar                1
2           SELECT * FROM FooBar                2

Showing that the plan for the dbo qualified query was successfully shared between the users with different default schemas but the non schema qualified query needed a new plan compiled.

If you don't see this sharing happening ensure that all logins you are testing have the same set_options,language_id, date_first,date_format as these are among the cache keys listed at the beginning and any differences in those  will prevent the plans being reused between sessions.


How to update value in inserted row in PostgreSQL? I have a table t1

create table t1 (
  id int primary key not null,
  name varchar(64),
  str2 varchar(64)
);

I want to get the row ID and update a field str2 in this row.

I tried this:

WITH inserted AS (
  insert into t1(id, name) values (38, 'www') returning *
) UPDATE t1 SET str2='aaa' WHERE id IN (SELECT id FROM inserted);
select * from t1;

I expected:

| id | name |   str2 |
|----|------|--------|
| 38 |  www |   aaa  |

But the actual result was:

| id | name |   str2 |
|----|------|--------|
| 38 |  www | (null) |

See this SQLFiddle for a reproduction scenario.

What am I doing wrong?

There's no point in using UPDATE to rows INSERTed in the same statement, as the INSERT could set the missing column in the first place:
insert into t1(id, name, str2) values (38, 'www', 'aaa');

Besides the pointlessness, it doesn't work, as shown in the question.
The reason is mentioned in the doc in 7.8.2. Data-Modifying Statements in WITH:

All the statements are executed with the same snapshot (see Chapter
13), so they cannot "see" one another's effects on the target tables
[...]
Trying to update the same row twice in a single statement is not
supported. Only one of the modifications takes place, but it is not
easy (and sometimes not possible) to reliably predict which one. This
also applies to deleting a row that was already updated in the same
statement: only the update is performed. Therefore you should
generally avoid trying to modify a single row twice in a single
statement. In particular avoid writing WITH sub-statements that could
affect the same rows changed by the main statement or a sibling
sub-statement. The effects of such a statement will not be
predictable.


Get notification about invalid views I have 40 views that are integrated/synced to a work order management system on a weekly basis.

The views are based on GIS tables which are notoriously messy. Over time, the views can end up becoming invalid.

Example:

A user deletes or renames a column in an underlying table, but fails to notify I.T. of the change, so the view is not updated accordingly.
As a result, the view would become invalid: ORA-04063: view "ROADS_VW" has errors or ORA-00904: "FIELD1": invalid identifier.

I would like to catch & fix invalid views (or fix the underlying data) before the views are synced to the work order management system each week.

Is there a way to get Oracle to notify me about invalid views?

For instance, if the integrations occur on Fridays, then get an email on Thursdays if any of the views are invalid.

You can get invalid views with a procedure like this:

DECLARE

    CURSOR InvaliViews AS
    SELECT OBJECT_NAME
    FROM USER_OBJECTS
    WHERE object_type = 'VIEW'
        AND status = 'INVALID';

BEGIN

    FOR aView IN InvaliViews LOOP
        UTL_MAIL.SEND(
            sender => 'noreply@your_domain.com',
            recipients => 'your-email@your_domain.com',
            subject => 'Notification about invalid views',
            message => 'View '||aView.OBJECT_NAME||' is not valid');
    end loop;

end;

You can run such procedure once a week triggered by Scheduler Job


Adding 4th node in MySQL group replication stuck at RECOVERING Environment:

OS: Debian 9

MySQL: 5.7.23 (Running in group replication cluster)

Issue:

The cluster is running fine with 3 mysql nodes in group replication and all are working fine.
While adding a 4th node in the group replication cluster, the 4th node is able to connect to the master and replication starts, however, the 4th node always shows as "RECOVERING".
I checked the logs but there was no error in the logs.

Since the 4th mysql node is able to connect to the other members and able to replicate the data, I am sure that there is no issue in the mysql config.

One of the suspicion is that it is somewhere related to the binlog events. If I check the count of 'SHOW BINGLOG EVENTS', it shows almost 141957 rows, however, the other nodes are having haardly 3-4 rows.

mysql> SHOW BINLOG EVENTS;
141957 rows in set (10.74 sec)

Please let me know how can I add the 4th node in mysql group replication cluster.

Finally, I found the issue and fixed it.
The details are provided below:

1) The issue was happening because of the GTIDs executed in the current set of group replication nodes and the binlog configurations. Be default, the binlog expiration days in the current MySQL config was set to 0 and it means, it can store lot of binlog files which are basically used for row transactions and group replication.
I purged the old binlog files and set the binlog expiration days to '3'. The commands used for that purpose.

mysql> PURGE BINARY LOGS TO 'binlog.000105';
mysql> SET GLOBAL expire_logs_days = 3;

2) After running the above commands on the MASTER server, I deleted all the old binlog files and now the replication should take long time. However, after purging the old binlog files, the MASTER cannot set the other nodes as the node connections to MASTER events were being purged which were in the older binlog files. Hence, we need to do some manual tasks.

3) Take the mysqldump of all databases from the current MASTER and restore it on the 4th mysql node you want to add.

# mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --port=mysql_port --user=username --password=password > dump.sql
# mysql -u root -ppassword < dump.sql

4) Once the restore gets completed, make sure that replication configs are already done in mysql configurations of all the nodes and then restart the node.

5) Restart replication on the 4th node and then replication should gets complete in few minutes or hours (Depends on the database size)

mysql> START GROUP_REPLICATION

6) Once the 4th node shows ONLINE, the node is part of the group replication cluster and can be used for other purposes.


index rebuild/reorganize frequency I am a dba by accident. I am doing index  rebuild/reorganize. I would love to use some scripts once I become comfortable doing these tasks. I did go through some of the forum pages regarding this topic. I have a sql server 2008R2 instance which has around 25 databases of different size and needs. I have been assigned to do index tuning for 8 databases. Since the size and the fragmentation level and the no of indexes and also the rate at which de-fragmentation happens differs from database to database, I understand that some databases need an index maintenance on a monthly basis and few others on a weekly basis. I have two questions:

How frequently should I schedule for the rebuild/reorganize process?
For eg: Last month I rebuilt indexes for fragment level above 30% and reorganized and updated stats for 5-30% fragment level. Then I checked it after a week, i found one or two fragmented indexes, I checked it after a month and I found roughly around 10 fragmented indexes..is it the right time to schedule for an index maintenance again?

If I develop a common script for the instance, how should I make it work for all the databases on the instance, because not every database would need an index maintenance at the same time.

Don't re-invent the wheel; just get a solution like Ola's in place now:

Ola Hallengren's SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

You can tweak the settings and learn how it works over time.


viewing a column in mysql database using php script I'm trying to print a row from a table in mysql using a PHP script - here is the code but its not working - it shows Bad query apparently there is no issue:

$conn = mysqli_connect($servername, $username, $password, $db);
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

else{

echo "Connected successfully";

}

$any = "show columns from help_category";
$chk = mysqli_query($any) or die("Bad Query: $sql");
echo"
";
echo"
Type
";

while($row = mysqli_fetch_assoc($chk))

{

echo"
{$row['Type']}
";

}

echo"
";

I have checked your code and i found out that you have issue with your query execution on php function mysqli_query

mysqli_query requires two parameter first one is connection object and second one is query variable to execute a query.

Below is exact code:-

$conn = mysqli_connect($servername, $username, $password, $db);
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

else{

echo "Connected successfully";

}

$any = "show columns from transaction";
$chk = mysqli_query($conn, $any) or die("Bad Query: $sql");
echo"
";
echo"
Type
";

while($row = mysqli_fetch_assoc($chk))

{

echo"
{$row['Type']}
";

}

echo"
";


Mongo import error: exception:read error, or input line too long (max length: 16777216) I have a shareded collection which I'm trying to migrate from an existing production Cluster running v 2.4 to a new one running 2.6.

As both clusters are shareded, I'm using mongoexport from the old and mongoimport to the new so the data will go through the config servers.
The way I'm doing it is the call mongoexport process and pipe the results into mongoimport process.

I'm guessing that the export | import process affects the size of the exported json which fails to be imported back to the new cluster.
Is there any way I can avoid having this problem or even ignore some documents that cause this error? (I'd rather lose a few than lost all 8 million records).

Is there a way to go around this?

Thanks in advance

For data migration (or backup) between MongoDB servers, you should be using mongodump and mongorestore (binary backups) rather than mongoimport/mongoexport (text backups).

Backups (and restores) of sharded collections need to be done through a mongos.

There are several reasons to use mongodump/mongorestore:

binary backups preserve type fidelity in your BSON documents; mongoexport to a text format can lose type information because JSON can only represent a subset of the BSON types
you can dump/restore full databases (rather than being limited to a single collection)
information on index definitions is included so they can be recreated by mongorestore

If you're trying to do a live migration by dumping/exporting records, this is going to be problematic because your dump will not represent a single point in time if there is write activity on the collections you are dumping.

You definitely want to disable the balancer on the source cluster before taking your mongodump, to ensure there are no active data migrations.

You may also want to look into a live migration tool like hydra.


perform non locking select when updating another table We need to perform the following (periodic) query in mariadb:

update A set status='INACTIVE' where status='ACTIVE' and a.b_id in
(select id from B where ... LOCK IN SHARE MODE);

Unfortunately it locks table B for updates and causes deadlock errors for  other transactions on B:

MySQL error code: 1213, SQLState: 40001, Msg: Deadlock found when trying to get lock;
try restarting transaction query: "UPDATE B set ... where id = ..."

We would like to have a lockless, non consistent isolation in the inner select. Is it possible? We perform the query periodically using EVENTs. Can we change the isolation level inside an event?

Our binlog format is binlog_format = mixed

I think I solved it by adding SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. Each event invocation opens a fresh session so changing the session inside an event handler seems to be safe. Here is full code snippet:

CREATE EVENT IF NOT EXISTS MY_EVENT ON SCHEDULE EVERY 1 DAY STARTS '2019-01-02 09:00:00'
    DO BEGIN
      /*  READ UNCOMMITTED should disable read locking during subquery selects */
      SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
      update A set status='INACTIVE' where status='ACTIVE' and b_id in
        (select id from B where ...);
    END;


How Are SQL Server Clients Connecting? I am asking two questions in this post. If I see activity in netstat with port 1433 for an unnamed instance, whose tcp addresses are not enabled, what does that mean?

What is the preferred way to configure a dynamic port that occurs with my second, named instance? Here are the details:

This post is slightly different than my first post, because in the last post I overlooked a critical detail with the default, unnamed instance, which was I could find no evidence that any of the TCP addresses were enabled. Here is some background:
I have a Windows 2016 server with an unnamed and a named MS SQL SERVER instances installed. TCP and Memory connections are turned on for both, until you get to the protocol section for TCP.
Both instances use the same IP address. The unnamed instance uses port 1433; and the named instance uses 49365.
The unnamed instance was installed first, and hence was assigned port 1433. The firewalls on the server and the client (Windows firewall) are set to allow the necessary UDP and TCP traffic.
Here are two pictures denoting most of the port configurations for the unnamed instance. Please note enabled is set to No for all settings, there is no dynamic port, and the port used is 1433.

This database belongs to a vendor, whose clients are able to connect to the database, so I am trying to figure out that happens in the hope that information will help me to connect to the named instance.
I've tried all suggestions in the prior post, read on-line, and my named database was able to do an import from an older SQL Server database, 2008.
Using netstat, I can see port 1433 is in use, so there is no point in co-opting it for the named instance. Should I just pick a port I know won't be used, or is not likely to be used?

I believe we have several misunderstandings here, so lets try to sort them out one by one and see if that clarifies the big picture in the end.
What you refer to as an "unnamed instance" is what we call a default instance. Just as FYI.
It is not the first installed instance that get port 1433. 1433 is a Well Known Port for the default instance. This is from back in the days when we couldn't have more than one instance (pre-SQL Server 2000).
A named instance get a port number from the Windows dynamic range at first startup. "Dynamic" does not mean a different port number at every startup. The assigned port is stored in the registry and is attempted to be used at following startups. "Dynamic" controls what happens if that fails (i.e., the port last used is now busy). "Dynamic" means "get a different port from Windows". The other option means "fail to start SQL server".
What you see in the GUI you posted as an IP being "disabled" is bad GUI presentation. The individual IP sections are being overridden by the bottom-most config (IPAll). Having a port there means all IP addresses are using that port (as far as I understand). Readers are welcome to comment/edit here.
As for your named instance, I suggest you set the port number under IPAll to 0 (and have nothing for the "static port") and re-start your SQL Server. It should now get a new port from the dynamic Windows range.


Duplicate database and keep it up to date - Replication or SSIS? I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally.

We don't need to transform data, just copy from DatabaseA to DatabaseB (but never from DatabaseB to DatabaseA).

Would you set this up under the Replication services or an SSIS job? What are the advantages and disadvantages of each?

The reason I am doing is so I can have a staging application which read and writes to a staging database, but then every night any new data from the live database is pulled in to the staging database.

The live database is updated daily with new events and we want to ensure that the staging environment resembles the live environment as much as possible.

Thanks,
Greg.

I would just backup/restore. This accomplishes the same thing, validates your recovery story, and will easily handle schema/object changes as well - without really putting any undue strain on the existing database (you're already backing it up, right?).  If it were on a different server (or if you ever split them up), I might suggest log shipping as opposed to mirroring or replication, that way you can fiddle with how long between log backups, how long to delay restores, what kind of maintenance window to use, etc. Of course even on a different server a very simple backup/restore will work fine.