Tag Info

New answers tagged

0

Given the answers above. If this is a one-off bulk insert, I'd create a temporary table with an identity column. Insert the data to it. And then select the data and insert to your target table. You could set the identity start value, or start at 1 and add a fixed value when you do the final insert. I can't check atmo but a believe data writer privileges ...


-1

The idea of single common master table instead of multiple is good but the integrity of Foreign Key constraint fails.


3

The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain. Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest ...


0

In Oracle you can use function based index, which can be used to create a kind of "conditional index". CREATE TABLE ARTICLE ( ID NUMBER, SUPPLIER_ID NUMBER, SUPPLIER_CP VARCHAR2 (10 ), NAME VARCHAR2 (10 ) ) / CREATE TABLE contact_person ( SUPPLIER_ID NUMBER, person_name VARCHAR2 (10) ) / CREATE ...


1

This is a perfectly valid practice, though it's more commonly used for inserts than updates. This is discussed elsewhere on Stack Exchange (e.g., http://programmers.stackexchange.com/a/78598/25946). It's worth confirming, though, that the whole time required for "create index, update, drop index" really is less than making the update without indices. You ...


6

Building temporary indexes for ETL jobs is not necessarily bad practice, as the index builds are fairly quick. Where it might not be so efficient is if you have relatively small incremental updates on very large tables, but it sounds like this is not the case here. The only caveat is if you expect the tables to grow substantially with time. If they are ...


2

The only thing which I can think of which could be bad is that while building indexes new inserts/updates on the relevant tables could be problematic. But if there is no use-case for changing the data while building indexes it is a fast and viable approach. You save time and space.


0

You can use this to significantly increase the readability of your queries. Rather than using a short alias, use your alias to describe the data that you are joining to, for example SELECT transaction.unique_id, authorisingUser.name AS authorising_user_name, requestingUser.name AS requesting_user_name FROM transactions AS transaction JOIN users ...


0

The real use case for table partitioning is fast load and unload of data. If your warehouse tables and staging tables are in the same database, and have the same schema, you will be able to swap a partition from staging to warehouse very quickly. Similarly, when data has reached it retention expiry date and must be purged, it is very quick to delete a ...


8

If it works for you then it's a good practice. There are basically no hard rules for databases.


3

The standard approach in Oracle is that global temporary tables are first-level objects. They should be created at install time when you create all your permanent tables. Doing DDL at runtime is very much frowned upon. I'm not sure how this increases maintenance or how it makes stored procedures "no longer wholly self-contained"-- you don't need to do any ...


2

Being in the database version control space for 5 years (as director of product management at DBmaestro) and having worked as a DBA for over two decades, I can tell you the simple fact that you cannot treat the database objects as you treat your Java, C# or other files. There are many reasons and I'll name a few: Files are stored locally on the ...



Top 50 recent answers are included