Clustered index in ETL (drop - insert - create?) I have a table which is 130 milion rows. Every day I extract data from a source system and insert into this table. The extract is incremental i.e. I only get the new data from the source. The table is a heap with one nonclustered index.
So I am thinking about creating a clustered index. I have read that loading data in clustered table creates page splits and it is better to drop the index, and after the insert recreate the index. Some also seem to say it is better to diable and then rebuild.
I am not sure but I don't think the above advice is solid. Even when I insert into a heap new pages have to be allocated. What makes page splits so bad on a clustered index?
I think perhaps it is better to just insert into the clustered index? It might be slower than to insert in a heap but later it will benefit from a clustered index in joins and calculations.
Is the above advice about dropping and recreating the clustered index a myth?
I would say the overall goal is to improve everything i.e. joins and calculations on the table after the new inserts and possibly maintaining the table and deleting oldest rows in another job and avoiding heap fragmentation. So I can live with inserts becoming slower..
The "inserts" are actually from a "data flow tap" from a ODBC data source in SSIS and they do not happen in a bulk (I think).
Thansk

The perfmon counters [\SQLServer\Databases(*)\Bulk Copy Rows/sec] can help determine whether the SSIS package is performing bulk insert.  You want bulk insert, especially if the underlying table is ever converted to a clustered columnstore.
In your ETL, I'm assuming that each ETL involves X+Y rows, where X is a number of rows that are updates and thus are deleted from the ~130 million row target table and Y are net new rows.  A delete is performed on the target table and then all X+Y rows are inserted.
(It's also possible that this ETL simply inserts Y new rows with each iteration, and deletes X old rows past some cutoff point - eg deletes all rows past 30 days old.  That makes some of this designing/planning easier especially if the ~130 million row target table stays fairly stable in size over time, rather than consistently growing over time.)
It's very important to know whether this schema and process is to be optimized for the ETL, optimized for the non-ETL workloads, or balanced between them.
For some system and workload combinations, ETL is done within an acceptable amount of time just about however it's done.  In such a case, optimization can almost completely focus on the non-ETL workloads.  The maxims then are "write the data how it will be read" and "keep it simple".  No need to worry about the page split behavior (and transaction log ramifications) of a clustered index vs heap, or that clustered index insert will most likely always be a serialization point in the ETL.  It all becomes: if there's a key - either single or combination - that is meaningful for the reporting queries and with a significant number of the table columns, that can be considered as a candidate for the clustered index key.
Fragmentation of both the clustered index (if adopted) and the nonclustered index can be managed according to the same considerations. (But index maintenance is usually more of a consideration for non-ETL workloads than ETL workloads.)  I usually focus on index reorganizations rather than rebuilds, because what I really care about is keeping the pages pretty full.  One reason I like looking at it from that perspective: it is much less expensive to keep track of over time.  I can just watch how many mb each million rows is on average, before and after a reorg.  That can be measured fairly inexpensively, in comparison to using sys.dm_db_index_physical_stats.  There are two inherent advantages to rebuild vs reorg: an index rebuild automatically includes a fullscan stats update, and an index rebuild can be performed in parallel while a reorg is always serial.
Now, what if ETL performance optimization takes precedence over everything else?  Then you don't want a non-clustered index on the heap, unless it's used to make the deletes faster by making a more efficient plan available.  It's possible that the added cost of maintaining that non-clustered index during the ETL inserts is a significant drag on ETL performance, even if it speeds up the deletes.  In that case, consider creating the non-clustered index before the deletes.  And dropping it immediately after the deletes. That way it never has to be maintained with REORG or REBUILD.  And the deletes get the benefit while the inserts don't have to worry about the cost.  But that has to be measured in order to know if it's benefecial.  And the measurement needs to be re-visited as the table grows to 2x, 3x size etc.  Or if the number of deleted rows in each ETL changes signficantly.
If ETL performance takes precedence over everything else, probably don't want a clustered index on that table, either.  Here's one of the performance pitfalls of heavy insert activity into a clustered index: if its estimated at more than 100 rows, its going to be sorted before the insert.  Depending on the size of the insert, the maximum memory grant, and the grant that insert query recieves that may mean tempdb spill - and a big increase in elapsed time for the insert.
All right.  Chances are, this system and workload hint toward performance optimizations needed for both ETL and non-ETL workloads.  Maybe the non-clustered index is used both for the ETL deletes and by reporting queries?
In that case, consider dropping it before the inserts, and creating it after the inserts.
This blog post from Paul White is one I think about a lot.  He describes wide, per-index plans and narrow, per-row plans here.
Optimizing T-SQL queries that change data
26 January 2013
https://www.sql.kiwi/2013/01/optimizing-t-sql-queries-that-change-data.html
Maybe keeping the non-clustered index in place for both deletes and inserts is the best choice for your needs.  Maybe creating before the deletes, and dropping after the deletes.  Maybe dropping before the deletes, and creating after the inserts.  It depends which query plans actually use the index, and the relative cost of creating the index vs keeping it around for the activity.
Similarly, maybe a clustered index will be beneficial to the deletes and to the reporting queries (although if the clustered index is meaningful to the deletes the non-clustered index is less likely to be meaningful to the deletes).  It'll be a net cost to the inserts, though, compared to a heap.
And... yeah, I agree that in general creating a clustered index and then dropping the clustered index in order to manage fragmentation/average fill in the heap is not something to be done on a regular basis.  I'm much more in favor of making sure TABLOCK/TABLOCKX hints are used as needed in delete statements, so page locks are taken and pages emptied by the delete can be deallocated from the heap.
If a clustered index will be created and dropped in order to manage fragmentation of a heap as a once-in-a-while-when-really-needed thing, I recommend taking such an action at a time that the NCI(s) can be dropped before the clustered index is created, and then NCI(s) created again after the clustered index is dropped. Because creating a clustered index on a heap with NCIs will rebuild the NCI(s).  And dropping the clustered index will rebuild the NCI(s) again.  Droppng the NCI(s) beforehand and creating the NCI(s) again at the end reduces that rigamarole by about half.
Hopefully, rather than creating a clustered index just to drop it again, if once-in-a-while intervention is needed on the heap, it can take the form of ALTER TABLE... MOVE.  For that operation, too, I recommend dropping NCI(s) beforehand, and creating NCI(s) afterward.  I like having individual control over the NCI rebuilds rather than having them take place automatically due to an action taken on the underlying heap.
One important variable to keep in mind in evaluating these options: the recovery model of the database in question.  If transaction log operations are a concern either for performance or transaction log space and the database is in simple recovery model, engaging minimally logged operations wherever possible may be a big gain.  If the database is in full recovery model and the txlog is a concern, page splits in a clustered index (or in the NCI for that matter) become a bigger deal.  For full recovery model, the benefit of sort_in_tempdb for index create or rebuild operations increases (unless tempdb is more of a sore spot for the instance than the txlog is for the database).


Stuck at Dynamic SQL execution in a procedure I'm learning PL/SQL and using Oracle 11g as my Database and HR as my schema for practice. There's one scenario where I'm stuck and not sure why it's not working. The requirement is:

"Create a procedure to extract all employees from employees table that are not managers and put them into new table non_managers".

NOTE: It could be done my many ways and one of the ways is by using composite data structures but I haven't reached that chapter so my solution listed below is based on what I've read till now and would like solutions based on these approaches first. But if there's a better way of doing this please do let me know. Probably I'll cover those topics later on during my reading.

As solution I came up with two approaches, approach 1 doesn't work for me but approach 2 works absolutely fine. I need your help on approach 1:

Approach 1: Doesn't work

Write a procedure

The procedure will have a cursor to hold all employees that are not managers
Create a table NON_MANAGERS to hold the data
INSERT every row of the cursor into the NON_MANAGERS table.

Write an anonymous block to run the above procedure.

Approach 2: Works Absolutely Fine

Create the table to hold the result of the procedure.
Write the procedure with below requirements:

create a cursor to hold all employees that are not managers
INSERT each row of the cursor in the NON_MANAGERS table created in step 1

Write an anonymous block to run the above procedure.

Here's my code for approach 1 that's not working:

-- Step 1
CREATE OR REPLACE PROCEDURE not_manager_procedure
IS
  v_SQL VARCHAR2(3000);
  v_insert_data VARCHAR2(3000);
  CURSOR emp_cur
  IS
    SELECT *
    FROM employees
    WHERE employee_id NOT IN
      (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
      );
BEGIN
  v_SQL := 'CREATE TABLE non_managers AS (SELECT * FROM employees WHERE 1=2)';
  EXECUTE IMMEDIATE v_SQL;
  --DBMS_OUTPUT.PUT_LINE();
  FOR emp_row IN emp_cur
  LOOP
    v_insert_data := 'INSERT INTO non_manager VALUES
      (
      '||emp_row.employee_id||',
      '''||emp_row.fiRst_name||''',
      '''||emp_row.last_name||''',
      '''||emp_row.email||''',
      '''||emp_row.phone_number||''',
      '||emp_row.hire_date||',
      '''||emp_row.job_id||''',
      '||emp_row.commission_pct||',
      '||emp_row.manager_id||',
      '||emp_row.department_id||'
      )';
    EXECUTE IMMEDIATE v_insert_data;
    DBMS_OUTPUT.PUT_LINE(v_insert_data);
  END LOOP;
  COMMIT;
END not_manager_procedure;
/

-- Step 2
BEGIN
  not_manager_procedure;
END;
/

The procedure gets complied properly and if I comment the "Execute Immediate v_insert_data;" statement and run the anonymous block in step 2, I can see 89 records in the "DBMS Output" pane in SQL Developer. But as soon as I uncomment that statement and try to run it, it gives me the below error:

Error starting at line : 40 in command -

    BEGIN
      not_manager_procedure;
    END;
    Error report -
    ORA-00936: missing expression
    ORA-06512: at "HR.NOT_MANAGER_PROCEDURE", line 31
    ORA-06512: at line 3
    00936. 00000 -  "missing expression"
    *Cause:
    *Action:

Any help would be good.

There were couple of issues that @vercelli pointed out in the comments. The main issue why the code was not working was that two columns COMMISSION_PCT and DEPARTMENT_ID had NULL values for many records in the cursor. In order to handle those NULL values, I had to use NVL function on these two columns within the INSERT clause as listed below. The code looks like below now:

-- Step 1
CREATE OR REPLACE PROCEDURE not_manager_procedure
IS
  v_SQL VARCHAR2(3000);
  v_insert_data VARCHAR2(3000);
  CURSOR emp_cur
  IS
    SELECT *
    FROM employees
    WHERE employee_id NOT IN
      (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
      );
BEGIN
  v_SQL := 'CREATE TABLE non_managers AS (SELECT * FROM employees    WHERE     1=2)';
  EXECUTE IMMEDIATE v_SQL;
  --DBMS_OUTPUT.PUT_LINE();
  FOR emp_row IN emp_cur
  LOOP
    v_insert_data := 'INSERT INTO non_managers VALUES
      (
      '||emp_row.employee_id||',
      '''||emp_row.first_name||''',
      '''||emp_row.last_name||''',
      '''||emp_row.email||''',
      '''||emp_row.phone_number||''',
      '''||emp_row.hire_date||''',
      '''||emp_row.job_id||''',
      '||emp_row.salary||',
      '||NVL(emp_row.commission_pct, 0)||',
      '||emp_row.manager_id||',
      '||NVL(emp_row.department_id, 0)||'
      )';
    DBMS_OUTPUT.PUT_LINE(v_insert_data);
    EXECUTE IMMEDIATE v_insert_data;
    --DBMS_OUTPUT.PUT_LINE(v_insert_data);
  END LOOP;
  COMMIT;
END not_manager_procedure;
/

-- Step 2
BEGIN
  not_manager_procedure;
END;
/

I'll try to write this code again without using cursor and dynamic SQL as suggested by Vercelli but this requirement was to understand cursor, procedure and dynamic SQL.

Thanks everyone.


Exporting tables from SQL Server I have to export some data from SQL Server 2012. I'm interested in flat file export (i.e.: .csv) but my database has about 500 tables.

Here is an example for you to better understand the situation:

Database: ETTEDB_list
Table1: list_20140101
Table2: list_20140102
.
..
...
TableN: list_20151706

All these tables have the same structure (number of columns, column names, data types).

Is there any way to automatically export all these tables into .csv files for later joining into one ore more BIG .csv files?

You have three options.

Use the gui of SQL Server Management Studio.
Use BCP to export your data in a loop (see here)
Use Powershell and script your export in a loop for example.

Here an example in PowerShell:

 $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'list[_]%'" -database "ETTEDB_list" -serverinstance "YOURSERVER"
 foreach ($Table in $Tables)
 {
     $TableName = $Table["name"]
     write-host -ForegroundColor White "Creating File $TableName.csv"
     invoke-sqlcmd -query "SELECT * FROM $TableName" -database "ETTEDB_list" -serverinstance "YOURSERVER" |export-csv -path c:\scripts\$TableName.csv
 }

You'll need to adjust "YOURSERVER" to the right value and maybe change the path property to the correct folder. Hopefully this will help.


MongoDB SSL Enabling/Configuration I have created self signed Certificated by using openssl and have mongodb.pem(mongodb.key + mongodb.cert) file generated from openssl. Also i have my mongodb-ssl.conf file as follows

storage:
   dbPath: C:\Users\rajshanb\MongoDB\data\db\
net:
   port: 27018
   bindIp: 127.0.0.1
   ssl:
       Mode = requireSSL
       PEMKeyFile = C:\Users\rajshanb\mongodb.pem
       PEMKeyPassword = rajshanb
       CAFile = C:\Users\rajshanb\client.pem

But when i am trying to start the server from terminal :

C:\Users\rajshanb\MongoDB\mongodb\bin>mongod -f C:\Users\rajshanb\mongodb-ssl.conf
Unrecognized option: net.ssl
try 'mongod --help' for more information

I'm getting above mentioned error. Please help me to identify whether i am missing something in configuration.

I am having installed MongoDB version as :

C:\Users\rajshanb>mongod --version
db version v3.4.6
git version: c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5
OpenSSL version: OpenSSL 1.0.1u-fips  22 Sep 2016
allocator: tcmalloc
modules: enterprise
build environment:
    distmod: windows-64
    distarch: x86_64
    target_arch: x86_64

That Mode should be mode, so no Capital first letter.

Those = should be :


Oracle redo log buffer cache vs redo log file While going through the Oracle architecture I got stuck with the below question. Could someone help me answering this?

Why do we need both redo log buffer cache and redo log files? Why can't we write the data directly to redo log files from database buffer cache?

Redo Log Buffers: A log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in the redo entries. Redo entries contain the information necessary to reconstruct or redo changes made to the database by insert, update, delete, create, alter, or drop operations. Redo entries are primarily used for database recovery as necessary.

Redo Log: The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

for more information about database structure please refer to https://docs.oracle.com/en/


Why is InnoDB auto_increment column skipping over large blocks of unused values? We have an active and important InnoDB table with about 8M rows.  We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time.

I turned on query logging while watching for a jump and found no deletions from the table during the time.  (We don't often delete historical data, anyway.)

MySQL version:  mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (i386) using readline 5.1

OS: Centos 6.3

Any ideas how or why this could happen?
Any other information you might need to assess the situation?

Thanks!

This would be expected behavior in a few scenarios that come to mind:

If you you ROLLBACK transactions where you have inserted records into the table, the auto_increment values transiently assigned to those rows you ultimately did not COMMIT the insertion of will not be reused.
If you are using INSERT IGNORE and you have another unique key (in addition to the primary key).  For every row you attempt to INSERT without explicitly specifying the primary key value in the VALUES, the auto_increment gets incremented, and if that row happens to be a duplicate of an existing unique key value, and therefore discarded because of INSERT IGNORE, the auto_increment counter will still have been incremented and those values will never be used.
Similarly, if you are using INSERT ... ON DUPLICATE KEY UPDATE and you have another unique key, the same behavior is observed.  For those rows where a duplicate key value is found and the query is performed as an update, the auto_increment value still increments if the primary key column is not part of the original INSERT and the duplicate key detection results from a conflict against a unique key on another column.


How to atomically replace a subset of table data In PostgreSQL 9.6 I have a table T like this
category | id | data
---------+----+------
A        | 1  | foo
A        | 2  | bar
A        | 3  | baz
B        | 4  | eh
B        | 5  | whatcomesafterfoobarbaz

There is a view V giving me the data for T, so it has columns category, id, data. T is essentially the materialized view for V, except that I need to refresh it with more granularity than "refresh everything".
So I will select from V for example
SELECT * FROM V WHERE category = 'A';

Or
SELECT * FROM V WHERE category = 'A' AND id = 2;

And replace the relevant rows in T with whatever data V gives me. Unfortunately I cannot do a simple UPDATE: asking V eg. for WHERE category = 'A' might give me a totally different set of rows than before. Therefore I need to do this sequence:
DELETE FROM T WHERE <condition>;
INSERT INTO T (SELECT FROM V WHERE <condition>);

<condition> is either WHERE category = ? or WHERE category = ? AND id = ?.
How do I do this so that the following conditions hold?

Reads from rows not satisfying <condition> should be unaffected.
The change should be atomic, meaning reads from rows satisfying <condition> should either see the the old row set or the new row set, not a mix.

Note: unlike this question, I don't want to replace the whole table at once - only the rows affected.
Added details

There are more reads than writes, on the order of 10-100 times more. After each write there will be a read to the adjacent categories. The application is looking at a set of categories, ids and data and updates the data for one or more categories at a time. Right after it will re-fetch those categories and display them, and it must see the fresh data. All the ids are always fetched with "their" category.

Each category will have something like 1-10 ids, there will be tens of thousands of categories.

More details after first answer

Transactions can run concurrently. There can definitely be a case when two transactions start with DELETE FROM T WHERE category = 'A';.

There is a table categories where it's possible to lock rows FOR UPDATE. There also is a table where ids can be locked FOR UPDATE.

RETURNING makes not much sense here as I need to fetch more than just the rows changed. Thus all is simpler with a separate SELECT.

Concurrent reads are not a problem. Writers don't block readers and vice versa in the default READ COMMITTED isolation level. Enclose DELETE and INSERT in a single transaction to make the operation atomic (all applied or nothing).
If there can be multiple transactions trying to write at the same time, that's a game changer. A single transaction protects you from inconsistent updates, but it cannot protect you from race conditions between concurrent transactions: deadlocks.
Say, we have two transactions T1 and T2, and category 'A' has 10 IDs:
T1: DELETE FROM T WHERE category = 'A';
-- starts taking row locks in arbitrary order: id 1,2,3,4,5,6,7 ...
                    T2: DELETE FROM T WHERE category = 'A';
                    -- starts taking row locks in arbitrary order: id 10, 9, 8, ...
T1: wait for T2 to release lock on id 8
                    T2: wait for T1 to release lock on id 7

DEADLOCK.

Postgres detects the deadlock after some time and kills one of the two transactions. (A deadlock error is reported.)
You could switch to SERIALIZABLE transaction isolation. But that's much more expensive and you need to prepare for serialization failures and retry in this case.
Or you can avoid the problem by always deleting rows in identical, deterministic order. Like:
WITH del AS (
   SELECT category, id
   FROM   T
   WHERE  category = 'A'
   ORDER  BY category, id  -- enforce this order in *all* writing queries
   FOR    UPDATE
   )
DELETE FROM T
USING  del
WHERE  T.category = del.category
AND    T.id = del.id;

But typically, there is a more convenient option. If you have a separate table holding unique categories named, say, cat, you can lock the single parent row in cat with:
SELECT * FROM cat WHERE category = 'A' FOR UPDATE;

and then (in the same transaction) write to category 'A' rows in T at will (still encapsulated in a single transaction to avoid intermediary, inconsistent states being visible).  Of course, all writing queries must follow the same protocol. Then, concurrent transactions will wait for the lock on cat before they write to T and everything is groovy ...
In Postgres 9.4 or later consider FOR NO KEY UPDATE instead:

How to perform conditional insert based on row count?

Concerning:

After each write there will be a read to the adjacent categories.

You are aware of the RETURNING clause, right? No need for a separate read, if you just inserted all rows for a given category. Example:

PostgreSQL multi INSERT…RETURNING with multiple columns


Diffrence between relational and unrelational database What is the difference between relational(MySql) and unrelational database(MongoDb)

A relational database (the concept) is a data structure that allows you to link information from different 'tables', or different types of data buckets. A data bucket must contain what is called a key or index (that allows to uniquely identify any atomic chunk of data within the bucket). Other data buckets may refer to that key so as to create a link between their data atoms and the atom pointed to by the key.

A non-relational database just stores data without explicit and structured mechanisms to link data from different buckets to one another.

Check this link to learn more about MongoDb Link 1

Also check this link for more details  Link 2

When should you use MySQL?

If your data structure fits nicely into tables and rows, MySQL will offer you robust and easy interaction with your data. If it's performance that is your concern, there is a good chance you don't really need MongoDB. Most likely, you just need to index your data properly. If you require SQL or transactions, you'll have to stick with MySQL.

When should you use MongoDB?

If your data seems complex to model in a relational database system, or if you find yourself de-normalizing your database schema or coding around performance issues you should consider using MongoDB. If you find yourself trying to store serialized arrays or JSON objects, that's a good sign that you are better off MongoDB. If you can't pre-define your schema or you want to store records in the same collection that have different fields, that's another good reason.


SQL query for getting Count of distinct items along with Total I'm using MySql (hosted locally at xampp) to store sale information

I want the output something like this

Is there a single query that gets me the output in this format or should I write multiple queries to get the intended result?
I'm very new to SQL and I don't know how to implement it or what to look for in the internet.

You need to be looking at GROUP BY and aggregate functions. See https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html for mysql's documentation.
In this case it looks like:

You want to be grouping by Item Name, you can just use the field directly in SELECT then as it is part of the grouping.
For Qty use the counting aggregate (COUNT(*)).
For Total Amount use SUM(Price).
Price could be a little more complicated. If the price for a given item is always the same (so something called Coffee is always 10) then either of the MIN() or MAX() aggregates will do, or you could include Price in the grouping (GROUP BY Item, Price) and not need any function. If the price of a named item can vary then you could use AVG(Price) to output the average, or you could chose to list each item+price combinations (so "2x coffee at 10, total 20, and 1x coffee at 12, total 12").


What hardware to purchase for MySQL server? Details below I am planning to build a basic server machine dedicated to running MySQL for my small business.
What I need the database for...
I will be setting up a database to hold records of registered voters in various states for business purposes. Each table will contain the records for registered voters in one state. The average number of records for each table will be approximately 6 to 7 million records. But the largest one will be approximately 17 million records.
I will only be doing 2 things with these tables. Querying them to search for a single record, then marking that record in a separate field to indicate that the record has been found.
I will have an average of 10 to 20 users (but as many as 100 users) simultaneously running the aforementioned queries. This should result in as few as 100 queries per hour, or as many as 10,000 queries per hour.
These queries need to return results as quickly as possible. Preferably in less than 1 second. Or even close to instantaneous as a user is typing in a search box on the front end.
I need to know what kind of hardware resources I should purchase with the above requirements in mind. What kind of cpu? How many cpus? How many cores? How much ram? How much disk space? How many drives? What kind of drives? Raid configuration? Also, how much internet bandwidth should I need?
Thanks for helping out a noob.

This is really tiny. Test it on a machine you have already. Index the right fields and you should get significantly better than 1 second even on very basic hardware.
cpus - 1 - you are never going to be executing more than a query at once based on usage described
RAM 2G+, but probably not more than 6. Depends a bit on table index types.
Raid - basic low end mirror (RAID1).
Bandwidth - not much at all. You could do this on 64kbps probably.
On a small business, I wouldn't by buying hardware. Use a cloud service if the terms of data usage allows it.


xp_cmdshell Date Parameter - Looks for File create Date of last modify date? While deleting files using xp_cmdshell, does the date column look for file creation date or last modified date?

If you are thinking about using the FORFILES command with the /D parameter, this uses the last modified date. Type FORFILES /? to get more info about this command. See also https://stackoverflow.com/questions/51054/batch-file-to-delete-files-older-than-n-days


SQL insert which updates one table and based on a field value chooses what other table needs updating DB setup: MSSQL
I have a source record coming in via API call. That call is intended to update a system in the database.
The first table is a common table where all systems have some details
SystemId   | SystemName   | Type      | Memory    | Storage  | Etc......
-----------+--------------+-----------+-----------+-----------
92         | Server1      | BlueType  | 16GB      | 10TB     |
96         | Server2      | RedType   | 24GB      | 20TB     |
...
...

Then I have at least 2 tables, each with different fields pertaining to a particular role of the system:
BlueId  | BrokerName   | Threshold | TriggerLevel  | Etc......
--------+--------------+-----------+---------------+-----------
41      | Palooza      | 400       | 4             |
42      | Almamater    | 900       | 3             |
...
...

RedId   | Parent      | MetalCast | Available  | Etc......
--------+-------------+-----------+------------+-----------
87      | Pindle      | Yes       | Yes        |
88      | Feldspar    | No        | No         |
...
...

The source that is providing the data would be submitting it in one statement. That statement would come from a source that knows what "type" it is (red, blue, etc) and would have the appropriate fields with it as it pertains to that type.
For example, if the statement is coming from a "Red" type of system, the statement would come in similar to the following:
SystemName = CrosserTT1
Type = BlueType
Memory = 20GB
Storage = 10TB
BrokerName = PintoCall
Threshold = 250
TriggerLevel = 6
etc....

This means that I need to take this data, update the "Common" table as above with the appropriate fields, determine what the value of "Type" is and based on the type value update the appropriate table with the appropriate fields. In this example above, I'd want to update the common table, and then update the Blue table based on the value of the type. I wouldn't want to even touch the red table in this case.
These are the things I'm wondering:

Is this possible to have an insert statement that in-one-shot can update common, choose which type table to update, then go update that?
Should I have a single table that contains all the fields of all the types and only add data into the columns appropriate to the type?
Should I have a single table INCLUDING COMMON that has all fields of everything system-related and leave null the fields that don't pertain to a record?

I'm trying to determine the best practice is for this kind of thing - for performance and simplicity.
In terms of the insert statement with the example above, would it make more sense to have a Stored Procedure and provide it all the values instead?
Thanks for your valued input.
If you need clarification, please let me know.

What you are looking for is called Trigger.
For example:
https://sqlite.org/lang_createtrigger.html
I've wrote a little sample for sqlite, you could repeat everything on any unix/linux system.
The init.sh:
# cat init.sh

echo "CREATE TABLE 'first_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        type            text DEFAULT 'blue', \
                        blabla          int  DEFAULT 0 \
                        );"

echo "CREATE TABLE 'blue_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 1 \
                        );"

echo "CREATE TABLE 'red_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 2 \
                        );"

echo "CREATE TRIGGER t1 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'blue' \
                BEGIN \
                        INSERT INTO blue_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "CREATE TRIGGER t2 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'red' \
                BEGIN \
                        INSERT INTO red_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "INSERT INTO first_table ( name, type ) VALUES ( 'ololo1', 'red' ), ( 'ololo2', 'blue' ), ( 'ololo3', 'blue' ), ( 'ololo4', 'red' );"

Firstly, here, you are create 3 tables, thereafter 2 triggers, which are fires according to type field, if type is 'red' - fired 1st trigger, if type is 'blue' - another one.
Usage:
# bash init.sh | sqlite3 tmp.db
# echo "SELECT * FROM red_table;" | sqlite3 tmp.db
1|ololo2|2
2|ololo3|2
# echo "SELECT * FROM blue_table;" | sqlite3 tmp.db
1|ololo1|1
2|ololo4|1


MySQL Sleep Process / Task How much "sleep process" is considered bad for a MySQL DB instance?

I'm wondering if the high CPU load usage I see could be caused by the sleep processes.

The DB is an "db.r3.xlarge" instance in AWS.

Sleeping processes take up virtually no resources.  I think I have seen 100, with no obvious detriment.


importing oracle schema I need to import a saved .dmp made with exp in Oracle9i in another server which is a copy of the server where the save was made, the save is from a specific scheme (PERCON) that is the one I want to import in the other server, I am using exp/imp Well it is an Oracle 9i the export command is:

exp system/ssss CONSISTENT=y OWNER=PERCON
FILE=/backup_exp/export_PERCON.dmp log=/backup_exp/export_PERCON.log BUFFER=30720

I would like to know how to import into the copy server since I understand that the method exp launch several errors, is it better to delete the PERCON schema on the copy server before the import? Thank you very much.

The easiest way to import a complete schema to the database would be to drop the existing user/schema beforehand.

A simple:

drop user percon cascade;

...should be enough to have all objects that are related to your schema percon to be dropped before you continue with the import.

When you run the import you could just define the schema to import as suggested in a comment:

imp fromuser=percon touser=percon file=yourdump.dmp log=imp_percon.log

This should import all relevant percon schema/user data. If any errors occur you will find them in the log file.


Impossible to log errors after dooming a transaction within an INSERT-EXEC statement We have a stored procedure that does some work within a transaction, wrapped within a stored procedure that returns some result data using the INSERT-EXEC method of sharing data.

After observing the generic error message "the transaction has been rolled back" floating back up to the UI, we went to check the log tables but found nothing. We ended up having to track down the error through the SQL Server Profiler (which is made excessively difficult by omitting things like line number in the Exception event class). I tracked down our logging failure as the reason in the first paragraph.

How can a failure in such a circumstance possibly be logged?

Here is some sample code demonstrating how it is impossible to write to a table after dooming a transaction while within an INSERT-EXEC statement:

IF OBJECT_ID('TempLog') IS NULL
    CREATE TABLE TempLog
    (
         RowNumber INT NOT NULL IDENTITY(1, 1)
        ,LogMessage VARCHAR(MAX) NOT NULL
        ,TimestampUtc DATETIME NOT NULL DEFAULT(GETUTCDATE())
    )
GO
TRUNCATE TABLE TempLog
GO
IF OBJECT_ID('Failer') IS NOT NULL
    DROP PROCEDURE Failer
GO
CREATE PROCEDURE Failer
AS BEGIN
    BEGIN TRANSACTION;
    SELECT 1 / 0
    --unmatched COMMIT for fun
END
GO
IF OBJECT_ID('Wrapper') IS NOT NULL
    DROP PROCEDURE Wrapper
GO
CREATE PROCEDURE Wrapper
AS BEGIN
    BEGIN TRY
        EXEC Failer
        SELECT 'success'
    END TRY
    BEGIN CATCH
        BEGIN TRY
            INSERT INTO TempLog(LogMessage) VALUES ('test 1')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 1: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 2')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 2: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF XACT_STATE() = -1
                ROLLBACK TRANSACTION;
            IF XACT_STATE() = 1
                COMMIT TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 3')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 3: ' + ERROR_MESSAGE()
        END CATCH
        ;THROW
    END CATCH
END
GO
DECLARE @Result TABLE(RowNumber INT IDENTITY(1, 1), OtherStuff VARCHAR(MAX) NULL)
INSERT INTO @Result(OtherStuff)
EXEC Wrapper

SELECT [@Result]='', *
FROM @Result

IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;
IF XACT_STATE() = 1
    COMMIT TRANSACTION;
GO
SELECT [Trancount] = @@TRANCOUNT
SELECT * FROM TempLog

The final selection statements return @@TRANCOUNT as 0 and nothing in TempLog with these messages:

failed test 2: Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
failed test 3: Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
Msg 8134, Level 16, State 1, Procedure Failer, Line 60
Divide by zero error encountered.

(but somehow the first INSERT statement to TempLog is permitted even though it is within a doomed transaction)

How is it possible to write any sort of log data in such a case? Ideally it goes to the log table as we have an admin GUI which lets us inspect it without bugging anybody to get us direct backend access.

P.S. The management log doesn't include anything from my testing.

P.P.S. I dislike the INSERT-EXEC method, but we have to deal with it sometimes.

There is a way to first write a 'custom' log entry by using raiserror with LOG. It is going to raise the error before the throw, and afterwards we will read from the log using xp_readerrorlog.

It is for sure not going to be pretty, and not really suited for production.

But here it goes:

  IF OBJECT_ID('TempLog') IS NULL
    CREATE TABLE TempLog
    (
         RowNumber INT NOT NULL IDENTITY(1, 1)
        ,LogMessage VARCHAR(MAX) NOT NULL
        ,TimestampUtc DATETIME NOT NULL DEFAULT(GETUTCDATE())
    )
GO
TRUNCATE TABLE TempLog
GO
IF OBJECT_ID('Failer') IS NOT NULL
    DROP PROCEDURE Failer
GO
CREATE PROCEDURE Failer
AS BEGIN
    BEGIN TRANSACTION;
    SELECT 1 / 0
    --unmatched COMMIT for fun
END
GO
IF OBJECT_ID('Wrapper') IS NOT NULL
    DROP PROCEDURE Wrapper
GO
CREATE PROCEDURE Wrapper
AS BEGIN
    BEGIN TRY
        EXEC Failer
        SELECT 'success'
    END TRY
    BEGIN CATCH
        BEGIN TRY
            INSERT INTO TempLog(LogMessage) VALUES ('test 1')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 1: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 2')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 2: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF XACT_STATE() = -1
                ROLLBACK TRANSACTION;
            IF XACT_STATE() = 1
                COMMIT TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 3')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 3: ' + ERROR_MESSAGE()
        END CATCH
 -- Create a custom error message with an identifier for reading from the log (WrapperIssue)
 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @ErrorSeverity INT
 SELECT
  @ErrorMessage = (cast(ERROR_MESSAGE() as nvarchar(2000)) + N' WrapperIssue'),
  @ErrorSeverity = ERROR_SEVERITY()

 -- RaisError with logging to the SQL Server LOG
 RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG
        ;THROW
    END CATCH
END
GO
DECLARE @Result TABLE(RowNumber INT IDENTITY(1, 1), OtherStuff VARCHAR(MAX) NULL)
INSERT INTO @Result(OtherStuff)
EXEC Wrapper

SELECT [@Result]='', *
FROM @Result

IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;
IF XACT_STATE() = 1
    COMMIT TRANSACTION;
GO
SELECT [Trancount] = @@TRANCOUNT
SELECT * FROM TempLog

-- Read from the rror log using a text filter + only for the last minute
-- Credits for query: https://sqlserver-help.com/2014/12/10/sql-internals-useful-parameters-for-xp_readerrorlog/
Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,    -- Don't know
        @LogType = 1,          -- 2 = SQL SERVER
        @Filter1Text='WrapperIssue',     -- First Text Filter
        @Filter2Text=NULL,     -- Second Text Filter
        @FirstEntry=dateadd(minute,-1,getdate()) ,      -- Start Date
        @LastEntry=NULL        -- End Date
Create table #readerrorlog(LogDate datetime2,
                           Processinfo nvarchar(2000),
                           text nvarchar(4000))
-- Get the data from the error log
insert into #readerrorlog
EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'DESC', @InstanceName

-- Finally insert into the TempLog table
INSERT INTO TempLog(LogMessage)
select Text from #readerrorlog

select * From TempLog

drop table #readerrorlog


Trouble using Max() function I have 2 tables: Customer and Billing, Customer contains generic details (name, address, you get the idea) and Billing contains items that are billed.

The end date of when the item gets billed to is variable (some people are on a monthly cycle, others on a 3 months, 6 months 1 year etc.).

In the Billing Item the customer can have a number of packages - some of them are packages that mark them as a Reseller. My current SQL query looks like this:

select customer.addresses_0_firstname AS AdminFName,
       customer.addresses_0_lastname AS AdminLName,
       customer.addresses_0_organisation AS CompanyName,
       customer.addresses_0_address1 AS Address1,
       customer.addresses_0_address2 AS Address2,
       customer.addresses_0_city AS City,
       customer.addresses_0_postcode AS Zip,
       customer.addresses_0_country AS CountryID,
       customer.addresses_0_email AS Email,
       (CASE WHEN billing.lines_0_description like '%Package0%'
             THEN 'False'
             ELSE 'true' END) AS Reseller,
       max(billing.end_date)
from customer
INNER JOIN billing on customer._id = billing.customer_$id
where customer.name='example'
  and (billing.lines_0_description like '%package1%'
       or
       billing.lines_0_description like '%package2%'
       OR
       billing.lines_0_description like'%package3%')
group by customer.addresses_0_firstname,
         customer.addresses_0_lastname,
         customer.addresses_0_organisation,
         customer.addresses_0_address1,
         customer.addresses_0_address2,
         customer.addresses_0_city,
         customer.addresses_0_postcode,
         customer.addresses_0_country,
         customer.addresses_0_email,
         billing.lines_0_description

Which outputs the following:

AdminFName  AdminLName  CompanyName Address1    Address2    City    Zip CountryID   Email           Reseller    (No column name)
Test User   Testing     123         test        Rd          test    0   Test        Test@test.com   true        2012-03-23
Test User   Testing     123         test        Rd          test    0   Test        Test@test.com   true        2012-01-23

Which is a list of all the user data (that we want) but joined to each billing line, as opposed to selecting the end_date with the largest value.

I suspect I've got to do a nested select, but I'm not sure on the logic that I need here.

Suggestions?

You cannot get a single MAX value for end_date while you're grouping on lines_0_description because each billing record will, presumably, have a different description producing a different distinct group and different max end_date values.

You can use a CTE to extract the most recent billing record per customer then join that to your main SELECT to produce the result set you want. Something like below should work (disclaimer: I've guessed at some column names, e.g. _id in the billing table, as I don't know the schema)

;WITH BillingRecords AS (
  SELECT MAX(_id) AS billing_id,
    customer_$id
  FROM billing
  WHERE (billing.lines_0_description like '%package1%'
       or
       billing.lines_0_description like '%package2%'
       OR
       billing.lines_0_description like'%package3%')
  GROUP BY customer_$id
)

  select customer.addresses_0_firstname AS AdminFName,
       customer.addresses_0_lastname AS AdminLName,
       customer.addresses_0_organisation AS CompanyName,
       customer.addresses_0_address1 AS Address1,
       customer.addresses_0_address2 AS Address2,
       customer.addresses_0_city AS City,
       customer.addresses_0_postcode AS Zip,
       customer.addresses_0_country AS CountryID,
       customer.addresses_0_email AS Email,
       (CASE WHEN billing.lines_0_description like '%Package0%'
             THEN 'False'
             ELSE 'true' END) AS Reseller,
       billing.end_date
from customer
INNER JOIN BillingRecords br ON br.customer_$id = customer._id
INNER JOIN billing on billing._id = br.billing_id
where customer.name='example'
  and (billing.lines_0_description like '%package1%'
       or
       billing.lines_0_description like '%package2%'
       OR
       billing.lines_0_description like'%package3%')
group by customer.addresses_0_firstname,
         customer.addresses_0_lastname,
         customer.addresses_0_organisation,
         customer.addresses_0_address1,
         customer.addresses_0_address2,
         customer.addresses_0_city,
         customer.addresses_0_postcode,
         customer.addresses_0_country,
         customer.addresses_0_email


How to have folders in the SSMS 2017 project? I am using SSMS 2017 and SQL server 2017, I have many companies and each company has many databases.

For example. I have five companies:

Company1, Company2, Company3, Company4, Company5

Every company has many databases:

Employees_DB, Products_DB, Customers_DB (we are talking about 15 database)

Each of the previous databases has many tables.

How can I create a folder for each company, so it has all its own databases?

To be the result :

Company1 folder : Employees_DB, Products_DB, Customers_DB

Company2 folder : Employees_DB, Products_DB, Customers_DB

Company3 folder : Employees_DB, Products_DB, Customers_DB

Company4 folder : Employees_DB, Products_DB, Customers_DB

Company5 folder : Employees_DB, Products_DB, Customers_DB

You can create groups in the server registration view... not sure how that fits with your use of the word ‘project’, so whether it’s suitable for you - you need to decide.

Create or Edit a Server Group (SQL Server Management Studio)


Serialize :new in trigger I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new.

What I am looking for is a way to transform the :new object into raw or a custom data type that can be set as the payload of the queue message, in a generic way.

At the moment I'm trying to convert :new to anydata and set that as the payload data type.

I implemented what you are trying to do this way:  the transformation to a user defined object happens in a packaged procedure that is called by triggers.

---all comments and debugging removed to keep it simple----
CREATE OR REPLACE TRIGGER when_close_ini
   AFTER UPDATE OF end_date
   ON YOUR_TABLE
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE

BEGIN
   IF :NEW.end_date IS NULL AND :OLD.end_date IS NOT NULL
   THEN
      my_schema.queue_util.add_file_to_queue ('REOPEN', :NEW.ID, NULL);

   END IF;

END when_close_ini;

----------------an excerpt from the package with debugging removed----
 PROCEDURE add_file_to_queue   (action_in      IN VARCHAR2,
                                d_case_id_in   IN NUMBER,
                                d_other_in     IN VARCHAR2 := NULL)
   IS

      queue_options        SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
      message_properties   SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
      message_id           RAW (16);
      my_message           MY_SCHEMA.file_action;
      err_text             VARCHAR2 (2000);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN

      my_message := my_schema.file_action (action_in, d_case_id_in, d_other_in);
      DBMS_AQ.enqueue (queue_name           => 'MY_SCHEMA.MY_QUEUE',
                       enqueue_options      => queue_options,
                       message_properties   => message_properties,
                       payload              => my_message,
                       msgid                => message_id);
     COMMIT;
   END add_file_to_queue;


Debugging a hanging session/lock We are having a recurring situation where we have 2 one query hanging, and other queries waiting for that one, and our DB grinds to a standstill. I am trying to debug this and am seeking help. The DB is RDS running Postgres 10.6.
I use DBeaver to monitor and admin this DB, and here are the screenshots from the Administer -> Lock Manager in that application. In this case, to resolve this issue, I needed to kill the one session the others are waiting on, but I would like to learn how to debug this deeper to figure out what it is waiting on, and why.

There are multiple copies of the above query because it is being generated by a very frequent cron job, so that is atleast expected.

According to RDS -> Performance insights, this query, and a lot others are spending a lot of time at this point in the lock state relation.
I expect I havent given enough information here. Let me know what I can add.

To debug this problem, first find out what the session that blocks the others is doing:

SELECT state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = 29303;  -- or whoever blocks the others

If the session is in state active, it is a long running query. Use EXPLAIN (ANALYZE, BUFFERS) with the query to find out where it spends its time and how to improve that.

If the state is idle in transactions, you have an application bug that forgot to close a transaction. Since all locks are held until the end of the transaction, this blocks other transactions even though it is doing nothing.

The short-term solution is to use the pg_cancel_backend() function to kill the blocking transactions.

The long-term solution is to fix bugs that keep transactions open and to improve concurrency by using fewer and less heavy locks:

The EXCLUSIVE lock that the transactions in your images place on a table are heavy-handed. In my experience, explicit table locks are hardly ever necessary or indicated. Usually they are a sign that the programmer doesn't understand database transactions well.


How to conduct integrity test on SQL Server database backup file? I was assigned to check the integrity of database backups from sql server every two weeks. I'm new in this field so I don't know how to correctly do this task. If someone(hacker) modified the data inside the database backup, is it a part of the integrity of the file? Is there a way to check it?

There's an old saying...

  The only way to verify a backup is good/usable is to restore it.
  REPEAT – you don’t know you have a good backup until you have restored
  it!

I would highly recommend checking your backups more often than every two weeks - Weekly, at a minimum and preferably more often than that.

At my shop, we have a 'play' server that we use to automate restores of the latest FULL/DIFF of the databases we deem 'important'.  We use Windows Task Scheduler to kick off a bat file which has several SQLCMD steps.  After successful restores, we run full DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file.  Then we email the output txt files to the database group for evaluation.

This process tests TWO things

Can I restore my backups?
Is the data in the backup structurally intact (DBCC)

Here are some sample steps in our bat file

SQLCMD -S Localhost -Q "IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'<YourDatabase>') DROP DATABASE [<YourDatabase>]" -E -b -d master

SQLCMD -S Localhost -Q "EXEC SCUTILITY.dbo.RestoreProdForDBCC" -E -b -d SCUTILITY

SQLCMD -S Localhost -Q "print 'Starting DBCC ' + convert(char,getdate(),121);dbcc checkdb ('<YourDatabase>') WITH ALL_ERRORMSGS,NO_INFOMSGS;print 'Completed DBCC ' + convert(char,getdate(),121)" -E -b -d master -o "F:\DBCC\dbcc_output.txt"

SQLCMD -S Localhost -Q "EXEC msdb.dbo.sp_send_dbmail @profile_name='SqlServerEmailProfile', @recipients='<DatabaseTeam>', @subject='DBCC has completed!', @file_attachments='F:\DBCC\dbcc_output.txt';" -E -b -d msdb

Check out these excellent posts for more information on Database Integrity Checking:

DBCC CHECKDB / Database Integrity

CHECKDB From Every Angle


sp_helpdb -- returns nothing, because db is restoring (log shipping target) SQL Server How can I see where the db files live, etc?

e.g. sp_helpdb output when sp_helpdb itself won't give results because db is in restoring state.

This is SQL Server 2014

If you want to see where the database files are you can use the sys.master_files dmv.


how to design a database's table to store mysql spatial data in a parent child relation? I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon.

Examples:

1. World
1.1 US
1.2 China
1.3 Japan
1.4 South Africa
1.4.1 Pretoria
1.4.2 Cape Town
1.5 India
1.5.1 New Delhi
1.5.2 Chennai

etc.

CREATE TABLE IF NOT EXISTS `geom` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `g` geometry DEFAULT NULL,

  `location` varchar(100) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

select *
from geom
where st_contains(g,(select g from geom where id =3))and id!=3

Your location table should have a self-reference. Use the Polygon1 type for your geography.

eg:

LocationId (Primary key, not null)
LocationSequence (int, not null)
LocationName (string, not null)
parentLocationId (int, can be null)
Geographical data (POLYGON , not null)

So, your table might have:

1001, 1, World, null, ?
1002, 1, US, 1001, ?
1003, 2, China, 1001, ?
1004, 3, Japan, 1001, ?
1005, 4, South Africa, 1001, ?
1006, 1, Pretoria, 1005, ?
1007, 2, Cape Town, 1005, ?
1008, 5, India, 1001, ?
1009, 1, New Delhi, 1008, ?
1010, 2, Chennai, 1008, ?


Query tuning - SQL Server One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was stopped.

I looked at the execution plan in SQL Sentry Plan Explorer - please find the execution plan below.

How can this query be tuned for better performance? Are there any glaring errors in the query? Are there any pointers, blogs posts that will help me improve this query?

Both the servers involved are SQL Server 2005

SELECT A.SETID
,A.CUST_ID
,A.CNTCT_SEQ_NUM
,A.NAME1
,A.TITLE
,C.DESCR
FROM PS_CUST_CONTACT A
,[linksrv].[prodDB].dbo.PS_BO_ROLE Z
,[linksrv].[prodDB].dbo.PS_RD_PERSON B
,[linksrv].[prodDB].dbo.PS_BO_ROLE_TYPE C
WHERE Z.BO_ID = B.BO_ID
AND Z.ROLE_TYPE_ID = C.ROLE_TYPE_ID
AND Z.ROLE_END_DT >= GETDATE()
AND A.EFFDT = (
    SELECT MAX(EFFDT)
    FROM PS_CUST_CONTACT CUST_CONTACT
    WHERE CUST_CONTACT.SETID = A.SETID
        AND CUST_CONTACT.CUST_ID = A.CUST_ID
        AND CUST_CONTACT.CNTCT_SEQ_NUM = A.CNTCT_SEQ_NUM
        AND CUST_CONTACT.EFFDT <= { FN CURDATE() }
    )
AND A.EFF_STATUS = 'A'
AND B.PERSON_ID IN (
    SELECT A1.PERSON_ID
    FROM PS_CONTACT A1
        ,PS_CONTACT_CUST B1
    WHERE A1.EFFDT = (
            SELECT MAX(A_ED.EFFDT)
            FROM PS_CONTACT A_ED
            WHERE A1.SETID = A_ED.SETID
                AND A1.CONTACT_ID = A_ED.CONTACT_ID
                AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE(), 121), 1, 10)
            )
        AND A1.SETID = B1.SETID
        AND A1.CONTACT_ID = B1.CONTACT_ID
        AND B1.EFFDT = (
            SELECT MAX(B_ED.EFFDT)
            FROM PS_CONTACT_CUST B_ED
            WHERE B1.SETID = B_ED.SETID
                AND B1.CONTACT_ID = B_ED.CONTACT_ID
                AND B_ED.EFFDT <= A.EFFDT
            )
        AND A.CNTCT_SEQ_NUM = B1.CNTCT_SEQ_NUM
        AND A.SETID = B1.CUSTOMER_SETID
        AND A.CUST_ID = B1.CUST_ID
    )

To flesh out what Aaron states a little more, linked server performance, particularly for large result sets, for cross-server joins and cross-server subqueries, is often disappointing. If you watch the remote server with Profiler, you may find that the local server bombards the remote server with requests for the fetch of a single row to match join columns. When that happens, the network latency and calling overhead conspire to kill performance.

If you can query local data without too much trouble, that would be best. You might be able to restore a production backup or use SSIS (or even bcp, it still works) to copy data from the production server to some working tables on the local server. Generally SSIS, bcp and similar tactics are faster than linked servers and may help to avoid issues with log file growth.

If you must query the data from a remote server, you may find that rewriting the query so that it uses OPENQUERY() (rather than four-part names) and 'sends' all of the 'remote parts' of the query over to the remote server and then joins the results of that to the local data will be more effective. SQL is supposed to be smart enough to move all of the joins to the remote server, but sometimes it doesn't and OPENQUERY() gives you a method to force SQL to do want you want.

Another, similar, tactic would be to run the 'remote part' of the query first and put the results into a temporary table, (optionally) index the temporary table and then join the 'local part' of the query to the temporary table. Again, this helps you to force SQL to do what it ought to.

It sounds like more work, but SQL may be able to behave more efficiently. As always, watch your data types on the joins and your SARGs or your indexes will be ignored.


How to restore only functions in windows I have taken function backup using following command:

pg_dump -h localhost -u username -fc  -f db_dump dbname

How to list the backup functions and how can I restore those functions to other database in windows?

Version is PostgreSQL 9.6.8

A dump generated with the -Fc option of pg_dump is in the custom format, which implies that it has a Table of Contents, which can be used to extract contents selectively.

As explained in pg_restore manual, the option to output the TOC is -l:

pg_restore -l db.dump > db.list

This produces a file with one line per object, including functions, that can be edited manually to keep only the objects you're interested in.

Then a restore into another database could be done with:

  pg_restore -L db.list db.dump | psql -d otherdatabase [otheroptions]

If you were interested in extracting only a couple of functions, a quicker way could be to use the -P option to pg_restore:

  -P function-name(argtype [, ...]), --function=function-name(argtype [, ...])

  Restore the named function only. Be careful to spell the function name and arguments
      exactly as they appear in the dump file's table of contents.


Microsoft SQL Server 2008 R2 Developer Edition Data Tuning Advisor tool I have Microsoft SQL Server 2008 R2 Developer Edition installed on my machine which has Win7 64 bit OS.
I am not able see Data Tuning Advisor tool in my SSMS. Before this installation my machine already had SQL server 2008 R2 express edition. I upgraded shared components as well but no success.

One thing I noticed while installing developer edition is that there was no option to choose "Management tools - Complete" it was only "Management tools - Basic". Is this an issue?

Can anybody help me getting this tool?

You'll have to completely uninstall the client tools portion of your Express instance first, since side-by-side installations of Management Studio Express and Management Studio are not supported - there is no direct upgrade path, it's either one or the other.


How to left join on groups of data I would like to know how to left join columns for each group of data that is return in a set of data. The template needs to fill in the blanks for each group where there are missing records. Groups 1 and 2 are part of a single query and need to be part of the same result set.

TEMPLATE

Name          Num
------------------------------------
Apples        10
Bananas       20
Oranges       15
Pineapple     5
Grapes        30
Chips         50
Chocolate     6

GROUP 1

Name          Num         Group
------------------------------------
Grapes        3    --     1
Chips         17   --     1

GROUP 2

Name          Num         Group
------------------------------------
Bananas       30   --     2
Oranges       10   --     2

NEW GROUP 1

Name          Num         Group
------------------------------------
Apples        10          1     <-- from template
Bananas       20          1     <-- from template
Oranges       15          1     <-- from template
Pineapple     5           1     <-- from template
Grapes        3           1     <-- same
Chips         17          1     <-- same
Chocolate     6           1     <-- from template

NEW GROUP 2

Name          Num         Group
------------------------------------
Apples        10          2     <-- from template
Bananas       30          2     <-- same
Oranges       10          2     <-- same
Pineapple     5           2     <-- from template
Grapes        30          2     <-- from template
Chips         50          2     <-- from template
Chocolate     6           2     <-- from template

Groups 1 and 2 are stored in different tables. The number of groups is not limited to two. The algorithm is, basically, "return template but overwrite template's num with group's num where it is given." The result needs to be a single result set.

New Group1:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   '1'                   AS Group
FROM Template t
LEFT JOIN Group1 g ON t.Name=G.Name

New Group2:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   '2'                   AS Group
FROM Template t
LEFT JOIN Group2 g ON t.Name=G.Name

The ISNULL is comparable to the COALESCE function, but simpler to type, without breaking fingers

  It needs to be a single result set since the numbers of groups is not limited to two

If your groups are in table Group, a single query would be:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   g.Group               AS Group
FROM Template t
LEFT JOIN Group g ON t.Name=G.Name


Foreign Key off of a UNIONed View I have a view that looks like this:

CREATE view   reference.Test WITH SCHEMABINDING  as

    SELECT        reference.HighlevelTestId as TestId, Name, IsActive,
                  cast(1 as bit) as IsHighLevelTest
    FROM          reference.HighlevelTest

    UNION ALL

    SELECT        LowLevelTestId  as TestId, Name, IsActive,
                  cast(0 as bit) as IsHighLevelTest
    FROM          reference.LowLevelTest

GO

NOTE: HighLevelTestId and LowLevelTestId are guaranteed to never have the same values (no conflicts).

I have another table that looks like this:

CREATE TABLE [Reference].[TestAddition](
    [TestId] [BigInt] NOT NULL,
    [OtherStuff] [bit] NOT NULL,
    ....
)

I would really like to FK my TestAddition table to my Test view on the TestId column (for referential integrity and ease of use with OData).

Is there any way to do that?  (Any way to change my view to make this work?)

The short answer: This is not possible.

The long answer: Problems like this usually arise from a non-optimal database design. In your case you have two types of tests. They are different, but both represent tests. Therefore you should have one table dbo.tests that contains the common attributes (columns) of all tests.

Each test type has specific attributes. that are not shared with other test types. So for each test type you should have an additional table that contains the primary key of dbo.tests with a declared FK relationship, followed by the test type specific attributes.

With that structure you can easily declare a FK relationship between tests and other tables. If the relationship needs to include all test types, the FK points to the dbo.test table. If the relationship needs to include only a specific test type, the FK points to the specific table for that test type.

To make the transition to the new table layout easier, you could create a view for each test type that contains the common columns and the specific columns. Those views then look similar to the tables you currently have. On those views you can create triggers to handle insert, updates and deletes directing the data between the common and the specific tables for each type. That way you could use the view the same way you are using your current tables.


SQL Server UNION ALL query I use a UNION ALL query and it outputs 2 rows for the same date. I want to output only 1 row like the second picture.

Do I have to use IIF, CASE or else command?

The date, time_in, time_out fields are output from other table (main_table) and then I use a left join with the following UNION ALL query.

   (select date, time_in, time_out,leave_start,leave_end,leave_type_id, leave_type_descr
   from   table1

   union all

   select date, time_in, time_out,null,null,leave_type_id, leave_type_descr
   from  table2)

If I understand the task correctly, then try something like

WITH
cte1 AS ( select date,
                 time_in,
                 time_out,
                 leave_start,
                 leave_end,
                 leave_type_id,
                 leave_type_descr
         from table1 ),
cte2 AS ( select date,
                 time_in,
                 time_out,
                 null leave_start,
                 null leave_end,
                 leave_type_id,
                 leave_type_descr
          from  table2 )
SELECT cte1.date,
       cte1.time_in,
       cte1.time_out,
       cte1.leave_start,
       cte1.leave_end,
       COALESCE(cte2.leave_type_id, cte1.leave_type_id) leave_type_id,
       COALESCE(cte2.leave_type_descr, cte1.leave_type_descr) leave_type_descr
FROM cte1
LEFT JOIN cte2
   ON cte1.date=cte2.date
  AND cte1.time_in=cte2.time_in
  AND cte1.time_out=cte2.time_out

Maybe FULL OUTER JOIN and COALESCE() for all output fields with arguments taken from both tables in proper order...


How to optimize query? I need to optimize the following query:

SELECT DISTINCT column1, column2
  FROM tbl_name
 WHERE column3 = 'x'
   AND TRIM(UPPER(column4)) = TRIM(UPPER('y'))
   AND TRIM(UPPER(column5)) = TRIM(UPPER('z'))
 ORDER BY column2

Does not have primary key. It contains only 5 columns.

It is taking about 2 mins when I am using JDBC statement objects for executing this query.

Following are the statments in java:

sql="SELECT DISTINCT column1, column2 FROM tbl_name"+
     "WHERE column3 = 'x'"+
       "AND TRIM(UPPER(column4)) = TRIM(UPPER('y'))"+
       "AND TRIM(UPPER(column5)) = TRIM(UPPER('z')) "+
     "ORDER BY column2"
stmt = conn.prepareStatement(sql);
stmt.executeUpdate();

Table Definition:

CREATE TABLE "NAME"."tbl_name"
  (
    "column1"   VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "column2" VARCHAR2(100 BYTE),
    "column3"         VARCHAR2(50 BYTE),
    "column4"           VARCHAR2(25 BYTE),
    "column5"         VARCHAR2(50 CHAR)
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "DATA" ;
CREATE INDEX "NAME"."tbl_name_IDX" ON "NAME"."tbl_name"
  (
    "column3",
    UPPER(TRIM("column4")),
    UPPER(TRIM("column5"))
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "DATA" ;

Please suggest me.

The function based index you have created uses same functions but in reverse order than your query, this prevent oracle from using this index.

Since WHERE clause of your query contains:

   AND TRIM(UPPER(column4)) = TRIM(UPPER('y'))
   AND TRIM(UPPER(column5)) = TRIM(UPPER('z')

you must create the index in this way:

CREATE INDEX "NAME"."tbl_name_IDX" ON "NAME"."tbl_name"
  (
    column3,
    TRIM(UPPER(column4)),
    TRIM(UPPER(column5))
  )

This still doesn't guarantee that your query will use that index,
that depends also on selectivity of the query, if the query is highly selective (returns only a few rows), then oracle will probably use the index, but when the query is not selective (returns thousands of rows), than the optimiser will probably prefer full table scan over using the index.


creating view on "oracle view" I am using oracle 12c. We have an oracle view ALL_SCHEUDLER_JOBS. As we will loosing the access to this table, I create a view on it as below :

the below function is placed inside a package called PCK_SUP which is in SCH1

function read_scheduler_jobs return t_scheduler_jobs pipelined
    as
    l_scheduler_jobs all_scheduler_jobs%ROWTYPE;
    cursor c
    is select * from all_scheduler_jobs where owner in ('SCH1', 'SCH2');
    begin
      open c ;
      loop
        fetch c into l_scheduler_jobs;
        exit when c%NOTFOUND;
        pipe row(l_scheduler_jobs);
      end loop;
      close c;
      return;
    end read_scheduler_jobs;

create or replace view vis_scheduler_jobs as
select "OWNER","JOB_NAME","JOB_SUBNAME","JOB_STYLE","JOB_CREATOR","CLIENT_ID","GLOBAL_UID","PROGRAM_OWNER","PROGRAM_NAME","JOB_TYPE","JOB_ACTION","NUMBER_OF_ARGUMENTS","SCHEDULE_OWNER","SCHEDULE_NAME","SCHEDULE_TYPE","START_DATE","REPEAT_INTERVAL","EVENT_QUEUE_OWNER","EVENT_QUEUE_NAME","EVENT_QUEUE_AGENT","EVENT_CONDITION","EVENT_RULE","FILE_WATCHER_OWNER","FILE_WATCHER_NAME","END_DATE","JOB_CLASS","ENABLED","AUTO_DROP","RESTART_ON_RECOVERY","RESTART_ON_FAILURE","STATE","JOB_PRIORITY","RUN_COUNT","MAX_RUNS","FAILURE_COUNT","MAX_FAILURES","RETRY_COUNT","LAST_START_DATE","LAST_RUN_DURATION","NEXT_RUN_DATE","SCHEDULE_LIMIT","MAX_RUN_DURATION","LOGGING_LEVEL","STORE_OUTPUT","STOP_ON_WINDOW_CLOSE","INSTANCE_STICKINESS","RAISE_EVENTS","SYSTEM","JOB_WEIGHT","NLS_ENV","SOURCE","NUMBER_OF_DESTINATIONS","DESTINATION_OWNER","DESTINATION","CREDENTIAL_OWNER","CREDENTIAL_NAME","INSTANCE_ID","DEFERRED_DROP","ALLOW_RUNS_IN_RESTRICTED_MODE","COMMENTS","FLAGS","RESTARTABLE","CONNECT_CREDENTIAL_OWNER","CONNECT_CREDENTIAL_NAME" from table(PCK_SUP.read_scheduler_jobs)
readonly;

We have two schemas SCH1 and SCH2. I created the above function and view in SCH1.
If I run "select distinct owner from ALL_SCHEDULER_JOBS", I can see both SCH1 and SCH2.
But, when I do the same from VIS_SCHEDULER_JOBS, I cannot see SCH2. I only see SCH1.

Thanks for your help

Creating this custom view makes no sense. The view ALL_SCHEDULER_JOBS is accessible to everyone. You will not lose access to it, so creating VIS_SCHEDULER_JOBS is just pointless. ALL_SCHEDULER_JOBS lists the jobs the current user can access. You may lose access to the jobs, but not to ALL_SCHEDULER_JOBS view.

ALL_SCHEDULER_JOBS


Equivalent of SQL’s "INNER JOIN USING" in relational algebra I'm following a course in databases (Standford introduction to databases on YT) and, at some point, it's mentioned that t1 INNER JOIN t2 USING attr1, attr2 is equivalent to the NATURAL JOIN symbolized by a bow tie1 in relational algebra.

The INNER JOIN … USING … expression, however, can point out what columns to use for the JOIN, while the bow tie2 operator in relational algebra does not seem to be capable of expressing that.

What is the exact representation of an SQL INNER JOIN … USING … in relational algebra? Can someone give me an example to nudge me in the right direction?

Notes

1, 2 The bowtie with no subscript is condisered the NATURAL JOIN, while the bowtie with the condition as a subscript is considered the THETA JOIN. Here I was refering to the bowtie with no subscript.

Relational natural join is more or less SQL natural join. SQL natural join is defined as SQL inner join using all common columns. (And it is only defined when each argument has exactly one column with each of those names.) If you find what the course actually says about relational vs SQL natural join & inner join with using then it will be that. If the course says that there is an "equivalence" for that expression then it will be because the set of common columns for those tables will be those columns in a particular example.

(To say that relational algebra natural join "is the equivalent of" something in SQL is vague & needs defining. There are many versions of relational algebra that differ in operators available, in how operators with a given name work & even in what a relation is. Meanwhile SQL tables can have NULLs, multiple columns with the same names & multiple rows with the same contents--all typically non-relational.)


MySQL: Why is auto_increment limited to just primary keys? I know MySQL limits auto_increment columns to primary keys. Why is this? My first thought is that it's a performance restriction, since there probably is some counter table somewhere that must be locked in order to get this value.

Why can't I have multiple auto_increment columns in the same table?

Thanks.

Why would you want to have an auto_increment column that is not the primary key?

If you want a column to be an auto_increment, by definition, you are not storing meaningful data in that column.  The only case where storing non-meaningful information makes sense is the special case that you want to have a synthetic primary key.  In that case, the lack of information is a benefit because there is no risk that someone will ever come along in the future and want to change the data because some attribute of some entity changed.

Having multiple auto_increment columns in the same table seems even odder.  The two columns would have the same data-- they're being generated by the same algorithm and being populated at the same time after all.  I suppose you could come up with an implementation where it is possible for them to be slightly out of sync if there were enough concurrent sessions.  But I can't imagine how that would ever be useful in an application.


Merge a table into an existing one I know how to JOIN two tables as create a new table out of the results, using CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ).

I forgot to include some columns from the JOIN in the result_table. Is it possible to merge some of columns from tableA into result_table? Is there a performance improvement over simply dropping and re-creating result_table again?

You are better off dropping and recreating. Why ?
ANALYSIS
Let's take the hypothetical example from your question
CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB );

This is literally a pure Cartesian Product.
Let's say tableA has 2,000 rows and tableB has 5,000. A Cartesian product would result in result_table having1,000,000 rows.
Now let's say you added the column col_1 to tableA.
If you had to fill in tableA into result_table, what would the join look like ?
First, you would have to graft col_1 into result_table.
ALTER TABLE result_table ADD COLUMN col_1;

That's a pass through 1,000,000 (1 million) rows to inject a column.
Then, you have to perform a join of the ugliest kind
UPDATE result_table JOIN tableA
ON result_table.column_key_from_table JOIN tableA.column_key_from_table;

Remember I said joining 2,000 rows to 5,000 rows made result_table 1,000,000 rows ?
To perform the UPDATE JOIN, you would have to create a temp table with 2,000,000,000 rows. Yes, 2 billion rows (1,000,000 times 2,000). The temp table has to be created. Then, you have to perform pass through the 2 billion rows to populate the col_1 column.
CONCLUSION
I think you want to be finished working with the table so you can have time to raise a family, collect your pension, etc.. So, I'd go with the dropping and recreating the table.


Creating an exam center database I am making an app than has an exam center... I am aware of the database normalization concepts...but in my case it looks like I need to store non atomic values in a column. Actually the app will have 2 screens -

Exam packs (that contains a set of questions) e.g. Kids Science, Modern Physics, History etc.
Custom Exams...where one can pick subjects and then conduct an exam.

So... ofcourse there should be a table called questions with

Now I have to fetch some out of these questions if user selects an Exam pack or set of subjects. It was clean till the Exam Pack was into picture. Now I have two options:

Create a columns called questions in exam_packs table that will contain the comma separated ques_ids or an array.
Create separate tables for each exam_pack with questions in them... by doing so..these questions could not be available for users taking exam by subject.

Any suggestions are welcome.

To explore a bit more about the question and comment, I want to note that generally when normalizing your goal (among others) is to reduce duplication.

A naive example of your table might be everything you have but adding things like the CSV list you mention, this ends with a very wide table that has multiple areas of duplication and which will require you to manage and clean your data (and is generally bad for performance.)

A solution to this problem is to simply project the data into another table and provide the keys to join back to the original.

Eg:

Table: Exams:
Columns: ExamID, ExamTitle, ExamCreatedDate

This table would only store information relevant to an exam, such as the title of the exam, the exam's attributes and categories.

Table: Questions:
Columns: QuestionID, QuestionTitle, QuestionValue

This table would store only the information relevant to the question itself.

Table: ExamQuestions:
Columns: ExamID, QuestionID, QuestionValueOverride (made up)

This table would store the ids for both the previous tables and any information that is specific to that exam's instance of that question.

This would allow you to write queries such as "How many questions belong to one exam"

select e.title, count(*)
from exams e
join examquestions eq on eq.examid = e.examid
join questions q on q.questionid = eq.questionid


Mysql pagination without offset Let's assume I have got a table with these data:

I want to order it by createdAt column DESC, and id column DESC

select id, createdAt from post order by createdAt desc, id desc

Now it looks like this:

I want to paginate it with 2 items per page and don't want to use offset, just limit:

select id, createdAt from post order by createdAt desc, id desc limit 2

To get the next 2 items i use this query:

SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2

I can go on like this. Get the last item's createdAt and id then use it for next page query.

But i'm trying to formulate the previous page query for almost two days and can't figure out a way yet.

Here is what i tried already:

Get the first item from current result (instead of last) use it's id and createdAt field and reverse the conditions in query (createdAt >=, id >, created at >). But this query always gives me the first two results (which is normal because the rows providing this conditions are the first two).

I'm already out of ideas. I need help. Thanks.


MySql - find redundant indexes is there a tool that spots redundant indexes in MySql?

e.g. if I have the following indexes:

index1(col1)
index2(col1,col2)

then index1 should be flagged as redundant.

any ideas?

Percona tool kit will I think, but I used these views by Roland Bouman

I've added them below:

CREATE OR REPLACE VIEW I_S_INDEXES
AS
SELECT TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE
, GROUP_CONCAT(
CONCAT('`',COLUMN_NAME,'`')
ORDER BY IF( INDEX_TYPE = 'BTREE' -- when BTREE then
, SEQ_IN_INDEX -- column order is important
, 0) -- else
, COLUMN_NAME -- only column content
) COLUMNS
FROM information_schema.STATISTICS
GROUP BY TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, NON_UNIQUE
;

CREATE OR REPLACE VIEW
I_S_REDUNDANT_INDEXES
AS
SELECT l.TABLE_SCHEMA
, l.TABLE_NAME
, CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN GREATEST(l.INDEX_NAME, r.INDEX_NAME)
ELSE l.INDEX_NAME
END REDUNDANT_INDEX_NAME
, GROUP_CONCAT(
DISTINCT
CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN LEAST(l.INDEX_NAME, r.INDEX_NAME)
ELSE r.INDEX_NAME
END
) INDEX_NAME
FROM I_S_INDEXES l
INNER JOIN I_S_INDEXES r
ON l.TABLE_SCHEMA = r.TABLE_SCHEMA -- index on the same table
AND l.TABLE_NAME = r.TABLE_NAME
AND l.INDEX_NAME != r.INDEX_NAME -- but not identical
AND l.INDEX_TYPE = r.INDEX_TYPE
AND CASE
WHEN l.COLUMNS = r.COLUMNS -- we require column equality
AND (l.IS_UNIQUE = 'NO' -- redundant if not unique
OR l.IS_UNIQUE = r.IS_UNIQUE) -- or if same uniqueness as other
THEN TRUE
WHEN l.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(r.COLUMNS, l.COLUMNS) = 1 -- and l is a prefix
AND l.IS_UNIQUE = 'NO' -- redundant if not unique
THEN TRUE
ELSE FALSE
END
GROUP BY l.TABLE_SCHEMA
, l.TABLE_NAME
, REDUNDANT_INDEX_NAME


Basic PARTITION question in MySQL Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance.

Would I need to change the applications select statements for the application to continue to work?
If it would continue to work with the existing SQL, would I need change the SQL in order to gain any advantage from the partitioning?
What can I expect to happen if my application is using a Database Abstraction Layer, where I would normally not know if the DB had been partitioned?

This may sound naive, however, a partitioned table is essentially no different from any other table from the outside:

select * from non-partitioned-table;

will be no different than a:

select * from partitioned-table;

Hence, for your application, nothing changes.

The big difference is the speed at which a query can be run against a table when using an appropriate where clause.

For example: if you have 1000 lego blocks, equally distributed between 3 different colors, and you are looking for one that you know is green and has your name written on it, you are only going to "partition" your search through the legos by looking only at the GREEN lego blocks. You are only going to look through 1/3 of your whole set.

Similarly, if you partition a table according to a certain column, date is always a good example, and you are trying to find an entry in that table where the approximate time is known, you mysql will only attempt to search only in a subset (a partition if you will) of the whole table. So to answer your question: ensure that if you DO use partitioning, that the majority of your queries will include a where clause referring to the partitioning key.

If my lego example was to abstract, or anything else sounds wierd: i would advice reading up on: http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html


MySQL replication to slave having VPN connection to Master I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network to Master's network to accomplish replication.

But I observed over a few days that sometimes the VPN connection is lost. I was wondering, if the slave loses connection to the master, will it result in lost data for replication?

And when the connection is re-established, will replication start from the point it stopped or from the point where the Master's pointer is at that moment ?

Let's start with a sample display of SHOW SLAVE STATUS\G

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000262
        Read_Master_Log_Pos: 803779735
             Relay_Log_File: relay-bin.067799
              Relay_Log_Pos: 402744
      Relay_Master_Log_File: mysql-bin.000262
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 803779735
            Relay_Log_Space: 402598
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

As long as Slave_IO_Running is Yes, MySQL Replication has the needed connectivity. According to your question, when MySQL loses connectivity and reconnects, will it lose data? In a perfect world, I would say no as long as the relay log (Relay_Log_File) is written in a clean format. Once in a long while, when the network intermittency is significantly long, the relay log may get corrupted.

There is one sure fire way to correct this (please follow along):

Take note of these two values from the SHOW SLAVE STATUS\G

Relay_Master_Log_File : This represents the binary log on the Master whose entry was last executed on the Slave
Exec_Master_Log_Pos : This represents the position of binary log on the Master whose entry was last executed on the Slave

To properly connect to a safe place on Master, do the following

Select the Relay_Master_Log_File and use the value : mysql-bin.000262

Select the Exec_Master_Log_Pos and use the value : 803779735

Once you have those values, then run these commands using those values:

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000262',master_log_pos=803779735;
START SLAVE;

The CHANGE MASTER TO command will erase all relay logs on the Slave and start with a fresh relay log.

This is most likely the best way because you can only be sure about the last binary log entry from the Master that successfully reached the Slave and was executed on the Slave. Any entries from the Master's binary log that was written to a current relay log but was not closed properly or correctly written due to an intermittency problem with the IO Thread could result in a malformed relay log.

The only way this method would not work is if the binary log on the Master is itself corrupt. I have seen this happen when a DB Server crashed leaving the last binary log not closed properly. Running mysqlbinlog against the Master's last binary log had shown a difference between the filesize and last legal position. Trying to reestablish replication from a Master's corrupt binary log is a headache. You must then fanagle replication to work by running RESET MASTER on the Master, connecting replication to the first binary log on the Master, and perform due diligence of syncing the Slave using pt-table-checksum and pt-table-sync.

UPDATE 2012-02-17 08:13 EDT

@vinny : If you are thinking of creating an additional Slave, here are other posts I have made in the past on this and similar subjects:

https://dba.stackexchange.com/a/1409/877
https://dba.stackexchange.com/a/2734/877
https://dba.stackexchange.com/a/12352/877
https://serverfault.com/questions/257394/create-a-mysql-slave-from-another-slave-but-point-it-at-the-master/257426#257426

I hope they help.


can't connect to mysql via command line but workbench works, ssh tunneling required I am trying to connect to a mysql server running at B.edu. I want to connect via SSH tunnel.
I first set this up in MySQL Workbench:
Connection Method: Standard TCP/IP via SSH
SSH hostname: B.edu
SSH username: me
MySQL hostname: B.edu
MySQL port: 3306

This works fine. I connect and can do as much as I can through Workbench.
Now I'd like to connect via terminal command line. I try:
ssh -N -L 9999:127.0.0.1:3306 me@B.edu &
mysql -h 127.0.0.1 -u me -P 9999

The ssh connection works fine, but the mysql command fails with the error messages:

channel 2: open failed: connect failed: Connection refused
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I see that I'm not really asking for the same thing through the two methods, because from the command line I'm using 127.0.0.1 as the mysql host name, while from Workbench I'm using B.edu as the hostname. Since the Workbench version works, I'd like to change my command line approach to work similarly, but I don't see how.
My ultimate application, once I have the command line version working, is to automate using Python and PyMySQL (presumably with paramiko to handle the tunneling), but I'd like to cross that bridge later and get the terminal line approach working first.

Wrestled this morning with a very similar issue. This worked for me:

ssh -N -L LOCALPORT:localhost:REMOTEPORT -i MYKEYFILE USER@REMOTE
mysql --port LOCALPORT --protocol=TCP -p


READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express I wonder if SSE 2005 supports the READ_COMMITTED_SNAPSHOT database option. What I found is this article: Features Supported by the Editions of SQL Server 2005. It states that SSE 2005 supports Snapshot Isolation Level (Row-level Versioning), but doesn't support Database Snapshot.

Does it mean that READ_COMMITTED_SNAPSHOT is not supported? Or it is about some other database snapshot feature?

Yes from that link it supports it. A Database Snapshot is a read only "frozen in time" (when the snapshot was taken) view of data. This is a tool useful in some  recovery, reporting and rollback preparation scenarios.

The isolation levels you are asking about are all covered by the line talking about Snapshot Isolation Level (Row-level Versioning).

I would question what sort of workload you are putting against SQL Server Express that needs a snapshot isolation level as there are other restrictions on SQL Server Express that tend to make it not a great fit for busier, high concurrency apps (Most notably database size and memory restrictions) but this is certainly supported from that edition features matrix you linked.


How to restrict deletion of specific row of SQL server table I want to restrict deletion of specific row. For example if someone wants to truncate a table or delete all the records from table then an error message should be thrown, for particular id it is not allowed. I have used trigger for the same but it is of no use, as it does not fire at truncate

Create TRIGGER vendorTrigger
    ON [Vendor]
    INSTEAD OF DELETE
AS
    IF EXISTS(SELECT * FROM Vendor WHERE VendorId = 515)
    BEGIN
        RAISERROR ('Is not allowed to delete VendorId: 515 as it is of type corpnet',16, 1)
    END
    ELSE
    BEGIN
       Delete from Vendor Where VendorId in (Select VendorId From Vendor)
    END

TRUNCATE TABLE requires ALTER permission on the table.
The best way to prevent truncating a table is to revoke that permission.

What’s more TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

See more on TRUNCATE TABLE (Transact-SQL)


Help with optimizing Oracle Query - nested with count I have 2 tables (Students and Cellphone).

Columns in Students: StudentID, FNAME, PHONEID

Columns in Cellphone: PhoneID, Phonename

I want to create this:

•  Run a query to show the most popular phone used by students

My answer is:

select D.phoneid, c.phonename, d.numberp
from
(select * from

(SELECT m.phoneid,
         COUNT(*) AS numberp
    FROM students m
GROUP BY m.phoneid
ORDER BY numberp DESC)

where rownum = 1) D, cellphone c
where d.phoneid = c.phoneid

Can this be optimized or a simpler way of achieving this?
May be using TOP ? or HAVING ? JOINS ?

SELECT c.phoneid, COUNT( /* DISTINCT*/ s.StudentID)
FROM Students s, Cellphone c
WHERE s.phoneid = c.phoneid
GROUP BY c.phoneid
ORDER BY 2 DESC
LIMIT FIRST 1 ROW /* WITH TIES */

?


Need the name value of the enterprise to be the same as it was when it was registered and not the value it currently has I will explain the problem with an example: I am designing a specific case of referential integrity in a table. In the logical design there are two tables, enterprise and document. We register the companies and then someone inserts the documents associated with it. The name of the enterprise is variable.

When it comes to recovering the documents, I need the name value of an enterprise to be the same as it was when it was registered and not the value it currently has.

The solution that I thought was to register the company again in each change with the same code, the updated name in this way would have the expected result, but I am not sure if it is the best solution. Can someone make a suggestion?

I'd simply store the register_name (meaning the first value that was taken by the name of the enterprise) in the enterprise table :

identerprise
code
name
register_name

Then I think you shouldn't use varchar(n) to store your text value (see that documentation page)


Use Oracle on Web App (New Question) Our database project is about creating a web application where the user can add or edit information. My concern is How do I connect Oracle 11g with my PHP?

Note: Our teacher did not teach us about doing all of these. Our teacher wants us to learn by "self-discovery." Hope you can help.

Try the free underground PHP and Oracle manual
http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html


Mongos unable to find document after migration I have set up a mongo cluster as follows:

Four shards => mongoshard1 (Disk1), mongoshard2 (Disk2), mongoshard3(Disk3), mongoshard4(Disk4)
Replica set of one configuration server => mongoshardconfig (Disk5)
One mongos server => mongos
Shard key is a hashed index of _id

The entire setup is on a single server and the various mongod instances have been setup on different ports. (Setting up on a single server instead of different nodes has been a conscious decision though against recommended practices)

My server got terminated and I created a new cluster on another server with exact same settings as above using the old disks. The sharded collection document has a field: url . For a particular id, if I search using _id, mongos returns null but returns the object on finding using the url. Connecting individually to shard2 returns the object on querying using same _id. Some of the documents are found by querying on _id through mongos and some are not.

Why is mongos unable to find the document using id? Can this be fixed?

When querying with _id thru mongos, system directs that query to right node using config -databases chunks -collection information. It sounds like that "order" of your shards has been changed. (meaning that disk1 is not anymore at mongoshard1 -machine). Connect shard directly  and

use local
db.me.find()

to check that nodes are who they should be.


Integrity checks on large dbs (700GB) What's the best strategy for integrity checks on large dbs? DBCC CHECKDB (DB Name) would probably take over 24 hours and the largest table, being 532GB, would probably take over 17 hours.

Based on your question it seems like this actually has not been run yet because you are using "probably".

However, there are two branches for this question:

Run the Integrity checks on a backup of the database.

Take a full database backup of the prod server
Restore the database on another server
Run consistency checks on the restored database

Break up the DBCC CHECKDB over multiple days.

Aa mentioned in Consistency Checking Options for a VLDB

It is NOT AN OPTION to run Consistency checks on a mirror database or on an availability group replica as a way of checking the principal or primary. Since these will be running on different I/O systems and corruptions do not propagate between servers. This will need to be run IN ADDITION.

Things to make CHECK DBCC run faster.

Use the physical only option trace flag 2562, and trace flag 2549
reduce memory grant
Disable non-clustered indexes on computed columns ahead of time – There is an issue with the DBCC_OBJECT_METADATA latch that it requires an exclusive lock to run.
TEMPDB performance will greatly impact the performance of your CHECKDB
If you want to change the amount of cores your DBCC command, you can either use:

TRACE FLAG 2528
SET MAXDOP