Ch 3 - KSU

Ch 3 - KSU

Ch 3 Synonym Synonyms Object Description Table Basic unit of storage; composed of rows View

Logically represents subsets of data from one or more tables Sequence Generates numeric values Index Improves the performance of some queries Synonym

Gives alternative names to objects Creating a Synonym for an Object Simplify access to objects by creating a synonym (another name for an object). With synonyms, you can: Create an easier reference to a table that is owned by another user CREATE [PUBLIC] SYNONYM synonym

Shorten lengthy object names FOR object; PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Specify PUBLIC to create a public synonym.

Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym. A synonym places a dependency on its target object and becomes invalid if the target object is changed or dropped. Some reasons to use synonyms are security (for example, to hide the owner and location of an object) Creating a Synonym for an Object

To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. Creating and Removing Synonyms Create a shortened name for the DEPT_SUM_VU view: CREATE SYNONYM d_sum

FOR dept_sum_vu; Drop a synonym: DROP SYNONYM d_sum; Creating and Removing Synonyms The database administrator can create a public synonym that is accessible to all users. The following example creates a public synonym named DEPT for Alices DEPARTMENTS table: CREATE PUBLIC SYNONYM dept FOR alice.departments;

Removing Synonyms To remove a synonym, use the DROP SYNONYM statement. Only the database administrator can drop a public synonym. DROP PUBLIC SYNONYM dept; Indexes Object Description Table

Basic unit of storage; composed of rows View Logically represents subsets of data from one or more tables Sequence Generates numeric values Index

Improves the performance of some queries Synonym Gives alternative names to objects Indexes An index: Is a schema object Can be used by the Oracle server to speed up the retrieval of rows by using a pointer If you do not have an index on the column, then a full table scan occurs.

Can reduce disk input/output (I/O) by using a rapid path access method to locate data quickly Is independent of the table that it indexes This means that they can be created or dropped at any time, and have no effect on the base tables or other indexes. Is used and maintained automatically by the Oracle server When you drop a table, the corresponding indexes are also dropped. How Are Indexes Created? Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

Manually: Users can create nonunique indexes on columns to speed up access to the rows. You can manually create a unique index, but it is recommended that you create a unique constraint, which implicitly creates a unique index. Creating an Index Create an index on one or more columns: CREATE [UNIQUE] INDEX indexName ON table (column[, column]...); Specify UNIQUE to indicate that the value of the column (or columns) upon which the index is based

must be unique. Alternatively, you can define UNIQUE integrity constraints on the desired columns Improve the speed of query access to the LAST_NAME column in the EMPLOYEES table: CREATE INDEX emp_last_name_idx ON employees(last_name); Index Creation Guidelines Create an index when: A column contains a wide range of values A column contains a large number of null values

One or more columns are frequently used together in a WHERE clause or a join condition The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table Do not create an index when: The columns are not often used as a condition in the query The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table The table is updated frequently The indexed columns are referenced as part of an expression More Is Not Always Better

Having more indexes on a table does not produce faster queries. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes that you have associated with a table, the more effort the Oracle server must make to update all the indexes after a DML operation. Removing an Index Remove an index from the data dictionary by using the DROP INDEX command: DROP INDEX indexName;

Remove the emp_last_name_idx index DROP INDEX emp_last_name_idx; To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege. You cannot modify indexes. To change an index, you must drop it and then re-create it.

Recently Viewed Presentations

  • Core Presentation - my.knowsley.gov.uk

    Core Presentation - my.knowsley.gov.uk

    Keeping safe with gas. Carbon monoxide. What is it? How will I recognise it? What are the symptoms? V4.2. What is it? Carbon monoxide (CO) is a highly poisonous substance produced by the incomplete burning of gas and Liquidfied Petroleum...
  • Molecular evolution of proteins and Phylogenetic Analysis ...

    Molecular evolution of proteins and Phylogenetic Analysis ...

    Times Helvetica Monotype Sorts Arial Courier Geneva Symbol Times New Roman Sans titre 1 Molecular Evolution of Proteins and Phylogenetic Analysis Fred R. Opperdoes Christian de Duve Institute of Cellular Pathology (ICP) and Laboratory of Biochemistry, Université catholique de Louvain,...
  • Clothespin Microwave Transmitter and Receiver

    Clothespin Microwave Transmitter and Receiver

    We would like to help these pre-service physics teachers bridge the gap between education theory and physics teaching practice, develop some reflective skills, develop their Physics PCK and develop some appreciation of the physics learner. Note that Ed Psych is...
  • Sounds Of The Day - Annotation

    Sounds Of The Day - Annotation

    Sounds Of The Day. In Sounds of The Day MacCaig begins by describing the sounds that he hears in a still silent environment. It begins in a positive and descriptive manner. However the sound of a door closing in stanza...
  • Chapter Chapter16Lecture Book Title Edition Bleeding and Shock

    Chapter Chapter16Lecture Book Title Edition Bleeding and Shock

    Title: PowerPoint Presentation Author: R U Last modified by: vijaya Created Date: 9/26/2007 5:29:17 AM Document presentation format: On-screen Show
  • Chapter Thirteen - Faculty Server Contact

    Chapter Thirteen - Faculty Server Contact

    Brain hemispheric theory: psychopaths rely less on right hemisphere and more on left. Reward dominance theory: reward centers are more powerful than the inhibitory systems in criminals. Prefrontal lobe theory. Suboptimal arousal theory. Seizuring theory: most controversial; seizures higher among...
  • Maths Games - doubleme.org

    Maths Games - doubleme.org

    CountDown. Take three single digit cards to make a three digit target number. Write down the number and replace the cards (take the 0 card out now).
  • Cannabinoid-Receptor Interacting Protein 1a (CRIP1a) effects ...

    Cannabinoid-Receptor Interacting Protein 1a (CRIP1a) effects ...

    Intrinsic efficacy of ligand. All three inverse agonists stimulation will be reduced to the same degree. Reduction of Stimulation. SR141716A = AM251 = AM281. Extreme ends of range . Stimulation caused by the less potent inverse agonists will be reduced...