Question

in reference to SQL Database, what are some benefits of creating sequences, indexes, and synonyms?

Answer 1

Benefit of creating sequencing -->

Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.

Without sequences, sequential values can only be produced programmatically. A new primary key value can be obtained by selecting the most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to wait for the next value of the primary key; this waiting is known as serialization. If developers have such constructs in applications, then you should encourage the developers to replace them with access to sequences. Sequences eliminate serialization and improve the concurrency of an application.

Creating Sequences >

CREATE SEQUENCE emp_sequence
      INCREMENT BY 1
      START WITH 1
      NOMAXVALUE
      NOCYCLE
      CACHE 10;

Benefit are

1-> , a sequence is a database object that guarantees you a unique number. This is often useful when trying to generate a value for a primary key

2-> When you ask for the next value from a sequence, a number is generated or incremented without having to invoke a transaction which the database needs to commit or rollback.

3-> Multiple users can access the same sequence, and values can be cached in memory to improve performance, although you may get gaps in your values as sequences are not necessarily guaranteed to be sequential..

4-> You could write your own code to generate a unique sequential number but you would be likely to create a bottleneck in your application if lots of users are trying to update the table column that stores the next value at the same time: this is avoided if you use Oracle sequences.

5-> I'd like to think that it is possible to implement such feature using string, like uuid or hash string. But number is the easiest to be understood by the client. And client applications, depending on the language may have different ways to implement string. Whereas integers are native.

Benefit

• Speed up SELECT query
• Helps to make a row unique or without duplicates(primary,unique)
• If index is set to fill-text index, then we can search against large string values. for example to find a word from a sentence etc.
• Their use in queries usually results in much better performance.
• They make it possible to quickly retrieve (fetch) data.
• They can be used for sorting. A post-fetch-sort operation can be eliminated.
• Unique indexes guarantee uniquely identifiable records in the database.
  • Synonyms

  • Benefits

  • SYNONYMs provide a layer of abstraction over the referenced object
  • Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
  • Provides flexibility for changing the location of objects without changing existing code.
  • SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
  • SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

  • Limitations

  • SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
  • Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
  • Obviously consumes possible object names, as you can not create a table with the same name of a synonym
  • The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
  • SYNONYM can not be referenced in a DDL statement