Oralce distinct method for removing duplicate records

The value of distinct is displayed only once.

No matter how many times this value appears, it is only displayed once.

Select distinct field name 1, field name 2 from Table order by field name 1

It is best to use it in combination with order. Improves efficiency

SQL>
SQL> CREATE TABLE employees (
2 au_id CHAR (3) not null,
3 au_fname VARCHAR (15) not null,
4 au_lname VARCHAR (15) not null,
5 phone VARCHAR (12) NULL,
6 address VARCHAR (20) NULL,
7 city VARCHAR (15) NULL,
8 state CHAR (2) NULL,
9 zip CHAR (5) NULL
10 );

Table created.

SQL>
SQL> insert into employees VALUES ('a01 ', 's',' B ', '2017-111-1111', '75 st', 'boston ', 'ny ', '123 ');

1 row created.

SQL> INSERT INTO employees VALUES ('a02', 'w', 'h', '2017-222-2222 ', '2017 Rd', 'boston ', 'co ', '123 ');

1 row created.

SQL> INSERT INTO employees VALUES ('a03', 'h', 'h', '2017-333-3333 ', '2017 Ave, # 14f', 'san Francisco ', 'CA', '123 ');

1 row created.

SQL> INSERT INTO employees VALUES ('a04 ', 'k', 'h', '2017-444-4444', '2017 Ave, # 14f', 'san Francisco ', 'CA', '123 ');

1 row created.

SQL> INSERT INTO employees VALUES ('a05 ', 'C', 'k', '2017-555-555555', '2017 st', 'New York', 'ny ', '123 ');

1 row created.

SQL> INSERT INTO employees VALUES ('a06 ', '', 'k', '2017-666-666', '2017 Mall', 'palo Alto ', 'CA ', '123 ');

1 row created.

SQL> INSERT INTO employees VALUES ('a07', 'P', 'O', '2017-777-7777 ', '2017 st', 'sarasota', 'fl ', '123 ');

1 row created.

SQL>
SQL>
SQL> SELECT DISTINCT state
2 FROM employees;

View duplicate SQL statements

SQL> select * from Employee
2/

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-----------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-apr-01232.78 Vancouver Tester

8 rows selected.

SQL>
SQL>
SQL> SELECT Description FROM employee
2/

DESCRIPTION
---------------
Programmer
Tester
Tester
Manager
Tester
Tester
Manager
Tester

8 rows selected.

SQL> SELECT DISTINCT Description FROM employee
2/

DESCRIPTION
---------------
Programmer
Manager
Tester

To process multiple lists, separate the fields with "," as follows:

SQL> SELECT DISTINCT city, state
2 FROM employees

Test the query efficiency after distinct is added

Only when the DISTINCT keyword is added, Oracle must sort all subsequent fields. Previously it was often found that because developers do not understand SQL very well, In the SELECT list of more than 20 fields added in front of the DISTINCT, resulting in the query is basically impossible to complete, or even generate a ORA-7445 error. Therefore, I have been emphasizing to developers the impact of DISTINCT on performance.

I did not expect the developer to tell me that if DISTINCT is added when testing a large SQL statement, it may take about four minutes to complete the query. If DISTINCT is not added, the query is executed for more than 10 minutes and the results are still not obtained.

The first thought is that DISTINCT is in the subquery. Due to the addition of DISTINCT, the result set in step 1 is reduced, resulting in improved query performance. The result is as follows, it is found that DISTINCT is in the outermost layer of the query.

Since the original SQL statement is too long and involves too many tables, it is hard to say clearly. Here we simulate an example. This example is due to the limited data volume and complexity of SQL statements, it cannot be seen that there is a significant difference in the execution time between the two. Here we will explain the problem through logical read comparison in two cases.

First, create a simulated environment:

 

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS
2 where owner = 'sys'
3 AND OBJECT_TYPE not like '% body'
4 AND OBJECT_TYPE not like 'java % ';
Table created.
SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SEGMENTS WHERE OWNER = 'sys ';
Table created.
SQL> CREATE TABLE T3 AS SELECT * FROM DBA_INDEXES WHERE OWNER = 'sys ';
Table created.
SQL> ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (OBJECT_NAME );
Table altered.
SQL> CREATE INDEX IND_T2_SEGNAME ON T2 (SEGMENT_NAME );
Index created.
SQL> CREATE INDEX IND_T3_TABNAME ON T3 (TABLE_NAME );
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 't1', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 't2', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 't3 ', METHOD_OPT =>' FOR ALL INDEXED COLUMNS SIZE 100 ', CASCADE => TRUE)
PL/SQL procedure successfully completed.