July/August 2017
This article is the 10th in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine. The previous Beyond SQL 101 article, “The Cost of Data Retrieval,” introduced bitmap indexes. You discovered how to create them and when they might be an appropriate index choice. You also learned how bitmap indexes should not be used in a database with regular transaction activity but instead in a data warehouse with occasional bulk loads of data. You were introduced to Oracle Database’s Oracle Optimizer program, SQL statement processing operations, and parsing. You learned what a bind variable is and how it can help reduce parsing activity. Finally, you learned about statistics and the creation and display of execution plans.
In this article, you will
To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 2 (12.2.0.1.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.
Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are used for this article’s examples. (View the script in a text editor for execution instructions.)
What Lies BeneathRecall from a previous Beyond SQL 101 article, “Defining, Constraining, and Manipulating Your Entities,” that the data dictionary is an internally owned and controlled set of tables and views that contains information about your database’s metadata. Oracle Database uses the data dictionary internally for many purposes, such as
The data dictionary includes both dynamic and static sets of tables and views. The dynamic views are used primarily to examine database performance. The static views store details about database objects such as tables, indexes, and views and are generally categorized into one of three prefixed categories: USER_, ALL_, and DBA_.
Whenever a new database object is created or altered, metadata about the object is recorded in a static data dictionary view. Static views can also be used by Oracle Database to retrieve information about indexed columns and referential integrity constraints. The USER_ views display information about objects belonging to the currently logged-in user. The ALL_ views return the same information as the USER_ views, but they also display information about any objects granted to the currently logged-in user by another user and display any public objects, such as public synonyms. (You will learn about synonyms and how to grant, receive, and revoke access privileges and roles in subsequent articles in this series.) The DBA_ views provide information about every object in the database. To query any of the DBA_ views, you need to have special database administrator privileges, such as the DBA role or the SELECT ANY DICTIONARY (as in data dictionary) privilege, granted to you. Consider the query in Listing 1.
Code Listing 1: Query the USER_TABLES static data dictionary view
SQL> set lines 10000 SQL> select table_name, num_rows, to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed 2 from user_tables 3 order by table_name; TABLE_NAME NUM_ROWS LAST_ANALYZED —————————————————————————————— —————————— ———————————————————— ANNUAL_REVIEW 15 05-JUL-2016 01:00:23 DEPARTMENT 4 05-JUL-2016 01:00:24 EMPLOYEE 16 04-JAN-2017 01:00:32 EMPLOYEE_CTAS 2 11-SEP-2016 16:21:22 EMPLOYEE_EXTRA 2 26-OCT-2016 01:00:32 EMPLOYEE_IDENTITY 16 04-JAN-2017 01:00:32 6 rows selected.
Query the USER_TABLES static data dictionary view to display the names of the tables owned by you (or the currently logged-in user), the number of rows each table contains, and the last time statistics were gathered for each table. Run the DESCRIBE command on the USER_TABLES data dictionary view to display all the other columns that constitute the view, as the example in Listing 2 demonstrates. For a description of every USER_TABLES column and the type of data it contains, refer to the documentation.
Code Listing 2: A partial result from the DESCRIBE command executed against the USER_TABLES static data dictionary view
SQL> describe user_tables Name Null? Type ———————————————————————————————————————————————————————————————— TABLE_NAME NOT NULL VARCHAR2(128) . . . NUM_ROWS NUMBER . . . LAST_ANALYZED DATE . . .
The dynamic data dictionary views are typically used by a database administrator to monitor the database and its performance. These views have a V$ prefix (which is why they are commonly referred to as “V-dollar tables”) and are continuously updated by Oracle Database background processes rather than database users. Much as with the DBA_ views, you need to have special database administrator privileges, such as the DBA role or the SELECT ANY DICTIONARY privilege, granted to you to give you access to the dynamic data dictionary views. Alternatively, a database administrator can grant you privileges to access specific individual dynamic data dictionary views you’d like to query. To learn more about the data dictionary and dynamic performance views, refer to the documentation.
All Together NowAlthough you know how to write and execute individual SQL statements, it is common for developers and DBAs to insert multiple SQL statements and SQL*Plus commands into a single script file for executing en masse. The script you used to create the tables for the SQL_201 schema for this article’s examples—SQL_201_objects_setup_script_Install_1.sql—contains data definition language (DDL) statements for creating tables and data manipulation language (DML) statements to insert the data, among other statements. Scripts are useful for tasks that require you to run a set of SQL statements, for example, for the following types of activities:
Consider the example in Listing 3. The test_this_script.sql script contains two SQL statements: one that creates a table called EMPLOYEE_SUBSET and one that selects data from the newly created table. You can create this example script in an editor of your choice and save it with the name test_this_script.sql. To avoid the error in Listing 4 when you run the script, either navigate to the directory in which you’ve saved the script before you log in to Oracle Database or fully qualify the directory location of your database script. It is good practice to fully qualify the directory location of your database script. Listing 5 shows the result of running the test_this_script.sql database script with SQL*Plus as the SQL execution environment. Note that the line that invokes and executes the script,
@test_this_script.sql
begins with an @ symbol. When followed by a filename, this symbol acts as a SQL*Plus command to execute, or run, the commands inside the file. Alternatively, you could use the keyword START, as in
START test_this_script.sql
Code Listing 3: The test_this_script.sql database script contents
create table employee_subset (employee_id,
employee_name,
create_date)
as
select employee_id, first_name||' '||last_name, sysdate
from employee;
select employee_id, employee_name, create_date
from employee_subset
order by employee_id;
Code Listing 4: Error received when the SQL environment cannot find your script
SQL> @test_this_script.sql SP2-0310: unable to open file "test_this_script.sql"
Code Listing 5: The result of running the test_this_script.sql database script
SQL> @test_this_script.sql
Table created.
EMPLOYEE_ID EMPLOYEE_NAME CREATE_DATE
——————————— ————————————————————————————— ————————————————————
1 Don Rose 11-MAY-2017 21:48:06
21 Gerald Sowell 11-MAY-2017 21:48:06
28 Emily Eckhard 11-MAY-2017 21:48:06
37 Frances Newton 11-MAY-2017 21:48:06
1234 Donald Newton 11-MAY-2017 21:48:06
6567 Roger Friedli 11-MAY-2017 21:48:06
6568 Betsy James 11-MAY-2017 21:48:06
6569 michael peterson 11-MAY-2017 21:48:06
6570 mark leblanc 11-MAY-2017 21:48:06
6571 Thomas Jeffrey 11-MAY-2017 21:48:06
6572 Theresa Wong 11-MAY-2017 21:48:06
6573 Lori Dovichi 11-MAY-2017 21:48:06
6574 Marcy Tamra 11-MAY-2017 21:48:06
6575 Sasha Meyer 11-MAY-2017 21:48:06
6576 Mary Streicher 11-MAY-2017 21:48:06
7895 Matthew Michaels 11-MAY-2017 21:48:06
16 rows selected.
The next few lines in the output in Listing 5 illustrate that the first statement was executed; the table, EMPLOYEE_SUBSET, was successfully created; then the second statement was executed; and the result of the query against the EMPLOYEE_SUBSET data was returned.
A Variety of SubstitutionsYou might have a requirement to execute a SQL statement over and over again, but one part of the statement—the input to the WHERE clause—must change for different executions. In such a case, that input is variable and is substituted by a different input value each time the statement is executed. You can rewrite this type of statement by using a substitution variable. A substitution variable consists of a variable name of your choosing, prefixed with an ampersand (&). The example in Listing 6 illustrates the use of a substitution variable, &v_salary, to input a salary value that is compared with salary values in the EMPLOYEE table. By using a substitution variable, you can reduce the amount of writing required for similar SQL statements that are run often.
Code Listing 6: A substitution variable for salary values
SQL> select first_name||' '||last_name employee_name 2 from employee 3 where salary < &v_salary 4 order by employee; Enter value for v_salary: 100000 old 3: where salary < &v_salary new 3: where salary < 100000 EMPLOYEE ————————————————————————————————————————————————————— Betsy James Don Rose Donald Newton Frances Newton Matthew Michaels Roger Friedli Sasha Meyer Theresa Wong mark leblanc michael peterson 10 rows selected.
The query in Listing 7 demonstrates how to re-execute the Listing 6 statement that is now in the SQL*Plus buffer. When you use a forward-slash character (/), the SQL statement is reissued to the SQL execution environment and you are prompted for a value for the substitution variable, without having to copy and paste the original SQL statement. Additionally, Listing 8 shows how the use of substitution variables is not limited to the WHERE clause of a statement.
Code Listing 7: Re-executing the statement with the forward-slash character (/)
SQL> / Enter value for v_salary: 90000 old 3: where salary < &v_salary new 3: where salary < 90000 EMPLOYEE —————————————————————————————————————————————————————— Betsy James Donald Newton Frances Newton Matthew Michaels Roger Friedli Sasha Meyer Theresa Wong mark leblanc 8 rows selected.
Code Listing 8: Substitution variables, not just for WHERE clauses
SQL> select &v_col1, &v_col2, &v_col3 2 from employee; Enter value for v_col1: first_name Enter value for v_col2: last_name Enter value for v_col3: hire_date old 1: select &v_col1, &v_col2, &v_col3 new 1: select first_name, last_name, hire_date FIRST_NAME LAST_NAME HIRE_DATE ——————————————— ———————————————— ————————— Marcy Tamra 04-MAY-16 Sasha Meyer 04-MAY-16 Gerald Sowell 29-OCT-16 Emily Eckhardt 07-JUL-04 Frances Newton 14-SEP-05 Donald Newton 24-SEP-06 Matthew Michaels 16-MAY-07 Roger Friedli 16-MAY-07 Betsy James 16-MAY-07 Michael peterson 03-NOV-08 Mark leblanc 06-MAR-09 Thomas Jeffrey 27-FEB-10 Theresa Wong 27-FEB-10 Lori Dovichi 07-JUL-11 Mary Streicher 16-MAY-16 Don Rose 26-OCT-16 16 rows selected. SQL> select first_name||' '||last_name, salary, hire_date 2 from employee 3 order by &v_order; Enter value for v_order: salary desc old 3: order by &v_order new 3: order by salary desc FIRST_NAME||''||LAST_NAME SALARY HIRE_DATE —————————————————————————————————————————— ————————— ————————— Marcy Tamra 04-MAY-16 Lori Dovichi 07-JUL-11 Thomas Jeffrey 300000 27-FEB-10 Mary Streicher 200000 16-MAY-16 Emily Eckhardt 110000 07-JUL-04 Gerald Sowell 100000 29-OCT-16 Don Rose 95000 26-OCT-16 michael peterson 90000 03-NOV-08 Sasha Meyer 85000 04-MAY-16 Frances Newton 82500 14-SEP-05 Donald Newton 80000 24-SEP-06 Matthew Michaels 70000 16-MAY-07 Theresa Wong 70000 27-FEB-10 mark leblanc 65000 06-MAR-09 Roger Friedli 60000 16-MAY-07 Betsy James 60000 16-MAY-07 16 rows selected.
Be aware that the SQL*Plus environment almost always reads the ampersand as a substitution variable. The queries in Listing 9 show the kind of error you might run into when you include a literal ampersand value or both a literal ampersand value and a substitution variable in your script, followed by a couple of workarounds.
Code Listing 9: When you want a literal text ampersand returned in your result
SQL> select 'This & That' 2 from dual; Enter value for that: old 1: select 'This & That' new 1: select 'This ' 'THIS ————— This --Create a database script called handle_the_ampersand.sql set define off select 'This & That' from dual; --Execute the script SQL> @handle_the_ampersand.sql 'THIS&THAT' ——————————— This & That --Mix substitution variables with literal text ampersands --Create a database script called variables_and_ampersands.sql set define on select 'This '||'&'||' That' from &v_table; --Execute the script SQL> @variables_and_ampersands.sql Enter value for v_table: dual old 2: from &v_table new 2: from dual 'THIS'||'&' ——————————— This & That
The first line of the database script handle_the_ampersand.sql is
set define off
This is a SQL*Plus command for disabling the prompting and replacement of substitution variables within the SQL*Plus execution environment. When this command is issued, ampersands are treated as literal values. However, there might be instances when you want to use both substitution variables and literal ampersand values within your statements or scripts. In such cases, you might not want to disable substitution variables. The database script variables_and_ampersands.sql sets the SQL*Plus environment back to accepting and replacing substitution variables and uses string concatenation to ensure that any literal ampersand is correctly read as a literal string value. Note that if you run the SET DEFINE OFF SQL*Plus command, any attempt to access your substitution variable as originally written in your script will result in an error message similar to the following:
SQL> @variables_and_ampersands.sql SP2-0552: Bind variable "V_TABLE" not declared.
One of the ways to handle this error is to ensure that you run the SET DEFINE ON SQL*Plus command—the first line of the variables_and_ampersands.sql script.
Occasionally you might want to use a named substitution variable more than once in your database script. If your input to the variable is the same for the duration of the script run, you can avoid being prompted for a value each time the variable is encountered by employing double ampersand (&&) notation. Listing 10 shows a script with a substitution variable used in multiple statements where the input is potentially the same for each variable invocation. Listing 11 demonstrates a similar script that uses double ampersand notation with the substitution variable.
Code Listing 10: Substitution variables requiring value input each time they are invoked
--Create a database script called resubstitute.sql select table_name, num_rows, to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed from user_tables where table_name = '&v_table'; select table_name, index_name, column_name, column_position from user_ind_columns where table_name = '&v_table'; --Execute the script SQL> @resubstitute.sql Enter value for v_table: EMPLOYEE old 3: where table_name = '&v_table' new 3: where table_name = 'EMPLOYEE' TABLE_NAME NUM_ROWS LAST_ANALYZED —————————————————————————————————————— —————————— ———————————————————— EMPLOYEE 16 13-MAY-2017 14:46:32 Enter value for v_table: EMPLOYEE old 3: where table_name = '&v_table' new 3: where table_name = 'EMPLOYEE' TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION —————————— ——————————————————————————— ———————————————————————— ——————————————— EMPLOYEE EMP_HIRE_DATE_I HIRE_DATE 1 EMPLOYEE DEP_WAGE_INCREASE_I DEPARTMENT_ID 1 EMPLOYEE DEP_WAGE_INCREASE_I WAGE_INCREASE_WORTHINESS 2 EMPLOYEE EMPLOYEE_PK EMPLOYEE_ID 1 EMPLOYEE EMP_DEPT_FK DEPARTMENT_ID 1 EMPLOYEE EMPLOYEE_WAGE_INC_WORTH_BMI WAGE_INCREASE_WORTHINESS 1 6 rows selected.
Code Listing 11: Using && to avoid repeated prompted user input for substitution variables
--Create a database script called substitute_and_reuse.sql select table_name, num_rows, to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed from user_tables where table_name = '&&v_table'; select table_name, index_name, column_name, column_position from user_ind_columns where table_name = '&&v_table'; --Execute the script SQL> @substitute_and_reuse.sql Enter value for v_table: EMPLOYEE old 3: where table_name = '&&v_table' new 3: where table_name = 'EMPLOYEE' TABLE_NAME NUM_ROWS LAST_ANALYZED ————————————————————————————————————————— —————————— ———————————————————— EMPLOYEE 16 13-MAY-2017 14:46:32 old 3: where table_name = '&&v_table' new 3: where table_name = 'EMPLOYEE' TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION —————————— ——————————————————————————— ———————————————————————— ——————————————— EMPLOYEE EMP_HIRE_DATE_I HIRE_DATE 1 EMPLOYEE DEP_WAGE_INCREASE_I DEPARTMENT_ID 1 EMPLOYEE DEP_WAGE_INCREASE_I WAGE_INCREASE_WORTHINESS 2 EMPLOYEE EMPLOYEE_PK EMPLOYEE_ID 1 EMPLOYEE EMP_DEPT_FK DEPARTMENT_ID 1 EMPLOYEE EMPLOYEE_WAGE_INC_WORTH_BMI WAGE_INCREASE_WORTHINESS 1 6 rows selected.
If you want to reset the value of your substitution variable when using double ampersand notation, delete the current value with the UNDEFINE SQL*Plus command. For example, to delete the current value of the v_table substitution variable in SQL*Plus, use the command
undefine v_tableConclusion
This article introduced you to the data dictionary, from the USER_, ALL_, and DBA_ static data dictionary views to the dynamic performance views (the V$ tables.) You saw database scripts combine multiple statements in a sequence into one file. You learned what a substitution variable is and how it can help in reducing SQL statement writing. Last, you learned how substitution variables can be reused and reset.
In the next article in this series, you’ll learn more about SQL statement scripting and SQL*Plus commands and get an introduction to dynamic SQL.
READ LEARN more about relational database design and concepts. |
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.