SQL Basic

Database Objects

- by OracleWorld 172
Post Views: 172

Database objects relate to schema and non-schema objects.

Schema objects

– a collection of logical structures of data related to a  schema

Clusters

A cluster is an object that contains data from a set of tables (but could be also from one table only) that share common columns and store related data in the same block. You can find all details about existing clusters in USER_CLUSTERS, ALL_CLUSTERS, and DBA_CLUSTERS data dictionary views.

Constraints

Constraints define data-integrity. With them, you can easily restrict or enforce values in the database.

There are five constraints in Oracle database:

  • Not Null – makes sure that database values will not  be stored as a NULL
  • Primary – makes sure that database will not store NULL values and any duplicate values
  • Foreign – restricts data that values in one table will match values in another table
  • Check – checks the value if it complies with a specific condition set
  • Unique – restricts from having same values in a column or set of columns. NULLs are allowed

Database links

Database links provide a connection between two physical databases to allow users to interact in them as in one logical database.

Database triggers

Triggers are procedures that are associated with a table and trigger on (before/after) an event of INSERT, UPDATE or DELETE. They can run SQL or PL/SQL code or trigger other procedures and packages.  The difference between a trigger and a procedure is that procedures are executed by users whereas triggers are triggered by an event no matter the user. A possible disadvantage of using triggers might be a performance issue.

Let’s imagine a situation, where you have a control mechanism implemented in a trigger on the table LOGIN. The trigger will check whether the user_id is in a range between 1 to 10. Considering we need to load a huge amount of data into this table the whole load would be slowed down dramatically because of after / before each insert statement the trigger will check the value of user_id and perform a corresponding action.

Dimensions

Dimensions organize data in order to help business users to answer their questions.

Examples of dimensions: TIME, ADDRESS, CATEGORY, CUSTOMER, EMPLOYEE, …

External procedure libraries

Some tasks might be achieved faster and easier with external programs than in a native SQL. PL/SQL offers an option where you can call some other scripts (mostly written in C).

Index-organized tables

These tables are characterized by having the primary key and non-key value in the same B*Tree structure (data are stored within the primary key) which makes it faster for access.

Indexes

The index represents an object that store values of indexed columns or cluster and provides a direct (thus fast) access to values. There are five main types of indexes:

  1. Normal / B*Tree index
    • the most commonly used index
    • sorts data in a B*Tree (B = Balanced) for a better orientation
    • most widely used in OLTP systems
  2. Function-based
    • index based on a function (expression) that could be custom made or built-in
    • it provides a great tool to handle some function based filters
    • can be used to handle NULL values
  3. Partitioned index
    • index on partitioned tables
    • for the partitioned table, we mostly use LOCAL indexes instead of GLOBAL indexes. Global indexes are used for non-partitioned tables. The use of the LOCAL index is handy in a way that when we drop a partition LOCAL indexes do not require to be rebuilt unlike GLOBAL indexes which fall into an invalid status and you have to rebuild them.
  4. Bitmap index
    • Bitmap index is not suitable for OLTP (=transactional) databases
    • They are most often found in OLAP (= analytical) databases -> having said that, bitmap indexes are better for databases where users are rather querying data only instead of modifying them
    • Bitmap indexes are highly compressed and therefore very fast to read due to its smaller size in a storage
    • Another advantage is they are very suitable for combining multiple bitmap indexes (comparing 0s and 1s is a very easy task to do)
    • They are a little bit more CPU demanding due to the need for a decompression
    • The only problem with bitmap indexes is if you update an indexed value, it will lock “several” rows and wait for the commit. Considering a huge load/update of data inside the table – solving the concurrency could be very dreadful.
  5. Domain indexes
    • Domain indexes have a very specific purpose and I haven’t met them in a real life.
    • They are designed for a specialized domain (i.e. processing of images)

Indexes do not consider NULL values. indexing might seem like a great performance feature and more indexes we have the better the performance will be -> NOT TRUE 🙂 Over-indexing or incorrectly indexed columns might even do the opposite and slow down the query and increase the resource consumption of the server.

Most commonly (but not exclusively) columns are selected for indexing if they have low selectivity. What does “low selectivity” mean? While using an index less than 15% * of records (out of all records in the table) must be selected.  The ideal selectivity is 1 (meaning only one record is selected from the table). This could be achieved by UNIQUE and NOT NULL constraints.

Disadvantages of using indexes might be slower query if we do not choose the column properly (high selectivity or using indexing over activated partition pruning – in some cases it is faster to perform a full table scan) and storage consumption. Indexes might consume a lot of space as well.

* the percentage depends on the size of a table … bigger tables might get up to 20-25% of selectivity and smaller around 5-10%

Indextypes

It is a routine managing a domain index.

Java classes, Java resources, Java sources

JAVA objects stored inside Oracle.

Materialized views

Materialized views (mViews) contain the result of a query statement. They can be scheduled for a refresh on various events (at a specific time regularly, on demand, fast refresh, …). The advantage of using them is they can pre-calculate data  (unlike regular views) and those data are already stored and ready to be accessed. The disadvantage of using mViews is that data might be obsolete (the last refresh might have been several hours ago and even though the source tables are already updated the materialized view is not -> that is why they were called “snapshots” before), Another bug in Oracle in mViews is, when you reload a big amount of data in mView it’s preferred to use TRUNCATE & INSERT /*+APPEND*/ (read more about Oracle Hints) instead of the method used by mViews -> DELETE & INSERT which is not only slow but might increase High Water Mark as well.

Materialized view logs

Materialized view logs (or MV logs if you will) are logs storing changes in base tables of materialized views. Every time there is a change in the source table, it is written in the MV log and then projected into materialized view (this is called fast refresh). Without the log, Oracle would have to re-execute the query defining the materialized view again (this is called complete refresh) which takes a lot longer than fast refresh.

Operators

Operators represent a particular operation done over a set of data or parameters which returns a result. There are multiple operators divided into 9 groups.

  • Unary and Binary Operators
    • unary –  takes only one parameter (i.e. -300; where ‘-‘ is the operator that negates the number)
    • binary – takes to parameters (i.e. 100 + 200; where ‘+’ is the operator)
  • Precedence
    • precedence operators define the order in which the Oracle will proceed with the operations. There are some with higher precedence and those are evaluated before those with lower precedence. Custom precedence might be enforced by using parenthesis.
      List of operatorsDescription
      +,-identity, negation
      *, /multiplication, division
      +, -, ||addition, substraction, concatenation
      IN, IS NULL, LIKE, BETWEEN, =, !=, <, >, <=, >=comparison
      NOTlogical negation
      ANDconjunction
      ORdisjunction

      Priority / PrecedenceOperator
      1Parentheses
      2*, /
      3+, -
      4<>, <, >, <=, >=, =
      5IS (IS NULL, IS NOT NULL, IS EMPTY, IS NOT EMPTY)
      6BETWEEN
      7NOT
      8AND
      9OR
    • For example:
      • using the default precedence: 5*5+5*6
        • multiplication has higher precedence than addition, thus:
          • 25 + 30 -> 55 
            SELECT 5*5+5*6 FROM dual;
      • using the custom precedence: 5*(5+5)*6
        • expression in parenthesis has higher precedence than the default precedence, thus:
          • 5* (10) * 6 -> 300 
            SELECT 5*(5+5)*6 FROM dual;
  • Arithmetic Operators
    • +, –   -> addition, subtraction (binary) or positive / negative (unary)
    • *, / -> multiplication, division (binary)
  • Concatenation Operator
    • || -> to concatenate (join) strings
  • Comparison Operators
    • compare two attributes
      List of operatorsDescription
      =equal
      !=
      <>      		not equal
      >greater than
      <less than
      >=greater than or equal to
      <= less than or equal to
      INequal to any member of a given list
      NOT INopposite of IN
      ANY
      SOME      		equal to any member of a given list; follows after one of the =, !=, >, <, <=, >=. (e.g. SELECT * FROM emp WHERE id = ANY (SELECT id FROM emp WHERE dept_id = 1001);
      ALLcompers to any member of a given list; follows after one of the =, !=, >, <, <=, >=. (e.g. SELECT * FROM emp WHERE id <= ALL (10002,10003);
      value1 [NOT] LIKE value2 [ESCAPE 'escape_character']value1 does [not] match the pattern of value2 considering escaped characters to allow filtering by special characters ( % and _ )
      EXISTSevaluated as TRUE if at least one record is returned
      IS [NOT] NULLevaluated as TRUE if the attributes is [not] NULL
      [NOT] BETWEEN some_value AND some_other_value[not] greater than or equal to "some_value" and less than or equal to "some_other_value"
  • Logical Operators: NOT, AND, OR
    • return BOOLEAN (true, false) based on the expression used
      • NOT – returns true if the expression is false
      • AND – returns true if the expression is true
      • OR – returns true if at least one of the expressions is true
  • Set Operators: UNION [ALL], INTERSECT, MINUS
    • combine two sets of data together
      • UNION – all sets of data (excl. duplicates)
      • UNION ALL – all sets of data  (incl. duplicates)
      • INTERSECT – all data that are common for both data sets
      • MINUS  – all data from the first data set that are not presented in the second data set

Packages

Packages groups logically relate subprograms, types or/and items and consist of two main parts:

  • specification – mandatory, declares types, subprograms, variables, exceptions, cursors, …
  • body – optional, defines types, subprograms, variables, exceptions, cursors, …

You can easily enforce a dependency between subprograms using packages, incorporate logging, handle exceptions and implement more complex logic into your programs.

Sequences

The sequence is an Oracle object that generates a sequence of numbers. It is usually used to generate ID columns or primary keys. When you expect to have a huge load to a table using sequences, it is recommended to CACHE your sequence numbers ahead.

Stored functions, stored procedures

Oracle allows users to create their own functions or procedures (sometimes referred to as UDF -> User Defined Functions). The difference between functions and procedures is that functions return a value, unlike procedures that only process data or do some operation.

Synonyms

Synonyms allow users to operate with objects schema-independently. That means, that you can refer to an object no matter in which schema it resides. Instead of using a schema name in your queries, you can create an alias of a table so you can call it from any schema.

Tables

Tables are basic structures in Oracle database. The table represents an entity in a business world (i.e. a customer is an entity). A table called CUSTOMER stores all data related to customers that are needed for business purposes.

Views

Views are logical objects and do not contain any data. They are only a “stored SQL queries” (a definition of how to retrieve data). Their advantage is that they always reflect the most updated data (unlike mViews). On the other hand, the disadvantage might be their performance.

Non-schema objects

– other objects not contained in a schema

Contexts

Context is a set of associated attributes (defined by an application) with a namespace linked to a managing package.

Directories

Directories define an alias for a physical directory stored on the server.

Parameter files (PFILEs) and server parameter files (SPFILEs)

PFile

  • is a text file containing database init configuration
  • might be changed in a text editor
  • cannot be backed up by RMAN

SPFile

  • is a binary file containing database init configuration
  • cannot be changed in a text editor; only by “ALTER SYSTEM” command
  • RMAN can back up this file

Profiles

Profiles define limits for users with regard to the use of database server resources.

Roles

Roles offers a way how to group set of privileges. You can then grant role (set of privileges) to a particular user instead granting every single permission to that user. Another advantage is when you have set of users with a specific set of access and you need to change it, it is easy to achieve it with roles (you simple revoke that particular privilege from the role) instead of revoking the privilege from all users one by one. If you want to know about this topic see our article about Permissions

Rollback segments

Rollback segments contain uncommitted transactions. Every time a user executes a DML before he commits or rollbacks all data is stored in UNDO logs in case of the need for a rollback.

Tablespaces

Tablespaces allocate space in the database to store database objects. There are three types of tablespaces:

  • Permanent – stored in data files; contains persistent changes
  • UNDO –  UNDO data management
  • Temporary – stored in temp files; contains only session-related schema objects

Users

Users are accounts which allows multiple people to access a database. Read more about User, schema and roles

Quiz:  With what object you can group your logic, re use parts of it internally, use encapsulation, overloading, … ?

Answer: